Stratégies de chargement de données pour un pool SQL dédié dans Azure Synapse Analytics
Les pools SQL dédiés SMP traditionnels utilisent un processus ELT (Extract, Load, and Transform – Extraire, charger et transformer) pour le chargement des données. Synapse SQL, au sein d’Azure Synapse Analytics, utilise une architecture de traitement de requêtes distribuées qui tire parti de la scalabilité et de la flexibilité des ressources de calcul et de stockage.
L’utilisation d’un processus ELT s’appuie sur ses capacités de traitement de requêtes distribuées intégrées et d’éliminer les ressources nécessaires à la transformation des données avant le chargement.
Bien que les pools SQL dédiés 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 scalable pour charger des données est d’utiliser des tables externes PolyBase et l’instruction COPY.
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. Pour une plus grande flexibilité lors du chargement, nous vous recommandons d’utiliser l’instruction COPY.
ELT, qu’est-ce que ça veut dire ?
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 dédié, puis transformées.
Les étapes de base pour implémenter ELT sont les suivantes :
- Extrayez les données sources dans des fichiers texte.
- Placez les données dans le stockage Blob Azure ou Azure Data Lake Store.
- Préparez les données pour le chargement.
- Chargez les données dans des tables de mise en lots avec PolyBase ou la commande COPY.
- Transformez les données.
- Insérez les données dans des tables de production.
Pour suivre un tutoriel sur le chargement, consultez Chargement de données à partir du Stockage Blob Azure.
1. Extraire les données sources dans des fichiers texte
L’extraction des données à partir de votre système source dépend de l’emplacement de stockage. L’objectif est de déplacer les données vers des fichiers texte délimités ou CSV pris en charge.
Formats de fichiers pris en charge
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. 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. PolyBase et l’instruction COPY peuvent aussi charger des données à partir de fichiers compressés Gzip et Snappy.
Le format ASCII étendu de largeur fixe et les formats imbriqués, comme WinZip ou XML, ne sont pas pris en charge. 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.
2. Placer les données dans le stockage Blob Azure ou 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. Quel que soit l’emplacement choisi, les données doivent être stockées dans des fichiers texte. PolyBase et l’instruction COPY peuvent charger depuis l’un ou l’autre emplacement.
Voici des outils et services que vous pouvez utiliser pour déplacer des données dans le stockage Azure.
- Le service Azure ExpressRoute améliore le débit, les performances et la prévisibilité du réseau. ExpressRoute est un service qui achemine vos données via une connexion privée dédiée vers Azure. Les connexions ExpressRoute n’acheminent pas vos données via le réseau Internet public. 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.
- L’utilitaire AzCopy déplace les données vers le stockage Azure via l’Internet public. Il fonctionne si la taille de vos données ne dépasse pas les 10 To. Pour effectuer des chargements réguliers avec AzCopy, testez la vitesse du réseau pour voir si elle est acceptable.
- Azure Data Factory (ADF) dispose d’une passerelle que vous pouvez installer sur votre serveur local. Ensuite, vous pouvez créer un pipeline pour déplacer des données à partir de votre serveur local vers le stockage Azure. Pour utiliser Data Factory avec des pools SQL dédiés, consultez Chargement de données pour des pools SQL dédiés.
3. Préparer les données pour le chargement
Avant de pouvoir charger les données de votre compte de stockage, vous devrez peut-être les préparer et les nettoyer. 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. Plus vous les préparez tôt, plus ce sera facile.
Définir les tables
Vous devez d’abord définir la ou les tables que vous chargez dans votre pool SQL dédié lors de l’utilisation de l’instruction COPY.
Si vous utilisez PolyBase, vous devez définir des tables externes dans votre pool SQL dédié avant le chargement. PolyBase utilise des tables externes pour définir les données dans le stockage Azure et y accéder. Une table externe est similaire à une vue de base de données. La table externe contient le schéma de table et pointe vers les données stockées en dehors du pool SQL dédié.
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. Les articles de référence sur la syntaxe T-SQL dont vous aurez besoin sont les suivants :
Utilisez le mappage de type de données SQL suivant lors du chargement des fichiers Parquet :
| Type Parquet | Type logique Parquet (annotation) | Type de données SQL |
|---|---|---|
| BOOLEAN | bit | |
| BINARY / BYTE_ARRAY | varbinary | |
| DOUBLE | float | |
| FLOAT | real | |
| INT32 | int | |
| INT64 | bigint | |
| INT96 | datetime2 | |
| FIXED_LEN_BYTE_ARRAY | binary | |
| BINARY | UTF8 | NVARCHAR |
| BINARY | STRING | NVARCHAR |
| BINARY | ENUM | NVARCHAR |
| BINARY | UUID | UNIQUEIDENTIFIER |
| BINARY | DECIMAL | Décimal |
| BINARY | JSON | nvarchar(MAX) |
| BINARY | BSON | varbinary(max) |
| FIXED_LEN_BYTE_ARRAY | DECIMAL | Décimal |
| BYTE_ARRAY | INTERVAL | varchar(max), |
| INT32 | INT(8, true) | SMALLINT |
| INT32 | INT(16, true) | SMALLINT |
| INT32 | INT(32, true) | int |
| INT32 | INT(8, false) | TINYINT |
| INT32 | INT(16, false) | int |
| INT32 | INT(32, false) | bigint |
| INT32 | DATE | Date |
| INT32 | DECIMAL | Décimal |
| INT32 | TIME (MILLIS ) | time |
| INT64 | INT(64, true) | bigint |
| INT64 | INT(64, false ) | decimal(20,0) |
| INT64 | DECIMAL | Décimal |
| INT64 | TIME (MILLIS) | time |
| INT64 | TIMESTAMP (MILLIS) | datetime2 |
| Type complexe | Liste | varchar(max) |
| Type complexe | MAP | varchar(max) |
Important
- Les pools dédiés SQL ne prennent actuellement pas en charge les types de données Parquet avec précision MICROS ou NANOS.
- Vous pouvez rencontrer l’erreur suivante si les types ne correspondent pas entre Parquet et SQL ou si vous avez des types de données Parquet non pris en charge :
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: ClassCastException:... - Le chargement d’une valeur en dehors de la plage 0-127 dans une colonne tinyint pour le format de fichier ORC et Parquet n’est pas pris en charge.
Pour un exemple de création d’objets externes, consultezCréer des tables externes.
Formater les fichiers texte
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. Les données de chaque ligne du fichier texte doivent être alignées avec la définition de la table. Pour formater les fichiers texte :
- Si vos données proviennent d’une source non relationnelle, vous devez les transformer en lignes et en colonnes. 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.
- 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. Un décalage entre les types de données dans les fichiers texte externes et la table du pool SQL dédié cause le rejet des lignes lors du chargement.
- Séparez les champs dans le fichier texte à l’aide d’une marque de fin. 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. Utilisez la marque de fin que vous avez spécifiée avec l’instruction CREATE EXTERNAL FILE FORMAT.
4. Charger les données à l’aide de PolyBase ou de l’instruction COPY
Il est recommandé de charger des données dans une table de mise en lots. Les tables de mise en lots vous permettent de gérer les erreurs sans interférer avec les tables de production. Une table de mise en lots vous donne également la possibilité d’utiliser l’architecture de traitement parallèle de pool SQL dédié pour transformer les données avant de les insérer dans des tables de production.
Options de chargement
Pour charger des données, vous pouvez utiliser l’une des options de chargement suivantes :
- L’instruction COPY est l’utilitaire de chargement recommandé, car il vous permet de charger des données de manière fluide et flexible. L’instruction dispose de nombreuses fonctionnalités de chargement supplémentaires non fournies par PolyBase. Consultez le didacticiel COPIE des taxis de New York pour exécuter un exemple de didacticiel.
- Polybase avec T-SQL vous oblige à définir des objets de données externes.
- PolyBase et l’instruction COPY avec Azure Data Factory (ADF) est un autre outil d’orchestration. Il définit un pipeline et planifie les travaux.
- Polybase avec SSIS fonctionne bien lorsque vos données sources se trouvent dans SQL Server. SSIS définit le mappage de la table « source vers destination » et orchestre aussi le chargement. 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.
- 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.
Autres options de chargement
En plus de PolyBase et de l’instruction COPY, vous pouvez utiliser bcp ou l’API SqlBulkCopy. L’outil bcp charge directement dans la base de données sans passer par Stockage Blob Azure et est destiné uniquement aux petits chargements.
Notes
Les performances de chargement de ces options sont beaucoup plus lentes qu’avec PolyBase et l’instruction COPY.
5. Transformer les données
Pendant que les données se trouvent dans la table de mise en lots, effectuez les transformations requises par votre charge de travail. Déplacez ensuite les données dans une table de production.
6. Insérer les données dans des tables de production
L’instruction INSERT INTO... SELECT déplace les données depuis la table de mise en lots vers la table permanente.
Lorsque vous concevez un processus ETL, commencez par exécuter le processus sur un petit échantillon. 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.
Solutions de chargement des partenaires
La plupart de nos partenaires proposent des solutions de chargement. Pour en savoir plus, consultez la liste de nos partenaires de solutions.
Étapes suivantes
Pour obtenir de l’aide concernant le chargement, consultez Meilleures pratiques pour le chargement de données.