CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Artículo
07/26/2023

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores azure Synapse Analytics Analytics Platform System (PDW)

Crea una tabla externa y, después, exporta en paralelo los resultados de una instrucción SELECT de Transact-SQL.

Azure Synapse Analytics y Analytics Platform System admiten Hadoop o Azure Blob Storage.
SQL Server 2022 (16.x) y versiones posteriores admiten CREATE EXTERNAL TABLE AS SELECT (CETAS) para crear una tabla externa y, después, exportar, en paralelo, el resultado de una instrucción SELECT de Transact-SQL a Azure Data Lake Storage (ADLS) Gen2, la cuenta de Azure Storage V2 y el almacenamiento de objetos compatible con S3.

Nota:

Las funcionalidades y la seguridad de CETAS para Azure SQL Managed Instance son diferentes de las de SQL Server o Azure Synapse Analytics. Para más información, consulte la versión de Azure SQL Managed Instance de CREATE EXTERNAL TABLE AS SELECT.

Nota:

Las funcionalidades y la seguridad de CETAS para grupos sin servidor en Azure Synapse Analytics son diferentes de las de SQL Server. Para más información, consulte CETAS con Synapse SQL.

Convenciones de sintaxis de Transact-SQL

Sintaxis

CREATE EXTERNAL TABLE { [ [ database_name . [ schema_name ] . ] | schema_name . ] table_name }
    [ (column_name [ , ...n ] ) ]
    WITH (
        LOCATION = 'hdfs_folder' | '<prefix>://<path>[:<port>]' ,
        DATA_SOURCE = external_data_source_name ,
        FILE_FORMAT = external_file_format_name
        [ , <reject_options> [ , ...n ] ]
    )
    AS <select_statement>
[;]

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

<select_statement> ::=
    [ WITH <common_table_expression> [ , ...n ] ]
    SELECT <select_criteria>

Argumentos

[ [ database_name. [ schema_name ] . ] | schema_name. ] table_name

Nombre de una, dos o tres partes de la tabla que se va a crear en la base de datos. Para una tabla externa, la base de datos relacional solo almacena los metadatos de la tabla.

[ ( column_name [ ,...n ] ) ]

El nombre de una columna de la tabla.

LOCATION

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis

'hdfs_folder'**
Especifica dónde se deben escribir los resultados de la instrucción SELECT en el origen de datos externo. La ubicación es un nombre de carpeta y puede incluir opcionalmente una ruta de acceso relativa a la carpeta raíz del clúster de Hadoop o Blob Storage. PolyBase crea la ruta de acceso y la carpeta si aún no existen.

Los archivos externos escriben en hdfs_folder y se denominan QueryID_date_time_ID.format, donde ID es un identificador incremental y format, el formato de los datos exportados. Un ejemplo es QID776_20160130_182739_0.orc.

La ubicación debe apuntar a una carpeta y tener un carácter / final; por ejemplo: aggregated_data/.

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

prefix://path[:port] proporciona el protocolo de conectividad (prefijo), la ruta de acceso y, opcionalmente, el puerto, al origen de datos externo, donde se escribirá el resultado de la instrucción SELECT.

Si el destino es un almacenamiento de objetos compatible con S3, primero debe existir un cubo, pero PolyBase puede crear subcarpetas si es necesario. SQL Server 2022 (16.x) admite Azure Data Lake Storage Gen2, la cuenta de Azure Storage V2 y el almacenamiento de objetos compatible con S3. Los archivos ORC no se admiten actualmente.

DATA_SOURCE = external_data_source_name

Especifica el nombre del objeto de origen de datos externo que contiene la ubicación en donde se almacenan (o se almacenarán) los datos externos. La ubicación es un clúster de Hadoop o una instancia de Azure Blob Storage. Para crear un origen de datos externo, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Opciones de REJECT

Las opciones de REJECT no son aplicables en el momento en que se ejecuta esta instrucción CREATE EXTERNAL TABLE AS SELECT. En su lugar, se especifican aquí para que la base de datos pueda usarlas en un momento posterior, cuando importe datos de la tabla externa. Más adelante, cuando la instrucción CREATE TABLE AS SELECT seleccione datos de la tabla externa, la base de datos usará las opciones de rechazo para determinar el número o el porcentaje de filas que pueden no importarse antes de que la importación se detenga.

REJECT_VALUE = reject_value

Especifica el valor o el porcentaje de filas que pueden no importarse antes de que la base de datos detenga la importación.
REJECT_TYPE = value | percentage

Especifica si la opción REJECT_VALUE es un valor literal o un porcentaje.
- value
  
  Se usa si REJECT_VALUE es un valor literal, no un porcentaje. La base de datos detiene la importación de filas del archivo de datos externos cuando el número de filas con errores supera el valor de reject_value.
  
  Por ejemplo, si REJECT_VALUE = 5 y REJECT_TYPE = value, la base de datos detiene la importación de filas cuando no se han podido importar cinco filas.
- percentage
  
  Se usa si REJECT_VALUE es un porcentaje, no un valor literal. La base de datos detiene la importación de filas del archivo de datos externos cuando el valor de percentage de las filas con errores supere el valor de reject_value. El porcentaje de filas con errores se calcula a intervalos. Solo es válido en grupos de SQL dedicados cuando TYPE=HADOOP.
REJECT_SAMPLE_VALUE = reject_sample_value

Necesario cuando REJECT_TYPE = percentage. Especifica el número de filas para intentar importarlas antes de que la base de datos recalcule el porcentaje de filas con errores.

Por ejemplo, si REJECT_SAMPLE_VALUE = 1000, la base de datos calculará 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, la base de datos intenta cargar otras 1000 filas. La base de datos seguirá recalculando el porcentaje de filas con errores después de intentar importar cada 1000 filas más.

Nota:

Puesto que la base de datos calcula el porcentaje de filas con errores a intervalos, el porcentaje real de filas con errores puede llegar a 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, REJECT_SAMPLE_VALUE = 100, podría darse el siguiente escenario:
- La base de datos intenta cargar 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 lo tanto, no es necesario detener la carga.
- La base de datos intenta cargar las siguientes 100 filas. Esta vez, 25 se cargan correctamente y 75 generan un error.
- 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 %.
- La carga no se efectúa y refleja un 50 % de filas con errores después de intentar cargar 200 filas, lo cual es superior al límite de 30 % especificado.

WITH common_table_expression

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE). Para más información, consulte WITH common_table_expression (Transact-SQL)

SELECT <select_criteria>

