Configurar o PolyBase para acessar dados externos no Armazenamento de Blobs do Azure

O artigo explica como usar o PolyBase em uma instância do SQL Server para consultar dados externos no Armazenamento de Blobs do Azure.

Pré-requisitos

• Armazenamento de Blobs do Azure em sua assinatura.
• Um contêiner criado no Armazenamento de Blobs do Azure.

Configurar a conexão do Azure Blob Storage

Primeiro, configure o APS para usar o Armazenamento de Blobs do Azure.

1. Execute sp_configure com "conectividade hadoop" definida para um provedor de Armazenamento de Blobs do Azure. Para localizar o valor para provedores, consulte a Configuração de Conectividade do 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
   

2. Reinicie a região do APS usando a página Status do Serviço no Gerenciador de Configurações do Dispositivo.

Configurar uma tabela externa

Para consultar os dados no Armazenamento de Blobs do Azure, você deve definir uma tabela externa a ser usada em consultas Transact-SQL. As etapas a seguir descrevem como configurar a tabela externa.

1. Crie uma chave mestra no banco de dados. É necessário criptografar o segredo da credencial.

   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S0me!nfo';  
   

2. Crie uma credencial com escopo de banco de dados para o Armazenamento de Blobs do Azure.

   -- 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>';
   

3. Crie uma fonte de dados externa com 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  
   );  
   

4. Crie um formato de arquivo externo com 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)  
   

5. Crie uma tabela externa que aponta para dados armazenados no armazenamento do Azure com CREATE EXTERNAL TABLE. Neste exemplo, os dados externos contêm os dados de sensor do carro.

   -- 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. Crie estatísticas em uma tabela externa.

   CREATE STATISTICS StatsForSensors on CarSensor_Data(CustomerKey, Speed)  
   

Consultas do PolyBase

O PolyBase é adequado para três funções:

• Consultas ad hoc em tabelas externas.
• importar dados.
• exportar dados.

As consultas a seguir fornecem exemplo com os dados de sensor de carro fictícios.

Consultas ad hoc

A consulta ad hoc a seguir combina dados relacionais com os dados no Armazenamento de Blobs do Azure. Ele seleciona clientes que dirigem mais de 35 mph, unindo dados estruturados do cliente armazenados no SQL Server com dados do sensor de carro armazenados no Armazenamento de Blobs do Azure.

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  

Importar dados

A consulta a seguir importa dados externos para APS. Este exemplo importa dados de condutores rápidos para o APS, a fim de realizar uma análise mais detalhada. Para melhorar o desempenho, ele utiliza a tecnologia Columnstore na 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  

Exportar dados

A consulta a seguir exporta dados do APS para o Armazenamento de Blobs do Azure. Ele pode ser usado para arquivar dados relacionais no Armazenamento de Blobs do Azure enquanto ainda pode consultá-los.

-- 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;  

Exibir objetos PolyBase no SSDT

No SSDT (SQL Server Data Tools), as tabelas externas são exibidas em uma pasta separada Tabelas Externas. As fontes de dados externas e os formatos de arquivo externos estão em subpastas em Recursos Externos.

Próxima etapa