Virtualización de un archivo CSV 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 archivos CSV. 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, el archivo CSV se almacena en Azure Blob Storage y se accede a él 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 CSV_Demo.

CREATE DATABASE [CSV_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, blob_storage.

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

CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';

4. Creación de un origen de datos externos

La credencial con ámbito de base de datos se usa para el origen de datos externos. En este ejemplo, el archivo CSV reside en Azure Blob Storage, por lo que debe usar el prefijo abs 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 Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

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

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)

Uso de OPENROWSET para acceder a los datos

En este ejemplo, el archivo se denomina call_center.csv y los datos comienzan en la segunda fila.

Dado que el origen de datos externos Blob_CSV se asigna en un nivel de contenedor, call_center.csv se encuentra en una subcarpeta denominada 2022 en la raíz del contenedor. 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 '/2022/call_center.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = 'Blob_CSV',
    FIRSTROW = 2
)
WITH (
    cc_call_center_sk INT,
    cc_call_center_id CHAR(16),
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(40),
    cc_division INT,
    cc_division_name VARCHAR(50),
    cc_company INT,
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(2),
    cc_zip CHAR(10),
    cc_country VARCHAR(20),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
) AS [cc];

Consulta de datos con una tabla externa

CREATE EXTERNAL TABLE también se puede usar para virtualizar los datos en formato CSV 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.

En el ejemplo siguiente, los datos comienzan en la segunda fila.

CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2)
);

2. Creación de una tabla externa

LOCATION es la carpeta y la ruta de acceso del archivo call_center.csv en relación con la ruta de la ubicación en el origen de datos externos, que se define mediante DATA_SOURCE. En este caso, el archivo se encuentra en una subcarpeta denominada 2022. Use FILE_FORMAT para especificar la ruta al formato de archivos externos csv_ff en SQL Server.

CREATE EXTERNAL TABLE extCall_Center_csv (
    cc_call_center_sk INT NOT NULL,
    cc_call_center_id CHAR(16) NOT NULL,
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(MAX),
    cc_division VARCHAR(50),
    cc_division_name VARCHAR(50),
    cc_company VARCHAR(60),
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(20),
    cc_zip CHAR(20),
    cc_country VARCHAR(MAX),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
    )
WITH (
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV,
    FILE_FORMAT = csv_ff
);
GO

Contenido relacionado