CREATE EXTERNAL TABLE (Transact-SQL)

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.

Diagrama de carpetas y datos de archivo para tablas externas.

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:

* Azure SQL Database *

Introducción: Azure SQL Database

En Azure SQL Database, crea una tabla externa para consultas elásticas (en versión preliminar).

Vea también CREATE EXTERNAL DATA SOURCE.

Sintaxis

-- Create a table for use with elastic query
CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH ( <sharded_external_table_options> )
[;]

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

<sharded_external_table_options> ::=
        DATA_SOURCE = external_data_source_name,
        SCHEMA_NAME = N'nonescaped_schema_name',
        OBJECT_NAME = N'nonescaped_object_name',
        [DISTRIBUTION  = SHARDED(sharding_column_name) | REPLICATED | ROUND_ROBIN]]
    )
[;]

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 la carpeta a los que se hace referencia en Azure SQL Database. Ningún dato real se mueve o se almacena en Azure SQL Database.

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.

Nota

Text, nText y XML no son tipos de datos admitidos para las columnas de tablas externas para Azure SQL Database.

Sharded external table options

Especifica el origen de datos externo (un origen de datos no SQL Server) y un método de distribución para la consulta elástica.

DATA_SOURCE

La cláusula DATA_SOURCE define el origen de datos externo (un mapa de particiones) que se usa para la tabla externa. Para obtener un ejemplo, vea Creación de tablas externas.

Importante

Azure SQL Database admite la creación de tablas externas para los tipos RDMS y SHARD_MAP_MANAGER de EXTERNAL DATA SOURCE. Azure SQL Database no admite la creación de tablas externas en Azure Blob Storage.

SCHEMA_NAME y OBJECT_NAME

Las cláusulas SCHEMA_NAME y OBJECT_NAME asignan la definición de tabla externa a una tabla en un esquema diferente. Si se omite, se considera que el esquema del objeto remoto es "dbo" y que su nombre es idéntico al de la tabla externa que se define. Esto es útil si el nombre de la tabla remota ya existe en la base de datos donde desea crear la tabla externa. Por ejemplo, quiere definir una tabla externa para obtener una vista agregada de las vistas de catálogo o DMV en la capa de datos con escala horizontal. Puesto que las vistas de catálogo y DMV ya existen localmente, no se pueden usar sus nombres para la definición de la tabla externa. En su lugar, use otro nombre y el nombre de la vista de catálogo o la DMV en las cláusulas SCHEMA_NAME u OBJECT_NAME. Para obtener un ejemplo, vea Creación de tablas externas.

DISTRIBUTION

Opcional. Este argumento solo es obligatorio para bases de datos de tipo SHARD_MAP_MANAGER. Este argumento controla si una tabla se trata como una tabla con particiones o una tabla replicada. Con las tablas SHARDED (nombre de columna), los datos de las distintas tablas no se superponen. REPLICATED especifica que las tablas tienen los mismos datos en cada partición. ROUND_ROBIN indica que se usa un método específico de la aplicación para distribuir los datos.

La cláusula DISTRIBUTION especifica la distribución de datos que se usa en esta tabla. El procesador de consultas usa la información proporcionada en la cláusula DISTRIBUTION para crear los planes de consulta más eficaces.

SHARDED significa que los datos se han particionado horizontalmente en la base de datos. La clave de creación de particiones para la distribución de datos es el parámetro sharding_column_name.
REPLICATED significa que copias idénticas de la tabla están presentes en cada base de datos. Es responsabilidad suya asegurarse de que las réplicas son idénticas en las bases de datos.
ROUND_ROBIN significa que la tabla tiene particiones horizontales mediante un método de distribución que depende de la aplicación.

Permisos

Los usuarios con acceso a la tabla externa obtienen automáticamente acceso a las tablas remotas subyacentes con la credencial proporcionada en la definición del origen de datos externo. Evite la elevación no deseada de privilegios a través de la credencial del origen de datos externo. Use GRANT o REVOKE para una tabla externa como si fuera una tabla normal. Una vez que defina el origen de datos externo y las tablas externas, puede usar el T-SQL completo en las tablas externas.

