Uso de tablas externas con Synapse SQL

Una tabla externa apunta a datos ubicados en Hadoop, Azure Storage Blob o Azure Data Lake Storage. Las tablas externas se usan para leer datos de archivos de Azure Storage o escribir datos en ellos. 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.

Las principales diferencias entre Hadoop y las tablas externas nativas se presentan en la tabla siguiente:

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.

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.

CREATE EXTERNAL DATA SOURCE

Los orígenes de datos externos se usan para conectarse a las cuentas de almacenamiento. La documentación completa se describe aquí.

Sintaxis de CREATE EXTERNAL DATA SOURCE

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
     , TYPE = HADOOP
)
[;]

CREATE EXTERNAL DATA SOURCE <data_source_name>
WITH
(    LOCATION         = '<prefix>://<path>'
     [, CREDENTIAL = <database scoped credential> ]
)
[;]

Argumentos de CREATE EXTERNAL DATA SOURCE

data_source_name

Especifica el nombre definido por el usuario para el origen de datos. El nombre debe ser único en la base de datos.

Location

LOCATION = '<prefix>://<path>': proporciona el protocolo de conectividad y la ruta de acceso al origen de datos externo. Estos patrones se pueden usar en la ubicación:

El prefijo https: le permite usar la subcarpeta en la ruta de acceso.

Credential:

CREDENTIAL = <database scoped credential> es una credencial opcional que se usará para realizar la autenticación en Azure Storage. Un origen de datos externo sin credencial puede acceder a la cuenta de almacenamiento pública o usar la identidad de Azure AD del autor de llamada para acceder a los archivos del almacenamiento.

• En un grupo de SQL dedicado, las credenciales cuyo ámbito es la base de datos pueden especificar una identidad de aplicación personalizada, una identidad administrada de área de trabajo o una clave SAK.
• En un grupo de SQL sin servidor, las credenciales cuyo ámbito es la base de datos pueden especificar una identidad administrada de área de trabajo o una clave SAS.

TYPE

TYPE = HADOOP es la opción que especifica que se debe usar la tecnología basada en Java para acceder a los archivos subyacentes. Este parámetro no se puede usar en los grupos de SQL sin servidor que usen un lector nativo integrado.

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/')

CREATE EXTERNAL FILE FORMAT

Crea un objeto de formato de archivo externo que define los datos externos almacenados en Azure Blob Storage o Azure Data Lake Store. La creación de un formato de archivo externo es un requisito previo para crear una tabla externa. La documentación completa se encuentra aquí.

Al crear un formato de archivo externo, se especifica el diseño real de los datos a los que hace referencia una tabla externa.

Sintaxis de CREATE EXTERNAL FILE FORMAT

-- Create an external file format for PARQUET files.  
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = PARQUET  
    [ , DATA_COMPRESSION = {  
        'org.apache.hadoop.io.compress.SnappyCodec'  
      | 'org.apache.hadoop.io.compress.GzipCodec'      }  
    ]);  

