Transformación de archivos de datos con la instrucción CREATE EXTERNAL TABLE AS SELECT

5 minutos

El lenguaje SQL incluye muchas características y funciones que permiten manipular datos. Por ejemplo, puede usar SQL para:

Filtrar filas y columnas en un conjunto de datos.
Cambiar el nombre de los campos de datos y convertir entre tipos de datos.
Calcular los campos de datos derivados.
Manipular valores de cadena.
Agrupar y agregar datos.

Los grupos de SQL sin servidor de Azure Synapse se pueden usar para ejecutar instrucciones SQL que transforman los datos y conservan los resultados como un archivo en un lago de datos para su posterior procesamiento o consulta. Si está familiarizado con la sintaxis de Transact-SQL, puede crear una instrucción SELECT que ejecute la transformación específica que le interese y almacenar los resultados de dicha instrucción en un formato de archivo seleccionado.

Puede usar una instrucción CREATE EXTERNAL TABLE AS SELECT (CETAS) en un grupo de SQL dedicado o un grupo de SQL sin servidor para conservar los resultados de una consulta en una tabla externa, que almacena sus datos en un archivo del lago de datos.

La instrucción CETAS incluye una instrucción SELECT que consulta y manipula datos de cualquier origen de datos válido (que podría ser una tabla o vista existente en una base de datos, o una función OPENROWSET que lee datos basados en archivos del lago de datos). Los resultados de la instrucción SELECT se conservan en una tabla externa, que es un objeto de metadatos de una base de datos que proporciona una abstracción relacional sobre los datos almacenados en archivos. En el siguiente diagrama, se ilustra el concepto de manera visual:

A diagram showing a CREATE EXTERNAL TABLE AS SELECT statement saving query results as a file.

Al aplicar esta técnica, puede usar SQL para extraer y transformar datos de archivos o tablas, y almacenar los resultados transformados para el análisis o el procesamiento de bajada. Las operaciones posteriores en los datos transformados se pueden realizar en la tabla relacional de la base de datos del grupo de SQL o directamente en los archivos de datos subyacentes.

Creación de objetos de base de datos externos para admitir CETAS

Para usar expresiones CETAS, debe crear los siguientes tipos de objeto en una base de datos para un grupo de SQL sin servidor o dedicado. Cuando se usa un grupo de SQL sin servidor, cree estos objetos en una base de datos personalizada (creada con la instrucción CREATE DATABASE), no en la base de datos integrada.

Origen de datos externo

Un origen de datos externo encapsula una conexión a una ubicación del sistema de archivos en un lago de datos. Después, puede usar esta conexión para especificar una ruta de acceso relativa en la que se guardan los archivos de datos de la tabla externa creada por la instrucción CETAS.

Si los datos de origen de la instrucción CETAS están en archivos de la misma ruta de acceso del lago de datos, puede usar el mismo origen de datos externo en la función OPENROWSET que se usa para consultarlo. Como alternativa, puede crear un origen de datos externo independiente para los archivos de origen o usar una ruta de acceso de archivo completa en la función OPENROWSET.

Para crear un origen de datos externo, use la instrucción CREATE EXTERNAL DATA SOURCE, como se muestra en este ejemplo:

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

En el ejemplo anterior se supone que los usuarios que ejecutan consultas que usan el origen de datos externo tendrán permisos suficientes para acceder a los archivos. Un enfoque alternativo consiste en encapsular una credencial en el origen de datos externo para que se pueda usar para acceder a los datos de archivo sin conceder a todos los usuarios permisos para leerlos directamente:

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

Sugerencia

Además de la autenticación SAS, puede definir credenciales que usen la identidad administrada (la identidad de Microsoft Entra que usa su área de trabajo de Azure Synapse), una entidad de seguridad de Microsoft Entra específica o una autenticación de paso según la identidad del usuario que ejecuta la consulta (que es el tipo de autenticación predeterminado). Para obtener más información sobre el uso de credenciales en un grupo de SQL sin servidor, consulte el artículo Control del acceso a la cuenta de almacenamiento del grupo de SQL sin servidor en Azure Synapse Analytics en la documentación de Azure Synapse Analytics.

Formato de archivo externo

La instrucción CETAS crea una tabla con sus datos almacenados en archivos. Debe especificar el formato de los archivos que quiere crear como un formato de archivo externo.

Para crear un formato de archivo externo, use la instrucción CREATE EXTERNAL FILE FORMAT, como se muestra en este ejemplo:

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

Sugerencia

En este ejemplo, los archivos se guardarán en formato Parquet. También puede crear formatos de archivo externos para otros tipos de archivo. Consulte CREATE EXTERNAL FILE FORMAT (Transact-SQL) para obtener más información.

Uso de la instrucción CETAS

Después de crear un origen de datos externo y un formato de archivo externo, puede usar la instrucción CETAS para transformar los datos y almacenar los resultados en una tabla externa.

Por ejemplo, supongamos que los datos de origen que quiere transformar constan de pedidos de ventas en archivos de texto delimitados por comas que se almacenan en una carpeta de un lago de datos. Quiere filtrar los datos para incluir solo los pedidos marcados como "pedido especial" y guardar los datos transformados como archivos Parquet en una carpeta diferente del mismo lago de datos. Puede usar el mismo origen de datos externo para las carpetas de origen y destino, como se muestra en este ejemplo:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Los parámetros LOCATION y BULK del ejemplo anterior son rutas de acceso relativas para los resultados y los archivos de origen, respectivamente. Las rutas de acceso son relativas a la ubicación del sistema de archivos a la que hace referencia el origen de datos externo de los archivos.

Un aspecto importante que debe comprender es que debe usar un origen de datos externo para especificar la ubicación donde se guardarán los datos transformados para la tabla externa. Cuando los datos de origen basados en archivos se almacenan en la misma jerarquía de carpetas, puede usar el mismo origen de datos externo. De lo contrario, puede usar un segundo origen de datos para definir una conexión a los datos de origen o usar la ruta de acceso completa, como se muestra en este ejemplo:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Eliminación de tablas externas

Si ya no necesita la tabla externa que contiene los datos transformados, puede quitarla de la base de datos mediante la instrucción DROP EXTERNAL TABLE, como se muestra aquí:

DROP EXTERNAL TABLE SpecialOrders;

Sin embargo, es importante comprender que las tablas externas son una abstracción de metadatos sobre los archivos que contienen los datos reales. La eliminación de una tabla externa no elimina los archivos subyacentes.

Continuar