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 peuvent être interrogées à partir d’une instance SQL Server avec des commandes T-SQL comme n’importe quelle autre table. Cette fonctionnalité utilise des connecteurs PolyBase et réduit le besoin de copier des données via des 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 des données, présentation de la virtualisation des données avec PolyBase.

Préconfiguration

1. Activer 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 une clé principale et des informations d’identification délimitées à la base de données

La clé principale de base de données dans la base de données utilisateur est requise pour chiffrer le secret d’informations d’identification délimité à la base de données. delta_storage_dsc Pour cet exemple, la table delta réside sur Azure Data Lake Stockage 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 externe

Les informations d’identification délimitées à la base de données sont 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 est nommé Contoso.

Étant donné que la source Delta_ED de données externe est mappée au niveau d’un conteneur. Le Contoso 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 des 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 l’interrogation 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 CREATE EXTERNAL TABLE.

Pour l’exemple suivant, la même source de données est utilisée.

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 la source s3_edsde données. PolyBase peut utiliser l’emplacement du dossier de la table delta ou du fichier absolu lui-même, qui se trouve à l’emplacement 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 l’interrogation de la table externe. Toutefois, si vous utilisez une OPENROWSET requête, la valeur de colonne retourne correctement. Pour contourner cette limitation, créez une vue sur la OPENROWSET requête, 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 QUERYTRACEON indicateur de requête qui peut être ajouté à la requête de métadonnées de fichier Delta et qui nécessite sysadmin l’exécution du rôle serveur. Si cela se produit, vous pouvez résoudre le problème en activant globalement l’indicateur de trace 14073 et cela empêche l’ajout de l’indicateur de requête.

Contenu connexe