Creación y uso de tablas externas nativas mediante grupos de SQL en Azure Synapse Analytics

Artículo
05/12/2023

En esta sección, aprenderá a crear y usar tablas externas nativas en grupos de Synapse SQL. Las tablas externas nativas tienen un mejor rendimiento en comparación con las tablas externas con TYPE=HADOOP en su definición de origen de datos externo. Esto se debe a que las tablas externas nativas usan código nativo para acceder a los datos externos.

Las tablas externas son útiles cuando se quiere controlar el acceso a los datos externos en grupos de Synapse SQL. También lo son si quiere usar herramientas, como Power BI, junto con grupos de Synapse SQL. Las tablas externas pueden acceder a dos tipos de almacenamiento:

Almacenamiento público, en el que los usuarios acceden a archivos de almacenamiento público.
Almacenamiento protegido, en el que los usuarios acceden a los archivos de almacenamiento mediante una credencial de SAS, una identidad de Microsoft Entra o una identidad administrada del área de trabajo de Synapse.

Nota:

En grupos de SQL dedicados, solo puede usar tablas externas nativas con un tipo de archivo Parquet y esta característica está en versión preliminar pública. Si quiere usar la funcionalidad de lector de Parquet disponible con carácter general en grupos de SQL dedicados, o necesita acceso a archivos CSV u ORC, use tablas externas de Hadoop. Las tablas externas nativas están disponibles con carácter general en grupos de SQL sin servidor. Más información sobre las diferencias entre las tablas externas nativas y Hadoop en Uso de tablas externas con Synapse SQL.

En la tabla siguiente se enumeran los formatos de datos admitidos:

Formato de datos (tablas nativas externas)	Grupo de SQL sin servidor	Grupo de SQL dedicado
Parquet	Sí (GA)	Sí (versión preliminar pública)
CSV	Sí	No (como alternativa, use tablas externas de Hadoop)
delta	Sí	No
Spark	Sí	No
Dataverse	Sí	No
Formatos de datos de Azure Cosmos DB (JSON, BSON, etc.)	No (como alternativa, cree vistas)	No

Prerrequisitos

El primer paso es crear la base de datos en que se crearán las tablas. 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 sobre esto, consulte CREATE MASTER KEY (Transact-SQL). Luego, se crean los siguientes objetos que se usan en este ejemplo:

DATABASE SCOPED CREDENTIAL sqlondemand que permite el acceso a la cuenta de Azure Storage https://sqlondemandstorage.blob.core.windows.net protegida mediante SAS.

CREATE DATABASE SCOPED CREDENTIAL [sqlondemand]
WITH IDENTITY='SHARED ACCESS SIGNATURE',  
SECRET = 'sv=2018-03-28&ss=bf&srt=sco&sp=rl&st=2019-10-14T12%3A10%3A25Z&se=2061-12-31T12%3A10%3A00Z&sig=KlSU2ullCscyTS0An0nozEpo4tO5JAgGBvw%2FJX2lguw%3D'

EXTERNAL DATA SOURCE sqlondemanddemo que hace referencia a una cuenta de almacenamiento de demostración protegida con clave SAS y EXTERNAL DATA SOURCE nyctlc que hace referencia a una cuenta de Azure Storage disponible públicamente en la ubicación https://azureopendatastorage.blob.core.windows.net/nyctlc/.

CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);
GO
CREATE EXTERNAL DATA SOURCE nyctlc
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/')
GO
CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( location = 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/' );

Los formatos de archivo QuotedCSVWithHeaderFormat y ParquetFormat que describen los tipos de archivo .csv y Parquet.

CREATE EXTERNAL FILE FORMAT QuotedCsvWithHeaderFormat
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS ( FIELD_TERMINATOR = ',', STRING_DELIMITER = '"', FIRST_ROW = 2   )
);
GO
CREATE EXTERNAL FILE FORMAT ParquetFormat WITH (  FORMAT_TYPE = PARQUET );
GO
CREATE EXTERNAL FILE FORMAT DeltaLakeFormat WITH (  FORMAT_TYPE = DELTA );
GO

