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

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.

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

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.

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.

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.

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.

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.

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.

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.