Meilleures pratiques pour charger des données dans un pool SQL dédié dans Azure Synapse Analytics

• Démarrez un essai gratuit Fabric.
• Assistant Migration pour Fabric Data Warehouse.

Dans cet article, vous trouverez des recommandations et des optimisations des performances pour le chargement des données.

Préparer des données dans stockage Azure

Pour réduire la latence, colocalisez votre couche de stockage et votre pool SQL dédié.

Lors de l’exportation de données au format de fichier ORC, vous pouvez rencontrer des erreurs de mémoire Java lorsque les colonnes de texte sont volumineuses. Pour contourner cette limitation, exportez uniquement un sous-ensemble des colonnes.

PolyBase ne peut pas charger de lignes qui ont plus de 1 000 000 octets de données. Lorsque vous placez des données dans les fichiers texte du stockage d'objets blob Azure ou d'Azure Data Lake Store, elles doivent avoir moins de 1 000 000 octets de données. Cette limitation d’octets est vraie, quel que soit le schéma de table.

Tous les formats de fichier ont des caractéristiques de performances différentes. Pour une charge la plus rapide, utilisez des fichiers texte délimités compressés. La différence entre les performances UTF-8 et UTF-16 est minimale.

Fractionnez les fichiers compressés volumineux en fichiers compressés plus petits.

Exécuter des charges de travail avec suffisamment de capacité de calcul

Pour une vitesse de chargement la plus rapide, exécutez une seule tâche de chargement à la fois. Si cela n’est pas possible, exécutez un nombre minimal de charges simultanément. Si vous attendez un travail de chargement volumineux, envisagez de monter en puissance votre pool SQL dédié avant la charge.

Pour exécuter des charges avec les ressources de calcul appropriées, créez des utilisateurs chargés pour l’exécution des charges. Affectez chaque utilisateur de chargement à une classe de ressources ou à un groupe de charge de travail spécifique. Pour exécuter une charge, connectez-vous en tant qu’un des utilisateurs chargés, puis exécutez la charge. La charge s’exécute avec la classe de ressources de l’utilisateur. Cette méthode est plus simple que d’essayer de modifier la classe de ressource d’un utilisateur pour qu’elle corresponde au besoin de la classe de ressources actuelle.

Créer un utilisateur de chargement de données

Cet exemple crée un utilisateur de chargement classé dans un groupe de charge de travail spécifique. La première étape consiste à se connecter à master et à créer une connexion.

   -- Connect to master
   CREATE LOGIN loader WITH PASSWORD = 'a123STRONGpassword!';

Connectez-vous au pool SQL dédié et créez un utilisateur. Le code suivant suppose que vous êtes connecté à la base de données appelée mySampleDataWarehouse. Il montre comment créer un utilisateur appelé chargeur et lui accorder les autorisations nécessaires pour créer des tables et charger à l’aide de l’instruction COPY. Il classifie ensuite l’utilisateur au groupe de charge de travail DataLoads avec des ressources maximales.

   -- Connect to the dedicated SQL pool
   CREATE USER loader FOR LOGIN loader;
   GRANT ADMINISTER DATABASE BULK OPERATIONS TO loader;
   GRANT INSERT ON <yourtablename> TO loader;
   GRANT SELECT ON <yourtablename> TO loader;
   GRANT CREATE TABLE TO loader;
   GRANT ALTER ON SCHEMA::dbo TO loader;
   
   CREATE WORKLOAD GROUP DataLoads
   WITH ( 
       MIN_PERCENTAGE_RESOURCE = 0
       ,CAP_PERCENTAGE_RESOURCE = 100
       ,REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
	);

   CREATE WORKLOAD CLASSIFIER [wgcELTLogin]
   WITH (
	     WORKLOAD_GROUP = 'DataLoads'
       ,MEMBERNAME = 'loader'
   );

Pour exécuter une charge avec des ressources pour le groupe de charge de travail, connectez-vous en tant qu'utilisateur chargeur et lancez la charge.

Autoriser plusieurs utilisateurs à charger

Il est souvent nécessaire que plusieurs utilisateurs chargent des données dans un entrepôt de données. Le chargement avec le CREATE TABLE AS SELECT (Transact-SQL) nécessite des permissions CONTROL de la base de données. L'autorisation CONTROL permet le contrôle de tous les schémas. Vous ne souhaiterez peut-être pas que tous les utilisateurs chargés aient un accès de contrôle sur tous les schémas. Pour limiter les autorisations, utilisez l’instruction DENY CONTROL.

Par exemple, considérez les schémas de base de données, schema_A pour le département A et schema_B pour le département B. Considérons que les utilisateurs de base de données user_A et user_B sont des utilisateurs pour le chargement avec PolyBase dans le département A et B, respectivement. Ils ont tous deux reçu des autorisations de base de données CONTROL. Les créateurs de schéma A et B verrouillent désormais leurs schémas à l’aide de DENY :

   DENY CONTROL ON SCHEMA :: schema_A TO user_B;
   DENY CONTROL ON SCHEMA :: schema_B TO user_A;

User_A et user_B sont désormais verrouillés hors du schéma de l'autre département.

Charger dans une table intermédiaire

Pour atteindre la vitesse de chargement la plus rapide pour déplacer des données dans une table d’entrepôt de données, chargez les données dans une table intermédiaire. Définissez la table intermédiaire en tant que tas et utilisez le "round-robin" pour l’option de distribution.

