CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Article
08/04/2023

S’applique à : SQL Server 2022 (16.x) et version ultérieure du système de plateforme Azure Synapse Analytics Analytics (PDW)

Crée une table externe, puis exporte en parallèle les résultats d’une instruction Transact-SQL SELECT.

Azure Synapse Analytics et Analytics Platform System prennent en charge Hadoop ou le Stockage Blob Azure.
SQL Server 2022 (16.x) et versions ultérieures prennent en charge CREATE EXTERNAL TABLE AS SELECT (CETAS) pour créer une table externe, puis exporter en parallèle le résultat d’une instruction Transact-SQL SELECT vers Azure Data Lake Storage (ADLS) Gen2, un compte de stockage Azure V2 et un stockage d’objets compatible S3.

Notes

Les fonctionnalités et la sécurité de CETAS pour Azure SQL Managed Instance sont différentes de SQL Server ou d’Azure Synapse Analytics. Pour plus d’informations, consultez la version Azure SQL Managed Instance de CREATE EXTERNAL TABLE AS SELECT.

Notes

Les fonctionnalités et la sécurité de CETAS pour les pools serverless dans Azure Synapse Analytics sont différentes de SQL Server. Pour plus d’informations, consultez CETAS avec Synapse SQL.

Conventions de la syntaxe Transact-SQL

Syntaxe

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value
}

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

Arguments

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

Nom (composé d’une à trois parties) de la table à créer dans la base de données. Pour une table externe, la base de données relationnelle ne stocke que ses métadonnées.

[ ( column_name [ ,...n ] ) ]

Nom d’une colonne de table.

LOCATION

S’applique à : Azure Synapse Analytics et Analytics Platform System

'hdfs_folder'**
Spécifie l’emplacement dans lequel écrire les résultats de l’instruction SELECT exécutée sur la source de données externes. L’emplacement correspond à un nom de dossier et peut inclure un chemin relatif au dossier racine du cluster Hadoop ou du stockage blob Azure. PolyBase crée le chemin et le dossier s’ils n’existent pas déjà.

Les fichiers externes sont écrits dans hdfs_folder et sont nommés QueryID_date_time_ID.format, où ID est un identificateur incrémentiel et format est le format des données exportées. par exemple QID776_20160130_182739_0.orc.

LOCATION doit pointer vers un dossier et se terminer par /, par exemple aggregated_data/.

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

prefix://path[:port] fournit le protocole de connectivité (préfixe), le chemin et éventuellement le port pour la source de données externe, où le résultat de l’instruction SELECT est écrit.

Si la destination est un stockage d’objets compatible S3, un compartiment doit d’abord exister, mais PolyBase peut créer des sous-dossiers si nécessaire. SQL Server 2022 (16.x) prend en charge Azure Data Lake Storage Gen2, les comptes Stockage Azure V2 et le stockage d’objets compatible S3. Les fichiers ORC ne sont actuellement pas pris en charge.

DATA_SOURCE = external_data_source_name

Spécifie le nom de l’objet de source de données externe contenant l’emplacement dans lequel les données externes sont stockées ou vont être stockées. L’emplacement est soit un cluster Hadoop Cluster, soit un stockage blob Azure. Pour créer une source de données externe, utilisez CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Options REJECT

Les options REJECT ne s’appliquent pas au moment où l’instruction CREATE EXTERNAL TABLE AS SELECT est exécutée. Ici, les options sont spécifiées pour que la base de données puisse les utiliser ultérieurement, lors de l’importation des données à partir de la table externe. Plus tard, quand l’instruction CREATE TABLE AS SELECT sélectionnera des données dans la table externe, la base de données utilisera les options REJECT pour déterminer le nombre ou le pourcentage de lignes dont l’importation peut échouer avant que l’importation ne soit arrêtée.

REJECT_VALUE = reject_value

Spécifie la valeur ou le pourcentage de lignes dont l’importation peut échouer avant que l’importation ne soit arrêtée.
REJECT_TYPE = value | percentage

Indique si l’option REJECT_VALUE est une valeur littérale ou un pourcentage.
- value
  
  Utilisé si REJECT_VALUE est une valeur littérale, et non un pourcentage. La base de données cesse d’importer des lignes du fichier de données externe lorsque le nombre de lignes ayant échoué dépasse la valeur de reject_value.
  
  Par exemple, si REJECT_VALUE = 5 et REJECT_TYPE = value, la base de données cesse d’importer des lignes au bout de cinq lignes dont l’importation a échoué.
- percentage
  
  Utilisé si REJECT_VALUE est un pourcentage, et non une valeur littérale. La base de données cesse d’importer des lignes du fichier de données externe lorsque le pourcentage (percentage) de lignes ayant échoué dépasse la valeur de reject_value. Le pourcentage de lignes ayant échoué est calculé à intervalles. Valide uniquement dans les pools SQL dédiés lorsque TYPE=HADOOP.
REJECT_SAMPLE_VALUE = reject_sample_value

Nécessaire lorsque REJECT_TYPE = percentage. Spécifie le nombre de lignes qu’il faut tenter d’importer avant que la base de données ne recalcule le pourcentage de lignes ayant échoué.

Par exemple, si REJECT_SAMPLE_VALUE = 1000, la base de données calcule le pourcentage de lignes ayant échoué après avoir tenté d’importer 1 000 lignes à partir du fichier de données externe. Si le pourcentage de lignes ayant échoué est inférieur à la valeur de reject_value, la base de données tente de charger 1 000 autres lignes. La base de données continue de recalculer le pourcentage de lignes ayant échoué après avoir tenté d’importer chacune des 1 000 lignes supplémentaires.

