CREATE EXTERNAL TABLE (Transact-SQL)

Crée une table externe.

Cet article fournit la syntaxe, les arguments, les notes, les autorisations et des exemples associés au produit SQL que vous choisissez.

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

Sélectionner un produit

Sur la ligne suivante, sélectionnez le nom du produit qui vous intéresse afin d’afficher uniquement les informations qui le concernent.

* SQL Server *  

Base de données SQL

Azure SQL Managed Instance

Azure Synapse
Analytics

Analytics Platform
System (PDW)

 

Présentation : SQL Server

Cette commande crée une table externe pour PolyBase, afin d’accéder à des données stockées dans un cluster Hadoop ou dans une table externe PolyBase Stockage Blob Azure qui fait référence à des données stockées dans un cluster Hadoop ou Stockage Blob Azure.

S’applique à : SQL Server 2016 (ou versions ultérieures)

Utilisez une table externe avec une source de données externe pour les 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
• Opérations de chargement en masse à l’aide de SQL Server ou SQL Database avec BULK INSERT ou OPENROWSET

Voir aussi CREATE EXTERNAL DATA SOURCE et DROP EXTERNAL TABLE.

Syntaxe

-- Create a new external table
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        [ FILE_FORMAT = external_file_format_name ]
        [ , <reject_options> [ ,...n ] ]
    )
[;]

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage
    | REJECT_VALUE = reject_value
    | REJECT_SAMPLE_VALUE = reject_sample_value,
    | REJECTED_ROW_LOCATION = '/REJECT_Directory'
}

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]

Arguments

{ database_name.schema_name.table_name | schema_name.table_name | table_name }

Nom (composé d’une à trois parties) de la table à créer. Pour une table externe, SQL stocke uniquement les métadonnées de la table avec des statistiques de base sur le fichier ou le dossier qui est référencé dans Hadoop ou Stockage Blob Azure. Aucune donnée n’est déplacée ni stockée dans SQL Server.

Important

Pour des performances optimales, si le pilote de source de données externe prend en charge un nom en trois parties, il est fortement recommandé de fournir ce nom en trois parties.

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE prend en charge la possibilité de configurer le nom de colonne, le type de données, la possibilité d’une valeur Null et le classement. Vous ne pouvez pas utiliser DEFAULT CONSTRAINT sur des tables externes.

Les définitions de colonne, notamment les types de données et le nombre de colonnes, doivent correspondre aux données des fichiers externes. En cas de non-correspondance, les lignes du fichier sont rejetées lors de l’interrogation des données réelles.

LOCATION = 'folder_or_filepath'

Spécifie le dossier ou le chemin et le nom du fichier où se trouvent les données dans Hadoop ou Stockage Blob Azure. En outre, le stockage d’objets compatible S3 est pris en charge à partir de SQL Server 2022 (16.x). L’emplacement commence au dossier racine. Le dossier racine est l’emplacement de données qui est spécifié dans la source de données externe.

Dans SQL Server, l’instruction CREATE EXTERNAL TABLE crée le chemin et le dossier s’ils n’existent pas déjà. Vous pouvez ensuite utiliser INSERT INTO pour exporter les données d’une table SQL Server locale dans la source de données externe. Pour plus d’informations, consultez Requêtes PolyBase.

Si vous spécifiez LOCATION comme étant un dossier, une requête PolyBase qui sélectionne des données dans la table externe récupère les fichiers dans le dossier et dans tous ses sous-dossiers. Tout comme Hadoop, PolyBase ne retourne pas les dossiers masqués. Il ne retourne pas non plus les fichiers dont le nom commence par un trait de soulignement (_) ou un point (.).

Dans l’exemple d’image suivant, si LOCATION='/webdata/', une requête PolyBase retourne des lignes de mydata.txt et mydata2.txt. Elle ne retourne pas mydata3.txt, car ce fichier se trouve dans un sous-dossier masqué. Elle ne retourne pas non plus _hidden.txt car il s’agit d’un fichier masqué.

Pour modifier la valeur par défaut et uniquement lire les données du dossier racine, définissez l’attribut <polybase.recursive.traversal>sur 'false' dans le fichier de configuration core-site.xml. Ce fichier se trouve sous <SqlBinRoot>\PolyBase\Hadoop\Conf sous la racine bin de SQL Server. Par exemple : C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn.

