CREATE EXTERNAL DATA SOURCE (Transact-SQL)

Vue d'ensemble : SQL Server 2016

S’applique à : SQL Server 2016 (13.x) et versions ultérieures

Crée une source de données externe pour des requêtes PolyBase. Des sources de données externes sont utilisées pour établir la connectivité et prendre en charge ces principaux cas d’utilisation :

Virtualisation des données et chargement des données à l’aide de PolyBase
Les opérations de chargement en bloc à l’aide de BULK INSERT ou OPENROWSET

Notes

Cette syntaxe varie selon les versions de SQL Server. Utilisez la liste déroulante du sélecteur de version pour choisir la version appropriée.
Pour connaître les fonctionnalités de SQL Server 2019 (15.x), consultez CREATE EXTERNAL DATA SOURCE. Pour connaître les fonctionnalités de SQL Server 2022 (16.x), consultez CREATE EXTERNAL DATA SOURCE.

Syntaxe pour SQL Server 2016

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { HADOOP } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Arguments

data_source_name

Spécifie le nom défini par l’utilisateur de la source de données. Ce nom doit être unique dans la base de données de SQL Server.

LOCATION = `'<prefix>://<path[:port]>'`

Fournit le protocole de connectivité et le chemin d’accès à la source de données externe.

Source de données externe	Préfixe de l’emplacement du connecteur	Chemin d’emplacement	Emplacements pris en charge par produit / service	Authentification
Cloudera CDH ou Hortonworks HDP	`hdfs`	`<Namenode>[:port]`	SQL Server 2016 (13.x), SQL Server 2019 (15.x)	Authentification anonyme ou de base
Compte de stockage Azure (V2)	`wasb[s]`	`<container>@<storage_account>.blob.core.windows.net`	À compter de SQL Server 2016 (13.x) Espace de noms hiérarchique pas pris en charge	Clé du compte de Stockage Azure

Chemin d’emplacement :

<Namenode> = nom de l’ordinateur, URI du service de noms ou adresse IP de Namenode dans le cluster Hadoop. PolyBase doit résoudre tous les noms DNS utilisés par le cluster Hadoop.
port = Le port d’écoute de la source de données externe. Dans Hadoop, le port se trouve à l’aide du paramètre de configuration fs.defaultFS. La valeur par défaut est 8020.
<container> = le conteneur du compte de stockage contenant les données. Les conteneurs racines sont en lecture seule, donc les données ne peuvent pas être réécrites sur le conteneur.
<storage_account> = le nom du compte de stockage de la ressource Azure.
<server_name> = le nom d’hôte.
<instance_name> = le nom de l’instance nommée de SQL Server. Utilisé si votre Service SQL Server Browser est en cours d’exécution sur l’instance cible.

Remarques et conseils supplémentaires lors de la définition de l’emplacement :

Le moteur de base de données SQL Server Database ne vérifie pas l’existence de la source de données externe lorsque l’objet est créé. Pour valider, créez une table externe à l’aide d’une source de données externe.
Utilisez la même source de données externe pour toutes les tables lors de l’interrogation de Hadoop afin de garantir la cohérence des paramètres sémantiques de requête.
wasbs est facultatif, mais recommandé dans SQL Server 2016 (13.x) pour l’accès aux comptes de Stockage Azure, car les données seront envoyées à l’aide d’une connexion TLS/SSL sécurisée.
Pour garantir la réussite des requêtes PolyBase lors du basculement du Namenode Hadoop, envisagez d’utiliser une adresse IP virtuelle pour le Namenodedu cluster Hadoop. Dans le cas contraire, exécutez une commande ALTER EXTERNAL DATA SOURCE pour pointer vers le nouvel emplacement.

CREDENTIAL = credential_name

Spécifie les informations d’identification limitées à la base de données servant à l’authentification auprès de la source de données externe.

CREDENTIAL est requis uniquement si les données ont été sécurisées. CREDENTIAL n’est pas requis pour les jeux de données qui autorisent l’accès anonyme.

Pour créer des informations d’identification délimitées à la base de données, consultez CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

TYPE = [ HADOOP ]

Spécifie le type de source de données externe en cours de configuration. Dans SQL Server 2016, ce paramètre est toujours obligatoire et doit être spécifié uniquement sous la forme HADOOP. Prend en charge les connexions à Cloudera CDH, à Hortonworks HDP ou à un compte Stockage Azure. Le comportement de ce paramètre est différent dans les versions ultérieures de SQL Server.

Pour obtenir un exemple d’utilisation pour charger des données à partir d’un compte Stockage Azure, consultez Créer une source de données externe pour accéder aux données dans Stockage Azure à l’aide de l’interface wasb://TYPE = HADOOP

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Configurez cette valeur facultative lors de la connexion à Cloudera CDH, Hortonworks HDP ou un compte de stockage Azure uniquement. Pour obtenir la liste complète des versions de Hadoop prises en charge, consultez Configuration de la connectivité PolyBase (Transact-SQL).

Lorsque RESOURCE_MANAGER_LOCATION est défini, l’optimiseur de requête prend une décision en fonction du coût pour améliorer les performances. Une tâche MapReduce peut être utilisée pour transmettre le calcul à Hadoop. En spécifiant RESOURCE_MANAGER_LOCATION, il est possible de considérablement réduire le volume des données transférées entre Hadoop et SQL Server, ce qui peut donc améliorer les performances des requêtes.

Si le Gestionnaire des ressources n’est pas spécifié, le transfert de calcul dans Hadoop est désactivé pour les requêtes PolyBase. Créer une source de données externe pour faire référence à Hadoop avec la transmission activée fournit un exemple concret ainsi que des instructions complémentaires.

La valeur RESOURCE_MANAGER_LOCATION n’est pas validée lorsque vous créez la source de données externe. La saisie d’une valeur incorrecte peut entraîner l’échec de la requête au moment de l’exécution chaque fois qu’une transmission est tentée, étant donné que la valeur fournie ne serait pas en mesure d’être résolue.

Pour que PolyBase fonctionne correctement avec une source de données externe Hadoop, les ports des composants de cluster Hadoop suivants doivent être ouverts :

Ports HDFS
- Namenode
- Datanode
Resource Manager
- Envoi du travail
- Historique des travaux

Si le port n’est pas spécifié, la valeur par défaut est déterminée d’après le paramètre actuel de la configuration de la « connexion à hadoop ».

Connexion Hadoop	Port du Gestionnaire de ressources par défaut
1	50300
2	50300
3	8021
4	8032
5	8050
6	8032
7	8050
8	8032

La table suivante présente les ports par défaut de ces composants. Il existe une dépendance de version Hadoop ainsi que la possibilité d’une configuration personnalisée qui n’utilise pas l’attribution de port par défaut.

Composant de cluster Hadoop	Port par défaut
NameNode	8020
DataNode (transfert de données, port IPC sans privilège)	50010
DataNode (transfert de données, port IPC avec privilège)	1019
Soumission de travaux Resource Manager (Hortonworks 1.3)	50300
Soumission de travaux Resource Manager (Cloudera 4.3)	8021
Soumission de travaux Resource Manager (Hortonworks 2.0 sur Windows, Cloudera 5.x sur Linux)	8032
Soumission de travaux Resource Manager (Hortonworks 2.x, 3.0 sur Linux, Hortonworks 2.1-3 sur Windows)	8050
Historique des travaux Resource Manager	10020

Autorisations

Nécessite l’autorisation CONTROL sur la base de données dans SQL Server.

Verrouillage

Prend un verrou partagé sur l’objet EXTERNAL DATA SOURCE.

Sécurité

PolyBase prend en charge l’authentification basée sur le proxy pour la plupart de ces sources de données externes. Créez des informations d’identification au niveau de la base de données pour créer le compte proxy.

Exemples

Important

Pour plus d’informations sur l’installation et l’activation de PolyBase, consultez Installer PolyBase sur Windows

R. Créer une source de données externe pour faire référence à Hadoop

Pour créer une source de données externe afin de référencer votre cluster Hortonworks HDP ou Cloudera CDH Hadoop, spécifiez le nom de l’ordinateur ou l’adresse IP du port et du Namenode Hadoop.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

B. Créer une source de données externe pour faire référence à Hadoop avec transmission activée

Spécifiez l’option RESOURCE_MANAGER_LOCATION pour activer le calcul transmis à Hadoop pour des requêtes PolyBase. Une fois activé, PolyBase prend une décision basée sur les coûts pour déterminer si le calcul de la requête doit être poussé vers Hadoop.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Créer une source de données externe pour faire référence à Hadoop sécurisé par Kerberos

Pour vérifier si le cluster Hadoop est sécurisé par Kerberos, regardez la valeur de la propriété hadoop.security.authentication dans Hadoop core-site.xml. Pour faire référence à un cluster Hadoop sécurisé par Kerberos, vous devez spécifier des informations d’identification limitées à la base de données qui contiennent votre nom d’utilisateur et votre mot de passe Kerberos. La clé principale de la base de données est utilisée pour chiffrer le secret des informations d’identification limitées à la base de données.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Créer une source de données externe pour accéder aux données dans le stockage Azure à l’aide de l’interface wasb://

Cet exemple montre comment créer des informations d’identification limitées à la base de données qui serviront à l’authentification auprès d’un compte de stockage Azure V2. Spécifiez la clé du compte de stockage Azure dans le secret des informations d’identification de la base de données. Vous pouvez spécifier toute chaîne de l’identité des informations d’identification limitées à la base de données, étant donné qu’elle n’est pas utilisée lors de l’authentification auprès du stockage Azure. Notez que quand vous vous connectez au Stockage Azure via le connecteur WASB[s], l’authentification doit être effectuée avec une clé de compte de stockage, et non avec une signature d’accès partagé (SAS).

Dans SQL Server 2016 (13.x), TYPE doit être défini sur HADOOP même lors de l’accès au Stockage Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

Étapes suivantes

Vue d'ensemble : SQL Server 2017

S’applique à : SQL Server 2017 (14.x) uniquement

Virtualisation des données et chargement des données à l’aide de PolyBase
Les opérations de chargement en bloc à l’aide de BULK INSERT ou OPENROWSET

Notes

Cette syntaxe varie selon les versions de SQL Server sur Linux. Utilisez la liste déroulante du sélecteur de version pour choisir la version appropriée.
Pour connaître les fonctionnalités de SQL Server 2019 (15.x), consultez CREATE EXTERNAL DATA SOURCE. Pour connaître les fonctionnalités de SQL Server 2022 (16.x), consultez CREATE EXTERNAL DATA SOURCE.

Notes

Syntaxe pour SQL Server 2017

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Arguments

data_source_name

Spécifie le nom défini par l’utilisateur de la source de données. Ce nom doit être unique dans la base de données de SQL Server.

LOCATION = `'<prefix>://<path[:port]>'`

Fournit le protocole de connectivité et le chemin d’accès à la source de données externe.

Source de données externe	Préfixe de l’emplacement du connecteur	Chemin d’emplacement	Emplacements pris en charge par produit / service	Authentification
Cloudera CDH ou Hortonworks HDP	`hdfs`	`<Namenode>[:port]`	SQL Server 2016 (13.x) vers SQL Server 2019 (15.x) uniquement	Authentification anonyme ou de base
Compte de stockage Azure (V2)	`wasb[s]`	`<container>@<storage_account>.blob.core.windows.net`	À compter de SQL Server 2016 (13.x) Espace de noms hiérarchique pas pris en charge	Clé du compte de Stockage Azure
opérations en bloc	`https`	`<storage_account>.blob.core.windows.net/<container>`	À compter de SQL Server 2017 (14.x)	Signature d’accès partagé (SAP)

Chemin d’emplacement :

