Stratégies de chargement des données pour le pool SQL SynapseData loading strategies for Synapse SQL pool

Les pools SQL SMP traditionnels utilisent un processus ELT (Extract, Load, and Transform – Extraire, charger et transformer) pour le chargement des données.Traditional SMP SQL pools use an Extract, Transform, and Load (ETL) process for loading data. Le pool SQL Synapse, au sein d’Azure Synapse Analytics, offre une architecture de traitement massivement parallèle (MPP) qui tire parti de la scalabilité et de la flexibilité des ressources de calcul et de stockage.Synapse SQL pool, within Azure Synapse Analytics, has a massively parallel processing (MPP) architecture that takes advantage of the scalability and flexibility of compute and storage resources.

L’utilisation d’un processus ELT s’appuie sur MPP et d’éliminer les ressources nécessaires à la transformation des données avant le chargement.Using an Extract, Load, and Transform (ELT) process leverages MPP and eliminates the resources needed for data transformation prior to loading.

Bien que les pools SQL prennent en charge de nombreuses méthodes de chargement, notamment des options SQL Server bien connues, comme bcp et l’API SqlBulkCopy, le moyen le plus rapide et le plus évolutif pour charger des données est d’utiliser des tables externes PolyBase et l’instruction COPY.While SQL pool supports many loading methods, including popular SQL Server options such as bcp and the SqlBulkCopy API, the fastest and most scalable way to load data is through PolyBase external tables and the COPY statement.

Avec PolyBase et l’instruction COPY, vous pouvez accéder à des données externes stockées dans Stockage Blob Azure ou dans Azure Data Lake Store via le langage T-SQL.With PolyBase and the COPY statement, you can access external data stored in Azure Blob storage or Azure Data Lake Store via the T-SQL language. Pour une plus grande flexibilité lors du chargement, nous vous recommandons d’utiliser l’instruction COPY.For the most flexibility when loading, we recommend using the COPY statement.

ELT, qu’est-ce que ça veut dire ?What is ELT?

ELT (Extract, Load, and Transform – Extraire, charger et transformer) est un processus par lequel des données sont extraites d’un système source, chargées dans un pool SQL, puis transformées.Extract, Load, and Transform (ELT) is a process by which data is extracted from a source system, loaded into a SQL pool, and then transformed.

Les étapes de base pour implémenter ELT sont les suivantes :The basic steps for implementing ELT are:

  1. Extrayez les données sources dans des fichiers texte.Extract the source data into text files.
  2. Placez les données dans le stockage Blob Azure ou Azure Data Lake Store.Land the data into Azure Blob storage or Azure Data Lake Store.
  3. Préparez les données pour le chargement.Prepare the data for loading.
  4. Chargez les données dans des tables de mise en lots avec PolyBase ou la commande COPY.Load the data into staging tables with PolyBase or the COPY command.
  5. Transformez les données.Transform the data.
  6. Insérez les données dans des tables de production.Insert the data into production tables.

Pour suivre un tutoriel sur le chargement, consultez Chargement de données à partir du Stockage Blob Azure.For a loading tutorial, see loading data from Azure blob storage.

1. Extraire les données sources dans des fichiers texte1. Extract the source data into text files

L’extraction des données à partir de votre système source dépend de l’emplacement de stockage.Getting data out of your source system depends on the storage location. L’objectif est de déplacer les données vers des fichiers texte délimités ou CSV pris en charge.The goal is to move the data into supported delimited text or CSV files.

Formats de fichiers pris en chargeSupported file formats

Avec PolyBase et l’instruction COPY, vous pouvez charger des données à partir de fichiers texte ou CSV encodés au format UTF-8 et UTF-16.With PolyBase and the COPY statement, you can load data from UTF-8 and UTF-16 encoded delimited text or CSV files. En plus des fichiers texte délimités ou CSV, il charge des données à partir de formats de fichiers Hadoop, comme ORC et Parquet.In addition to delimited text or CSV files, it loads from the Hadoop file formats such as ORC and Parquet. PolyBase et l’instruction COPY peuvent aussi charger des données à partir de fichiers compressés Gzip et Snappy.PolyBase and the COPY statement can also load data from Gzip and Snappy compressed files.

Le format ASCII étendu de largeur fixe et les formats imbriqués, comme WinZip ou XML, ne sont pas pris en charge.Extended ASCII, fixed-width format, and nested formats such as WinZip or XML aren't supported. Si vous exportez à partir de SQL Server, vous pouvez utiliser l’outil en ligne de commande bcp pour exporter les données dans des fichiers texte délimités.If you're exporting from SQL Server, you can use the bcp command-line tool to export the data into delimited text files.

