Bien commencer avec PolyBase dans SQL Server 2022

  • Article

S’applique à : SQL Server 2016 (13.x) - Windows et versions ultérieures SQL Server 2017 (14.x) - Linux et versions ultérieures.

Cet article vous guide tout au long d’un didacticiel sur l’utilisation de plusieurs dossiers et fichiers avec PolyBase dans SQL Server 2022 (16.x). Cet ensemble de requêtes de didacticiel illustre différentes fonctionnalités de PolyBase.

La virtualisation des données avec PolyBase dans SQL Server vous permet de tirer parti des fonctions de fichier de métadonnées pour interroger plusieurs dossiers, fichiers ou effectuer l’élimination des dossiers. La combinaison de la découverte de schémas avec l’élimination de dossiers et de fichiers est une fonctionnalité puissante qui permet à SQL d’extraire uniquement les données requises à partir de n’importe quel compte de stockage Azure ou solution de stockage d’objets compatible S3.

Prérequis

Avant d’utiliser PolyBase dans ce tutoriel, vous devez :

  1. Installez PolyBase sur Windows ou installez PolyBase sur Linux.
  2. Activer PolyBase dans sp_configure si nécessaire.
  3. Autoriser l’accès au réseau externe pour accéder au stockage Blob Azure disponible publiquement sur pandemicdatalake.blob.core.windows.net et azureopendatastorage.blob.core.windows.net.

Échantillons de jeux de données

Si vous débutez avec la virtualisation des données et souhaitez tester rapidement les fonctionnalités, commencez par interroger les jeux de données publics dans Azure Open Datasets, comme le jeu de données Bing COVID-19 autorisant l’accès anonyme.

Utilisez les points de terminaison suivants pour interroger les jeux de données Bing COVID-19 :

  • Parquet : abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV : abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Pour un démarrage rapide, exécutez cette requête T-SQL simple pour d’abord obtenir des insights sur le jeu de données. Cette requête utilise OPENROWSET pour interroger un fichier stocké dans un compte de stockage disponible publiquement :

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet', 
 FORMAT = 'parquet' 
) AS filerows;

Vous pouvez continuer l’exploration du jeu de données en ajoutant WHERE, GROUP BY et d’autres clauses T-SQL en fonction du jeu de résultats de la première requête.

Si la première requête échoue sur votre instance SQL Server, l’accès réseau est probablement empêché au compte de stockage Azure public. Contactez votre expert en réseau pour activer l’accès avant de pouvoir poursuivre avec les requêtes.

Une fois que vous êtes familiarisé avec l’interrogation des jeux de données publics, passez à des jeux de données non publics qui nécessitent des informations d’identification, en accordant des droits d’accès et en configurant des règles de pare-feu. Dans de nombreux scénarios réels, vous allez travailler principalement avec des jeux de données privés.

Source de données externe

Une source de données externe est une abstraction qui permet un référencement facile d’un emplacement de fichier sur plusieurs requêtes. Pour interroger des emplacements publics, tout ce que vous devez spécifier lors de la création d’une source de données externe est l’emplacement du fichier :

CREATE EXTERNAL DATA SOURCE MyExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
);

Remarque

Si vous recevez un message d’erreur 46530, External data sources are not supported with type GENERIC, cochez l’option de configuration PolyBase Enabled dans votre instance SQL Server. Elle doit avoir la valeur 1.

Exécutez ce qui suit pour activer PolyBase dans votre instance SQL Server :

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

Lorsque vous accédez à des comptes de stockage non publics, vous devez, outre l’emplacement, référencer un identifiant étendu à une base de données avec des paramètres d’authentification encapsulés. Le script suivant crée une source de données externe pointant vers le chemin du fichier et référençant un identifiant étendu à une base de données.

--Create external data source pointing to the file path, and referencing database-scoped credential: 
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

Interroger des sources de données avec OPENROWSET

La syntaxe OPENROWSET permet les requêtes ad hoc instantanées tout en créant uniquement le nombre minimal d’objets de base de données nécessaires.

OPENROWSET nécessite uniquement la création de la source de données externe (voire des informations d’identification), contrairement à l’approche de table externe qui nécessite un format de fichier externe et la table externe elle-même.

La valeur du paramètre DATA_SOURCE est automatiquement ajoutée au début du paramètre BULK pour former le chemin complet du fichier.

Quand vous utilisez OPENROWSET, indiquez le format du fichier, comme dans l’exemple suivant, qui interroge un fichier unique :

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.parquet', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Interroger plusieurs fichiers et dossiers

La commande OPENROWSET permet également d’interroger plusieurs fichiers ou dossiers en utilisant des caractères génériques dans le chemin BULK.

L’exemple suivant utilise le jeu de données ouvert NYC Yellow Taxi :

Dans un premier temps, créez la source de données externe :

--Create the data source first
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource 
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

À présent, nous pouvons interroger tous les fichiers présentant l’extension .parquet dans les dossiers. Par exemple, pour ce cas, nous allons interroger uniquement ces fichiers correspondant à un modèle de nom :

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Lors de l’interrogation de plusieurs fichiers ou dossiers, tous les fichiers accessibles avec la fonction OPENROWSET unique doivent avoir la même structure (par exemple le même nombre de colonnes et les mêmes types de données). Les dossiers ne peuvent pas être parcourus de manière récursive.

Inférence de schéma

L’inférence de schéma automatique vous permet d’écrire rapidement des requêtes et d’explorer des données quand vous ignorez les schémas de fichier. L’inférence de schéma fonctionne uniquement avec les fichiers Parquet.