<NameNode> = Nom de la machine, nom de l’URI de service ou adresse IP du Namenode du cluster Hadoop. PolyBase doit résoudre tous les noms DNS utilisés par le cluster Hadoop.
port = Le port d’écoute de la source de données externe. Dans Hadoop, le port se trouve à l’aide du paramètre de configuration fs.defaultFS. La valeur par défaut est 8020.
<container> = le conteneur du compte de stockage contenant les données. Les conteneurs racines sont en lecture seule, donc les données ne peuvent pas être réécrites sur le conteneur.
<storage_account> = le nom du compte de stockage de la ressource Azure.
<server_name> = le nom d’hôte.
<instance_name> = le nom de l’instance nommée de SQL Server. Utilisé si votre Service SQL Server Browser est en cours d’exécution sur l’instance cible.

Remarques et conseils supplémentaires lors de la définition de l’emplacement :

Le moteur de base de données SQL Server Database ne vérifie pas l’existence de la source de données externe lorsque l’objet est créé. Pour valider, créez une table externe à l’aide d’une source de données externe.
Utilisez la même source de données externe pour toutes les tables lors de l’interrogation de Hadoop afin de garantir la cohérence des paramètres sémantiques de requête.
Spécifiez Driver={<Name of Driver>} lors de la connexion via ODBC.
wasbs est facultatif, mais recommandé dans SQL Server 2017 (14.x) pour l’accès aux comptes de Stockage Azure, car les données seront envoyées à l’aide d’une connexion TLS/SSL sécurisée.
Pour garantir la réussite des requêtes PolyBase lors du basculement du Namenode Hadoop, envisagez d’utiliser une adresse IP virtuelle pour le Namenodedu cluster Hadoop. Dans le cas contraire, exécutez une commande ALTER EXTERNAL DATA SOURCE pour pointer vers le nouvel emplacement.

CREDENTIAL = credential_name

Spécifie les informations d’identification limitées à la base de données servant à l’authentification auprès de la source de données externe.

Remarques et conseils supplémentaires lors de la création d’informations d’identification :

CREDENTIAL est requis uniquement si les données ont été sécurisées. CREDENTIAL n’est pas requis pour les jeux de données qui autorisent l’accès anonyme.
Quand TYPE = BLOB_STORAGE, les informations d’identification doivent être créées avec l’identité SHARED ACCESS SIGNATURE.
TYPE = BLOB_STORAGE est autorisé uniquement pour les opérations en bloc. Vous ne pouvez pas créer de tables externes pour une source de données externe avec TYPE = BLOB_STORAGE.
Notez que quand vous vous connectez au Stockage Azure via le connecteur WASB[s], l’authentification doit être effectuée avec une clé de compte de stockage, et non avec une signature d’accès partagé (SAS).
Quand TYPE = HADOOP, les informations d’identification doivent être créées à l’aide de la clé de compte de stockage en tant que SECRET.

Il existe plusieurs façons de créer une signature d’accès partagé :

Vous pouvez créer un jeton SAS en accédant au portail Azure -><Votre_compte_de_stockage> -> Signature d’accès partagé -> Configurer les autorisations -> Générer une signature SAS et la chaîne de connexion. Pour plus d’informations, consultez Générer une signature d’accès partagé.
Vous pouvez créer et configurer une SAS avec l’Explorateur Stockage Azure.
Vous pouvez créer une SAS par programmation avec PowerShell, Azure CLI, .NET et l’API REST. Pour plus d’informations, consultez Accorder un accès limité aux ressources du Stockage Azure à l’aide des signatures d’accès partagé (SAS).
Le jeton SAS doit être configuré comme suit :
- Lorsqu’un jeton SAS est généré, son nom commence par un point d’interrogation (« ? »). Retirez le caractère ? de début lorsqu’il est configuré en tant que SECRET.
- Utilisez une période d’expiration valide (toutes les dates sont au format UTC).
- Accordez au moins l’autorisation en lecture sur le fichier qui doit être chargé (par exemple srt=o&sp=r). Plusieurs signatures d’accès partagé peuvent être créées pour différents cas d’usage. Les autorisations doivent être accordées comme suit :
Action Autorisation

Lire les données d’un fichier Lire

Lire les données de plusieurs fichiers et sous-dossiers Lire et Liste

Pour obtenir un exemple d’utilisation de CREDENTIAL avec SHARED ACCESS SIGNATURE et TYPE = BLOB_STORAGE, consultez Créer une source de données externe pour exécuter des opérations en bloc et récupérer des données du Stockage Azure dans SQL Database

Pour créer des informations d’identification délimitées à la base de données, consultez CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

TYPE = [ HADOOP | BLOB_STORAGE ]

Spécifie le type de source de données externe en cours de configuration. Ce paramètre n’est pas toujours obligatoire et doit être spécifié uniquement lors de la connexion à Cloudera CDH, Hortonworks HDP, un compte de stockage Azure ou à Azure Data Lake Storage Gen2.

Utilisez HADOOP quand la source de données externe est Cloudera CDH, Hortonworks HDP, un compte de stockage Azure ou Azure Data Lake Storage Gen2.
Utilisez BLOB_STORAGE quand vous exécutez des opérations en bloc depuis un compte de Stockage Azure à l’aide de BULK INSERT ou d’OPENROWSET. Introduite dans SQL Server 2017 (14.x). Utilisez HADOOP quand vous prévoyez d’utiliser CREATE EXTERNAL TABLE sur Stockage Azure.

Notes

TYPE doit être défini sur HADOOP même lors de l’accès à Stockage Azure.

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Lorsque RESOURCE_MANAGER_LOCATION est défini, l’optimiseur de requête prend une décision basée sur le coût pour améliorer les performances. Une tâche MapReduce peut être utilisée pour transmettre le calcul à Hadoop. En spécifiant RESOURCE_MANAGER_LOCATION, il est possible de considérablement réduire le volume des données transférées entre Hadoop et SQL Server, ce qui peut donc améliorer les performances des requêtes.

Pour que PolyBase fonctionne correctement avec une source de données externe Hadoop, les ports des composants de cluster Hadoop suivants doivent être ouverts :

Ports HDFS
- Namenode
- Datanode
Resource Manager
- Envoi du travail
- Historique des travaux

Si le port n’est pas spécifié, la valeur par défaut est déterminée d’après le paramètre actuel de la configuration de la « connexion à hadoop ».

Connexion Hadoop	Port du Gestionnaire de ressources par défaut
1	50300
2	50300
3	8021
4	8032
5	8050
6	8032
7	8050
8	8032

La table suivante présente les ports par défaut de ces composants. Notez qu’il existe une dépendance de version Hadoop ainsi que la possibilité d’une configuration personnalisée qui n’utilise pas l’attribution de port par défaut.

Composant de cluster Hadoop	Port par défaut
NameNode	8020
DataNode (transfert de données, port IPC sans privilège)	50010
DataNode (transfert de données, port IPC avec privilège)	1019
Soumission de travaux Resource Manager (Hortonworks 1.3)	50300
Soumission de travaux Resource Manager (Cloudera 4.3)	8021
Soumission de travaux Resource Manager (Hortonworks 2.0 sur Windows, Cloudera 5.x sur Linux)	8032
Soumission de travaux Resource Manager (Hortonworks 2.x, 3.0 sur Linux, Hortonworks 2.1-3 sur Windows)	8050
Historique des travaux Resource Manager	10020

Autorisations

Nécessite l’autorisation CONTROL sur la base de données dans SQL Server.

Verrouillage

Prend un verrou partagé sur l’objet EXTERNAL DATA SOURCE.

Sécurité

Un jeton SAP avec le type HADOOP n’est pas pris en charge. Il est uniquement pris en charge avec un type = BLOB_STORAGE lorsqu’une clé d’accès de compte de stockage est utilisée à la place. Toute tentative de créer une source de données externe avec le type HADOOP et les informations d’identification SAS échoue avec l’erreur :

Msg 105019, Level 16, State 1 - EXTERNAL TABLE access failed due to internal error: 'Java exception raised on call to HdfsBridge_Connect. Java exception message: Parameters provided to connect to the Azure storage account are not valid.: Error [Parameters provided to connect to the Azure storage account are not valid.] occurred while accessing external file.'

Exemples

Important

Pour plus d’informations sur l’installation et l’activation de PolyBase, consultez Installer PolyBase sur Windows

R. Créer une source de données externe pour faire référence à Hadoop

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

B. Créer une source de données externe pour faire référence à Hadoop avec transmission activée

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Créer une source de données externe pour faire référence à Hadoop sécurisé par Kerberos

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Créer une source de données externe pour accéder aux données dans le stockage Azure à l’aide de l’interface wasb://

Dans cet exemple, la source de données externe est un compte de stockage Azure V2 appelé logs. Le conteneur de stockage est appelé daily. La source de données externe de stockage Azure sert uniquement au transfert des données. Elle ne prend pas en charge le pushdown de prédicats. Les espaces de noms hiérarchiques ne sont pas pris en charge lors de l’accès aux données via l’interface wasb://. Notez que quand vous vous connectez au Stockage Azure via le connecteur WASB[s], l’authentification doit être effectuée avec une clé de compte de stockage, et non avec une signature d’accès partagé (SAS).

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

Exemples : Opérations en bloc

Important

N’ajoutez pas de / de fin, de nom de fichier ni de paramètres de signature d’accès partagé à la fin de l’URL LOCATION lors de la configuration d’une source de données externe pour les opérations en bloc.

E. Créer une source de données externe pour les opérations en bloc de récupération de données dans le stockage Azure

S’applique à : SQL Server 2017 (14.x) et versions ultérieures.

Utilisez la source de données suivante pour les opérations en bloc à l’aide de BULK INSERT ou OPENROWSET. L’identifiant utilisé doit donner à l’identité la valeur SHARED ACCESS SIGNATURE, ne doit pas avoir le premier ? dans le jeton SAS, doit avoir au moins les droits de lecture sur le fichier à charger (par exemple srt=o&sp=r), et doit présenter une période d’expiration valide (toutes les dates sont en heure UTC). Pour plus d’informations sur les signatures d’accès partagé, consultez Utilisation des signatures d’accès partagé (SAP).

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    -- Remove ? from the beginning of the SAS token
    SECRET = '<azure_storage_account_key>';

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices,
    TYPE = BLOB_STORAGE
);

Pour voir une utilisation de cet exemple, consultez l’exemple BULK INSERT.

Étapes suivantes

Vue d'ensemble : SQL Server 2019

S’applique à : SQL Server 2019 (15.x) et versions ultérieures

Virtualisation des données et chargement des données à l’aide de PolyBase
Les opérations de chargement en bloc à l’aide de BULK INSERT ou OPENROWSET

Notes

Syntaxe pour SQL Server 2019

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
    [ [ , ] TYPE = { HADOOP | BLOB_STORAGE } ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Arguments

data_source_name

Spécifie le nom défini par l’utilisateur de la source de données. Ce nom doit être unique dans la base de données de SQL Server.

LOCATION = `'<prefix>://<path[:port]>'`

Fournit le protocole de connectivité et le chemin d’accès à la source de données externe.

Source de données externe	Préfixe de l’emplacement du connecteur	Chemin d’emplacement	Emplacements pris en charge par produit / service	Authentification
Cloudera CDH ou Hortonworks HDP	`hdfs`	`<Namenode>[:port]`	SQL Server 2016 (13.x), SQL Server 2019 (15.x)	Authentification anonyme ou de base
Compte de stockage Azure (V2)	`wasb[s]`	`<container>@<storage_account>.blob.core.windows.net`	À compter de SQL Server 2016 (13.x) Espace de noms hiérarchique pas pris en charge	Clé du compte de Stockage Azure
SQL Server	`sqlserver`	`<server_name>[\<instance_name>][:port]`	À compter de SQL Server 2019 (15.x)	Authentification SQL uniquement
Oracle	`oracle`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x)	Authentification de base uniquement
Teradata	`teradata`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x)	Authentification de base uniquement
MongoDB ou API Cosmos DB pour MongoDB	`mongodb`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x)	Authentification de base uniquement
ODBC générique	`odbc`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x) - Windows uniquement	Authentification de base uniquement
opérations en bloc	`https`	`<storage_account>.blob.core.windows.net/<container>`	À compter de SQL Server 2017 (14.x)	Signature d’accès partagé (SAP)
Azure Data Lake Storage Gen2	`abfs[s]`	`abfss://<container>@<storage _account>.dfs.core.windows.net`	À compter de SQL Server 2019 (15.x) CU11+.	Storage Access Key (Clé d’accès de stockage)
Pool de données des clusters Big Data SQL Server	`sqldatapool`	`sqldatapool://controller-svc/default`	Uniquement pris en charge dans les clusters Big Data SQL Server 2019	Authentification de base uniquement
Pool de stockage des clusters Big Data SQL Server	`sqlhdfs`	`sqlhdfs://controller-svc/default`	Uniquement pris en charge dans les clusters Big Data SQL Server 2019	Authentification de base uniquement