2. Placer les données dans le stockage Blob Azure ou Azure Data Lake Store2. Land the data into Azure Blob storage or Azure Data Lake Store

Pour charger les données dans Stockage Azure, vous pouvez les déplacer dans Stockage Blob Azure ou dans Azure Data Lake Store Gen2.To land the data in Azure storage, you can move it to Azure Blob storage or Azure Data Lake Store Gen2. Quel que soit l’emplacement choisi, les données doivent être stockées dans des fichiers texte.In either location, the data should be stored in text files. PolyBase et l’instruction COPY peuvent charger depuis l’un ou l’autre emplacement.PolyBase and the COPY statement can load from either location.

Voici des outils et services que vous pouvez utiliser pour déplacer des données dans le stockage Azure.Tools and services you can use to move data to Azure Storage:

  • Le service Azure ExpressRoute améliore le débit, les performances et la prévisibilité du réseau.Azure ExpressRoute service enhances network throughput, performance, and predictability. ExpressRoute est un service qui achemine vos données via une connexion privée dédiée vers Azure.ExpressRoute is a service that routes your data through a dedicated private connection to Azure. Les connexions ExpressRoute n’acheminent pas vos données via le réseau Internet public.ExpressRoute connections do not route data through the public internet. Elles offrent davantage de fiabilité, des vitesses supérieures, des latences inférieures et une sécurité renforcée par rapport aux connexions publiques sur Internet.The connections offer more reliability, faster speeds, lower latencies, and higher security than typical connections over the public internet.
  • L’utilitaire AZCopy déplace les données vers le stockage Azure via l’Internet public.AZCopy utility moves data to Azure Storage over the public internet. Il fonctionne si la taille de vos données ne dépasse pas les 10 To.This works if your data sizes are less than 10 TB. Pour effectuer des chargements réguliers avec AZCopy, testez la vitesse du réseau pour voir si elle est acceptable.To perform loads on a regular basis with AZCopy, test the network speed to see if it is acceptable.
  • Azure Data Factory (ADF) dispose d’une passerelle que vous pouvez installer sur votre serveur local.Azure Data Factory (ADF) has a gateway that you can install on your local server. Ensuite, vous pouvez créer un pipeline pour déplacer des données à partir de votre serveur local vers le stockage Azure.Then you can create a pipeline to move data from your local server up to Azure Storage. Pour utiliser Data Factory avec le pool SQL, consultez Charger des données pour un pool SQL.To use Data Factory with SQL pool, see Loading data for SQL pool.

3. Préparer les données pour le chargement3. Prepare the data for loading

Avant de pouvoir charger les données de votre compte de stockage, vous devrez peut-être les préparer et les nettoyer.You might need to prepare and clean the data in your storage account before loading. Vous pouvez préparer vos données pendant qu’elles sont dans la source, pendant que vous exportez les données dans des fichiers texte ou une fois que les données se trouvent dans le stockage Azure.Data preparation can be performed while your data is in the source, as you export the data to text files, or after the data is in Azure Storage. Plus vous les préparez tôt, plus ce sera facile.It is easiest to work with the data as early in the process as possible.

Définir les tablesDefine the tables

Vous devez d’abord définir la ou les tables que vous chargez dans votre pool SQL lors de l’utilisation de l’instruction COPY.You must first defined the table(s) you are loading to in your SQL pool when using the COPY statement.

Si vous utilisez PolyBase, vous devez définir des tables externes dans votre pool SQL avant le chargement.If you are using PolyBase, you need to define external tables in your SQL pool before loading. PolyBase utilise des tables externes pour définir les données dans le stockage Azure et y accéder.PolyBase uses external tables to define and access the data in Azure Storage. Une table externe est similaire à une vue de base de données.An external table is similar to a database view. La table externe contient le schéma de table et pointe vers les données stockées en dehors du pool SQL.The external table contains the table schema and points to data that is stored outside the SQL pool.

La définition des tables externes implique de spécifier la source des données, le format des fichiers texte et les définitions de la table.Defining external tables involves specifying the data source, the format of the text files, and the table definitions. Les articles de référence sur la syntaxe T-SQL dont vous aurez besoin sont les suivants :T-SQL syntax reference articles that you will need are:

Utilisez le mappage de type de données SQL suivant lors du chargement des fichiers Parquet :Use the following SQL data type mapping when loading Parquet files:

Type ParquetParquet type Type logique Parquet (annotation)Parquet logical type (annotation) Type de données SQLSQL data type
BOOLEANBOOLEAN bitbit
BINARY / BYTE_ARRAYBINARY / BYTE_ARRAY varbinaryvarbinary
DOUBLEDOUBLE floatfloat
FLOATFLOAT realreal
INT32INT32 intint
INT64INT64 bigintbigint
INT96INT96 datetime2datetime2
FIXED_LEN_BYTE_ARRAYFIXED_LEN_BYTE_ARRAY binarybinary
BINARYBINARY UTF8UTF8 NVARCHARnvarchar
BINARYBINARY STRINGSTRING NVARCHARnvarchar
BINARYBINARY ENUMENUM NVARCHARnvarchar
BINARYBINARY UUIDUUID UNIQUEIDENTIFIERuniqueidentifier
BINARYBINARY DECIMALDECIMAL Décimaldecimal
BINARYBINARY JSONJSON nvarchar(MAX)nvarchar(MAX)
BINARYBINARY BSONBSON varbinary(max)varbinary(max)
FIXED_LEN_BYTE_ARRAYFIXED_LEN_BYTE_ARRAY DECIMALDECIMAL Décimaldecimal
BYTE_ARRAYBYTE_ARRAY INTERVALINTERVAL varchar(max),varchar(max),
INT32INT32 INT(8, true)INT(8, true) SMALLINTsmallint
INT32INT32 INT(16, true)INT(16, true) SMALLINTsmallint
INT32INT32 INT(32, true)INT(32, true) intint
INT32INT32 INT(8, false)INT(8, false) TINYINTtinyint
INT32INT32 INT(16, false)INT(16, false) intint
INT32INT32 INT(32, false)INT(32, false) bigintbigint
INT32INT32 DATEDATE Datedate
INT32INT32 DECIMALDECIMAL Décimaldecimal
INT32INT32 TIME (MILLIS )TIME (MILLIS ) timetime
INT64INT64 INT(64, true)INT(64, true) bigintbigint
INT64INT64 INT(64, false )INT(64, false ) decimal(20,0)decimal(20,0)
INT64INT64 DECIMALDECIMAL Décimaldecimal
INT64INT64 TIME (MICROS / NANOS)TIME (MICROS / NANOS) timetime
INT64INT64 TIMESTAMP (MILLIS / MICROS / NANOS)TIMESTAMP (MILLIS / MICROS / NANOS) datetime2datetime2
Type complexeComplex type ListeLIST varchar(max)varchar(max)
Type complexeComplex type MAPMAP varchar(max)varchar(max)

Pour un exemple de création d’objets externes, consultezCréer des tables externes.For an example of creating external objects, see Create external tables.

Formater les fichiers texteFormat text files

Si vous utilisez PolyBase, les objets externes définis doivent aligner les lignes des fichiers texte avec la table externe et la définition du format de fichier.If you are using PolyBase, the external objects defined need to align the rows of the text files with the external table and file format definition. Les données de chaque ligne du fichier texte doivent être alignées avec la définition de la table.The data in each row of the text file must align with the table definition. Pour formater les fichiers texte :To format the text files:

  • Si vos données proviennent d’une source non relationnelle, vous devez les transformer en lignes et en colonnes.If your data is coming from a non-relational source, you need to transform it into rows and columns. Que les données proviennent d’une source relationnelle ou non relationnelle, elles doivent être transformées pour être alignées avec les définitions des colonnes pour la table dans laquelle vous souhaitez les charger.Whether the data is from a relational or non-relational source, the data must be transformed to align with the column definitions for the table into which you plan to load the data.
  • Formatez les données dans le fichier texte pour les aligner avec les types de colonnes et de données dans la table de destination.Format data in the text file to align with the columns and data types in the destination table. Un décalage entre les types de données dans les fichiers texte externes et la table du pool SQL cause le rejet des lignes lors du chargement.Misalignment between data types in the external text files and the SQL pool table causes rows to be rejected during the load.
  • Séparez les champs dans le fichier texte à l’aide d’une marque de fin.Separate fields in the text file with a terminator. Assurez-vous d’utiliser un caractère ou une séquence de caractères qui ne se trouve pas dans votre source de données.Be sure to use a character or a character sequence that isn't found in your source data. Utilisez la marque de fin que vous avez spécifiée avec l’instruction CREATE EXTERNAL FILE FORMAT.Use the terminator you specified with CREATE EXTERNAL FILE FORMAT.