Bien que pratiques, les types de données déduits peuvent être plus volumineux que les types de données réels, car il peut y avoir suffisamment d’informations dans les fichiers sources pour garantir que le type de données approprié est utilisé. Cela peut entraîner une baisse des performances des requêtes. Par exemple, les fichiers Parquet ne contiennent pas de métadonnées sur la longueur maximale des colonnes de caractères, l’instance déduit donc qu’il s’agit d’une valeur varchar(8000).

Utilisez la procédure stockée sys.sp_describe_first_results_set pour vérifier les types de données résultants de votre requête, comme dans l’exemple suivant :

EXEC sp_describe_first_result_set N'
 SELECT 
 vendorID, tpepPickupDateTime, passengerCount 
 FROM 
 OPENROWSET( 
  BULK ''yellow/*/*/*.parquet'', 
  DATA_SOURCE = ''NYCTaxiExternalDataSource'', 
  FORMAT=''parquet'' 
 ) AS nyc';

Une fois que vous connaissez les types de données, vous pouvez les spécifier à l’aide de la clause WITH pour améliorer les performances :

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount 
FROM 
 OPENROWSET( 
  BULK 'yellow/*/*/*.parquet', 
  DATA_SOURCE = 'NYCTaxiExternalDataSource', 
  FORMAT='PARQUET' 
 ) 
WITH ( 
 vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000 
 tpepPickupDateTime datetime2, 
 passengerCount int 
 ) AS nyc;

Étant donné que le schéma des fichiers CSV ne peut pas être déterminé automatiquement, les colonnes doivent toujours être spécifiées avec la clause WITH :

SELECT TOP 10 id, updated, confirmed, confirmed_change 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.csv', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'CSV', 
 FIRSTROW = 2 
) 
WITH ( 
 id int, 
 updated date, 
 confirmed int, 
 confirmed_change int 
) AS filerows;

Fonctions de métadonnées de fichier

Lors de l’interrogation de plusieurs fichiers ou dossiers, vous pouvez utiliser les fonctions filepath() et filename() pour lire les métadonnées de fichier et obtenir une partie du chemin d’accès ou le chemin complet, ainsi que le nom du fichier d’où provient la ligne du jeu de résultats : Dans l’exemple suivant, interrogez tous les fichiers et le chemin d’accès au fichier projet et les informations de nom de fichier pour chaque ligne :

--Query all files and project file path and file name information for each row: 

SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder], 
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows;

  • En cas d’appel sans paramètre, la fonction filepath() retourne le chemin du fichier dont provient la ligne. Quand DATA_SOURCE est utilisé dans OPENROWSET, il retourne le chemin relatif à DATA_SOURCE. Dans le cas contraire, il retourne le chemin de fichier complet.

  • En cas d’appel avec paramètre, la fonction filepath() retourne une partie du chemin qui correspond au caractère générique occupant la position spécifiée dans le paramètre. Par exemple, la valeur de paramètre 1 retourne une partie du chemin qui correspond au premier caractère générique.

La fonction filepath() peut également être utilisée pour le filtrage et l’agrégation de lignes :

SELECT 
 r.filepath() AS filepath 
 ,r.filepath(1) AS [year] 
 ,r.filepath(2) AS [month] 
 ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
DATA_SOURCE = 'NYCTaxiExternalDataSource', 
FORMAT = 'parquet' 
 ) AS r 
WHERE 
 r.filepath(1) IN ('2017') 
 AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
 r.filepath() 
 ,r.filepath(1) 
 ,r.filepath(2) 
ORDER BY 
 filepath;

Créer une vue sur OPENROWSET

Vous pouvez créer les vues pour inclure les requêtes OPENROWSET afin de pouvoir réutiliser facilement la requête sous-jacente. Les vues permettent également d’utiliser des outils de création de rapports et analytiques comme Power BI pour consommer les résultats de OPENROWSET.

Par exemple, considérez l’affichage suivant en fonction d’une OPENROWSET commande :

CREATE VIEW TaxiRides AS 
SELECT * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Il est également pratique d’ajouter des colonnes avec les données d’emplacement de fichier à une vue à l’aide de la fonction filepath() pour un filtrage plus facile et plus performant. L’utilisation des vues permet de réduire le nombre de fichiers et la quantité de données que la requête doit lire et traiter sur la vue quand elle est filtrée par l’une de ces colonnes :

CREATE VIEW TaxiRides AS 
SELECT * 
 , filerows.filepath(1) AS [year] 
 , filerows.filepath(2) AS [month] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Tables externes

Les tables externes encapsulent l’accès aux fichiers, ce qui rend l’expérience d’interrogation presque identique à l’interrogation des données relationnelles locales stockées dans les tables utilisateur. Pour créer une table externe, la source de données externe et les objets de format de fichier externe doivent exister :

--Create external file format 
CREATE EXTERNAL FILE FORMAT DemoFileFormat 
WITH ( 
 FORMAT_TYPE=PARQUET 
) 
GO 
 
--Create external table: 
CREATE EXTERNAL TABLE tbl_TaxiRides( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
);

Une fois la table externe créée, vous pouvez l’interroger comme toute autre table :

SELECT TOP 10 * 
FROM tbl_TaxiRides;

Tout comme OPENROWSET, les tables externes autorisent l’interrogation de plusieurs fichiers et dossiers à l’aide de caractères génériques. L’inférence de schéma n’est pas prise en charge avec les tables externes.

Sources de données externes

Pour plus de tutoriels sur la création de sources de données externes et de tables externes vers diverses sources de données, consultez référence PolyBase Transact-SQL.

Pour plus de tutoriels sur différentes sources de données externes, consultez :

Contenu connexe