Chemin d’emplacement :

<Namenode> = nom de l’ordinateur, URI du service de noms ou adresse IP de Namenode dans le cluster Hadoop. PolyBase doit résoudre tous les noms DNS utilisés par le cluster Hadoop.
port = Le port d’écoute de la source de données externe. Dans Hadoop, le port se trouve à l’aide du paramètre de configuration fs.defaultFS. La valeur par défaut est 8020.
<container> = le conteneur du compte de stockage contenant les données. Les conteneurs racines sont en lecture seule, donc les données ne peuvent pas être réécrites sur le conteneur.
<storage_account> = le nom du compte de stockage de la ressource Azure.
<server_name> = le nom d’hôte.
<instance_name> = le nom de l’instance nommée de SQL Server. Utilisé si votre Service SQL Server Browser est en cours d’exécution sur l’instance cible.

Remarques et conseils supplémentaires lors de la définition de l’emplacement :

Le moteur de base de données SQL Server Database ne vérifie pas l’existence de la source de données externe lorsque l’objet est créé. Pour valider, créez une table externe à l’aide d’une source de données externe.
Utilisez la même source de données externe pour toutes les tables lors de l’interrogation de Hadoop afin de garantir la cohérence des paramètres sémantiques de requête.
Vous pouvez utiliser le connecteur sqlserver pour connecter SQL Server 2019 (15.x) à un autre SQL Server ou à Azure SQL Database.
Spécifiez Driver={<Name of Driver>} lors de la connexion via ODBC.
L’utilisation de wasbs ou abfss est facultative, mais recommandée dans SQL Server 2019 (15.x) pour l’accès aux comptes de Stockage Azure, car les données seront envoyées à l’aide d’une connexion TLS/SSL sécurisée.
Les API abfs ou abfss sont prises en charge pour l’accès aux comptes Stockage Azure à compter de SQL Server 2019 (15.x) CU11. Pour plus d’informations, consultez Pilote ABFS (Azure Blob Filesystem).
L’option d’espace de noms hiérarchique pour les comptes Stockage Azure (V2) avec abfs[s] est prise en charge par le biais d’Azure Data Lake Storage Gen2 à compter de SQL Server 2019 (15.x) CU11+. Sinon, l’option d’espace de noms hiérarchique n’est pas prise en charge et doit rester désactivée.
Pour garantir la réussite des requêtes PolyBase lors du basculement du Namenode Hadoop, envisagez d’utiliser une adresse IP virtuelle pour le Namenodedu cluster Hadoop. Dans le cas contraire, exécutez une commande ALTER EXTERNAL DATA SOURCE pour pointer vers le nouvel emplacement.
Les types sqlhdfs et sqldatapool sont pris en charge pour la connexion entre l’instance maître et le pool de stockage d’un cluster Big Data. Pour Cloudera CDH ou Hortonworks HDP, utilisez hdfs. Pour plus d’informations sur l’utilisation de sqlhdfs pour interroger les pools de stockage des clusters Big Data SQL Server, consultez Interroger HDFS dans un cluster Big Data SQL Server 2019.
La prise en charge par SQL Server des sources de données externes HDFS Cloudera (CDP) et Hortonworks (HDP) sera supprimée et ne sera pas incluse dans SQL Server 2022 (16.x). Pour plus d’informations, consultez Options Big data sur la plateforme Microsoft SQL Server.

CONNECTION_OPTIONS = key_value_pair

Spécifié pour SQL Server 2019 (15.x) et versions ultérieures. Spécifie des options supplémentaires lors de la connexion via ODBC à une source de données externe. Pour utiliser plusieurs options de connexion, séparez-les par un point-virgule.

S’applique aux connexions ODBC génériques ainsi qu’aux connecteurs ODBC intégrés pour SQL Server, Oracle, Teradata, MongoDB et l’API Azure Cosmos DB pour MongoDB.

key_value_pair correspond au mot clé et à la valeur d’une option de connexion spécifique. Les mots clés et les valeurs disponibles dépendent du type de source de données externe. Au minimum, le nom du pilote est nécessaire, mais d’autres options telles que APP='<your_application_name>' ou ApplicationIntent= ReadOnly|ReadWrite s’avèrent également utiles à paramétrer et facilitent la résolution des problèmes.

Les paires clé-valeur possibles sont spécifiques au fournisseur pour le fournisseur de source de données externe. Pour plus d’informations sur chaque fournisseur, consultez CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.

À compter de la mise à jour cumulative 19 de SQL Server 2019 (15.x), des mots clés supplémentaires ont été introduits pour prendre en charge les fichiers Oracle TNS :

Le mot clé TNSNamesFile spécifie le chemin du fichier tnsnames.ora sur le serveur Oracle.
Le mot clé ServerName spécifie l’alias utilisé dans le fichier tnsnames.ora qui sera utilisé pour remplacer le nom d’hôte et le port.

PUSHDOWN = ON | OFF

Spécifié pour SQL Server 2019 (15.x) uniquement. Indique si le calcul peut être transmis à la source de données externe. La valeur par défaut est ON.

PUSHDOWN est pris en charge pour la connexion à SQL Server, Oracle, Teradata, MongoDB, l’API Azure Cosmos DB pour MongoDB ou ODBC au niveau de la source de données externe.

L’activation ou la désactivation de la transmission au niveau de la requête s’effectue au moyen d’un indicateur.

CREDENTIAL = credential_name

Spécifie les informations d’identification limitées à la base de données servant à l’authentification auprès de la source de données externe.

Remarques et conseils supplémentaires lors de la création d’informations d’identification :

CREDENTIAL est requis uniquement si les données ont été sécurisées. CREDENTIAL n’est pas requis pour les jeux de données qui autorisent l’accès anonyme.
Quand TYPE = BLOB_STORAGE, les informations d’identification doivent être créées avec l’identité SHARED ACCESS SIGNATURE.
- TYPE = BLOB_STORAGE est autorisé uniquement pour les opérations en bloc. Vous ne pouvez pas créer de tables externes pour une source de données externe avec TYPE = BLOB_STORAGE.

Il existe plusieurs façons de créer une signature d’accès partagé :

Vous pouvez créer un jeton SAS en accédant au portail Azure -><Votre_compte_de_stockage> -> Signature d’accès partagé -> Configurer les autorisations -> Générer une signature SAS et la chaîne de connexion. Pour plus d’informations, consultez Générer une signature d’accès partagé.
Vous pouvez créer et configurer une SAS avec l’Explorateur Stockage Azure.
Vous pouvez créer une SAS par programmation avec PowerShell, Azure CLI, .NET et l’API REST. Pour plus d’informations, consultez Accorder un accès limité aux ressources du Stockage Azure à l’aide des signatures d’accès partagé (SAS).
Le jeton SAS doit être configuré comme suit :
- Lorsqu’un jeton SAS est généré, son nom commence par un point d’interrogation (« ? »). Retirez le caractère ? de début lorsqu’il est configuré en tant que SECRET.
- Utilisez une période d’expiration valide (toutes les dates sont au format UTC).
- Accordez au moins l’autorisation en lecture sur le fichier qui doit être chargé (par exemple srt=o&sp=r). Plusieurs signatures d’accès partagé peuvent être créées pour différents cas d’usage. Les autorisations doivent être accordées comme suit :
Action Autorisation

Lire les données d’un fichier Lire

Lire les données de plusieurs fichiers et sous-dossiers Lire et Liste

Pour créer des informations d’identification délimitées à la base de données, consultez CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

TYPE = [ HADOOP | BLOB_STORAGE ]

Dans SQL Server 2019 (15.x), ne spécifiez pas de TYPE, sauf si vous vous connectez à Cloudera CDH, à Hortonworks HDP ou à un compte Stockage Azure.
Utilisez HADOOP quand la source de données externe est Cloudera CDH, Hortonworks HDP, un compte de stockage Azure ou Azure Data Lake Storage Gen2.
Utilisez BLOB_STORAGE quand vous exécutez des opérations en bloc depuis un compte Stockage Azure avec BULK INSERT ou OPENROWSET dans SQL Server 2017 (14.x). Utilisez HADOOP quand vous prévoyez d’utiliser CREATE EXTERNAL TABLE sur Stockage Azure.
La prise en charge par SQL Server des sources de données externes HDFS Cloudera (CDP) et Hortonworks (HDP) sera supprimée et ne sera pas incluse dans SQL Server 2022 (16.x). Pour plus d’informations, consultez Options Big data sur la plateforme Microsoft SQL Server.

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Dans SQL Server 2019 (15.x), ne spécifiez pas RESOURCE_MANAGER_LOCATION, sauf si vous vous connectez à Cloudera CDH, à Hortonworks HDP ou à un compte Stockage Azure.

Pour que PolyBase fonctionne correctement avec une source de données externe Hadoop, les ports des composants de cluster Hadoop suivants doivent être ouverts :

Ports HDFS
- Namenode
- Datanode
Resource Manager
- Envoi du travail
- Historique des travaux

Si le port n’est pas spécifié, la valeur par défaut est déterminée d’après le paramètre actuel de la configuration de la « connexion à hadoop ».

Connexion Hadoop	Port du Gestionnaire de ressources par défaut
1	50300
2	50300
3	8021
4	8032
5	8050
6	8032
7	8050
8	8032

Composant de cluster Hadoop	Port par défaut
NameNode	8020
DataNode (transfert de données, port IPC sans privilège)	50010
DataNode (transfert de données, port IPC avec privilège)	1019
Soumission de travaux Resource Manager (Hortonworks 1.3)	50300
Soumission de travaux Resource Manager (Cloudera 4.3)	8021
Soumission de travaux Resource Manager (Hortonworks 2.0 sur Windows, Cloudera 5.x sur Linux)	8032
Soumission de travaux Resource Manager (Hortonworks 2.x, 3.0 sur Linux, Hortonworks 2.1-3 sur Windows)	8050
Historique des travaux Resource Manager	10020

Autorisations

Nécessite l’autorisation CONTROL sur la base de données dans SQL Server.

Verrouillage

Prend un verrou partagé sur l’objet EXTERNAL DATA SOURCE.

Sécurité

Quand vous vous connectez au stockage ou au pool de données dans un cluster Big data de SQL Server 2019, les informations d’identification de l’utilisateur sont transmises au système back-end. Créez des connexions dans le pool de données lui-même pour activer l’authentification en transfert direct.

Exemples

Important

Pour plus d’informations sur l’installation et l’activation de PolyBase, consultez Installer PolyBase sur Windows

R. Créer une source de données externe dans SQL Server 2019 pour référencer Oracle

Pour créer une source de données externe qui fait référence à Oracle, assurez-vous d’avoir des informations d’identification de niveau base de données. Vous pouvez également, si vous le souhaitez, activer ou désactiver la transmission des calculs par rapport à cette source de données.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CREDENTIAL = OracleProxyAccount,
    PUSHDOWN = ON
);

Si vous le souhaitez, la source de données externe à Oracle peut utiliser l’authentification du proxy pour fournir un contrôle d’accès précis. Un utilisateur proxy peut être configuré pour disposer d’un accès limité par rapport à l’utilisateur dont l’identité est empruntée.

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

Vous pouvez aussi utiliser l’authentification TNS.

