CREATE EXTERNAL TABLE (Transact-SQL)

Crea una tabla externa.

En este artículo se proporciona la sintaxis, argumentos, comentarios, permisos y ejemplos para cualquier producto SQL que elija.

Para más información sobre las convenciones de sintaxis, vea Convenciones de sintaxis de Transact-SQL.

Selección de un producto

En la fila siguiente, seleccione el nombre del producto que le interese; de esta manera, solo se mostrará la información de ese producto.

* SQL Server *  

SQL Database

Instancia administrada de Azure SQL

Azure Synapse
Análisis

Analytics Platform
System (PDW)

 

Introducción: SQL Server

Este comando crea una tabla externa para PolyBase con el fin de acceder a los datos almacenados en un clúster de Hadoop o una tabla externa de PolyBase en Azure Blob Storage en la que se hace referencia a datos almacenados en un clúster de Hadoop o en Azure Blob Storage.

Se aplica a: SQL Server 2016 (o posterior)

Usa una tabla externa con un origen de datos externo para consultas de PolyBase. Los orígenes de datos externos se usan para establecer la conectividad y admiten estos casos de uso principales:

• Virtualización y carga de datos mediante PolyBase
• Operaciones de carga masiva mediante SQL Server o SQL Database utilizando BULK INSERT o OPENROWSET

Vea también CREATE EXTERNAL DATA SOURCE y DROP EXTERNAL TABLE.

Sintaxis

-- 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 ]

Argumentos

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

Nombre de entre una y tres partes de la tabla que se va a crear. En una tabla externa, SQL solo almacena los metadatos de tabla junto con estadísticas básicas sobre el archivo o carpeta a los que se hace referencia en Hadoop o Azure Blob Storage. Ningún dato real se mueve o se almacena en SQL Server.

Importante

Para obtener el mejor rendimiento, si el controlador de origen de datos externo admite un nombre de tres partes, se recomienda encarecidamente que lo proporcione.

<column_definition> [ ,...n ]

CREATE EXTERNAL TABLE admite la capacidad de configurar el nombre de columna, tipo de datos, nulabilidad e intercalación. No se puede usar DEFAULT CONSTRAINT en tablas externas.

Las definiciones de columna, incluidos los tipos de datos y el número de columnas, deben coincidir con los datos de los archivos externos. Si hay algún error de coincidencia, se rechazarán las filas de archivo al consultar los datos reales.

LOCATION = 'folder_or_filepath'

Especifica la carpeta o la ruta de acceso y el nombre de archivo para los datos en Hadoop o Azure Blob Storage. Además, se admite el almacenamiento de objetos compatible con S3 a partir de SQL Server 2022 (16.x). La ubicación empieza desde la carpeta raíz. La carpeta raíz es la ubicación de datos especificada en el origen de datos externo.

En SQL Server, la instrucción CREATE EXTERNAL TABLE crea la ruta de acceso y la carpeta si todavía no existen. Luego puede usar INSERT INTO para exportar datos de una tabla de SQL Server local al origen de datos externo. Para obtener más información, vea Escenarios de consulta de PolyBase.

Si se especifica LOCATION para que sea una carpeta, una consulta de PolyBase que seleccione en la tabla externa recuperará los archivos de la carpeta y todas sus subcarpetas. Al igual que Hadoop, PolyBase no devuelve carpetas ocultas. Tampoco devuelve los archivos cuyo nombre comienza con un carácter de subrayado (_) o un punto (.).

En el ejemplo de la imagen siguiente, si LOCATION='/webdata/', una consulta de PolyBase devolverá filas de mydata.txt y mydata2.txt. No devolverá mydata3.txt porque es un archivo en una subcarpeta oculta. Y no devolverá _hidden.txt porque es un archivo oculto.

Para cambiar el valor predeterminado y leer solo de la carpeta raíz, establezca el atributo <polybase.recursive.traversal> en "false" en el archivo de configuración core-site.xml. Este archivo se encuentra en <SqlBinRoot>\PolyBase\Hadoop\Conf bajo el bin raíz de SQL Server. Por ejemplo, C:\Program Files\Microsoft SQL Server\MSSQL13.XD14\MSSQL\Binn.