DATA_SOURCE = external_data_source_name

Spécifie le nom de la source de données externe contenant l’emplacement des données externes. Cet emplacement est un système de fichiers Hadoop (HDFS), un conteneur Stockage Blob Azure ou Azure Data Lake Store. Pour créer une source de données externe, utilisez CREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Spécifie le nom de l’objet de format de fichier externe qui stocke le type de fichier et la méthode de compression pour les données externes. Pour créer un format de fichier externe, utilisez CREATE EXTERNAL FILE FORMAT.

Les formats de fichiers externes peuvent être réutilisés par plusieurs fichiers externes similaires.

Options REJECT

Cette option peut être utilisée uniquement avec des sources de données externes où TYPE = HADOOP.

Vous pouvez spécifier les paramètres REJECT qui déterminent la façon dont PolyBase traite les enregistrements incorrects qu’il récupère à partir de la source de données externe. Un enregistrement de données est considéré comme « incorrect » si les types de données ou le nombre de colonnes ne correspondent pas aux définitions de colonnes de la table externe.

Si vous ne spécifiez pas ou ne changez pas les valeurs REJECT, PolyBase utilise les valeurs par défaut. Ces informations sur les paramètres REJECT sont stockées en tant que métadonnées supplémentaires lorsque vous créez une table externe avec l’instruction CREATE EXTERNAL TABLE. Quand une prochaine instruction SELECT ou SELECT INTO SELECT sélectionne des données dans la table externe, PolyBase utilise les options REJECT pour déterminer le nombre ou le pourcentage de lignes pouvant être rejetées avant de provoquer l’échec de la requête. La requête retourne des résultats (partiels) jusqu’à ce que le seuil de rejet soit dépassé. Ensuite, elle échoue avec le message d’erreur correspondant.

REJECT_TYPE = value | percentage

Précise si l’option REJECT_VALUE est spécifiée comme une valeur littérale ou un pourcentage.

value

REJECT_VALUE est une valeur littérale, et non un pourcentage. La requête échoue lorsque le nombre de lignes rejetées dépasse la valeur reject_value.

Par exemple, si REJECT_VALUE = 5 et REJECT_TYPE = value, la requête SELECT échoue au bout de cinq lignes rejetées.

percentage

REJECT_VALUE est un pourcentage, et non une valeur littérale. Une requête échoue lorsque le pourcentage de lignes ayant échoué dépasse la valeur reject_value. Le pourcentage de lignes ayant échoué est calculé à intervalles.

REJECT_VALUE = reject_value

Spécifie la valeur ou le pourcentage de lignes pouvant être rejetées avant de provoquer l’échec de la requête.

Pour REJECT_TYPE = value, reject_value doit être un entier compris entre 0 et 2 147 483 647.

Pour REJECT_TYPE = percentage, reject_value doit être une valeur float comprise entre 0 et 100.

REJECT_SAMPLE_VALUE = reject_sample_value

Cet attribut est nécessaire lorsque vous spécifiez REJECT_TYPE = percentage. Il détermine le nombre de lignes à tenter de récupérer avant que PolyBase ne recalcule le pourcentage de lignes rejetées.

Le paramètre reject_sample_value doit être un entier compris entre 0 et 2 147 483 647.

Par exemple, si REJECT_SAMPLE_VALUE = 1000, PolyBase calcule le pourcentage de lignes ayant échoué après avoir tenté d’importer 1000 lignes à partir du fichier de données externe. Si le pourcentage de lignes ayant échoué est inférieur à la valeur de reject_value, PolyBase tente de récupérer 1000 autres lignes. Il continue de recalculer le pourcentage de lignes ayant échoué après avoir tenté d’importer chacune des 1000 lignes supplémentaires.

Notes

Étant donné que PolyBase calcule le pourcentage de lignes ayant échoué à intervalles, le pourcentage de lignes ayant échoué peut dépasser la valeur de reject_value.

Exemple :

