Ejercicio: Uso de PolyBase para consultar un archivo Parquet

10 minutos

En este ejercicio tú:

Instale y habilite PolyBase.
Crear una base de datos.
Cree una clave maestra de base de datos para asegurar la credencial de ámbito de base de datos.
Cree una credencial con ámbito de base de datos para acceder a la fuente de datos.
Cree el origen de datos.
Consulte y manipule los datos almacenados en el origen de datos público.
Cree un formato de archivo externo y una tabla externa.

Instalación de PolyBase

Puede instalar PolyBase con el ejecutable de instalación de SQL Server durante la instalación inicial o agregarlo como una característica más adelante. En la página Selección de características de SQL Server setup.exe, seleccione Servicio de Consultas de PolyBase para Datos Externos.

Los servicios de PolyBase requieren que los puertos de firewall estén habilitados para conectarse a orígenes de datos externos. De forma predeterminada, PolyBase usa puertos que van de 16450 a 16460.

Imagen del ejecutable de instalación de SQL Server que muestra la configuración del intervalo de puertos de PolyBase.

El programa de instalación de PolyBase instala dos servicios de PolyBase, sql Server PolyBase Engine y movimiento de datos de SQL Server PolyBase. Para obtener información completa y requisitos previos para la instalación de PolyBase, consulte:

Habilitación de PolyBase

Una vez instalado el servicio, conéctese a la instancia de SQL Server 2025 en SQL Server Management Studio (SSMS) y ejecute el siguiente comando para habilitar PolyBase.

EXEC SP_CONFIGURE @CONFIGNAME = N'POLYBASE ENABLED', @CONFIGVALUE = 1;
RECONFIGURE;

Imagen de la habilitación de PolyBase mediante T-SQL en SQL Server Management Studio.

Nota:

En este ejercicio, consultará los archivos de Apache Parquet mediante la API REST de PolyBase, por lo que no es necesario habilitar ni configurar los servicios SQL Server PolyBase Data Movement o SQL Server PolyBase Engine.

Creación de una base de datos

Ejecute el siguiente comando en SSMS para crear una base de datos para este ejercicio denominada Demo1. Si la base de datos ya se creó, el script la elimina y la vuelve a crear.

USE MASTER;

IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'Demo1')
BEGIN
    ALTER DATABASE Demo1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE IF EXISTS Demo1
END;

CREATE DATABASE Demo1;

USE Demo1;

Creación de la clave maestra de la base de datos

Debe crear una clave maestra de base de datos para garantizar la seguridad de las credenciales con ámbito de base de datos. En el ejemplo siguiente se crea la clave con una contraseña generada aleatoriamente y se requiere una copia de seguridad.

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXECUTE sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Para comprender y mantener mejor las claves de cifrado en un entorno de producción, consulte:

Crear la credencial de ámbito de base de datos

La credencial con ámbito de base de datos es responsable de almacenar las credenciales que usa el origen de datos para conectarse al punto de conexión. En este ejemplo se usa un punto de conexión público, por lo que la credencial no necesita un secreto.

IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = N'PublicCredential')
    DROP DATABASE SCOPED CREDENTIAL PublicCredential;
 
CREATE DATABASE SCOPED CREDENTIAL PublicCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<KEY>'; -- This example doesn't need the SECRET because the data source is public

Crear el origen de datos

En este ejemplo se usa un conjunto de datos de COVID en formato Parquet disponible públicamente que se almacena en Azure Blob Storage. Use el elemento PublicCredential con ámbito de base de datos que creó para establecer la conexión.

Valores de ubicación:

Prefijo: abs
Cuenta de Azure Storage: pandemicdatalake
Ruta de acceso completa de la cuenta de Azure Storage: pandemicdatalake.blob.core.windows.net
Nombre del contenedor: public
Ruta de acceso completa del contenedor: public/curated/covid-19/bing_covid-19_data/latest

IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = N'Public_Covid') DROP EXTERNAL DATA SOURCE Public_Covid;
 
CREATE EXTERNAL DATA SOURCE Public_Covid
WITH (
    LOCATION = 'abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest',
    CREDENTIAL = [PublicCredential]
);

Para obtener una lista completa de los orígenes de datos y los prefijos correspondientes, consulte CREATE EXTERNAL DATA SOURCE.
Para más información sobre el conjunto de datos público, consulte Bing COVID-19.

Consulta de los datos con OPENROWSET

Puede usar OPENROWSET para acceder a los datos y explorarlos. OPENROWSET está optimizado para escenarios de exploración de datos y cargas de trabajo ad hoc.

Valores OPENROWSET:

BULK: nombre de archivo y extensión. BULK se agrega automáticamente a la información de la fuente de datos, por lo que la ubicación completa del archivo es abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
FORMATO: PARQUET
DATA_SOURCE: información de conexión, en este caso el nuevo origen de datos Public_Covid