À compter de la mise à jour cumulative 19 de SQL Server 2019 (15.x), CREATE EXTERNAL DATA SOURCE prend désormais en charge l’utilisation des fichiers TNS lors de la connexion à Oracle. Le paramètre CONNECTION_OPTIONS a été étendu et utilise désormais TNSNamesFile et ServerName comme variables pour parcourir le fichier tnsnames.ora et établir une connexion avec le serveur.

Dans l’exemple suivant, SQL Server recherche à l’exécution l’emplacement du fichier tnsnames.ora spécifié par TNSNamesFile, et l’hôte et le port réseau spécifiés par ServerName.

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

Pour obtenir des exemples supplémentaires pour d’autres sources de données telles que MongoDB, consultez Configurer PolyBase pour accéder aux données externes dans MongoDB.

B. Créer une source de données externe pour faire référence à Hadoop

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

C. Créer une source de données externe pour faire référence à Hadoop avec transmission activée

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Créer une source de données externe pour faire référence à Hadoop sécurisé par Kerberos

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

E. Créer une source de données externe pour accéder aux données dans le stockage Azure à l’aide de l’interface wasb://

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

F. Créer une source de données externe pour référencer une instance nommée SQL Server par le biais d’une connectivité PolyBase

S’applique à : SQL Server 2019 (15.x) et versions ultérieures

Pour créer une source de données externe qui référence une instance nommée de SQL Server, utilisez CONNECTION_OPTIONS pour spécifier le nom de l’instance.

Dans l’exemple suivant, WINSQL2019 est le nom d’hôte, et SQL2019 le nom de l’instance. 'Server=%s\SQL2019' est la paire clé-valeur.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

Vous pouvez également utiliser un port pour vous connecter à une instance par défaut de SQL Server.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

G. Créer une source de données externe pour référencer un réplica secondaire lisible de Always On groupe de disponibilité

S’applique à : SQL Server 2019 (15.x) et versions ultérieures

Pour créer une source de données externe qui fait référence à une réplica secondaire lisible de SQL Server, utilisez CONNECTION_OPTIONS pour spécifier le paramètre ApplicationIntent=ReadOnly. En outre, vous devez définir la base de données de disponibilité comme Database={dbname} dans CONNECTION_OPTIONS, ou définir la base de données de disponibilité comme base de données par défaut de la connexion utilisée pour les informations d’identification délimitées à la base de données. Vous devez effectuer cette opération sur tous les réplicas de disponibilité du groupe de disponibilité.

Tout d’abord, créez les informations d’identification délimitées par la base de données, stockant les informations d’identification pour une connexion SQL authentifiée. Le connecteur ODBC SQL pour PolyBase prend uniquement en charge l’authentification de base. Avant que vous ne créiez des informations d’identification incluses dans l’étendue de la base de données, la base de données doit avoir une clé principale pour protéger les informations d’identification. Pour plus d’informations, consultez CREATE MASTER KEY. L’exemple suivant crée des informations d’identification étendues à la base de données, fournissez votre propre connexion et mot de passe.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

Ensuite, créez la nouvelle source de données externe.

Que vous soyez inclus Database=dbname dans la CONNECTION_OPTIONS base de données de disponibilité ou défini comme base de données par défaut pour la connexion dans les informations d’identification délimitées à la base de données, vous devez toujours fournir le nom de la base de données via un nom en trois parties dans l’instruction CREATE EXTERNAL TABLE, dans le paramètre LOCATION. Pour un exemple, voir CREATE EXTERNAL TABLE

Dans l’exemple suivant, WINSQL2019AGL est le nom de l’écouteur de groupe de disponibilité, et dbname le nom de la base de données ciblée par l’instruction CREATE EXTERNAL TABLE.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = SQLServerCredentials
);

Vous pouvez illustrer le comportement de redirection du groupe de disponibilité en spécifiant ApplicationIntent et en créant une table externe sur l’affichage sys.serverssystème. Dans l’exemple de script suivant, deux sources de données externes sont créées et une table externe est créée pour chacune d’elles. Utilisez les vues pour tester le serveur qui répond à la connexion. Des résultats similaires peuvent également être obtenus via la fonctionnalité de routage en lecture seule. Pour plus d'informations, consultez la section Configurer le routage en lecture seule pour un groupe de disponibilité Always On .

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

Dans la base de données du groupe de disponibilité, créez une vue pour retourner sys.servers et le nom de l’instance locale, ce qui vous permet d’identifier le réplica qui répond à la requête. Pour plus d’informations, consultez sys.servers.

CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO

Ensuite, créez une table externe sur l’instance source :

CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO

Exemples : Opérations en bloc

Important

H. Créer une source de données externe pour les opérations en bloc de récupération de données dans le stockage Azure

S’applique à : SQL Server 2017 (14.x) et SQL Server 2019 (15.x)

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    -- Remove ? from the beginning of the SAS token
    SECRET = '<azure_shared_access_signature>';

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices,
    TYPE = BLOB_STORAGE
);

Pour voir une utilisation de cet exemple, consultez l’exemple BULK INSERT.

I. Créer une source de données externe pour accéder aux données dans le stockage Azure à l’aide de l’interface abfs://

S’applique à : SQL Server 2019 (15.x) CU11 et versions ultérieures

Dans cet exemple, la source de données externe est un compte Azure Data Lake Storage Gen2 logs, utilisant le pilote Azure Blob File System (ABFS). Le conteneur de stockage est appelé daily. La source de données externe Azure Data Lake Storage Gen2 est destinée au transfert de données uniquement, car le pushdown de prédicats n’est pas prise en charge.

Cet exemple montre comment créer des informations d’identification limitées à la base de données qui serviront à l’authentification auprès d’un compte Azure Data Lake Storage Gen2. Spécifiez la clé du compte de stockage Azure dans le secret des informations d’identification de la base de données. Vous pouvez spécifier toute chaîne de l’identité des informations d’identification limitées à la base de données, étant donné qu’elle n’est pas utilisée lors de l’authentification auprès du stockage Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abfss://daily@logs.dfs.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

J. Créer une source de données externe avec ODBC générique vers PostgreSQL

Comme dans les exemples précédents, créez d’abord une clé principale de base de données et des informations d’identification délimitées à la base de données. Les informations d’identification incluses dans l’étendue de la base de données seront utilisées pour la source de données externe. Cet exemple suppose également qu’un fournisseur de données ODBC générique pour PostgreSQL est installé sur le serveur.

Dans cet exemple, le fournisseur de données ODBC générique est utilisé pour établir une connexion à un serveur de base de données PostgreSQL dans le même réseau, où le nom de domaine complet du serveur PostgreSQL est POSTGRES1, avec le port par défaut TCP 5432.

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

Étapes suivantes

Vue d'ensemble : SQL Server 2022

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

Virtualisation des données et chargement des données à l’aide de PolyBase
Les opérations de chargement en bloc à l’aide de BULK INSERT ou OPENROWSET

Notes

Cette syntaxe varie selon les versions de SQL Server. Utilisez la liste déroulante du sélecteur de version pour choisir la version appropriée. Ce contenu s’applique à SQL Server 2022 (16.x) et versions ultérieures.

Syntaxe pour SQL Server 2022 (et versions plus récentes)

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CONNECTION_OPTIONS = '<key_value_pairs>'[,...]]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] PUSHDOWN = { ON | OFF } ]
  )
[ ; ]

Arguments

data_source_name

Spécifie le nom défini par l’utilisateur de la source de données. Ce nom doit être unique dans la base de données de SQL Server.

LOCATION = `'<prefix>://<path[:port]>'`

Fournit le protocole de connectivité et le chemin d’accès à la source de données externe.

Source de données externe	Préfixe de l’emplacement du connecteur	Chemin d’emplacement	Emplacements pris en charge par produit / service	Authentification
Compte de stockage Azure (V2)	`abs`	`abs://<container_name>@<storage_account_name>.blob.core.windows.net/` or `abs://<storage_account_name>.blob.core.windows.net/<container_name>`	À compter de SQL Server 2022 (16.x) L’espace de noms hiérarchique est pris en charge.	Signature d’accès partagé (SAP)
Azure Data Lake Storage Gen2	`adls`	`adls://<container_name>@<storage_account_name>.dfs.core.windows.net/` or `adls://<storage_account_name>.dfs.core.windows.net/<container_name>`	À compter de SQL Server 2022 (16.x)	Signature d’accès partagé (SAP)
SQL Server	`sqlserver`	`<server_name>[\<instance_name>][:port]`	À compter de SQL Server 2019 (15.x)	Authentification SQL uniquement
Oracle	`oracle`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x)	Authentification de base uniquement
Teradata	`teradata`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x)	Authentification de base uniquement
MongoDB ou API Cosmos DB pour MongoDB	`mongodb`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x)	Authentification de base uniquement
ODBC générique	`odbc`	`<server_name>[:port]`	À compter de SQL Server 2019 (15.x) - Windows uniquement	Authentification de base uniquement
opérations en bloc	`https`	`<storage_account>.blob.core.windows.net/<container>`	À compter de SQL Server 2017 (14.x)	Signature d’accès partagé (SAP)
Stockage d’objets compatible S3	`s3`	- Compatible S3 : `s3://<server_name>:<port>/` - AWS S3 : `s3://<bucket_name>.S3.amazonaws.com[:port]/<folder>` ou `s3://s3.amazonaws.com[:port]/<bucket_name>/<folder>`	À compter de SQL Server 2022 (16.x)	De base ou pass-through (STS) *

* Doit être des informations d’identification délimitées à la base de données, où l’IDENTITÉ est codée IDENTITY = 'S3 Access Key' en dur et où l’argument SECRET est au format = '<AccessKeyID>:<SecretKeyID>' ou utilise l’autorisation directe (STS). Pour plus d’informations, consultez Configurer PolyBase pour accéder aux données externes dans le stockage d’objets compatible S3.

Chemin d’emplacement :

port = Le port d’écoute de la source de données externe. Facultatif dans de nombreux cas, en fonction de la configuration réseau.
<container_name> = le conteneur du compte de stockage contenant les données. Les conteneurs racines sont en lecture seule, donc les données ne peuvent pas être réécrites sur le conteneur.
<storage_account> = le nom du compte de stockage de la ressource Azure.
<server_name> = le nom d’hôte.
<instance_name> = le nom de l’instance nommée de SQL Server. Utilisé si votre Service SQL Server Browser est en cours d’exécution sur l’instance cible.
<ip_address>:<port> = pour le stockage d’objets compatible S3 uniquement (à compter de SQL Server 2022 (16.x)), le point de terminaison et le port utilisés pour se connecter au stockage compatible S3.
<bucket_name> = Pour le stockage d’objets compatible S3 uniquement (à partir de SQL Server 2022 (16.x)), spécifique à la plateforme de stockage.
<region> = Pour le stockage d’objets compatible S3 uniquement (à partir de SQL Server 2022 (16.x)), spécifique à la plateforme de stockage.
<folder> = Partie du chemin de stockage dans l’URL de stockage.

Remarques et conseils supplémentaires lors de la définition de l’emplacement :

Le moteur de base de données SQL Server Database ne vérifie pas l’existence de la source de données externe lorsque l’objet est créé. Pour valider, créez une table externe à l’aide d’une source de données externe.
Vous pouvez utiliser le connecteur sqlserver pour connecter SQL Server 2019 (15.x) à un autre SQL Server ou à Azure SQL Database.
Spécifiez Driver={<Name of Driver>} lors de la connexion via ODBC.
L’option d’espace de noms hiérarchique pour les comptes Stockage Azure (V2) utilisant le préfixe adls est prise en charge via Azure Data Lake Storage Gen2 dans SQL Server 2022 (16.x).