Control de errores

Mientras se ejecuta la instrucción CREATE EXTERNAL TABLE, 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 SQL Database reintenta la conexión antes de que se produzca un error en la consulta.

Comentarios

En escenarios de consulta "ad hoc", como SELECT FROM EXTERNAL TABLE, SQL Database almacena en una tabla temporal las filas que se recuperan del origen de datos externo. Una vez que ha finalizado la consulta, SQL Database 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, SQL Database 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 SQL Database recupera los datos externos.

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

Limitaciones y restricciones

El acceso a los datos a través de una tabla externa no se adhiere a la semántica de aislamiento en SQL Server. Esto significa que la consulta de un origen externo no impone ningún aislamiento de instantánea o bloqueo, por lo que los datos devueltos pueden cambiar si los datos del origen de datos externo cambian. 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.

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

CREATE TABLE y DROP TABLE.
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
Enmascaramiento dinámico de datos en columnas de tablas externas.
No se admiten cursores para tablas externas de Azure SQL Database.

Solo los predicados literales definidos en una consulta se pueden insertar en el origen de datos externo. Esto es diferente de los servidores vinculados y el acceso donde se pueden usar los predicados determinados durante la ejecución de la consulta, es decir, al usarlos en conjunción con un bucle anidado en un plan de consulta. Con frecuencia, la tabla externa completa se copia localmente y, después, se produce la unión.

-- Assuming External.Orders is an external table and Customer is a local table.
-- This query  will copy the whole of the external locally as the predicate needed
-- to filter isn't known at compile time. Its only known during execution of the query

SELECT Orders.OrderId, Orders.OrderTotal
FROM External.Orders
WHERE CustomerId IN (
        SELECT TOP 1 CustomerId
        FROM Customer
        WHERE CustomerName = 'MyCompany'
);

El uso de tablas externas evita el uso de paralelismo en el plan de consulta.

Las tablas externas se implementan como una consulta remota y, como tal, el número estimado de filas devueltas es generalmente 1000; hay otras reglas basadas en el tipo de predicado que se usan para filtrar la tabla externa. Son estimaciones basadas en reglas en lugar de estimaciones basadas en los datos reales de la tabla externa. El optimizador no accede al origen de datos remoto para obtener una estimación más precisa.

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

Bloqueo

Bloqueo compartido en el objeto SCHEMARESOLUTION.

Ejemplos

A. Crear tabla externa para Azure SQL Database

CREATE EXTERNAL TABLE [dbo].[CustomerInformation]
( [CustomerID] [int] NOT NULL,
  [CustomerName] [varchar](50) NOT NULL,
  [Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)

B. Crear una tabla externa para un origen de datos con particiones

En este ejemplo se vuelve a asignar una DMV remota a una tabla externa mediante las cláusulas SCHEMA_NAME y OBJECT_NAME.

CREATE EXTERNAL TABLE [dbo].[all_dm_exec_requests]([session_id] smallint NOT NULL,
  [request_id] int NOT NULL,
  [start_time] datetime NOT NULL,
  [status] nvarchar(30) NOT NULL,
  [command] nvarchar(32) NOT NULL,
  [sql_handle] varbinary(64),
  [statement_start_offset] int,
  [statement_end_offset] int,
  [cpu_time] int NOT NULL)
WITH
(
  DATA_SOURCE = MyExtSrc,
  SCHEMA_NAME = 'sys',
  OBJECT_NAME = 'dm_exec_requests',
  DISTRIBUTION=ROUND_ROBIN
);

Pasos siguientes

Obtenga más información sobre las tablas externas de Azure SQL Database en los artículos siguientes:

Azure SQL Database

* Azure Synapse
Analytics *

Introducción: Azure Synapse Analytics

Use una tabla externa para:

Los grupos de SQL dedicados pueden consultar, importar y almacenar datos de Hadoop, Azure Blob Storage y Azure Data Lake Storage Gen1 y Gen2.
Los grupos de SQL sin servidor pueden consultar, importar y almacenar datos de Azure Blob Storage, Azure Data Lake Storage Gen1 y Gen2. La opción sin servidor no admite TYPE=Hadoop.

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

Para obtener más información y ejemplos sobre el uso de tablas externas con Azure Synapse, consulte Uso de tablas externas con Synapse SQL.

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

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

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

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 ] ]
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]         
    )
