Utiliser des tables externes avec Synapse SQL

Une table externe pointe vers des données situées dans Hadoop, Stockage Blob Azure ou Azure Data Lake Storage. Vous pouvez utiliser les tables externes pour lire des données à partir de fichiers ou écrire des données dans des fichiers dans Stockage Azure.

Avec Synapse SQL, vous pouvez utiliser des tables externes pour lire des données externes en utilisant le pool SQL dédié ou le pool SQL serverless.

Selon le type de la source de données externe, vous pouvez utiliser deux types de tables externes :

• Les tables externes Hadoop, qui vous permettent de lire et d’exporter des données dans divers formats de données, par exemple CSV, Parquet et ORC. Les tables externes Hadoop sont disponibles dans les pools SQL dédiés, mais pas dans les pools SQL serverless.
• Les tables externes natives, qui vous permettent de lire et d’exporter des données dans divers formats de données, par exemple CSV et Parquet. Des tables externes natives sont disponibles dans des pools SQL serverless. Elles sont en préversion publique dans des pools SQL dédiés. L’écriture/l’exportation de données à l’aide de CETAS et des tables externes natives est disponible uniquement dans le pool SQL serverless, mais pas dans les pools SQL dédiés.

Principales différences entre les tables externes Hadoop et natives :

Type de table externe	Hadoop	Natif
Pool SQL dédié	Disponible	Seules les tables Parquet sont disponibles en préversion publique.
Pool SQL serverless	Non disponible	Disponible
Formats pris en charge	Délimité/CSV, Parquet, ORC, Hive RC et RC	Pool SQL serverless : Délimité/CSV, Parquet, et Delta Lake Pool SQL dédié : Parquet (préversion)
Élimination des partitions de dossier	Non	L’élimination des partitions est disponible uniquement dans les tables partitionnées créées sur des formats Parquet ou CSV synchronisés à partir de pools Apache Spark. Vous pouvez créer des tables externes sur des dossiers partitionnés Parquet, mais les colonnes de partitionnement sont inaccessibles et ignorées. De plus, l’élimination des partitions ne sera pas appliquée. Ne créez pas de tables externes sur des dossiers Delta Lake, car elles ne sont pas prises en charge. Utilisez des vues partitionnées Delta si vous avez besoin d’interroger des données Delta Lake partitionnées.
Élimination de fichier (pushdown de prédicat)	No	Oui, dans un pool SQL serverless. Pour le pushdown de chaîne, vous devez utiliser un classement Latin1_General_100_BIN2_UTF8 sur les colonnes VARCHAR pour activer le pushdown. Pour plus d’informations sur les classements, consultez Types de classements pris en charge pour Synapse SQL.
Format personnalisé pour l’emplacement	Non	Oui, en utilisant des caractères génériques comme /year=*/month=*/day=* pour les formats Parquet ou CSV. Les chemins vers des dossiers personnalisés ne sont pas disponibles dans Delta Lake. Dans le pool SQL serverless, vous pouvez également utiliser des caractères génériques récursifs /logs/** pour référencer des fichiers Parquet ou CSV dans n’importe quel sous-dossier sous le dossier référencé.
Analyse récursive des dossiers	Oui	Oui. Dans les pools SQL serverless, /** doit être spécifié à la fin du chemin d’accès d’emplacement. Dans le pool dédié, les dossiers sont toujours analysés de manière récursive.
Authentification du stockage	Clé d’accès de stockage, authentification directe Microsoft Entra, identité managée, identité Microsoft Entra d’application personnalisée	Signature d’accès partagé (SAS), Authentification directe Microsoft Entra, Identité managée, Identité Microsoft Entra d’application personnalisée.
Mappage de colonnes	Ordinal : les colonnes de la définition de table externe sont mappées aux colonnes des fichiers Parquet sous-jacents par position.	Pool serverless : par nom. Les colonnes de la définition de table externe sont mappées aux colonnes des fichiers Parquet sous-jacents par correspondance de nom de colonne. Pool dédié : correspondance ordinale. Les colonnes de la définition de table externe sont mappées aux colonnes des fichiers Parquet sous-jacents par position.
CETAS (exportation/transformation)	Oui	CETAS avec les tables natives pour cible fonctionne uniquement dans le pool SQL serverless. Vous ne pouvez pas utiliser les pools SQL dédiés pour exporter des données à l’aide de tables natives.

Notes

Les tables externes natives constituent la solution recommandée dans les pools où elles sont généralement disponibles. Si vous avez besoin d’accéder à des données externes, utilisez toujours les tables natives dans des pools serverless. Dans des pools dédiés, vous devez basculer vers les tables natives pour la lecture des fichiers Parquet une fois ceux-ci en disponibilité générale. Utilisez les tables Hadoop uniquement si vous devez accéder à certains types qui ne sont pas pris en charge dans des tables externes natives (par exemple, ORC, RC), ou si la version native n’est pas disponible.

Tables externes dans un pool SQL dédié et un pool SQL serverless

Vous pouvez utiliser des tables externes pour :

• Interroger Stockage Blob Azure et Azure Data Lake Gen2 avec des instructions Transact-SQL.
• Stocker les résultats de requête dans des fichiers dans le Stockage Blob Azure ou Azure Data Lake Storage à l’aide de CETAS.
• Importer des données à partir du Stockage Blob Azure et d’Azure Data Lake Storage et les stocker dans un pool SQL dédié (tables Hadoop uniquement dans un pool dédié).

Notes

Utilisée conjointement avec l’instruction CREATE TABLE AS SELECT, la sélection à partir d’une table externe permet d’importer des données dans une table du pool SQL dédié.

Si les performances des tables externes Hadoop dans les pools dédiés ne répondent pas à vos objectifs en matière de performances, envisagez de charger des données externes dans les tables de l’entrepôt de données à l’aide de l’instruction Copy.

Pour obtenir un tutoriel sur le chargement, consultez Utiliser PolyBase pour charger des données du Stockage Blob Azure.

Vous pouvez créer des tables externes dans des pools Synapse SQL en effectuant les étapes suivantes :

1. CRÉEZ UNE SOURCE DE DONNÉES EXTERNE pour référencer un stockage Azure externe, puis spécifiez les informations d’identification à utiliser pour accéder au stockage.
2. CRÉEZ UN FORMAT DE FICHIER EXTERNE pour décrire le format des fichiers CSV ou Parquet.
3. CRÉEZ UNE TABLE EXTERNE en plus des fichiers placés dans la source de données avec le même format de fichier.

Élimination des partitions de dossier

Les tables externes natives dans des pools Synapse peuvent ignorer les fichiers placés dans les dossiers qui ne sont pas pertinents pour les requêtes. Si vos fichiers sont stockés dans une hiérarchie de dossiers (par exemple, /year=2020/month=03/day=16) et que les valeurs year, month et day sont exposées en tant que colonnes, les requêtes contenant des filtres tels que year=2020 lisent les fichiers uniquement à partir des sous-dossiers placés dans le dossier year=2020. Les fichiers et les dossiers placés dans d’autres dossiers (year=2021 ou year=2022) sont ignorés dans cette requête. Cette élimination est appelée élimination de partition.

L’élimination de partition de dossier est disponible dans les tables externes natives qui sont synchronisées à partir des pools Synapse Spark. Si vous avez partitionné le jeu de données et souhaitez tirer parti de l’élimination de partition avec les tables externes que vous créez, utilisez les vues partitionnées à la place des tables externes.

Élimination de fichiers

Certains formats de données tels que Parquet et Delta contiennent des statistiques de fichier pour chaque colonne (par exemple, des valeurs minimales/maximales pour chaque colonne). Les requêtes qui filtrent les données ne lisent pas les fichiers dans lesquelles les valeurs de colonne requises n’existent pas. La requête explorera d’abord les valeurs minimales/maximales pour les colonnes utilisées dans le prédicat de requête afin de rechercher les fichiers qui ne contiennent pas les données requises. Ces fichiers seront ignorés et éliminés du plan de requête. Cette technique également appelée pushdown de prédicat de filtre peut améliorer les performances de vos requêtes. Le pushdown de filtre est disponible dans les pools SQL serverless sur les formats Parquet et Delta. Pour tirer parti du pushdown de filtre pour les types de chaînes, utilisez le type VARCHAR avec le classement Latin1_General_100_BIN2_UTF8. Pour plus d’informations sur les classements, consultez Types de classements pris en charge pour Synapse SQL.

Sécurité

L’utilisateur doit disposer de l’autorisation SELECT sur une table externe pour lire les données. Les tables externes accèdent au stockage Azure sous-jacent à l’aide des informations d’identification étendues à la base de données définies dans la source de données selon les règles suivantes :

• La source de données sans informations d’identification permet aux tables externes d’accéder aux fichiers en disponibilité publique sur le stockage Azure.
• La source de données peut comporter des informations d’identification permettant aux tables externes d’accéder uniquement aux fichiers sur le stockage Azure à l’aide d’un jeton SAP ou de l’identité managée de l’espace de travail. Pour voir des exemples, consultez l’article Développement du contrôle d’accès au stockage des fichiers de stockage.

Exemple pour CREATE EXTERNAL DATA SOURCE

Hadoop

L’exemple suivant crée une source de données externe Hadoop dans un pool SQL dédié pour Azure Data Lake Gen2 pointant vers le jeu de données New York :

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

L’exemple suivant crée une source de données externe pour Azure Data Lake Gen2 pointant vers le jeu de données New York en disponibilité publique :

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

Natif

L’exemple suivant crée une source de données externe dans un pool SQL serverless ou dédié pour Azure Data Lake Gen2, accessible à l’aide des informations d’identification SAS :

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

Notes

Les utilisateurs SQL doivent disposer d’autorisations appropriées sur les informations d’identification étendues à la base de données pour accéder à la source de données dans le pool de SQL Serverless Azure Synapse Analytics. Accédez à un stockage externe en utilisant le pool SQL serverless dans Azure Synapse Analytics. L’exemple suivant crée une source de données externe pour Azure Data Lake Gen2 pointant vers le jeu de données New York en disponibilité publique :

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

Exemple pour CREATE EXTERNAL FILE FORMAT

L’exemple suivant crée un format de fichier externe pour des fichiers de recensement :

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

Exemple pour CREATE EXTERNAL TABLE

L’exemple suivant crée une table externe : Il retourne la première ligne :

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)
GO

SELECT TOP 1 * FROM census_external_table

Créer et interroger des tables externes à partir d’un fichier dans Azure Data Lake

Avec les fonctionnalités d’exploration de Data Lake offertes par Synapse Studio, vous pouvez désormais créer et interroger une table externe à l’aide d’un pool Synapse SQL avec un simple clic droit sur le fichier. Le clic simple qui permet de créer des tables externes à partir d’un compte de stockage ADLS Gen2 est possible uniquement pour les fichiers Parquet.

Prérequis

• Vous devez avoir accès à l’espace de travail en ayant au moins le rôle d’accès Storage Blob Data Contributor au compte ADLS Gen2 ou aux listes de contrôle d’accès (ACL) qui vous permettent d’interroger les fichiers.

• Vous devez disposer au moins d’autorisations de créer une table externe et d’interroger des tables externes sur le pool Synapse SQL (dédié ou serverless).

Dans le panneau Données, sélectionnez le fichier à partir duquel vous souhaitez créer la table externe :

Une fenêtre de dialogue s’ouvre. Sélectionnez un pool SQL dédié ou un pool SQL serverless, donnez un nom à la table et sélectionnez Ouvrir le script :

Le script SQL est généré automatiquement en déduisant le schéma à partir du fichier :

Exécutez le script. Le script exécutera automatiquement un Select Top 100 *. :

La table externe est maintenant créée. Pour toute exploration ultérieure du contenu de cette table externe, l’utilisateur peut l’interroger directement à partir du volet Données :

Étapes suivantes

Consultez l’article CETAS pour découvrir comment enregistrer des résultats de requête dans une table externe dans Stockage Azure. Vous pouvez sinon commencer à interroger des Tables externes Apache Spark pour Azure Synapse.