Configurar o PolyBase para acessar dados externos no Hadoop

  • Artigo

Aplica-se a:SQL Server – Somente Windows Instância Gerenciada de SQL do Azure

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

Observação

A partir do SQL Server 2022 (16.x), o Hadoop não é mais compatível com o PolyBase.

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.
  • O PolyBase é compatível com dois provedores de Hadoop, HDP (Hortonworks Data Platform) e CDH (Cloudera Distributed Hadoop). O Hadoop segue o padrão "Principal.Secundária.Versão" para suas novas versões, e há suporte para todas as versões em uma versão Principal e Secundária com suporte. Para obter informações sobre versões do HDP (Hortonworks Data Platform) e CDH (Cloudera Distributed Hadoop) com suporte, confira Configuração de conectividade do PolyBase.

Observação

O PolyBase é compatível com as zonas de criptografia do Hadoop, começando com o SQL Server 2016 SP1 CU7 e o SQL Server 2017 CU3. Caso esteja usando os grupos de escala horizontal do PolyBase, todos os nós de computação também deverão estar em um build que tenha suporte para zonas de criptografia do Hadoop.

Configurar a conectividade do Hadoop

Primeiro, configure o PolyBase do SQL Server para usar o provedor específico do Hadoop.

  1. Execute sp_configure com “conectividade do hadoop” e defina um valor adequado para seu provedor. Para encontrar o valor de seu provedor, consulte 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. É necessário reiniciar 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

    stop and start PolyBase services in services.msc

Habilitar a computação de pushdown

Para melhorar o desempenho de consulta, habilite a computação de aplicação para seu cluster do Hadoop:

  1. Localize o arquivo yarn-site.xml no caminho de instalação do SQL Server. Normalmente, o caminho é:

    C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\PolyBase\Hadoop\conf\

  2. No computador do Hadoop, localize o arquivo análogo no diretório da configuração do Hadoop. No arquivo, localize e copie o valor da chave de configuração yarn.application.classpath.

  3. No computador do SQL Server, no yarn-site.xml file, localize a propriedade yarn.application.classpath. Cole o valor do computador do Hadoop no elemento de valor.

  4. Para todas as versões do CDH 5.X, você precisará adicionar os parâmetros de configuração mapreduce.application.classpath ao final do arquivo yarn-site.xml ou ao arquivo mapred-site.xml. O HortonWorks inclui essas configurações nas configurações yarn.application.classpath. Veja Configuração do PolyBase para obter exemplos.

Importante

Para usar a funcionalidade de pushdown de computação no Hadoop, o cluster do Hadoop de destino deve ter os componentes principais do HDFS, YARN e MapReduce, com o servidor de histórico de trabalhos habilitado. O PolyBase envia a consulta de aplicação via MapReduce e recebe o status do servidor de histórico de trabalhos. A consulta falhará se não tiver um desses componentes.

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, caso ainda não exista. Isso é necessário para criptografar o segredo da credencial. 

     CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

    Argumentos

    PASSWORD ='password'

    É a senha usada para criptografar a chave mestra no banco de dados. password precisa atender aos requisitos da política de senha do Windows do computador que está hospedando a instância do SQL Server.

  2. Crie uma credencial com escopo do banco de dados para clusters do Hadoop protegidos por Kerberos.

    -- IDENTITY: the Kerberos user name.  
-- SECRET: the Kerberos password  
CREATE DATABASE SCOPED CREDENTIAL HadoopUser1
WITH IDENTITY = '<hadoop_user_name>', Secret = '<hadoop_password>';

  3. Crie uma fonte de dados externa, usando CREATE EXTERNAL DATA SOURCE.

    -- LOCATION (Required) : Hadoop Name Node IP address and port.  
-- RESOURCE MANAGER LOCATION (Optional): Hadoop Resource Manager location to enable pushdown computation.  
-- CREDENTIAL (Optional):  the database scoped credential, created above.  
CREATE EXTERNAL DATA SOURCE MyHadoopCluster WITH (  
      TYPE = HADOOP,
      LOCATION ='hdfs://10.xxx.xx.xxx:xxxx',
      RESOURCE_MANAGER_LOCATION = '10.xxx.xx.xxx:xxxx',
      CREDENTIAL = HadoopUser1
);

  4. Crie um formato de arquivo externo com 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. Crie uma tabela externa que aponta para dados armazenados no Hadoop 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 = MyHadoopCluster,  
      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.

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 56 km/h, unindo dados estruturados do cliente armazenados no SQL Server com os dados do sensor do carro armazenados no 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)

Importar dados

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 aprimorar o desempenho, o exemplo usa um índice 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;

Exportar dados

A consulta a seguir exporta dados do SQL Server para o Hadoop. Para fazer isso, primeiro você precisa habilitar a exportação do PolyBase. Em seguida, crie uma tabela externa para o destino antes de exportar dados para ele.

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

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.

PolyBase objects in SSMS

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: