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

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

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

Requisitos previos

Si no ha instalado PolyBase, consulte Instalación de PolyBase en Windows. En el artículo de instalación se explican los requisitos previos.

SQL Server 2022

En SQL Server 2022 (16.x), configure los orígenes de datos externos para usar nuevos conectores al conectarse a Azure Storage. En la tabla siguiente se resumen los cambios:

Origen de datos externo	De	En
Azure Blob Storage	wasb[s]	abs
ADLS Gen2	abfs[s]	adls

Configuración de la conectividad de Azure Blob Storage

En primer lugar, configure SQL Server PolyBase para usar Azure Blob Storage.

1. Reinicie SQL Server con services.msc. Al reiniciar SQL Server, se reinician estos servicios:

   • Servicio de movimiento de datos de SQL Server PolyBase
   • Motor de SQL Server PolyBase

   

Configuración de una tabla externa

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

1. Cree una clave maestra de base de datos (DMK) en la base de datos. La DMK es necesaria para cifrar el secreto de credencial.

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>';
   

2. Cree una credencial de ámbito de base de datos para Azure Blob Storage mediante una firma de acceso compartido (SAS); IDENTITY puede ser cualquier cosa, ya que no se usa.

   CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
   WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
        -- Remove ? from the beginning of the SAS token
        SECRET = '<azure_shared_access_signature>';
   

3. Cree un origen de datos externo con CREATE EXTERNAL DATA SOURCE. Al conectarse a Azure Storage mediante el conector WASB[s], la autenticación con una firma de acceso compartido (SAS).

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

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

   -- FORMAT TYPE: Type of format in Hadoop (DELIMITEDTEXT,  RCFILE, ORC, PARQUET).
   CREATE EXTERNAL FILE FORMAT TextFileFormat
   WITH (
       FORMAT_TYPE = DELIMITEDTEXT,
       FORMAT_OPTIONS (FIELD_TERMINATOR = '|', USE_TYPE_DEFAULT = TRUE)
   );
   

5. 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 del sensor del automóvil; LOCATION no puede ser /, sino /Demo/, ya que en este ejemplo no tiene que estar creado de antes.

   -- 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 (
       DATA_SOURCE = AzureStorage,
       LOCATION = '/Demo/',
       FILE_FORMAT = TextFileFormat
   );
   

6. 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 Hadoop. Selecciona los clientes que conducen a más de 35 mph, y combina los datos estructurados del cliente almacenados en SQL Server con datos de sensor de vehículo almacenados en Hadoop.

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
OPTION (FORCE EXTERNALPUSHDOWN); -- or OPTION (DISABLE EXTERNALPUSHDOWN)

Importación de datos con PolyBase

La consulta siguiente importa datos externos en SQL Server. En este ejemplo se importan los datos relativos a los conductores más rápidos en SQL Server para hacer un análisis más profundo. Para mejorar el rendimiento, usa la tecnología columnstore.

SELECT DISTINCT Insured_Customers.FirstName,
                Insured_Customers.LastName,
                Insured_Customers.YearlyIncome,
                Insured_Customers.MaritalStatus
INTO Fast_Customers
FROM Insured_Customers
    INNER JOIN (SELECT *
                FROM CarSensor_Data
                WHERE Speed > 35
    ) AS SensorD
        ON Insured_Customers.CustomerKey = SensorD.CustomerKey
ORDER BY YearlyIncome;

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FastCustomers
ON Fast_Customers;

Exportación de datos con PolyBase

La consulta siguiente exporta datos de SQL Server a Azure Blob Storage. En primer lugar, habilite la exportación de PolyBase. Luego, cree una tabla externa para el destino antes de exportar los datos a ella.

-- Enable INSERT into external table
EXECUTE sp_configure 'allow polybase export', 1;
RECONFIGURE;
GO

-- Create an external table.
CREATE EXTERNAL TABLE [dbo].[FastCustomers2009]
(
    FirstName CHAR (25) NOT NULL,
    LastName CHAR (25) NOT NULL,
    YearlyIncome FLOAT NULL,
    MaritalStatus CHAR (1) NOT NULL
)
WITH (
    DATA_SOURCE = HadoopHDP2,
    LOCATION = '/old_data/2009/customerdata',
    FILE_FORMAT = TextFileFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0
);

-- Export data: Move old data to Hadoop while keeping it query-able via an external table.
INSERT INTO dbo.FastCustomer2009
SELECT T.*
FROM Insured_Customers AS T1
     INNER JOIN CarSensor_Data AS T2
         ON (T1.CustomerKey = T2.CustomerKey)
WHERE T2.YearMeasured = 2009
      AND T2.Speed > 40;

La exportación de PolyBase con este método podría crear varios archivos.

Ver objetos PolyBase en SSMS

En SSMS, 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.

Contenido relacionado

• Referencia de Transact-SQL de PolyBase
• Grupos de escalado horizontal de PolyBase
• Supervisión y solución de problemas de PolyBase
• CREAR ORIGEN DE DATOS EXTERNO (Transact-SQL)