Cet exemple montre comment les trois options REJECT interagissent les unes avec les autres. Par exemple, si REJECT_TYPE = percentage, REJECT_VALUE = 30 et REJECT_SAMPLE_VALUE = 100, le scénario suivant peut se produire :

• PolyBase tente de récupérer les 100 premières lignes : la récupération échoue pour 25 d’entre elles, et réussit pour les 75 autres.
• Le pourcentage de lignes ayant échoué qui est obtenu est de 25 %, ce qui est inférieur à la valeur de rejet de 30 %. Par conséquent, PolyBase va continuer de récupérer les données à partir de la source de données externe.
• PolyBase tente de charger les 100 lignes suivantes. Cette fois-ci, le chargement réussit pour 25 lignes et échoue pour les 75 autres.
• Le pourcentage de lignes ayant échoué est recalculé et on obtient 50 %. Le pourcentage de lignes ayant échoué a donc dépassé la valeur de rejet de 30 %.
• La requête PolyBase échoue après le rejet de 50 % des 200 premières lignes qu’elle a tenté de retourner. Notez que les lignes correspondantes sont retournées avant que la requête PolyBase ne détecte que le seuil de rejet a été dépassé.

REJECTED_ROW_LOCATION = Emplacement de répertoire

S’applique à : SQL Server 2019 CU6 (et versions ultérieures) et Azure Synapse Analytics.

Spécifie le répertoire dans la Source de données externe dans lequel les lignes rejetées et le fichier d’erreur correspondant doivent être écrits.

Si le chemin spécifié n’existe pas, PolyBase en crée un en votre nom. Un répertoire enfant est créé sous le nom « _rejectedrows ». Le caractère « _ » garantit que le répertoire est placé dans une séquence d’échappement pour le traitement d’autres données, sauf s’il est explicitement nommé dans le paramètre d’emplacement. Dans ce répertoire se trouve un dossier créé d’après l’heure de soumission du chargement au format YearMonthDay -HourMinuteSecond (par exemple 20230330-173205). Dans ce dossier, deux types de fichiers sont écrits : le fichier _reason (raison) et le fichier de données. Cette option peut être utilisée uniquement avec des sources de données externes où TYPE = HADOOP et pour des tables externes avec DELIMITEDTEXT FORMAT_TYPE. Pour plus d’informations, consultez CREATE EXTERNAL DATA SOURCE et CREATE EXTERNAL FILE FORMAT.

Les fichiers de raison et les fichiers de données ont tous deux le queryID associé à l’instruction CTAS. Comme les données et la raison se trouvent dans des fichiers distincts, les fichiers correspondants ont un suffixe analogue.

Autorisations

Nécessite les autorisations utilisateur suivantes :

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT (s’applique uniquement aux sources de données externes Hadoop et Stockage Azure)
• CONTROL DATABASE (s’applique uniquement aux sources de données externes Hadoop et Stockage Azure)

Notez que la connexion distante spécifiée dans les informations d’identification DATABASE SCOPED utilisées dans la commande CREATE EXTERNAL TABLE doit disposer d’une autorisation en lecture pour le chemin/la table/la collection sur la source de données externe spécifiée dans le paramètre LOCATION. Si vous envisagez d’utiliser cette TABLE EXTERNE pour exporter des données vers une source de données externe Hadoop ou Stockage Azure, la connexion spécifiée doit disposer d’une autorisation en écriture sur le chemin spécifié dans LOCATION. Notez que Hadoop n’est actuellement pas pris en charge dans SQL Server 2022 (16.x).

Pour Stockage Blob Azure, lors de la configuration des clés d’accès et de la signature d’accès partagé (SAP) dans le portail Azure, les comptes de stockage Stockage Blob Azure ou ADLS Gen2, configurez les autorisations acceptées pour octroyer au moins des autorisations enlecture et en écriture. L’autorisation de liste peut également être requise en cas de recherche dans plusieurs dossiers. Vous devez également sélectionner Conteneur et Objet comme types de ressources autorisés.

Important

L’autorisation ALTER ANY EXTERNAL DATA SOURCE accorde à n’importe quel principal la possibilité de créer et de modifier tout objet de source de données externe. Par conséquent, elle permet également d’accéder à toutes les informations d’identification délimitées à la base de données. Cette autorisation doit être considérée comme fournissant des privilèges très élevés, et doit donc être accordée uniquement aux principaux de confiance du système.

