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.

Observação

O APS atualmente oferece suporte somente ao Armazenamento de Blobs do Azure localmente redundante (LRS) de uso geral v1 padrão.

Pré-requisitos

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

Configure a conectividade do Armazenamento de Blobs do Azure

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

1. Execute sp_configure com "conectividade do hadoop" definido como um provedor do Armazenamento de Blobs do Azure. Para encontrar o valor dos provedores, consulte Configuração de conectividade do PolyBase.

   SQL

   -- 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 APS usando a página Status do serviço no Appliance Configuration Manager.

Configurar uma tabela externa

Para consultar os dados na fonte de dados do Armazenamento de Blobs do Azure, defina uma tabela externa para usar nas consultas Transact-SQL. As etapas a seguir descrevem como configurar a tabela externa.

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

   SQL

   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.

   SQL

   -- 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, usando CREATE EXTERNAL DATA SOURCE.

   SQL

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

   SQL

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

   SQL

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

   SQL

   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.

Consulta ad hoc

A consulta ad hoc a seguir associa os dados relacionais aos dados do Armazenamento de Blobs do Azure. Ela seleciona clientes que dirigem a uma velocidade superior a 56 km/h, integrando os dados estruturados do cliente armazenados no SQL Server aos dados do sensor do carro armazenados no Armazenamento de Blobs do Azure.

SQL

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 o APS. Esse exemplo importa dados de motoristas velozes para o APS para fazer uma análise mais detalhada. Para melhorar o desempenho, ela usa a tecnologia Columnstore no APS.

SQL

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 Armazenamento de Blobs do Azure. Ela pode ser usada para arquivar dados relacionais no Armazenamento de Blobs do Azure e pode continuar a consultá-los.

SQL

-- 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 do PolyBase no SSDT

No SQL Server Data Tools (SSDT), 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

Apresentação da virtualização de dados com o PolyBase