COPY INTO (Transact-SQL)

S’applique à : Azure Synapse Analytics

Cet article explique comment utiliser l’instruction COPY dans Azure Synapse Analytics pour le chargement à partir de comptes de stockage externes. L’instruction COPY offre une souplesse maximale pour l’ingestion de données à débit élevé dans Azure Synapse Analytics.

Notes

Pour l’entrepôt dans Microsoft Fabric, consultez COPY INTO.

Utilisez COPY pour les fonctionnalités suivantes :

• Utiliser le chargement aux utilisateurs avec privilèges plus restreints, sans avoir besoin d’autorisations CONTROL strictes sur l’entrepôt de données
• Exécuter uniquement une instruction T-SQL sans avoir à créer d’objets de base de données supplémentaires
• Analyser et charger correctement les fichiers CSV où les séparateurs (chaîne, champ, ligne) sont placés dans une séquence d’échappement dans des colonnes délimitées par des chaînes
• Spécifier un meilleur modèle d’autorisation sans exposer les clés de compte de stockage à l’aide de signatures d’accès partagé (SAS)
• Utiliser un autre compte de stockage pour l’emplacement ERRORFILE (REJECTED_ROW_LOCATION)
• Personnaliser les valeurs par défaut pour chaque colonne cible et spécifier les champs de données sources à charger dans des colonnes cibles spécifiques
• Spécifier un indicateur de fin de ligne personnalisé, un indicateur de fin de champ et un guillemet de champ pour les fichiers CSV
• Utiliser des formats de date SQL Server pour les fichiers CSV
• Spécifier des caractères génériques et plusieurs fichiers dans le chemin de l’emplacement du stockage
• La découverte automatique de schéma simplifie le processus de définition et de mappage des données sources dans les tables cibles
• Le processus de création automatique de la table crée automatiquement les tables et fonctionne parallèlement à la découverte automatique de schéma
• Charger directement des types de données complexes à partir de fichiers Parquet tels que Cartes et listes dans des colonnes de chaîne, sans utiliser d’autres outils pour prétraiter les données

Remarque

Pour charger des types de données complexes à partir de fichiers Parquet, la création automatique de tables doit être activée à l’aide AUTO_CREATE_TABLEde .

Pour obtenir des exemples complets et des Démarrages rapides à l’aide de l’instruction COPY, consultez la documentation suivante :

• Démarrage rapide : Charger en masse des données à l’aide de l’instruction COPY
• Démarrage rapide : Exemples utilisant l’instruction COPY et les méthodes d’authentification prises en charge
• Démarrage rapide : Création de l’instruction COPY à l’aide de l’interface utilisateur de Synapse Studio enrichie

Remarque

Microsoft Entra ID s'appelait Azure Active Directory (Azure AD) jusqu'à une date récente.

Syntaxe