Notes

Comme la base de données calcule le pourcentage de lignes ayant échoué à intervalles, le pourcentage de lignes ayant échoué peut dépasser la valeur de reject_value.

Exemple :

Cet exemple montre comment les trois options REJECT interagissent les unes avec les autres. Par exemple, si REJECT_TYPE = percentage, REJECT_VALUE = 30, REJECT_SAMPLE_VALUE = 100, le scénario suivant peut se produire :
- La base de données tente de charger les 100 premières lignes, dont 25 ne sont pas importées et 75 sont importées.
- Le pourcentage de lignes ayant échoué qui est obtenu est de 25 %, ce qui est inférieur à la valeur de rejet de 30 %. Par conséquent,il n’est pas nécessaire de stopper le chargement.
- La base de données tente de charger les 100 lignes suivantes. Cette fois-ci, 25 sont importées et 75 ne sont pas importées.
- Le pourcentage de lignes ayant échoué est recalculé et on obtient 50 %. Le pourcentage de lignes ayant échoué a donc dépassé la valeur de rejet de 30 %.
- Le chargement échoue après la tentative d’importation de 200 lignes et l’échec de 50 % d’entre elles, ce qui est supérieur à la limite de 30 % spécifiée.

WITH common_table_expression

Spécifie un jeu de résultats nommé temporaire, désigné par le terme d'expression de table commune (CTE, Common Table Expression). Pour plus d’informations, consultez WITH common_table_expression (Transact-SQL).

SELECT <select_criteria>

Remplit la nouvelle table avec les résultats d’une instruction SELECT. select_criteria correspond au corps de l’instruction SELECT qui détermine les données qui sont copiées dans la nouvelle table. Pour plus d’informations sur les instructions SELECT, consultez SELECT (Transact-SQL).

Notes

La clause ORDER BY dans SELECT n’a aucun effet sur CETAS.

Options de colonne

column_name [ ,...n ]

Les noms de colonne n’autorisent pas les options de colonne mentionnées dans CREATE TABLE. À la place, vous pouvez fournir une liste facultative d’un ou plusieurs noms de colonne pour la nouvelle table. Les colonnes de la nouvelle table utilisent les noms que vous spécifiez. Quand vous spécifiez des noms de colonne, le nombre de colonnes figurant dans la liste de colonnes doit correspondre au nombre de colonnes figurant dans les résultats de l’instruction select. Si vous ne spécifiez pas de noms de colonnes, la nouvelle table cible utilise ceux qui figurent dans les résultats de l’instruction SELECT.

Vous ne pouvez spécifier aucune autre option de colonne comme les types de données, le classement ou la possibilité de valeur NULL. Chacun de ces attributs est dérivé des résultats de l’instruction SELECT. Cependant, vous pouvez utiliser l’instruction SELECT pour modifier les attributs. Pour obtenir un exemple, consultez Utiliser CETAS pour modifier des attributs de colonne.

Autorisations

Pour exécuter cette commande, les utilisateurs de la base de données ont besoin des autorisations ou appartenances suivantes :

Autorisation ALTER SCHEMA pour le schéma local devant contenir la nouvelle table ou appartenance au rôle de base de données fixe db_ddladmin.
Autorisation CREATE TABLE ou appartenance au rôle de base de données fixe db_ddladmin
Autorisation SELECT pour les objets référencés dans select_criteria.

La connexion a besoin de toutes ces autorisations :

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
En général, vous devez disposer d’autorisations permettant de lister le contenu des dossiers et d’écrire dans le dossier LOCATION pour CETAS.
Dans Azure Synapse Analytics et Analytics Platform System, l’autorisation d’écriture est nécessaire pour lire et écrire dans le dossier externe du cluster Hadoop ou du Stockage Azure Blob.
Dans SQL Server 2022 (16.x), il est également nécessaire de définir des autorisations appropriées sur l’emplacement externe. L’autorisation d’écriture est nécessaire pour générer les données à l’emplacement LOCATION, et l’autorisation de lecture pour y accéder.
Pour Stockage Blob Azure et Azure Data Lake Gen2, le jeton SHARED ACCESS SIGNATURE doit disposer des privilèges suivants sur le conteneur : Lecture, Écriture, Liste, Création.
Pour le Stockage Blob Azure, la case Allowed Services : Blob doit être cochée pour générer le jeton SAS.
Pour Azure Data Lake Gen2, les cases Allowed Services : Container et Object doivent être cochées pour générer le jeton SAS.

Important

L’autorisation ALTER ANY EXTERNAL DATA SOURCE accorde à n’importe quel principal la possibilité de créer et de modifier tout objet de source de données externe. Par conséquent, elle permet également d’accéder à toutes les informations d’identification délimitées à la base de données. Cette autorisation doit être considérée comme fournissant des privilèges très élevés et doit être accordée uniquement aux principaux de confiance du système.

Gestion des erreurs

Lorsque CREATE EXTERNAL TABLE AS SELECT exporte des données vers un fichier texte délimité, aucun fichier de rejet n’est disponible pour les lignes dont l’exportation échoue.

Lorsque vous créez la table externe, la base de données tente de se connecter à l’emplacement externe. En cas d’échec de la connexion, la commande échoue et la table externe n’est pas créée. L’échec de la commande peut prendre plusieurs minutes, car la base de données tente de se connecter au moins trois fois.

Si CREATE EXTERNAL TABLE AS SELECT est annulé ou échoue, la base de données effectue une tentative de suppression des nouveaux fichiers et des dossiers déjà créés dans la source de données externe.