DATA_SOURCE = external_data_source_name

Especifica el nombre del origen de datos externo que contiene la ubicación donde se almacenan los datos externos. Esta ubicación es un sistema de archivos de Hadoop (HDFS), un contenedor de Azure Blob Storage o Azure Data Lake Store. Para crear un origen de datos externo, useCREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Especifica el nombre del objeto de formato de archivo externo que almacena el tipo de archivo y el método de compresión para los datos externos. Para crear un formato de archivo externo, use CREATE EXTERNAL FILE FORMAT.

Los formatos de archivo externos se pueden volver a usar en varios archivos externos similares.

Opciones de Reject

Esta opción solo se puede usar con orígenes de datos externos donde TYPE = HADOOP.

Puede especificar parámetros de Reject que determinen cómo va a administrar PolyBase los registros desfasados que recupera del origen de datos externo. Un registro de datos se considera "desfasado" si los tipos de datos reales o el número de columnas no coinciden con las definiciones de columna de la tabla externa.

Si no se especifican ni se cambian los valores de Reject, PolyBase usa los valores predeterminados. Esta información sobre los parámetros de Reject se almacena como metadatos adicionales al crear una tabla externa con la instrucción CREATE EXTERNAL TABLE. Cuando una futura instrucción SELECT o SELECT INTO SELECT selecciona datos de la tabla externa, PolyBase usa las opciones de rechazo para determinar el número o porcentaje de filas que se pueden rechazar antes de que se produzca un error en la consulta real. La consulta devolverá resultados (parciales) hasta que se supere el umbral de rechazo. Después, se produce un error con el mensaje de error correspondiente.

REJECT_TYPE = value | percentage

Aclara si la opción REJECT_VALUE se especifica como un valor literal o como un porcentaje.

value

REJECT_VALUE es un valor literal, no un porcentaje. Si el número de filas rechazadas supera el valor reject_value, se produce un error en la consulta.

Por ejemplo, si REJECT_VALUE = 5 y REJECT_TYPE = value, se producirá un error en la consulta SELECT después de que se hayan rechazado cinco filas.

percentage

REJECT_VALUE es un porcentaje, no un valor literal. Si el porcentaje de filas con errores supera el valor reject_value, se produce un error al realizar una consulta. El porcentaje de filas con errores se calcula a intervalos.

REJECT_VALUE = reject_value

Especifica el valor o el porcentaje de filas que se pueden rechazar antes de que se produzca un error en la consulta.

Para REJECT_TYPE = value, reject_value debe ser un entero comprendido entre 0 y 2.147.483.647.

Para REJECT_TYPE = percentage, reject_value debe ser un valor float comprendido entre 0 y 100.

REJECT_SAMPLE_VALUE = reject_sample_value

Este atributo es necesario cuando se especifica REJECT_TYPE = percentage. Determina el número de filas que se intentan recuperar antes de que PolyBase vuelva a calcular el porcentaje de filas rechazadas.

El parámetro reject_sample_value debe ser un entero comprendido entre 0 y 2.147.483.647.

Por ejemplo, si REJECT_SAMPLE_VALUE = 1000, PolyBase calcula el porcentaje de filas con errores después de haber intentado importar 1000 filas desde el archivo de datos externos. Si el porcentaje de filas con errores es inferior al valor de reject_value, PolyBase intenta recuperar otras 1000 filas. Sigue recalculando el porcentaje de filas con errores después de intentar importar cada 1000 filas más.

Nota

Puesto que PolyBase calcula el porcentaje de filas con errores a intervalos, el porcentaje real de filas con errores puede superar el valor de reject_value.

Ejemplo:

En este ejemplo se muestra cómo interactúan entre sí las tres opciones REJECT. Por ejemplo, si REJECT_TYPE = percentage, REJECT_VALUE = 30 y REJECT_SAMPLE_VALUE = 100, sucederá lo siguiente:

