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

Aplica-se a: SQL Server (somente Windows) Banco de Dados SQL do Azure Azure Synapse Analytics PDW (Analytics Platform System)

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

Se você ainda não instalou o PolyBase, veja Instalação do PolyBase. O artigo sobre a instalação explica os pré-requisitos.

SQL Server 2022

No SQL Server 2022 (16.x), configure suas fontes de dados externas para usar os conectores novos ao se conectar ao Azure Storage. A tabela a seguir resume a alteração:

Fonte de dados externa	De	Para
Armazenamento do Blobs do Azure	wasb[s]	abs
ADLS Gen 2	abfs[s]	adls

Configurar a conectividade do Armazenamento de Blobs do Azure

Primeiro, configure o PolyBase do SQL Server para usar o Armazenamento de Blobs do Azure.

1. Reinicie o SQL Server usando services.msc. A reinicialização do o SQL Server reiniciará estes serviços:

   • Serviço de movimentação de dados de PolyBase do SQL Server
   • Mecanismo PolyBase do SQL Server

   

Configurar uma tabela externa

Para consultar os dados em sua fonte de dados do Hadoop, você precisa definir uma tabela externa para usar em consultas Transact-SQL. As etapas a seguir descrevem como configurar a tabela externa.

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

   SQL

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

2. Crie uma credencial com escopo de banco de dados para o Armazenamento de Blobs do Azure usando uma assinatura de acesso compartilhado (SAS). A IDENTITY pode ser qualquer coisa, pois não é usada.

   SQL

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

3. Crie uma fonte de dados externa, usando CREATE EXTERNAL DATA SOURCE. Observe que, ao se conectar ao Azure Storage por meio do conector WASB[s], a autenticação deve ser feita com uma assinatura de acesso compartilhado (SAS).

   SQL

   -- 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. Crie um formato de arquivo externo com CREATE EXTERNAL FILE FORMAT.

   SQL

   -- 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. Crie uma tabela externa que aponta para dados armazenados no armazenamento do Azure com CREATE EXTERNAL TABLE. Neste exemplo, os dados externos contêm dados de sensor de carros; LOCATION não pode ser /, mas /Demo/, como neste exemplo, não precisa existir anteriormente.

   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 dados relacionais aos dados do Hadoop. Ela seleciona clientes que dirigem mais rápido do que 55 km/h e reúne dados estruturados do cliente armazenados no SQL Server com os dados de sensor de carro armazenados no Hadoop.

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

Importar dados com o PolyBase

A consulta a seguir importa dados externos para o SQL Server. Este exemplo importa dados de motoristas velozes para o SQL Server para fazer uma análise mais detalhada. Para melhorar o desempenho, ela aproveita a tecnologia de Columnstore.

SQL

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;  

Exportar dados com o PolyBase

A consulta a seguir exporta dados do SQL Server para Armazenamento de Blobs do Azure. Primeiro, habilite a exportação do PolyBase. Em seguida, crie uma tabela externa para o destino antes de exportar dados para ele.

SQL

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

A exportação do PolyBase com esse método pode criar vários arquivos.

Exibir objetos do PolyBase no SSMS

No SSMS, 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óximas etapas

Para obter mais tutoriais sobre como criar fontes de dados externas e tabelas externas para uma variedade de fontes de dados, consulte Referência do PolyBase Transact-SQL.

Explore mais maneiras de usar e monitorar o PolyBase nos seguintes artigos:

• Grupos de expansão do PolyBase.
• Solução de problemas do PolyBase.
• CREATE EXTERNAL DATA SOURCE