Dans Azure Synapse Analytics et Analytics Platform System, la base de données signale les erreurs Java qui se produisent sur la source de données externe pendant l’exportation de données.

Notes

Une fois l’instruction CREATE EXTERNAL TABLE AS SELECT terminée, vous pouvez exécuter des requêtes Transact-SQL sur la table externe. Ces opérations importent des données dans la base de données pendant toute la durée de la requête, à moins que les données ne soient importées à l’aide de l’instruction CREATE TABLE AS SELECT.

Le nom et la définition de la table externe sont stockés dans les métadonnées de la base de données. Les données sont stockées dans la source de données externe.

L’instruction CREATE EXTERNAL TABLE AS SELECT crée toujours une table non partitionnée, même si la table source est partitionnée.

Pour SQL Server 2022 (16.x), l’option allow polybase export doit être activée à l’aide de sp_configure. Pour plus d’informations, consultez Définir allow polybase export l’option de configuration.

Pour les plans de requête dans Azure Synapse Analytics et Analytics Platform System, créé avec EXPLAIN, la base de données utilise ces opérations de plan de requête pour les tables externes : déplacement aléatoire externe, déplacement de diffusion externe, déplacement de partition externe.

Dans Analytics Platform System, l’un des prérequis à la création d’une table externe est que la connectivité Hadoop doit être configurée par l’administrateur de l’appliance. Pour plus d’informations, consultez « Configurer la connectivité aux données externes (Analytics Platform System) » dans la documentation Analytics Platform System, que vous pouvez télécharger dans le centre de téléchargement Microsoft.

Limitations et restrictions

Comme les données de la table externe sont situées en dehors de la base de données, les opérations de sauvegarde et de restauration fonctionnent uniquement sur les données stockées dans la base de données. De ce fait, seules les métadonnées sont sauvegardées et restaurées.

La base de données ne vérifie pas la connexion à la source de données externe lorsque vous restaurez une sauvegarde de base de données contenant une table externe. Si la source d’origine n’est pas accessible, la restauration de métadonnées de la table externe est quand même effectuée, mais les opérations SELECT sur la table externe échouent.

La base de données ne garantit pas la cohérence des données entre la base de données et les données externes. Vous seul êtes responsable de maintenir la cohérence entre les données externes et la base de données.

Les opérations DML ne sont pas prises en charge avec les tables externes. Par exemple, vous ne pouvez pas utiliser les instructions Transact-SQL update, insert ou delete pour modifier les données externes.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW et DROP VIEW sont les seules opérations DDL qui sont autorisées avec les tables externes.

Limitations et restrictions pour Azure Synapse Analytics

Dans les pools SQL dédiés Azure Synapse Analytics et Analytics Platform System, PolyBase peut consommer un maximum de 33 000 fichiers par dossier lors de l’exécution de 32 requêtes PolyBase simultanées. Ce nombre maximal inclut les fichiers et les sous-dossiers de chaque dossier HDFS. Si le degré de concurrence est inférieur à 32, un utilisateur peut exécuter des requêtes PolyBase sur des dossiers dans des systèmes HDFS contenant plus de 33 000 fichiers. Nous recommandons aux utilisateurs de Hadoop et PolyBase de raccourcir au maximum les chemins de fichiers et de ne pas utiliser plus de 30 000 fichiers par dossier HDFS. Lorsque trop de fichiers sont référencés, une exception d’insuffisance de mémoire JVM est levée.
Dans les pools SQL serverless, les tables externes ne peuvent pas être créées à un emplacement où figurent actuellement des données. Pour réutiliser un emplacement qui a été utilisé pour stocker des données, l’emplacement doit être supprimé manuellement sur ADLS. Pour plus d’informations sur les limitations et les meilleures pratiques, consultez Meilleures pratiques en matière d’optimisation des filtres.

Dans les pools SQL dédiés Azure Synapse Analytics et Analytics Platform System, lorsque CREATE EXTERNAL TABLE AS SELECT sélectionne des données à partir d’un RCFile, les valeurs de colonne du RCFile ne doivent pas contenir le caractère barre verticale (|).

SET ROWCOUNT (Transact-SQL) n’a aucun effet sur CREATE EXTERNAL TABLE AS SELECT. Pour obtenir un comportement similaire, utilisez TOP (Transact-SQL).

Pour connaître les limitations des noms de fichier, consultez Nommage et référence des conteneurs, blobs et métadonnées.

Erreurs de caractère

Les caractères suivants présents dans les données peuvent entraîner des erreurs, notamment des enregistrements rejetés avec CREATE EXTERNAL TABLE AS SELECT sur des fichiers Parquet.

Dans Azure Synapse Analytics et Analytics Platform System, cela s’applique également aux fichiers ORC.

|
" (guillemet)
\r\n
\r
\n

Pour utiliser CREATE EXTERNAL TABLE AS SELECT avec ces caractères, vous devez d’abord exécuter l’instruction CREATE EXTERNAL TABLE AS SELECT pour exporter les données vers des fichiers texte délimités, avant de les convertir en fichiers Parquet ou ORC à l’aide d’un outil externe.

Utilisation de parquet

Lorsque vous utilisez des fichiers parquet, CREATE EXTERNAL TABLE AS SELECT génère un fichier parquet par processeur disponible, jusqu’au degré maximal configuré de parallélisme (MAXDOP). Chaque fichier peut atteindre 190 Go, après quoi SQL Server génère d’autres fichiers Parquet en fonction des besoins.