SELECT TOP 1000 *
FROM OPENROWSET 
     (BULK 'bing_covid-19_data.parquet'
     , FORMAT = 'PARQUET'
     , DATA_SOURCE = 'Public_Covid')
     AS [COVID_Dataset]

En el ejemplo siguiente se usa la flexibilidad de T-SQL para consultar el archivo Parquet en tiempo real, al igual que una tabla normal. Para devolver el número de casos confirmados por estado de EE. UU. en orden descendente, ejecute la consulta siguiente:

SELECT [COVID_Dataset].admin_region_1, 
       SUM(CAST([COVID_Dataset].confirmed AS BIGINT)) AS Confirmed
FROM OPENROWSET 
     (BULK 'bing_covid-19_data.parquet'
     , FORMAT = 'PARQUET'
     , DATA_SOURCE = 'Public_Covid')
     AS [COVID_Dataset]
WHERE [COVID_Dataset].country_region = 'United States' AND 
      [COVID_Dataset].admin_region_1  IS NOT NULL
GROUP BY [COVID_Dataset].admin_region_1 
ORDER BY confirmed DESC

Creación y consulta de una tabla externa

OPENROWSET está optimizado para la ejecución ad hoc y la exploración de datos. Las tablas externas son más adecuadas para el acceso periódico, ya que también pueden usar estadísticas.

Detección del esquema de la tabla externa

Para crear una tabla externa, determine primero las columnas y el tipo. El esquema procede de un archivo externo, por lo que puede llevar mucho tiempo determinar con precisión los tipos de datos y los intervalos. Afortunadamente, puede usar el procedimiento almacenado sp_describe_first_result_set (Transact-SQL) para acelerar este proceso.

DECLARE @tsql NVARCHAR(MAX) = 'SELECT TOP 1000 *
FROM OPENROWSET 
    (BULK ''bing_covid-19_data.parquet''
    , FORMAT = ''PARQUET''
    , DATA_SOURCE = ''Public_Covid'')
    AS [COVID_Dataset]';
EXEC sys.sp_describe_first_result_set @tsql;

Imagen de los resultados del esquema de la fuente de datos externa Parquet en SQL Server Management Studio.

Puede ver que sp_describe_first_result_set devuelve los nombres de las columnas, los tipos, la longitud, la precisión e incluso la intercalación de la fuente de datos.

Creación del formato de archivo externo

Debido a que tiene que hacer referencia al archivo Parquet a la tabla externa, primero tiene que ejecutar CREATE EXTERNAL FILE FORMAT para agregar el formato de archivo Parquet. La definición de formato de archivo es importante para las tablas externas porque especifica el diseño real y el tipo de compresión.

Ejecute el siguiente comando:

IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
      DROP EXTERNAL FILE FORMAT ParquetFileFormat;

CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);

Creación de la tabla externa

Por último, con toda la información que acaba de adquirir y el formato de archivo externo creado, puede crear la tabla externa mediante el siguiente script:

IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
      DROP EXTERNAL FILE FORMAT ParquetFileFormat;
 
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
 
-- 8.3 CREATE EXTERNAL TABLE
IF OBJECT_ID(N'ext_covid_data', N'ET') IS NOT NULL
      DROP EXTERNAL TABLE ext_covid_data;
 
CREATE EXTERNAL TABLE ext_covid_data
(
id                            int,
updated                       date,
confirmed               int,
confirmed_change  int,
deaths                        int,
deaths_change           smallint,
recovered               int,
recovered_change  int,
latitude                float,
longitude               float,
iso2                    varchar(8000),
iso3                    varchar(8000),
country_region          varchar(8000),
admin_region_1          varchar(8000),
iso_subdivision         varchar(8000),
admin_region_2          varchar(8000),
load_time                     datetime2(7)
)
WITH
(
LOCATION = 'bing_covid-19_data.parquet'
     , FILE_FORMAT = ParquetFileFormat
     , DATA_SOURCE = Public_Covid
);
 
CREATE STATISTICS [Stats_ext_covid_data_updated] ON ext_covid_data([updated]);
 
SELECT TOP 1000 * FROM ext_covid_data;

Nota:

Los nombres de columna deben coincidir con las columnas almacenadas en el archivo Parquet, o SQL Server no puede identificar las columnas y devuelve NULL.

Después de crear la tabla ext_covid_dataexterna, puede agregar estadísticas en las columnas actualizadas para mejorar la eficacia. Para obtener más información sobre las estadísticas de la tabla externa, vea CREATE STATISTICS (Transact-SQL).

En esta unidad, ha usado PolyBase para conectarse a un origen de datos externo y ha usado OPENROWSET o una tabla externa para consultar el archivo Parquet. En el ejercicio siguiente, usará los servicios de PolyBase para conectarse y crear una tabla externa a partir de una base de datos de Azure SQL Database.

Comentarios

¿Le ha resultado útil esta página?