Partager via

Virtualisation des données avec Azure SQL Database (préversion)

S’applique à :Azure SQL Database

La fonctionnalité de virtualisation des données d’Azure SQL Database vous permet d’exécuter des requêtes Transact-SQL (T-SQL) sur des fichiers qui stockent des données dans des formats de données courants tels que CSV (sans avoir besoin d’utiliser du texte délimité), Parquet et Delta (1.0). Vous pouvez interroger ces données dans Azure Data Lake Storage Gen2 ou Stockage Blob Azure et les combiner avec des données relationnelles stockées localement à l’aide de jointures. De cette façon, vous pouvez accéder de façon transparente aux données externes (en mode lecture seule) tout en les conservant dans le format et l’emplacement d’origine. C’est ce que l’on appelle également la virtualisation des données.

Aperçu

La virtualisation des données fournit deux façons d’interroger des fichiers pour différents ensembles de scénarios :

  • Syntaxe OPENROWSET : optimisée pour l’interrogation ad hoc des fichiers. Généralement utilisé pour explorer rapidement le contenu et la structure d’un nouvel ensemble de fichiers.
  • Syntaxe CREATE EXTERNAL TABLE : optimisées pour l’interrogation répétitive des fichiers avec une syntaxe identique, comme si les données étaient stockées localement dans la base de données. Les tables externes nécessitent plusieurs étapes de préparation par rapport à la syntaxe OPENROWSET, mais elles offrent plus de contrôle sur l’accès aux données. Les tables externes sont généralement utilisées pour la génération de rapports et les charges de travail analytiques.

Dans les deux cas, une source de données externe doit être créée en utilisant la syntaxe T-SQL CREATE EXTERNAL DATA SOURCE, comme illustré dans cet article.

Formats de fichier

Les formats de fichiers Parquet et texte délimité (CSV) sont directement pris en charge. Le format de fichier JSON est pris en charge indirectement en spécifiant le format de fichier CSV dans lequel les requêtes retournent chaque document sous la forme d’une ligne distincte. Vous pouvez analyser les lignes à l’aide de JSON_VALUE et OPENJSON.

Types de stockage

Les fichiers peuvent être stockés dans Azure Data Lake Storage Gen2 ou Stockage Blob Azure. Pour interroger des fichiers, vous devez fournir l’emplacement dans un format spécifique et utiliser le préfixe de type d’emplacement correspondant au type de source externe et de point de terminaison/protocole, comme les exemples suivants :

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

Importante

Utilisez toujours des préfixes spécifiques au point de terminaison. Le préfixe de type d’emplacement fourni est utilisé pour choisir le protocole optimal pour la communication et tirer parti de toutes les fonctionnalités avancées offertes par ce type de stockage particulier.

Le préfixe générique https:// est uniquement pris en charge pour BULK INSERT, mais pas pour d’autres cas d’usage, y compris OPENROWSET ou EXTERNAL TABLE.

Commencez

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 :

--Quick query on a file stored in a publicly available storage account:
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 poursuivre l’exploration du jeu de données en ajoutant WHERE, GROUP BY et d’autres clauses en fonction du jeu de résultats de la première requête.

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.

Accès à des comptes de stockage non publics

Un utilisateur connecté à une base de données Azure SQL Doit être autorisé à accéder aux fichiers stockés dans des comptes de stockage non publics et les interroger. Les étapes d’autorisation dépendent de la façon dont Azure SQL Database authentifie le stockage. Les types d’authentification et les paramètres associés ne sont pas fournis directement avec chaque requête. Ils sont encapsulés dans l’objet d’informations d’identification pour la base de données stocké dans la base de données utilisateur. Les informations d’identification sont utilisées par la base de données pour accéder au compte de stockage chaque fois que la requête s’exécute.

Azure SQL Database prend en charge les types d’authentification suivants :

  • Signature d’accès partagé (SAS)
  • Identité managée
  • Authentification directe Microsoft Entra via l’identité utilisateur

Une signature d’accès partagé (SAP) fournit un accès délégué aux fichiers d’un compte de stockage. SAS offre un contrôle granulaire sur le type d’accès que vous accordez, y compris l’intervalle de validité, les autorisations accordées et la plage d’adresses IP acceptable. Une fois le jeton SAP créé, il ne peut pas être révoqué ou supprimé, et il autorise l’accès jusqu’à l’expiration de sa période de validité.

  1. Vous pouvez obtenir un jeton SAP de plusieurs façons :

  2. Octroyez des autorisations Lecture et Liste par le biais de la SAS pour accéder aux données externes. Actuellement, la virtualisation des données avec Azure SQL Database est en lecture seule.

  3. Pour créer des informations d’identification délimitées à la base de données dans Azure SQL Database, vous devez d’abord créer la clé principale de base de données, si elle n’existe pas déjà. Une clé principale de base de données est requise lorsque les informations d’identification nécessitent SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. Lorsqu’un jeton SAP est généré, il inclut un point d’interrogation (?) au début du jeton. Pour utiliser le jeton, vous devez supprimer le point d’interrogation (?) lors de la création des informations d’identification. Par exemple:

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Accès au stockage public via des comptes anonymes