L’indicateur de requête OPTION (MAXDOP n) n’affecte que la partie SELECT de CREATE EXTERNAL TABLE AS SELECT, il n’a pas d’influence sur la quantité de fichiers parquet. Seul MAXDOP au niveau de la base de données et MAXDOP au niveau de l’instance est pris en compte.

Verrouillage

Applique un verrou partagé sur l’objet SCHEMARESOLUTION.

Types de données prises en charge

CETAS peut être utilisé pour stocker les jeux de résultats avec les types de données SQL suivants :

binary
varbinary
char
varchar
nchar
NVARCHAR
smalldate
Date
DATETIME
datetime2
datetimeoffset
time
Décimal
numeric
float
real
bigint
TINYINT
SMALLINT
int
bigint
bit
money
smallmoney

Exemples

R. Créer une table Hadoop à l’aide de CREATE EXTERNAL TABLE AS SELECT

S’applique à : Azure Synapse Analytics et Analytics Platform System

L’exemple suivant crée une nouvelle table externe nommée hdfsCustomer qui utilise les définitions de colonne et les données de la table source dimCustomer.

La définition de la table est stockée dans la base de données, et les résultats de l’instruction SELECT sont exportés dans le fichier /pdwdata/customer.tbl au sein de la source de données externe Hadoop customer_ds. Le fichier est mis en forme selon le format de fichier externe customer_ff.

Le nom de fichier est généré par la base de données et contient l’ID de requête pour faciliter l’alignement du fichier sur la requête qui l’a généré.

Le chemin hdfs://xxx.xxx.xxx.xxx:5000/files/ qui précède le répertoire Client doit déjà exister. Si le répertoire Customer n’existe pas, la base de données le crée.

Notes

Cet exemple spécifie 5000. Si le port n’est pas spécifié, la base de données utilise le port 8020 comme port par défaut.

L’emplacement et le nom de fichier Hadoop résultants seront : hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt..

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. Utiliser un indicateur de requête avec CREATE EXTERNAL TABLE AS SELECT

S’applique à : Azure Synapse Analytics et Analytics Platform System

Cette requête présente la syntaxe de base pour utiliser un indicateur de jointure de requête avec l’instruction CREATE EXTERNAL TABLE AS SELECT. Une fois la requête envoyée, la base de données utilise la stratégie de jointure hachée pour générer le plan de requête. Pour plus d’informations sur les indicateurs de jointure et la clause OPTION, voir Clause OPTION (Transact-SQL).

Notes

Cet exemple spécifie 5000. Si le port n’est pas spécifié, la base de données utilise le port 8020 comme port par défaut.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. Utiliser CETAS pour modifier des attributs de colonne

S’applique à : Azure Synapse Analytics et Analytics Platform System

Cet exemple utilise CETAS pour modifier des types de données, la possibilité de valeur NULL et le classement pour plusieurs colonnes dans la table FactInternetSales.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. Utiliser CREATE EXTERNAL TABLE AS SELECT exportant des données en tant que parquet

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

L'exemple suivant crée une nouvelle table externe nommée ext_sales, qui utilise les données de la table SalesOrderDetail sur AdventureWorks2022. L’option de configuration allow polybase export doit être activée.

Le résultat de l’instruction SELECT est enregistré sur un stockage d’objets compatible S3 précédemment configuré et nommé s3_eds, et les informations d’identification appropriées sont créées en tant que s3_dsc. L’emplacement du fichier Parquet sera <ip>:<port>/cetas/sales.parquet l’emplacement où cetas est le compartiment de stockage créé précédemment.

Notes

Le format delta est actuellement pris en charge uniquement en lecture seule.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. Utiliser CREATE EXTERNAL TABLE AS SELECT de la table delta vers Parquet

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

L’exemple suivant crée une table externe nommée Delta_to_Parquet, qui utilise le type de données de table delta situé dans un stockage d’objets compatible S3 nommé s3_delta et écrit le résultat dans une autre source de données nommée s3_parquet en tant que fichier Parquet. Pour cela, l’exemple utilise la commande OPENROWSET. L’option de configuration allow polybase export doit être activée.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. Utiliser CREATE EXTERNAL TABLE AS SELECT avec une vue comme source

S’applique à : pools SQL serverless Azure Synapse Analytics et pools SQL dédiés.

Dans cet exemple, nous pouvons voir un exemple de code de modèle pour l’écriture de CETAS avec une vue définie par l’utilisateur comme source, à l’aide de l’identité managée comme authentification et wasbs:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. Utiliser CREATE EXTERNAL TABLE AS SELECT avec une vue comme source

S’applique à : pools SQL serverless Azure Synapse Analytics et pools SQL dédiés.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Étapes suivantes

S’applique à : Azure SQL Managed Instance

Crée une table externe, puis exporte en parallèle les résultats d’une instruction Transact-SQL SELECT.

Vous pouvez utiliser CREATE EXTERNAL TABLE AS SELECT (CETAS) pour effectuer les tâches suivantes :

Créer une table externe par-dessus des fichiers Parquet ou CSV dans le Stockage Blob Azure ou Azure Data Lake Storage (ADLS) Gen2.
Exporter en parallèle les résultats d’une instruction SELECT T-SQL dans la table externe créée.
Pour plus d’informations sur les fonctionnalités de virtualisation de données d’Azure SQL Managed Instance, consultez Virtualisation des données avec Azure SQL Managed Instance.

Notes

Ce contenu s’applique à Azure SQL Managed Instance uniquement. Pour les autres plateformes, choisissez la version appropriée de CREATE EXTERNAL TABLE AS SELECT dans le menu déroulant de sélection.