4. Charger les données à l’aide de PolyBase ou de l’instruction COPY4. Load the data using PolyBase or the COPY statement

Il est recommandé de charger des données dans une table de mise en lots.It is best practice to load data into a staging table. Les tables de mise en lots vous permettent de gérer les erreurs sans interférer avec les tables de production.Staging tables allow you to handle errors without interfering with the production tables. Une table de mise en lots vous donne également la possibilité d’utiliser l’architecture de traitement parallèle de pool SQL pour transformer les données avant de les insérer dans des tables de production.A staging table also gives you the opportunity to use the SQL pool parallel processing architecture for data transformations before inserting the data into production tables.

Options de chargementOptions for loading

Pour charger des données, vous pouvez utiliser l’une des options de chargement suivantes :To load data, you can use any of these loading options:

  • L’instruction COPY est l’utilitaire de chargement recommandé, car il vous permet de charger des données de manière fluide et flexible.The COPY statement is the recommended loading utility as it enables you to seamlessly and flexibly load data. L’instruction dispose de nombreuses fonctionnalités de chargement supplémentaires non fournies par PolyBase.The statement has many additional loading capabilities that PolyBase does not provide.
  • Polybase avec T-SQL vous oblige à définir des objets de données externes.PolyBase with T-SQL requires you to define external data objects.
  • PolyBase et l’instruction COPY avec Azure Data Factory (ADF) est un autre outil d’orchestration.PolyBase and COPY statement with Azure Data Factory (ADF) is another orchestration tool. Il définit un pipeline et planifie les travaux.It defines a pipeline and schedules jobs.
  • Polybase avec SSIS fonctionne bien lorsque vos données sources se trouvent dans SQL Server.PolyBase with SSIS works well when your source data is in SQL Server. SSIS définit le mappage de la table « source vers destination » et orchestre aussi le chargement.SSIS defines the source to destination table mappings, and also orchestrates the load. Si vous disposez déjà de packages SSIS, vous pouvez modifier les packages pour travailler avec le nouvel entrepôt de données de destination.If you already have SSIS packages, you can modify the packages to work with the new data warehouse destination.
  • PolyBase avec Azure Databricks transfère les données d’une table vers une trame de données Databricks et/ou écrit des données d’une trame de données Databricks dans une table à l’aide de la technologie PolyBase.PolyBase with Azure Databricks transfers data from a table to a Databricks dataframe and/or writes data from a Databricks dataframe to a table using PolyBase.

Autres options de chargementOther loading options

En plus de PolyBase et de l’instruction COPY, vous pouvez utiliser bcp ou l’API SqlBulkCopy.In addition to PolyBase and the COPY statement, you can use bcp or the SqlBulkCopy API. L’outil bcp charge directement dans la base de données sans passer par Stockage Blob Azure et est destiné uniquement aux petits chargements.bcp loads directly to the database without going through Azure Blob storage, and is intended only for small loads.

Notes

Les performances de chargement de ces options sont beaucoup plus lentes qu’avec PolyBase et l’instruction COPY.The load performance of these options is slower than PolyBase and the COPY statement.

5. Transformer les données5. Transform the data

Pendant que les données se trouvent dans la table de mise en lots, effectuez les transformations requises par votre charge de travail.While data is in the staging table, perform transformations that your workload requires. Déplacez ensuite les données dans une table de production.Then move the data into a production table.

6. Insérer les données dans des tables de production6. Insert the data into production tables

L’instruction INSERT INTO... SELECT déplace les données depuis la table de mise en lots vers la table permanente.The INSERT INTO ... SELECT statement moves the data from the staging table to the permanent table.

Lorsque vous concevez un processus ETL, commencez par exécuter le processus sur un petit échantillon.As you design an ETL process, try running the process on a small test sample. Essayez d’extraire 1 000 lignes de la table dans un fichier, déplacez-le vers Azure, puis essayez de le charger dans une table de mise en lots.Try extracting 1000 rows from the table to a file, move it to Azure, and then try loading it into a staging table.

Solutions de chargement des partenairesPartner loading solutions

La plupart de nos partenaires proposent des solutions de chargement.Many of our partners have loading solutions. Pour en savoir plus, consultez la liste de nos partenaires de solutions.To find out more, see a list of our solution partners.

Étapes suivantesNext steps

Pour le chargement des instructions, consultez Guidance for loading data (Conseils relatifs au chargement des données).For loading guidance, see Guidance for loading data.