Introducción a PolyBase en SQL Server 2022

  • Artículo

Se aplica a: SQL Server 2016 (13.x): Windows y versiones posteriores SQL Server 2017 (14.x): Linux y versiones posteriores

Este artículo le guía a través de un tutorial sobre cómo trabajar con varias carpetas y archivos con PolyBase en SQL Server 2022 (16.x). En este conjunto de consultas de tutorial se muestran varias características de PolyBase.

La virtualización de datos con PolyBase en SQL Server permite aprovechar las funciones de archivo de metadatos para consultar varias carpetas o archivos, o realizar la eliminación de carpetas. La combinación de detección de esquemas con la eliminación de carpetas y archivos es una potente funcionalidad que permite a SQL capturar solo los datos necesarios de cualquier cuenta de Azure Storage o solución de almacenamiento de objetos compatible con S3.

Requisitos previos

Antes de usar PolyBase en este tutorial, debe hacer lo siguiente:

  1. Instale PolyBase en Windows o instale PolyBase en Linux.
  2. Habilite PolyBase en sp_configure si es necesario.
  3. Permita el acceso a la red externa para acceder a Azure Blob Storage disponible públicamente en pandemicdatalake.blob.core.windows.net y azureopendatastorage.blob.core.windows.net.

Conjuntos de datos de ejemplo

Si es primera vez que utiliza la virtualización de datos y quiere probar rápidamente la funcionalidad, empieza por consultar conjuntos de datos públicos en Azure Open Datasets, como el conjunto de datos Bing COVID-19, que permite un acceso anónimo.

Use los puntos de conexión siguientes para consultar los conjuntos de datos Bing COVID-19:

  • Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Para empezar rápido, ejecute esta consulta de T-SQL sencilla para obtener la primera información sobre el conjunto de datos. Esta consulta usa OPENROWSET para consultar un archivo almacenado en una cuenta de almacenamiento disponible públicamente:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet', 
 FORMAT = 'parquet' 
) AS filerows;

Puede continuar la exploración del conjunto de datos anexando WHERE, GROUP BY y otras cláusulas de T-SQL basadas en el conjunto de resultados de la primera consulta.

Si se produce un error en la primera consulta en la instancia de SQL Server, es probable que se impida el acceso a la red a la cuenta de almacenamiento de Azure pública. Hable con su experto en redes para habilitar el acceso a fin de poder continuar con la consulta.

Una vez que se haya familiarizado con la consulta de conjuntos de datos públicos, considere la posibilidad de cambiar a conjuntos de datos no públicos que requieren proporcionar credenciales, conceder derechos de acceso y configurar reglas de firewall. En muchos escenarios reales, operará principalmente con conjuntos de datos privados.

Origen de datos externo

Un origen de datos externo es una abstracción que permite hacer referencia fácilmente a una ubicación de archivo en varias consultas. Para consultar ubicaciones públicas, lo único que debe especificar al crear un origen de datos externo es la ubicación del archivo:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
);

Nota:

Si recibe un mensaje de error 46530, External data sources are not supported with type GENERIC, active la opción de configuración PolyBase Enabled en la instancia de SQL Server. Debe ser 1.

Ejecute lo siguiente para habilitar PolyBase en la instancia de SQL Server:

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

Al acceder a cuentas de almacenamiento no públicas, junto con la ubicación, también debe hacer referencia a una credencial con ámbito de base de datos con parámetros de autenticación encapsulados. El script siguiente crea un origen de datos externo que apunta a la ruta de acceso del archivo y hace referencia a una credencial con ámbito de base de datos.

--Create external data source pointing to the file path, and referencing database-scoped credential: 
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

Consulta de orígenes de datos mediante OPENROWSET

La sintaxis de OPENROWSET permite realizar consultas ad hoc instantáneas a la vez que solo se crea la cantidad mínima necesaria de objetos de base de datos.

OPENROWSET solo requiere crear el origen de datos externo (y, posiblemente, la credencial), a diferencia del enfoque de tabla externa, que requiere un formato de archivo externo y la tabla externa misma.

El valor del parámetro DATA_SOURCE se antepone automáticamente al parámetro BULK para formar la ruta de acceso completa al archivo.

Cuando utilice OPENROWSET, proporcione el formato del archivo, como en el ejemplo siguiente, en el que se consulta un archivo único:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.parquet', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Consulta de varios archivos y carpetas

El comando OPENROWSET también permite consultar varios archivos o carpetas mediante el uso de caracteres comodín en la ruta de acceso BULK.

En el ejemplo siguiente, se usa el conjunto de datos abierto de registros de viajes NY Yellow Taxi:

En primer lugar, cree el origen de datos externo:

--Create the data source first
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource 
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Ahora podemos consultar todos los archivos con la extensión .parquet en carpetas. Por ejemplo, aquí solo consultaremos los archivos que coincidan con un patrón de nombre:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Cuando se consultan varios archivos o carpetas, todos los archivos a los que se accede con OPENROWSET único deben tener la misma estructura (como la misma cantidad de columnas y tipos de datos). Las carpetas no se pueden recorrer de manera recursiva.

Inferencia de esquemas

La inferencia automática de esquemas ayuda a escribir rápidamente consultas y a explorar los datos cuando no se conocen los esquemas de archivo. La inferencia de esquemas solo funciona con los archivos de formato Parquet.