Las consultas de este artículo se ejecutarán en la base de datos de ejemplo y usarán estos objetos.

Tabla externa en un archivo

Puede crear tablas externas que accedan a los datos de una cuenta de Azure Storage que permita el acceso a los usuarios con una identidad de Microsoft Entra o clave SAS. Puede crear tablas externas de la misma manera que crea tablas externas de SQL Server normales.

La siguiente consulta crea una tabla externa que lee el archivopopulation.csv de la cuenta de Azure Storage de demostración de SynapseSQL a la que se hace referencia mediante un sqlondemanddemo origen de datos y que está protegida con una credencial cuyo ámbito es la base de datos llamada sqlondemand.

El origen de datos y la credencial cuyo ámbito es la base de datos se crean en el script de instalación.

Nota

Cambie la primera línea de la consulta, es decir, [mydbname], para usar la base de datos que ha creado.

USE [mydbname];
GO
CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat
);

Las tablas CSV nativas solo están disponibles actualmente en los grupos de SQL sin servidor.

Tabla externa en un conjunto de archivos

Puede crear tablas externas que lean datos de un conjunto de archivos que se encuentran en Azure Storage:

CREATE EXTERNAL TABLE Taxi (
     vendor_id VARCHAR(100) COLLATE Latin1_General_BIN2, 
     pickup_datetime DATETIME2, 
     dropoff_datetime DATETIME2,
     passenger_count INT,
     trip_distance FLOAT,
     fare_amount FLOAT,
     tip_amount FLOAT,
     tolls_amount FLOAT,
     total_amount FLOAT
) WITH (
         LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
         DATA_SOURCE = nyctlc,
         FILE_FORMAT = ParquetFormat
);

Puede especificar el patrón que deben seguir los archivos para que la tabla externa haga referencia a estos. El patrón solo es necesario para las tablas de CSV y Parquet. Si usa el formato Delta Lake, solo es necesario especificar una carpeta raíz y la tabla externa encontrará automáticamente el patrón.

Nota

La tabla se crea en una estructura de carpetas con particiones, pero no se pueden eliminar algunas de estas particiones. Si desea mejorar el rendimiento omitiendo los archivos que no cumplen algunos criterios (como un año o un mes específicos), utilice las vistas de datos externos.

Tabla externa en archivos anexables

Los archivos a los que hace referencia una tabla externa no deben cambiarse mientras se ejecuta la consulta. En una consulta de ejecución larga, el grupo de SQL puede reintentar lecturas, leer partes de los archivos o incluso leer el archivo varias veces. Los cambios en el contenido del archivo provocarían resultados incorrectos. Por lo tanto, el grupo de SQL genera un error en la consulta si detecta que cambia la hora de modificación de algún archivo durante la ejecución de la consulta. En algunos escenarios, es posible que desee crear una tabla de los archivos que se anexan constantemente. Para evitar los errores de consulta debidos a archivos que se anexan constantemente, puede especificar que la tabla externa debe omitir las lecturas potencialmente incoherentes con el valor TABLE_OPTIONS.

CREATE EXTERNAL TABLE populationExternalTable
(
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
)
WITH (
    LOCATION = 'csv/population/population.csv',
    DATA_SOURCE = sqlondemanddemo,
    FILE_FORMAT = QuotedCSVWithHeaderFormat,
    TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}'
);

La opción de lectura ALLOW_INCONSISTENT_READS deshabilitará la comprobación de la hora de modificación de los archivos durante el ciclo de vida de la consulta y leerá lo que esté disponible en los archivos a los que hace referencia la tabla externa. En los archivos anexables, el contenido existente no se actualiza y solo se agregan nuevas filas. Por lo tanto, se reduce la probabilidad de resultados incorrectos en comparación con los archivos actualizables. Esta opción podría permitirle leer los archivos anexados con frecuencia sin necesidad de administrar los errores.