[;]
<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]

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

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, solo los metadatos de tabla junto con estadísticas básicas sobre el archivo o la carpeta a los que se hace referencia en Azure Data Lake, Hadoop o Azure Blob Storage. No se mueven ni almacenan datos reales cuando se crean tablas externas.

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.

Nota:

Los tipos de datos en desuso text, ntext y XML no son tipos de datos admitidos en columnas de tablas externas de Synapse Analytics.

Al leer archivos delimitados, 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.
Al leer archivos con formato Parquet, solo puede especificar las columnas que desea leer y omitir el resto.

LOCATION = 'folder_or_filepath'

Especifica la carpeta o la ruta y el nombre de archivo de los datos reales en Azure Data Lake, Hadoop o Azure Blob Storage. 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. La instrucción CREATE EXTERNAL TABLE AS SELECT crea la ruta de acceso y la carpeta si no existen. CREATE EXTERNAL TABLE no crea la ruta de acceso y la carpeta.

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 está en una subcarpeta de una carpeta oculta. Y no devolverá _hidden.txt porque es un archivo oculto.

Diagrama de carpetas y datos de archivo para tablas externas.

A diferencia de las tablas externas de Hadoop, las tablas externas nativas no devuelven subcarpetas a menos que especifique /** al final de la ruta de acceso. En este ejemplo, si LOCATION='/webdata/', una consulta del grupo de SQL sin servidor, devolverá filas de mydata.txt. No devolverá mydata2. txt y mydata3. txt porque se encuentran en una subcarpeta. Las tablas de Hadoop devolverán todos los archivos de cualquier subcarpeta.

Tanto Hadoop como las tablas externas nativas omitirán los archivos con nombres que comiencen por un subrayado (_) o un punto (.).

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 se encuentra en Azure Data Lake. Para crear un origen de datos externo, useCREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

OPCIONES DE TABLA

Especifica el conjunto de opciones que describen cómo leer los archivos subyacentes. Actualmente, la única opción disponible es {"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}, que indica a la tabla externa que ignore las actualizaciones realizadas en los archivos subyacentes, aunque esto pueda provocar algunas operaciones de lectura incoherentes. Use esta opción solo en casos especiales en los que haya anexado archivos frecuentemente. Esta opción está disponible en el grupo de SQL sin servidor para el formato CSV.

Opciones de REJECT

Las opciones de Reject están en versión preliminar para los grupos de SQL sin servidor en Azure Synapse Analytics.

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

La opción de formato PARSER_VERSION solo se admite en grupos de SQL sin servidor.

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 de PolyBase.

Por ejemplo, si REJECT_VALUE = 5 y REJECT_TYPE = value, se producirá un error en la consulta SELECT de PolyBase 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 en la consulta de PolyBase. 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. El porcentaje solo es válido para grupos de SQL dedicados con TYPE=HADOOP.

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.

REJECT_SAMPLE_VALUE = reject_sample_value

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

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

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, se creará. 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 los grupos de SQL sin servidor, la ruta de acceso es YearMonthDay_HourMinuteSecond_StatementID. Puede usar el identificador de instrucción para correlacionar la carpeta con la consulta que la generó.
En los grupos de SQL dedicados, la ruta de acceso creada se basa en el tiempo de envío de carga en el formato YearMonthDay -HourMinuteSecond; por ejemplo 20180330-173205.

En esta carpeta, se escriben dos tipos de archivos: el archivo _reason y el archivo de datos.

Para más información, vea CREATE EXTERNAL DATA SOURCE.

En los grupos de SQL sin servidor, el archivo error.json contiene una matriz JSON con errores detectados relacionados con las filas rechazadas. Cada elemento que representa un error contiene los siguientes atributos:

Atributo	Descripción
Error	Motivo por el que se rechaza la fila.
Row	Número ordinal de la fila rechazada en el archivo.
Columna	Número ordinal de la columna rechazada.
Valor	Valor de la columna rechazada. Si el valor tiene más de cien caracteres, solo se mostrarán los cien primeros.
Archivo	Ruta de acceso al archivo al que pertenece la fila.

Permisos

Requiere estos permisos de usuario:

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT

Nota

Se necesitan permisos CONTROL DATABASE para crear solo los valores MASTER KEY, DATABASE SCOPED CREDENTIAL y EXTERNAL DATA SOURCE

Tenga en cuenta que el inicio de sesión que crea el origen de datos externo debe tener permiso para leer y escribir en el origen de datos externo, ubicado en Hadoop o Azure Blob Storage.

Importante

Control de errores

Comentarios

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

Preste atención a los datos de origen que usen la intercalación UTF-8. Para datos de origen que use la intercalación UTF-8, debe proporcionar manualmente una intercalación que no sea UTF-8 para cada columna UTF-8 de la instrucción CREATE EXTERNAL TABLE. Esto se debe a que la compatibilidad con UTF-8 no se extiende a tablas externas. Al intentar crear una tabla externa con una intercalación UTF-8, recibirá un mensaje de error Unsupported collation. Si la intercalación de base de datos de la tabla externa es una intercalación UTF-8, se producirá un error en la creación de tablas externas a menos que proporcione una intercalación de columna no UTF-8 explícita, por ejemplo, [UTF8_column] varchar(128) COLLATE LATIN1_GENERAL_100_CI_AS_KS_WS NOT NULL,.

Los grupos de SQL sin servidor y dedicados de Azure Synapse Analytics usan diferentes bases de código para la virtualización de datos. Los grupos de SQL sin servidor admiten una tecnología nativa de virtualización de datos. Los grupos de SQL dedicados admiten la virtualización de datos nativa y de PolyBase. La virtualización de datos de PolyBase se usa cuando se crea el ORIGEN DE DATOS EXTERNO con TYPE=HADOOP.

Limitaciones y restricciones

Como los datos de una tabla externa no están bajo el control de administración directo de Azure Synapse, 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.

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
Enmascaramiento dinámico de datos en columnas de tablas externas

Limitaciones de las consultas

Se recomienda no superar un máximo de 30 000 archivos por carpeta. Si hay referencias a demasiados archivos, podría producirse una excepción de memoria insuficiente de Máquina virtual Java (JVM), o el rendimiento podría verse degradado.

Limitaciones de ancho de tabla

PolyBase en Azure Data Warehouse tiene un límite de ancho de fila de 1 MB, 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 1 MB, 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

Bloqueo

Bloqueo compartido en el objeto SCHEMARESOLUTION.

Ejemplos

A. Importación de datos de ADLS Gen 2 en Azure Synapse Analytics

Para ver ejemplos de ADLS Gen 1, consulte Crear origen de datos externos.

-- These values come from your Azure Active Directory Application used to authenticate to ADLS Gen 2.
CREATE DATABASE SCOPED CREDENTIAL ADLUser
WITH IDENTITY = '<clientID>@\<OAuth2.0TokenEndPoint>',
SECRET = '<KEY>' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH (TYPE = HADOOP,
      LOCATION = 'abfss://data@pbasetr.azuredatalakestore.net'
);

CREATE EXTERNAL FILE FORMAT TextFileFormat
WITH
(
    FORMAT_TYPE = DELIMITEDTEXT
    , FORMAT_OPTIONS ( FIELD_TERMINATOR = '|'
       , STRING_DELIMITER = ''
      , DATE_FORMAT = 'yyyy-MM-dd HH:mm:ss.fff'
      , USE_TYPE_DEFAULT = FALSE
      )
);

CREATE EXTERNAL TABLE [dbo].[DimProduct_external]
( [ProductKey] [int] NOT NULL,
  [ProductLabel] nvarchar NULL,
  [ProductName] nvarchar NULL )
WITH
(
    LOCATION='/DimProduct/' ,
    DATA_SOURCE = AzureDataLakeStore ,
    FILE_FORMAT = TextFileFormat ,
    REJECT_TYPE = VALUE ,
    REJECT_VALUE = 0
);

CREATE TABLE [dbo].[DimProduct]
WITH (DISTRIBUTION = HASH([ProductKey] ) )
AS SELECT * FROM
[dbo].[DimProduct_external] ;

B. Importación de datos de Parquet en Azure Synapse Analytics

En el ejemplo siguiente se crea una tabla externa. Devuelve la primera fila:

CREATE EXTERNAL TABLE census_external_table
(
    decennialTime varchar(20),
    stateName varchar(100),
    countyName varchar(100),
    population int,
    race varchar(50),
    sex    varchar(10),
    minAge int,
    maxAge int
)  
WITH (
    LOCATION = '/parquet/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
);
GO
SELECT TOP 1 * FROM census_external_table;

Pasos siguientes

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

Azure SQL Database

* Analytics
Platform System (PDW) *

Introducción: Sistema de la plataforma de análisis

Use una tabla externa para:

Consultar datos de Hadoop o Azure Blob Storage con instrucciones de Transact-SQL.
Importar datos de Hadoop o Azure Blob Storage y almacenarlos en Analytics Platform System.

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

Sintaxis

CREATE EXTERNAL TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    ( <column_definition> [ ,...n ] )
    WITH (
        LOCATION = 'hdfs_folder_or_filepath',
        DATA_SOURCE = external_data_source_name,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ ,...n ] ]
    )
[;]

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

<reject_options> ::=
{
    | REJECT_TYPE = value | percentage,
    | REJECT_VALUE = reject_value,
    | REJECT_SAMPLE_VALUE = reject_sample_value,

}

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, Analytics Platform System 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 Analytics Platform System.

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.

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. 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 Analytics Platform System, la instrucción CREATE EXTERNAL TABLE AS SELECT crea la ruta de acceso y la carpeta si no existen. CREATE EXTERNAL TABLE no crea la ruta de acceso y la carpeta.

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 está en una subcarpeta de una carpeta oculta. Y no devolverá _hidden.txt porque es un archivo oculto.

Diagrama de carpetas y datos de archivo para tablas externas.

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 Hadoop o Azure Blob Storage. Para crear un origen de datos externo, useCREATE EXTERNAL DATA SOURCE.

FILE_FORMAT = external_file_format_name

Opciones de Reject

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

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 de PolyBase.

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

percentage

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

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

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.

Permisos

Requiere estos permisos de usuario:

CREATE TABLE
ALTER ANY SCHEMA
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
CONTROL DATABASE

Tenga en cuenta que el inicio de sesión que crea el origen de datos externo debe tener permiso para leer y escribir en el origen de datos externo, ubicado en Hadoop o Azure Blob Storage.

Importante

Control de errores

Comentarios

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

Limitaciones y restricciones

Como los datos de una tabla externa no están bajo el control de administración directo del dispositivo, 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.

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
Enmascaramiento dinámico de datos en columnas de tablas externas

Limitaciones de las consultas

Limitaciones de ancho de tabla

En Azure Synapse Analytics, esta limitación se ha elevado a 1 MB.

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

Bloqueo

Bloqueo compartido en el objeto SCHEMARESOLUTION.

Seguridad

Ejemplos

A. Combinar datos HDFS con datos de Analytics Platform System

SELECT cs.user_ip FROM ClickStream cs
JOIN [User] u ON cs.user_ip = u.user_ip
WHERE cs.url = 'www.microsoft.com';

B. Importar datos de filas de HDFS en una tabla distribuida de Analytics Platform System

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = HASH (url) )
AS SELECT url, event_date, user_ip FROM ClickStream;

C. Importar datos de filas de HDFS en una tabla replicada de Analytics Platform System

CREATE TABLE ClickStream_PDW
WITH ( DISTRIBUTION = REPLICATE )
AS SELECT url, event_date, user_ip
FROM ClickStream;

Pasos siguientes

Obtenga más información sobre las tablas externas de Analytics Platform System en los artículos siguientes:

Azure SQL Database

* Azure SQL Managed Instance *