Gestion des erreurs

Lors de l’exécution de l’instruction CREATE EXTERNAL TABLE, PolyBase tente de se connecter à la source de données externe. Si la tentative de connexion échoue, l’instruction échoue et la table externe n’est pas créée. L’échec de la commande peut prendre plusieurs minutes, car PolyBase tente plusieurs connexions avant que la requête n’échoue.

Remarques

Dans les scénarios de requête ad hoc, comme SELECT FROM EXTERNAL TABLE, PolyBase stocke les lignes qui sont récupérées de la source de données externe dans une table temporaire. Une fois la requête terminée, PolyBase supprime la table temporaire. Aucune donnée permanente n’est stockée dans les tables SQL.

En revanche, dans le scénario d’importation, comme avec SELECT INTO FROM EXTERNAL TABLE, PolyBase stocke les lignes qui sont extraites de la source de données externe sous forme de données permanentes dans la table SQL. La nouvelle table est créée lors de l’exécution de la requête, au moment où PolyBase récupère les données externes.

PolyBase peut envoyer (push) une partie du calcul des requêtes vers Hadoop pour améliorer les performances des requêtes. Cette action est appelée « poussée de prédicats ». Pour l’activer, spécifiez l’option d’emplacement du Gestionnaire de ressources Hadoop dans CREATE EXTERNAL DATA SOURCE.

Vous pouvez créer plusieurs tables externes qui référencent les mêmes sources de données externes ou des sources différentes.

Limitations et restrictions

Dans la mesure où les données d’une table externe ne sont pas sous le contrôle de gestion direct de SQL Server, elles peuvent être changées ou supprimées à tout moment par un processus externe. Par conséquent, il n’est pas garanti que les résultats d’une requête exécutée sur une table externe soient déterministes. La même requête peut retourner des résultats différents chaque fois qu’elle est exécutée sur une table externe. De même, une requête peut échouer si les données externes sont déplacées ou supprimées.

Vous pouvez créer plusieurs tables externes qui référencent chacune des sources de données externes différentes. Si vous exécutez simultanément plusieurs requêtes sur des sources de données Hadoop différentes, chaque source Hadoop doit utiliser le même paramètre de configuration de serveur « hadoop connectivity » (connectivité Hadoop). Par exemple, vous ne pouvez pas exécuter simultanément une requête sur un cluster Hadoop Cloudera et sur un cluster Hadoop Hortonworks, puisqu’ils utilisent des paramètres de configuration différents. Pour connaître les paramètres de configuration et les combinaisons prises en charge, consultez Configuration de la connectivité PolyBase.

Lorsque la table externe utilise DELIMITEDTEXT, CSV, PARQUET ou DELTA en tant que types de données, les tables externes prennent uniquement en charge les statistiques d’une colonne par commande CREATE STATISTICS.

Seules les instructions DDL suivantes sont autorisées avec les tables externes :

• CREATE TABLE et DROP TABLE
• CREATE STATISTICS et DROP STATISTICS
• CREATE VIEW et DROP VIEW

Les constructions et les opérations suivantes ne sont pas prises en charge :

• La contrainte DEFAULT sur les colonnes de table externe
• Les opérations DML delete, insert et update

Limitations des requêtes

PolyBase peut consommer un maximum de 33 000 fichiers par dossier lors de l’exécution simultanée de 32 requêtes PolyBase. Ce nombre maximal inclut les fichiers et les sous-dossiers de chaque dossier HDFS. Si le degré de concurrence est inférieur à 32, un utilisateur peut exécuter des requêtes PolyBase sur des dossiers dans des systèmes HDFS contenant plus de 33 000 fichiers. Il est recommandé de raccourcir au maximum les chemins de fichiers externes et de ne pas utiliser plus de 30 000 fichiers par dossier HDFS. Lorsque trop de fichiers sont référencés, une exception d’insuffisance de mémoire Java Virtual Machine (JVM) peut être levée.

Limitations concernant la largeur des tables

