Exercice - Utiliser PolyBase pour interroger un fichier Parquet
Dans cet exercice, vous procédez comme suit :
- Installez et activez PolyBase.
- Créez une base de données.
- Créez une clé principale de base de données pour sécuriser les identifiants associés à la base de données.
- Créez des informations d’identification délimitées à la base de données pour accéder à la source de données.
- Créez la source de données.
- Interrogez et manipulez les données stockées sur la source de données publique.
- Créez un format de fichier externe et une table externe.
Installer PolyBase
Vous pouvez installer PolyBase avec l’exécutable d’installation de SQL Server lors de l’installation initiale ou l’ajouter en tant que fonctionnalité ultérieurement. Dans la page Sélection des fonctionnalités de SQL Server setup.exe, sélectionnez Service de requête PolyBase pour les données externes.
Les services PolyBase nécessitent que les ports de pare-feu soient activés pour se connecter à des sources de données externes. Par défaut, PolyBase utilise des ports allant de 16450 à 16460.
Le programme d’installation de PolyBase installe deux services PolyBase, SQL Server PolyBase Engine et SQL Server PolyBase Data Movement. Pour obtenir des informations complètes et des prérequis pour l’installation de PolyBase, consultez :
Activer PolyBase
Une fois le service installé, connectez-vous à votre instance SQL Server 2025 dans SQL Server Management Studio (SSMS) et exécutez la commande suivante pour activer PolyBase.
EXEC SP_CONFIGURE @CONFIGNAME = N'POLYBASE ENABLED', @CONFIGVALUE = 1;
RECONFIGURE;
Remarque
Dans cet exercice, vous interrogez des fichiers Apache Parquet à l'aide de l’API REST PolyBase, donc vous n’avez pas besoin d’activer ou de configurer le SQL Server PolyBase Data Movement ou le moteur PolyBase de SQL Server.
Créer une base de données
Exécutez la commande suivante dans SSMS pour créer une base de données pour cet exercice nommé Demo1. Si la base de données a déjà été créée, le script supprime et le recrée.
USE MASTER;
IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'Demo1')
BEGIN
ALTER DATABASE Demo1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE IF EXISTS Demo1
END;
CREATE DATABASE Demo1;
USE Demo1;
Créez la clé principale de base de données
Vous devez créer une clé principale de base de données pour garantir la sécurité des informations d’identification délimitées à la base de données. L’exemple suivant crée la clé avec un mot de passe généré de manière aléatoire et une sauvegarde est requise.
DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ' + QUOTENAME(@randomWord, '''')
EXECUTE sp_executesql @createMasterKey;
SELECT * FROM sys.symmetric_keys;
Pour mieux comprendre et gérer les clés de chiffrement dans un environnement de production, consultez :
Créer les informations d’identification délimitées à la base de données
Les informations d’identification spécifiques à la base de données sont responsables du stockage des informations d’identification que la source de données utilise pour se connecter au terminal. Cet exemple utilise un point de terminaison public, de sorte que les informations d’identification n’ont pas besoin d’un secret.
IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = N'PublicCredential')
DROP DATABASE SCOPED CREDENTIAL PublicCredential;
CREATE DATABASE SCOPED CREDENTIAL PublicCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<KEY>'; -- This example doesn't need the SECRET because the data source is public
Créer la source de données
Cet exemple utilise un jeu de données COVID Parquet publiquement disponible stocké dans stockage Blob Azure. Vous utilisez les PublicCredential délimitées à la base de données que vous avez créées pour établir la connexion.
Valeurs d'emplacement :
- Préfixe :
abs - Compte de stockage Azure :
pandemicdatalake - Chemin complet du compte stockage Azure :
pandemicdatalake.blob.core.windows.net - Nom du conteneur :
public - Chemin complet du conteneur :
public/curated/covid-19/bing_covid-19_data/latest
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = N'Public_Covid') DROP EXTERNAL DATA SOURCE Public_Covid;
CREATE EXTERNAL DATA SOURCE Public_Covid
WITH (
LOCATION = 'abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest',
CREDENTIAL = [PublicCredential]
);
- Pour obtenir la liste complète des sources de données et des préfixes correspondants, consultez CREATE EXTERNAL DATA SOURCE.
- Pour plus d’informations sur le jeu de données public, consultez Bing COVID-19.
Interroger les données avec OPENROWSET
Vous pouvez utiliser OPENROWSET pour accéder aux données et les explorer. OPENROWSET est optimisé pour les scénarios d’exploration de données et de charge de travail ad hoc.
Valeurs OPENROWSET :
- BULK : Nom et extension de fichier. BULK ajoute automatiquement les informations de source de données, de sorte que l’emplacement complet du fichier est
abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet - FORMAT :
PARQUET - DATA_SOURCE : informations de connexion, dans ce cas votre nouvelle source de données
Public_Covid
SELECT TOP 1000 *
FROM OPENROWSET
(BULK 'bing_covid-19_data.parquet'
, FORMAT = 'PARQUET'
, DATA_SOURCE = 'Public_Covid')
AS [COVID_Dataset]
L’exemple suivant utilise la flexibilité T-SQL pour interroger le fichier Parquet en temps réel, comme une table régulière. Pour retourner le nombre de cas confirmés par état américain dans l’ordre décroissant, exécutez la requête suivante :
SELECT [COVID_Dataset].admin_region_1,
SUM(CAST([COVID_Dataset].confirmed AS BIGINT)) AS Confirmed
FROM OPENROWSET
(BULK 'bing_covid-19_data.parquet'
, FORMAT = 'PARQUET'
, DATA_SOURCE = 'Public_Covid')
AS [COVID_Dataset]
WHERE [COVID_Dataset].country_region = 'United States' AND
[COVID_Dataset].admin_region_1 IS NOT NULL
GROUP BY [COVID_Dataset].admin_region_1
ORDER BY confirmed DESC
Créer et interroger une table externe
OPENROWSET est optimisé pour l’exécution ad hoc et l’exploration des données. Les tables externes sont mieux adaptées à l’accès périodique, car elles peuvent également utiliser des statistiques.
Découvrir le schéma de la table externe
Pour créer une table externe, commencez par déterminer les colonnes et le type. Le schéma provient d’un fichier externe. Il peut donc être fastidieux de déterminer précisément les types et plages de données. Heureusement, vous pouvez utiliser la procédure stockée sp_describe_first_result_set (Transact-SQL) pour accélérer ce processus.
DECLARE @tsql NVARCHAR(MAX) = 'SELECT TOP 1000 *
FROM OPENROWSET
(BULK ''bing_covid-19_data.parquet''
, FORMAT = ''PARQUET''
, DATA_SOURCE = ''Public_Covid'')
AS [COVID_Dataset]';
EXEC sys.sp_describe_first_result_set @tsql;
Vous pouvez voir que sp_describe_first_result_set les noms de colonnes, les types, la longueur, la précision et même le classement de la source de données sont retournés.
Créer le format de fichier externe
Étant donné que vous devez référencer le fichier Parquet à la table externe, vous devez d'abord exécuter CREATE EXTERNAL FILE FORMAT pour ajouter le format de fichier Parquet. La définition de format de fichier est importante pour les tables externes, car elle spécifie le type de disposition et de compression réels.
Exécutez la commande suivante:
IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
DROP EXTERNAL FILE FORMAT ParquetFileFormat;
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
Créer la table externe
Enfin, avec toutes les informations que vous venez d’acquérir et le format de fichier externe créé, vous pouvez créer la table externe à l’aide du script suivant :
IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
DROP EXTERNAL FILE FORMAT ParquetFileFormat;
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
-- 8.3 CREATE EXTERNAL TABLE
IF OBJECT_ID(N'ext_covid_data', N'ET') IS NOT NULL
DROP EXTERNAL TABLE ext_covid_data;
CREATE EXTERNAL TABLE ext_covid_data
(
id int,
updated date,
confirmed int,
confirmed_change int,
deaths int,
deaths_change smallint,
recovered int,
recovered_change int,
latitude float,
longitude float,
iso2 varchar(8000),
iso3 varchar(8000),
country_region varchar(8000),
admin_region_1 varchar(8000),
iso_subdivision varchar(8000),
admin_region_2 varchar(8000),
load_time datetime2(7)
)
WITH
(
LOCATION = 'bing_covid-19_data.parquet'
, FILE_FORMAT = ParquetFileFormat
, DATA_SOURCE = Public_Covid
);
CREATE STATISTICS [Stats_ext_covid_data_updated] ON ext_covid_data([updated]);
SELECT TOP 1000 * FROM ext_covid_data;
Remarque
Les noms de colonnes doivent correspondre aux colonnes stockées dans le fichier Parquet, ou SQL Server ne peut pas identifier les colonnes et les retourner NULL.
Après avoir créé la table ext_covid_dataexterne, vous pouvez ajouter des statistiques sur les colonnes mises à jour pour plus d’efficacité. Pour plus d'informations sur les statistiques liées à la table externe, consultez CREATE STATISTICS (Transact-SQL).
Dans cette unité, vous avez utilisé PolyBase pour vous connecter à une source de données externe et utiliser OPENROWSET ou une table externe pour interroger le fichier Parquet. Dans l’exercice suivant, vous utilisez les services PolyBase pour vous connecter et créer une table externe à partir d’une base de données dans Azure SQL Database.