• PolyBase intenta recuperar las 100 primeras filas; 25 no se importan y 75 sí.
• El porcentaje de las filas con errores se calcula en un 25 %, que es menor que el valor de rechazo de 30 %. Por tanto, PolyBase seguirá recuperando datos del origen de datos externo.
• PolyBase intenta cargar las siguientes 100 filas; esta vez, 25 lo hacen y 75 no.
• El porcentaje de filas con errores se recalcula en un 50 %. El porcentaje de filas con errores supera pues el valor de rechazo de 30 %.
• Se produce un error en la consulta de PolyBase con un 50 % de filas rechazadas después de intentar devolver las 200 primeras filas. Tenga en cuenta que se han devuelto filas coincidentes antes de que la consulta de PolyBase detecte que se ha superado el umbral de rechazo.

REJECTED_ROW_LOCATION = Ubicación del directorio

Se aplica a: SQL Server 2019 CU6 y versiones posteriores, Azure Synapse Analytics.

Especifica el directorio del origen de datos externo en el que se deben escribir las filas rechazadas y el archivo de errores correspondiente.

Si la ruta de acceso especificada no existe, PolyBase creará una en su nombre. Se crea un directorio secundario con el nombre «_rejectedrows». El carácter «_» garantiza que se escape el directorio para otro procesamiento de datos a menos que se mencione explícitamente en el parámetro de ubicación. En este directorio hay una carpeta que se crea según la hora de envío de la carga con el formato YearMonthDay -HourMinuteSecond (por ejemplo, 20230330-173205). En esta carpeta se escriben dos tipos de archivos: el archivo _reason y el archivo de datos. Esta opción solo se puede usar con orígenes de datos externos donde TYPE = HADOOP y para tablas externas mediante DELIMITEDTEXT FORMAT_TYPE. Para más información, vea CREATE EXTERNAL DATA SOURCE y CREATE EXTERNAL FILE FORMAT.

Los archivos reason y los archivos de datos tienen el identificador de consulta asociado a la instrucción CTAS. Como los datos y los archivos reason están en archivos independientes, los archivos correspondientes tienen un sufijo coincidente.

Permisos

Requiere estos permisos de usuario:

• CREATE TABLE
• ALTER ANY SCHEMA
• ALTER ANY EXTERNAL DATA SOURCE
• ALTER ANY EXTERNAL FILE FORMAT (solo se aplica a orígenes de datos externos de Hadoop y Azure Storage)
• CONTROL DATABASE (solo se aplica a orígenes de datos externos de Hadoop y Azure Storage)

Tenga en cuenta que el inicio de sesión remoto especificado en el valor de DATABASE SCOPED CREDENTIAL usado en el comando CREATE EXTERNAL TABLE debe tener permiso de lectura para la ruta de acceso, tabla o colección en el origen de datos externo especificado en el parámetro LOCATION. Si planea usar esta EXTERNAL TABLE para exportar datos a un origen de datos externo de Hadoop o Azure Storage, el inicio de sesión especificado debe tener el permiso de escritura en la ruta de acceso especificada en LOCATION. Tenga en cuenta que Hadoop no se admite actualmente en SQL Server 2022 (16.x).

Para Azure Blob Storage, al configurar las claves de acceso y la firma de acceso compartido (SAS) en Azure Portal, las cuentas de almacenamiento de Azure Blob Storage o ADLS Gen2, configure los permisos permitidos para conceder al menos permisos de lectura y escritura. También es posible que se requiera el permiso de lista al buscar entre carpetas. También debe seleccionar Contenedor y Objeto como los tipos de recursos permitidos.

Importante

El permiso ALTER ANY EXTERNAL DATA SOURCE concede a cualquier entidad de seguridad la capacidad de crear y modificar cualquier objeto de origen de datos externo y, por tanto, también permite acceder a todas las credenciales con ámbito de base de datos de la base de datos. Debe considerarse como un permiso con muchos privilegios, por lo que solo debe concederse a las entidades de seguridad de confianza del sistema.

Control de errores

Al ejecutar la instrucción CREATE EXTERNAL TABLE, PolyBase intenta conectarse al origen de datos externo. Si se produce un error en el intento de conexión, se producirá un error en la instrucción y no se creará la tabla externa. El error en el comando puede tardar un minuto o más en producirse, ya que PolyBase vuelve a intentar conectar hasta que al final da como errónea la consulta.

Comentarios