La prise en charge par SQL Server des sources de données externes HDFS Cloudera (CDP) et Hortonworks (HDP) est supprimée et n’est pas incluse dans SQL Server 2022 (16.x). Il n’est pas nécessaire d’utiliser l’argument TYPE dans SQL Server 2022 (16.x).
Pour plus d’informations sur le stockage d’objets compatible S3 et PolyBase à compter de SQL Server 2022 (16.x), consultez Configurer PolyBase pour accéder aux données externes dans le stockage d’objets compatible S3. Pour obtenir un exemple d’interrogation d’un fichier Parquet dans le stockage d’objets compatible S3, consultez Virtualisation de Parquet dans un stockage d’objets compatible S3 avec PolyBase.
Différent des versions précédentes, dans SQL Server 2022 (16.x), le préfixe utilisé pour le compte Stockage Azure (v2) est passé de wasb[s] à abs.
Différent des versions précédentes, dans SQL Server 2022 (16.x), le préfixe utilisé pour Azure Data Lake Storage Gen2 est passé de abfs[s] à adls.
Pour obtenir un exemple utilisant PolyBase pour virtualiser un fichier CSV dans Stockage Azure, consultez Virtualiser un fichier CSV avec PolyBase.
Pour obtenir un exemple utilisant PolyBase pour virtualiser une table delta dans ADLS Gen2, consultez Virtualiser une table delta avec PolyBase.
SQL Server 2022 (16.x) prend entièrement en charge deux formats d’URL pour Azure Storage Account v2 (abs) et Azure Data Lake Gen2 (adls).
- Le chemin LOCATION peut utiliser les formats suivants : <container>@<storage_account_name>.. (recommandé) ou <storage_account_name>../<container>. Par exemple :
  - Azure Storage Account v2 : abs://<container>@<storage_account_name>.blob.core.windows.net (recommandé) ou abs://<storage_account_name>.blob.core.windows.net/<container>.
  - Azure Data Lake Gen2 prend en charge : adls://<container>@<storage_account_name>.blob.core.windows.net (recommandé) ou adls://<storage_account_name>.dfs.core.windows.net/<container>.

CONNECTION_OPTIONS = key_value_pair

S’applique aux connexions ODBC génériques ainsi qu’aux connecteurs ODBC intégrés pour SQL Server, Oracle, Teradata, MongoDB et l’API Azure Cosmos DB pour MongoDB.

Les paires clé-valeur possibles sont spécifiques au pilote. Pour plus d’informations sur chaque fournisseur, consultez CREATE EXTERNAL DATA SOURCE (Transact-SQL) CONNECTION_OPTIONS.

À compter de S’applique à : SQL Server 2022 (16.x) mise à jour cumulative 2, des mots clés supplémentaires ont été introduits pour prendre en charge les fichiers TNS Oracle :

Le mot clé TNSNamesFile spécifie le chemin du fichier tnsnames.ora sur le serveur Oracle.
Le mot clé ServerName spécifie l’alias utilisé dans le fichier tnsnames.ora qui sera utilisé pour remplacer le nom d’hôte et le port.

PUSHDOWN = ON | OFF

S’applique à : SQL Server 2019 (15.x) et versions plus récentes Indique si le calcul peut être transmis à la source de données externe. Cette option est activée par défaut.

PUSHDOWN est pris en charge pour la connexion à SQL Server, Oracle, Teradata, MongoDB, l’API Azure Cosmos DB pour MongoDB ou ODBC au niveau de la source de données externe.

L’activation ou la désactivation de la transmission au niveau de la requête s’effectue au moyen d’un indicateur.

CREDENTIAL = credential_name

Spécifie les informations d’identification limitées à la base de données servant à l’authentification auprès de la source de données externe.

Remarques et conseils supplémentaires lors de la création d’informations d’identification :

CREDENTIAL est requis uniquement si les données ont été sécurisées. CREDENTIAL n’est pas requis pour les jeux de données qui autorisent l’accès anonyme.
Lors de l’accès au compte Stockage Azure (V2) ou à Azure Data Lake Storage Gen2, IDENTITY doit être défini sur SHARED ACCESS SIGNATURE.
- Pour obtenir un exemple, consultez Créer une source de données externe pour exécuter des opérations en bloc et récupérer des données du stockage Azure dans SQL Database.

Il existe plusieurs façons de créer une signature d’accès partagé :

Vous pouvez créer un jeton SAS en accédant au portail Azure -><Votre_compte_de_stockage> -> Signature d’accès partagé -> Configurer les autorisations -> Générer une signature SAS et la chaîne de connexion. Pour plus d’informations, consultez Générer une signature d’accès partagé.
Vous pouvez créer et configurer une SAS avec l’Explorateur Stockage Azure.
Vous pouvez créer une SAS par programmation avec PowerShell, Azure CLI, .NET et l’API REST. Pour plus d’informations, consultez Accorder un accès limité aux ressources du Stockage Azure à l’aide des signatures d’accès partagé (SAS).

Le jeton SAS doit être configuré comme suit :

Lorsqu’un jeton SAS est généré, son nom commence par un point d’interrogation (« ? »). Retirez le caractère ? de début lorsqu’il est configuré en tant que SECRET.
Utilisez une période d’expiration valide (toutes les dates sont au format UTC).
Accordez au moins l’autorisation en lecture sur le fichier qui doit être chargé (par exemple srt=o&sp=r). Plusieurs signatures d’accès partagé peuvent être créées pour différents cas d’usage. Les autorisations doivent être accordées comme suit :

Action	Autorisation
Lire les données d’un fichier	Lire
Lire les données de plusieurs fichiers et sous-dossiers	Lire et Liste
Utiliser Create External Table as Select (CETAS)	Lecture, Création, Liste et Écriture

Pour Stockage Blob Azure et Azure Data Lake Gen 2 :
- Services autorisés : Blob doit être sélectionné pour générer le jeton SAS
- Services autorisés : Container et Object doivent être sélectionnés pour générer le jeton SAS

Pour obtenir un exemple d’utilisation de CREDENTIAL avec le stockage d’objets compatible S3 et PolyBase, consultez Configuration de PolyBase pour accéder à des données externes dans le stockage d’objets compatible S3.

Pour créer des informations d’identification délimitées à la base de données, consultez CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

Autorisations

Nécessite l’autorisation CONTROL sur la base de données dans SQL Server.

Verrouillage

Prend un verrou partagé sur l’objet EXTERNAL DATA SOURCE.

Sécurité

Mise à niveau vers SQL Server 2022

À compter de SQL Server 2022 (16.x), les sources de données externes Hadoop ne sont plus prises en charge. Il est nécessaire de recréer manuellement des sources de données externes créées précédemment avec TYPE = HADOOP et toute table externe qui utilise cette source de données externe.

Les utilisateurs devront configurer leurs sources de données externes pour utiliser les nouveaux connecteurs lors de la connexion au Stockage Azure.

Source de données externe	Du	À
Stockage Blob Azure	wasb[s]	abs
ADLS Gen2	abfs[s]	adls

Exemples

Important

Pour plus d’informations sur l’installation et l’activation de PolyBase, consultez Installer PolyBase sur Windows

R. Créer une source de données externe dans SQL Server pour référencer Oracle

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL OracleProxyAccount
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE MyOracleServer
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CREDENTIAL = OracleProxyAccount,
    PUSHDOWN = ON
);

CREATE DATABASE SCOPED CREDENTIAL [OracleProxyCredential]
    WITH IDENTITY = 'oracle_username',
    SECRET = 'oracle_password';

CREATE EXTERNAL DATA SOURCE [OracleSalesSrvr]
WITH (
    LOCATION = 'oracle://145.145.145.145:1521',
    CONNECTION_OPTIONS = 'ImpersonateUser=%CURRENT_USER',
    CREDENTIAL = [OracleProxyCredential]
);

Vous pouvez aussi vous authentifier en utilisant TNS.

À compter de S’applique à : SQL Server 2022 (16.x) Mise à jour cumulative 2, CREATE EXTERNAL DATA SOURCE prend désormais en charge l’utilisation de fichiers TNS lors de la connexion à Oracle. Le paramètre CONNECTION_OPTIONS a été étendu et utilise désormais TNSNamesFile et ServerName comme variables pour parcourir le fichier tnsnames.ora et établir une connexion avec le serveur.

Dans l’exemple suivant, SQL Server recherche à l’exécution l’emplacement du fichier tnsnames.ora spécifié par TNSNamesFile, et l’hôte et le port réseau spécifiés par ServerName.

CREATE EXTERNAL DATA SOURCE [external_data_source_name]
WITH (
    LOCATION = N'oracle://XE',
    CREDENTIAL = [OracleCredentialTest],
    CONNECTION_OPTIONS = N'TNSNamesFile=C:\Temp\tnsnames.ora;ServerName=XE'
);

B. Créer une source de données externe pour référencer une instance nommée SQL Server par le biais d’une connectivité PolyBase

S’applique à : SQL Server 2019 (15.x) et versions ultérieures

Pour créer une source de données externe qui référence une instance nommée de SQL Server, utilisez CONNECTION_OPTIONS pour spécifier le nom de l’instance.

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

Dans l’exemple suivant, WINSQL2019 est le nom d’hôte, et SQL2019 le nom de l’instance. 'Server=%s\SQL2019' est la paire clé-valeur.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019',
    CONNECTION_OPTIONS = 'Server=%s\SQL2019',
    CREDENTIAL = SQLServerCredentials
);

Vous pouvez également utiliser un port pour vous connecter à une instance par défaut de SQL Server.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019:58137',
    CREDENTIAL = SQLServerCredentials
);

C. Créer une source de données externe pour référencer un réplica secondaire lisible de Always On groupe de disponibilité

S’applique à : SQL Server 2019 (15.x) et versions ultérieures

CREATE DATABASE SCOPED CREDENTIAL SQLServerCredentials
    WITH IDENTITY = 'username',
    SECRET = 'password';

Ensuite, créez la nouvelle source de données externe.

Dans l’exemple suivant, WINSQL2019AGL est le nom de l’écouteur de groupe de disponibilité, et dbname le nom de la base de données ciblée par l’instruction CREATE EXTERNAL TABLE.

CREATE EXTERNAL DATA SOURCE SQLServerInstance2
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = SQLServerCredentials
);

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadOnlyIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadOnly; Database=dbname',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE EXTERNAL DATA SOURCE [DataSource_SQLInstanceListener_ReadWriteIntent]
WITH (
    LOCATION = 'sqlserver://WINSQL2019AGL',
    CONNECTION_OPTIONS = 'ApplicationIntent=ReadWrite',
    CREDENTIAL = [SQLServerCredentials]
);
GO

CREATE VIEW vw_sys_servers
AS
SELECT [name]
FROM sys.servers
WHERE server_id = 0;
GO

Ensuite, créez une table externe sur l’instance source :

