Partage via

Virtualiser une table delta avec PolyBase

  • Article

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

SQL Server 2022 (16.x) peut interroger des données directement à partir d’un dossier de table delta. Ce concept, communément appelé virtualisation des données, 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 à la copie des données via les processus ETL.

Dans l’exemple suivant, le dossier de table delta est stocké sur Stockage Blob Azure et accessible via OPENROWSET ou une table externe.

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

Préconfiguration

1. Activez PolyBase dans sp_configure :

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Créer une base de données utilisateur

Cet exercice crée un exemple de base de données avec les paramètres et l’emplacement par défaut. Vous utilisez cet exemple de base de données vide pour utiliser les données et stocker les informations d’identification délimitées. Dans cet exemple, une nouvelle base de données vide nommée Delta_demo est utilisée.

CREATE DATABASE [Delta_demo];

3. Créer la clé principale et les informations d’identification délimitées à la base de données

La clé principale de la base de données dans la base de données utilisateur est requise pour chiffrer le secret des informations d’identification limitées à la base de données, delta_storage_dsc. Pour cet exemple, la table delta réside sur Azure Data Lake Storage Gen2.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Créer une source de données externes

Les informations d’identification délimitées à la base de données seront utilisées pour la source de données externe. Dans cet exemple, la table delta réside dans Azure Data Lake Storage Gen2 ; utilisez par conséquent le préfixe adls et la méthode d’identité SHARED ACCESS SIGNATURE. Pour plus d’informations sur les connecteurs et les préfixes, y compris les nouveaux paramètres de SQL Server 2022 (16.x), reportez-vous à CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Par exemple, si votre compte de stockage se nomme delta_lake_sample et que le conteneur se nomme sink, le code sera :

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Utiliser OPENROWSET pour accéder aux données

Dans cet exemple, le dossier table de données se nomme Contoso.

Étant donné que le Delta_ED de la source de données externe est mappée au niveau d’un conteneur. Le Contoso du dossier de table delta se trouve dans une racine. Pour interroger un fichier dans une structure de dossiers, fournissez un mappage de dossiers par rapport au paramètre LOCATION de la source de données externe.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Interroger les données avec une table externe

CREATE EXTERNAL TABLE peut également être utilisé pour virtualiser les données de table delta dans SQL Server. Les colonnes doivent être définies et fortement typées. Bien que les tables externes prennent plus d’efforts pour créer, elles offrent également des avantages supplémentaires sur la requête d’une source de données externe avec OPENROWSET. Vous pouvez :

  • Renforcer la définition de la saisie des données pour une colonne donnée
  • Définir la nullabilité
  • Définir LE CLASSEMENT
  • Créer des statistiques pour une colonne afin d’optimiser la qualité du plan de requête
  • Créer un modèle plus granulaire dans SQL Server pour l’accès aux données afin d’améliorer votre modèle de sécurité

Pour plus d'informations, consultez la page CREATE EXTERNAL TABLE.

Pour l’exemple suivant, on utilise la même source de données.

1. Créer un format de fichier externe

Pour définir la mise en forme du fichier, un format de fichier externe est requis. Les formats de fichiers externes sont également recommandés en raison de la réutilisation. Pour plus d’informations, consultez CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Créer une table externe

Les fichiers de table delta se trouvent à l’emplacement /delta/Delta_yob/ et la source de données externe pour cet exemple est le stockage d’objets compatible S3, précédemment configuré sous le s3_eds de la source de données. PolyBase peut utiliser L’EMPLACEMENT du dossier de la table delta ou du fichier absolu lui-même, qui se trouve à delta/Delta_yob/_delta_log/00000000000000000000.json.

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Limites

Si vous créez une table externe pointant vers une table delta partitionnée, la colonne utilisée pour le partitionnement retourne NULL lors de la requête de la table externe. Toutefois, si vous utilisez une requête OPENROWSET, la valeur de colonne retourne correctement. Pour contourner cette limitation, créez une vue sur la requête OPENROWSET, puis interrogez la vue pour obtenir les valeurs de colonne partitionnée à retourner correctement.

Vous pouvez rencontrer les erreurs suivantes lors de l’interrogation d’une table Delta externe :

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

Cela peut se produire, car il existe un indicateur de requête QUERYTRACEON qui peut être ajouté à la requête de métadonnées de fichier Delta et qui nécessite le rôle serveur sysadmin pour s’exécuter. Si cela se produit, vous pouvez résoudre le problème en activant globalement l’indicateur de trace 14073, ce qui empêchera l’ajout de l’indicateur de requête.

Contenu connexe