Rellena la nueva tabla con los resultados de una instrucción SELECT. select_criteria es el cuerpo de la instrucción SELECT que determina qué datos se copian en la nueva tabla. Para más información sobre las instrucciones SELECT, consulte SELECT (Transact-SQL).

Nota

La cláusula ORDER BY de SELECT no tiene ningún efecto en CETAS.

Opciones de columna

column_name [ ,...n ]

Los nombres de columna no admiten las opciones de columna mencionadas en CREATE TABLE. En su lugar, puede proporcionar una lista opcional de uno o más nombres de columna para la nueva tabla. Las columnas de la nueva tabla usan los nombres que especifique. Al especificar nombres de columna, el número de columnas de la lista de columnas debe coincidir con el número de columnas de los resultados de selección. Si no especifica nombres de columna, la nueva tabla de destino usa los nombres de columna de los resultados de la instrucción de selección.

No se pueden especificar otras opciones de columna, como tipos de datos, intercalación o la nulabilidad. Cada uno de estos atributos se deriva de los resultados de la instrucción SELECT. Aun así, puede usar la instrucción SELECT para cambiar los atributos. Para obtener un ejemplo, vea Uso de CETAS para cambiar los atributos de columna.

Permisos

Para ejecutar este comando, el usuario de base de datos necesita todos estos permisos o pertenencias a grupos:

Permiso ALTER SCHEMA en el esquema local que va a contener la nueva tabla o pertenencia al rol fijo de base de datos db_ddladmin.
Permiso CREATE TABLE o pertenencia al rol fijo de base de datos db_ddladmin.
Permiso SELECT en cualquier objeto al que se haga referencia en select_criteria.

El inicio de sesión necesita todos estos permisos:

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT
En general, debe tener permisos para mostrar el contenido de la carpeta y escribir en la carpeta LOCATION de CETAS.
En Azure Synapse Analytics y Analytics Platform System, el permiso de escritura para leer y escribir en la carpeta externa en el clúster de Hadoop o Azure Blob Storage.
En SQL Server 2022 (16.x), también es necesario establecer los permisos adecuados en la ubicación externa. Permiso de escritura para generar los datos en la ubicación y permiso de lectura para acceder a ellos.
En Azure Blob Storage y Azure Data Lake Gen2, se debe conceder al token SHARED ACCESS SIGNATURE los siguientes privilegios en el contenedor: Lectura, Escritura, Enumerar y Crear.
En Azure Blog Storage, la casilla Allowed Services: Blob debe estar activada para generar el token de SAS.
En Azure Data Lake Gen2, las casillas Allowed Services: Container y Object deben estar activadas para generar el token de SAS.

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 y solo debe concederse a las entidades de seguridad de confianza del sistema.

Control de errores

Cuando CREATE EXTERNAL TABLE AS SELECT exporta los datos a un archivo delimitado de texto, no hay ningún archivo de rechazo para las filas que no se pueden exportar.

Al crear la tabla externa, la base de datos intenta conectarse a la ubicación externa. Si se produce un error en la conexión, el comando genera un error y no se crea la tabla externa. El comando puede tardar un minuto (o más) en producir un error, ya que la base de datos intenta conectar al menos en tres ocasiones.

Si CREATE EXTERNAL TABLE AS SELECT se cancela o produce un error, la base de datos realiza un único intento para quitar los nuevos archivos y carpetas que ya se hayan creado en el origen de datos externo.

En Azure Synapse Analytics y Analytics Platform System, la base de datos notifica los errores de Java que se producen en el origen de datos externo durante la exportación de datos.

Comentarios

Una vez finalizada la instrucción CREATE EXTERNAL TABLE AS SELECT, se pueden ejecutar consultas de Transact-SQL en la tabla externa. Estas operaciones importan datos a la base de datos el tiempo que dure la consulta, a menos que la importación se realice con la instrucción CREATE TABLE AS SELECT.

La definición y el nombre de tabla externa se almacenan en los metadatos de la base de datos. Los datos se almacenan en el origen de datos externo.

La instrucción CREATE EXTERNAL TABLE AS SELECT siempre crea una tabla sin particiones, incluso si la tabla de origen tiene particiones.

En SQL Server 2022 (16.x), la opción allow polybase export debe habilitarse mediante sp_configure. Para obtener más información, consulte Establecimiento de la opción de configuraciónallow polybase export.

En el caso de los planes de consulta de Azure Synapse Analytics y Analytics Platform System que se han creado con EXPLAIN, la base de datos usa estas operaciones de plan de consulta para tablas externas: movimiento aleatorio externo, movimiento de difusión externo, movimiento de partición externa.

En Analytics Platform System, como requisito previo para crear una tabla externa, el administrador del dispositivo debe configurar la conectividad de Hadoop. Para más información, vea la sección sobre cómo configurar la conectividad a datos externos (Analytics Platform System) en la documentación de Analytics Platform System, que puede descargar desde el Centro de descarga de Microsoft.

Limitaciones y restricciones

Dado que los datos de la tabla externa están fuera de la base de datos, las operaciones de copia de seguridad y restauración solo funcionarán con los datos almacenados en la base de datos. Como consecuencia, solo se hará una copia de seguridad y una restauración de los metadatos.

La base de datos no comprueba la conexión al origen de datos externo al restaurar una copia de seguridad de base de datos que contiene una tabla externa. Si el origen inicial no está accesible, la restauración de metadatos de la tabla externa seguirá realizándose correctamente, pero no se podrán llevar a cabo operaciones SELECT en la tabla externa.

La base de datos no garantiza la coherencia de los datos entre la base de datos y los datos externos. Usted, como cliente, es el único responsable de mantener la coherencia entre los datos externos y la base de datos.

No se admiten operaciones de lenguaje de manipulación de datos (DML) en las tablas externas. Por ejemplo, no se pueden usar las instrucciones UPDATE, INSERT o DELETE de Transact-SQL para modificar los datos externos.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW y DROP VIEW son las únicas operaciones de lenguaje de manipulación de datos (DDL) que se permiten en las tablas externas.

Limitaciones y restricciones de Azure Synapse Analytics

En los grupos de SQL dedicados de Azure Synapse Analytics y en Analytics Platform System, PolyBase puede consumir un máximo de 33 000 archivos por carpeta al ejecutar 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 que los usuarios de Hadoop y PolyBase mantengan unas rutas de acceso de archivo cortas y no usen 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 JVM.
En grupos de SQL sin servidor, no se pueden crear tablas externas en una ubicación donde actualmente tiene datos. A fin de volver a usar una ubicación que se ha utilizado para almacenar datos, la ubicación debe eliminarse manualmente en ADLS. Para conocer más limitaciones y procedimientos recomendados, consulte Procedimientos recomendados de optimización de filtros.