Conventions de la syntaxe Transact-SQL

Syntaxe

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

Arguments

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

Nom (composé d’une à trois parties) de la table à créer. Pour une table externe, seules les métadonnées de la table sont stockées. Aucune donnée réelle n’est déplacée ni stockée.

LOCATION = 'path_to_folder'

Spécifie l’emplacement dans lequel écrire les résultats de l’instruction SELECT exécutée sur la source de données externes. Le dossier racine est l’emplacement de données qui est spécifié dans la source de données externe. LOCATION doit pointer vers un dossier et se terminer par /. Exemple : aggregated_data/.

Le dossier de destination de CETAS doit être vide. Si le chemin et le dossier n’existent pas, ils sont créés automatiquement.

DATA_SOURCE = external_data_source_name

Spécifie le nom de l’objet de source de données externe qui contient l’emplacement où les données externes vont être stockées. Pour créer une source de données externe, utilisez CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Spécifie le nom de l’objet de format de fichier externe qui contient le format du fichier de données externe. Pour créer un format de fichier externe, utilisez CREATE EXTERNAL FILE FORMAT (Transact-SQL). Seuls les formats de fichiers externes avec FORMAT_TYPE=PARQUET et FORMAT_TYPE=DELIMITEDTEXT sont actuellement pris en charge. La compression GZip pour le format DELIMITEDTEXT n’est pas prise en charge.

[, PARTITION ( column name [ , ...n ] ) ]

Partitionne les données de sortie en plusieurs chemins de fichiers Parquet. Le partitionnement se produit en fonction des colonnes spécifiées (column_name), en faisant correspondre les caractères génériques (*) de LOCATION à la colonne de partitionnement respective. Le nombre de colonnes dans la partie PARTITION doit correspondre au nombre de caractères génériques dans LOCATION. Une colonne au moins ne doit pas être utilisée pour le partitionnement.

WITH <common_table_expression>

SELECT <select_criteria>

Notes

La clause ORDER BY de SELECT n'est pas prise en charge pour CETAS.

Autorisations

Autorisations dans le stockage

Pour que CETAS fonctionne, vous devez avoir l’autorisation de lister le contenu des dossiers et d’écrire dans le chemin LOCATION.

Les méthodes d’authentification prises en charge sont une identité managée ou un jeton SAP (Shared Access Signature).

Si vous utilisez l’identité managée pour l’authentification, assurez-vous que le principal de service de votre instance managée SQL a un rôle de contributeur aux données Blob de stockage sur le conteneur de destination.
Si vous utilisez un jeton SAS, les autorisations Lecture, Écriture et Liste sont nécessaires.
Pour le Stockage Blob Azure, la case Allowed Services : Blob doit être cochée pour générer le jeton SAS.
Pour Azure Data Lake Gen2, les cases Allowed Services : Container et Object doivent être cochées pour générer le jeton SAS.

Une identité managée affectée par l’utilisateur n’est pas prise en charge. L’authentification directe Microsoft Entra n’est pas prise en charge. L’ID Microsoft Entra est (anciennement Azure Active Directory).

Autorisations dans l’instance managée SQL

Pour exécuter cette commande, les utilisateurs de la base de données ont besoin des autorisations ou appartenances suivantes :

Autorisation ALTER SCHEMA pour le schéma local devant contenir la nouvelle table ou appartenance au rôle de base de données fixe db_ddladmin.
Autorisation CREATE TABLE ou appartenance au rôle de base de données fixe db_ddladmin
Autorisation SELECT pour les objets référencés dans select_criteria.

La connexion a besoin de toutes ces autorisations :

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT

Important

Types de données pris en charge

CETAS stocke les jeux de résultats avec les types de données SQL suivants :

binary
varbinary
char
varchar
nchar
NVARCHAR
smalldatetime
Date
DATETIME
datetime2
datetimeoffset
time
Décimal
numeric
float
real
bigint
TINYINT
SMALLINT
int
bigint
bit
money
smallmoney

Remarque

Les LOB supérieurs à 1 Mo ne peuvent pas être utilisés avec CETAS.

Limitations et restrictions

CREATE EXTERNAL TABLE AS SELECT (CETAS) pour Azure SQL Managed Instance est désactivé par défaut. Pour plus d’informations, consultez la section suivante, Désactivé par défaut.
Pour plus d’informations sur les limitations ou les problèmes connus liés à la virtualisation des données dans Azure SQL Managed Instance, consultez Limitations et problèmes connus.

Les opérations DML ne sont pas prises en charge avec les tables externes. Par exemple, vous ne pouvez pas utiliser les instructions Transact-SQL UPDATE, INSERT ou DELETE pour modifier les données externes.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW et DROP VIEW sont les seules opérations DDL qui sont autorisées avec les tables externes.

Les tables externes ne peuvent pas être créées à un emplacement où figurent actuellement des données. Pour réutiliser un emplacement qui a été utilisé pour stocker des données, l’emplacement doit être supprimé manuellement sur ADLS.

SET ROWCOUNT (Transact-SQL) n’a aucun effet sur CREATE EXTERNAL TABLE AS SELECT. Pour obtenir un comportement similaire, utilisez TOP (Transact-SQL).

Pour connaître les limitations des noms de fichier, consultez Nommage et référence des conteneurs, blobs et métadonnées.

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

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

Important

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. L’utilisation du préfixe générique https:// est désactivée. Utilisez toujours des préfixes spécifiques au point de terminaison.

Désactivée par défaut