CREATE EXTERNAL TABLE vw_sys_servers_ro (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadOnlyIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

CREATE EXTERNAL TABLE vw_sys_servers_rw (name SYSNAME NOT NULL)
WITH (
    DATA_SOURCE = [DataSource_SQLInstanceListener_ReadWriteIntent],
    LOCATION = N'dbname.dbo.vw_sys_servers'
);
GO

SELECT [name]
FROM dbo.vw_sys_servers_ro;--should return secondary replica instance

SELECT [name]
FROM dbo.vw_sys_servers_rw;--should return primary replica instance
GO

D. Créer une source de données externe pour interroger un fichier Parquet dans le stockage d’objets compatible S3 avec PolyBase

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

L’exemple de script suivant illustre la création d’une source de données externe s3_ds dans la base de données utilisateur source au sein de SQL Server. La source de données externe fait référence aux informations d’identification de portée étendue à la base de données s3_dc.

CREATE DATABASE SCOPED CREDENTIAL s3_dc
    WITH IDENTITY = 'S3 Access Key', -- for S3-compatible object storage the identity must always be S3 Access Key
    SECRET = '<access_key_id>:<secret_key_id>' -- provided by the S3-compatible object storage
GO

CREATE EXTERNAL DATA SOURCE s3_ds
WITH (
    LOCATION = 's3://<ip_address>:<port>/',
    CREDENTIAL = s3_dc
);
GO

Vérifiez la nouvelle source de données externe avec sys.external_data_sources.

SELECT * FROM sys.external_data_sources;

Ensuite, l’exemple suivant illustre l’utilisation de T-SQL pour interroger un fichier Parquet stocké dans le stockage d’objets compatible S3 au moyen d’une requête OPENROWSET. Pour plus d’informations, consultez Virtualisation d’un fichier Parquet dans un stockage d’objets compatible S3 avec PolyBase.

SELECT *
FROM OPENROWSET (
    BULK '/<bucket>/<parquet_folder>',
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_ds'
) AS [cc];

E. Créer une source de données externe avec ODBC générique vers PostgreSQL

CREATE EXTERNAL DATA SOURCE POSTGRES1
WITH (
    LOCATION = 'odbc://POSTGRES1.domain:5432',
    CONNECTION_OPTIONS = 'Driver={PostgreSQL Unicode(x64)};',
    CREDENTIAL = postgres_credential
);

Stockage Azure

Créer une signature d’accès partagé

Pour le Stockage Blob Azure et Azure Data Lake Gen2, la méthode d’authentification prise en charge est la signature d’accès partagé (SAP). Pour générer facilement un jeton de signature d’accès partagé, procédez comme suit. Pour plus d’informations, consultez Informations d’identification.

Accédez au portail Azure, puis au compte de stockage de votre choix.
Accédez au conteneur souhaité dans le menu Stockage des données.
Sélectionnez Jetons d’accès partagé.
Choisissez l’autorisation appropriée en fonction de l’action souhaitée. Pour référence, utilisez le tableau ci-dessous :

Action	Autorisation
Lire les données d’un fichier	Lire
Lire les données de plusieurs fichiers et sous-dossiers	Lire et Liste
Utiliser Create External Table as Select (CETAS)	Lire, Créer et Écrire

Choisir la date d’expiration du jeton.
Générer un jeton SAS et une URL.
Copier le jeton SAS.

F. Créer une source de données externe pour accéder aux données Stockage Blob Azure à l’aide de l’interface abs://

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

À compter de SQL Server 2022 (16.x), utilisez un nouveau préfixe abs pour le compte Stockage Azure v2. Le préfixe abs prend en charge l’authentification à l’aide de SHARED ACCESS SIGNATURE. Le préfixe abs remplace wasb utilisé dans les versions précédentes. HADOOP n’est plus pris en charge, il n’est plus nécessaire d’utiliser TYPE = BLOB_STORAGE.

La clé de compte de stockage Azure n’est plus nécessaire. Utilisez à la place un jeton de signature d'accès partagé comme indiqué dans l’exemple suivant :

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredentialv2
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE', -- to use SAS the identity must be fixed as-is
    SECRET = '<Blob_SAS_Token>';
GO

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredentialv2,
);

Pour obtenir un exemple plus détaillé de la procédure pour accéder aux fichiers CSV stockés dans Stockage Blob Azure, consultez Virtualiser un fichier CSV avec PolyBase.

G. Créer une source de données externe pour accéder aux données dans Azure Data Lake Gen2

S’applique à : SQL Server 2022 (16.x) et versions ultérieures

À compter de SQL Server 2022 (16.x), utilisez un nouveau préfixe adls pour Azure Data Lake Gen2, qui remplace le préfixe abfs utilisé dans les versions précédentes. Le préfixe adls prend également en charge le jeton SAP comme méthode d’authentification, comme le montre l’exemple suivant :

--Create a database scoped credential using SAS Token
CREATE DATABASE SCOPED CREDENTIAL datalakegen2
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '<DataLakeGen2_SAS_Token>';
GO

CREATE EXTERNAL DATA SOURCE data_lake_gen2_dfs
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = datalakegen2
);

Pour obtenir un exemple plus détaillé de la procédure pour accéder aux fichiers delta stockés sur Azure Data Lake Gen2, consultez Virtualiser la table delta avec PolyBase.

Exemples : opérations en bloc

Important

H. Créer une source de données externe pour les opérations en bloc de récupération de données dans le stockage Azure

S’applique à :SQL Server 2022 (16.x) et versions plus récentes.

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    -- Remove ? from the beginning of the SAS token
    SECRET = '<azure_shared_access_signature>';

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'abs://<container>@<storage_account_name>.blob.core.windows.net/',
    CREDENTIAL = AccessAzureInvoices,
);

Étapes suivantes

* SQL Database *

Présentation : Azure SQL Database

S’applique à : Azure SQL Database

Crée une source de données externe pour des requêtes élastiques. Des sources de données externes sont utilisées pour établir la connectivité et prendre en charge ces principaux cas d’utilisation :

Les opérations de chargement en bloc à l’aide de BULK INSERT ou OPENROWSET
Interrogation d’instances distantes SQL Database ou Azure Synapse à l’aide de SQL Database avec des requêtes élastiques
Interrogation d’une base SQL Database partitionnée à l’aide de requêtes élastiques

Syntaxe

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = { BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER } ]
    [ [ , ] DATABASE_NAME = '<database_name>' ]
    [ [ , ] SHARD_MAP_NAME = '<shard_map_manager>' ] )
[ ; ]

Arguments

data_source_name

Spécifie le nom défini par l’utilisateur de la source de données. Ce nom doit être unique dans la base de données de SQL Database.

LOCATION = `'<prefix>://<path[:port]>'`

Fournit le protocole de connectivité et le chemin d’accès à la source de données externe.

Source de données externe	Préfixe de l’emplacement du connecteur	Chemin d’emplacement	Disponibilité
opérations en bloc	`https`	`<storage_account>.blob.core.windows.net/<container>`
Requête élastique (partition)	Non requis	`<shard_map_server_name>.database.windows.net`
Requête élastique (distant)	Non requis	`<remote_server_name>.database.windows.net`
EdgeHub	`edgehub`	`edgehub://`	Disponible dans Azure SQL Edge uniquement. EdgeHub est toujours local à l’instance de Azure SQL Edge. Par conséquent, il n’est pas nécessaire de spécifier un chemin d’accès ou une valeur de port.
Kafka	`kafka`	`kafka://<kafka_bootstrap_server_name_ip>:<port_number>`	Disponible dans Azure SQL Edge uniquement.

Chemin d’emplacement :

<shard_map_server_name> = le nom du serveur logique dans Azure qui héberge le Gestionnaire de la carte de partitions. L’argument DATABASE_NAME fournit la base de données utilisée pour héberger la carte de partitions et l’argument SHARD_MAP_NAME est utilisé pour la carte de partitions proprement dite.
<remote_server_name> = le nom du serveur logique cible pour la requête élastique. Le nom de la base de données est spécifié avec l’argument DATABASE_NAME.

Remarques et conseils supplémentaires lors de la définition de l’emplacement :

Le moteur de base de données ne vérifie pas l’existence de la source de données externe lorsque l’objet est créé. Pour valider, créez une table externe à l’aide d’une source de données externe.

CREDENTIAL = credential_name

Spécifie les informations d’identification limitées à la base de données servant à l’authentification auprès de la source de données externe.

Remarques et conseils supplémentaires lors de la création d’informations d’identification :

Pour charger des données à partir de Stockage Azure dans Azure SQL Database, utilisez une signature d’accès partagé (jeton SAS).
CREDENTIAL est requis uniquement si les données ont été sécurisées. CREDENTIAL n’est pas requis pour les jeux de données qui autorisent l’accès anonyme.
Quand TYPE = BLOB_STORAGE, les informations d’identification doivent être créées avec l’identité SHARED ACCESS SIGNATURE.
Quand vous vous connectez à Stockage Azure par le biais du connecteur WASB[s], l’authentification doit être effectuée avec une clé de compte de stockage, et non avec une signature d’accès partagé (SAS).
Quand TYPE = HADOOP, les informations d’identification doivent être créées à l’aide de la clé de compte de stockage en tant que SECRET.
TYPE = BLOB_STORAGE est autorisé uniquement pour les opérations en bloc. Vous ne pouvez pas créer de tables externes pour une source de données externe avec TYPE = BLOB_STORAGE.

Il existe plusieurs façons de créer une signature d’accès partagé :

Vous pouvez créer un jeton SAS en accédant au portail Azure -><Votre_compte_de_stockage> -> Signature d’accès partagé -> Configurer les autorisations -> Générer une signature SAS et la chaîne de connexion. Pour plus d’informations, consultez Générer une signature d’accès partagé.
Vous pouvez créer et configurer une SAS avec l’Explorateur Stockage Azure.
Vous pouvez créer une SAS par programmation avec PowerShell, Azure CLI, .NET et l’API REST. Pour plus d’informations, consultez Accorder un accès limité aux ressources du Stockage Azure à l’aide des signatures d’accès partagé (SAS).

Le jeton SAS doit être configuré comme suit :

Lorsqu’un jeton SAS est généré, son nom commence par un point d’interrogation (« ? »). Retirez le caractère ? de début lorsqu’il est configuré en tant que SECRET.
Utilisez une période d’expiration valide (toutes les dates sont au format UTC).
Accordez au moins l’autorisation en lecture sur le fichier qui doit être chargé (par exemple srt=o&sp=r). Plusieurs signatures d’accès partagé peuvent être créées pour différents cas d’usage. Les autorisations doivent être accordées comme suit :

Action	Autorisation
Lire les données d’un fichier	Lire
Lire les données de plusieurs fichiers et sous-dossiers	Lire et Liste
Utiliser Create External Table as Select (CETAS)	Lire, Créer et Écrire

Pour créer des informations d’identification délimitées à la base de données, consultez CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

TYPE = [ BLOB_STORAGE | RDBMS | SHARD_MAP_MANAGER]

Spécifie le type de source de données externe en cours de configuration. Ce paramètre n’est pas toujours requis.

Utilisez RDBMS pour les requêtes sur plusieurs bases de données utilisant les requêtes élastiques à partir d’une base de données SQL.
Utilisez SHARD_MAP_MANAGER lors de la création d’une source de données externe lorsque vous vous connectez à une base de données SQL partitionnée.
Utilisez BLOB_STORAGE quand vous exécutez des opérations en bloc avec BULK INSERT ou OPENROWSET.

Important

Ne paramétrez pas TYPE si vous utilisez toute autre source de données externe.

DATABASE_NAME = database_name

Configurez cet argument lorsque TYPE a la valeur RDBMS ou SHARD_MAP_MANAGER.

TYPE	Valeur de DATABASE_NAME
SGBDR	Le nom de la base de données distante sur le serveur fourni à l’aide de `LOCATION`
SHARD_MAP_MANAGER	Nom de la base de données faisant office de Gestionnaire de la carte de partitions

Pour un exemple montrant comment créer une source de données externe où TYPE = RDBMS, consultez Créer une source de données externe SGBDR

SHARD_MAP_NAME = shard_map_name

Utilisé lorsque l’argument TYPE a la valeur SHARD_MAP_MANAGER uniquement pour définir le nom de la carte de partitions.

Pour un exemple montrant comment créer une source de données externe où TYPE = SHARD_MAP_MANAGER, consultez Créer une source de données externe de Gestionnaire de la carte des partitions

Autorisations

Nécessite l’autorisation CONTROL sur la base de données dans Azure SQL Database.

Verrouillage

Prend un verrou partagé sur l’objet EXTERNAL DATA SOURCE.

Exemples

R. Créer une source de données externe de Gestionnaire de cartes de partitions

Pour créer une source de données externe afin de référencer SHARD_MAP_MANAGER, spécifiez le nom du serveur SQL Database qui héberge le Gestionnaire de cartes de partitions dans SQL Database ou une base de données SQL Server sur une machine virtuelle.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
    WITH IDENTITY = '<username>',
    SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
    TYPE = SHARD_MAP_MANAGER,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'ElasticScaleStarterKit_ShardMapManagerDb',
    CREDENTIAL = ElasticDBQueryCred,
    SHARD_MAP_NAME = 'CustomerIDShardMap'
);

Pour un tutoriel détaillé, consultez Bien démarrer avec les requêtes élastiques pour le partitionnement (partitionnement horizontal).