Considérez que le chargement est généralement un processus en deux étapes dans lequel vous chargez d’abord dans une table intermédiaire, puis insérez les données dans une table d’entrepôt de données de production. Si la table de production utilise une distribution de hachage, le temps total de chargement et d'insertion pourrait être plus rapide si vous définissez la table de mise en scène avec une distribution de hachage. Le chargement dans la table intermédiaire prend plus de temps, mais la deuxième étape de l’insertion des lignes dans la table de production n’entraîne pas de déplacement des données entre les distributions.

Charger dans un index columnstore

Les index Columnstore nécessitent de grandes quantités de mémoire pour compresser les données dans des rowgroups de haute qualité. Pour optimiser la compression et l’efficacité des index, l’index columnstore doit compresser le maximum de 1 048 576 lignes dans chaque rowgroup. En cas de sollicitation de la mémoire, l’index columnstore peut ne pas être en mesure d’atteindre des taux de compression maximum. Cela affecte les performances des requêtes. Pour une exploration approfondie, consultez les optimisations mémoire de columnstore.

• Pour vous assurer que l’utilisateur de chargement a suffisamment de mémoire pour atteindre les taux de compression maximum, utilisez le chargement d’utilisateurs membres d’une classe de ressources moyenne ou grande.
• Chargez suffisamment de lignes pour remplir complètement de nouveaux rowgroups. Pendant un chargement par lots, toutes les 1 048 576 lignes sont compressées directement dans le columnstore en tant que groupe de lignes complet. Les chargements de moins de 102 400 lignes transfèrent ces lignes vers le deltastore, où elles sont conservées dans un index de type b-tree. Si vous chargez trop peu de lignes, elles pourraient toutes être dirigées vers le deltastore et ne pas être compressées immédiatement au format columnstore.

Augmenter la taille des lots lors de l’utilisation de l’API SQLBulkCopy ou BCP

Le chargement avec l’instruction COPY fournit le débit le plus élevé avec des pools SQL dédiés. Si vous ne pouvez pas utiliser COPY pour charger et que vous devez utiliser l’API SqLBulkCopy ou bcp, il serait préférable d’envisager d’augmenter la taille des lots afin d'améliorer le débit.

Gérer les échecs de chargement

Une charge à l’aide d’une table externe peut échouer avec l’erreur « Requête abandonnée : le seuil maximal de rejet a été atteint lors de la lecture à partir d’une source externe ». Ce message indique que vos données externes contiennent des enregistrements incorrects. Un enregistrement de données est considéré comme incorrect si les types de données et le nombre de colonnes ne correspondent pas aux définitions de colonnes de la table externe, ou si les données ne sont pas conformes au format de fichier externe spécifié.

Pour corriger les enregistrements incorrects, vérifiez que vos définitions de format de fichier externe et de table externe sont correctes et que vos données externes sont conformes à ces définitions. Si un sous-ensemble d’enregistrements de données externes est incorrect, vous pouvez choisir de rejeter ces enregistrements pour vos requêtes à l’aide des options de rejet dans « CREATE EXTERNAL TABLE ».

Insérer des données dans une table de production

Une charge ponctuelle sur une petite table avec une instruction INSERT, ou même un rechargement périodique d’une recherche peut être suffisamment efficace avec une instruction comme INSERT INTO MyLookup VALUES (1, 'Type 1'). Toutefois, les insertions individuelles ne sont pas aussi efficaces que l’exécution d’une charge en bloc.

Si vous avez des milliers ou plusieurs insertions uniques tout au long de la journée, lotez les insertions afin de pouvoir les charger en bloc. Développez vos processus pour ajouter les insertions uniques à un fichier, puis créez un autre processus qui charge régulièrement le fichier.

Créer des statistiques après le chargement

Pour améliorer les performances des requêtes, il est important de créer des statistiques sur toutes les colonnes de toutes les tables après la première charge, ou des modifications majeures se produisent dans les données. Vous pouvez créer des statistiques manuellement ou activer la création automatique des statistiques.

Pour obtenir une explication détaillée des statistiques, consultez Statistiques. L’exemple suivant montre comment créer manuellement des statistiques sur cinq colonnes de la table Customer_Speed.

create statistics [SensorKey] on [Customer_Speed] ([SensorKey]);
create statistics [CustomerKey] on [Customer_Speed] ([CustomerKey]);
create statistics [GeographyKey] on [Customer_Speed] ([GeographyKey]);
create statistics [Speed] on [Customer_Speed] ([Speed]);
create statistics [YearMeasured] on [Customer_Speed] ([YearMeasured]);

Faire pivoter les clés de stockage

Il est conseillé de changer régulièrement la clé d’accès à votre stockage Blob. Vous disposez de deux clés de stockage pour votre compte de stockage d'objets blob, ce qui vous permet de faire la transition des clés.

Pour faire tourner les clés de compte stockage Azure :

Pour chaque compte de stockage dont la clé a changé, émettez ALTER DATABASE SCOPED CREDENTIAL.

Exemple :

La clé d’origine est créée

CREATE DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key1'

Faire pivoter la clé de la clé 1 à la clé 2

ALTER DATABASE SCOPED CREDENTIAL my_credential WITH IDENTITY = 'my_identity', SECRET = 'key2'

Aucune autre modification apportée aux sources de données externes sous-jacentes n’est nécessaire.

Contenu connexe

• Pour en savoir plus sur PolyBase et la conception d’un processus d’extraction, de chargement et de transformation (ELT), consultez Design ELT pour Azure Synapse Analytics.
• Pour un didacticiel de chargement, utilisez PolyBase pour charger des données à partir du stockage Blob Azure vers Azure Synapse Analytics.
• Pour surveiller les chargements de données, consultez Surveiller votre charge de travail à l’aide de DMV.