Comme CREATE EXTERNAL TABLE AS SELECT (CETAS) vous permet d’exporter des données de votre instance managée SQL vers un compte de stockage externe, il y a un risque d’exfiltration des données avec ces fonctionnalités. Par conséquent, CETAS est désactivé par défaut pour Azure SQL Managed Instance.

Activer CETAS

CETAS pour Azure SQL Managed Instance peut uniquement être activé avec une méthode qui nécessite des autorisations Azure élevées, et ne peut pas être activé avec T-SQL. En raison du risque d’exfiltration de données non autorisées, CETAS ne peut pas être activé avec la procédure stockée T-SQL sp_configure, mais demande une action de l’utilisateur en dehors de l’instance managée SQL.

Autorisations pour activer CETAS

Pour activer via Azure PowerShell, votre utilisateur exécutant la commande doit avoir des rôles Contributeur ou Sql Security Manager Azure RBAC pour votre instance managée SQL.

Un rôle personnalisé peut également être créé pour cela, nécessitant l’action Lire et Écrire pour l’action Microsoft.Sql/managedInstances/serverConfigurationOptions .

Méthodes d’activation de CETAS

Pour appeler les commandes PowerShell sur un ordinateur, vous devez installer localement le package Az version 9.7.0 ou version ultérieure. Vous pouvez également utiliser Azure Cloud Shell pour exécuter Azure PowerShell sur shell.azure.com.

Tout d’abord, connectez-vous à Azure et définissez le contexte approprié pour votre abonnement :

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

Pour gérer l’option de configuration de serveur « allowPolybaseExport », ajustez les scripts PowerShell suivants en fonction de votre abonnement et du nom de votre instance managée SQL, puis exécutez les commandes. Pour plus d’informations, consultez Set-AzSqlServerConfigurationOption et Get-AzSqlServerConfigurationOption.

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

Pour désactiver l’option de configuration de serveur « allowPolybaseExport » :

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

Pour obtenir la valeur actuelle de l’option de configuration de serveur « allowPolybaseExport » :

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

Vous pouvez utiliser Azure CLI pour gérer l’option de configuration de serveur « allowPolybaseExport ».

Commencez par ouvrir une invite de commandes Windows (CMD) ou PowerShell, et connectez-vous à Azure. Si nécessaire, basculez vers votre abonnement.

az login
$subscriptionId = "<your subscription ID>"
az account set --subscription $subscriptionId

Utilisez az sql mi server-configuration-option set pour activer l’option de configuration de serveur nommée « allowPolybaseExport » :

# Enable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '1'

Pour désactiver l’option de configuration de serveur « allowPolybaseExport » :

# Disable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '0'

Pour obtenir la valeur actuelle de l’option de configuration de serveur « allowPolybaseExport » :

# Get server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option show --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport'

Alias d’arguments Azure CLI :

Version courte	Alias
-g	--resource-group
--mi	--instance-name --managed-instance --managed-instance-name
-n	--name --server-configuration-option-name
--value	--server-configuration-option-value

Vous pouvez appeler une commande d’API générique dans Azure Cloud Shell pour basculer l’option de configuration de serveur allowPolybaseExport vers 1.

Pour activer CETAS sur votre instance managée SQL, ajustez les variables de script Azure PowerShell suivantes en fonction de votre abonnement et du nom de l’instance managée SQL, puis exécutez-le dans votre instance Azure Cloud Shell.

Notes

Pour les étapes Azure Cloud Shell suivantes de configuration ou de vérification de CETAS, utilisez la même console PowerShell. Les scripts exécutés par la suite qui vérifient l’état dépendent du contexte défini par le script d’origine.

# Enter your Azure subscription ID
$SubscriptionID = "<YourSubscriptionIDHERE>"

# Enter your managed instance name – for example, "sqlmi1"
$ManagedInstanceName = "<YourManagedInstanceNameHere>"

# ===============================================================================
# INVOKING THE API CALL -- REST OF THE SCRIPT IS NOT USER CONFIGURABLE
# ===============================================================================
# Log in and select a subscription if needed.

if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID

# Build the URI for the API call.
$resource_group_name = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
$uriFull = "https://management.azure.com/subscriptions/" + $SubscriptionID + "/resourceGroups/" + $resource_group_name+ "/providers/Microsoft.Sql/managedInstances/" + $ManagedInstanceName + "/serverConfigurationOptions/allowPolybaseExport" + "?api-version=2022-08-01-preview"

# Build the API request body.
$properties = @{serverConfigurationOptionValue = 1}
$bodyFull = @{properties = $properties}
$jsonBody = $bodyFull | ConvertTo-Json

# Get auth token and build the HTTP request header.
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile)
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId)
$authToken = $token.AccessToken
$headers = @{}
$headers.Add("Authorization", "Bearer "+"$authToken")

# Invoke API call. API call creates an Asynchronous operation that will change the configuration setting. 
# In rare cases that your SQL MI is down when the async operation will run, the async operation may fail.
# You can check the status of the Async operation using the callback URL in the response header.
# Look for response header parameter: Azure-AsyncOperation.
Invoke-WebRequest -Method PUT -Headers $headers -Uri $uriFull -ContentType "application/json" -Body $jsonBody

Notes

La définition de l’option de configuration de serveur en appelant l’API est une opération asynchrone. L’opération asynchrone s’exécute généralement rapidement, en quelques secondes, mais elle peut aussi échouer pour différentes raisons, par exemple, si votre instance managée SQL est inactive au moment de l’exécution de l’opération. Vous devez donc bien vérifier le résultat du changement de votre option de configuration.

Pour vérifier le résultat de l’opération asynchrone de l’appel d’API, vous avez deux méthodes :