B. Créer une source de données externe de SGBDR

Pour créer une source de données externe pour faire référence à un SGBDR, spécifie le nom du serveur SQL Database de la base de données distante dans SQL Database.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL SQL_Credential
    WITH IDENTITY = '<username>',
    SECRET = '<password>';

CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc
WITH (
    TYPE = RDBMS,
    LOCATION = '<server_name>.database.windows.net',
    DATABASE_NAME = 'Customers',
    CREDENTIAL = SQL_Credential
);

Pour un tutoriel détaillé sur le SGBDR, consultez Prise en main des requêtes de bases de données croisées (partitionnement vertical).

Exemples : Opérations en bloc

Important

C. Créer une source de données externe pour les opérations en bloc de récupération de données dans le stockage Azure

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    -- Remove ? from the beginning of the SAS token
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
WITH (
    LOCATION = 'https://newinvoices.blob.core.windows.net/week3',
    CREDENTIAL = AccessAzureInvoices,
    TYPE = BLOB_STORAGE
);

Pour voir une utilisation de cet exemple, consultez BULK INSERT.

Exemples : Azure SQL Edge

Important

Pour plus d’informations sur la configuration de données externes pour Azure SQL Edge, consultez Streaming des données dans Azure SQL Edge.

R. Créer une source de données externe pour faire référence à Kafka

S’applique à : Azure SQL Edge uniquement

Dans cet exemple, la source de données externe est un serveur Kafka avec l’adresse IP xxx.xxx.xxx.xxx et à l’écoute sur le port 1900. La source de données externe Kafka est uniquement destinée au streaming de données et ne prend pas en charge le pushdown de prédicats.

-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyKafkaServer
    WITH (LOCATION = 'kafka://xxx.xxx.xxx.xxx:1900');

B. Créer une source de données externe pour faire référence à EdgeHub

S’applique à : Azure SQL Edge uniquement

Dans cet exemple, la source de données externe est un EdgeHub s’exécutant sur le même périphérique que Azure SQL Edge. La source de données externe EdgeHub est uniquement destinée au streaming de données et ne prend pas en charge pushdown de prédicats.

-- Create an External Data Source for Kafka
CREATE EXTERNAL DATA SOURCE MyEdgeHub
    WITH (LOCATION = 'edgehub://');

Étapes suivantes

* Azure Synapse
Analytics *

Présentation : Azure Synapse Analytics

S’applique à : Azure Synapse Analytics

Crée une source de données externe pour la virtualisation des données. Les sources de données externes sont utilisées pour établir la connectivité et prendre en charge le cas d’usage principal de la virtualisation et du chargement des données à partir de sources externes. Pour plus d’informations, consultez Utilisation de tables externes avec Synapse SQL.

Important

Pour créer une source de données externes afin d’interroger une ressource Azure Synapse Analytics en utilisant Azure SQL Database avec des requêtes élastiques, consultez SQL Database.

Syntaxe

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

Pool SQL dédié
Pool SQL serverless

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
( [ LOCATION = '<prefix>://<path>[:<port>]' ]
  [ [ , ] CREDENTIAL = <credential_name> ]
  [ [ , ] TYPE = HADOOP ]
)
[ ; ]

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(   LOCATION = '<prefix>://<path>'
)
[;]

Arguments

data_source_name

Spécifie le nom défini par l’utilisateur de la source de données. Le nom doit être unique au sein de la base de données Azure SQL Database dans Azure Synapse Analytics.

LOCATION = `'<prefix>://<path>'`

Fournit le protocole de connectivité et le chemin d’accès à la source de données externe.

Source de données externe	Préfixe de l’emplacement du connecteur	Chemin d’emplacement
Data Lake Storage* Gen1	`adl`	`<storage_account>.azuredatalake.net`
Data Lake Storage Gen2	`abfs[s]`	`<container>@<storage_account>.dfs.core.windows.net`
Stockage Blob Azure	`wasbs`	`<container>@<storage_account>.blob.core.windows.net`
Stockage Blob Azure	`https`	`<storage_account>.blob.core.windows.net/<container>/subfolders`
Data Lake Storage Gen 1	`http[s]`	`<storage_account>.azuredatalakestore.net/webhdfs/v1`
Data Lake Storage Gen2	`http[s]`	`<storage_account>.dfs.core.windows.net/<container>/subfolders`
Data Lake Storage Gen2	`wasb[s]`	`<container>@<storage_account>.blob.core.windows.net`

* Microsoft Azure Data Lake Storage Gen1 offre un support limité. Gen2 est recommandé pour tous les nouveaux développements.

Source de données externe	Préfixe de l’emplacement du connecteur	Pools SQL dédiés : PolyBase	Pools SQL dédiés : native*	Pools SQL serverless
Data Lake Storage** Gen1	`adl`	Non	Non	Oui
Data Lake Storage Gen2	`abfs[s]`	Oui	Oui	Oui
Stockage Blob Azure	`wasbs`	Oui	Oui***	Oui
Azure Blob Storage	`https`	Non	Oui	Oui
Data Lake Storage Gen 1	`http[s]`	Non	Non	Oui
Data Lake Storage Gen2	`http[s]`	Oui	Oui	Oui
Data Lake Storage Gen2	`wasb[s]`	Oui	Oui	Oui

* Les pools SQL serverless et dédiés dans Azure Synapse Analytics utilisent des codebases différents pour la virtualisation des données. Les pools SQL serverless prennent en charge une technologie native de virtualisation de données. Les pools SQL dédiés, eux, sont compatibles avec la virtualisation des données native et PolyBase. La virtualisation des données PolyBase est utilisée lorsque la source de données externe (EXTERNAL DATA SOURCE) est créée avec TYPE=HADOOP.

** Microsoft Azure Data Lake Storage Gen1 offre un support limité. Gen2 est recommandé pour tous les nouveaux développements.

*** Le connecteur wasbs, plus sécurisé que wasb, est recommandé. Seule la virtualisation des données native dans les pools SQL dédiés (où TYPE n’est pas égal à HADOOP) prend en charge wasb.

Chemin d’emplacement :

<container> = le conteneur du compte de stockage contenant les données. Les conteneurs racines sont en lecture seule, donc les données ne peuvent pas être réécrites sur le conteneur.
<storage_account> = le nom du compte de stockage de la ressource Azure.

Remarques et conseils supplémentaires lors de la définition de l’emplacement :

L’option par défaut consiste à utiliser enable secure SSL connections lors du provisionnement d’Azure Data Lake Storage Gen2. Si cette option est activée, vous devez utiliser abfss lorsqu’une connexion TLS/SSL sécurisée est sélectionnée. Notez que abfss fonctionne également pour les connexions TLS non sécurisées. Pour plus d’informations, consultez Pilote ABFS (Azure Blob Filesystem).
Azure Synapse ne vérifie pas l’existence de la source de données externe lorsque l’objet est créé. Pour valider, créez une table externe à l’aide d’une source de données externe.
Utilisez la même source de données externe pour toutes les tables lors de l’interrogation de Hadoop afin de garantir la cohérence des paramètres sémantiques de requête.
Le préfixe https: vous permet d’utiliser un sous-dossier dans le chemin. https n’est pas disponible pour tous les mécanismes d’accès aux données.
wasbs est recommandé, car il permet d’envoyer les données au moyen d’une connexion TLS sécurisée.
Les espaces de noms hiérarchiques ne sont pas pris en charge avec les comptes de stockage Azure v2 en cas d’accès aux données à l’aide de l’interface héritée wasb://, tandis que wasbs:// est compatible avec les espaces de noms hiérarchiques.

CREDENTIAL = credential_name

facultatif. Spécifie des informations d’identification délimitées à la base de données pour l’authentification auprès de la source de données externe. Une source de données externe sans informations d’identification peut accéder au compte de stockage public ou utiliser l’identité Microsoft Entra de l’appelant pour accéder aux fichiers sur le stockage Azure.

Remarques et conseils supplémentaires lors de la création d’informations d’identification :

Pour charger des données du Stockage Azure ou d’Azure Data Lake Storage (ADLS) Gen2 dans Azure Synapse Analytics, utilisez une clé de stockage Azure.
CREDENTIAL est requis uniquement si les données ont été sécurisées. CREDENTIAL n’est pas requis pour les jeux de données qui autorisent l’accès anonyme.

Pour créer des informations d’identification délimitées à la base de données, consultez CREATE DATABASE SCOPED CREDENTIAL (Transact-SQL).

Dans le pool SQL serverless, les informations d’identification délimitées à la base de données peuvent spécifier l’identité managée de l’espace de travail, le nom du principal de service ou le jeton de signature d’accès partagé (SAP). L’accès via une identité utilisateur, également appelée passthrough Microsoft Entra, est également possible dans les informations d’identification délimitées par la base de données, comme l’accès anonyme au stockage disponible publiquement. Pour plus d’informations, consultez Types d’autorisation de stockage pris en charge.
Dans un pool SQL dédié, les informations d’identification délimitées à la base de données peuvent spécifier le jeton de signature d’accès partagé (SAP), la clé d’accès au stockage, le principal du service, l’identité managée de l’espace de travail ou le passthrough Microsoft Entra.

TYPE = HADOOP

Facultatif, mais recommandé.

TYPE ne peut être spécifié qu’avec des pools SQL dédiés. HADOOP représente alors la seule valeur autorisée. Les sources de données externes avec TYPE=HADOOP sont disponibles uniquement dans les pools SQL dédiés.

Utilisez HADOOP pour les implémentations héritées. Sinon, il est recommandé de passer par l’accès aux données natif, plus récent, auquel cas vous ne devez pas spécifier l’argument TYPE.

Pour obtenir un exemple d’utilisation de TYPE = HADOOP pour charger des données depuis Stockage Azure, consultez Créer une source de données externe pour référencer Azure Data Lake Store Gen 1 ou 2 à l’aide d’un principal de service.

Les pools SQL serverless et dédiés dans Azure Synapse Analytics utilisent des codebases différents pour la virtualisation des données. Les pools SQL serverless prennent en charge une technologie native de virtualisation de données. Les pools SQL dédiés, eux, sont compatibles avec la virtualisation des données native et PolyBase. La virtualisation des données PolyBase est utilisée lorsque la source de données externe (EXTERNAL DATA SOURCE) est créée avec TYPE=HADOOP.

Autorisations

Requiert l'autorisation CONTROL sur la base de données.

Verrouillage

Prend un verrou partagé sur l’objet EXTERNAL DATA SOURCE.

Sécurité

La plupart des sources de données externes prennent en charge l’authentification du proxy, en utilisant des informations d’identification au niveau de la base de données pour créer le compte proxy.

Les clés de signature d’accès partagé (SAS) sont prises en charge pour l’authentification auprès des comptes de stockage Azure Data Lake Store Gen 2. Les clients qui souhaitent s’authentifier à l’aide d’une signature d’accès partagé doivent créer des informations d’identification limitées à la base de données où IDENTITY = "Shared Access Signature" et entrer un jeton SAS comme secret.

Si vous créez des informations d’identification limitées à la base de données où IDENTITY = "Shared Access Signature" et que vous utilisez une valeur de clé de stockage comme secret, vous obtenez le message d’erreur suivant :

'HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: "Server failed to authenticate the request. Please refer to the information in the www-authenticate header.", 401, HEAD, [Storage path URL]'

Exemples

R. Créer une source de données externe pour accéder aux données dans le stockage Azure à l’aide de l’interface wasb://

Dans cet exemple, la source de données externe est un compte de stockage Azure v2 nommé logs. Le conteneur de stockage est appelé daily. La source de données externe de stockage Azure sert uniquement au transfert des données. Elle ne prend pas en charge le pushdown de prédicats. Les espaces de noms hiérarchiques ne sont pas pris en charge lors de l’accès aux données via l’interface wasb://. Notez que quand vous vous connectez au Stockage Azure via le connecteur WASB[s], l’authentification doit être effectuée avec une clé de compte de stockage, et non avec une signature d’accès partagé (SAS).