Dans SQL Server 2016, PolyBase a une limite de largeur de ligne de 32 Ko, basée sur la taille maximale d’une ligne valide par définition de table. Si la somme du schéma de colonne est supérieure à 32 Ko, PolyBase ne peut pas interroger les données.

Limitations des types de données

Les types de données suivants ne peuvent pas être utilisés dans des tables externes Polybase :

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• Tout type défini par l’utilisateur

Limitations propres à la source de données

Oracle

L’utilisation de synonymes Oracle avec PolyBase n’est pas prise en charge.

Tables externes aux collections MongoDB qui contiennent des tableaux

Pour créer des tables externes dans des collections MongoDB qui contiennent des tableaux, vous devez utiliser l’extension de virtualisation des données pour Azure Data Studio afin de produire une instruction CREATE EXTERNAL TABLE basée sur le schéma détecté par le pilote ODBC PolyBase pour MongoDB. Les actions d’aplatissement sont effectuées automatiquement par le pilote. Vous pouvez également utiliser sp_data_source_objects (Transact-SQL) pour détecter le schéma de collection (colonnes) et créer manuellement la table externe. La procédure stockée sp_data_source_table_columns effectue également automatiquement l’aplatissement via le pilote ODBC PolyBase pour MongoDB. L’extension de virtualisation des données pour Azure Data Studio et sp_data_source_table_columns utilise les mêmes procédures stockées internes pour interroger le schéma externe.

Verrouillage

Verrou partagé sur l’objet SCHEMARESOLUTION.

Sécurité

Les fichiers de données d’une table externe sont stockés dans Hadoop ou Stockage Blob Azure. Ces fichiers de données sont créés et gérés par vos propres processus. Il vous incombe de gérer la sécurité des données externes.

Exemples

R. Créer une table externe avec des données au format texte délimité

Cet exemple montre toutes les étapes nécessaires à la création d’une table externe dont les données sont des fichiers de texte délimité. Il définit la source de données externe mydatasource et le format de fichier externe myfileformat. Ces objets de niveau base de données sont ensuite référencés dans l’instruction CREATE EXTERNAL TABLE. Pour plus d’informations, consultez CREATE EXTERNAL DATA SOURCE et CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL DATA SOURCE mydatasource
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS (FIELD_TERMINATOR ='|')
);

CREATE EXTERNAL TABLE ClickStream (
    url varchar(50),
    event_date date,
    user_IP varchar(50)
)
WITH (
        LOCATION='/webdata/employee.tbl',
        DATA_SOURCE = mydatasource,
        FILE_FORMAT = myfileformat
    )
;

B. Créer une table externe avec des données au format RCFile

Cet exemple montre toutes les étapes nécessaires à la création d’une table externe contenant des données au format RCFile. Il définit la source de données externe mydatasource_rc et le format de fichier externe myfileformat_rc. Ces objets de niveau base de données sont ensuite référencés dans l’instruction CREATE EXTERNAL TABLE. Pour plus d’informations, consultez CREATE EXTERNAL DATA SOURCE et CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL DATA SOURCE mydatasource_rc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_rc
WITH (
    FORMAT_TYPE = RCFILE,
    SERDE_METHOD = 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
)
;

CREATE EXTERNAL TABLE ClickStream_rc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/employee_rc.tbl',
        DATA_SOURCE = mydatasource_rc,
        FILE_FORMAT = myfileformat_rc
    )
;

C. Créer une table externe avec des données au format ORC

Cet exemple montre toutes les étapes nécessaires à la création d’une table externe contenant des données au format ORC. Il définit une source de données externe, mydatasource_orc, et un format de fichier externe, myfileformat_orc. Ces objets de niveau base de données sont ensuite référencés dans l’instruction CREATE EXTERNAL TABLE. Pour plus d’informations, consultez CREATE EXTERNAL DATA SOURCE et CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL DATA SOURCE mydatasource_orc
WITH (
    TYPE = HADOOP,
    LOCATION = 'hdfs://xxx.xxx.xxx.xxx:8020'
)

CREATE EXTERNAL FILE FORMAT myfileformat_orc
WITH (
    FORMAT = ORC,
    COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)
;