Important

Quand vous exécutez les scripts suivants, utilisez la même console Azure Cloud Shell que celle où vous avez exécuté le script pour définir l’option de configuration. Ce script dépend du contexte défini par le script d’origine Invoke-WebRequest.

Utilisez PowerShell pour appeler HTTP GET sur l’API de configuration de serveur et vérifiez que l’option de configuration contient la valeur souhaitée. Le script PowerShell suivant montre comment appeler une demande GET pour vérifier l’option de configuration de serveur. Recherchez la réponse JSON ainsi que serverConfigurationOptionValue pour vérifier que ça correspond à ce que vous avez défini à l’étape précédente. Utilisez la même console PowerShell que celle où vous avez exécuté le script pour définir l’option de configuration, car le script dépend du contexte défini par le script d’origine.
```
# You can query the configuration option directly to see if it contains the new changed value.
# Get the configuration status using the call below. This will return JSON with 
# serverConfigurationOptionValue variable value. 
# It should match what you provided in your original request:
Invoke-WebRequest -Method GET -Headers $headers -Uri $uriFull -ContentType "application/json" 
```

Utilisez PowerShell pour appeler HTTP GET sur l’URL de rappel pour l’opération asynchrone, et lisez la réponse JSON en recherchant la variable « status » et sa valeur. Quand vous appelez l’API pour faire le changement de configuration du serveur, l’en-tête de réponse contient une URL de rappel que vous pouvez utiliser pour vérifier le résultat et l’état de l’opération asynchrone. La valeur d’en-tête Azure-AsyncOperation contient l’URL de rappel.

Voici un exemple d’en-tête de réponse :

[Azure-AsyncOperation, https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview]

L’exemple PowerShell suivant utilise l’URL de rappel de l’en-tête de réponse, qui commence par https:// :

# If you got the Azure-AsyncOperation URL from response header, 
# use the below to query the operation for status:
$AzAsyncOpURL = "https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview"
Invoke-WebRequest -Method GET -Headers $headers -Uri $AzAsyncOpURL -ContentType "application/json"

Vérifier l’état de CETAS

À tout moment, vous pouvez vérifier l’état actuel de l’option de configuration CETAS.

Connectez-vous à l’instance managée SQL. Exécutez l’instruction T-SQL suivante et observez la colonne value de la réponse. Une fois le changement de configuration de serveur effectué, les résultats de cette requête doivent correspondre au paramètre souhaité.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

Dépanner

Pour connaître les étapes de résolution des problèmes de virtualisation de données dans Azure SQL Managed Instance, consultez Résoudre les problèmes. La section suivante décrit la gestion des erreurs et les messages d’erreur courants pour CETAS dans Azure SQL Managed Instance.

Gestion des erreurs

Lorsque CREATE EXTERNAL TABLE AS SELECT exporte des données vers un fichier texte délimité, aucun fichier de rejet n’est disponible pour les lignes dont l’exportation échoue.

Lorsque vous créez la table externe, la base de données tente de se connecter à l’emplacement externe. En cas d’échec de la connexion, la commande échoue, et la table externe n’est pas créée. L’échec de la commande peut prendre plusieurs minutes, car la base de données tente de se connecter au moins trois fois.

Messages d’erreur courants

Ces messages d’erreur courants ont des explications rapides concernant CETAS pour Azure SQL Managed Instance.

Spécification d’un emplacement déjà existant dans le stockage.

Solution : effacez l’emplacement de stockage (y compris l’instantané) ou changez le paramètre d’emplacement dans la requête.

Exemple de message d’erreur : Msg 15842: Cannot create external table. External table location already exists.
Valeurs de colonne mises en forme dans des objets JSON.

Solution : convertissez la colonne de valeurs en colonne VARCHAR ou NVARCHAR, ou en ensemble de colonnes avec des types définis explicitement.