L’exemple suivant utilise la méthode d’accès Java HADOOP héritée. Il montre comment créer les informations d’identification au niveau de la base de données qui serviront à l’authentification auprès du Stockage Azure. Spécifiez la clé du compte de stockage Azure dans le secret des informations d’identification de la base de données. Vous pouvez spécifier toute chaîne de l’identité des informations d’identification limitées à la base de données, étant donné qu’elle n’est pas utilisée lors de l’authentification auprès du stockage Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
    SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

B. Créer une source de données externe pour référencer Azure Data Lake Store Gen 1 ou 2 à l’aide d’un principal de service

La connectivité Azure Data Lake Store peut être basée sur votre URI ADLS et le principal de service de votre application Microsoft Entra. Vous trouverez la documentation relative à la création de cette application à l’aide de l’authentification Data Lake Store à l’aide de l’ID Microsoft Entra.

-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- These values come from your Microsoft Entra application used to authenticate to ADLS
CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
    -- IDENTITY = '<clientID>@<OAuth2.0TokenEndPoint>' ,
    IDENTITY = '536540b4-4239-45fe-b9a3-629f97591c0c@https://login.microsoftonline.com/42f988bf-85f1-41af-91ab-2d2cd011da47/oauth2/token',
    -- SECRET = '<KEY>'
    SECRET = 'BjdIlmtKp4Fpyh9hIvr8HJlUida/seM5kQ3EpLAmeDI=';

-- For Gen 1 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen 1 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    LOCATION = 'adl://newyorktaxidataset.azuredatalakestore.net',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);

-- For Gen2 - Create an external data source
-- TYPE: HADOOP - PolyBase uses Hadoop APIs to access data in Azure Data Lake Storage.
-- LOCATION: Provide Data Lake Storage Gen2 account name and URI
-- CREDENTIAL: Provide the credential created in the previous step
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (
    -- Note the abfss endpoint when your account has secure transfer enabled
    LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);

C. Créer une source de données externe pour référencer Azure Data Lake Store Gen2 à l’aide d’une clé de compte de stockage

-- If you do not have a Master Key on your DW you will need to create one.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

CREATE DATABASE SCOPED CREDENTIAL ADLS_credential
WITH
    -- IDENTITY = '<storage_account_name>' ,
    IDENTITY = 'newyorktaxidata',
    -- SECRET = '<storage_account_key>'
    SECRET = 'yz5N4+bxSb89McdiysJAzo+9hgEHcJRJuXbF/uC3mhbezES/oe00vXnZEl14U0lN3vxrFKsphKov16C0w6aiTQ==';

-- Note this example uses a Gen2 secured endpoint (abfss)
CREATE EXTERNAL DATA SOURCE < data_source_name >
WITH (
    LOCATION = 'abfss://2013@newyorktaxidataset.dfs.core.windows.net',
    CREDENTIAL = ADLS_credential,
    TYPE = HADOOP
);

D. Créer une source de données externe vers Azure Data Lake Store Gen2 avec abfs://

Il n’est pas nécessaire de spécifier SECRET lors de la connexion au compte Azure Data Lake Store Gen2 avec un mécanisme d’identité managée.

-- If you do not have a Master Key on your DW you will need to create one
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

--Create database scoped credential with **IDENTITY = 'Managed Service Identity'**
CREATE DATABASE SCOPED CREDENTIAL msi_cred
WITH IDENTITY = 'Managed Service Identity';

--Create external data source with abfss:// scheme for connecting to your Azure Data Lake Store Gen2 account
CREATE EXTERNAL DATA SOURCE ext_datasource_with_abfss
WITH (
    TYPE = HADOOP,
    LOCATION = 'abfss://myfile@mystorageaccount.dfs.core.windows.net',
    CREDENTIAL = msi_cred
);

Étapes suivantes

* Analytics
Platform System (PDW) *

Présentation : Système de la plateforme d'analyse

S’applique à : Analytics Platform System (PDW)

Crée une source de données externe pour des requêtes PolyBase. Des sources de données externes sont utilisées pour établir la connectivité et prendre en charge le cas d’utilisation suivant : Virtualisation des données et chargement des données à l’aide de PolyBase.

Syntaxe

Pour plus d’informations sur les conventions de la syntaxe, consultez Conventions de la syntaxe Transact-SQL.

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
  ( [ LOCATION = '<prefix>://<path>[:<port>]' ]
    [ [ , ] CREDENTIAL = <credential_name> ]
    [ [ , ] TYPE = HADOOP ]
    [ [ , ] RESOURCE_MANAGER_LOCATION = '<resource_manager>[:<port>]' )
[ ; ]

Arguments

data_source_name

Spécifie le nom défini par l’utilisateur de la source de données. Le nom doit être unique au sein du serveur dans Analytics Platform System (PDW).

LOCATION = `'<prefix>://<path[:port]>'`

Fournit le protocole de connectivité et le chemin d’accès à la source de données externe.

Source de données externe	Préfixe de l’emplacement du connecteur	Chemin d’emplacement
Cloudera CDH ou Hortonworks HDP	`hdfs`	`<Namenode>[:port]`
Compte Stockage Azure	`wasb[s]`	`<container>@<storage_account>.blob.core.windows.net`

Chemin d’emplacement :

<Namenode> = nom de l’ordinateur, URI du service de noms ou adresse IP de Namenode dans le cluster Hadoop. PolyBase doit résoudre tous les noms DNS utilisés par le cluster Hadoop.
port = Le port d’écoute de la source de données externe. Dans Hadoop, le port se trouve à l’aide du paramètre de configuration fs.defaultFS. La valeur par défaut est 8020.
<container> = le conteneur du compte de stockage contenant les données. Les conteneurs racines sont en lecture seule, donc les données ne peuvent pas être réécrites sur le conteneur.
<storage_account> = le nom du compte de stockage de la ressource Azure.

Remarques et conseils supplémentaires lors de la définition de l’emplacement :

Le moteur PDW ne vérifie pas l’existence de la source de données externe lorsque l’objet est créé. Pour valider, créez une table externe à l’aide d’une source de données externe.
Utilisez la même source de données externe pour toutes les tables lors de l’interrogation de Hadoop afin de garantir la cohérence des paramètres sémantiques de requête.
wasbs est recommandé, car il permet d’envoyer les données au moyen d’une connexion TLS sécurisée.
Les espaces de noms hiérarchiques ne sont pas pris en charge lorsqu’ils sont utilisés avec des comptes de stockage Azure sur wasb://.
Pour garantir la réussite des requêtes PolyBase lors du basculement du Namenode Hadoop, envisagez d’utiliser une adresse IP virtuelle pour le Namenodedu cluster Hadoop. Dans le cas contraire, exécutez une commande ALTER EXTERNAL DATA SOURCE pour pointer vers le nouvel emplacement.

CREDENTIAL = credential_name

Spécifie les informations d’identification limitées à la base de données servant à l’authentification auprès de la source de données externe.

Remarques et conseils supplémentaires lors de la création d’informations d’identification :

Pour charger les données à partir du stockage Azure dans Azure Synapse ou PDW, utilisez une clé de stockage Azure.
CREDENTIAL est requis uniquement si les données ont été sécurisées. CREDENTIAL n’est pas requis pour les jeux de données qui autorisent l’accès anonyme.

TYPE = [ HADOOP ]

Spécifie le type de source de données externe en cours de configuration. Ce paramètre n’est pas toujours requis.

Utilisez HADOOP quand la source de données externe est Cloudera CDH, Hortonworks HDP ou Stockage Azure.

Pour obtenir un exemple d’utilisation de TYPE = HADOOP pour charger des données depuis Stockage Azure, consultez Créer une source de données externe pour référencer Hadoop.

RESOURCE_MANAGER_LOCATION = 'ResourceManager_URI[:port]'

Dans SQL Server 2019 (15.x), ne spécifiez pas RESOURCE_MANAGER_LOCATION, sauf si vous vous connectez à Cloudera CDH, à Hortonworks HDP ou à un compte Stockage Azure.

Lorsque RESOURCE_MANAGER_LOCATION est défini, l’optimiseur de requête prend une décision en fonction du coût pour améliorer les performances. Une tâche MapReduce peut être utilisée pour transmettre le calcul à Hadoop. En spécifiant RESOURCE_MANAGER_LOCATION, il est possible de considérablement réduire le volume des données transférées entre Hadoop et SQL, ce qui peut donc améliorer les performances des requêtes.

Pour que PolyBase fonctionne correctement avec une source de données externe Hadoop, les ports des composants de cluster Hadoop suivants doivent être ouverts :

Ports HDFS
- Namenode
- Datanode
Resource Manager
- Envoi du travail
- Historique des travaux

Si le port n’est pas spécifié, la valeur par défaut est déterminée d’après le paramètre actuel de la configuration de la « connexion à hadoop ».

Connexion Hadoop	Port du Gestionnaire de ressources par défaut
1	50300
2	50300
3	8021
4	8032
5	8050
6	8032
7	8050

Composant de cluster Hadoop	Port par défaut
NameNode	8020
DataNode (transfert de données, port IPC sans privilège)	50010
DataNode (transfert de données, port IPC avec privilège)	1019
Soumission de travaux Resource Manager (Hortonworks 1.3)	50300
Soumission de travaux Resource Manager (Cloudera 4.3)	8021
Soumission de travaux Resource Manager (Hortonworks 2.0 sur Windows, Cloudera 5.x sur Linux)	8032
Soumission de travaux Resource Manager (Hortonworks 2.x, 3.0 sur Linux, Hortonworks 2.1-3 sur Windows)	8050
Historique des travaux Resource Manager	10020

Autorisations

Nécessite l’autorisation CONTROL sur la base de données dans Analytics Platform System (PDW).

Notes

Dans les versions précédentes de PDW, la création d’une source de données externe nécessitait des autorisations ALTER ANY EXTERNAL DATA SOURCE.

Verrouillage

Prend un verrou partagé sur l’objet EXTERNAL DATA SOURCE.

Sécurité

Exemples

R. Créer une source de données externe pour faire référence à Hadoop

Pour créer une source de données externe afin de référencer Hortonworks HDP ou Cloudera CDH, spécifiez le nom de l’ordinateur ou l’adresse IP du port et du Namenode Hadoop.

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    TYPE = HADOOP
);

B. Créer une source de données externe pour faire référence à Hadoop avec transmission activée

CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8020',
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

C. Créer une source de données externe pour faire référence à Hadoop sécurisé par Kerberos

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Kerberos user name and password.
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
    WITH IDENTITY = '<hadoop_user_name>',
    SECRET = '<hadoop_password>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyHadoopCluster
WITH (
    LOCATION = 'hdfs://10.10.10.10:8050',
    CREDENTIAL = HadoopUser1,
    TYPE = HADOOP,
    RESOURCE_MANAGER_LOCATION = '10.10.10.10:8050'
);

D. Créer une source de données externe pour accéder aux données dans le stockage Azure à l’aide de l’interface wasb://

Cet exemple montre comment créer des informations d’identification limitées à la base de données qui serviront à l’authentification auprès du stockage Azure. Spécifiez la clé du compte de stockage Azure dans le secret des informations d’identification de la base de données. Vous pouvez spécifier toute chaîne de l’identité des informations d’identification limitées à la base de données, étant donné qu’elle n’est pas utilisée lors de l’authentification auprès du stockage Azure.

-- Create a database master key if one does not already exist, using your own password.
-- This key is used to encrypt the credential secret in next step.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

-- Create a database scoped credential with Azure storage account key as the secret.
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
    WITH IDENTITY = '<my_account>',
        SECRET = '<azure_storage_account_key>';

-- Create an external data source with CREDENTIAL option.
CREATE EXTERNAL DATA SOURCE MyAzureStorage
WITH (
    LOCATION = 'wasbs://daily@logs.blob.core.windows.net/',
    CREDENTIAL = AzureStorageCredential,
    TYPE = HADOOP
);

Étapes suivantes

* SQL Managed Instance *