En los grupos de SQL dedicados de Azure Synapse Analytics y en Analytics Platform System, cuando CREATE EXTERNAL TABLE AS SELECT realiza una selección desde un RCFile, los valores de columna del RCFile no deben contener el carácter (|) de canalización.

SET ROWCOUNT (Transact-SQL) no tiene ningún efecto en CREATE EXTERNAL TABLE AS SELECT. Para lograr un comportamiento similar, use TOP (Transact-SQL).

Revise Nomenclatura y referencia a contenedores, blobs y metadatos para conocer las limitaciones de los nombres de archivo.

Errores de caracteres

Los caracteres siguientes presentes en los datos pueden provocar errores, incluidos los registros rechazados con CREATE EXTERNAL TABLE AS SELECT en archivos Parquet.

En Azure Synapse Analytics y Analytics Platform System, esto también se aplica a los archivos ORC.

|
" (carácter de comillas)
\r\n
\r
\n

Para usar CREATE EXTERNAL TABLE AS SELECT con estos caracteres, primero debe ejecutar esta instrucción para exportar los datos a archivos de texto delimitados donde puede convertirlos en archivos ORC o de Parquet mediante una herramienta externa.

Trabajar con parquet

Al trabajar con archivos parquet, CREATE EXTERNAL TABLE AS SELECT generará un archivo parquet por CPU disponible, hasta el grado máximo de paralelismo configurado (MAXDOP). Cada archivo puede crecer hasta 190 GB, después de que SQL Server genere más archivos Parquet según sea necesario.

La sugerencia OPTION (MAXDOP n) de consulta solo afectará a la parte SELECT de CREATE EXTERNAL TABLE AS SELECT, no tiene ninguna influencia en la cantidad de archivos parquet. Solo se considera MAXDOP de nivel de base de datos y MAXDOP de nivel de instancia.

Bloqueo

Toma un bloqueo compartido en el objeto SCHEMARESOLUTION.

Supported data types (Tipos de datos admitidos)

CETAS se puede usar para almacenar conjuntos de resultados con los siguientes tipos de datos de SQL:

binary
varbinary
char
varchar
nchar
NVARCHAR
smalldate
date
datetime
datetime2
datetimeoffset
time
Decimal
NUMERIC
FLOAT
real
bigint
TINYINT
SMALLINT
int
bigint
bit
money
smallmoney

Ejemplos

A. Crear una tabla de Hadoop con CREATE EXTERNAL TABLE AS SELECT

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis

En el siguiente ejemplo se crea una tabla externa denominada hdfsCustomer, usando para ello los datos y las definiciones de columna de la tabla de origen dimCustomer.

La definición de tabla se almacena en la base de datos y los resultados de la instrucción SELECT se exportan al archivo /pdwdata/customer.tbl en el origen de datos externo de Hadoop customer_ds. El archivo tiene un formato acorde al formato de archivo externo customer_ff.

La base de datos genera el nombre de archivo, que contiene el identificador de la consulta para que sea más fácil establecer una correlación entre el archivo y la consulta que lo generó.

La ruta de acceso hdfs://xxx.xxx.xxx.xxx:5000/files/ que precede al directorio Customer ya debe existir. Si no existe el directorio de cliente, la base de datos lo creará.

Nota:

En este ejemplo se especifica el puerto 5000. Si el puerto no se especifica, la base de datos usará 8020 como puerto predeterminado.

