Concevoir une stratégie de chargement de données PolyBase pour un pool SQL dédié dans Azure Synapse Analytics

  • Article

Les entrepôts de données SMP traditionnels utilisent un processus d’extraction, transformation et chargement (ETL) pour le chargement des données. Un pool Azure SQL est 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. L’utilisation d’un processus ELT permet de tirer parti des 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 le pool SQL prenne en charge de nombreuses méthodes de chargement, notamment des options non-Polybase, telles que les API BCP et SQL BulkCopy, le moyen le plus rapide et le plus évolutif de charger des données consiste à utiliser PolyBase. PolyBase est une technologie qui accède aux données externes stockées dans Stockage Blob Azure ou Azure Data Lake Store par le biais du langage T-SQL.

Extraire, charger et transformer (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 entrepôt de données, puis transformées.

Les étapes de base pour implémenter un processus ELT PolyBase pour un pool SQL dédié sont les suivantes :

  1. Extrayez les données sources dans des fichiers texte.
  2. Placez les données dans le stockage Blob Azure ou Azure Data Lake Store.
  3. Préparez les données pour le chargement.
  4. Chargez les données dans les tables de mise en lots du pool SQL dédié à l’aide de PolyBase.
  5. Transformez les données.
  6. Insérez les données dans des tables de production.

Pour suivre un tutoriel sur le chargement, consultez l’article Utiliser PolyBase pour charger des données du Stockage Blob Azure dans Azure Synapse Analytics.

Pour en savoir plus, voir l’article de blog Loading patterns (Modèles de charge).

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 pris en charge par PolyBase.

Formats des fichiers externes PolyBase

PolyBase charge les données à partir de fichiers texte encodés avec UTF-8 et UTF-16. PolyBase charge également les données à partir des formats de fichier Hadoop RC, ORC et Parquet. PolyBase peut également charger des données à partir de fichiers compressés Gzip et Snappy. PolyBase ne prend actuellement pas en charge le codage ASCII étendu, le format de largeur fixe et les formats imbriqués tels que WinZip, JSON et XML.

Si vous exportez des données depuis SQL Server, vous pouvez utiliser l’outil en ligne de commande bcp pour exporter les données dans des fichiers texte délimités. Le mappage du type de données Parquet à Azure Synapse Analytics est le suivant :

Type de données Parquet Type de données SQL
TINYINT TINYINT
SMALLINT SMALLINT
int int
bigint bigint
boolean bit
double float
float real
double money
double SMALLMONEY
string NCHAR
string NVARCHAR
string char
string varchar
binary binary
binary varbinary
timestamp Date
timestamp smalldatetime
timestamp datetime2
timestamp DATETIME
timestamp time
Date Date
Décimal Décimal

2. Placer les données dans le stockage Blob Azure ou Azure Data Lake Store

Pour charger les données dans le stockage Azure, vous pouvez les déplacer dans le stockage Blob Azure ou dans Azure Data Lake Store. Quel que soit l’emplacement choisi, les données doivent être stockées dans des fichiers texte. PolyBase peut effectuer le chargement à partir des deux emplacements.

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 le pool SQL dédié, consultez Charger des données dans un pool SQL dédié.

3. Préparer les données pour le chargement

Avant de pouvoir charger les données de votre compte de stockage dans le pool SQL dédié, 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 des tables externes

Avant de pouvoir charger des données, vous devez définir des tables externes dans votre entrepôt de données. 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 de l’entrepôt de données.

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. Voici les rubriques sur la syntaxe T-SQL dont vous aurez besoin :

Formater les fichiers texte

Une fois les objets externes définis, vous devez 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 du pool SQL. Un décalage entre les types de données dans les fichiers texte externes et la table de l’entrepôt de données 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 dans les tables de mise en lots du pool SQL dédié à l’aide de PolyBase

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 les capacités de traitement des requêtes distribuées intégrées au pool SQL pour transformer les données avant de les insérer dans des tables de production.

Options de chargement avec PolyBase

Pour charger des données avec PolyBase, vous pouvez utiliser l’une des options de chargement suivantes :

  • PolyBase avec T-SQL fonctionne bien lorsque vos données se trouvent dans le stockage Blob Azure ou dans Azure Data Lake Store. Il vous offre un contrôle optimal sur le processus de chargement, mais nécessite également que vous définissiez des objets de données externes. Les autres méthodes définissent ces objets en arrière-plan pendant que vous mappez les tables sources vers les tables de destination. Pour orchestrer les chargements T-SQL, vous pouvez utiliser Azure Data Factory, SSIS ou les fonctions Azure.
  • Polybase avec SSIS fonctionne bien quand 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 Data Factory (ADF) est un autre outil d’orchestration. Il définit un pipeline et planifie les travaux.
  • PolyBase avec Azure Databricks transfère les données d’une table Azure Synapse Analytics vers une trame de données Databricks et/ou écrit des données d’une trame de données Databricks dans une table Azure Synapse Analytics à l’aide de la technologie PolyBase.

Options de chargement non-PolyBase

Si vos données ne sont pas compatibles avec PolyBase, vous pouvez utiliser l’outil bcp ou l’API SQLBulkCopy. L’outil BCP charge directement les données dans le pool SQL dédié sans passer par la solution Stockage Blob Azure et est destiné uniquement aux petits chargements. Notez que les performances de chargement de ces options sont plus lentes que celles de PolyBase.

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 des instructions sur le chargement, consultez Meilleures pratiques de chargement de données.