Exemple de message d’erreur : Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.
Paramètre d’emplacement non valide (par exemple, plusieurs //).

Solution : corrigez le paramètre d’emplacement.

Exemple de message d’erreur : Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.
Une des options obligatoires est manquante (DATA_SOURCE, FILE_FORMAT, LOCATION).

Solution : ajoutez le paramètre manquant à la requête CETAS.

Exemple de message d’erreur : Msg 46505: Missing required external DDL option 'FILE_FORMAT'
Problèmes d’accès (informations d’identification non valides, informations d’identification expirées ou informations d’identification avec autorisations insuffisantes). Une autre possibilité est un chemin non valide, où l’instance managée SQL a reçu une erreur 404 du stockage.

Solution : vérifiez la validité et les autorisations des informations d’identification. Vous pouvez également vérifier que le chemin d’accès est valide et que le stockage existe. Utilisez le chemind’URL adls://<container>@<storage_account>.blob.core.windows.net/<path>/.

Exemple de message d’erreur : Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'
La partie LOCATION de DATA_SOURCE contient des caractères génériques.

Solution : supprimez les caractères génériques de l’emplacement.

Exemple de message d’erreur : Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.
Le nombre de caractères génériques dans le paramètre LOCATION et le nombre de colonnes partitionnée ne correspondent pas.

Solution : veillez à ce qu’il y ait autant de caractères génériques dans LOCATION que de colonnes de partition.

Exemple de message d’erreur : Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.
Le nom de colonne dans la clause PARTITION ne correspond à aucune colonne de la liste.

Solution : vérifiez que les colonnes dans PARTITION sont valides.

Exemple de message d’erreur : Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list
Colonne spécifiée plusieurs fois dans la liste PARTITION.

Solution : vérifiez que les colonnes de la clause PARTITION sont uniques.

Exemple de message d’erreur : Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.
La colonne a été spécifiée plusieurs fois dans la liste PARTITION ou elle ne correspond à aucune colonne de la liste SELECT.

Solution : vérifiez qu’il n’y a pas de doublons dans la liste de partitions et que les colonnes de partition existent dans la partie SELECT.

Exemple de message d’erreur : Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter. ou Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.
Utilisation de toutes les colonnes de la liste PARTITION.

Solution : une des colonnes au moins de la partie SELECT ne doit pas se trouver dans la partie PARTITION de la requête.

Exemple de message d’erreur : Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.
La fonctionnalité est désactivée.

Solution : activez la fonctionnalité en utilisant la section Désactivé par défaut de cet article.

Exemple de message d’erreur : Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

Verrouillage

Applique un verrou partagé sur l’objet SCHEMARESOLUTION.

Exemples

R. Utiliser CETAS avec une vue pour créer une table externe en utilisant l’identité managée

Cet exemple fournit du code permettant d’écrire CETAS avec une vue en tant que source, à l’aide de l’identité managée système une authentification.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

B. Utiliser CETAS avec une vue pour créer une table externe avec l’authentification SAS

Cet exemple fournit du code pour écrire CETAS avec une vue comme source, en utilisant un jeton SAS pour l’authentification.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';

CREATE DATABASE SCOPED CREDENTIAL SAS_token
WITH
  IDENTITY = 'SHARED ACCESS SIGNATURE',
  -- Remove ? from the beginning of the SAS token
  SECRET = '<azure_shared_access_signature>' ;
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

C. Créer une table externe dans un seul fichier Parquet dans le stockage

Les deux exemples suivants montrent comment décharger certaines données d’une table locale dans une table externe stockée sous forme de fichier(s) Parquet dans un conteneur de stockage Blob Azure. Ils sont conçus pour fonctionner avec la base de données AdventureWorks2022. Cet exemple montre comment créer une table externe sous forme de fichier Parquet, où l’exemple d’après montre comment créer une table externe et la partitionner dans plusieurs dossiers avec des fichiers Parquet.

L’exemple ci-dessous fonctionne à l’aide de l’identité managée pour l’authentification. Par conséquent, vérifiez que votre principal de service Azure SQL Managed Instance a le rôle Contributeur aux données blob du stockage sur votre conteneur de stockage blob Azure. Vous pouvez également modifier l’exemple et utiliser des jetons de signature d’accès partagé (SAS) pour l’authentification.

Dans l’exemple suivant, vous créez une table externe dans un seul fichier Parquet dans le Stockage Blob Azure, en sélectionnant dans la table SalesOrderHeader des commandes antérieures au 1er janvier 2014 :

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D. Créer une table externe partitionnée dans plusieurs fichiers Parquet stockés dans une arborescence de dossiers

Cet exemple s’appuie sur l’exemple précédent pour montrer comment créer une table externe et la partitionner dans plusieurs dossiers avec des fichiers Parquet. Vous pouvez utiliser des tables partitionnée pour améliorer les performances si votre jeu de données est grand.

Créez une table externe à partir des données SalesOrderHeader, en suivant les étapes de l’exemple B, mais partitionnez la table externe par année et par mois de OrderDate. Pendant l’interrogation des tables externes partitionnées, nous pouvons éliminer des partitions pour améliorer les performances.

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

Partager via

CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Syntaxe

Arguments

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

[ ( column_name [ ,...n ] ) ]

LOCATION

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

Options REJECT

WITH common_table_expression

SELECT <select_criteria>

Autorisations

Gestion des erreurs

Notes

Limitations et restrictions

Limitations et restrictions pour Azure Synapse Analytics

Erreurs de caractère

Utilisation de parquet

Verrouillage

Types de données prises en charge

Exemples

R. Créer une table Hadoop à l’aide de CREATE EXTERNAL TABLE AS SELECT

B. Utiliser un indicateur de requête avec CREATE EXTERNAL TABLE AS SELECT

C. Utiliser CETAS pour modifier des attributs de colonne

D. Utiliser CREATE EXTERNAL TABLE AS SELECT exportant des données en tant que parquet

E. Utiliser CREATE EXTERNAL TABLE AS SELECT de la table delta vers Parquet

F. Utiliser CREATE EXTERNAL TABLE AS SELECT avec une vue comme source

G. Utiliser CREATE EXTERNAL TABLE AS SELECT avec une vue comme source

Étapes suivantes

Syntaxe

Arguments

[ [ database_name . [ schema_name ] . ] | schema_name . ] table_name

LOCATION = 'path_to_folder'

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

[, PARTITION ( column name [ , ...n ] ) ]

WITH <common_table_expression>

SELECT <select_criteria>

Autorisations

Autorisations dans le stockage

Autorisations dans l’instance managée SQL

Types de données pris en charge

Limitations et restrictions

Types de stockage

Désactivée par défaut

Activer CETAS

Autorisations pour activer CETAS

Méthodes d’activation de CETAS

Vérifier l’état de CETAS

Dépanner

Gestion des erreurs

Messages d’erreur courants

Verrouillage

Exemples

R. Utiliser CETAS avec une vue pour créer une table externe en utilisant l’identité managée

B. Utiliser CETAS avec une vue pour créer une table externe avec l’authentification SAS

C. Créer une table externe dans un seul fichier Parquet dans le stockage

D. Créer une table externe partitionnée dans plusieurs fichiers Parquet stockés dans une arborescence de dossiers

Étapes suivantes

Commentaires

Ressources supplémentaires