En escenarios de consulta "ad hoc", como SELECT FROM EXTERNAL TABLE, PolyBase almacena en una tabla temporal las filas que se recuperan del origen de datos externo. Una vez finalizada la consulta, PolyBase quita y elimina la tabla temporal. En las tablas SQL no se almacenan datos permanentes.

En cambio, en el escenario de importación, como SELECT INTO FROM EXTERNAL TABLE, PolyBase almacena las filas que se recuperan del origen de datos externo como datos permanentes en la tabla SQL. La tabla se crea durante la ejecución de la consulta cuando PolyBase recupera los datos externos.

PolyBase puede insertar algunos de los cálculos de la consulta en Hadoop para mejorar el rendimiento. Esta acción se denomina "aplicación de predicado". Para habilitarla, especifique la opción de ubicación del administrador de recursos de Hadoop en CREATE EXTERNAL DATA SOURCE.

Puede crear varias tablas externas que hagan referencia a los mismos orígenes de datos externos o a otros.

Limitaciones y restricciones

Puesto que los datos de una tabla externa no están bajo el control de administración directo de SQL Server, un proceso externo puede modificarlos o quitarlos en cualquier momento. Como resultado, no se garantiza que los resultados de la consulta sobre una tabla externa sean deterministas. La misma consulta puede devolver resultados diferentes cada vez que se ejecute en una tabla externa. Del mismo modo, es posible que se produzca un error en una consulta si los datos externos se mueven o se quitan.

Puede crear varias tablas externas que hagan referencia a diferentes orígenes de datos externos. Si ejecuta consultas de forma simultánea sobre otros orígenes de datos de Hadoop, cada uno tendrá que usar el mismo valor de configuración de servidor "hadoop connectivity". Por ejemplo, no se puede ejecutar simultáneamente una consulta en un clúster de Cloudera Hadoop y un clúster de Hortonworks Hadoop, ya que usan valores de configuración diferentes. Para obtener los valores de configuración y las combinaciones admitidas, vea Configuración de conectividad de PolyBase.

Cuando la tabla externa usa DELIMITEDTEXT, CSV, PARQUET o DELTA como tipos de datos, las tablas externas solo admiten estadísticas para una columna por cada comando CREATE STATISTICS.

En tablas externas solo se admiten estas instrucciones de lenguaje de definición de datos (DDL):

• CREATE TABLE y DROP TABLE
• CREATE STATISTICS y DROP STATISTICS
• CREATE VIEW y DROP VIEW

Construcciones y operaciones no admitidas:

• Restricción DEFAULT en columnas de tabla externa
• Operaciones de lenguaje de manipulación de datos (DML) delete, insert y update

Limitaciones de las consultas

PolyBase puede consumir un máximo de 33 000 archivos por carpeta cuando se ejecutan 32 consultas simultáneas de PolyBase. Esta cifra máxima engloba los archivos y las subcarpetas de cada carpeta de HDFS. Si el grado de simultaneidad es inferior a 32, un usuario puede ejecutar consultas de PolyBase en carpetas de HDFS que contengan más de 33 000 archivos. Se recomienda tener unas rutas de acceso de archivos externos cortas y no usar más de 30.000 archivos por carpeta de HDFS. Si hay referencias a demasiados archivos, podría producirse una excepción de memoria insuficiente de Máquina virtual Java (JVM).

Limitaciones de ancho de tabla

PolyBase en SQL Server 2016 tiene un límite de ancho de fila de 32 KB, en función del tamaño máximo de una sola fila válida por definición de tabla. Si la suma del esquema de columnas es superior a 32 KB, PolyBase no puede consultar los datos.

Limitaciones de tipos de datos

Los tipos de datos siguientes no se pueden usar en las tablas externas de PolyBase:

• geography
• geometry
• hierarchyid
• image
• text
• nText
• xml
• Cualquier tipo definido por el usuario

Limitaciones específicas del origen de datos

Oracle

Los sinónimos de Oracle no se admiten para su uso con PolyBase.

Tablas externas a colecciones de MongoDB que contienen matrices

