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

Se aplica a: SQL Server (solo Windows) Azure SQL Database Azure Synapse Analytics Analytics Platform System (PDW)

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 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 resume el cambio:

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 en la base de datos. Se necesita la clave maestra 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. Tenga en cuenta que al conectarse a la instancia de Azure Storage mediante el conector WASB, la autenticación debe realizarse 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 (LOCATION='/Demo/',
         DATA_SOURCE = AzureStorage,  
         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, aprovecha 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  
sp_configure 'allow polybase export', 1;  
reconfigure  
  
-- 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 (  
      LOCATION='/old_data/2009/customerdata',  
      DATA_SOURCE = HadoopHDP2,  
      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 T1 JOIN CarSensor_Data T2  
ON (T1.CustomerKey = T2.CustomerKey)  
WHERE T2.YearMeasured = 2009 and T2.Speed > 40;  

La exportación de PolyBase con este método puede 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.

Pasos siguientes

Para obtener más tutoriales sobre cómo crear orígenes de datos externos y tablas externas en una variedad de orígenes de datos, consulte Referencia de Transact-SQL de PolyBase.

Descubra más formas de usar y supervisar PolyBase en los siguientes artículos:

• Grupos de escalabilidad horizontal de PolyBase.
• Solución de problemas de PolyBase.
• CREATE EXTERNAL DATA SOURCE