COPIER DANS (Transact-SQL)

S’applique à :Azure Synapse Analytics

Cet article explique comment utiliser l’instruction COPY dans Azure Synapse Analytics pour charger des données à partir de comptes de stockage externes. Cette COPY déclaration offre la plus grande flexibilité pour l’ingestion de données à haut débit dans Azure Synapse Analytics.

Utilisation COPY pour les capacités suivantes :

• Utilisez des utilisateurs privilégiés inférieurs pour charger des données 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.
• Analysez et chargez correctement les fichiers CSV dans lesquels les délimiteurs (chaîne, champ, ligne) sont placés dans des colonnes délimitées par des chaînes.
• Spécifiez un modèle d’autorisation plus fin sans exposer de clés de compte de stockage à l’aide de signatures d’accès partagé (SAP).
• Utilisez 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écifiez un terminateur de ligne personnalisé, un terminateur de champ et une citation de champ pour les fichiers CSV.
• Utilisez SQL Server formats Date 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 des tables cibles.
• Le processus de création automatique de table crée automatiquement les tables et fonctionne en même temps que la découverte automatique de schémas.
• Chargez 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.

Pour obtenir des exemples complets et des guides de démarrage rapide à l’aide de l’instruction COPY , consultez :

• 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

Syntax

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 vous ne spécifiez pas de schéma 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, l’opération COPY est annulée et un message d’erreur est retourné.