Para crear tablas externas en colecciones de MongoDB que contienen matrices, debe usar la extensión de virtualización de datos para Azure Data Studio a fin de generar una instrucción CREATE EXTERNAL TABLE basada en el esquema que detecta el controlador ODBC de PolyBase para MongoDB. El controlador realiza automáticamente las acciones de acoplamiento. También puede usar sp_data_source_objects (Transact-SQL) para detectar el esquema de colección (columnas) y crear manualmente la tabla externa. El procedimiento almacenado sp_data_source_table_columns también realiza automáticamente el acoplamiento mediante el controlador ODBC de PolyBase para MongoDB. La extensión de virtualización de datos para Azure Data Studio y sp_data_source_table_columns usa los mismos procedimientos almacenados internos a fin de consultar el esquema externo.

Bloqueo

Bloqueo compartido en el objeto SCHEMARESOLUTION.

Seguridad

Los archivos de datos de una tabla externa se almacenan en Hadoop o Azure Blob Storage. Son sus propios procesos los que crean y administran estos archivos de datos. Es responsabilidad suya administrar la seguridad de los datos externos.

Ejemplos

A. Crear una tabla externa con datos en formato delimitado por texto

En este ejemplo se muestran todos los pasos necesarios para crear una tabla externa que tenga datos con formato de archivos delimitados por texto. Se define un origen de datos externo mydatasource y un formato de archivo externo myfileformat. Luego se hace referencia a estos objetos de nivel de base de datos en la instrucción CREATE EXTERNAL TABLE. Para más información, vea CREATE EXTERNAL DATA SOURCE y 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. Crear una tabla externa con datos en formato RCFile

En este ejemplo se muestran todos los pasos necesarios para crear una tabla externa que tenga datos con formato de RCFiles. Se define un origen de datos externo mydatasource_rc y un formato de archivo externo myfileformat_rc. Luego se hace referencia a estos objetos de nivel de base de datos en la instrucción CREATE EXTERNAL TABLE. Para más información, vea CREATE EXTERNAL DATA SOURCE y 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. Crear una tabla externa con datos en formato ORC

En este ejemplo se muestran todos los pasos necesarios para crear una tabla externa que tenga datos con formato de archivos ORC. Se define un origen de datos externo mydatasource_orc y un formato de archivo externo myfileformat_orc. Luego se hace referencia a estos objetos de nivel de base de datos en la instrucción CREATE EXTERNAL TABLE. Para más información, vea CREATE EXTERNAL DATA SOURCE y 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. Consultar datos de Hadoop

ClickStream es una tabla externa que se conecta con el archivo de texto delimitado employee.tbl en un clúster de Hadoop. La consulta siguiente parece una consulta en una tabla estándar, pero recupera datos de Hadoop y luego calcula los resultados.

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

E. Combinar datos de Hadoop con datos SQL

Esta consulta parece una operación JOIN estándar en dos tablas SQL. La diferencia es que PolyBase recupera la información de la secuencia de clic de Hadoop y luego los combina con la tabla UrlDescription. Una tabla es una tabla externa y la otra es una tabla SQL estándar.

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

F. Importar datos de Hadoop en una tabla SQL

En este ejemplo se crea una nueva tabla SQL ms_user que almacena de forma permanente el resultado de una combinación entre la tabla SQL estándar user y la tabla externa 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. Crear una tabla externa para SQL Server

Para poder crear una credencial con ámbito de base de datos, la base de datos debe tener una clave maestra para proteger la credencial. Para obtener más información, consulte CREATE MASTER KEY y 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

Cree un nuevo origen de datos externo denominado SQLServerInstance y una tabla externa denominada 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. Crear una tabla externa para 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. Crear una tabla externa para 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. Crear una tabla externa para 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. Consulta del almacenamiento de objetos compatible con S3 a través de una tabla externa

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores

En el ejemplo siguiente se muestra el uso de T-SQL para consultar un archivo PARQUET almacenado en el almacenamiento de objetos compatible con S3 mediante la consulta de tabla externa. En el ejemplo se usa una ruta de acceso relativa dentro del origen de datos externo.

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

Pasos siguientes

Obtenga más información sobre los conceptos relacionados en los artículos siguientes:

• CREATE EXTERNAL DATA SOURCE
• CREATE EXTERNAL FILE FORMAT
• sp_data_source_objects (Transact-SQL)
• Extensión de virtualización de datos para Azure Data Studio