Exercício – Usar o PolyBase para consultar um arquivo Parquet

  • 10 minutos

Neste exercício, você:

  • Instale e habilite o PolyBase.
  • Crie um banco de dados.
  • Crie uma chave mestra de banco de dados para proteger a credencial de escopo do banco de dados.
  • Crie uma credencial com escopo de banco de dados para acessar a fonte de dados.
  • Crie a fonte de dados.
  • Consultar e manipular dados armazenados na fonte de dados pública.
  • Crie um formato de arquivo externo e uma tabela externa.

Instalar o PolyBase

Você pode instalar o PolyBase usando o arquivo executável de instalação do SQL Server durante a configuração inicial ou adicioná-lo como uma funcionalidade posteriormente. Na página Seleção de Recursos do SQL Server setup.exe, selecione o Serviço de Consulta do PolyBase para Dados Externos.

Imagem do executável de instalação do SQL Server mostrando a opção PolyBase.

Os serviços do PolyBase exigem que as portas de firewall sejam habilitadas para se conectar a fontes de dados externas. Por padrão, o PolyBase usa portas que variam de 16450 a 16460.

Imagem do executável de instalação do SQL Server mostrando a configuração do intervalo de portas do PolyBase.

A instalação do PolyBase instala dois serviços do PolyBase, o Mecanismo PolyBase do SQL Server e a Movimentação de Dados do PolyBase do SQL Server. Para obter informações completas e pré-requisitos para a instalação do PolyBase, consulte:

Habilitar o PolyBase

Depois de instalar o serviço, conecte-se à instância do SQL Server 2025 no SQL Server Management Studio (SSMS) e execute o comando a seguir para habilitar o PolyBase.

EXEC SP_CONFIGURE @CONFIGNAME = N'POLYBASE ENABLED', @CONFIGVALUE = 1;
RECONFIGURE;

Uma imagem de habilitar o PolyBase usando o T-SQL no SQL Server Management Studio.

Observação

Neste exercício, você consulta arquivos Apache Parquet usando a API REST do PolyBase, por isso não é necessário habilitar ou configurar os serviços Movimentação de Dados PolyBase do SQL Server ou Mecanismo PolyBase do SQL Server.

Criar um banco de dados

Execute o comando a seguir no SSMS para criar um banco de dados para este exercício chamado Demo1. Se o banco de dados já tiver sido criado, o script o removerá e o recriará.

USE MASTER;

IF EXISTS (SELECT * FROM sys.databases WHERE [name] = N'Demo1')
BEGIN
    ALTER DATABASE Demo1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE IF EXISTS Demo1
END;

CREATE DATABASE Demo1;

USE Demo1;

Criar a chave mestra de banco de dados

Você deve criar uma chave mestra de banco de dados para garantir a segurança das credenciais com escopo no banco de dados. O exemplo a seguir cria a chave com uma senha gerada aleatoriamente e um backup é necessário.

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXECUTE sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Para entender melhor e manter chaves de criptografia em um ambiente de produção, confira:

Criar a credencial no escopo do banco de dados

A credencial com escopo de banco de dados é responsável por armazenar as credenciais que a fonte de dados usa para se conectar ao ponto de extremidade. Esse exemplo usa um ponto de extremidade público, portanto a credencial não precisa de um segredo.

IF EXISTS (SELECT * FROM sys.database_scoped_credentials WHERE name = N'PublicCredential')
    DROP DATABASE SCOPED CREDENTIAL PublicCredential;
 
CREATE DATABASE SCOPED CREDENTIAL PublicCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<KEY>'; -- This example doesn't need the SECRET because the data source is public

Criar a fonte de dados

Este exemplo usa um conjunto de dados COVID Parquet publicamente disponível armazenado no Armazenamento de Blobs do Azure. Use o escopo do banco de dados PublicCredential que você criou para estabelecer a conexão.

Valores DE LOCALIZAÇÃO:

  • Prefixo: abs
  • Conta de Armazenamento do Azure: pandemicdatalake
  • Caminho completo da conta de Armazenamento do Azure: pandemicdatalake.blob.core.windows.net
  • Nome do contêiner: public
  • Caminho completo do contêiner: public/curated/covid-19/bing_covid-19_data/latest
IF EXISTS (SELECT * FROM sys.external_data_sources WHERE name = N'Public_Covid') DROP EXTERNAL DATA SOURCE Public_Covid;
 
CREATE EXTERNAL DATA SOURCE Public_Covid
WITH (
    LOCATION = 'abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest',
    CREDENTIAL = [PublicCredential]
);

Consultar os dados com OPENROWSET

Você pode usar OPENROWSET para acessar e explorar os dados. OPENROWSET é otimizado para cenários de exploração de dados e carga de trabalho ad hoc.

Valores OPENROWSET:

  • BULK: nome do arquivo e extensão. BULK acrescenta automaticamente às informações da fonte de dados, de modo que o local completo do arquivo é abs://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • FORMATO: PARQUET
  • DATA_SOURCE: Informações de conexão, nesse caso, sua nova fonte de dados Public_Covid