Aunque es conveniente, los tipos de datos inferidos pueden ser mayores que los tipos de datos reales, ya que podría haber suficiente información en los archivos de origen para asegurarse de que se usa el tipo de datos adecuado. Esto puede provocar un rendimiento deficiente de las consultas. Por ejemplo, los archivos Parquet no contienen metadatos sobre la longitud máxima de la columna de caracteres, por lo que la instancia se infiere como varchar(8000).

Utilice el procedimiento almacenado sys.sp_describe_first_results_set para comprobar los tipos de datos resultantes de su consulta, como en el ejemplo siguiente:

EXEC sp_describe_first_result_set N'
 SELECT 
 vendorID, tpepPickupDateTime, passengerCount 
 FROM 
 OPENROWSET( 
  BULK ''yellow/*/*/*.parquet'', 
  DATA_SOURCE = ''NYCTaxiExternalDataSource'', 
  FORMAT=''parquet'' 
 ) AS nyc';

Una vez que conoce los tipos de datos, puede especificarlos con la cláusula WITH para mejorar el rendimiento:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount 
FROM 
 OPENROWSET( 
  BULK 'yellow/*/*/*.parquet', 
  DATA_SOURCE = 'NYCTaxiExternalDataSource', 
  FORMAT='PARQUET' 
 ) 
WITH ( 
 vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000 
 tpepPickupDateTime datetime2, 
 passengerCount int 
 ) AS nyc;

Como el esquema de los archivos CSV no se puede determinar automáticamente, debe especificar siempre las columnas con la cláusula WITH:

SELECT TOP 10 id, updated, confirmed, confirmed_change 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.csv', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'CSV', 
 FIRSTROW = 2 
) 
WITH ( 
 id int, 
 updated date, 
 confirmed int, 
 confirmed_change int 
) AS filerows;

Funciones de metadatos del archivo

Al consultar varios archivos o carpetas, puede usar las funciones filepath() y filename() para leer los metadatos de los archivos y obtener parte de la ruta de acceso (o la ruta de acceso completa) y el nombre del archivo del que se origina la fila del conjunto de resultados. En el ejemplo siguiente, consulte todos los archivos, la ruta de acceso del archivo del proyecto y la información de nombre de archivo para cada fila:

--Query all files and project file path and file name information for each row: 

SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder], 
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows;

  • Cuando se la llama sin ningún parámetro, la función filepath() devuelve la ruta de acceso al archivo del que se origina la fila. Cuando se usa DATA_SOURCE en OPENROWSET, devuelve la ruta de acceso relativa a DATA_SOURCE; de lo contrario, devuelve la ruta de acceso completa al archivo.

  • Cuando se llama con un parámetro, la función filepath() devuelve la parte de la ruta de acceso que coincide con el carácter comodín en la posición especificada del parámetro. Por ejemplo, el valor del primer parámetro devolvería la parte de la ruta de acceso que coincide con el primer carácter comodín.

La función filepath() también se puede usar para filtrar y agregar filas:

SELECT 
 r.filepath() AS filepath 
 ,r.filepath(1) AS [year] 
 ,r.filepath(2) AS [month] 
 ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
DATA_SOURCE = 'NYCTaxiExternalDataSource', 
FORMAT = 'parquet' 
 ) AS r 
WHERE 
 r.filepath(1) IN ('2017') 
 AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
 r.filepath() 
 ,r.filepath(1) 
 ,r.filepath(2) 
ORDER BY 
 filepath;

Creación de una vista sobre OPENROWSET

Puede crear vistas para encapsular las consultas OPENROWSET a fin de reutilizar fácilmente la consulta subyacente. Las vistas también permiten que herramientas de informes y análisis, como Power BI, consuman los resultados de OPENROWSET.

Por ejemplo, considere la siguiente vista basada en un comando OPENROWSET:

CREATE VIEW TaxiRides AS 
SELECT * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

También resulta conveniente agregar columnas con los datos de ubicación del archivo a una vista mediante la función filepath() para lograr un filtrado más sencillo y con mejor rendimiento. El uso de las vistas puede disminuir el número de archivos y la cantidad de datos que la consuma sobre la vista debe leer y procesar cuando se filtra en función de cualquiera de esas columnas:

CREATE VIEW TaxiRides AS 
SELECT * 
 , filerows.filepath(1) AS [year] 
 , filerows.filepath(2) AS [month] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Tablas externas

Las tablas externas encapsulan el acceso a los archivos, lo que hace que la experiencia de consulta sea casi idéntica a la consulta de datos relacionales almacenados en tablas de usuario. Para crear una tabla externa, se requiere el origen de datos externo y los objetos de formato de archivo externo:

--Create external file format 
CREATE EXTERNAL FILE FORMAT DemoFileFormat 
WITH ( 
 FORMAT_TYPE=PARQUET 
) 
GO 
 
--Create external table: 
CREATE EXTERNAL TABLE tbl_TaxiRides( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
);

Una vez que se crea la tabla externa, puede consultarla tal como lo haría con cualquier otra tabla:

SELECT TOP 10 * 
FROM tbl_TaxiRides;

Al igual que OPENROWSET, las tablas externas permiten consultar varios archivos y carpetas mediante el uso de caracteres comodín. No se admite la inferencia de esquemas con tablas externas.

Orígenes de datos externos

Consulte más tutoriales sobre cómo crear orígenes de datos externos y tablas externas en varios orígenes de datos en Referencia de Transact-SQL de PolyBase.

Para obtener más tutoriales sobre varios orígenes de datos externos, consulte:

Contenido relacionado