COPY INTO [ schema. ] table_name
[ (Column_list) ]
FROM '<external_location>' [ , ...n ]
WITH
 (
 [ FILE_TYPE = { 'CSV' | 'PARQUET' | 'ORC' } ]
 [ , FILE_FORMAT = EXTERNAL FILE FORMAT OBJECT ]
 [ , CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , ERRORFILE = ' [ http(s)://storageaccount/container ] /errorfile_directory [ / ] ] '
 [ , ERRORFILE_CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , MAXERRORS = max_errors ]
 [ , COMPRESSION = { 'Gzip' | 'DefaultCodec' | 'Snappy' } ]
 [ , FIELDQUOTE = 'string_delimiter' ]
 [ , FIELDTERMINATOR =  'field_terminator' ]
 [ , ROWTERMINATOR = 'row_terminator' ]
 [ , FIRSTROW = first_row ]
 [ , DATEFORMAT = 'date_format' ]
 [ , ENCODING = { 'UTF8' | 'UTF16' } ]
 [ , IDENTITY_INSERT = { 'ON' | 'OFF' } ]
 [ , AUTO_CREATE_TABLE = { 'ON' | 'OFF' } ]
)

Arguments

schema_name

Facultatif si le schéma par défaut de l’utilisateur réalisant l’opération est le schéma de la table spécifiée. Si le schéma n’est pas spécifié et que le schéma par défaut de l’utilisateur effectuant l’opération COPY est différent du schéma de la table spécifiée, COPY est annulé et un message d’erreur est retourné.

table_name

Le nom de la table dans laquelle copier les données. La table cible peut être une table temporaire ou permanente et elle doit déjà exister dans la base de données. Pour le mode de détection automatique de schéma, ne fournissez pas de liste de colonnes.

(column_list)

Une liste facultative d’une ou de plusieurs colonnes utilisées pour mapper les champs de données sources aux colonnes de la table cible en vue du chargement des données.

Ne spécifiez pas la valeur column_list quand AUTO_CREATE_TABLE = 'ON'.

column_list doit être placé entre parenthèses et délimité par des virgules. La liste des colonnes est au format suivant :

[(Column_name [default Default_value] [Field_number] [,...n])]

• Column_name : nom de la colonne dans la table cible.
• Default_value : valeur par défaut qui remplace toute valeur NULL dans le fichier d’entrée. Elle s’applique à tous les formats de fichier. L’instruction COPY tente de charger une valeur NULL à partir du fichier d’entrée quand une colonne de la liste de colonnes est omise ou qu’un champ est vide dans le fichier d’entrée. La valeur par défaut est précédée du mot clé ’default’
• Field_number : numéro de champ de fichier d’entrée mappé à la colonne cible.
• L’indexation des champs commence au numéro 1.

Quand aucune liste de colonnes n’est spécifiée, COPY mappe les colonnes en suivant l’ordre de la source et de la cible : le champ d’entrée 1 est mappé à la colonne cible 1, le champ 2 à la colonne 2, et ainsi de suite.

Emplacements externes

Désigne l’emplacement où sont placés les fichiers de données. Actuellement, Azure Data Lake Storage (ADLS) Gen2 et Stockage Blob Azure sont pris en charge :

• Emplacement externe pour le Stockage Blob : https://<account\>.blob.core.windows.net/<container\>/<path\>
• Emplacement externe pour ADLS Gen2 : https://<account\>.dfs.core.windows.net/<container\>/<path\>

Notes

Le point de terminaison .blob est également disponible pour la ADLS Gen2 et produit actuellement les meilleures performances. Utilisez le point de terminaison .blob quand .dfs n’est pas nécessaire pour votre méthode d’authentification.

• Account : nom du compte de stockage

• Container : nom du conteneur d’objets blob

• Path : chemin du fichier ou dossier contenant les données. L’emplacement part du conteneur. Si un dossier est spécifié, l’opération COPY récupère tous les fichiers du dossier et de tous ses sous-dossiers. COPY ignore les dossiers masqués et ne retourne pas les fichiers dont le nom débute par un trait de soulignement (_) ou un point (.), sauf si ces caractères sont explicitement spécifiés dans le chemin. Ce comportement est le même quand vous spécifiez un chemin comportant un caractère générique.

Les caractères génériques sont autorisés dans les chemins dans les cas suivants

• La correspondance des noms de chemin contenant des caractères génériques respecte la casse
• Un caractère générique peut être placé dans une séquence d’échappement par l’usage de la barre oblique inverse (\)
• Le développement des caractères génériques est effectué de manière récursive. Par exemple, tous les fichiers CSV sous Customer1 (y compris les sous-répertoires de Customer1) seront chargés dans l’exemple suivant : Account/Container/Customer1/*.csv

Notes

Pour des performances optimales, ne spécifiez pas de caractères génériques qui seraient développés dans plus de fichiers. Dans la mesure du possible, listez plusieurs emplacements de fichiers au lieu de spécifier des caractères génériques.

Les emplacements de fichiers multiples peuvent uniquement être spécifiés à partir du même compte de stockage et du même conteneur en utilisant une liste d’emplacements séparés par des virgules, comme ceci :

• https://<account>.blob.core.windows.net/<container\>/<path\>, https://<account\>.blob.core.windows.net/<container\>/<path\>

FILE_TYPE = { 'CSV' | 'PARQUET' | 'ORC' }

FILE_TYPE spécifie le format des données externes.

• CSV : spécifie un fichier de valeurs séparées par des virgules conformément à la norme RFC 4180.
• PARQUET : spécifie un format Parquet.
• ORC : Spécifie un format ORC.

Notes

Le type de fichier « Texte délimité » dans PolyBase est remplacé par le format de fichier « CSV » dans lequel le délimiteur par défaut (la virgule) peut être configuré à l’aide du paramètre FIELDTERMINATOR.

FILE_FORMAT = external_file_format_name

FILE_FORMAT s’applique aux fichiers Parquet et ORC uniquement ; il spécifie le nom de l’objet de format de fichier externe qui stocke le type de fichier et la méthode de compression des données externes. Pour créer un format de fichier externe, utilisez CREATE EXTERNAL FILE FORMAT.

CREDENTIAL (IDENTITY = '', SECRET = '')

CREDENTIAL spécifie le mécanisme d’authentification pour accéder au compte de stockage externe. Voici les méthodes d’authentification :

	CSV	Parquet	ORC
Stockage Blob Azure	SAS/MSI/SERVICE PRINCIPAL/KEY/AAD	SAS/KEY	SAS/KEY
Azure Data Lake Gen2	SAS/MSI/SERVICE PRINCIPAL/KEY/AAD	SAS (blob 1 )/MSI (dfs 2 )/SERVICE PRINCIPAL/KEY/AAD	SAS (blob 1 )/MSI (dfs 2 )/SERVICE PRINCIPAL/KEY/AAD

1 : Le point de terminaison .blob ( .blob.core.windows.net) dans le chemin de votre emplacement externe est nécessaire pour cette méthode d’authentification.

2 : Le point de terminaison .dfs ( .dfs.core.windows.net) dans le chemin de votre emplacement externe est nécessaire pour cette méthode d’authentification.

Remarque

• Lors de l’authentification à l’aide de Microsoft Entra ID ou d’un compte de stockage public, credential n’a pas besoin d’être spécifié.
• Si votre compte de stockage est associé à un réseau virtuel, vous devez vous authentifier à l’aide d’une identité managée.

• Authentification avec la signature d’accès partagé (SAS)

  • IDENTITY : constante avec une valeur « Shared Access Signature »
  • SECRET : La signature d’accès partagé fournit un accès délégué aux ressources de votre compte de stockage.
  • Autorisations minimales requises : READ et LIST

• Authentification avec des principaux de service

  • IDENTITÉ : <ClientID>@<OAuth_2.0_Token_EndPoint>
  • SECRET : Clé du principal du service d’application Microsoft Entra
  • Rôles RBAC minimum requis : Contributeur aux données Blob du stockage, Propriétaire des données Blob du stockage ou Lecteur des données Blob du stockage

• Authentification avec une clé de compte de stockage

  • IDENTITY : constante avec une valeur « Storage Account Key »
  • SECRET : clé du compte de stockage

• Authentification avec une identité managée (points de terminaison du service VNet)

  • IDENTITY : constante avec une valeur « Managed Identity »
  • Rôles RBAC minimaux requis : contributeur aux données blob de stockage ou propriétaire des données blob de stockage pour le serveur logique inscrit par Microsoft Entra dans Azure. Lors de l’utilisation d’un pool SQL dédié (anciennement SQL DW) qui n’est pas associé à un espace de travail Synapse, ce rôle RBAC n’est pas obligatoire, mais l’identité managée nécessite des autorisations de liste de contrôle d’accès (ACL) sur les objets cibles pour permettre l’accès en lecture aux fichiers sources

• Authentification auprès d’un utilisateur Microsoft Entra

  • CREDENTIAL n’est pas requis
  • Rôles RBAC minimaux requis : contributeur aux données blob de stockage ou propriétaire des données blob de stockage pour l’utilisateur Microsoft Entra

ERRORFILE = emplacement du répertoire

ERRORFILE s’applique uniquement au format CSV. Spécifie le répertoire dans l’instruction COPY dans lequel les lignes rejetées et le fichier d’erreur correspondant doivent être écrits. Il est possible de spécifier le chemin complet du compte de stockage ou le chemin relatif du conteneur. Si le chemin spécifié n’existe pas, un chemin est créé pour vous. Un répertoire enfant est créé sous le nom « _rejectedrows ». Le caractère « _ » garantit que le répertoire est placé dans une séquence d’échappement pour le traitement d’autres données, sauf s’il est explicitement nommé dans le paramètre d’emplacement.

Remarque

Lorsqu’un chemin relatif est passé à ERRORFILE, le chemin d’accès est relatif au chemin d’accès du conteneur spécifié dans external_location.

Dans ce répertoire figure un dossier qui est créé d’après l’heure de soumission du chargement au format AnnéeMoisJour - HeureMinuteSeconde (par exemple, 20180330-173205). Dans ce dossier, deux types de fichiers sont écrits : le fichier de la raison (erreur) et le fichier des données (ligne). Chaque fichier est prérempli avec un ID de requête (queryID), un ID de distribution (distributionID) et un GUID de fichier. Comme les données et la raison se trouvent dans des fichiers distincts, les fichiers correspondants ont un préfixe analogue.

Si ERRORFILE a le chemin complet du compte de stockage défini, ERRORFILE_CREDENTIAL sera utilisé pour les connexions à ce stockage. Sinon, la valeur spécifiée pour CREDENTIAL sera utilisée. Lorsque les mêmes informations d’identification utilisées pour les données sources sont utilisées pour ERRORFILE, les restrictions qui s’appliquent à ERRORFILE_CREDENTIAL s’appliquent également

ERRORFILE_CREDENTIAL = (IDENTITY= '', SECRET = '')

ERRORFILE_CREDENTIAL s’applique uniquement aux fichiers CSV. Les sources de données et les méthodes d’authentification suivantes sont prises en charge :

• Stockage Blob Azure - SAS/SERVICE PRINCIPAL/AAD

• Azure Data Lake Gen2 - SAS/MSI/SERVICE PRINCIPAL/AAD

• Authentification avec la signature d’accès partagé (SAS)

  • IDENTITY : constante avec une valeur « Shared Access Signature »
  • SECRET : La signature d’accès partagé fournit un accès délégué aux ressources de votre compte de stockage.
  • Autorisations minimales requises : READ, LIST, WRITE, CREATE, DELETE

• Authentification avec des principaux de service

  • IDENTITÉ : <ClientID>@<OAuth_2.0_Token_EndPoint>
  • SECRET : Clé du principal du service d’application Microsoft Entra
  • Rôles RBAC minimum requis : Contributeur aux données Blob du stockage ou Propriétaire des données Blob du stockage

Notes

Utilisez le point de terminaison de jeton OAuth 2.0 V1

• Authentification avec une identité managée (points de terminaison du service VNet)

  • IDENTITY : constante avec une valeur « Managed Identity »
  • Rôles RBAC minimaux requis : contributeur aux données blob de stockage ou propriétaire des données blob de stockage pour le serveur SQL Database inscrit par Microsoft Entra

• Authentification auprès d’un utilisateur Microsoft Entra

  • CREDENTIAL n’est pas requis
  • Rôles RBAC minimaux requis : contributeur aux données blob de stockage ou propriétaire des données blob de stockage pour l’utilisateur Microsoft Entra

L’utilisation d’une clé de compte de stockage avec ERRORFILE_CREDENTIAL n’est pas prise en charge.

Remarque

Si vous utilisez le même compte de stockage pour ERRORFILE et que vous spécifiez pour ERRORFILE un chemin relatif à la racine du conteneur, vous n’avez pas besoin de spécifier ERROR_CREDENTIAL.

MAXERRORS = max_errors

MAXERRORS spécifie le nombre maximal de lignes de rejet autorisées dans le chargement avant l’échec de l’opération COPY. Chaque ligne qui ne peut pas être importée par l’opération COPY est ignorée et est comptabilisée comme une erreur. Si max_errors n’est pas spécifié, la valeur par défaut est 0.

MAXERRORS ne peut pas être utilisé avec AUTO_CREATE_TABLE.

Lorsque FILE_TYPE est « PARQUET », les exceptions provoquées par des erreurs de conversion de type de données (par exemple, un fichier binaire Parquet en entier SQL) entraînent toujours l’échec de COPY INTO, ignorant MAXERRORS.

COMPRESSION = { 'DefaultCodec ' | 'Snappy' | 'GZIP' | 'NONE'}

COMPRESSION est facultatif et spécifie la méthode de compression appliquée aux données externes.

• CSV prend en charge GZIP
• Parquet prend en charge GZIP et Snappy
• ORC prend en charge DefaultCodec et Snappy
• Zlib est la compression par défaut utilisée pour ORC

La commande COPY détecte automatiquement le type de compression en fonction de l’extension de fichier quand ce paramètre n’est pas spécifié :

• .gz - GZIP
• .snappy – Snappy
• .deflate - DefaultCodec (Parquet et ORC uniquement)

La commande COPY nécessite que les fichiers gzip ne contiennent pas de mémoire de fin pour fonctionner normalement. Le format gzip exige strictement que les fichiers soient composés de membres valides sans aucune information supplémentaire avant, entre ou après eux. Tout écart par rapport à ce format, tel que la présence de données non gzip de fin, entraîne l’échec de la commande COPY. Veillez à vérifier qu’il n’existe aucune mémoire de fin à la fin des fichiers gzip pour vous assurer que COPY peut traiter correctement ces fichiers.

FIELDQUOTE = 'field_quote'

FIELDQUOTE s’applique au format CSV et spécifie un caractère unique qui est utilisé comme guillemet (délimiteur de chaîne) dans le fichier CSV. Si vous ne spécifiez pas cet argument, le caractère guillemet (") servira de guillemet conformément à l’utilisation définie dans la norme RFC 4180. La notation hexadécimale est également prise en charge pour FIELDQUOTE. Les caractères ASCII et multioctets étendus ne sont pas pris en charge avec UTF-8 pour FIELDQUOTE.

Remarque

Les caractères FIELDQUOTE sont placés dans une séquence d’échappement dans les colonnes de type chaîne qui contiennent un double FIELDQUOTE (délimiteur).

FIELDTERMINATOR = 'field_terminator'

FIELDTERMINATOR s’applique uniquement au format CSV. Spécifie la marque de fin de champ à utiliser dans le fichier CSV. La marque de fin de champ peut être spécifiée en notation hexadécimale. Plusieurs caractères peuvent être utilisés comme marque de fin de champ. La virgule (,) est la marque de fin de champ par défaut. Les caractères ASCII et multioctets étendus ne sont pas pris en charge avec UTF-8 pour FIELDTERMINATOR.

ROW TERMINATOR = 'row_terminator'

ROW TERMINATOR s’applique uniquement au format CSV. Spécifie la marque de fin de ligne à utiliser dans le fichier CSV. La marque de fin de ligne peut être spécifiée en notation hexadécimale. Plusieurs caractères peuvent être utilisés comme marque de fin de ligne. La combinaison de caractères « \r\n » est la marque de fin de ligne par défaut.

La commande COPY ajoute le caractère \r quand vous spécifiez le caractère \n (nouvelle ligne), ce qui donne \r\n. Pour spécifier le caractère \n uniquement, utilisez sa notation hexadécimale (0x0A). Si vous spécifiez des marques de fin de ligne à plusieurs caractères au format hexadécimal, n’ajoutez pas « 0x » entre chaque caractère.

Les caractères ASCII et multioctets étendus ne sont pas pris en charge avec UTF-8 pour ROW TERMINATOR.

FIRSTROW = First_row_int

FIRSTROW s’applique au format CSV et spécifie le numéro de ligne qui est lu en premier dans tous les fichiers par la commande COPY. Les valeurs commencent à 1, la valeur par défaut. Si la valeur est définie sur 2, la première ligne de chaque fichier (ligne d’en-tête) est ignorée lorsque les données sont chargées. Les lignes sont ignorées en présence de marques de fin de ligne.

DATEFORMAT = { 'mdy' | 'dmy' | 'ymd' | 'ydm' | 'myd' | 'dym' }

DATEFORMAT s’applique uniquement au format CSV et spécifie le format de date pour le mappage des dates avec les formats de date SQL Server. Pour obtenir une vue d’ensemble de tous les types de données et fonctions de date et d’heure Transact-SQL, consultez Types de données et fonctions de date et d’heure (Transact-SQL). Dans la commande COPY, DATEFORMAT a une précédence supérieure au DATEFORMAT configuré au niveau de la session.

ENCODING = 'UTF8' | 'UTF16'

ENCODING s’applique uniquement au format CSV. La valeur par défaut est UTF8. Spécifie la norme d’encodage des données dans les fichiers chargés par la commande COPY.

IDENTITY_INSERT = 'ON' | 'OFF'

IDENTITY_INSERT spécifie si la ou les valeurs d’identité figurant dans le fichier de données importé doivent être utilisées pour la colonne d’identité. Si IDENTITY_INSERT est défini sur OFF (valeur par défaut), les valeurs d’identité de cette colonne sont vérifiées, mais pas importées. Azure Synapse Analytics affectera automatiquement des valeurs uniques en fonction des valeurs de départ et d’incrément spécifiées au moment de la création de la table. Notez le comportement suivant avec la commande COPY :

• Si IDENTITY_INSERT est désactivé et que la table a une colonne d’identité
  • Une liste de colonnes doit être spécifiée, mais elle ne doit pas mapper un champ d’entrée à la colonne d’identité.
• Si IDENTITY_INSERT est ACTIVÉ et que la table a une colonne d’identité
  • Si une liste de colonnes est passée, elle doit mapper un champ d’entrée à la colonne d’identité.
• La valeur par défaut n’est pas prise en charge pour la colonne d’identité (IDENTITY) dans la liste des colonnes.
• IDENTITY_INSERT ne peut être défini que pour une table à la fois.

AUTO_CREATE_TABLE = { 'ON' | 'OFF' }

AUTO_CREATE_TABLE spécifie si la table peut être créée automatiquement parallèlement à la découverte automatique de schéma. Vous pouvez l’utiliser uniquement pour les fichiers Parquet.

• ON : active la création automatique de table. Le processus COPY INTO crée automatiquement une table en découvrant la structure du fichier à charger. Peut également être utilisé avec des tables préexistantes pour tirer parti de la découverte automatique du schéma des fichiers Parquet.
• OFF : la création automatique de table n’est pas activée. Par défaut.

Notes

La création automatique de table fonctionne parallèlement à la découverte automatique de schéma. La création automatique de table n’est PAS activée par défaut.

Ne les chargez pas dans des tables distribuées par code de hachage à partir de fichiers Parquet à l’aide de COPY INTO avec AUTO_CREATE_TABLE = ’ON’.

Si les fichiers Parquet doivent être chargés dans des tables distribuées par synthèse à l’aide de COPY INTO, chargez-les dans une table intermédiaire de tourniquet (round robin) suivis de INSERT ... SELECT de cette table vers la table distribuée par code de hachage cible.

Autorisations

L’utilisateur qui exécute la commande COPY doit avoir les autorisations suivantes :

• ADMINISTER DATABASE BULK OPERATIONS
• INSERT

Requiert les autorisations INSERT et ADMINISTER BULK OPERATIONS. Dans Azure Synapse Analytics, les opérations INSERT et ADMINISTER DATABASE BULK OPERATIONS sont nécessaires.

En outre, si l’utilisateur qui exécute la commande COPY a également l’intention de générer une nouvelle table et d’y charger des données, il a besoin des autorisations CREATE TABLE et ALTER ON SCHEMA.

Par exemple, pour autoriser mike@contoso.com à utiliser COPY pour créer une table dans le schéma HR et insérer les données à partir d’un fichier Parquet, utilisez l’exemple Transact-SQL suivant :

GRANT ADMINISTER DATABASE BULK OPERATIONS to [mike@contoso.com];
GRANT INSERT to [mike@contoso.com];

GRANT CREATE TABLE to [mike@contoso.com];
GRANT ALTER on SCHEMA::HR to [mike@contoso.com];

Notes

L’instruction COPY accepte uniquement les caractères valides UTF-8 et UTF-16 pour les données de ligne et les paramètres de commande. Les fichiers sources ou les paramètres (tels que ROW TERMINATOR ou FIELD TERMINATOR) qui utilisent des caractères non valides risquent d’être interprétés de manière incorrecte par l’instruction COPY et de provoquer des résultats inattendus comme l’altération des données ou d’autres échecs. Assurez-vous que vos fichiers sources et paramètres sont conformes à UTF-8 ou UTF-16 avant d’appeler l’instruction COPY.

Exemples

R. Charger à partir d’un compte de stockage public

L’exemple suivant est la forme la plus simple de la commande COPY, qui charge les données à partir d’un compte de stockage public. Ici, les valeurs par défaut de l’instruction COPY correspondent au format du fichier CSV « lineitem ».

COPY INTO dbo.[lineitem]
FROM 'https://unsecureaccount.blob.core.windows.net/customerdatasets/folder1/lineitem.csv'
WITH (FIELDTERMINATOR = '|')

Ces valeurs par défaut sont les suivantes :

• DATEFORMAT = Session DATEFORMAT

• MAXERRORS = 0

• COMPRESSION = désactivée par défaut

• FIELDQUOTE = '"'

• FIELDTERMINATOR = ’,’

• ROWTERMINATOR = '\n'

Important

COPY traite \n comme \r\n en interne. Pour plus d’informations, consultez la section ROWTERMINATOR.

• FIRSTROW = 1

• ENCODING = 'UTF8'

• FILE_TYPE = 'CSV'

• IDENTITY_INSERT = 'OFF'

B. Charger l’authentification avec la signature d’accès partagé (SAS)

L’exemple suivant charge des fichiers qui utilisent le saut de ligne comme marque de fin de ligne, par exemple une sortie UNIX. Il utilise également une clé SAS pour l’authentification auprès du Stockage Blob Azure.

COPY INTO test_1
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>'),
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='?sv=2018-03-28&ss=bfqt&srt=sco&sp=rl&st=2016-10-17T20%3A14%3A55Z&se=2021-10-18T20%3A19%3A00Z&sig=IEoOdmeYnE9%2FKiJDSHFSYsz4AkNa%2F%2BTx61FuQ%2FfKHefqoBE%3D'),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=';',
    ROWTERMINATOR='0X0A',
    ENCODING = 'UTF8',
    DATEFORMAT = 'ymd',
    MAXERRORS = 10,
    ERRORFILE = '/errorsfolder',--path starting from the storage container
    IDENTITY_INSERT = 'ON'
)

C. Charger avec une liste de colonnes contenant des valeurs par défaut pour l’authentification avec une clé de compte de stockage

Cet exemple charge des fichiers en spécifiant une liste de colonnes avec des valeurs par défaut.

--Note when specifying the column list, input field numbers start from 1
COPY INTO test_1 (Col_one default 'myStringDefault' 1, Col_two default 1 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='<Your_Account_Key>'),
    --CREDENTIAL should look something like this:
    --CREDENTIAL=(IDENTITY= 'Storage Account Key', SECRET='x6RWv4It5F2msnjelv3H4DA80n0PQW0daPdw43jM0nyetx4c6CpDkdj3986DX5AHFMIf/YN4y6kkCnU8lb+Wx0Pj+6MDw=='),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0x0A',
    ENCODING = 'UTF8',
    FIRSTROW = 2
)

D. Charger Parquet ou ORC à l’aide d’un objet de format de fichier existant

Cet exemple utilise un caractère générique pour charger tous les fichiers Parquet dans un dossier.

COPY INTO test_parquet
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.parquet'
WITH (
    FILE_FORMAT = myFileFormat,
    CREDENTIAL=(IDENTITY= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
)

E. Charger en spécifiant des caractères génériques et plusieurs fichiers

COPY INTO t1
FROM
'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt',
    'https://myaccount.blob.core.windows.net/myblobcontainer/folder1'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL=(IDENTITY= '<client_id>@<OAuth_2.0_Token_EndPoint>',SECRET='<key>'),
    FIELDTERMINATOR = '|'
)

F. Charger en utilisant des informations d’identification MSI

COPY INTO dbo.myCOPYDemoTable
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder0/*.txt'
WITH (
    FILE_TYPE = 'CSV',
    CREDENTIAL = (IDENTITY = 'Managed Identity'),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=','
)

G. Charger à l’aide de la détection automatique de schéma

COPY INTO [myCOPYDemoTable]
FROM 'https://myaccount.blob.core.windows.net/customerdatasets/folder1/lineitem.parquet'
WITH (
    FILE_TYPE = 'Parquet',
    CREDENTIAL = ( IDENTITY = 'Shared Access Signature',  SECRET='<key>'),
    AUTO_CREATE_TABLE = 'ON'
)

Questions fréquentes (FAQ)

Quelles sont les performances de la commande COPY par rapport à Polybase ?

La commande COPY offre de meilleures performances en fonction de votre charge de travail.

• Les fichiers compressés ne peuvent pas être divisés automatiquement. Pour obtenir de meilleures performances de chargement, divisez votre entrée en plusieurs fichiers quand vous chargez des fichiers CSV compressés.

• Les fichiers CSV volumineux non compressés pouvant être divisés et chargés en parallèle automatiquement, il est inutile de diviser manuellement les fichiers CSV non compressés dans la majorité des cas. Dans certains cas où la division automatique des fichiers n’est pas possible en raison des caractéristiques des données, le fait de diviser manuellement les fichiers CSV volumineux peut encore améliorer les performances.

Quelles sont les directives de division de fichier pour la commande COPY pendant le chargement de fichiers CSV ?

Le tableau suivant indique les nombres de fichiers conseillés. Une fois le nombre de fichiers recommandé atteint, vous obtenez de meilleures performances, plus les fichiers sont volumineux. Le nombre de fichiers est déterminé par le nombre de nœuds de calcul multiplié par 60. Par exemple, à 6 000 DWU, nous avons 12 nœuds de calcul et 12*60 = 720 partitions. Pour une expérience de fractionnement de fichiers simple, consultez Guide pratique pour optimiser le débit de charge COPY avec des fractionnements de fichiers.

DWU	Nombre de fichiers
100	60
200	60
300	60
400	60
500	60
1 000	120
1 500	180
2 000	240
2 500	300
3 000	360
5 000	600
6 000 / 750	720
7 500	900
10 000	1200
15,000	1800
30,000	3600

Quelles sont les recommandations de fractionnement de fichiers pour la commande COPY lors du chargement de fichiers Parquet ou ORC ?

Il n’est pas nécessaire de fractionner les fichiers Parquet et ORC, car la commande COPY le fait automatiquement. Les fichiers Parquet et ORC du compte de stockage Azure doivent avoir une taille de 256 Mo ou plus pour obtenir des performances optimales.

Existe-t-il des restrictions quant au nombre ou à la taille des fichiers ?

Il n’existe aucune limite quant au nombre ou à la taille des fichiers. Toutefois, nous recommandons des fichiers d’au moins 4 Mo pour obtenir des performances optimales.

Existe-t-il des problèmes connus avec l’instruction COPY ?

Si vous disposez d’un espace de travail Azure Synapse qui a été créé avant le 7 décembre 2020, vous pouvez rencontrer un message d’erreur similaire lors de l’authentification avec une identité managée : com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity has not been enabled on this server. Please enable Managed Service Identity and try again.

Effectuez les étapes suivantes pour contourner ce problème en réinscrivant l’identité managée de l’espace de travail :

1. Installez Azure PowerShell. Reportez-vous à Installer PowerShell.
2. Inscrivez l’identité managée de votre espace de travail à l’aide de PowerShell :

   Connect-AzAccount
   Select-AzSubscription -SubscriptionId <subscriptionId>
   Set-AzSqlServer -ResourceGroupName your-database-server-resourceGroup -ServerName your-SQL-servername -AssignIdentity
   

Contenu connexe

• Vue d’ensemble du chargement avec Azure Synapse Analytics