Configuración de PolyBase para acceder a datos externos en Azure Blob Storage

Artículo
12/11/2023

En el artículo se explica cómo usar PolyBase en una instancia de SQL Server para consultar datos externos en Azure Blob Storage.

Nota:

APS actualmente solo admite la versión 1 de Azure Blob Storage de uso general estándar con redundancia local (LRS).

Requisitos previos

Azure Blob Storage en su suscripción.
Un contenedor creado en Azure Blob Storage.

Configuración de la conectividad de Azure Blob Storage

En primer lugar, configure APS para usar Azure Blob Storage.

Ejecute sp_configure con "hadoop connectivity" establecido en un proveedor de Azure Blob Storage. Para buscar el valor de los proveedores, consulte Configuración de la conectividad de PolyBase.

-- Values map to various external data sources.  
-- Example: value 7 stands for Hortonworks HDP 2.1 to 2.6 on Linux,
-- 2.1 to 2.3 on Windows Server, and Azure Blob Storage  
sp_configure @configname = 'hadoop connectivity', @configvalue = 7;
GO

RECONFIGURE
GO

Reinicie la región de APS mediante la página Estado del servicio en Configuration Manager del dispositivo.

Configuración de una tabla externa

Para consultar los datos en Azure Blob Storage, debe definir una tabla externa para usar en las consultas de Transact-SQL. Los pasos siguientes describen cómo configurar la tabla externa.

Cree una clave maestra en la base de datos. Esto es necesario para cifrar el secreto de credencial.
```
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';  
```

Cree una credencial de ámbito de base de datos para Azure Blob Storage.

-- IDENTITY: any string (this is not used for authentication to Azure storage).  
-- SECRET: your Azure storage account key.  
CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = 'user', Secret = '<azure_storage_account_key>';

Cree un origen de datos externo con CREATE EXTERNAL DATA SOURCE.

-- LOCATION:  Azure account storage account name and blob container name.  
-- CREDENTIAL: The database scoped credential created above.  
CREATE EXTERNAL DATA SOURCE AzureStorage with (  
      TYPE = HADOOP,
      LOCATION ='wasbs://<blob_container_name>@<azure_storage_account_name>.blob.core.windows.net',  
      CREDENTIAL = AzureStorageCredential  
);

Cree un formato de archivo externo con CREATE EXTERNAL FILE FORMAT.

-- FORMAT TYPE: Type of format in Azure Blob Storage (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
-- In this example, the files are pipe (|) delimited
CREATE EXTERNAL FILE FORMAT TextFileFormat WITH (  
      FORMAT_TYPE = DELIMITEDTEXT,
      FORMAT_OPTIONS (FIELD_TERMINATOR ='|',
            USE_TYPE_DEFAULT = TRUE)

Cree una tabla externa que señale a los datos almacenados en Azure Storage con CREATE EXTERNAL TABLE. En este ejemplo, los datos externos contienen datos de sensor de vehículo.

-- LOCATION: path to file or directory that contains the data (relative to HDFS root).  
CREATE EXTERNAL TABLE [dbo].[CarSensor_Data] (  
      [SensorKey] int NOT NULL,
      [CustomerKey] int NOT NULL,
      [GeographyKey] int NULL,
      [Speed] float NOT NULL,
      [YearMeasured] int NOT NULL  
)  
WITH (LOCATION='/Demo/',
      DATA_SOURCE = AzureStorage,  
      FILE_FORMAT = TextFileFormat  
);

Cree estadísticas en una tabla externa.

CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)

Consultas de PolyBase

PolyBase es adecuado para realizar tres funciones:

Consultas ad hoc en tablas externas.
Importar datos.
Exportar datos.

Las siguientes consultas proporcionan un ejemplo con datos de sensor de vehículo ficticios.

Consultas ad hoc

La siguiente consulta ad hoc combina datos relacionales con datos de Azure Blob Storage. Selecciona los clientes que conducen a más de 35 mph, combinando los datos estructurados del cliente almacenados en SQL Server con los datos del sensor de vehículo almacenados en Azure Blob Storage.

SELECT DISTINCT Insured_Customers.FirstName,Insured_Customers.LastName,
       Insured_Customers. YearlyIncome, CarSensor_Data.Speed  
FROM Insured_Customers, CarSensor_Data  
WHERE Insured_Customers.CustomerKey = CarSensor_Data.CustomerKey and CarSensor_Data.Speed > 35
ORDER BY CarSensor_Data.Speed DESC

Importación de datos

La consulta siguiente importa datos externos en APS. En este ejemplo se importan los datos relativos a los controladores más rápidos en APS para llevar a cabo un análisis más profundo. Para mejorar el rendimiento, aprovecha la tecnología Columnstore en APS.

CREATE TABLE Fast_Customers
WITH
(CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH (CustomerKey))
AS
SELECT DISTINCT
      Insured_Customers.CustomerKey, Insured_Customers.FirstName, Insured_Customers.LastName,   
      Insured_Customers.YearlyIncome, Insured_Customers.MaritalStatus  
from Insured_Customers INNER JOIN   
(  
      SELECT * FROM CarSensor_Data where Speed > 35   
) AS SensorD  
ON Insured_Customers.CustomerKey = SensorD.CustomerKey

Exportación de datos

La consulta siguiente exporta datos de APS a Azure Blob Storage. Se puede usar para archivar datos relacionales en Azure Blob Storage y, a la vez, seguir pudiendo consultarlos.

-- Export data: Move old data to Azure Blob Storage while keeping it query-able via an external table.  
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009] 
WITH (  
      LOCATION='/archive/customer/2009',  
      DATA_SOURCE = AzureStorage,  
      FILE_FORMAT = TextFileFormat
)  
AS
SELECT T.* FROM Insured_Customers T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;

Visualización de objetos PolyBase en SSDT

En SQL Server Data Tools (SSDT), las tablas externas se muestran en una carpeta independiente llamada Tablas externas. Los orígenes de datos y los formatos de archivo externos se encuentran en subcarpetas de Recursos externos.

A screenshot of PolyBase objects in the object explorer of SSDT.

Paso siguiente

Introducción a la virtualización de datos con PolyBase

Compartir vía