Virtualiser un fichier Parquet dans un stockage d’objets compatible S3 avec PolyBase

  • Article

S’applique à : SQL Server 2022 (16.x)

SQL Server 2022 (16.x) peut virtualiser les données des fichiers Parquet. Ce processus permet aux données de rester à leur emplacement d’origine, mais il est possible de les interroger à partir d’une instance de SQL Server avec des commandes T-SQL au même titre qu’une table. Cette fonctionnalité utilise des connecteurs Polybase et minimise le recours aux processus ETL.

Dans l’exemple ci-dessous, nous allons virtualiser un fichier Parquet stocké sur un stockage d’objets compatible S3.

Pour plus d’informations sur la virtualisation de données, consultez Présentation de la virtualisation des données avec PolyBase.

Prérequis

Pour utiliser les fonctionnalités d’intégration du stockage d’objets compatible S3, vous avez besoin des outils et ressources suivants :

  • Installez la fonctionnalité PolyBase pour SQL Server.
  • Installez SQL Server Management Studio (SSMS) ou Azure Data Studio.
  • Stockage compatible S3.
  • Compartiment S3 créé. Les compartiments ne peuvent pas être créés ni configurés à partir de SQL Server.
  • Un utilisateur (Access Key ID) a été configuré, et le secret (Secret Key ID) et cet utilisateur sont connus de vous. Vous devez vous authentifier auprès du point de terminaison de stockage d’objets S3.
  • Autorisation ListBucket sur l’utilisateur S3.
  • Autorisation ReadOnly sur l’utilisateur S3.
  • TLS doit avoir été configuré. Il est supposé que toutes les connexions sont transmises de manière sécurisée par le protocole HTTPS et non HTTP. Le point de terminaison sera validé par un certificat installé sur l’hôte de système d’exploitation SQL Server.

Autorisation

Pour que l’utilisateur proxy puisse lire le contenu d’un compartiment S3, l’utilisateur doit être autorisé à effectuer les actions suivantes sur le point de terminaison S3 :

  • ListBucket ;
  • ReadOnly ;

Préconfiguration

  1. Activez PolyBase dans sp_configure :
exec sp_configure @configname = 'polybase enabled', @configvalue = 1
;
RECONFIGURE
;
exec sp_configure @configname = 'polybase enabled'
;
  1. Avant la création d’informations d’identification délimitées à la base de données, la base de données utilisateur doit avoir une clé principale pour protéger les informations d’identification. Pour plus d’informations, consultez CREATE MASTER KEY.

Créer des informations d’identification incluses dans l’étendue de la base de données

L’exemple de script suivant crée des informations d’identification s3-dc délimitées par la base de données utilisateur source dans SQL Server. Pour plus d’informations, consultez CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

IF NOT EXISTS(SELECT * FROM sys.credentials WHERE name = 's3_dc')
BEGIN
 CREATE DATABASE SCOPED CREDENTIAL s3_dc
 WITH IDENTITY = 'S3 Access Key',
 SECRET = '<AccessKeyID>:<SecretKeyID>' ;
END

Vérifiez les nouvelles informations d’identification délimitées par la base de données avec sys.database_scoped_credentials (Transact-SQL) :

SELECT * FROM sys.database_scoped_credentials;

Créer une source de données externe

L’exemple de script suivant illustre la création d’une source de données externe s3_ds dans la base de données utilisateur source au sein de SQL Server. La source de données externe fait référence aux informations d’identification de portée étendue à la base de données s3_dc. Pour plus d’informations, consultez CRÉER UNE SOURCE DE DONNÉES EXTERNES.

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO

Vérifiez la nouvelle source de données externe avec sys.external_data_sources.

SELECT * FROM sys.external_data_sources;

URL hébergées virtuelles

Certains systèmes de stockage compatibles S3 (tels qu’Amazon Web Services) utilisent des URL de style virtual_hosted pour implémenter la structure de dossiers dans le compartiment S3. Ajoutez le CONNECTION_OPTIONS suivant pour permettre la création de tables externes pointant vers des emplacements de dossiers dans le compartiment S3, par exemple CONNECTION_OPTIONS = '{"s3":{"url_style":"virtual_hosted"}}'.

Sans ce paramètre CONNECTION_OPTIONS, lors de la requête de tables externes pointant vers un dossier, vous pouvez observer l’erreur suivante :

Msg 13807, Level 16, State 1, Line 23  
Content of directory on path '/<folder_name>/' cannot be listed.

SELECT à partir d’un fichier Parquet avec OPENROWSET

L’exemple suivant illustre l’utilisation de T-SQL pour interroger un fichier Parquet stocké dans le stockage d’objets compatible S3 au moyen d’une requête OPENROWSET. Pour plus d’informations, consultez OPENROWSET (Transact-SQL).

Comme il s’agit d’un fichier Parquet, deux choses importantes se produisent automatiquement :

  1. SQL Server lit le schéma à partir du fichier lui-même. Il n’est donc pas nécessaire de définir la table, les colonnes ou les types de données.
  2. Il n’est pas nécessaire de déclarer le type de compression pour que le fichier soit lu.
SELECT  * 
FROM    OPENROWSET
        (   BULK '/<bucket>/<parquet_folder>'
        ,   FORMAT       = 'PARQUET'
        ,   DATA_SOURCE  = 's3_ds'
        ) AS [cc];

Interroger le stockage d’objets compatible S3 via une table externe

L’exemple suivant illustre l’utilisation de T-SQL pour interroger un fichier parquet stocké dans un stockage d’objets compatible S3 via l’interrogation d’une table externe. L’exemple utilise un chemin relatif dans la source de données externe.

CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Region (
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds', 
FILE_FORMAT = ParquetFileFormat);
GO

SELECT * FROM [Region];

Pour plus d’informations, consultez l’article suivant :

Limites

  1. Les requêtes SQL Server sur une table externe soutenue par un stockage compatible S3 sont limitées à 1 000 objets par préfixe. En effet, la liste d’objets compatible S3 est limitée à 1 000 clés d’objet par préfixe.
  2. Pour le stockage d’objets compatible S3, les clients ne sont pas autorisés à créer leur ID de clé d’accès comportant un caractère :.
  3. La longueur totale de l’URL est limitée à 259 caractères. Cela signifie que s3://<hostname>/<objectkey> ne doit pas dépasser 259 caractères. s3:// est déduit de cette limite, par conséquent, la longueur du chemin d’accès ne peut pas dépasser 259-5 = 254 caractères.
  4. Le nom des informations d’identification SQL est limité à 128 caractères au format UTF-16.
  5. Le nom des informations d’identification créé doit contenir le nom du compartiment, sauf si ces informations d’identification concernent une nouvelle source de données externe.
  6. L’ID de clé d’accès et l’ID de clé secrète doivent uniquement contenir des valeurs alphanumériques.

Étapes suivantes