nom_de_table

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_value par défaut] [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.

Lorsque vous ne spécifiez pas de liste de colonnes, COPY mappe les colonnes en fonction de l’ordre source et cible : le champ d’entrée 1 passe à la colonne cible 1, le champ 2 passe à la colonne 2, et ainsi de suite.

Emplacements externes

Emplacement où les fichiers contenant les données sont intermédiaires. 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\>

• 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 vous spécifiez un dossier, COPY récupère tous les fichiers du dossier et tous ses sous-dossiers. COPY ignore les dossiers masqués et ne retourne pas de fichiers commençant par un trait de soulignement (_) ou un point (.) sauf indication explicite dans le chemin d’accès. Ce comportement est le même quand vous spécifiez un chemin comportant un caractère générique.

Vous pouvez inclure des caractères génériques dans le chemin d’accès où :

• La correspondance des noms de chemin contenant des caractères génériques respecte la casse
• Vous pouvez échapper à un caractère générique à l’aide du caractère 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) sont chargés dans l’exemple suivant : Account/Container/Customer1/*.csv

Vous pouvez spécifier plusieurs emplacements de fichiers uniquement à partir du même compte de stockage et du même conteneur via une liste séparée par des virgules, par exemple :

• 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 conforme à la norme RFC 4180 .
• PARQUET : spécifie un format Parquet.
• ORC : Spécifie un format ORC.

FILE_FORMAT = external_file_format_name

FILE_FORMAT s’applique uniquement aux fichiers Parquet et ORC. 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 pour les 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 permettant d’accéder au compte de stockage externe. Voici les méthodes d’authentification :

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

¹ Le point de terminaison blob (.blob.core.windows.net) dans votre chemin d’accès d’emplacement externe est requis pour cette méthode d’authentification.

² Le point de terminaison dfs (.dfs.core.windows.net) dans votre chemin d’accès à l’emplacement externe est requis pour cette méthode d’authentification.

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

  • IDENTITY: constante avec une valeur de 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

  • IDENTITY: <ClientID>@<OAuth_2.0_Token_EndPoint>
  • SECRET : clé de 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 de Storage Account Key
  • SECRET: clé de compte de stockage

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

  • IDENTITY: constante avec une valeur de 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. Lorsque vous utilisez 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 (ACL) Access Control sur les objets cibles pour permettre l'accès en lecture aux fichiers sources.

• Authentification auprès d’un utilisateur Microsoft Entra

  • LES INFORMATIONS D’IDENTIFICATION ne sont pas requises

• 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. Il spécifie le répertoire dans l’instruction COPY où les lignes rejetées et le fichier d’erreur correspondant sont écrits. Vous pouvez spécifier le chemin complet à partir du compte de stockage ou du chemin d’accès relatif au conteneur. Si le chemin spécifié n’existe pas, l’entrepôt en crée un. Un répertoire enfant est créé avec le nom _rejectedrows. Le _ caractère garantit que le répertoire est placé dans une séquence d’échappement pour d’autres traitements de données, sauf si le paramètre d’emplacement est explicitement nommé.

Dans ce répertoire, l’entrepôt crée un dossier basé sur l’heure de chargement de la soumission au format YearMonthDay -HourMinuteSecond (par exemple). 20180330-173205 Dans ce dossier, le processus écrit deux types de fichiers : le fichier de raison (erreur) et le fichier de données (ligne). Chaque fichier précède , queryIDdistributionIDet 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 le chemin d’accès complet du compte de stockage est défini, COPY utilise ERRORFILE_CREDENTIAL pour se connecter à ce stockage. Sinon, elle utilise la valeur que vous spécifiez pour CREDENTIAL. Lorsque vous utilisez les mêmes informations d’identification pour les données sources et ERRORFILEque les restrictions qui s’appliquent également ERRORFILE_CREDENTIAL .

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 : SAP, principal de service ou Microsoft Entra

• Azure Data Lake Gen2 : SAP, MSI, principal de service ou Microsoft Entra

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

  • IDENTITY: constante avec une valeur de 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

  • IDENTITY: <ClientID>@<OAuth_2.0_Token_EndPoint>
  • SECRET : clé de 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

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

  • IDENTITY: constante avec une valeur de 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 obligatoire

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

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 que l’opération COPY ne peut pas importer est ignorée et est comptabilisée comme une erreur. Si vous ne spécifiez pas de valeur pour le nombre maximal d’erreurs, la valeur par défaut est 0.

MAXERRORS ne peut pas être utilisé avec AUTO_CREATE_TABLE.

Quand FILE_TYPE c’est PARQUETle cas, les exceptions provoquées par les erreurs de conversion de type de données (par exemple, le fichier binaire Parquet en entier SQL) provoquent COPY INTO toujours l’échec, ignorant MAXERRORS.

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

COMPRESSION est facultatif et spécifie la méthode de compression des données pour les 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 pour ORC.

La commande COPY détecte automatiquement le type de compression en fonction de l’extension de fichier lorsque vous ne spécifiez pas ce paramètre :

• .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. Pour vous assurer que COPY s’exécute correctement, veillez à vérifier qu’il n’existe aucune mémoire de fin à la fin des fichiers gzip.

FIELDQUOTE = 'field_quote'

FIELDQUOTE s’applique au fichier CSV et spécifie un caractère unique utilisé comme caractère de guillemet (délimiteur de chaîne) dans le fichier CSV. Si vous ne spécifiez pas cette valeur, le caractère de guillemet (") est utilisé comme caractère de guillemet tel que défini 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.

FIELDTERMINATOR = 'field_terminator'

FIELDTERMINATOR s’applique uniquement au format CSV. Spécifie le terminateur de champ utilisé dans le fichier CSV. Vous pouvez spécifier la marque de fin de champ à l’aide de la notation hexadécimale. Le terminateur de champ peut être multicharacteur. L’indicateur de fin de champ par défaut est un (,). Les caractères ASCII et multioctets étendus ne sont pas pris en charge avec UTF-8 pour FIELDTERMINATOR.

ROWTERMINATOR = 'row_terminator'

ROWTERMINATOR s’applique uniquement au format CSV. Spécifie le terminateur de ligne utilisé dans le fichier CSV. Vous pouvez spécifier la marque de fin de ligne à l’aide de la notation hexadécimale. Le terminateur de ligne peut être multicharacteur. 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). Lorsque vous spécifiez des terminateurs de ligne multicharacteur en hexadécimal, ne spécifiez 0x pas entre chaque caractère.

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

FIRSTROW = First_row_int

FIRSTROW s’applique au fichier CSV et spécifie le numéro de ligne lu en premier dans tous les fichiers de la commande COPY. Les valeurs commencent par 1, qui est la valeur par défaut. Si vous définissez la valeur 2sur , 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 valeur d’identité ou les valeurs du fichier de données importé doivent être utilisées pour la colonne d’identité. Si IDENTITY_INSERT elle est OFF (valeur par défaut), les valeurs d’identité de cette colonne sont vérifiées, mais pas importées. Notez le comportement suivant avec la commande COPY :

• Si IDENTITY_INSERT la valeur est OFF, et la table a une colonne d’identité
  • Vous devez spécifier une liste de colonnes qui ne mappe pas un champ d’entrée à la colonne d’identité.
• Si IDENTITY_INSERT la valeur est ON, et la table a une colonne d’identité
  • Si vous passez une liste de colonnes, 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 IDENTITY dans la liste des colonnes.
• Vous ne pouvez définir IDENTITY_INSERT qu’une seule table à la fois.

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.

AUTO_CREATE_TABLE = { 'ON' | 'OFF' }

AUTO_CREATE_TABLE spécifie si la table peut être créée automatiquement en travaillant avec la découverte automatique de schémas. AUTO_CREATE_TABLE est disponible uniquement pour les fichiers Parquet dans Azure Synapse Analytics.

• ON : active la création automatique de table. Le COPY INTO processus crée automatiquement une table en découvrant la structure du fichier à charger. Vous pouvez également l’utiliser avec des tables préexistantes pour tirer parti de la découverte automatique de schémas de fichiers Parquet.
• OFF : la création automatique de table n’est pas activée. Default.

Permissions

L’utilisateur exécutant la commande COPY doit disposer des autorisations suivantes :

• ADMINISTRER DES OPÉRATIONS EN BLOC DE BASE DE DONNÉES
• 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];

Remarks

L’instruction COPY n’accepte que les caractères valides UTF-8 et UTF-16 pour les lignes de données et les paramètres de commande. L’instruction COPY peut interpréter incorrectement les fichiers sources ou les paramètres (tels que ROWTERMINATOR ou FIELDTERMINATOR) qui utilisent des caractères non valides et provoquent des résultats inattendus tels que 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’invoquer l’instruction COPY .

L’indicateur MAXDOP de requête n’est pas pris en charge avec COPY INTO.

Pour garantir une exécution fiable, ne modifiez pas les fichiers et dossiers sources pendant l’opération COPY INTO .

• La modification, la suppression ou le remplacement de tous les fichiers ou dossiers référencés pendant l’exécution de la commande peuvent entraîner l’échec de l’opération ou entraîner une ingestion de données incohérente.
• Avant d’exécuter COPY INTO, vérifiez que toutes les données sources sont stables et ne seront pas modifiées pendant le processus.

Si les données sources ont une plus grande précision que la définition de colonne de destination, la valeur est tronquée, non arrondie, pour les types numériques, de date et d’heure.

Examples

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

L’exemple suivant montre la forme la plus simple de la COPY commande, qui charge les données à partir d’un compte de stockage public. Pour cet exemple, les valeurs par défaut de l’instruction COPY correspondent au format du fichier CSV de l’élément de ligne.

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 par défaut est non compressé

• FIELDQUOTE = '"'

• FIELDTERMINATOR = ','

• ROWTERMINATOR = '\n'

• 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 les fichiers qui utilisent le flux de ligne en tant que terminateur de ligne, tel qu’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>'),
    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'
)

FAQ

Comment les performances de la commande COPY sont-ils comparées à PolyBase ?

Les performances de la commande COPY peuvent être meilleures en fonction de votre charge de travail.

• L’entrepôt ne peut pas fractionner automatiquement les fichiers compressés. Pour des performances de chargement optimales, envisagez de fractionner votre entrée en plusieurs fichiers lors du chargement de CSV compressés.

• L’entrepôt peut fractionner automatiquement les fichiers CSV non compressés volumineux pour le chargement parallèle. Vous n’avez donc généralement pas besoin de fractionner manuellement les fichiers CSV non compressés. Dans certains cas où le fractionnement automatique de fichiers n’est pas possible en raison de caractéristiques de données, le fractionnement manuel de grandes VSV peut toujours bénéficier de performances.

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

Le tableau suivant décrit le nombre de fichiers que vous devez utiliser. Lorsque vous atteignez le nombre recommandé de fichiers, vous obtenez de meilleures performances avec des fichiers plus volumineux. Le nombre de fichiers est déterminé par le nombre de nœuds de calcul multiplié par 60. Par exemple, à 6 000 DWU, vous avez 12 nœuds de calcul. Vous avez donc 12 * 60 = 720 partitions. Pour une expérience de fractionnement de fichiers simple, consultez Comment optimiser le débit de charge COPY avec les 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	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 ?

Vous n’avez pas besoin de fractionner les fichiers Parquet et ORC, car la commande COPY fractionne automatiquement les fichiers. Pour des performances optimales, les fichiers Parquet et ORC dans le compte de stockage Azure doivent être de 256 Mo ou plus.

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

Il n’existe aucune limitation quant au nombre ou à la taille des fichiers. Toutefois, pour des performances optimales, utilisez des fichiers d’au moins 4 Mo. En outre, limitez le nombre de fichiers sources à un maximum de 5 000 fichiers pour de meilleures performances.

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

Si vous avez un espace de travail Azure Synapse que vous avez créé avant le 7 décembre 2020, vous risquez d’avoir un message d’erreur similaire lors de l’authentification à l’aide de l’identité managée : com.microsoft.sqlserver.jdbc.SQLServerException: Managed Service Identity isn't enabled on this server. Please enable Managed Service Identity and try again.

Pour contourner ce problème, réinscrivez l’identité managée de l’espace de travail :

1. Installez Azure PowerShell. Consultez 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

S’applique à :Entrepôt dans Microsoft Fabric

Cet article explique comment utiliser l’instruction COPY dans Warehouse dans Microsoft Fabric pour le chargement à partir de comptes de stockage externes. L’instruction COPY offre la plus grande flexibilité pour l’ingestion de données à haut débit dans votre entrepôt dans Microsoft Fabric et est la stratégie de Données les plus importantes dans votre entrepôt dans Microsoft Fabric.

Dans Fabric Data Warehouse, l’instruction COPY prend actuellement en charge les formats de fichiers CSV, JSONL et PARQUET. Pour les sources de données, les comptes Azure Data Lake Storage Gen2 et les sources OneLake sont pris en charge.

Pour plus d’informations sur l’utilisation COPY INTO de votre entrepôt dans Microsoft Fabric, consultez Ingestion de données dans votre entrepôt dans Microsoft Fabric à l’aide de l’instruction COPY.

Par défaut, COPY INTO s’authentifie en tant qu’utilisateur Microsoft Entra ID en cours d’exécution.

Utilisation COPY pour les capacités suivantes :

• Utilisez des utilisateurs privilégiés inférieurs pour charger des données sans avoir besoin d’autorisations CONTROL strictes sur l’entrepôt.
• 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.
• Analysez et chargez correctement des fichiers JSONL dans lesquels chaque ligne est un objet JSON valide et les champs sont mappés à l’aide d’expressions de chemin JSON.
• Spécifiez un modèle d’autorisation plus fin sans exposer de clés de compte de stockage à l’aide de signatures d’accès partagé (SAP).
• Utilisez 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écifiez un terminateur de ligne personnalisé, un terminateur de champ et une citation de champ pour les fichiers CSV.
• Spécifier des caractères génériques et plusieurs fichiers dans le chemin de l’emplacement du stockage.
• Pour plus d’informations sur les options d’ingestion de données et les meilleures pratiques, consultez Ingestion de données dans votre entrepôt dans Microsoft Fabric à l’aide de l’instruction COPY.

Syntax

COPY INTO [ warehouse_name. ] [ schema_name. ] table_name
[ (Column_list) ]
FROM '<external_location>' [ , ...n ]
WITH
 (
 [ FILE_TYPE = { 'CSV' | 'JSONL' | 'PARQUET' } ]
 [ , CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , ERRORFILE = ' [ http(s)://storageaccount/container ] /errorfile_directory [ / ] ] '
 [ , ERRORFILE_CREDENTIAL = (AZURE CREDENTIAL) ]
 [ , MAXERRORS = max_errors ]
 [ , COMPRESSION = { 'Gzip' | 'Snappy' } ]
 [ , FIELDQUOTE = 'string_delimiter' ]
 [ , FIELDTERMINATOR =  'field_terminator' ]
 [ , ROWTERMINATOR = 'row_terminator' ]
 [ , FIRSTROW = first_row ]
 [ , DATEFORMAT = 'date_format' ]
 [ , ENCODING = { 'UTF8' | 'UTF16' } ]
 [ , PARSER_VERSION = { '1.0' | '2.0' } ]
 [ , MATCH_COLUMN_COUNT = { 'ON' | 'OFF' } ]
)

Arguments

warehouse_name

Facultatif si l’entrepôt actuel de l’utilisateur effectuant l’opération est l’entrepôt de la table spécifiée. Si vous ne spécifiez pas d’entrepôt, et que le schéma et la table spécifiés n’existent pas sur l’entrepôt actuel, COPY échoue et qu’un message d’erreur est retourné.

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 vous ne spécifiez pas de schéma 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é.

nom_de_table

Le nom de la table à COPY utiliser pour les données. La table cible doit déjà exister dans l’entrepôt.

(column_list)

Liste facultative des colonnes utilisées pour mapper les champs de données sources aux colonnes de table cible pendant le chargement des données.

column_list doit être placé entre parenthèses et délimité par des virgules. La syntaxe est la suivante :

[(Column_name [Default_value par défaut] [Field_number | JSON_path] [,... n])]

• Column_name : nom de la colonne dans la table cible.
• Default_value - la valeur par défaut qui remplace n’importe quelle NULL valeur dans le fichier d’entrée. Elle s’applique à tous les formats de fichier. COPY Tente de charger NULL depuis le fichier d’entrée lorsqu’une colonne est omise de la liste de colonnes ou lorsqu’un champ de fichier d’entrée est vide. La valeur par défaut est précédée du mot clé 'default'
• Field_number : s’applique uniquement aux fichiers CSV. Spécifie la position ordinale du champ dans les données d’entrée. L’indexation des champs commence au numéro 1.
• JSON_path : s’applique uniquement aux fichiers JSONL. Spécifie une expression de chemin JSON qui identifie le champ à extraire de chaque objet JSON (par exemple). $.CustomerName

Lorsque vous ne spécifiez pas column_list, COPY mappe les colonnes en fonction de l’ordre source et cible : le champ d’entrée 1 passe à la colonne cible 1, le champ 2 passe à la colonne 2, et ainsi de suite.

Lorsque vous ne spécifiez pas de liste de colonnes, COPY mappe les colonnes en fonction de l’ordre source et cible : le champ d’entrée 1 passe à la colonne cible 1, le champ 2 passe à la colonne 2, et ainsi de suite.

Emplacement externe

Spécifie l’emplacement où les fichiers contenant les données sont intermédiaires. Actuellement, Azure Data Lake Storage (ADLS) Gen2, Stockage Blob Azure et OneLake 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\>
• Emplacement externe pour OneLake : https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/

Azure Data Lake Storage (ADLS) Gen2 offre de meilleures performances que Stockage Blob Azure (hérité). Envisagez d’utiliser un compte ADLS Gen2 aussi souvent que possible.

• 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 vous spécifiez un dossier, COPY récupère tous les fichiers du dossier et tous ses sous-dossiers. COPY ignore les dossiers masqués et ne retourne pas de fichiers commençant par un trait de soulignement (_) ou un point (.) sauf indication explicite dans le chemin d’accès. 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
• Vous pouvez échapper à un caractère générique à l’aide du caractère barre oblique inverse (\)

Vous pouvez spécifier plusieurs emplacements de fichiers uniquement à partir du même compte de stockage et du même conteneur via une liste séparée par des virgules, par exemple :

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

Emplacements externes derrière le pare-feu

Pour accéder aux fichiers sur Azure Data Lake Storage (ADLS) Gen2 et les emplacements stockage Blob Azure qui se trouvent derrière un pare-feu, les conditions préalables suivantes s’appliquent :

• Une identité d’espace de travail pour l’espace de travail hébergeant votre entrepôt doit être provisionnée. Pour plus d’informations sur la configuration d’une identité d’espace de travail, consultez l’identité de l’espace de travail.
• Votre compte Entra ID doit pouvoir utiliser l’identité de l’espace de travail.
• Votre compte Entra ID doit avoir accès aux fichiers sous-jacents par le biais du contrôle d’accès en fonction du rôle (RBAC) Azure ou des ACL data lake.
• Votre espace de travail Fabric hébergeant l’entrepôt doit être ajouté en tant que règle d’instance de ressource. Pour plus d’informations sur l’ajout de votre espace de travail Fabric avec une règle d’instance de ressource, consultez la règle d’instance de ressource.

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

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

• CSV : spécifie un fichier de valeurs séparées par des virgules conforme à la norme RFC 4180 .
• JSONL : spécifie un fichier JSON (lignes JSON) délimité par une ligne, où chaque ligne est un objet JSON valide.
• PARQUET : spécifie un format Parquet.

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

CREDENTIAL Spécifie le mécanisme d’authentification permettant d’accéder au compte de stockage externe.

Dans Fabric Data Warehouse :

• COPY INTO n’est pas pris en charge lorsque l’accès public est désactivé.
• Pour les comptes de stockage public, les mécanismes d’authentification pris en charge sont Microsoft Entra ID, signature d’accès partagé (SAS) ou clé de compte de stockage (SAK).
• Pour les comptes de stockage public derrière un pare-feu, l’authentification Microsoft Entra ID est la seule méthode d’authentification prise en charge. COPY INTO l’utilisation de OneLake comme source ne prend en charge que l’authentification EntraID.

L'authentification Microsoft Entra ID de l'utilisateur est par défaut. Aucune information d’identification ne doit être spécifiée.

• Authentification avec signature d’accès partagé (SAP)
  • IDENTITY: constante avec une valeur de 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 la clé de compte de stockage
  • IDENTITY: constante avec une valeur de Storage Account Key.
  • SECRET: Clé de compte de stockage.

ERRORFILE = Emplacement du répertoire

ERRORFILE s’applique à CSV et JSONL. Spécifie le répertoire dans lequel les lignes rejetées et le fichier d’erreur correspondant doivent être écrits. Vous pouvez spécifier le chemin complet à partir du compte de stockage ou du chemin d’accès relatif au conteneur. Si le chemin spécifié n’existe pas, le système en crée un pour votre compte. Un répertoire enfant est créé avec le nom _rejectedrows. Le _ caractère garantit que le répertoire est placé dans une séquence d’échappement pour d’autres traitements de données, sauf si le paramètre d’emplacement est explicitement nommé.

Dans ce répertoire, l’entrepôt crée un dossier basé sur l’heure de chargement de la soumission au format YearMonthDay -HourMinuteSecond (par exemple). 20180330-173205 Dans ce dossier, l’entrepôt crée un dossier avec l’ID d’instruction et, sous ce dossier, deux types de fichiers sont écrits : une erreur. Fichier Json contenant les raisons de rejet et un fichier row.csv contenant les lignes rejetées.

Si ERRORFILE le chemin d’accès complet du compte de stockage est défini, il ERRORFILE_CREDENTIAL est utilisé pour se connecter à ce stockage. Sinon, la valeur mentionnée pour CREDENTIAL est utilisée. Lorsque les informations d’identification utilisées pour les données sources sont utilisées pour ERRORFILEles restrictions qui s’appliquent ERRORFILE_CREDENTIAL également.

Lorsque vous utilisez un compte de stockage Azure protégé par le pare-feu, le fichier d’erreur est créé dans le même conteneur spécifié dans le chemin du compte de stockage. Lorsque vous envisagez d’utiliser l’option ERRORFILE dans ce scénario, il est également nécessaire de spécifier le MAXERROR paramètre. Si ERRORFILE le chemin d’accès complet du compte de stockage est défini, il ERRORFILE_CREDENTIAL est utilisé pour se connecter à ce stockage. Sinon, la valeur mentionnée pour CREDENTIAL est utilisée.

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

ERRORFILE_CREDENTIAL s’applique aux fichiers CSV et JSONL. Sur Warehouse dans Microsoft Fabric, le seul mécanisme d’authentification pris en charge est la signature d’accès partagé (SAP).

• Authentification avec des signatures d’accès partagé (SAP)
  • IDENTITY: constante avec une valeur de 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

MAXERRORS = max_errors

MAXERRORS s’applique à CSV et JSONL. Spécifie le nombre maximal de lignes de rejet autorisées dans la charge avant l’échec de l’opération COPY . Chaque ligne que l’opération COPY ne peut pas importer est ignorée et comptée comme une seule erreur. Si vous ne spécifiez pas un nombre maximal d’erreurs, la valeur par défaut est 0.

Dans Fabric Data Warehouse, vous ne pouvez pas utiliser MAXERRORS lorsque FILE_TYPE est PARQUET.

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

COMPRESSION est facultatif et spécifie la méthode de compression des données pour les données externes.

• CSV prend en charge GZIP.
• Parquet prend en charge GZIP et Snappy.
• Non pris en charge pour JSONL

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

• .gz - GZIP

Le chargement de fichiers compressés est actuellement pris en charge uniquement avec l’analyseur version 1.0.

La COPY commande 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 COPY commande. Pour vous assurer que COPY les exécutions réussissent, veillez à vérifier qu’il n’existe aucune mémoire de fin à la fin des fichiers gzip.

FIELDQUOTE = 'field_quote'

FIELDQUOTE s’applique uniquement au format CSV. Spécifie un caractère unique qui sera utilisé comme guillemet (délimiteur de chaîne) dans le fichier CSV. Si vous ne spécifiez FIELDQUOTEpas, le caractère de guillemet (") est utilisé comme caractère de guillemet tel que défini 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.

FIELDTERMINATOR = 'field_terminator'

FIELDTERMINATOR s’applique uniquement au format CSV. Spécifie la marque de fin de champ à utiliser dans le fichier CSV. Vous pouvez également spécifier la marque de fin de champ à l’aide de la notation hexadécimale. Le terminateur de champ peut être multicharacteur. 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.

ROWTERMINATOR = 'row_terminator'

ROWTERMINATOR s’applique uniquement au format CSV. Spécifie la marque de fin de ligne à utiliser dans le fichier CSV. Vous pouvez spécifier la marque de fin de ligne à l’aide de la notation hexadécimale. Le terminateur de ligne peut être multicharacteur. Les terminateurs par défaut sont \r\n, \net \r.

La COPY commande préfixe le \r caractère lors de la spécification \n (nouvelle ligne), ce qui donne \r\n. Pour spécifier le caractère \n uniquement, utilisez sa notation hexadécimale (0x0A). Lorsque vous spécifiez des terminateurs de ligne multicharacteur en hexadécimal, ne spécifiez pas 0x entre chaque caractère.

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

FIRSTROW = First_row_int

FIRSTROW s’applique uniquement au format CSV. Spécifie le numéro de ligne lu en premier dans tous les fichiers pour la COPY commande. Les valeurs commencent par 1, qui est la valeur par défaut. Si vous définissez la valeur 2sur , 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 au format CSV et JSONL. Spécifie le format de date du mappage de dates aux 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). DATEFORMAT dans la COPY commande prime sur DATEFORMAT configuré au niveau de la session.

ENCODING = 'UTF8' | 'UTF16'

ENCODING s’applique à CSV et JSONL. La valeur par défaut est UTF8. Spécifie la norme d’encodage des données pour les fichiers chargés par la COPY commande.

PARSER_VERSION = { '1.0' | '2.0' }

PARSER_VERSION s’applique uniquement aux fichiers CSV. La valeur par défaut est 2.0. PARSER_VERSION spécifie l’analyseur de fichiers utilisé pour l’ingestion lorsque le type de fichier source est CSV. L’analyseur 2.0 offre des performances améliorées pour l’ingestion de fichiers CSV.

L’analyseur version 2.0 présente les limitations suivantes :

• Les fichiers CSV compressés ne sont pas pris en charge.
• Les fichiers avec encodage UTF-16 ne sont pas pris en charge.
• Multicharacter ou multioctet ROWTERMINATOR, FIELDTERMINATORou FIELDQUOTE n’est pas pris en charge. Cependant, \r\n est accepté comme un défaut ROWTERMINATOR.

Lorsque vous utilisez l’analyseur version 1.0 avec des fichiers UTF-8, les terminateurs multioctets et multicharacteurs ne sont pas pris en charge pour FIELDTERMINATOR.

L’analyseur version 1.0 est disponible uniquement pour la compatibilité descendante. Utilisez-la uniquement lorsque vous rencontrez ces limitations.

MATCH_COLUMN_COUNT = { 'ON' | 'OFF' }

MATCH_COLUMN_COUNT s’applique uniquement aux fichiers CSV. La valeur par défaut est OFF. Elle spécifie si la commande doit vérifier si les COPY lignes du nombre de colonnes dans les fichiers sources correspondent au nombre de colonnes de la table de destination. Le comportement suivant s’applique :

• S’il s’agit MATCH_COLUMN_COUNT de OFF:
  • La commande ignore le dépassement des colonnes des lignes sources.
    • La commande insère des NULL valeurs dans des colonnes nullables pour les lignes avec moins de colonnes.
  • Si une valeur n’est pas fournie à une colonne non nullable, la COPY commande échoue.
• S’il s’agit MATCH_COLUMN_COUNT de ON:
  • La COPY commande vérifie si le nombre de colonnes sur chaque ligne de chaque fichier depuis la source correspond au nombre de colonnes de la table de destination.
• En cas d’incompatibilité de nombre de colonnes, la COPY commande échoue.

Utiliser COPY INTO avec OneLake

Utilisez COPY INTO pour charger des données directement à partir de fichiers stockés dans le Fabric OneLake, sous les éléments existants. Cette méthode élimine la nécessité de comptes intermédiaires externes, tels qu’ADLS Gen2 ou Stockage Blob, et active l’ingestion native SaaS par l’espace de travail à l’aide d’autorisations de Fabric. Cette fonctionnalité prend en charge :

• Lecture à partir de n’importe quel emplacement au sein d’un espace de travail et d’un élément
• Chargements d’espace de travail à entrepôt au sein du même locataire
• Application d’identité native à l’aide de Microsoft Entra ID

Example:

COPY INTO t1
FROM 'https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/*.csv'
WITH (
    FILE_TYPE = 'CSV',
    FIRSTROW = 2
);

Permissions

Autorisations du plan de contrôle

Pour exécuter la commande, vous devez disposer de l’appartenance COPY INTO à un rôle d’espace de travail via Gérer l’accès dans l’espace de travail, avec au moins le rôle Visionneuse. Vous pouvez également partager l’accès à l’entrepôt avec un utilisateur via Item Permissions dans le portail Fabric, avec au moins des autorisations de lecture. Pour s’aligner sur le principe du privilège minimum, l’autorisation Lecture suffit.

Autorisations du plan de données

Après avoir accordé des autorisations de plan de contrôle via des rôles d’espace de travail ou des autorisations d’élément, si l’utilisateur dispose uniquement d’autorisations en lecture au niveau du plan de données, accordez également à l’utilisateur INSERT et ADMINISTER DATABASE BULK OPERATIONS aux autorisations à l’aide de commandes T-SQL.

Par exemple, le script T-SQL suivant accorde ces autorisations à un utilisateur individuel à l’aide de son Microsoft Entra ID.

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

GRANT INSERT to [mike@contoso.com];
GO

Lorsque vous utilisez l’option de fichier d’erreur, l’utilisateur doit disposer de l’autorisation minimale d’Stockage Blob Contributeur sur le conteneur du compte de stockage.

Lorsque vous utilisez OneLake comme source, l’utilisateur doit disposer d’autorisations Contributeur ou supérieures sur l’espace de travail source (où se trouve Lakehouse) et l’espace de travail cible (où réside l’entrepôt). Microsoft Entra ID et les rôles d’espace de travail Fabric régissent tous les accès.

Remarks

L’instruction COPY n’accepte que les caractères valides UTF-8 et UTF-16 pour les lignes de données et les paramètres de commande. Si vous utilisez des fichiers sources ou des paramètres (tels que ROWTERMINATOR ou FIELDTERMINATOR) qui contiennent des caractères non valides, l’instruction COPY peut les interpréter de manière incorrecte et provoquer des résultats inattendus, tels que l’altération des données ou d’autres échecs. Avant d’appeler l’instruction COPY , vérifiez que vos fichiers et paramètres sources sont conformes à UTF-8 ou UTF-16.

L’instruction COPY INTO a des restrictions sur la taille des colonnes varchar(max) et varbinary(max), ainsi que sur la taille totale des lignes que vous pouvez ingérer.

• Parquet : taille maximale de colonne varchar(max)/varbinary(max) 16 Mo, taille maximale de ligne 1 Go.
• CSV et JSONL : taille maximale de colonne varchar(max)/varbinary(max) 1 Mo, taille de ligne maximale de 16 Mo.

Pour garantir une exécution fiable, ne modifiez pas les fichiers et dossiers sources pendant l’opération COPY INTO .

• La modification, la suppression ou le remplacement de tous les fichiers ou dossiers référencés pendant l’exécution de la commande peuvent entraîner l’échec de l’opération ou entraîner une ingestion de données incohérente.
• Avant d’exécuter COPY INTO, vérifiez que toutes les données sources sont stables et qu’elles ne sont pas modifiées pendant le processus.

Si les données sources ont une plus grande précision que la définition de colonne de destination, la valeur est tronquée, non arrondie, pour les types numériques, de date et d’heure.

Limitations pour OneLake en tant que source

• Seule l’authentification Microsoft Entra ID est pris en charge. D’autres méthodes d’authentification, telles que des jetons SAP, des clés partagées ou des chaînes de connexion, ne sont pas autorisées.

• Les éléments de l’entrepôt ne sont pas pris en charge en tant qu’emplacements sources. Les fichiers doivent provenir d’autres éléments fabric qui exposent des fichiers via le stockage OneLake.

• Les chemins OneLake doivent utiliser des ID d’espace de travail et d’entrepôt. Les noms conviviaux pour les espaces de travail ou Lakehouses ne sont pas pris en charge pour l’instant.

• Les autorisations de contributeur sont requises sur les deux espaces de travail. L’utilisateur en cours d’exécution doit avoir au moins le rôle Contributeur sur l’espace de travail Lakehouse source et l’espace de travail de l’entrepôt cible.

Examples

Pour plus d’informations sur l’utilisation COPY INTO de votre entrepôt dans Microsoft Fabric, consultez Ingestion de données dans votre entrepôt dans Microsoft Fabric à l’aide de l’instruction COPY.

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

L’exemple suivant montre la forme la plus simple de la COPY commande, qui charge les données à partir d’un compte de stockage public. Pour cet exemple, les valeurs par défaut de l’instruction COPY correspondent au format du fichier CSV de l’élément de ligne.

COPY INTO dbo.[lineitem]
FROM 'https://unsecureaccount.blob.core.windows.net/customerdatasets/folder1/lineitem.csv'

Les valeurs par défaut de la COPY commande sont :

• MAXERRORS = 0

• COMPRESSION (la valeur par défaut est non compressée)

• FIELDQUOTE = '"'

• FIELDTERMINATOR = ','

• ROWTERMINATOR = '\n'

  Important

  COPY Traite \n comme \r\n à l’intérieur. Pour plus d’informations, consultez la section ROWTERMINATOR.

• FIRSTROW = 1

• ENCODING = 'UTF8'

• FILE_TYPE = 'CSV'

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

L’exemple suivant charge les fichiers qui utilisent le flux de ligne en tant que terminateur de ligne, tel qu’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>'),
    FIELDQUOTE = '"',
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = '0X0A',
    ENCODING = 'UTF8',
    MAXERRORS = 10,
    ERRORFILE = '/errorsfolder'--path starting from the storage container
)

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

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>'),
    FIELDQUOTE = '"',
    FIELDTERMINATOR=',',
    ROWTERMINATOR='0x0A',
    ENCODING = 'UTF8',
    FIRSTROW = 2
)

D. Charger Parquet

Cet exemple utilise un caractère générique pour charger tous les fichiers Parquet sous un dossier à l'aide de la Entra ID de l'utilisateur en cours d'exécution.

COPY INTO test_parquet
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.parquet'
WITH (
    FILE_TYPE = 'PARQUET'
)

E. Charger JSONL

Cet exemple utilise un caractère générique pour charger tous les fichiers JSONL sous un dossier à l'aide du Entra ID de l'utilisateur en cours d'exécution.

COPY INTO test_jsonl
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/*.jsonl'
WITH (
    FILE_TYPE = 'JSONL'
)

F. Mapper les noms de colonnes aux chemins d’accès aux champs dans des documents JSONL

L’exemple suivant montre un fichier JSON Lines (JSONL), où chaque ligne représente un seul objet JSON :

{"CountryKey": 0, "CountryName": "ALGERIA", "RegionID": 0, "Population": 34800000}
{"CountryKey": 1, "CountryName": "ARGENTINA", "RegionID": 1, "Population": 46044703}
{"CountryKey": 2, "CountryName": "BRAZIL", "RegionID": 1, "Population": 203080756}

Dans COPY INTO, vous pouvez mapper des colonnes de table à des champs JSON spécifiques à l’aide d’expressions de chemin JSON. Ce mappage vous permet d’ingérer uniquement les champs requis à partir des données sources.

COPY INTO Countries (
  CountryID '$.CountryKey', 
  CountryName '$.CountryName', 
  RegionID '$.RegionKey', 
  Population '$.Population'
)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/countries.jsonl'
WITH (   
    FILE_TYPE = 'JSONL' 
)

G. Charger des données 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= 'Shared Access Signature', SECRET='<Your_SAS_Token>')
    FIELDTERMINATOR = '|'
)

H. Charger des données à partir de OneLake

COPY INTO t1
FROM 'https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/*.csv'
WITH (
    FILE_TYPE = 'CSV',
    FIRSTROW = 2
);

Contenu connexe

• Ingérer des données dans votre entrepôt dans Microsoft Fabric
• Ingérer des données dans votre entrepôt à l’aide de l’instruction COPY