CREATE EXTERNAL TABLE ClickStream_orc (
    url varchar(50),
    event_date date,
    user_ip varchar(50)
)
WITH (
        LOCATION='/webdata/',
        DATA_SOURCE = mydatasource_orc,
        FILE_FORMAT = myfileformat_orc
    )
;

D. Interroger des données Hadoop

ClickStream est une table externe qui se connecte au fichier texte délimité employee.tbl dans un cluster Hadoop. La requête suivante ressemble à une requête exécutée sur une table standard. Toutefois, cette requête récupère les données dans Hadoop, puis calcule les résultats.

SELECT TOP 10 (url) FROM ClickStream WHERE user_ip = 'xxx.xxx.xxx.xxx';

E. Joindre des données Hadoop à des données SQL

Cette requête ressemble à une requête JOIN standard exécutée sur deux tables SQL. La différence réside dans le fait que PolyBase récupère les données clickStream dans Hadoop, puis les joint à la table UrlDescription. L’une des tables est une table externe, et l’autre est une table SQL standard.

SELECT url.description
FROM ClickStream cs
JOIN UrlDescription url ON cs.url = url.name
WHERE cs.url = 'msdn.microsoft.com';

F. Importer des données Hadoop dans une table SQL

Cet exemple crée une table SQL, ms_user, qui stocke de façon permanente le résultat d’une jointure entre la table SQL standard user et la table externe ClickStream.

SELECT DISTINCT user.FirstName, user.LastName
INTO ms_user
FROM user INNER JOIN (
    SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com'
    ) AS ms
ON user.user_ip = ms.user_ip;

G. Créer une table externe pour SQL Server

Avant la création d’informations d’identification délimitées à la base de données, la base de données utilisateur doit avoir une clé principale pour protéger les informations d’identification. Pour plus d’informations, consultez CREATE MASTER KEY et CREATE DATABASE SCOPED CREDENTIAL.

     -- Create a Master Key
      CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';
    GO
     /*  specify credentials to external data source
     *  IDENTITY: user name for external source.
     *  SECRET: password for external source.
     */
     CREATE DATABASE SCOPED CREDENTIAL SqlServerCredentials
     WITH IDENTITY = 'username', Secret = 'password';
    GO

