Uso de tablas externas con Synapse SQL

Una tabla externa apunta a datos ubicados en Hadoop, Azure Storage Blob o Azure Data Lake Storage. Puede usar tablas externas para leer datos de archivos o escribir datos en archivos en Azure Storage.

Con Synapse SQL se pueden usar tablas externas para leer datos externos mediante un grupo de SQL dedicado o un grupo de SQL sin servidor.

En función del tipo de origen de datos externo, puede usar dos tipos de tablas externas:

• Tablas externas de Hadoop, que puede usar para leer y exportar datos en varios formatos de datos, como CSV, Parquet y ORC. Las tablas externas de Hadoop están disponibles en los grupos de SQL dedicados, pero no en los grupos de SQL sin servidor.
• Tablas externas nativas, que puede usar para leer y exportar datos en varios formatos de datos, como CSV y Parquet. Las tablas externas nativas están disponibles en los grupos de SQL sin servidor, y están disponibles en versión preliminar pública en grupos de SQL dedicados. La escritura y exportación de datos mediante CETAS y las tablas externas nativas solo está disponible en el grupo de SQL sin servidor, pero no en los grupos de SQL dedicados.

Principales diferencias entre Hadoop y las tablas externa:

Tablas externas en un grupo de SQL dedicado y en un grupo de SQL sin servidor

Puede usar tablas externas para:

• Consultar Azure Blob Storage y Azure Data Lake Gen2 con instrucciones Transact-SQL.
• Almacenar los resultados de las consultas en archivos de Azure Blob Storage o Azure Data Lake Storage mediante CETAS.
• Importar datos de Azure Blob Storage y Azure Data Lake Storage y almacenarlos en un grupo de SQL dedicado (solo tablas de Hadoop en un grupo dedicado).

Para crear tablas externas en grupos de Synapse SQL, siga estos pasos:

1. CREAR ORIGEN DE DATOS EXTERNO para hacer referencia a un almacenamiento externo de Azure y especificar la credencial que se debe usar para acceder al almacenamiento.
2. CREAR FORMATO DE ARCHIVO EXTERNO para describir el formato de los archivos CSV o Parquet.
3. CREAR TABLA EXTERNA sobre los archivos incluidos en el origen de datos con el mismo formato de archivo.

Eliminación de particiones de carpetas

Las tablas externas nativas de los grupos de Synapse pueden omitir los archivos colocados en las carpetas que no son pertinentes para las consultas. Si los archivos se almacenan en una jerarquía de carpetas (por ejemplo, /year=2020/month=03/day=16) y los valores para year, month y day se exponen como las columnas, las consultas que contienen filtros como year=2020 leerán los archivos solo desde las subcarpetas colocadas dentro de la carpeta year=2020. En esta consulta, se omitirán los archivos y las carpetas colocados en otras carpetas (year=2021 o year=2022). Esta eliminación se conoce como eliminación de particiones.

La eliminación de las particiones de una carpeta está disponible en las tablas externas nativas que se encuentran sincronizadas de los grupos de Spark de Synapse. Si tiene conjuntos de datos particionados y quiere aprovechar la eliminación de particiones con las tablas externas que crea, utilice las vistas particionadas en lugar de las tablas externas.

Eliminación de archivos

Algunos formatos de datos, como Parquet y Delta, contienen estadísticas de archivos para cada columna (por ejemplo, valores mínimos y máximos para cada columna). Las consultas que filtran datos no leerán los archivos en los que no existen los valores de columna necesarios. En primer lugar, la consulta explorará los valores mínimos y máximos de las columnas que se usan en el predicado de la consulta para buscar los archivos que no contienen los datos necesarios. Estos archivos se omitirán y se eliminarán del plan de consulta. Esta técnica también se conoce como delegación de predicados de filtros y puede mejorar el rendimiento de las consultas. La delegación de filtros está disponible en los grupos de SQL sin servidor en formatos Parquet y Delta. Si desea aprovechar la delegación de filtros para los tipos de cadena, utilice el tipo VARCHAR con la intercalación Latin1_General_100_BIN2_UTF8. Para más información sobre las intercalaciones, consulte Tipos de intercalación admitidos para Synapse SQL.

Seguridad

El usuario debe tener permiso SELECT en una tabla externa para leer los datos. Las tablas externas acceden al almacenamiento de Azure subyacente mediante la credencial con ámbito de base de datos definida en el origen de datos mediante las siguientes reglas:

• El origen de datos sin credenciales permite que las tablas externas accedan a archivos disponibles públicamente en Azure Storage.
• El origen de datos puede tener una credencial que permita a las tablas externas acceder solo a los archivos de Azure Storage mediante el token de SAS o la identidad administrada del área de trabajo. Para consultar ejemplos, consulte el artículo sobre el desarrollo del control de acceso al almacenamiento de archivos.

Ejemplo de CREATE EXTERNAL DATA SOURCE

CREATE DATABASE SCOPED CREDENTIAL [ADLS_credential]
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'
GO
CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH
  -- Please note the abfss endpoint when your account has secure transfer enabled
  ( LOCATION = 'abfss://data@newyorktaxidataset.dfs.core.windows.net' ,
    CREDENTIAL = ADLS_credential ,
    TYPE = HADOOP
  ) ;

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/',
       TYPE = HADOOP)

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'
GO
CREATE EXTERNAL DATA SOURCE SqlOnDemandDemo WITH (
    LOCATION = 'https://sqlondemandstorage.blob.core.windows.net',
    CREDENTIAL = sqlondemand
);

CREATE EXTERNAL DATA SOURCE YellowTaxi
WITH ( LOCATION = 'https://azureopendatastorage.blob.core.windows.net/nyctlc/yellow/')

Ejemplo de CREATE EXTERNAL FILE FORMAT

En el ejemplo siguiente se crea un formato de archivo externo para los archivos del censo:

CREATE EXTERNAL FILE FORMAT census_file_format
WITH
(  
    FORMAT_TYPE = PARQUET,
    DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
)

Ejemplo de CREATE EXTERNAL TABLE

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

Creación y consulta de tablas externas a partir de un archivo en Azure Data Lake

Mediante las funcionalidades de exploración de Data Lake de Synapse Studio ya se puede crear y consultar una tabla externa mediante un grupo de Synapse SQL con un solo clic con el botón derecho en el archivo. El gesto de un solo clic para crear tablas externas desde la cuenta de almacenamiento de ADLS Gen2 solo se admite para los archivos con formato Parquet.

Requisitos previos

• Debe tener acceso al área de trabajo con al menos el rol de acceso Storage Blob Data Contributor a la cuenta de ADLS Gen2 o a las listas de control de acceso (ACL) que le permiten consultar los archivos.

• Debe tener al menos permisos para crear una tabla externa y consultar tablas externas en el grupo de Synapse SQL (dedicado o sin servidor).

En el panel Data (Datos), seleccione el archivo desde el que desea crear la tabla externa:

Se abrirá una ventana de diálogo. Seleccione un grupo de SQL dedicado o sin servidor, asígnele un nombre a la tabla y seleccione Abrir script:

El script de SQL se genera automáticamente e infiere el esquema del archivo:

Ejecute el script. El script ejecutará automáticamente una instrucción Select Top 100 *:

Ahora se crea la tabla externa, para una futura exploración del contenido de esta tabla externa el usuario puede consultarla directamente desde el panel Data (Datos):

Pasos siguientes

Consulte el artículo CETAS para obtener información sobre cómo guardar los resultados de una consulta en una tabla externa en Azure Storage. O bien puede empezar a consultar Tablas externas de Apache Spark para Azure Synapse.