Copier et transformer des données dans Azure Synapse Analytics à l’aide de pipelines Azure Data Factory ou Synapse

  • Article

S’APPLIQUE À : Azure Data Factory Azure Synapse Analytics

Conseil

Essayez Data Factory dans Microsoft Fabric, une solution d’analyse tout-en-un pour les entreprises. Microsoft Fabric couvre tous les aspects, du déplacement des données à la science des données, en passant par l’analyse en temps réel, l’aide à la décision et la création de rapports. Découvrez comment démarrer un nouvel essai gratuitement !

Cet article explique comment utiliser l’activité de copie dans un pipeline Azure Data Factory ou Synapse pour copier des données depuis et vers Azure Synapse Analytics et utiliser Data Flow pour transformer les données dans Azure Data Lake Storage Gen2. Pour en savoir plus sur Azure Data Factory, lisez l’article d’introduction.

Fonctionnalités prises en charge

Ce connecteur Azure Synapse Analytics est pris en charge pour les fonctionnalités suivantes :

Fonctionnalités prises en charge IR Point de terminaison privé managé
Activité de copie (source/récepteur) ① ② ✓
Mappage de flux de données (source/récepteur) ① ✓
Activité de recherche ① ② ✓
Activité GetMetadata ① ② ✓
Activité de script ① ② ✓
Activité de procédure stockée ① ② ✓

① runtime d’intégration Azure ② runtime d’intégration auto-hébergé

Pour l’activité de copie, ce connecteur Azure Synapse Analytics prend en charge les fonctions suivantes :

  • Copier des données à l’aide de l’authentification SQL et de l’authentification du jeton de l’application Microsoft Entra avec un principal de service ou l’identité managée pour les ressources Azure.
  • En tant que source, récupération de données à l’aide d’une requête SQL ou d’une procédure stockée. Vous pouvez également choisir de copier en parallèle à partir de la source Azure Synapse Analytics. Pour plus d’informations, consultez la section Copier en parallèle à partir d’Azure Synapse Analytics.
  • En tant que récepteur, chargez les données avec l’instruction COPY, PolyBase ou bulk insert. Nous vous recommandons d’utiliser PolyBase ou l’instruction COPY pour de meilleures performances de copie. Le connecteur prend également en charge la création automatique de la table de destination avec DISTRIBUTION = ROUND_ROBIN, si celle-ci n’existe pas, en fonction du schéma source.

Important

Si vous copiez des données à l’aide d’Azure Integration Runtime, configurez une règle de pare-feu au niveau du serveur de façon à ce que les services Azure puissent accéder au serveur SQL logique. Si vous copiez des données à l’aide d’un runtime d’intégration auto-hébergé, configurez le pare-feu pour qu’il autorise la plage IP appropriée. Cette plage inclut l’adresse IP de l’ordinateur qui est utilisé pour se connecter à Azure Synapse Analytics.

Bien démarrer

Conseil

Pour obtenir le meilleur niveau de performance possible, utilisez PolyBase ou l’instruction COPY quand vous chargez des données dans Azure Synapse Analytics. Pour plus d’informations, consultez les sections Chargement de données dans Azure Synapse Analytics avec PolyBase et Chargement de données dans Azure Synapse Analytics avec l’instruction COPY. Consultez Charger 1 To dans Azure Synapse Analytics en moins de 15 minutes avec Azure Data Factory pour obtenir une procédure pas à pas avec un cas d’utilisation.

Pour effectuer l’activité Copie avec un pipeline, vous pouvez vous servir de l’un des outils ou kits SDK suivants :

Créer un service lié Azure Synapse Analytics à l’aide de l’IU

Pour créer un service lié Azure Synapse Analytics dans l’interface utilisateur du portail Azure, procédez comme suit.

  1. Accédez à l’onglet Gérer dans votre espace de travail Azure Data Factory ou Synapse et sélectionnez Services liés, puis cliquez sur Nouveau :

  2. Recherchez Synapse et sélectionnez le connecteur Azure Synapse Analytics.

    Capture d’écran montrant le connecteur Azure Synapse Analytics.

  3. Configurez les informations du service, testez la connexion et créez le nouveau service lié.

    Capture d’écran de la configuration d’un service lié Azure Synapse Analytics.

Informations de configuration des connecteurs

Les sections suivantes fournissent des informations détaillées sur les propriétés qui définissent les entités de pipeline Data Factory et Synapse propres à un connecteur Azure Synapse Analytics.

Propriétés du service lié

Ces propriétés génériques sont prises en charge pour un service lié Azure Synapse Analytics :

Propriété Description Obligatoire
type La propriété de type doit être définie sur AzureSqlDW Oui
connectionString Spécifiez les informations requises pour la connexion à l’instance Azure Synapse Analytics pour la propriété connectionString.
Marquez ce champ en tant que SecureString afin de le stocker en toute sécurité. Vous pouvez également stocker un mot de passe/une clé de principal de service dans Azure Key Vault et, en cas d'authentification SQL, extraire la configuration password de la chaîne de connexion. Pour plus d'informations, reportez-vous à l'exemple JSON décrit sous le tableau et à l'article Stocker des informations d'identification dans Azure Key Vault.		 Oui
azureCloudType Pour l’authentification du principal de service, spécifiez le type d’environnement cloud Azure auquel votre application Microsoft Entra est inscrite.
Les valeurs autorisées sont AzurePublic, AzureChina, AzureUsGovernment et AzureGermany. Par défaut, l’environnement cloud du pipeline de fabrique de données ou Synapse est utilisé.		 Non
connectVia Le runtime d’intégration à utiliser pour se connecter à la banque de données. Vous pouvez utiliser Azure Integration Runtime ou un runtime d’intégration auto-hébergé (si votre magasin de données se trouve sur un réseau privé). À défaut de spécification, le runtime d’intégration Azure par défaut est utilisé. Non

Pour en savoir plus sur les autres types d’authentification, consultez les sections suivantes sur les propriétés spécifiques, les prérequis et les exemples JSON, respectivement :

Conseil

Lors de la création d’un service lié pour un pool SQL serverless dans Azure Synapse depuis le portail Azure :

  1. Pour Méthode de sélection de compte, choisissez Entrer manuellement.
  2. Collez le nom de domaine complet du point de terminaison serverless. Vous pouvez le trouver dans la page Vue d’ensemble de votre espace de travail Synapse dans le portail Azure, dans les propriétés sous Point de terminaison SQL serverless. Par exemple : myserver-ondemand.sql-azuresynapse.net.
  3. Pour Nom de la base de données, indiquez le nom de la base de données dans le pool SQL serverless.

Conseil

Si vous rencontrez une erreur avec le code d’erreur « UserErrorFailedToConnectToSqlServer » et un message tel que « La limite de session pour la base de données est XXX et a été atteinte. », ajoutez Pooling=false à votre chaîne de connexion, puis réessayez.

Authentification SQL

Pour utiliser le type d’authentification SQL, spécifiez les propriétés génériques décrites dans la section précédente.

Exemple de service lié qui utilise l’authentification SQL