Esta opción solo está disponible en las tablas externas creadas con el formato de archivo CSV.

Nota

Como el nombre de la opción implica, el creador de la tabla acepta el riesgo de que los resultados no sean coherentes. En los archivos anexables, es posible que se obtengan resultados incorrectos si se fuerzan varias lecturas de los archivos subyacentes mediante la autocombinación de la tabla. En la mayoría de las consultas "clásicas", la tabla externa simplemente omitirá algunas filas anexadas mientras se estaba ejecutando la consulta.

Tabla externa de Delta Lake

Las tablas externas se pueden crear encima de una carpeta de Delta Lake. La única diferencia entre las tablas externas creadas en un único archivo o un conjunto de archivos y las tablas externas creadas en formato Delta Lake es que en la tabla externa de Delta Lake se debe hacer referencia a una carpeta que contenga la estructura de Delta Lake.

ECDC COVID-19 Delta Lake folder

Un ejemplo de una definición de tabla creada en una carpeta de Delta Lake es:

CREATE EXTERNAL TABLE Covid (
     date_rep date,
     cases int,
     geo_id varchar(6)
) WITH (
        LOCATION = 'covid', --> the root folder containing the Delta Lake files
        data_source = DeltaLakeStorage,
        FILE_FORMAT = DeltaLakeFormat
);

No se pueden crear tablas externas en una carpeta con particiones. Revise los demás problemas conocidos en la página de autoayuda del grupo de SQL sin servidor de Synapse.

Tablas delta en carpetas con particiones

Las tablas externas de grupos de SQL sin servidor no admiten la creación de particiones con el formato Delta Lake. Use vistas con particiones delta en lugar de tablas si ha particionado conjuntos de datos de Delta Lake.

Importante

No cree tablas externas en carpetas de Delta Lake con particiones aunque vea que podrían funcionar en algunos casos. El uso de características no admitidas, como tablas externas en carpetas delta con particiones, puede causar problemas o inestabilidad en el grupo sin servidor. El equipo de Soporte técnico de Azure no podrá resolver ningún problema si usa tablas en carpetas con particiones. Se le pedirá que cambie a vistas con particiones delta y reescriba el código para usar solo la característica admitida antes de proceder a la resolución del problema.

Uso de una tabla externa

Puede usar tablas externas en las consultas de la misma manera que las utiliza en las consultas de SQL Server.

En la consulta siguiente se muestra, para lo que se usa la tabla externa population que creamos en la sección anterior. Devuelve los nombres de país/región con su población en 2019, en orden descendente.

Nota

Cambie la primera línea de la consulta, es decir, [mydbname], para usar la base de datos que ha creado.

USE [mydbname];
GO

SELECT
    country_name, population
FROM populationExternalTable
WHERE
    [year] = 2019
ORDER BY
    [population] DESC;

El rendimiento de esta consulta puede variar en función de la región. Es posible que el área de trabajo no se coloque en la misma región que las cuentas de almacenamiento de Azure que se usan en estos ejemplos. En el caso de las cargas de trabajo de producción, coloque el área de trabajo de Synapse y el almacenamiento de Azure en la misma región.

Pasos siguientes

Para información sobre cómo se almacenan los resultados de una consulta en Storage, consulte el artículo Almacenamiento de los resultados de las consultas en Storage.

Creación y uso de tablas externas nativas mediante grupos de SQL en Azure Synapse Analytics

Prerrequisitos

Tabla externa en un archivo

Tabla externa en un conjunto de archivos

Tabla externa en archivos anexables

Tabla externa de Delta Lake

Tablas delta en carpetas con particiones

Uso de una tabla externa

Pasos siguientes

Recursos adicionales