SELECT TOP 1000 *
FROM OPENROWSET 
     (BULK 'bing_covid-19_data.parquet'
     , FORMAT = 'PARQUET'
     , DATA_SOURCE = 'Public_Covid')
     AS [COVID_Dataset]

O exemplo a seguir usa flexibilidade T-SQL para consultar o arquivo Parquet em tempo real, assim como uma tabela regular. Para retornar o número de casos confirmados por estado dos EUA em ordem decrescente, execute a seguinte consulta:

SELECT [COVID_Dataset].admin_region_1, 
       SUM(CAST([COVID_Dataset].confirmed AS BIGINT)) AS Confirmed
FROM OPENROWSET 
     (BULK 'bing_covid-19_data.parquet'
     , FORMAT = 'PARQUET'
     , DATA_SOURCE = 'Public_Covid')
     AS [COVID_Dataset]
WHERE [COVID_Dataset].country_region = 'United States' AND 
      [COVID_Dataset].admin_region_1  IS NOT NULL
GROUP BY [COVID_Dataset].admin_region_1 
ORDER BY confirmed DESC

Criar e consultar uma tabela externa

OPENROWSET é otimizado para execução ad hoc e exploração de dados. Tabelas externas são mais adequadas para acesso recorrente, pois também podem usar estatísticas.

Descubra o esquema da tabela externa

Para criar uma tabela externa, primeiro determine as colunas e o tipo. O esquema vem de um arquivo externo, portanto, pode ser demorado determinar com precisão os tipos de dados e intervalos. Felizmente, você pode usar o procedimento armazenado sp_describe_first_result_set (Transact-SQL) para acelerar esse processo.

DECLARE @tsql NVARCHAR(MAX) = 'SELECT TOP 1000 *
FROM OPENROWSET 
    (BULK ''bing_covid-19_data.parquet''
    , FORMAT = ''PARQUET''
    , DATA_SOURCE = ''Public_Covid'')
    AS [COVID_Dataset]';
EXEC sys.sp_describe_first_result_set @tsql;

Imagem dos resultados do esquema da fonte de dados externa do Parquet no SQL Server Management Studio.

Você pode ver que sp_describe_first_result_set retornou os nomes de coluna, tipos, comprimento, precisão e até mesmo a ordenação da fonte de dados.

Criar o formato de arquivo externo

Como você precisa referenciar o arquivo Parquet à tabela externa, primeiro você precisa executar CREATE EXTERNAL FILE FORMAT para adicionar o formato de arquivo Parquet. A definição de formato de arquivo é importante para tabelas externas porque especifica o layout real e o tipo de compactação.

Execute o comando a seguir:

IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
      DROP EXTERNAL FILE FORMAT ParquetFileFormat;

CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);

Criar a tabela externa

Por fim, com todas as informações que você acabou de adquirir e o formato de arquivo externo criado, você pode criar a tabela externa usando o seguinte script:

IF EXISTS (SELECT * FROM sys.external_file_formats WHERE name = N'ParquetFileFormat')
      DROP EXTERNAL FILE FORMAT ParquetFileFormat;
 
CREATE EXTERNAL FILE FORMAT ParquetFileFormat WITH(FORMAT_TYPE = PARQUET);
 
-- 8.3 CREATE EXTERNAL TABLE
IF OBJECT_ID(N'ext_covid_data', N'ET') IS NOT NULL
      DROP EXTERNAL TABLE ext_covid_data;
 
CREATE EXTERNAL TABLE ext_covid_data
(
id                            int,
updated                       date,
confirmed               int,
confirmed_change  int,
deaths                        int,
deaths_change           smallint,
recovered               int,
recovered_change  int,
latitude                float,
longitude               float,
iso2                    varchar(8000),
iso3                    varchar(8000),
country_region          varchar(8000),
admin_region_1          varchar(8000),
iso_subdivision         varchar(8000),
admin_region_2          varchar(8000),
load_time                     datetime2(7)
)
WITH
(
LOCATION = 'bing_covid-19_data.parquet'
     , FILE_FORMAT = ParquetFileFormat
     , DATA_SOURCE = Public_Covid
);
 
CREATE STATISTICS [Stats_ext_covid_data_updated] ON ext_covid_data([updated]);
 
SELECT TOP 1000 * FROM ext_covid_data;

Observação

Os nomes de coluna devem corresponder às colunas armazenadas no arquivo Parquet ou o SQL Server não pode identificar as colunas e retornar NULL.

Depois de criar a tabela ext_covid_dataexterna, você pode adicionar estatísticas nas colunas atualizadas para eficiência. Para obter mais informações sobre estatísticas na tabela externa, consulte CREATE STATISTICS (Transact-SQL).

Nesta unidade, você usou o PolyBase para se conectar a uma fonte de dados externa e usou OPENROWSET ou tabela externa para consultar o arquivo Parquet. No próximo exercício, você usará os serviços do PolyBase para se conectar e criar uma tabela externa de um banco de dados no Banco de Dados SQL do Azure.