{
    "name": "AzureSqlDWLinkedService",
    "properties": {
        "type": "AzureSqlDW",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Password=<password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Mot de passe dans Azure Key Vault :

{
    "name": "AzureSqlDWLinkedService",
    "properties": {
        "type": "AzureSqlDW",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;User ID=<username>@<servername>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30",
            "password": {
                "type": "AzureKeyVaultSecret",
                "store": {
                    "referenceName": "<Azure Key Vault linked service name>",
                    "type": "LinkedServiceReference"
                },
                "secretName": "<secretName>"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Authentification d’un principal du service

Pour utiliser l’authentification de principal de service, outre les propriétés génériques décrites dans la section précédente, spécifiez les propriétés suivantes :

Propriété Description Obligatoire
servicePrincipalId Spécifiez l’ID client de l’application. Oui
servicePrincipalKey Spécifiez la clé de l’application. Marquez ce champ en tant que SecureString afin de le stocker en toute sécurité, ou référencez un secret stocké dans Azure Key Vault. Oui
tenant Spécifiez les informations de locataire (nom de domaine ou ID de locataire) dans lesquels se trouve votre application. Vous pouvez le récupérer en pointant la souris dans le coin supérieur droit du Portail Azure. Oui

Vous devez également effectuer les étapes suivantes :

  1. Créez une application Microsoft Entra à partir du portail Azure. Prenez note du nom de l’application et des valeurs suivantes qui définissent le service lié :

    • ID de l'application
    • Clé de l'application
    • ID client

  2. Approvisionnez un administrateur Microsoft Entra pour votre serveur sur le portail Azure, si ce n’est pas déjà fait. L’administrateur Microsoft Entra peut être un utilisateur Microsoft Entra ou un groupe Microsoft Entra. Si vous accordez au groupe avec identité managée un rôle d’administrateur, ignorez les étapes 3 et 4. L’administrateur a un accès complet à la base de données.

  3. Créez des utilisateurs de base de données autonome pour le principal de service. Connectez-vous à l’entrepôt de données vers lequel ou à partir duquel vous souhaitez copier des données à l’aide d’outils tels que SSMS, avec une identité Microsoft Entra qui a au moins l’autorisation ALTER ANY USER. Exécutez la commande T-SQL suivante :

    CREATE USER [your_application_name] FROM EXTERNAL PROVIDER;

  4. Accordez les autorisations requises par le principal de service comme vous le feriez d’habitude pour des utilisateurs SQL ou autres. Exécutez le code suivant, ou consultez davantage d’options ici. Si vous souhaitez utiliser PolyBase pour charger les données, consultez l’autorisation de base de données requise.

    EXEC sp_addrolemember db_owner, [your application name];

  5. Configurez un service lié Azure Synapse Analytics dans un espace de travail Azure Data Factory ou Synapse.

Exemple de service lié qui utilise l’authentification du principal de service

{
    "name": "AzureSqlDWLinkedService",
    "properties": {
        "type": "AzureSqlDW",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30",
            "servicePrincipalId": "<service principal id>",
            "servicePrincipalKey": {
                "type": "SecureString",
                "value": "<service principal key>"
            },
            "tenant": "<tenant info, e.g. microsoft.onmicrosoft.com>"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Authentification des identités managées affectées par le système pour les ressources Azure

Une fabrique de données ou un espace de travail Synapse peut être associé à une identité managée affectée par le système pour les ressources Azure, qui représente la ressource. Vous pouvez utiliser cette identité managée pour l’authentification Azure Synapse Analytics. La ressource désignée peut accéder et copier des données vers ou à partir de votre entrepôt de données à l’aide de cette identité.

Pour utiliser l’authentification d’identité managée affectée par le système, spécifiez les propriétés génériques décrites dans la section précédente et effectuez ces étapes.

  1. Approvisionnez un administrateur Microsoft Entra pour votre serveur sur le portail Azure, si ce n’est pas déjà fait. L’administrateur Microsoft Entra peut être un utilisateur Microsoft Entra ou un groupe Microsoft Entra. Si vous octroyez un rôle d’administrateur au groupe disposant d’une identité managée affectée par le système, ignorez les étapes 3 et 4. L’administrateur a un accès complet à la base de données.

  2. Créer des utilisateurs de base de données autonome pour l’identité managée affectée par le système. Connectez-vous à l’entrepôt de données vers lequel ou à partir duquel vous souhaitez copier des données à l’aide d’outils tels que SSMS, avec une identité Microsoft Entra qui a au moins l’autorisation ALTER ANY USER. Exécutez le code T-SQL suivant.

    CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;

  3. Octroyez à l’identité managée affectée par le système les autorisations nécessaires, comme vous le faites normalement pour les utilisateurs SQL et les autres utilisateurs. Exécutez le code suivant, ou consultez davantage d’options ici. Si vous souhaitez utiliser PolyBase pour charger les données, consultez l’autorisation de base de données requise.

    EXEC sp_addrolemember db_owner, [your_resource_name];

  4. Configurez un service lié Azure Synapse Analytics.

Exemple :

{
    "name": "AzureSqlDWLinkedService",
    "properties": {
        "type": "AzureSqlDW",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30"
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Authentification d’identité managée affectée par l’utilisateur

Une fabrique de données ou un espace de travail Synapse peut être associé à une identité managée affectée par l’utilisateur, qui représente la ressource. Vous pouvez utiliser cette identité managée pour l’authentification Azure Synapse Analytics. La ressource désignée peut accéder et copier des données vers ou à partir de votre entrepôt de données à l’aide de cette identité.

Pour utiliser l’authentification d’identité managée affectée par l’utilisateur, outre les propriétés génériques décrites dans la section précédente, spécifiez les propriétés suivantes :

Propriété Description Obligatoire
credentials Spécifiez l’identité managée affectée par l’utilisateur en tant qu’objet d’informations d’identification. Oui

Vous devez également effectuer les étapes suivantes :

  1. Approvisionnez un administrateur Microsoft Entra pour votre serveur sur le portail Azure, si ce n’est pas déjà fait. L’administrateur Microsoft Entra peut être un utilisateur Microsoft Entra ou un groupe Microsoft Entra. Si vous octroyez un rôle d’administrateur au groupe disposant d’une identité managée affectée par l’utilisateur, ignorez les étapes 3. L’administrateur a un accès complet à la base de données.

  2. Créer des utilisateurs de base de données autonome pour l’identité managée affectée par l’utilisateur. Connectez-vous à l’entrepôt de données vers lequel ou à partir duquel vous souhaitez copier des données à l’aide d’outils tels que SSMS, avec une identité Microsoft Entra qui a au moins l’autorisation ALTER ANY USER. Exécutez le code T-SQL suivant.

    CREATE USER [your_resource_name] FROM EXTERNAL PROVIDER;

  3. Créez une ou plusieurs identités managées affectées par l’utilisateur, puis octroyez à ces identités managées affectées par l’utilisateur les autorisations nécessaires, comme vous le faites normalement pour les utilisateurs SQL et les autres utilisateurs. Exécutez le code suivant, ou consultez davantage d’options ici. Si vous souhaitez utiliser PolyBase pour charger les données, consultez l’autorisation de base de données requise.

    EXEC sp_addrolemember db_owner, [your_resource_name];

  4. Attribuez une ou plusieurs identités managées affectées par l’utilisateur à votre fabrique de données et créez des informations d’identification pour chaque identité managée affectée par l’utilisateur.

  5. Configurez un service lié Azure Synapse Analytics.

Exemple :

{
    "name": "AzureSqlDWLinkedService",
    "properties": {
        "type": "AzureSqlDW",
        "typeProperties": {
            "connectionString": "Server=tcp:<servername>.database.windows.net,1433;Database=<databasename>;Connection Timeout=30",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "<name of Integration Runtime>",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Propriétés du jeu de données

Pour obtenir la liste complète des sections et propriétés disponibles pour la définition de jeux de données, consultez l’article Jeux de données.

Les propriétés prises en charge pour le jeu de données Azure Synapse Analytics sont les suivantes :

Propriété Description Obligatoire
type La propriété type du jeu de données doit être définie sur AzureSqlDWTable. Oui
schéma Nom du schéma. Non pour Source, Oui pour Récepteur
table Nom de la table/vue. Non pour Source, Oui pour Récepteur
tableName Nom de la table/vue avec schéma. Cette propriété est prise en charge pour la compatibilité descendante. Pour les nouvelles charges de travail, utilisez schema et table. Non pour Source, Oui pour Récepteur

Exemple de propriétés du jeu de données

{
    "name": "AzureSQLDWDataset",
    "properties":
    {
        "type": "AzureSqlDWTable",
        "linkedServiceName": {
            "referenceName": "<Azure Synapse Analytics linked service name>",
            "type": "LinkedServiceReference"
        },
        "schema": [ < physical schema, optional, retrievable during authoring > ],
        "typeProperties": {
            "schema": "<schema_name>",
            "table": "<table_name>"
        }
    }
}

Propriétés de l’activité de copie

Pour obtenir la liste complète des sections et des propriétés disponibles pour la définition des activités, consultez l’article Pipelines. Cette section fournit la liste des propriétés prises en charge par Azure Synapse Analytics en tant que source et récepteur.

Azure Synapse Analytics en tant que source

Conseil

Pour savoir comment charger efficacement des données à partir d’Azure Synapse Analytics à l’aide du partitionnement des données, consultez Copier en parallèle à partir d’Azure Synapse Analytics.

Pour copier des données depuis Azure Synapse Analytics, affectez la valeur SqlDWSource à la propriété type dans la source d’activité de copie. Les propriétés prises en charge dans la section source de l’activité de copie sont les suivantes :

Propriété Description Obligatoire
type La propriété type de la source d’activité de copie doit être définie sur SqlDWSource. Oui
sqlReaderQuery Utiliser la requête SQL personnalisée pour lire les données. Exemple : select * from MyTable. Non
sqlReaderStoredProcedureName Nom de la procédure stockée qui lit les données de la table source. La dernière instruction SQL doit être une instruction SELECT dans la procédure stockée. Non
storedProcedureParameters Paramètres de la procédure stockée.
Les valeurs autorisées sont des paires de noms ou de valeurs. Les noms et la casse des paramètres doivent correspondre aux noms et à la casse des paramètres de la procédure stockée.		 Non
isolationLevel Spécifie le comportement de verrouillage des transactions pour la source SQL. Les valeurs autorisées sont les suivantes : ReadCommitted, ReadUncommitted, RepeatableRead, Serializable, Snapshot. S’il n’est pas spécifié, le niveau d’isolation par défaut de la base de données est utilisé. Pour plus d’informations, consultez system.data.isolationlevel. Non
partitionOptions Spécifie les options de partitionnement des données utilisées pour charger des données à partir d’Azure Synapse Analytics.
Les valeurs autorisées sont les suivantes : None (valeur par défaut), PhysicalPartitionsOfTable et DynamicRange.
Lorsqu’une option de partition est activée (autrement dit, pas None), le degré de parallélisme pour charger simultanément des données à partir d’une instance Azure Synapse Analytics est contrôlé par le paramètre parallelCopies de l’activité de copie.		 Non
partitionSettings Spécifiez le groupe de paramètres pour le partitionnement des données.
S’applique lorsque l’option de partitionnement n’est pas None.		 Non
Sous partitionSettings :
partitionColumnName Spécifiez le nom de la colonne source en type entier ou date/DateHeure (int, smallint, bigint, date, smalldatetime, datetime, datetime2 ou datetimeoffset) que le partitionnement par plages de valeurs va utiliser pour la copie en parallèle. S’il n’est pas spécifié, l’index ou la clé primaire de la table est détecté automatiquement et utilisé comme colonne de partition.
S’applique lorsque l’option de partitionnement est DynamicRange. Si vous utilisez une requête pour récupérer des données sources, utilisez ?AdfDynamicRangePartitionCondition dans la clause WHERE. Pour obtenir un exemple, consultez la section Copier en parallèle à partir de la base de données SQL.		 Non
partitionUpperBound Valeur maximale de la colonne de partition pour le fractionnement de la plage de partition. Cette valeur est utilisée pour décider du stride de la partition, et non pour filtrer les lignes de la table. Toutes les lignes de la table ou du résultat de la requête seront partitionnées et copiées. Si la valeur n’est pas spécifiée, l’activité de copie la détecte automatiquement.
S’applique lorsque l’option de partitionnement est DynamicRange. Pour obtenir un exemple, consultez la section Copier en parallèle à partir de la base de données SQL.		 Non
partitionLowerBound Valeur minimale de la colonne de partition pour le fractionnement de la plage de partition. Cette valeur est utilisée pour décider du stride de la partition, et non pour filtrer les lignes de la table. Toutes les lignes de la table ou du résultat de la requête seront partitionnées et copiées. Si la valeur n’est pas spécifiée, l’activité de copie la détecte automatiquement.
S’applique lorsque l’option de partitionnement est DynamicRange. Pour obtenir un exemple, consultez la section Copier en parallèle à partir de la base de données SQL.		 Non

Notez le point suivant :

  • Lorsque vous utilisez une procédure stockée dans la source pour récupérer des données, sachez que si votre procédure stockée est conçue pour renvoyer un schéma différent quand une valeur de paramètre différente est entrée, vous risquez d’échouer ou d’obtenir un résultat inattendu lors de l’importation d’un schéma à partir de l’interface utilisateur ou lors de la copie de données dans la base de données SQL avec création de table automatique.

Exemple : utilisation d’une requête SQL

"activities":[
    {
        "name": "CopyFromAzureSQLDW",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure Synapse Analytics input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlDWSource",
                "sqlReaderQuery": "SELECT * FROM MyTable"
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Exemple : utilisation d’une procédure stockée

"activities":[
    {
        "name": "CopyFromAzureSQLDW",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "<Azure Synapse Analytics input dataset name>",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "<output dataset name>",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlDWSource",
                "sqlReaderStoredProcedureName": "CopyTestSrcStoredProcedureWithParameters",
                "storedProcedureParameters": {
                    "stringData": { "value": "str3" },
                    "identifier": { "value": "$$Text.Format('{0:yyyy}', <datetime parameter>)", "type": "Int"}
                }
            },
            "sink": {
                "type": "<sink type>"
            }
        }
    }
]

Exemple de procédure stockée :

CREATE PROCEDURE CopyTestSrcStoredProcedureWithParameters
(
    @stringData varchar(20),
    @identifier int
)
AS
SET NOCOUNT ON;
BEGIN
    select *
    from dbo.UnitTestSrcTable
    where dbo.UnitTestSrcTable.stringData != stringData
    and dbo.UnitTestSrcTable.identifier != identifier
END
GO

Azure Synapse Analytics en tant que récepteur

Les pipelines Azure Data Factory et Synapse prennent en charge trois manières de charger des données dans Azure Synapse Analytics.

La méthode la plus rapide et la plus évolutive pour charger des données consiste à utiliser l’instruction COPY ou PolyBase.

Pour copier des données vers Azure Synapse Analytics, définissez SqlDWSink comme type de récepteur dans l’activité de copie. Les propriétés suivantes sont prises en charge dans la section sink de l’activité de copie :

Propriété Description Obligatoire
type La propriété type du récepteur d’activité de copie doit être définie sur SqlDWSink. Oui
allowPolyBase Indique s’il faut utiliser PolyBase pour charger des données dans Azure Synapse Analytics. allowCopyCommand et allowPolyBase ne peuvent pas avoir tous les deux la valeur True.

Reportez-vous à la section Utiliser PolyBase pour charger des données dans Azure Synapse Analytics pour connaître les contraintes et les détails.

Les valeurs autorisées sont True et False (par défaut).		 Non.
Appliquer lors de l’utilisation de PolyBase.
polyBaseSettings Groupe de propriétés pouvant être spécifié lorsque la propriété allowPolybase est définie sur true. Non.
Appliquer lors de l’utilisation de PolyBase.
allowCopyCommand Indique s’il faut utiliser l’instruction COPY pour charger des données dans Azure Synapse Analytics. allowCopyCommand et allowPolyBase ne peuvent pas avoir tous les deux la valeur True.

Reportez-vous à la section Utiliser l’instruction COPY pour charger des données dans Azure Synapse Analytics pour connaître les contraintes et les détails.

Les valeurs autorisées sont True et False (par défaut).		 Non.
Appliquer au moment de l’utilisation de l’instruction COPY.
copyCommandSettings Groupe de propriétés pouvant être spécifié lorsque la propriété allowCopyCommand est définie sur TRUE. Non.
Appliquer au moment de l’utilisation de l’instruction COPY.
writeBatchSize Nombre de lignes à insérer dans le tableau SQL par lot.

La valeur autorisée est integer (nombre de lignes). Par défaut, le service détermine de façon dynamique la taille de lot appropriée selon la taille de ligne.		 Non.
Appliquer au moment de l’utilisation de l’instruction BULK INSERT.
writeBatchTimeout Le temps d’attente pour que l’opération d’insertion, d’insertion ascendante et de procédure stockée se termine avant l’expiration du délai.
Les valeurs autorisées sont celles qui expriment un intervalle de temps. Exemple : « 00:30:00 » pour 30 minutes. Si aucune valeur n’est spécifiée, le délai d’expiration est par défaut « 00:30:00 ».		 Non.
Appliquer au moment de l’utilisation de l’instruction BULK INSERT.
preCopyScript Spécifiez une requête SQL pour l’activité de copie à exécuter avant l’écriture de données dans Azure Synapse Analytics à chaque exécution. Utilisez cette propriété pour nettoyer les données préchargées. Non
tableOption Spécifie s’il faut créer automatiquement la table de récepteur, si elle n’existe pas, en fonction du schéma source. Les valeurs autorisées sont none (par défaut) et autoCreate. Non
disableMetricsCollection Le service collecte des métriques telles que les DWU Azure Synapse Analytics pour effectuer des recommandations et optimiser les performances de copie, introduisant un accès de base de données master supplémentaire. Si ce comportement vous préoccupe, spécifiez true pour le désactiver. Non (la valeur par défaut est false)
 maxConcurrentConnections La limite supérieure de connexions simultanées établies au magasin de données pendant l’exécution de l’activité. Spécifiez une valeur uniquement lorsque vous souhaitez limiter les connexions simultanées.  Aucune
WriteBehavior Spécifiez le comportement d’écriture pour l’activité de copie afin de charger des données dans Azure SQL Database.
La valeur autorisée est Insert ou Upsert. Par défaut, le service utilise Insert pour charger des données.		 Non
upsertSettings Spécifiez le groupe de paramètres pour le comportement d’écriture.
S’applique quand l’option WriteBehavior a la valeur Upsert.		 Non
Sous upsertSettings :
clés Spécifiez les noms de colonne à des fins d’identification unique des lignes. Vous pouvez utiliser une seule clé ou une série de clés. Si la valeur n’est pas spécifiée, la clé primaire est utilisée. Non
interimSchemaName Spécifiez le schéma intermédiaire pour la création de la table intermédiaire. Remarque : L’utilisateur a besoin d’une autorisation de créer et supprimer une table. Par défaut, la table intermédiaire partage le même schéma que la table du récepteur. Non

Exemple 1 : Récepteur Azure Synapse Analytics

"sink": {
    "type": "SqlDWSink",
    "allowPolyBase": true,
    "polyBaseSettings":
    {
        "rejectType": "percentage",
        "rejectValue": 10.0,
        "rejectSampleValue": 100,
        "useTypeDefault": true
    }
}

Exemple 2 : Faire un upsert des données

"sink": {
    "type": "SqlDWSink",
    "writeBehavior": "Upsert",
    "upsertSettings": {
        "keys": [
             "<column name>"
        ],
        "interimSchemaName": "<interim schema name>"
    },
}

Copie en parallèle à partir d’Azure Synapse Analytics

Le connecteur Azure Synapse Analytics dans l’activité de copie propose un partitionnement de données intégré pour copier des données en parallèle. Vous trouverez des options de partitionnement de données dans l’onglet Source de l’activité de copie.

Capture d’écran représentant les options de partition

Lorsque vous activez la copie partitionnée, l’activité de copie exécute des requêtes en parallèle sur votre source Azure Synapse Analytics pour charger des données par partitions. Le degré de parallélisme est contrôlé via le paramètre parallelCopies sur l’activité de copie. Par exemple, si vous définissez parallelCopies sur la valeur quatre, le service génère et exécute simultanément quatre requêtes selon l’option de partition et les paramètres que vous avez spécifiés, chacune récupérant des données à partir de votre instance Azure Synapse Analytics.

Il vous est recommandé d’activer la copie en parallèle avec partitionnement des données notamment lorsque vous chargez une grande quantité de données à partir de votre instance Azure Synapse Analytics. Voici quelques suggestions de configurations pour différents scénarios. Lors de la copie de données dans un magasin de données basé sur un fichier, il est recommandé d’écrire les données dans un dossier sous la forme de plusieurs fichiers (spécifiez uniquement le nom du dossier). Les performances seront meilleures qu’avec l’écriture de données dans un seul fichier.

Scénario Paramètres suggérés
Chargement complet à partir d’une table volumineuse, avec des partitions physiques. Option de partition : Partitions physiques de la table.

Pendant l’exécution, le service détecte automatiquement les partitions physiques et copie les données par partition.

Pour vérifier si votre table possède, ou non, une partition physique, vous pouvez vous reporter à cette requête.
Chargement complet d’une table volumineuse, sans partitions physiques, avec une colonne d’entiers ou DateHeure pour le partitionnement des données. Options de partition : Partition dynamique par spécification de plages de valeurs.
Colonne de partition (facultatif) : Spécifiez la colonne utilisée pour partitionner les données. Si la valeur n’est pas spécifiée, la colonne de l’index ou de la clé primaire est utilisée.
Limite supérieure de partition et limite inférieure de partition (facultatif) : Spécifiez si vous souhaitez déterminer le stride de la partition. Cela ne permet pas de filtrer les lignes de la table ; toutes les lignes de la table sont partitionnées et copiées. Si les valeurs ne sont pas spécifiées, l’activité de copie les détecte automatiquement.

Par exemple, si les valeurs de la colonne de partition « ID » sont comprises entre 1 et 100, et que vous définissez la limite inférieure à 20 et la limite supérieure à 80, avec la copie parallèle à 4, le service récupère des données en fonction de 4 partitions, (ID des plages <=20, [21, 50], [51, 80] et >=81, respectivement).
Chargement d’une grande quantité de données à l’aide d’une requête personnalisée, sans partitions physiques, et avec une colonne d’entiers ou de date/DateHeure pour le partitionnement des données. Options de partition : Partition dynamique par spécification de plages de valeurs.
Requête: SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>.
Colonne de partition : Spécifiez la colonne utilisée pour partitionner les données.
Limite supérieure de partition et limite inférieure de partition (facultatif) : Spécifiez si vous souhaitez déterminer le stride de la partition. Cela ne permet pas de filtrer les lignes de la table ; toutes les lignes du résultat de la requête sont partitionnées et copiées. Si la valeur n’est pas spécifiée, l’activité de copie la détecte automatiquement.

Lors de l’exécution, le service remplace ?AdfRangePartitionColumnName par le nom réel de la colonne et les plages de valeurs de chaque partition, et les envoie à Azure Synapse Analytics.
Par exemple, si les valeurs de la colonne de partition « ID » sont comprises entre 1 et 100, et que vous définissez la limite inférieure à 20 et la limite supérieure à 80, avec la copie parallèle à 4, le service récupère des données en fonction de 4 partitions (ID des plages <=20, [21, 50], [51, 80] et >=81, respectivement).

Voici d’autres exemples de requêtes pour différents scénarios :
1. Interroger l’ensemble de la table :
SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition
2. Interroger une table avec une sélection de colonnes et des filtres de la clause WHERE supplémentaires :
SELECT <column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
3. Effectuer une requête avec des sous-requêtes :
SELECT <column_list> FROM (<your_sub_query>) AS T WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>
4. Effectuer une requête avec une partition dans une sous-requête :
SELECT <column_list> FROM (SELECT <your_sub_query_column_list> FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition) AS T

Meilleures pratiques pour charger des données avec l’option de partition :

  1. Choisissez une colonne distinctive comme colonne de partition (p. ex. : clé primaire ou clé unique) pour éviter l’asymétrie des données.
  2. Si la table possède une partition intégrée, utilisez l’option de partition « Partitions physiques de la table » pour obtenir de meilleures performances.
  3. Si vous utilisez Azure Integration Runtime pour copier des données, vous pouvez définir des « unités d’intégration de données (DIU) » plus grandes (>4) pour utiliser davantage de ressources de calcul. Vérifiez les scénarios applicables ici.
  4. Le « degré de parallélisme de copie » contrôle le nombre de partitions : un nombre trop élevé nuit parfois aux performances. Il est recommandé de définir ce nombre selon (DIU ou nombre de nœuds d'IR auto-hébergé) * (2 à 4).
  5. Notez qu’Azure Synapse Analytics peut exécuter un maximum de 32 requêtes à un moment donné. La définition d’un « degré de parallélisme de copie » trop important peut entraîner un problème de limitation de Synapse.

Exemple : chargement complet à partir d’une table volumineuse, avec des partitions physiques

"source": {
    "type": "SqlDWSource",
    "partitionOption": "PhysicalPartitionsOfTable"
}

Exemple : requête avec partition dynamique par spécification de plages de valeurs

"source": {
    "type": "SqlDWSource",
    "query": "SELECT * FROM <TableName> WHERE ?AdfDynamicRangePartitionCondition AND <your_additional_where_clause>",
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "<partition_column_name>",
        "partitionUpperBound": "<upper_value_of_partition_column (optional) to decide the partition stride, not as data filter>",
        "partitionLowerBound": "<lower_value_of_partition_column (optional) to decide the partition stride, not as data filter>"
    }
}

Exemple de requête pour vérifier une partition physique

SELECT DISTINCT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, CASE WHEN c.name IS NULL THEN 'no' ELSE 'yes' END AS HasPartition
FROM sys.tables AS t
LEFT JOIN sys.objects AS o ON t.object_id = o.object_id
LEFT JOIN sys.schemas AS s ON o.schema_id = s.schema_id
LEFT JOIN sys.indexes AS i ON t.object_id = i.object_id
LEFT JOIN sys.index_columns AS ic ON ic.partition_ordinal > 0 AND ic.index_id = i.index_id AND ic.object_id = t.object_id
LEFT JOIN sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN sys.types AS y ON c.system_type_id = y.system_type_id
WHERE s.name='[your schema]' AND t.name = '[your table name]'

Si la table possède une partition physique, vous voyez « HasPartition » avec la valeur « yes » (oui).

Utiliser l’instruction COPY pour charger des données dans Azure Synapse Analytics

L’utilisation de l’instruction COPY est un moyen simple et flexible de charger des données dans Azure Synapse Analytics avec un débit élevé. Pour plus d’informations, consultez Chargement en masse de données à l’aide de l’instruction Copy

  • Si vos données sources se trouvent dans Stockage Blob Azure ou Azure Data Lake Storage Gen2 et que le format est compatible avec l’instruction COPY, vous pouvez utiliser l’activité de copie pour appeler directement l’instruction COPY et permettre à Azure Synapse Analytics d’extraire les données de la source. Pour plus d’informations, consultez Copie directe à l’aide de l’instruction COPY .
  • Si votre magasin de données source et son format ne sont pas pris en charge à l’origine par l’instruction COPY, utilisez plutôt la fonctionnalité Copie intermédiaire avec l’instruction COPY . La fonctionnalité de copie intermédiaire offre également un meilleur débit. Elle convertit automatiquement les données dans un format compatible avec l’instruction COPY, stocke les données dans Stockage Blob Azure, puis appelle l’instruction COPY pour charger les données dans Azure Synapse Analytics.

Conseil

Lorsque l’instruction COPY est utilisée avec Azure Integration Runtime, le nombre réel d’unités d’intégration de données est toujours de 2. La modification de ce nombre n’a aucun impact sur les performances, étant donné que le chargement de données à partir du stockage est alimenté par le moteur Azure Synapse.

Copie directe à l’aide de l’instruction COPY

L’instruction COPY Azure Synapse Analytics prend directement en charge Stockage Blob Azure, Azure Data Lake Storage Gen1 et Azure Data Lake Storage Gen2. Si vos données sources répondent aux critères décrits dans cette section, utilisez l’instruction COPY pour copier directement à partir du magasin de données source vers Azure Synapse Analytics. Sinon, utilisez la méthode Copie intermédiaire à l’aide de l’instruction COPY. Le service vérifie les paramètres et fait échouer l’exécution de l’activité de copie si les critères ne sont pas satisfaits.

  1. Le format et le service liés à la source ont les types et méthodes d’authentification suivants :

    Type de magasin de données source pris en charge Format pris en charge Type d’authentification source pris en charge
    Blob Azure Texte délimité Authentification avec clé de compte, authentification avec signature d’accès partagé, authentification avec principal du service, authentification avec identité managée affectée par le système
      Parquet Authentification avec clé de compte, authentification avec signature d’accès partagé
      ORC Authentification avec clé de compte, authentification avec signature d’accès partagé
    Azure Data Lake Storage Gen2 Texte délimité
    Parquet
    ORC    		 Authentification avec clé de compte, authentification avec principal du service, authentification avec identité managée affectée par le système

    Important

  2. Les paramètres du format sont comme suit :

    1. Pour Parquet : compression peut correspondre à no compression, Snappy ou GZip.
    2. Pour ORC : compression peut être sans compression, zlib ou Snappy.
    3. Pour Texte délimité :
      1. rowDelimiter est explicitement défini comme caractère unique ou « \r\n », la valeur par défaut n’est pas prise en charge.
      2. nullValue conserve sa valeur par défaut ou est défini sur chaîne vide ("").
      3. encodingName conserve sa valeur par défaut ou est défini sur utf-8 ou utf-16.
      4. escapeChar doit être identique à quoteChar et n’est pas vide.
      5. skipLineCount conserve sa valeur par défaut ou est défini sur 0.
      6. compression peut être no compression ou GZip.

  3. Si votre source est un dossier, recursive dans l’activité de copie doit être défini sur true, et wildcardFilename doit être * ou *.*.

  4. wildcardFolderPath , wildcardFilename (autre que * ou *.*), modifiedDateTimeStart, modifiedDateTimeEnd, prefix, enablePartitionDiscovery et additionalColumns ne sont pas spécifiés.

Les paramètres de l’instruction COPY suivants sont pris en charge sous allowCopyCommand dans l’activité de copie :

Propriété Description Obligatoire
defaultValues Spécifie les valeurs par défaut de chaque colonne cible dans Azure Synapse Analytics. Les valeurs par défaut de la propriété remplacent le jeu de contraintes DEFAULT défini dans l’entrepôt de données, et la colonne d’identité ne peut pas avoir de valeur par défaut. Non
additionalOptions Options supplémentaires qui seront passées à l’instruction COPY d’Azure Synapse Analytics directement dans la clause « With » dans l’instruction COPY. Citez la valeur en fonction des besoins pour l’aligner sur les spécifications de l’instruction COPY. Non 
"activities":[
    {
        "name": "CopyFromAzureBlobToSQLDataWarehouseViaCOPY",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "ParquetDataset",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "AzureSQLDWDataset",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "ParquetSource",
                "storeSettings":{
                    "type": "AzureBlobStorageReadSettings",
                    "recursive": true
                }
            },
            "sink": {
                "type": "SqlDWSink",
                "allowCopyCommand": true,
                "copyCommandSettings": {
                    "defaultValues": [
                        {
                            "columnName": "col_string",
                            "defaultValue": "DefaultStringValue"
                        }
                    ],
                    "additionalOptions": {
                        "MAXERRORS": "10000",
                        "DATEFORMAT": "'ymd'"
                    }
                }
            },
            "enableSkipIncompatibleRow": true
        }
    }
]

Copie intermédiaire à l’aide de l’instruction COPY

Quand vos données sources ne sont pas compatibles en mode natif avec l’instruction COPY, activez la copie des données par le biais d’un Stockage Blob Azure intermédiaire ou d’Azure Data Lake Storage Gen2 (il ne peut pas s’agir du Stockage Premium Azure). Dans ce cas, le service convertit automatiquement les données pour répondre aux exigences de format de données de l’instruction COPY. Il appelle ensuite l’instruction COPY pour charger des données dans Azure Synapse Analytics. Enfin, il nettoie vos données temporaires du stockage. Pour plus d’informations sur la copie de données par le biais d’un stockage de préproduction, consultez Copie .

Pour utiliser cette fonctionnalité, créez un service lié Stockage Blob Azure ou un service lié Azure Data Lake Storage Gen2 avec authentification par clé de compte ou identité managée par le système qui désigne le compte de stockage Azure comme stockage temporaire.

Important

Important

Si votre stockage Azure intermédiaire est configuré avec un point de terminaison privé géré et avec le pare-feu de stockage activé, vous devez utiliser l’authentification par identité managée et accorder des autorisations de lecteur de données d’objet blob de stockage à Synapse SQL Server pour qu’il puisse accéder aux fichiers intermédiaires pendant le chargement de l’instruction COPY.

"activities":[
    {
        "name": "CopyFromSQLServerToSQLDataWarehouseViaCOPYstatement",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "SQLServerDataset",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "AzureSQLDWDataset",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
            },
            "sink": {
                "type": "SqlDWSink",
                "allowCopyCommand": true
            },
            "stagingSettings": {
                "linkedServiceName": {
                    "referenceName": "MyStagingStorage",
                    "type": "LinkedServiceReference"
                }
            }
        }
    }
]

Utiliser PolyBase pour charger des données dans Azure Synapse Analytics

L’utilisation de PolyBase est un moyen efficace de charger de grandes quantités de données dans Azure Synapse Analytics avec un débit élevé. Vous profiterez d’un gain important de débit en utilisant PolyBase au lieu du mécanisme BULKINSERT par défaut.

  • Si vos données sources se trouvent dans Stockage Blob Azure, Azure Data Lake Storage Gen1 ou Azure Data Lake Storage Gen2 et que le format est compatible avec PolyBase, vous pouvez utiliser l’activité de copie pour appeler directement PolyBase et permettre à Azure Synapse Analytics d’extraire les données de la source. Pour plus d’informations, consultez Copie directe à l’aide de PolyBase .
  • Si votre magasin de données source et son format ne sont pas pris en charge à l’origine par PolyBase, utilisez plutôt la fonctionnalité Copie intermédiaire avec PolyBase . La fonctionnalité de copie intermédiaire offre également un meilleur débit. Elle convertit automatiquement les données dans un format compatible avec PolyBase, stocke les données dans Stockage Blob Azure, puis appelle PolyBase pour charger les données dans Azure Synapse Analytics.

Conseil

Apprenez-en plus sur les Bonnes pratiques d’utilisation de PolyBase. Lorsque PolyBase est utilisé avec Azure Integration Runtime, le nombre réel d’unités d’intégration de données pour le stockage direct ou de préproduction dans Synapse est toujours de 2. La modification de ce nombre n’a aucun impact sur les performances, étant donné que le chargement de données à partir du stockage est alimenté par le moteur Synapse.

Les paramètres PolyBase suivants sont pris en charge sous polyBaseSettings dans l’activité de copie :

Propriété Description Obligatoire
rejectValue Spécifie le nombre ou le pourcentage de lignes pouvant être rejetées avant l’échec de la requête.

Découvrez-en plus sur les options de rejet de PolyBase dans la section Arguments de CREATE EXTERNAL TABLE (Transact-SQL).

Les valeurs autorisées sont : 0 (par défaut), 1, 2, et ainsi de suite.		 Non
rejectType Indique si l’option rejectValue est une valeur littérale ou un pourcentage.

Les valeurs autorisées sont Value (par défaut) et Percentage.		 Non
rejectSampleValue Détermine le nombre de lignes à extraire avant que PolyBase recalcule le pourcentage de lignes rejetées.

Les valeurs autorisées sont 1, 2, et ainsi de suite.		 Oui, si rejectType est percentage
useTypeDefault Spécifie comment gérer les valeurs manquantes dans les fichiers texte délimité lorsque PolyBase récupère les données à partir du fichier texte.

Pour plus d’informations sur cette propriété, consultez la section Arguments dans CREATE EXTERNAL FILE FORMAT (Transact-SQL).

Les valeurs autorisées sont True et False (par défaut).

 Non

Copie directe à l’aide de PolyBase

Azure Synapse Analytics PolyBase prend directement en charge Stockage Blob Azure, Azure Data Lake Storage Gen1 et Azure Data Lake Storage Gen2. Si vos données sources répondent aux critères décrits dans cette section, utilisez PolyBase pour copier directement à partir du magasin de données source vers Azure Synapse Analytics. Sinon, utilisez la méthode Copie intermédiaire à l’aide de PolyBase.

Conseil

Pour copier des données efficacement vers Azure Synapse Analytics, consultez Azure Data Factory facilite et rend plus pratique la découverte d’insights à partir de données lors de l’utilisation de Data Lake Store avec Azure Synapse Analytics.

Si les critères ne sont pas remplis, le service contrôle les paramètres et rétablit automatiquement le mécanisme BULKINSERT pour le déplacement des données.

  1. Le service lié source contient les types et méthodes d’authentification suivants :

    Type de magasin de données source pris en charge Type d’authentification source pris en charge
    Blob Azure Authentification par clé de compte, authentification avec identité managée affectée par le système
    Azure Data Lake Storage Gen1 Authentification d’un principal du service
    Azure Data Lake Storage Gen2 Authentification par clé de compte, authentification avec identité managée affectée par le système

    Important

  2. Le format de données source est Parquet, ORC, ou Texte délimité, avec les configurations suivantes :

    1. Le chemin du dossier ne contient pas de filtre de caractères génériques.
    2. Le nom de fichier est vide ou pointe vers un fichier unique. Si vous spécifiez un nom de fichier avec caractères génériques dans l’activité de copie, les seuls caractères autorisés sont * et *.*.
    3. rowDelimiter peut être \n, \r\n ou \r ou conserver sa valeur par défaut.
    4. nullValue est défini sur une chaîne vide («») ou conserve sa valeur par défaut, et treatEmptyAsNull conserve sa valeur par défaut ou est défini sur true.
    5. encodingName conserve sa valeur par défaut ou est défini sur utf-8.
    6. quoteChar, escapeChar et skipLineCount ne sont pas spécifiés. PolyBase est capable d’ignorer la ligne d’en-tête. Cela peut être paramétré en tant que firstRowAsHeader.
    7. compression peut correspondre à no compression, GZip ou Deflate.

  3. Si votre source est un dossier, recursive dans l’activité de copie doit être défini sur true.

  4. wildcardFolderPath, wildcardFilename, modifiedDateTimeStart, modifiedDateTimeEnd, prefix, enablePartitionDiscovery ni additionalColumns ne sont pas spécifiés.

Notes

Si votre source est un dossier, notez que PolyBase récupère les fichiers du dossier et de tous ses sous-dossiers, mais qu’il ne récupère pas les données des fichiers dont le nom commence par un trait de soulignement (_) ou un point (.), comme indiqué ici - argument LOCATION.

"activities":[
    {
        "name": "CopyFromAzureBlobToSQLDataWarehouseViaPolyBase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "ParquetDataset",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "AzureSQLDWDataset",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "ParquetSource",
                "storeSettings":{
                    "type": "AzureBlobStorageReadSettings",
                    "recursive": true
                }
            },
            "sink": {
                "type": "SqlDWSink",
                "allowPolyBase": true
            }
        }
    }
]

Copie intermédiaire à l’aide de PolyBase

Quand vos données sources ne sont pas compatibles en mode natif avec PolyBase, activez la copie des données par le biais d’un Stockage Blob Azure intermédiaire ou d’Azure Data Lake Storage Gen2 (il ne peut pas s’agir du Stockage Premium Azure). Dans ce cas, le service convertit automatiquement les données pour répondre aux exigences de format de données de PolyBase. Ensuite, il appelle PolyBase pour charger les données dans Azure Synapse Analytics. Enfin, il nettoie vos données temporaires du stockage. Pour plus d’informations sur la copie de données par le biais d’un stockage de préproduction, consultez Copie .

Pour utiliser cette fonctionnalité, créez un service lié Stockage Blob Azure ou un service lié Azure Data Lake Storage Gen2 avec authentification par clé de compte ou identité managée qui désigne le compte de stockage Azure comme stockage temporaire.

Important

Important

Si votre stockage Azure intermédiaire est configuré avec un point de terminaison privé géré et avec le pare-feu de stockage activé, vous devez utiliser l’authentification d’identité managée et accorder des autorisations de lecteur de données d’objet blob de stockage à Synapse SQL Server pour qu’il puisse accéder aux fichiers intermédiaires pendant le chargement de PolyBase.

"activities":[
    {
        "name": "CopyFromSQLServerToSQLDataWarehouseViaPolyBase",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "SQLServerDataset",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "AzureSQLDWDataset",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "SqlSource",
            },
            "sink": {
                "type": "SqlDWSink",
                "allowPolyBase": true
            },
            "enableStaging": true,
            "stagingSettings": {
                "linkedServiceName": {
                    "referenceName": "MyStagingStorage",
                    "type": "LinkedServiceReference"
                }
            }
        }
    }
]

Bonnes pratiques d’utilisation de PolyBase

Les sections suivantes présentent les meilleures pratiques qui s’ajoutent à celles mentionnées dans Meilleures pratiques pour Azure Synapse Analytics.

Autorisation de base de données requise

Pour utiliser PolyBase, l’utilisateur qui charge des données dans Azure Synapse Analytics doit disposer de l’autorisation « CONTROL » sur la base de données cible. Vous pouvez pour cela l’ajouter en tant que membre du rôle db_owner. Découvrez comment procéder dans la vue d’ensemble d’Azure Synapse Analytics.

Limitations en matière de taille de ligne et de type de données

Les chargements PolyBase sont limités aux lignes inférieures à 1 Mo. Vous ne pouvez pas l’utiliser pour charger VARCHR(MAX), NVARCHAR(MAX) ou VARBINARY(MAX). Pour plus d’informations, consultez Limites de la capacité du service Azure Synapse Analytics.

Quand votre source de données comporte des lignes supérieures à 1 Mo, vous pouvez fractionner verticalement les tables sources en plusieurs petites tables. Vérifiez que la taille maximale de chaque ligne ne dépasse pas la limite. Vous pouvez ensuite charger les tables plus petites à l’aide de PolyBase et les fusionner dans Azure Synapse Analytics.

Autrement, pour les données avec des colonnes aussi larges, vous pouvez utiliser non-PolyBase pour charger les données, en désactivant le paramètre « Autoriser PolyBase ».

Classe de ressources Azure Synapse Analytics

Pour obtenir le meilleur débit possible, attribuez une classe de ressources plus volumineuse à l’utilisateur qui charge des données dans Azure Synapse Analytics par le biais de PolyBase.

Résolution des problèmes liés à PolyBase

Chargement de la colonne décimale

Si votre source de données est au format texte ou se trouve dans d’autres magasins non compatibles avec PolyBase (avec copie intermédiaire et PolyBase) et qu’elle contient une valeur vide à charger dans une colonne décimale d’Azure Synapse Analytics, vous risquez d’obtenir l’erreur suivante :

ErrorCode=FailedDbOperation, ......HadoopSqlException: Error converting data type VARCHAR to DECIMAL.....Detailed Message=Empty string can't be converted to DECIMAL.....

La solution consiste à désélectionner l’option « Utiliser l’option de type par défaut » (false) dans Récepteur d’activité Copy -> Paramètres de PolyBase. « USE_TYPE_DEFAULT » est une configuration native PolyBase qui spécifie comment gérer les valeurs manquantes dans les fichiers texte délimités quand PolyBase extrait des données à partir du fichier texte.

Vérification de la propriété tableName dans Azure Synapse Analytics

Le tableau suivant donne des exemples montrant comment spécifier la propriété tableName dans le jeu de données JSON. Il montre plusieurs combinaisons de noms de schéma et de table.

Schéma BD Nom de la table Propriété JSON tableName
dbo MyTable MyTable ou dbo.MyTable ou [dbo].[MyTable]
dbo1 MyTable dbo1.MyTable ou [dbo1].[MyTable]
dbo My.Table [My.Table] ou [dbo].[My.Table]
dbo1 My.Table [dbo1].[My.Table]

Si vous voyez l’erreur suivante, il peut s’agir d’un problème avec la valeur spécifiée pour la propriété tableName. Consultez le tableau précédent pour savoir comment spécifier des valeurs pour la propriété JSON tableName.

Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'stg.Account_test'.,Source=.Net SqlClient Data Provider

Colonnes avec des valeurs par défaut

Actuellement, la fonctionnalité PolyBase accepte seulement le même nombre de colonnes que dans la table cible. Par exemple, vous avez une table avec quatre colonnes dont l’une est définie avec une valeur par défaut. Les données d’entrée doivent toujours avoir quatre colonnes. Un jeu de données d’entrée de trois colonnes produit une erreur semblable au message suivant :

All columns of the table must be specified in the INSERT BULK statement.

La valeur NULL est une forme spéciale de la valeur par défaut. Si la colonne est nullable, les données d’entrée dans l’objet Blob pour cette colonne peuvent être vides. En revanche, elles ne peuvent pas être manquantes dans le jeu de données d’entrée. PolyBase insère NULL pour les données manquantes dans Azure Synapse Analytics.

Échec de l’accès au fichier externe

Si vous recevez l’erreur suivante, vérifiez que vous utilisez l’authentification d’identité managée et que vous avez accordé des autorisations de lecteur de données d’objet blob de stockage à l’identité managée de l’espace de travail Azure Synapse.

Job failed due to reason: at Sink '[SinkName]': shaded.msdataflow.com.microsoft.sqlserver.jdbc.SQLServerException: External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:\r\nHdfsBridge::isDirExist

Pour plus d’informations, consultez Octroi d’autorisations à l’identité managée après la création de l’espace de travail.

Propriétés du mappage de flux de données

Lors de la transformation de données dans le flux de données de mappage, vous pouvez lire et écrire dans des tables à partir d’Azure Synapse Analytics. Pour plus d’informations, consultez la transformation de la source et la transformation du récepteur dans le flux de données de mappage.

Transformation de la source

Les paramètres spécifiques à Azure Synapse Analytics sont disponibles dans l’onglet Options de la source de la transformation de la source.

Entrée Indiquez si votre source pointe vers une table (ce qui correspond à Select * from <table-name>) ou si vous souhaitez entrer une requête SQL personnalisée.

Activer la préproduction : Il est vivement recommandé d’utiliser cette option dans les charges de travail de production avec des sources Azure Synapse Analytics. Si vous exécutez une activité de flux de données avec des sources Azure Synapse Analytics à partir d’un pipeline, vous êtes invité à entrer un compte de stockage d’emplacement de préproduction que vous utiliserez pour le chargement des données de préproduction. Il s’agit du mécanisme le plus rapide pour charger des données à partir d’Azure Synapse Analytics.

Requête : Si vous sélectionnez Requête dans le champ Entrée, entrez une requête SQL pour votre source. Ce paramètre remplace toute table que vous avez choisie dans le jeu de données. Les clauses Order By ne sont pas prises en charge ici, mais vous pouvez définir une instruction SELECT FROM complète. Vous pouvez également utiliser des fonctions de table définies par l’utilisateur. select * from udfGetData() est une fonction UDF dans SQL qui retourne une table. Cette requête génère une table source que vous pouvez utiliser dans votre flux de données. L’utilisation de requêtes est également un excellent moyen de réduire les lignes pour les tests ou les recherches.

Exemple SQL : Select * from MyTable where customerId > 1000 and customerId < 2000

Taille du lot : entrez la taille de lot que doivent avoir les lectures créées à partir d’un large volume de données. Dans les flux de données, ce paramètre permet de définir la mise en cache en colonnes Spark. Il s’agit d’un champ facultatif qui utilise les valeurs par défaut de Spark s’il est laissé vide.

Niveaux d’isolement : La valeur par défaut pour les sources SQL dans le flux de données de mappage est Lecture non validée. Vous pouvez remplacer ici le niveau d’isolement par l’une des valeurs suivantes :

  • Lecture validée
  • Lecture non validée
  • Lecture renouvelée
  • Sérialisable
  • Aucun (ignorer le niveau d’isolement)

Niveau d’isolation

Transformation du récepteur

Les paramètres spécifiques à Azure Synapse Analytics sont disponibles dans l’onglet Paramètres de la transformation du récepteur.

Méthode de mise à jour : détermine les opérations autorisées sur la destination de votre base de données. Par défaut, seules les insertions sont autorisées. Pour mettre à jour, effectuer un upsert ou supprimer des lignes, une transformation alter-row est requise afin de baliser les lignes relatives à ces actions. Pour les mises à jour, les opérations upsert et les suppressions, une ou plusieurs colonnes clés doivent être définies pour déterminer la ligne à modifier.

Action table : Détermine si toutes les lignes de la table de destination doivent être recréées ou supprimées avant l’écriture.

  • Aucune : Aucune action ne sera effectuée sur la table.
  • Recréer : La table sera supprimée et recréée. Obligatoire en cas de création dynamique d’une nouvelle table.
  • Tronquer : Toutes les lignes de la table cible seront supprimées.

Activer le mode de préproduction : cette option permet de charger dans des pools SQL Azure Synapse Analytics à l’aide de la commande de copie, et est recommandée pour la plupart des récepteurs Synpase. Le stockage de préproduction est configuré dans Exécuter l’activité Flux de données.

Taille du lot : contrôle le nombre de lignes écrites dans chaque compartiment. Les plus grandes tailles de lot améliorent la compression et l’optimisation de la mémoire, mais risquent de lever des exceptions de type mémoire insuffisante lors de la mise en cache des données.

Utiliser le schéma du récepteur : Par défaut, une table temporaire est créée sous le schéma récepteur en tant que table intermédiaire. Vous pouvez également décocher l’option Utiliser le schéma de récepteur et spécifier à la place, dans Sélectionner le schéma de base de données de l'utilisateur, un nom de schéma sous lequel Data Factory crée une table intermédiaire pour charger des données en amont et les nettoyer automatiquement une fois l’opération terminée. Vérifiez que vous disposez d’une autorisation de création de table dans la base de données et modifiez l’autorisation sur le schéma.

Capture d’écran montrant le flux de données « Utiliser le schéma de récepteur ».

Pré et post-scripts SQL : Entrez des scripts SQL multilignes qui s’exécutent avant (prétraitement) et après (post-traitement) l’écriture de données dans votre base de données de réception.

Capture d’écran montrant les scripts avant et après traitement SQL dans le flux de données Azure Synapse Analytics.

Conseil

  1. Il est recommandé de diviser les scripts de commandes par lot uniques contenant plusieurs commandes en plusieurs lots.
  2. Seules des instructions DDL (Data Definition Language, langage de définition de données) et DML (Data Manipulation Language, langage de manipulation de données) qui retournent un seul nombre de mises à jour peuvent être exécutées dans un lot. Pour en savoir plus, consultez Exécution d’opérations par lot

Gestion des lignes d’erreur

Lors de l’écriture dans Azure Synapse Analytics, certaines lignes de données peuvent échouer en raison de contraintes définies par la destination. Quelques exemples d’erreurs courantes :

  • Les données binary ou String seront tronquées dans le tableau
  • Impossible d’insérer la valeur NULL dans la colonne
  • Échec de conversion de la valeur en type de données

Par défaut, l’exécution d’un flux de données échouera à la première erreur rencontrée. Vous pouvez choisir de Continuer en cas d’erreur, ce qui permet à votre flux de données de se terminer, même si des lignes individuelles comportent des erreurs. Le service offre différentes options qui vous permettent de gérer ces lignes d’erreur.

Valider une transaction : Indiquez si vos données sont écrites dans une seule transaction ou par lots. Une transaction unique offre de meilleures performances et aucune donnée écrite n’est visible des autres utilisateurs tant que la transaction n’est pas terminée. Les transactions par lots présentent de moins bonnes performances, mais peuvent fonctionner pour des jeux de données volumineux.

Données de sortie rejetées : Si cette option est activée, vous pouvez générer les lignes d’erreur dans un fichier CSV dans le stockage Blob Azure ou dans un compte Azure Data Lake Storage Gen2 de votre choix. Cela écrira les lignes d’erreur avec trois colonnes supplémentaires : l’opération SQL comme INSERT ou UPDATE, le code d’erreur de flux de données et le message d’erreur sur la ligne.

Réussite signalée malgré l’erreur : S’il est activé, le flux de données est marqué comme ayant réussi, même si des lignes d’erreur sont détectées.

Diagramme montrant le traitement des lignes d’erreur dans la transformation de récepteur du flux de données de mappage.

Propriétés de l’activité Lookup

Pour en savoir plus sur les propriétés, consultez Activité Lookup.

Propriétés de l’activité GetMetadata

Pour en savoir plus sur les propriétés, consultez Activité GetMetadata.

Mappage de type de données pour Azure Synapse Analytics

Quand vous copiez des données depuis ou vers Azure Synapse Analytics, les mappages suivants sont utilisés entre les types de données Azure Synapse Analytics et les types de données intermédiaires Azure Data Factory. Ces mappages sont également utilisés lors de la copie de données à partir de ou vers Azure Synapse Analytics à l’aide de pipelines Synapse, puisque les pipelines implémentent également Azure Data Factory au sein d’Azure Synapse. Pour découvrir comment l’activité de copie mappe le schéma et le type de données sources au récepteur, consultez Mappages de schémas et de types de données.

Conseil

Reportez-vous à l’article Types de tables de données dans Azure Synapse Analytics pour découvrir les types de données pris en charge par Azure Synapse Analytics et les solutions de contournement pour les types non pris en charge.

Type de données Azure Synapse Analytics Type de données intermédiaires d’Azure Data Factory
bigint Int64
binary Byte[]
bit Boolean
char String, Char[]
Date DateTime
Datetime DateTime
datetime2 DateTime
Datetimeoffset DateTimeOffset
Decimal Decimal
FILESTREAM attribute (varbinary(max)) Byte[]
Float Double
image Byte[]
int Int32
money Decimal
NCHAR String, Char[]
numeric Decimal
NVARCHAR String, Char[]
real Unique
rowversion Byte[]
smalldatetime DateTime
SMALLINT Int16
SMALLMONEY Decimal
time TimeSpan
TINYINT Byte
UNIQUEIDENTIFIER Guid
varbinary Byte[]
varchar String, Char[]

Contenu connexe

Consultez les formats et magasins de données pris en charge pour obtenir la liste des sources et magasins de données pris en charge en tant que récepteurs par l’activité de copie.