Si le jeu de données souhaité autorise l’accès public (également appelé accès anonyme), aucune information d’identification n’est requise tant que le stockage Azure est correctement configuré, consultez Configurer l’accès en lecture anonyme pour les conteneurs et les objets blob.

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'
);

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://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       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 :

--Query all files with .parquet extension in folders matching name pattern:
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 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 :

--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;

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, elle 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 et utiliser les vues pour inclure les requêtes OPENROWSET dans un wrapper afin de pouvoir réutiliser facilement la requête sous-jacente :

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;

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.

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
);

--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.

Considérations relatives aux performances

Il n’existe pas de limite inconditionnelle en termes de nombre de fichiers ou de données pouvant être interrogés. Toutefois, les performances des requêtes dépendent de la quantité et du format des données, de la façon dont les données sont organisées, ainsi que de la complexité des requêtes et des jointures.

Interroger des données partitionnées

Les données sont souvent organisées dans des sous-dossiers, aussi appelés partitions. Vous pouvez demander à la requête de lire uniquement des dossiers et des fichiers particuliers. En procédant ainsi, vous réduisez le nombre de fichiers et la quantité de données que la requête doit lire et traiter, et augmentez les performances. Ce type d’optimisation de requête est connu sous le nom de nettoyage de partition ou d’élimination de partition. Vous pouvez éliminer les partitions de l’exécution de requête à l’aide de la fonction filepath() de métadonnées dans la WHERE clause de la requête.

L’exemple de requête suivant lit les fichiers de données NYC Yellow Taxi uniquement pour les trois derniers mois de 2017 :

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'
    )
WITH (
    vendorID INT
) 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;

Si vos données stockées ne sont pas partitionnées, envisagez de les partitionner pour améliorer les performances des requêtes.

Si vous utilisez des tables externes, les fonctions filepath() et filename() sont prises en charge, mais pas dans la clause WHERE.

Résolution des problèmes

Les problèmes liés à l’exécution des requêtes sont généralement causés par Azure SQL Database qui n’est pas en mesure d’accéder à l’emplacement du fichier. Les messages d’erreur associés peuvent signaler des droits d’accès insuffisants, un emplacement ou un chemin de fichier inexistants ou un fichier utilisé par un autre processus, ou indiquer que l’annuaire ne peut pas être répertorié. Dans la plupart des cas, cela indique que l’accès aux fichiers est bloqué par les stratégies de contrôle du trafic réseau ou en raison d’un manque de droits d’accès. Voici les éléments devant être vérifiés :

  • Chemin d’emplacement incorrect ou mal orthographié.
  • Validité de la clé SAS : elle peut avoir expiré, contenir une faute de frappe ou commencer par un point d’interrogation.
  • Autorisations de clé SAS accordées : Lecture au minimum et Liste si des caractères génériques sont utilisés.
  • Trafic entrant bloqué sur le compte de stockage. Vérifiez la gestion des règles de réseau virtuel pour stockage Azure.
  • Droits d’accès à l’identité managée : assurez-vous que l’identité managée d’Azure SQL Database dispose des droits d’accès au compte de stockage.
  • Le niveau de compatibilité de la base de données doit être égal ou supérieur à 130 pour que les requêtes de virtualisation des données fonctionnent.

Limites

  • Actuellement, les statistiques sur les tables externes ne sont pas prises en charge dans Azure SQL Database.
  • Actuellement, CREATE EXTERNAL TABLE AS SELECT n’est pas disponible sur Azure SQL Database.
  • La fonctionnalité Sécurité au niveau des lignes n’est pas prise en charge avec les tables externes.
  • La règle Dynamic Data Masking ne peut pas être définie pour une colonne dans une table externe.
  • Managed Identity ne prend pas en charge les scénarios interlocataires, si vous avez un compte de stockage Azure dans un autre locataire, la signature d’accès partagé est la méthode prise en charge.

Problèmes connus

  • Quand le paramétrage pour Always Encrypted est activé dans SQL Server Management Studio (SSMS), les requêtes de virtualisation des données échouent avec le message d’erreur Incorrect syntax near 'PUSHDOWN'.

Contenu connexe

  • Last updated on