Créez une source de données externe nommée SQLServerInstance et une table externe nommée sqlserver.customer :

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE SQLServerInstance
    WITH (
    LOCATION = 'sqlserver://SqlServer',
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = SQLServerCredentials
    );
    GO

    CREATE SCHEMA sqlserver;
    GO

     /* LOCATION: sql server table/view in 'database_name.schema_name.object_name' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE sqlserver.customer(
     C_CUSTKEY INT NOT NULL,
     C_NAME VARCHAR(25) NOT NULL,
     C_ADDRESS VARCHAR(40) NOT NULL,
     C_NATIONKEY INT NOT NULL,
     C_PHONE CHAR(15) NOT NULL,
     C_ACCTBAL DECIMAL(15,2) NOT NULL,
     C_MKTSEGMENT CHAR(10) NOT NULL,
     C_COMMENT VARCHAR(117) NOT NULL
      )
      WITH (
      LOCATION='tpch_10.dbo.customer',
      DATA_SOURCE=SqlServerInstance
     );

I. Créer une table externe pour Oracle

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

   /*
   * LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
   * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
   * CONNECTION_OPTIONS: Specify driver location
   * CREDENTIAL: the database scoped credential, created above.
   */
   CREATE EXTERNAL DATA SOURCE external_data_source_name
   WITH (
     LOCATION = 'oracle://<server address>[:<port>]',
     -- PUSHDOWN = ON | OFF,
     CREDENTIAL = credential_name)

   /*
   * LOCATION: Oracle table/view in '<database_name>.<schema_name>.<object_name>' format. Note this may be case sensitive in the Oracle database.
   * DATA_SOURCE: the external data source, created above.
   */
   CREATE EXTERNAL TABLE customers(
   [O_ORDERKEY] DECIMAL(38) NOT NULL,
   [O_CUSTKEY] DECIMAL(38) NOT NULL,
   [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
   [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
   [O_ORDERDATE] DATETIME2(0) NOT NULL,
   [O_ORDERPRIORITY] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_CLERK] CHAR(15) COLLATE Latin1_General_BIN NOT NULL,
   [O_SHIPPRIORITY] DECIMAL(38) NOT NULL,
   [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
   )
   WITH (
    LOCATION='DB1.mySchema.customer',
    DATA_SOURCE= external_data_source_name
   );

J. Créer une table externe pour Teradata

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

    /* LOCATION: Location string should be of format '<vendor>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = teradata://<server address>[:<port>],
   -- PUSHDOWN = ON | OFF,
    CREDENTIAL =credential_name
    );


     /* LOCATION: Teradata table/view in '<database_name>.<object_name>' format
      * DATA_SOURCE: the external data source, created above.
      */
     CREATE EXTERNAL TABLE customer(
      L_ORDERKEY INT NOT NULL,
      L_PARTKEY INT NOT NULL,
     L_SUPPKEY INT NOT NULL,
     L_LINENUMBER INT NOT NULL,
     L_QUANTITY DECIMAL(15,2) NOT NULL,
     L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,
     L_DISCOUNT DECIMAL(15,2) NOT NULL,
     L_TAX DECIMAL(15,2) NOT NULL,
     L_RETURNFLAG CHAR NOT NULL,
     L_LINESTATUS CHAR NOT NULL,
     L_SHIPDATE DATE NOT NULL,
     L_COMMITDATE DATE NOT NULL,
     L_RECEIPTDATE DATE NOT NULL,
     L_SHIPINSTRUCT CHAR(25) NOT NULL,
     L_SHIPMODE CHAR(10) NOT NULL,
     L_COMMENT VARCHAR(44) NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

K. Créer une table externe pour MongoDB

  -- Create a Master Key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

   /*
   * Specify credentials to external data source
   * IDENTITY: user name for external source.
   * SECRET: password for external source.
   */
   CREATE DATABASE SCOPED CREDENTIAL credential_name
   WITH IDENTITY = 'username', Secret = 'password';

     /* LOCATION: Location string should be of format '<type>://<server>[:<port>]'.
    * PUSHDOWN: specify whether computation should be pushed down to the source. ON by default.
    * CONNECTION_OPTIONS: Specify driver location
    * CREDENTIAL: the database scoped credential, created above.
    */
    CREATE EXTERNAL DATA SOURCE external_data_source_name
    WITH (
    LOCATION = mongodb://<server>[:<port>],
    -- PUSHDOWN = ON | OFF,
      CREDENTIAL = credential_name
    );

     /* LOCATION: MongoDB table/view in '<database_name>.<schema_name>.<object_name>' format
     * DATA_SOURCE: the external data source, created above.
     */
     CREATE EXTERNAL TABLE customers(
     [O_ORDERKEY] DECIMAL(38) NOT NULL,
     [O_CUSTKEY] DECIMAL(38) NOT NULL,
     [O_ORDERSTATUS] CHAR COLLATE Latin1_General_BIN NOT NULL,
     [O_TOTALPRICE] DECIMAL(15,2) NOT NULL,
     [O_ORDERDATE] DATETIME2(0) NOT NULL,
     [O_COMMENT] VARCHAR(79) COLLATE Latin1_General_BIN NOT NULL
     )
     WITH (
     LOCATION='customer',
     DATA_SOURCE= external_data_source_name
     );

L. Interroger le stockage d’objets compatible S3 via une table externe

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

L’exemple suivant illustre l’utilisation de T-SQL pour interroger un fichier parquet stocké dans un stockage d’objets compatible S3 via l’interrogation d’une table externe. L’exemple utilise un chemin relatif dans la source de données externe.

CREATE EXTERNAL DATA SOURCE s3_ds
WITH
(   LOCATION = 's3://<ip_address>:<port>/'
,   CREDENTIAL = s3_dc
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
GO
CREATE EXTERNAL TABLE Region(
r_regionkey BIGINT,
r_name CHAR(25),
r_comment VARCHAR(152) )
WITH (LOCATION = '/region/', DATA_SOURCE = 's3_ds',
FILE_FORMAT = ParquetFileFormat);
GO

Étapes suivantes

En savoir plus sur les concepts associés dans les articles suivants :

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• sp_data_source_objects (Transact-SQL)
• Extension de virtualisation des données pour Azure Data Studio