Virtualización de una tabla delta con PolyBase

  • Artículo

Se aplica a: SQL Server 2022 (16.x) y versiones posteriores.

SQL Server 2022 (16.x) puede consultar datos directamente de la carpeta de una tabla delta. Este concepto, que normalmente se conoce como virtualización de datos, permite que los datos permanezcan en su ubicación original, pero se puedan consultar desde una instancia de SQL Server con comandos T-SQL, como cualquier otra tabla. Esta característica usa conectores de PolyBase y minimiza la necesidad de copiar datos mediante procesos de ETL.

En el ejemplo siguiente, la carpeta de una tabla delta se almacena en Azure Blob Storage y se accede a ella a través de OPENROWSET o una tabla externa.

Para más información sobre la virtualización de datos, consulte Introducción a la virtualización de datos con PolyBase.

Preconfiguración

1. Habilitación de PolyBase en sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Creación de una base de datos de usuario

En este ejercicio se crea una base de datos de ejemplo con la configuración y la ubicación predeterminadas. Usará esta base de datos de ejemplo vacía para trabajar con los datos y almacenar la credencial con ámbito. En este ejemplo, se usará una nueva base de datos vacía denominada Delta_demo.

CREATE DATABASE [Delta_demo];

3. Creación de una clave maestra y credenciales con ámbito de base de datos

La clave maestra de la base de datos de usuario se necesita para cifrar el secreto de la credencial de ámbito de base de datos, delta_storage_dsc. En este ejemplo, la tabla delta reside en Azure Data Lake Storage Gen2.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Creación de un origen de datos externos

La credencial con ámbito de base de datos se usará para el origen de datos externos. En este ejemplo, la tabla delta reside en Azure Data Lake Storage Gen2, por lo que debe usar el prefijo adls y el método de identidad SHARED ACCESS SIGNATURE. Para obtener más información sobre los conectores y prefijos, incluida la nueva configuración de SQL Server 2022 (16.x), consulte CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Por ejemplo, si la cuenta de almacenamiento se denomina delta_lake_sample y el contenedor se denomina sink, el código sería:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Uso de OPENROWSET para acceder a los datos

En este ejemplo, la carpeta de la tabla de datos se llama Contoso.

Dado que el origen de datos externos Delta_ED se asigna en un nivel de contenedor, La carpeta de la tabla delta Contoso se encuentra en una raíz. Para consultar un archivo en una estructura de carpetas, proporcione una asignación de carpetas relativa al parámetro LOCATION del origen de datos externos.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Consulta de datos con una tabla externa

CREATE EXTERNAL TABLE también se puede usar para virtualizar los datos de una tabla delta en SQL Server. Las columnas deben estar definidas y fuertemente tipadas. Aunque las tablas externas tardan más en crearse, también proporcionan ventajas adicionales sobre la consulta de un origen de datos externos con OPENROWSET. Puede:

  • Reforzar la definición de los datos que se escriben para una columna determinada
  • Definir la nulabilidad
  • Definir la intercalación
  • Crear estadísticas para una columna a fin de optimizar la calidad del plan de consulta
  • Crear un modelo más granular en SQL Server para el acceso a los datos a fin de mejorar el modelo de seguridad

Para obtener más información, consulte CREATE EXTERNAL TABLE.

En el ejemplo siguiente, se usa el mismo origen de datos.

1. Creación del formato de archivos externos

Para definir el formato de archivo, se requiere un formato de archivos externos. También se recomiendan formatos de archivo externos debido a su capacidad de reutilización. Para más información, consulte CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Creación de una tabla externa

Los archivos de las tablas delta se encuentran en /delta/Delta_yob/ y el origen de datos externos de este ejemplo es el almacenamiento de objetos compatible con S3, configurado anteriormente en el origen de datos s3_eds. PolyBase puede usar como LOCATION la carpeta de la tabla delta o el propio archivo absoluto, que se ubicaría en delta/Delta_yob/_delta_log/00000000000000000000.json.

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Limitaciones

Si crea una tabla externa que apunte a la tabla delta particionada, la columna utilizada para la partición devuelve NULL al consultar la tabla externa. Sin embargo, si usa una consulta OPENROWSET, el valor de columna se devuelve correctamente. Para solucionar esta limitación, cree una vista en la consulta OPENROWSET y, a continuación, consulte la vista para obtener los valores de columna particionados para que se devuelvan correctamente.

Es posible que encuentres los siguientes errores al consultar una tabla Delta externa:

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

Esto puede ocurrir porque hay una sugerencia de consulta QUERYTRACEON que se puede agregar a la consulta de metadatos del archivo Delta y que requiere que se ejecute el rol de servidor sysadmin. Si esto ocurre, puedeS resolver el problema si habilitas de manera global la marca de seguimiento 14073; esto impedirá que se agregue la sugerencia de consulta.

Contenido relacionado