--Create an external file format for DELIMITED TEXT files
CREATE EXTERNAL FILE FORMAT file_format_name  
WITH (  
    FORMAT_TYPE = DELIMITEDTEXT  
    [ , DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec' ]
    [ , FORMAT_OPTIONS ( <format_options> [ ,...n  ] ) ]  
    );  

<format_options> ::=  
{  
    FIELD_TERMINATOR = field_terminator  
    | STRING_DELIMITER = string_delimiter
    | First_Row = integer
    | USE_TYPE_DEFAULT = { TRUE | FALSE }
    | Encoding = {'UTF8' | 'UTF16'}
    | PARSER_VERSION = {'parser_version'}
}

Argumentos para CREATE EXTERNAL FILE FORMAT

file_format_name: especifica un nombre para el formato de archivo externo.

FORMAT_TYPE = [ PARQUET | DELIMITEDTEXT]: especifica el formato de los datos externos.

• PARQUET: especifica un formato Parquet.
• DELIMITEDTEXT: especifica un formato de texto con delimitadores de columna, también denominados terminadores de campo.

FIELD_TERMINATOR = field_terminator: solo se aplica a archivos de texto delimitado. El terminador de campo especifica uno o varios caracteres que marcan el final de cada campo (columna) en el archivo de texto delimitado. El valor predeterminado es el carácter de barra vertical ("|").

Ejemplos:

• FIELD_TERMINATOR = '|'
• FIELD_TERMINATOR = ' '
• FIELD_TERMINATOR = ꞌ\tꞌ

STRING_DELIMITER = string_delimiter: especifica el terminador de campo de los datos de tipo cadena en el archivo de texto delimitado. El delimitador de cadena tiene una longitud de uno o más caracteres y se escribe entre comillas simples. El valor predeterminado es la cadena vacía ("").

Ejemplos:

• STRING_DELIMITER = '"'
• STRING_DELIMITER = '*'
• STRING_DELIMITER = ꞌ,ꞌ

FIRST_ROW = First_row_int: especifica el número de fila que se lee primero y se aplica a todos los archivos. Si establece el valor en dos hará que se omita la primera fila de cada archivo (fila de encabezado) al cargar los datos. Las filas se omiten en función de la existencia de terminadores de fila (/r/n, /r, /n).

USE_TYPE_DEFAULT = { TRUE | FALSE }: especifica cómo administrar valores que faltan en archivos de texto delimitado al recuperar datos del archivo de texto.

TRUE: si va a recuperar datos del archivo de texto, almacene todos los valores que falten mediante el tipo de datos del valor predeterminado para la columna correspondiente en la definición de la tabla externa. Por ejemplo, reemplace un valor que falta con:

• 0 si la columna se define como una columna numérica. Las columnas decimales no se admiten y provocarán un error.
• Cadena vacía ("") si la columna es una columna de cadena.
• 1900-01-01 si la columna es una columna de fecha.

FALSE: almacena todos los valores que faltan como NULL. Los valores NULL que se almacenan mediante la palabra NULL en el archivo de texto delimitado se importan como la cadena 'NULL'.

Encoding = {'UTF8' | 'UTF16'}: el grupo de SQL sin servidor puede leer archivos de texto delimitados con codificación UTF8 y UTF16.

DATA_COMPRESSION = data_compression_method: este argumento especifica el método de compresión de datos para los datos externos.

El tipo de formato PARQUET admite estos métodos de compresión:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'
• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'

Al leer de tablas externas de PARQUET, este argumento se omite, pero se usa al escribir en tablas externas mediante CETAS.

El tipo de formato de archivo DELIMITEDTEXT admite estos métodos de compresión:

• DATA_COMPRESSION = 'org.apache.hadoop.io.compress.GzipCodec'

PARSER_VERSION = 'parser_version' especifica la versión del analizador que se utilizará al leer archivos CSV. Las versiones disponibles del analizador son la 1.0 y la 2.0. Esta opción solo está disponible en los grupos de SQL sin servidor.

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'
)

CREATE EXTERNAL TABLE

El comando CREATE EXTERNAL TABLE crea una tabla externa para Synapse SQL para acceder a los datos almacenados en Azure Blob Storage o Azure Data Lake Storage.

Sintaxis de CREATE 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
        [, TABLE_OPTIONS = N'{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}' ]
        [, <reject_options> [ ,...n ] ] 
    )
[;] 

<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'
}   

Argumento de CREATE EXTERNAL TABLE

{ 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. Si se trata de una tabla externa, el grupo de Synapse SQL almacena solo los metadatos de la tabla. Ningún dato real se mueve o se almacena en la base de datos de Synapse SQL.

<column_definition>, ...n ]

CREATE EXTERNAL TABLE admite la capacidad de configurar el nombre de columna, el tipo de datos y la intercalación. No se puede usar DEFAULT CONSTRAINT en tablas externas.

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 de acceso del archivo y el nombre de archivo de los datos reales en 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.

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

Opciones de Reject

Puede especificar los parámetros de rechazo que determinan la forma en que el servicio manejará los registros desfasados que recupera de la fuente de datos externa. Un registro de datos se considera "desfasado" si los tipos de datos reales no coinciden con las definiciones de columna de la tabla externa.

Si no se especifican ni se cambian las opciones de rechazo, el servicio utiliza 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, el servicio usa las opciones de rechazo para determinar el número 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

Este es el único valor que se admite en este momento. Aclara que la opción REJECT_VALUE se especifica como un valor literal.

value

REJECT_VALUE es un valor literal. 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_VALUE = reject_value

Especifica el número 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.

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, el servicio 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 AñoMesDía_HoraMinutoSegundo_Id.Instrucción (Ej. 20180330-173205-559EE7D2-196D-400A-806D-3BF5D007F891). Puede usar el identificador de instrucción para correlacionar la carpeta con la consulta que la generó. En esta carpeta, se escriben dos archivos: el archivo error.json y el archivo de datos.

El archivo error.json contiene una matriz json con los errores encontrados relacionados con las filas rechazadas. Cada elemento que representa un error contiene los siguientes atributos:

OPCIONES DE TABLA

TABLE_OPTIONS = json options. 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.

Permisos de CREATE EXTERNAL TABLE

Para realizar la selección en una tabla externa, se necesitan las credenciales adecuadas con permisos de lectura y de lista.

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