El nombre de archivo y ubicación de Hadoop resultantes serán hdfs:// xxx.xxx.xxx.xxx:5000/files/Customer/ QueryID_YearMonthDay_HourMinutesSeconds_FileIndex.txt..

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE hdfsCustomer
    WITH (
            LOCATION = '/pdwdata/customer.tbl',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT *
FROM dimCustomer;
GO

B. Usar una sugerencia de consulta con CREATE EXTERNAL TABLE AS SELECT

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis

Esta consulta muestra la sintaxis básica para usar una sugerencia de combinación de consulta con la instrucción CREATE EXTERNAL TABLE AS SELECT. Después de enviar la consulta, la base de datos usa la estrategia de combinación hash para generar el plan de consulta. Para más información sobre las sugerencias de combinación y cómo usar la cláusula OPTION, consulte Cláusula OPTION (Transact-SQL).

Nota:

En este ejemplo se especifica el puerto 5000. Si el puerto no se especifica, la base de datos usará 8020 como puerto predeterminado.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.*
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

C. Uso de CETAS para cambiar los atributos de columna

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis

En este ejemplo se usa CETAS para cambiar los tipos de datos, la nulabilidad y la intercalación de varias columnas en la tabla FactInternetSales.

-- Example is based on AdventureWorks
CREATE EXTERNAL TABLE dbo.FactInternetSalesNew
    WITH (
            LOCATION = '/files/Customer',
            DATA_SOURCE = customer_ds,
            FILE_FORMAT = customer_ff
            ) AS

SELECT T1.ProductKey AS ProductKeyNoChange,
    T1.OrderDateKey AS OrderDate,
    T1.ShipDateKey AS ShipDate,
    T1.CustomerKey AS CustomerKeyNoChange,
    T1.OrderQuantity AS Quantity,
    T1.SalesAmount AS MONEY
FROM dbo.FactInternetSales T1
INNER JOIN dbo.DimCustomer T2
    ON (T1.CustomerKey = T2.CustomerKey)
OPTION (HASH JOIN);
GO

D. Uso de CREATE EXTERNAL TABLE AS SELECT al exportar datos como parquet

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

En el siguiente ejemplo se crea una tabla externa denominada ext_sales, usando para ello los datos de columna de la tabla SalesOrderDetail de AdventureWorks2022. La opción de configuración para permitir exportación de PolyBase debe estar habilitada.

El resultado de la instrucción SELECT se guardará en un almacenamiento de objetos compatible con S3 configurado previamente y denominado s3_eds, y se crearán las credenciales adecuadas como s3_dsc. La ubicación del archivo parquet será <ip>:<port>/cetas/sales.parquet donde cetas es el depósito de almacenamiento creado anteriormente.

Nota

El formato Delta solo se admite actualmente como de solo lectura.

-- Credential to access the S3-compatible object storage
CREATE DATABASE SCOPED CREDENTIAL s3_dsc
    WITH IDENTITY = 'S3 Access Key',
        SECRET = '<accesskeyid>:<secretkeyid>'
GO

-- S3-compatible object storage data source
CREATE EXTERNAL DATA SOURCE s3_eds
    WITH (
            LOCATION = 's3://<ip>:<port>',
            CREDENTIAL = s3_dsc
            )

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE ext_sales
    WITH (
            LOCATION = '/cetas/sales.parquet',
            DATA_SOURCE = s3_eds,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM AdventureWorks2022.[Sales].[SalesOrderDetail];
GO

E. Uso de CREATE EXTERNAL TABLE AS SELECT de la tabla delta a parquet

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

En el ejemplo siguiente se crea una nueva tabla externa denominada Delta_to_Parquet, que usa el tipo de la tabla Delta de los datos ubicados en un almacenamiento de objetos de compatible con S3 denominado s3_delta, y escribe el resultado en otro origen de datos denominado s3_parquet como un archivo parquet. Para ello, el ejemplo usa el comando OPENROWSET. La opción de configuración para permitir exportación de PolyBase debe estar habilitada.

-- External File Format for PARQUET
CREATE EXTERNAL FILE FORMAT ParquetFileFormat
    WITH (FORMAT_TYPE = PARQUET);
GO

CREATE EXTERNAL TABLE Delta_to_Parquet
    WITH (
            LOCATION = '/backup/sales.parquet',
            DATA_SOURCE = s3_parquet,
            FILE_FORMAT = ParquetFileFormat
            ) AS

SELECT *
FROM OPENROWSET(BULK '/delta/sales_fy22/', FORMAT = 'DELTA', DATA_SOURCE = 's3_delta') AS [r];
GO

F. Usar CREATE EXTERNAL TABLE AS SELECT con una vista como origen

Se aplica a: grupos de SQL dedicados y grupos de SQL sin servidor de Azure Synapse Analytics.

En este ejemplo, podemos ver un ejemplo de código de plantilla para escribir CETAS con una vista definida por el usuario como origen, mediante la identidad administrada como autenticación y wasbs:.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

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

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'wasbs://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

G. Usar CREATE EXTERNAL TABLE AS SELECT con una vista como origen

Se aplica a: grupos de SQL dedicados y grupos de SQL sin servidor de Azure Synapse Analytics.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

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

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SynapseSQLwriteable] WITH (
    LOCATION = 'https://<mystoageaccount>.dfs.core.windows.net/<mycontainer>/<mybaseoutputfolderpath>',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SynapseSQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

Pasos siguientes

Se aplica a: Azure SQL Instancia administrada

Crea una tabla externa y, después, exporta en paralelo los resultados de una instrucción SELECT de Transact-SQL.

Puede usar la instrucción CREATE EXTERNAL TABLE AS SELECT (CETAS) para completar las siguientes tareas:

Crear una tabla externa a partir de archivos parquet o CSV en Azure Blob Storage o Azure Data Lake Storage (ADLS) Gen2.
Exportar, en paralelo, los resultados de una instrucción SELECT de T-SQL en la tabla externa creada.
Para ver más funcionalidades de virtualización de datos de Azure SQL Managed Instance, consulte Virtualización de datos con Azure SQL Managed Instance.

Nota

Este contenido solo se aplica a Azure SQL Managed Instance. Con otras plataformas, elija la versión adecuada de CREATE EXTERNAL TABLE AS SELECT en el selector desplegable.

Convenciones de sintaxis de Transact-SQL

Sintaxis

CREATE EXTERNAL TABLE [ [database_name  . [ schema_name ] . ] | schema_name . ] table_name
    WITH (
        LOCATION = 'path_to_folder/',  
        DATA_SOURCE = external_data_source_name,  
        FILE_FORMAT = external_file_format_name
        [, PARTITION ( column_name [ , ...n ] ) ]
)
    AS <select_statement>  
[;]

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT <select_criteria>

Argumentos

[ [ database_name. [ schema_name ] . ] | schema_name. ] table_name

Nombre de entre una y tres partes de la tabla que se va a crear. En el caso de una tabla externa, solo se almacenan los metadatos de la tabla. Ningún dato real se mueve ni se almacena.

LOCATION = 'path_to_folder'

Especifica dónde se deben escribir los resultados de la instrucción SELECT en el origen de datos externo. La carpeta raíz es la ubicación de datos especificada en el origen de datos externo. LOCATION debe apuntar a una carpeta y tener un carácter / final. Ejemplo: aggregated_data/.

La carpeta de destino de CETAS debe estar vacía. Si la ruta de acceso y la carpeta aún no existen, se crean automáticamente.

DATA_SOURCE = external_data_source_name

Especifica el nombre del objeto de origen de datos externo que contiene la ubicación en donde se almacenarán los datos externos. Para crear un origen de datos externo, use CREATE EXTERNAL DATA SOURCE (Transact-SQL).

FILE_FORMAT = external_file_format_name

Especifica el nombre del objeto de formato de archivo externo que contiene el formato del archivo de datos externos. Para crear un formato de archivo externo, use CREATE EXTERNAL FILE FORMAT (Transact-SQL). Actualmente solo se admiten formatos de archivos externos con FORMAT_TYPE=PARQUET y FORMAT_TYPE=DELIMITEDTEXT. No se admite la compresión GZip para el formato DELIMITEDTEXT.

[, PARTITION ( nombre de columna [ , ...n ] ) ]

Crea particiones de los datos de salida en varias rutas de acceso de archivo parquet. La partición se realiza por columnas dadas (column_name), haciendo coincidir los comodines (*) de LOCATION con la respectiva columna de partición. El número de columnas de la parte PARTITION debe coincidir con el número de caracteres comodín de LOCATION. Debe haber al menos una columna que no se use para la creación de particiones.

WITH <common_table_expression>

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE). Para más información, consulte WITH common_table_expression (Transact-SQL).

SELECT <select_criteria>

Nota

La cláusula ORDER BY en SELECT no se admite en CETAS.

Permisos

Permisos en el almacenamiento

Es necesario tener permisos para mostrar el contenido de la carpeta y escribir en la ruta de acceso de LOCATION para que CETAS funcione.

Los métodos de autenticación admitidos son identidad administrada o un token de firma de acceso compartido (SAS).

Si usa la identidad administrada para la autenticación, asegúrese de que la entidad de servicio de la instancia administrada de SQL tiene un rol de Colaborador de datos de Storage Blob en el contenedor de destino.
Si usa un token de SAS, se requieren permisos de Lectura, Escritura y Enumeración.
En Azure Blog Storage, la casilla Allowed Services: Blob debe estar activada para generar el token de SAS.
En Azure Data Lake Gen2, las casillas Allowed Services: Container y Object deben estar activadas para generar el token de SAS.

No se admite una identidad administrada asignada por el usuario. No se admite la autenticación de paso a través de Microsoft Entra. Microsoft Entra ID es (anteriormente Azure Active Directory).

Permisos en la instancia administrada de SQL

Para ejecutar este comando, el usuario de base de datos necesita todos estos permisos o pertenencias a grupos:

Permiso ALTER SCHEMA en el esquema local que va a contener la nueva tabla o pertenencia al rol fijo de base de datos db_ddladmin.
Permiso CREATE TABLE o pertenencia al rol fijo de base de datos db_ddladmin.
Permiso SELECT en cualquier objeto al que se haga referencia en select_criteria.

El inicio de sesión necesita todos estos permisos:

ADMINISTER BULK OPERATIONS
ALTER ANY EXTERNAL DATA SOURCE
ALTER ANY EXTERNAL FILE FORMAT

Importante

Tipos de datos admitidos

CETAS almacena conjuntos de resultados con los siguientes tipos de datos de SQL:

binary
varbinary
char
varchar
nchar
NVARCHAR
smalldatetime
date
datetime
datetime2
datetimeoffset
time
Decimal
NUMERIC
FLOAT
real
bigint
TINYINT
SMALLINT
int
bigint
bit
money
smallmoney

Nota:

Los objetos grandes mayores de 1 MB no se pueden usar con CETAS.

Limitaciones y restricciones

CREATE EXTERNAL TABLE AS SELECT (CETAS) para Azure SQL Managed Instance está deshabilitado de forma predeterminada. Para más información, consulte la sección siguiente Deshabilitado de forma predeterminada.
Para más información sobre las limitaciones o los problemas conocidos con la virtualización de datos en Azure SQL Managed Instance, consulte Limitaciones y problemas conocidos.

La base de datos no comprueba la conexión al origen de datos externo al restaurar una copia de seguridad de base de datos que contiene una tabla externa. Si no se puede acceder al origen, la restauración de metadatos de la tabla externa sigue realizándose correctamente, pero no se pueden llevar a cabo operaciones SELECT en la tabla externa.

No se admiten operaciones de lenguaje de manipulación de datos (DML) en las tablas externas. Por ejemplo, no se pueden usar las instrucciones de actualización, inserción o eliminación de Transact-SQL para modificar los datos externos.

CREATE TABLE, DROP TABLE, CREATE STATISTICS, DROP STATISTICS, CREATE VIEW y DROP VIEW son las únicas operaciones de lenguaje de manipulación de datos (DDL) que se permiten en las tablas externas.

No se pueden crear tablas externas en una ubicación donde actualmente se tienen datos. A fin de volver a usar una ubicación que se ha utilizado para almacenar datos, la ubicación debe eliminarse manualmente en ADLS.

SET ROWCOUNT (Transact-SQL) no tiene ningún efecto en CREATE EXTERNAL TABLE AS SELECT. Para lograr un comportamiento similar, use TOP (Transact-SQL).

Revise Nomenclatura y referencia a contenedores, blobs y metadatos para conocer las limitaciones de los nombres de archivo.

Tipos de almacenamiento

Los archivos se pueden almacenar en Azure Data Lake Storage Gen2 o Azure Blob Storage. Para consultar archivos, debe proporcionar la ubicación en un formato específico y usar el prefijo de tipo de ubicación correspondiente al tipo de origen externo y punto de conexión o protocolo, como los ejemplos siguientes:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

--Data Lake endpoint
adls://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet

Importante

El prefijo de tipo de ubicación proporcionado se usa para elegir el protocolo óptimo para la comunicación y aprovechar las funcionalidades avanzadas que ofrece el tipo de almacenamiento determinado. El uso del prefijo https:// genérico está deshabilitado. Use siempre prefijos específicos del punto de conexión.

Deshabilitado de forma predeterminada

CREATE EXTERNAL TABLE AS SELECT (CETAS) le permite exportar datos de la instancia administrada de SQL a una cuenta de almacenamiento externa, por lo que existe la posibilidad de riesgo de filtración de datos con estas funcionalidades. Por lo tanto, CETAS está deshabilitado de forma predeterminada para Azure SQL Managed Instance.

Habilitación de CETAS

CETAS para Azure SQL Managed Instance solo se puede habilitar a través de un método que requiere permisos elevados de Azure; no se puede habilitar a través de T-SQL. Debido al riesgo de filtración de datos no autorizados, CETAS no se puede habilitar a través del procedimiento almacenado de T-SQL sp_configure, sino que requiere que la acción del usuario esté fuera de la instancia administrada de SQL.

Permisos para habilitar CETAS

Para habilitar a través de Azure PowerShell, el usuario que ejecuta el comando debe tener roles de Azure RBAC de Colaborador o Administrador de seguridad de SQL para la instancia administrada de SQL.

También se puede crear un rol personalizado para ello, lo que requiere la acción de Lectura y Escritura para la acción Microsoft.Sql/managedInstances/serverConfigurationOptions.

Métodos para habilitar CETAS

Para invocar los comandos de PowerShell en un equipo, el paquete Az versión 9.7.0 o posterior debe estar instalado localmente. O bien, considere la posibilidad de usar Azure Cloud Shell para ejecutar Azure PowerShell en shell.azure.com.

En primer lugar, inicie sesión en Azure y establezca el contexto adecuado para la suscripción:

Login-AzAccount
$SubscriptionID = "<YourSubscriptionIDHERE>"
Select-AzSubscription -SubscriptionName $SubscriptionID

Para administrar la opción de configuración del servidor "allowPolybaseExport", ajuste los siguientes scripts de PowerShell a la suscripción y al nombre de la instancia administrada de SQL y ejecute los comandos. Para más información, consulte Set-AzSqlServerConfigurationOption y Get-AzSqlServerConfigurationOption.

# Enable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 1

Para deshabilitar la opción de configuración del servidor "allowPolybaseExport":

# Disable ServerConfigurationOption with name "allowPolybaseExport"
Set-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport" -Value 0

Para obtener el valor actual de la opción de configuración del servidor "allowPolybaseExport":

# Get ServerConfigurationOptions with name "allowPolybaseExport"
Get-AzSqlServerConfigurationOption -ResourceGroupName "resource_group_name" -InstanceName "ManagedInstanceName" `
-Name "allowPolybaseExport"

Puede usar la CLI de Azure para administrar la opción de configuración del servidor "allowPolybaseExport".

En primer lugar, abra un símbolo del sistema de Windows (CMD) o PowerShell e inicie sesión en Azure. Si es necesario, cambie a su suscripción.

az login
$subscriptionId = "<your subscription ID>"
az account set --subscription $subscriptionId

Use az sql mi server-configuration-option set para habilitar la opción de configuración del servidor con el nombre "allowPolybaseExport":

# Enable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '1'

Para deshabilitar la opción de configuración del servidor "allowPolybaseExport":

# Disable server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option set --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport' --value '0'

Para obtener el valor actual de la opción de configuración del servidor "allowPolybaseExport":

# Get server-configuration-option with name "allowPolybaseExport"
az sql mi server-configuration-option show --resource-group 'resource_group_name' --managed-instance-name 'ManagedInstanceName' \
    --name 'allowPolybaseExport'

Alias de argumento de la CLI de Azure:

Versión corta	Alias
-g	--resource-group
--mi	--instance-name --managed-instance --managed-instance-name
-n	--name --server-configuration-option-name
--value	--server-configuration-option-value

Puede invocar un comando de API genérico a través de Azure Cloud Shell para cambiar la opción de configuración del servidor allowPolybaseExport por 1.

Para habilitar CETAS en la instancia administrada de SQL, ajuste las siguientes variables de script de Azure PowerShell a la suscripción y al nombre de la instancia administrada de SQL y ejecútelo en Azure Cloud Shell.

Nota:

En los siguientes pasos de Azure Cloud Shell relacionados con la configuración o comprobación de CETAS, use la misma consola de PowerShell en todo momento. Los scripts posteriores para comprobar el estado dependen del contexto establecido por el script original.

# Enter your Azure subscription ID
$SubscriptionID = "<YourSubscriptionIDHERE>"

# Enter your managed instance name – for example, "sqlmi1"
$ManagedInstanceName = "<YourManagedInstanceNameHere>"

# ===============================================================================
# INVOKING THE API CALL -- REST OF THE SCRIPT IS NOT USER CONFIGURABLE
# ===============================================================================
# Log in and select a subscription if needed.

if ((Get-AzContext ) -eq $null)
{
    echo "Logging to Azure subscription"
    Login-AzAccount
}
Select-AzSubscription -SubscriptionName $SubscriptionID

# Build the URI for the API call.
$resource_group_name = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName
$uriFull = "https://management.azure.com/subscriptions/" + $SubscriptionID + "/resourceGroups/" + $resource_group_name+ "/providers/Microsoft.Sql/managedInstances/" + $ManagedInstanceName + "/serverConfigurationOptions/allowPolybaseExport" + "?api-version=2022-08-01-preview"

# Build the API request body.
$properties = @{serverConfigurationOptionValue = 1}
$bodyFull = @{properties = $properties}
$jsonBody = $bodyFull | ConvertTo-Json

# Get auth token and build the HTTP request header.
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$currentAzureContext = Get-AzContext
$profileClient = New-Object Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient($azProfile)
$token = $profileClient.AcquireAccessToken($currentAzureContext.Tenant.TenantId)
$authToken = $token.AccessToken
$headers = @{}
$headers.Add("Authorization", "Bearer "+"$authToken")

# Invoke API call. API call creates an Asynchronous operation that will change the configuration setting. 
# In rare cases that your SQL MI is down when the async operation will run, the async operation may fail.
# You can check the status of the Async operation using the callback URL in the response header.
# Look for response header parameter: Azure-AsyncOperation.
Invoke-WebRequest -Method PUT -Headers $headers -Uri $uriFull -ContentType "application/json" -Body $jsonBody

Nota:

Establecer la opción de configuración del servidor mediante la invocación de la API es una operación asincrónica. Se espera que la operación asincrónica se ejecute rápidamente y se complete en cuestión de segundos, pero su ejecución puede producir un error por diversos motivos, por ejemplo, si la instancia administrada de SQL estaba inactiva en el momento de ejecutarse la operación. Por lo tanto, es importante comprobar el resultado del cambio en la opción de configuración.

Para comprobar el resultado de la operación asincrónica de la llamada API, hay dos métodos:

Importante

Al ejecutar los scripts siguientes, use la misma consola de Azure Cloud Shell donde ejecutó el script para establecer la opción de configuración. Este script depende del contexto establecido por el script original Invoke-WebRequest.

Use PowerShell para invocar HTTP GET hacia la API de configuración del servidor y compruebe si la opción de configuración contiene el valor que desee. El siguiente script de PowerShell muestra cómo invocar una solicitud GET para comprobar la opción de configuración del servidor. Busque el código JSON de respuesta y busque serverConfigurationOptionValue para asegurarse de que corresponde a lo que ha establecido en el paso anterior. Use la misma consola de PowerShell en la que ejecutó el script para establecer la opción de configuración, ya que el script depende del contexto establecido por el script original.
```
# You can query the configuration option directly to see if it contains the new changed value.
# Get the configuration status using the call below. This will return JSON with 
# serverConfigurationOptionValue variable value. 
# It should match what you provided in your original request:
Invoke-WebRequest -Method GET -Headers $headers -Uri $uriFull -ContentType "application/json" 
```

Use PowerShell para invocar HTTP GET hacia la dirección URL de devolución de llamada de la operación asincrónica y lea la respuesta JSON que busca la variable "status" y su valor. Al invocar la API para realizar el cambio de configuración del servidor, el encabezado de respuesta contendrá una dirección URL de devolución de llamada, que puede usar para comprobar el resultado y el estado de la operación asincrónica. El valor Azure-AsyncOperation del encabezado contiene la dirección URL de devolución de llamada.

Este es un encabezado de respuesta de ejemplo:

[Azure-AsyncOperation, https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview]

En el siguiente ejemplo de PowerShell se usa la dirección URL de devolución de llamada del encabezado de respuesta, que empieza por https://:

# If you got the Azure-AsyncOperation URL from response header, 
# use the below to query the operation for status:
$AzAsyncOpURL = "https://management.azure.com/subscriptions/abcdefgh-abcd-abcd-abcd-subscription/resourceGroups/<ResourceGroupName>/providers/Microsoft.Sql/locations/eastus/serverConfigurationOptionAzureAsyncOperation/abcdefgh-abcd-abcd-abcd-abcdef123456?api-version=2022-08-01-preview"
Invoke-WebRequest -Method GET -Headers $headers -Uri $AzAsyncOpURL -ContentType "application/json"

Comprobación del estado de CETAS

En cualquier momento puede comprobar el estado actual de la opción de configuración CETAS.

Conéctese a la instancia administrada de SQL. Ejecute la siguiente instrucción T-SQL y observe la columna value de la respuesta. Una vez completado el cambio de configuración del servidor, los resultados de esta consulta deben coincidir con la configuración deseada.

SELECT [name], [value] FROM sys.configurations WHERE name ='allow polybase export';

Solución de problemas

Para ver más pasos de solución de problemas de virtualización de datos en Azure SQL Managed Instance, consulte Solución de problemas. A continuación, se indican el control de errores y los mensajes de error comunes para CETAS en Azure SQL Managed Instance.

Control de errores

Cuando CREATE EXTERNAL TABLE AS SELECT exporta los datos a un archivo delimitado de texto, no hay ningún archivo de rechazo para las filas que no se pueden exportar.

Al crear la tabla externa, la base de datos intenta conectarse a la ubicación externa. Si se produce un error en la conexión, el comando genera un error y no se creará la tabla externa. El comando puede tardar un minuto (o más) en producir un error, ya que la base de datos intenta conectar al menos en tres ocasiones.

Mensajes comunes de error

Estos mensajes de error comunes tienen explicaciones rápidas de CETAS para Azure SQL Managed Instance.

Se ha especificado una ubicación que ya existe en el almacenamiento.

Solución: borre la ubicación de almacenamiento (incluida la instantánea) o cambie el parámetro de ubicación en la consulta.

Ejemplo de mensaje de error: Msg 15842: Cannot create external table. External table location already exists.
Valores de columna a los que se les ha aplicado formato mediante objetos JSON.

Solución: convierta la columna de valor en una sola columna VARCHAR o NVARCHAR, o un conjunto de columnas con tipos definidos explícitamente.

Ejemplo de mensaje de error: Msg 16549: Values in column value are formatted as JSON objects and cannot be written using CREATE EXTERNAL TABLE AS SELECT.
Parámetro de ubicación no válido (por ejemplo, varios valores //).

Solución: corrija el parámetro de ubicación.

Ejemplo de mensaje de error: Msg 46504: External option 'LOCATION' is not valid. Ensure that the length and range are appropriate.
Falta una de las opciones necesarias (DATA_SOURCE, FILE_FORMAT, LOCATION).

Solución: agregue el parámetro que falta a la consulta CETAS.

Ejemplo de mensaje de error: Msg 46505: Missing required external DDL option 'FILE_FORMAT'
Problemas de acceso (credencial no válida, credencial expirada o credencial con permisos insuficientes). La posibilidad alternativa es una ruta de acceso no válida, donde la instancia administrada de SQL haya recibido un error 404 del almacenamiento.

Solución: compruebe la validez de las credenciales y los permisos. Como alternativa, valide que la ruta de acceso sea válida y que haya almacenamiento. Use la ruta de acceso URL adls://<container>@<storage_account>.blob.core.windows.net/<path>/.

Ejemplo de mensaje de error: Msg 15883: Access check for '<Read/Write>' operation against '<Storage>' failed with HRESULT = '0x...'
La parte de ubicación de DATA_SOURCE contiene caracteres comodín.

Solución: quite los caracteres comodín de la ubicación.

Ejemplo de mensaje de error: Msg 16576: Location provided by DATA_SOURCE '<data source name>' cannot contain any wildcards.
El número de caracteres comodín del parámetro LOCATION y el número de columnas con particiones no coinciden.

Solución: asegúrese de que haya tantos caracteres comodín en LOCATION como en las columnas de partición.

Ejemplo de mensaje de error: Msg 16577: Number of wildcards in LOCATION must match the number of columns in PARTITION clause.
El nombre de columna de la cláusula PARTITION no coincide con ninguna columna de la lista.

Solución: asegúrese de que las columnas de PARTITION sean válidas.

Ejemplo de mensaje de error: Msg 16578: The column name '<column name>' specified in the PARTITION option does not match any column specified in the column list
Columna especificada más de una vez en la lista PARTITION.

Solución: asegúrese de que las columnas de la cláusula PARTITION sean únicas.

Ejemplo de mensaje de error: Msg 16579: A column has been specified more than once in the PARTITION list. Column '<column name>' is specified more than once.
La columna se especificó más de una vez en la lista PARTITION o no coincide con ninguna columna de la lista SELECT.

Solución: asegúrese de que no haya duplicados en la lista de particiones y de que las columnas de partición existan en la parte SELECT.

Ejemplo de mensajes de error: Msg 11569: Column <column name> has been specified more than once in the partition columns list. Please try again with a valid parameter. o Msg 11570: Column <column name> specified in the partition columns list does not match any columns in SELECT clause. Please try again with a valid parameter.
Uso de todas las columnas de la lista PARTITION.

Solución: al menos una de las columnas de la parte SELECT no debe estar en la parte PARTITION de la consulta.

Ejemplo de mensaje de error: Msg 11571: All output columns in DATA_EXPORT query are declared as PARTITION columns. DATA_EXPORT query requires at least one column to export.
La característica está deshabilitada.

Solución: habilite la característica mediante la sección Deshabilitado de forma predeterminada de este artículo.

Ejemplo de mensaje de error: Msg 46552: Writing into an external table is disabled. See 'https://go.microsoft.com/fwlink/?linkid=2201073' for more information

Bloqueo

Toma un bloqueo compartido en el objeto SCHEMARESOLUTION.

Ejemplos

A. Uso de CETAS con una vista para crear una tabla externa mediante la identidad administrada

En este ejemplo se proporciona código para escribir CETAS con una vista como origen, mediante la identidad administrada del sistema una autenticación.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

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

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity] WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [WorkspaceIdentity]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

B. Uso de CETAS con una vista para crear una tabla externa con autenticación SAS

En este ejemplo se proporciona código para escribir CETAS con una vista como origen, usando un token de SAS como autenticación.

CREATE DATABASE [<mydatabase>];
GO

USE [<mydatabase>];
GO

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

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

CREATE EXTERNAL FILE FORMAT [ParquetFF] WITH (
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
);
GO

CREATE EXTERNAL DATA SOURCE [SQLwriteable] WITH (
    LOCATION = 'adls://container@mystorageaccount.blob.core.windows.net/mybaseoutputfolderpath',
    CREDENTIAL = [SAS_token]
);
GO

CREATE EXTERNAL TABLE [dbo].[<myexternaltable>] WITH (
        LOCATION = '<myoutputsubfolder>/',
        DATA_SOURCE = [SQLwriteable],
        FILE_FORMAT = [ParquetFF]
) AS
SELECT * FROM [<myview>];
GO

C. Creación de una tabla externa en un único archivo parquet en el almacenamiento

En los dos ejemplos siguientes se muestra cómo descargar algunos de los datos de una tabla local en una tabla externa almacenada como archivos parquet en el contenedor de Azure Blob Storage. Están diseñados para trabajar con la base de datos AdventureWorks2022. En este ejemplo se muestra cómo crear una tabla externa como un único archivo parquet y en el ejemplo siguiente se muestra cómo crear una tabla externa y crear particiones de ella en varias carpetas con archivos parquet.

En el ejemplo siguiente se usa la identidad administrada para la autenticación. Por lo tanto, asegúrese de que la entidad de servicio de Azure SQL Managed Instance tenga el rol Colaborador de datos de Storage Blob en el contenedor de Azure Blob Storage. Como alternativa, puede modificar el ejemplo y usar tokens de secreto de acceso compartido (SAS) para la autenticación.

En el ejemplo siguiente, se crea una tabla externa en un único archivo parquet en Azure Blob Storage, que se selecciona de la tabla SalesOrderHeader para pedidos anteriores al 1 de enero de 2014:

--Example 1: Creating an external table into a single parquet file on the storage, selecting from SalesOrderHeader table for orders older than 1-Jan-2014:
USE [AdventureWorks2022]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong Password';
GO

CREATE DATABASE SCOPED CREDENTIAL [CETASCredential]
    WITH IDENTITY = 'managed identity';
GO

CREATE EXTERNAL DATA SOURCE [CETASExternalDataSource]
WITH (
    LOCATION = 'abs://container@storageaccount.blob.core.windows.net',
    CREDENTIAL = [CETASCredential] );
GO

CREATE EXTERNAL FILE FORMAT [CETASFileFormat]
WITH(
    FORMAT_TYPE=PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );
GO

-- Count how many rows we plan to offload
SELECT COUNT(*) FROM [AdventureWorks2022].[Sales].[SalesOrderHeader] WHERE
        OrderDate < '2013-12-31';

-- CETAS write to a single file, archive all data older than 1-Jan-2014:
CREATE EXTERNAL TABLE SalesOrdersExternal
WITH (
    LOCATION = 'SalesOrders/',
    DATA_SOURCE = [CETASExternalDataSource],
    FILE_FORMAT = [CETASFileFormat])
AS 
    SELECT 
        *
    FROM 
        [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';

-- you can query the newly created external table
SELECT COUNT (*) FROM SalesOrdersExternal;

D. Creación de una tabla externa con particiones en varios archivos parquet almacenados en un árbol de carpetas

Este ejemplo se basa en el ejemplo anterior para mostrar cómo crear una tabla externa y crear particiones en varias carpetas con archivos parquet. Puede usar tablas con particiones para obtener ventajas de rendimiento si el conjunto de datos es grande.

Cree una tabla externa con los datos SalesOrderHeader, siguiendo los pasos del ejemplo B, pero cree particiones de la tabla externa por año y mes de OrderDate. Al consultar tablas externas con particiones, podemos beneficiarnos de la eliminación de particiones para aumentar el rendimiento.

--CETAS write to a folder hierarchy (partitioned table):
CREATE EXTERNAL TABLE SalesOrdersExternalPartitioned
WITH (
    LOCATION = 'PartitionedOrders/year=*/month=*/', 
    DATA_SOURCE = CETASExternalDataSource,
    FILE_FORMAT = CETASFileFormat,
    --year and month will correspond to the two respective wildcards in folder path    
    PARTITION (
        [Year],
        [Month]
        ) 
    )
AS
    SELECT
        *,
        YEAR(OrderDate) AS [Year],
        MONTH(OrderDate) AS [Month]
    FROM [AdventureWorks2022].[Sales].[SalesOrderHeader]
    WHERE
        OrderDate < '2013-12-31';
GO

-- you can query the newly created partitioned external table
SELECT COUNT (*) FROM SalesOrdersExternalPartitioned;

Compartir a través de

CREATE EXTERNAL TABLE AS SELECT (CETAS) (Transact-SQL)

Sintaxis

Argumentos

[ [ database_name. [ schema_name ] . ] | schema_name. ] table_name

[ ( column_name [ ,...n ] ) ]

LOCATION

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

Opciones de REJECT

WITH common_table_expression

SELECT <select_criteria>

Permisos

Control de errores

Comentarios

Limitaciones y restricciones

Limitaciones y restricciones de Azure Synapse Analytics

Errores de caracteres

Trabajar con parquet

Bloqueo

Supported data types (Tipos de datos admitidos)

Ejemplos

A. Crear una tabla de Hadoop con CREATE EXTERNAL TABLE AS SELECT

B. Usar una sugerencia de consulta con CREATE EXTERNAL TABLE AS SELECT

C. Uso de CETAS para cambiar los atributos de columna

D. Uso de CREATE EXTERNAL TABLE AS SELECT al exportar datos como parquet

E. Uso de CREATE EXTERNAL TABLE AS SELECT de la tabla delta a parquet

F. Usar CREATE EXTERNAL TABLE AS SELECT con una vista como origen

G. Usar CREATE EXTERNAL TABLE AS SELECT con una vista como origen

Pasos siguientes

Sintaxis

Argumentos

[ [ database_name. [ schema_name ] . ] | schema_name. ] table_name

LOCATION = 'path_to_folder'

DATA_SOURCE = external_data_source_name

FILE_FORMAT = external_file_format_name

[, PARTITION ( nombre de columna [ , ...n ] ) ]

WITH <common_table_expression>

SELECT <select_criteria>

Permisos

Permisos en el almacenamiento

Permisos en la instancia administrada de SQL

Tipos de datos admitidos

Limitaciones y restricciones

Tipos de almacenamiento

Deshabilitado de forma predeterminada

Habilitación de CETAS

Permisos para habilitar CETAS

Métodos para habilitar CETAS

Comprobación del estado de CETAS

Solución de problemas

Control de errores

Mensajes comunes de error

Bloqueo

Ejemplos

A. Uso de CETAS con una vista para crear una tabla externa mediante la identidad administrada

B. Uso de CETAS con una vista para crear una tabla externa con autenticación SAS

C. Creación de una tabla externa en un único archivo parquet en el almacenamiento

D. Creación de una tabla externa con particiones en varios archivos parquet almacenados en un árbol de carpetas

Pasos siguientes

Comentarios

Recursos adicionales