Lendo Azure Blob Storage através do SQL Server 2016 com T-SQL

Fala galera, o Polybase é mais uma das coisas interessantes que foi lançado no segmento de Big Data e Data Science do SQL Server 2016. Ele nos dá a possibilidade de ler dados diretamente de uma base Hadoop ou de um arquivo armazenado no Azure Blob Storage. Neste post vou mostrar como configurar e disparar consultas escritas em T-SQL de dentro do SQL lendo dados de um arquivo CSV que está no Blob Storage. Lembrando que para isso é necessário que você tenha um subscription ativa no Azure, tanto para replicar este experimento no Azure Blob Storage e/ou um cluster do Hadoop. No meu caso vou usar um arquivo que subi em um contêiner do Azure Blob Storage, mas poderia ser em um cluster do Hadoop dentro do HDInsight que também funcionaria. Num próximo post, quando o tempo permitir, mostrarei funcionando também no HDInsight.

Pré Requisitos e Instalação

Para replicar estas técnicas no seu ambiente, é necessário que tenha baixado o SQL Server 2016 CTP 3 e também o Java Runtime 7. Um ponto importante durante a instalação é que isso que estou mostrando é público com o SQL Server 2016 CTP 3. Pode ser que na versão final do SQL Server 2016 isso seja diferente, mas até o lançamento, dá pra gente usar o recurso desta forma.

Durante a instalação é preciso que você garanta a seleção da instalação do PolyBase Query Services for External Data. Este recurso do SQL 2016 exige o Java Runtime 7 porque o motor do Hadoop roda em cima do Java. Caso você não tenha o Java instalado, ele vai pedir para você instalar e lhe dará um link para fazer o download. Baixe e conclua a instalação do Java antes de terminar a do SQL.

Depois de concluir a instalação, abra o SQL Server 2016 e vamos fazer a configuração do ambiente para permitir os acessos.

Configuração do ambiente

Depois de tudo instalado, é hora de configurar o ambiente para recuperar dados do Hadoop ou Azure Blob Storage e mostrar diretamente no seu SQL 2016. É necessário criar uma base de dados para armazenar a tabela externa que por sua vez receberá os dados. Fique à vontade para criar um banco vazio ou usar um já existente. No meu caso de exemplo, criei um chamado PolybaseAzure.

CREATE DATABASE PolybaseAzure
GO USE PolybaseAzure
GO

Lembra que na instalação você marcou a caixa do Polybase? Então, é hora de habilitar este recurso no seu SQL 2016. Faça isso utilizando o comando T-SQL abaixo. Lembrando que o parametro 7 permite ler um arquivo do Azure Blob Storage e o parametro 6 é para ler do Hadoop (no nosso caso, como é no Azure então é diretamente do HDInsight).

exec sp_configure 'hadoop connectivity', 7
Reconfigure Go

sp_configure
go

Confira se o recurso do Hadoop Connectivity está configurado com o parametro que quer ler (6 ou 7).

Reinicie o SQL Server 2016, e garanta que também reiniciaram os serviços SQL Server PolyBase Engine e SQL Server PolyBase Data Movement Service.

Configurando o Polybase

Depois da configuração macro no servidor, é preciso configurar o Polybase na base de dados para ler os dados externos. Vamos fazer isso agora.

Usaremos aquele banco de dados criado anteriormente para ter objetos que armazenarão as credenciais necessárias para se conectar ao Azure Blob Storage e recuperar os dados de lá. Neste banco de dados ficarão:

• Chave de acesso ao seu Azure Blob Storage
• Origem de dados Externa
• Arquivo Externo

Garanta que está no banco correto, e então execute o código abaixo:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'coloque sua senha aqui'
go

Este código cria uma senha master no banco. Lembrando que esta senha master não é do database de sistema master, é para o banco de dados que você criou (ou está usando um já existente). Não esqueça de colocar uma senha neste código antes de executar.

Depois de criada a senha master, é preciso colocar a chave de autenticação do Storage do Azure, para poder acessar os dados dentro dele. Esta chave fica nas configurações do Blob Storage, acesse o seu portal, vá até o item Storage (Armazenamento) no menu da esquerda e procure o seu Storage. Ao acessar, vá até o painel e no meu inferior procure as chaves de acesso, copie a chave primária.

Com essa chave em mãos, substitua ela no código T-SQL abaixo e execute. Pode deixar o usuário que está como ‘user’ desta forma, não precisa mexer nisso.

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential
WITH IDENTITY = 'user', SECRET = 'chave de segurança do Azure'
GO Select * from sys.database_credentials
GO

Se a configuração funcionou corretamente, você terá um resultado como o abaixo:

O passo seguinte é criar uma origem de dados externa. Faça isso com o código abaixo.

Lembre-se de colocar o nome do container e do storage, sem os sinais de menor “<” e maior “>”. No meu exemplo, o storage tem o nome blognogareml e o contêiner é ssis. A construção do caminho ficou assim: wasbs://ssis@blognogareml.blob.core.windows.net

CREATE EXTERNAL DATA SOURCE AzureStorage
WITH ( TYPE = Hadoop,
LOCATION = 'wasbs://<nome do container>@<nome do storage>.blob.core.windows.net', CREDENTIAL = AzureStorageCredential)
GO select * from sys.external_data_sources
GO

Acompanhe no seu Object Explorer surgir um item dentro do banco de dados PolybaseAzure >> External Resources >> Data Sources. É a origem de dados externa que será consultada.

Da mesma forma que criou a origem de dados externa, vamos criar agora um apontamento para uma estruturação de arquivo. Ajuste o Field Terminator para o caracter que você usou pra separar os seus campos no arquivo que quer ler (neste caso é a tabulação). Execute o código abaixo:

CREATE EXTERNAL FILE FORMAT ArquivoPorTab
WITH ( FORMAT_TYPE = DelimitedText,
FORMAT_OPTIONS (FIELD_TERMINATOR = '\t')) GO

select * from sys.external_file_formats GO

Veja o resultado da execução da consulta na tabela de arquivos externos.

Mais uma vez acompanhe no Object Explorer o objeto que foi criado.

Acessando os dados externos

Para receber estes dados do Hadoop ou do Azure Blob Storage, é necessário criar uma tabela que faça a referência externa necessária. Complete o código abaixo com o seu caminho do arquivo do Blog Storage e execute. O que você precisa garantir é que o caminho do seu arquivo esteja no LOCATION, o nome da origem de dados externa que criamos a pouco esteja no DATA_SOURCE e o apontador pra estrutura do arquivo externo esteja no FILE_FORMAT.

CREATE EXTERNAL TABLE Municipios
(Ano varchar(5) , ufid varchar(5)
, uf varchar(50) , municipioid varchar(15)
, municipio varchar(200) , regiaometropolitana varchar(200)
, mesorregiaoid varchar(15) , mesorregiao varchar(200)
, microrregiaoid varchar(15) , microrregiao varchar(200)
, valoragropecuaria varchar(20) , valorindustria varchar(20)
, valorservicos varchar(20) , valoradministracao varchar(20)
, valorimpostos varchar(20) , pibcorrente varchar(20)
, populacao varchar(20) , pibpercapita varchar(20))
WITH ( LOCATION='/dadosBrutos.csv',
DATA_SOURCE=AzureStorage, FILE_FORMAT=ArquivoPorTab )
GO

Você pode verificar as duas estruturas de tipos de tabelas, tanto as internas quanto as externas, através dos códigos abaixo:

select * from sys.tables
select * from sys.external_tables

Lembre-se que a tabela externa gerencia a tabela interna. Ao criar uma tabela externa, ela também cria automaticamente a tabela interna. A exclusão da tabela externa também exclui a tabela interna. É importante lembrar disso para não ter surpresas em seus ambientes.

Se tudo foi configurado corretamente, ao executar o código abaixo você terá os dados no seu SQL Server 2016 vindo diretamente do Azure Blob Storage ou do Hadoop. Isso só dependerá das configurações que você fez.

SELECT top 10 *   FROM [PolybaseAzure].[dbo].[Municipios]

Veja o resultado.

Tendo acesso ao cluster Hadoop e ao Azure Blob Storage de dentro do SQL Server 2016 fica muito mais tranquilo para trabalharmos com Big Data em um ambiente próximo da nossa realidade diária. As consultas aqui serão feitas através de códigos T-SQL e não Hive-QL. Isso faz a nossa curva de aprendizado para trabalhar com Big Data bem menor, porque podemos evitar de usar outras linguagens para consultas simples de nossos dados externos.

Caso queira rodar o código inteiro, é este (você precisará ajustar algumas coisas com os seus dados de credenciais):

exec sp_configure 'hadoop connectivity', 7
Reconfigure Go

sp_configure go

/****************/ CREATE DATABASE PolybaseAzure
GO USE PolybaseAzure
GO

/****************/
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MinhaSenhaSegura@SQL2016&' GO

CREATE DATABASE SCOPED CREDENTIAL AzureStorageCredential WITH IDENTITY = 'user', 
SECRET = 'chave_do_storage_azure' GO

SELECT * FROM sys.database_credentials GO /****************/
CREATE EXTERNAL DATA SOURCE AzureStorage WITH (
TYPE = Hadoop, LOCATION = 'wasbs://contêiner_do_azure@storage_do_azure.blob.core.windows.net',
CREDENTIAL = AzureStorageCredential) GO

select * from sys.external_data_sources GO

/****************/ CREATE EXTERNAL FILE FORMAT ArquivoPorTab
WITH ( FORMAT_TYPE = DelimitedText,
FORMAT_OPTIONS (FIELD_TERMINATOR = '\t')) GO

select * from sys.external_file_formats GO

/****************/ CREATE EXTERNAL TABLE Municipios
(Ano varchar(5)
, ufid varchar(5) , uf varchar(50)
, municipioid varchar(15) , municipio varchar(200)
, regiaometropolitana varchar(200) , mesorregiaoid varchar(15)
, mesorregiao varchar(200) , microrregiaoid varchar(15)
, microrregiao varchar(200) , valoragropecuaria varchar(20)
, valorindustria varchar(20) , valorservicos varchar(20)
, valoradministracao varchar(20) , valorimpostos varchar(20)
, pibcorrente varchar(20) , populacao varchar(20)
, pibpercapita varchar(20)) WITH (
LOCATION='/dadosBrutos.csv', DATA_SOURCE=AzureStorage,
FILE_FORMAT=ArquivoPorTab ) GO

/****************/ select * from sys.tables
select * from sys.external_tables /****************/
SELECT TOP 10 [Ano]       ,[ufid]
 ,[uf]       ,[municipioid]
 ,[municipio]  ,[regiaometropolitana]       ,[mesorregiaoid]
 ,[mesorregiao]       ,[microrregiaoid]
 ,[microrregiao]       ,[valoragropecuaria]
 ,[valorindustria]       ,[valorservicos]
 ,[valoradministracao]       ,[valorimpostos]
 ,[pibcorrente]       ,[populacao]
 ,[pibpercapita]   FROM [PolybaseAzure].[dbo].[Municipios]

Divirta-se com o SQL 2016 e as consultas externas J