Introdução ao PolyBase no SQL Server 2022

  • Artigo

Aplicável a: SQL Server 2016 (13.x) - Windows e versões posteriores SQL Server 2017 (14.x) - Linux e versões posteriores

Esse artigo orienta você por meio de um tutorial de como trabalhar com várias pastas e arquivos com o PolyBase no SQL Server 2022 (16.x). Esse conjunto de tutoriais consultas demonstra vários recursos do PolyBase.

A virtualização de dados com o PolyBase no SQL Server permite que você aproveite as funções de arquivo de metadados para consultar várias pastas, arquivos ou executar a eliminação de pastas. A combinação de descoberta de esquema com eliminação de pastas e arquivos é um recurso poderoso que permite ao SQL buscar apenas os dados necessários de qualquer Conta de Armazenamento do Azure ou solução de armazenamento de objetos compatível com o S3.

Pré-requisitos

Antes de usar o PolyBase neste tutorial, você deve:

  1. Instalar o PolyBase no Windows ou Instalar o PolyBase no Linux.
  2. Habilitar o PolyBase no sp_configure, se necessário.
  3. Permitir que o acesso à rede externa acesse o armazenamento de Blobs do Azure disponível publicamente em pandemicdatalake.blob.core.windows.net e azureopendatastorage.blob.core.windows.net.

Conjuntos de dados de exemplo

Se você estiver se familiarizando com a virtualização de dados e quiser testar rapidamente a funcionalidade, comece consultando conjuntos de dados disponíveis publicamente no Azure Open Datasets, como o Conjunto de dados de COVID-19 do Bing, permitindo acesso anônimo.

Use os seguintes pontos de extremidade para consultar os conjuntos de dados de COVID-19 do Bing:

  • Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Para um início rápido, execute esta consulta T-SQL simples para obter os primeiros insights sobre o conjunto de dados. Essa consulta usa OPENROWSET para consultar um arquivo armazenado em uma conta de armazenamento disponível publicamente:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet', 
 FORMAT = 'parquet' 
) AS filerows;

Você pode continuar a exploração do conjunto de dados acrescentando WHERE, GROUP BY e outras cláusulas com base no conjunto de resultados da primeira consulta.

Se a primeira consulta falhar na instância do SQL Server, o acesso à rede provavelmente será impedido para a conta de armazenamento pública do Azure. Fale com seu especialista em redes para habilitar o acesso antes de prosseguir com a consulta.

Depois de se familiarizar com a consulta de conjuntos de dados públicos, considere mudar para conjuntos de dados não públicos que exigem o fornecimento de credenciais, a concessão de direitos de acesso e a configuração de regras de firewall. Em muitos cenários do mundo real, você operará principalmente com conjuntos de dados privados.

Fonte de dados externa

Uma fonte de dados externa é uma abstração que permite fazer referência fácil a um local de arquivo em várias consultas. Para consultar locais públicos, tudo o que você precisa especificar ao criar uma fonte de dados externa é o local do arquivo:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
);

Observação

Se você receber uma mensagem de erro 46530, External data sources are not supported with type GENERIC, marque a opção de configuração PolyBase Enabled na instância do SQL Server. Ela deverá ser 1.

Execute o seguinte para habilitar o PolyBase na sua instância do SQL Server:

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

Ao acessar contas de armazenamento não públicas, além do local, você também precisará fazer referência a uma credencial com escopo do banco de dados com parâmetros de autenticação encapsulados. O script a seguir cria uma fonte de dados externa apontando para o caminho do arquivo e fazendo referência a uma credencial com escopo de banco de dados.

--Create external data source pointing to the file path, and referencing database-scoped credential: 
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource 
WITH ( 
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

Consultar fontes de dados usando OPENROWSET

A sintaxe OPENROWSET permite a consulta ad hoc instantânea, ao mesmo tempo que cria apenas o número mínimo de objetos de banco de dados necessários.

O OPENROWSET requer apenas a criação da fonte de dados externa (e possivelmente a credencial), em oposição à abordagem de tabela externa, que requer um formato de arquivo externo e a própria tabela externa.

O valor do parâmetro DATA_SOURCE é automaticamente anexado ao parâmetro EM MASSA para formar o caminho completo para o arquivo.

Ao usar OPENROWSET, forneça o formato do arquivo, como o exemplo a seguir, que consulta um único arquivo:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.parquet', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Consultar vários arquivos e pastas

O comando OPENROWSET também permite consultar vários arquivos ou pastas usando curingas no caminho EM MASSA.

O exemplo a seguir usa o conjunto de dados aberto dos registros de viagem de táxi amarelo NYC:

Primeiro, crie a fonte de dados externa

--Create the data source first
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource 
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Agora, podemos consultar todos os arquivos com extensão .parquet em pastas. Por exemplo, aqui consultaremos apenas os arquivos que correspondem a um padrão de nome:

SELECT TOP 10 * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Ao consultar vários arquivos ou pastas, todos os arquivos acessados com o único OPENROWSET devem ter a mesma estrutura (como o mesmo número de colunas e tipos de dados). As pastas não podem ser percorridas recursivamente.

Inferência de esquema

A inferência automática de esquemas ajuda a escrever rapidamente consultas e explorar dados quando você não conhece os esquemas de arquivos. A inferência de esquema funciona apenas com arquivos parquet.

Embora convenientes, os tipos de dados inferidos podem ser maiores do que os tipos de dados reais porque pode haver informações suficientes nos arquivos de origem para garantir que o tipo de dados apropriado seja usado. Isso pode levar a um baixo desempenho de consulta. Por exemplo, os arquivos parquet não contêm metadados sobre o comprimento máximo da coluna de caracteres, portanto, a instância o infere como varchar(8000).

Use o procedimento armazenado sys.sp_describe_first_results_set para verificar os tipos de dados resultantes da sua consulta, como o exemplo a seguir:

EXEC sp_describe_first_result_set N'
 SELECT 
 vendorID, tpepPickupDateTime, passengerCount 
 FROM 
 OPENROWSET( 
  BULK ''yellow/*/*/*.parquet'', 
  DATA_SOURCE = ''NYCTaxiExternalDataSource'', 
  FORMAT=''parquet'' 
 ) AS nyc';

Depois de conhecer os tipos de dados, você pode especificá-los usando a cláusula WITH para melhorar o desempenho:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount 
FROM 
 OPENROWSET( 
  BULK 'yellow/*/*/*.parquet', 
  DATA_SOURCE = 'NYCTaxiExternalDataSource', 
  FORMAT='PARQUET' 
 ) 
WITH ( 
 vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000 
 tpepPickupDateTime datetime2, 
 passengerCount int 
 ) AS nyc;

Como o esquema de arquivos CSV não pode ser determinado automaticamente, as colunas devem sempre ser especificadas usando a cláusula WITH:

SELECT TOP 10 id, updated, confirmed, confirmed_change 
FROM OPENROWSET( 
 BULK 'bing_covid-19_data.csv', 
 DATA_SOURCE = 'MyExternalDataSource', 
 FORMAT = 'CSV', 
 FIRSTROW = 2 
) 
WITH ( 
 id int, 
 updated date, 
 confirmed int, 
 confirmed_change int 
) AS filerows;

Funções de metadados de arquivo

Ao consultar vários arquivos ou pastas, você pode usar as funções filepath() e filename() para ler metadados de arquivo e obter parte do caminho ou caminho completo e o nome do arquivo do qual a linha no conjunto de resultados se origina. No exemplo abaixo, consulte todos os arquivos e informações de caminho e nome de arquivo de projeto para cada linha:

--Query all files and project file path and file name information for each row: 

SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder], 
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows; 

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet') AS filerows;

  • Quando chamada sem um parâmetro, a função filepath() retorna o caminho do arquivo de onde a linha se origina. Quando DATA_SOURCE é usado no OPENROWSET, ele retorna o caminho relativo ao DATA_SOURCE, caso contrário, ele retorna o caminho de arquivo completo.

  • Quando chamada com um parâmetro, a função filepath() retorna parte do caminho correspondente ao caractere curinga na posição especificada no parâmetro. Por exemplo, o primeiro valor de parâmetro retornaria a parte do caminho correspondente ao primeiro caractere curinga.

A função filepath() também pode ser usada para filtrar e agregar linhas:

SELECT 
 r.filepath() AS filepath 
 ,r.filepath(1) AS [year] 
 ,r.filepath(2) AS [month] 
 ,COUNT_BIG(*) AS [rows] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
DATA_SOURCE = 'NYCTaxiExternalDataSource', 
FORMAT = 'parquet' 
 ) AS r 
WHERE 
 r.filepath(1) IN ('2017') 
 AND r.filepath(2) IN ('10', '11', '12') 
GROUP BY 
 r.filepath() 
 ,r.filepath(1) 
 ,r.filepath(2) 
ORDER BY 
 filepath;

Criar exibição com base na OPENROWSET

Você pode criar exibições para envolver as consultas OPENROWSET, de modo que possa reutilizar facilmente a consulta subjacente. As exibições também permitem que ferramentas analíticas e de relatório, como Power BI, consumam os resultados de OPENROWSET.

Por exemplo, considere o seguinte modo de exibição com base em um comando OPENROWSET:

CREATE VIEW TaxiRides AS 
SELECT * 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Também é conveniente adicionar colunas com os dados do local do arquivo a uma exibição usando a função filepath() para uma filtragem mais fácil e de alto desempenho. O uso de modos de exibição pode reduzir o número de arquivos e a quantidade de dados que a consulta na parte superior da exibição precisa ler e processar quando filtrada por qualquer uma dessas colunas:

CREATE VIEW TaxiRides AS 
SELECT * 
 , filerows.filepath(1) AS [year] 
 , filerows.filepath(2) AS [month] 
FROM OPENROWSET( 
 BULK 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = 'NYCTaxiExternalDataSource', 
 FORMAT = 'parquet' 
) AS filerows;

Tabelas externas

Tabelas externas encapsulam o acesso a arquivos, tornando a experiência de consulta quase idêntica à consulta de dados relacionais locais armazenados em tabelas de usuário. A criação de uma tabela externa exige que a fonte de dados externa e os objetos de formato de arquivo externo existam:

--Create external file format 
CREATE EXTERNAL FILE FORMAT DemoFileFormat 
WITH ( 
 FORMAT_TYPE=PARQUET 
) 
GO 
 
--Create external table: 
CREATE EXTERNAL TABLE tbl_TaxiRides( 
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2, 
 tpepPickupDateTime DATETIME2, 
 tpepDropoffDateTime DATETIME2, 
 passengerCount INT, 
 tripDistance FLOAT, 
 puLocationId VARCHAR(8000), 
 doLocationId VARCHAR(8000), 
 startLon FLOAT, 
 startLat FLOAT, 
 endLon FLOAT, 
 endLat FLOAT, 
 rateCodeId SMALLINT, 
 storeAndFwdFlag VARCHAR(8000), 
 paymentType VARCHAR(8000), 
 fareAmount FLOAT, 
 extra FLOAT, 
 mtaTax FLOAT, 
 improvementSurcharge VARCHAR(8000), 
 tipAmount FLOAT, 
 tollsAmount FLOAT, 
 totalAmount FLOAT 
) 
WITH ( 
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet', 
 DATA_SOURCE = NYCTaxiExternalDataSource, 
 FILE_FORMAT = DemoFileFormat 
);

Depois que a tabela externa for criada, você poderá consultá-la assim como qualquer outra tabela:

SELECT TOP 10 * 
FROM tbl_TaxiRides;

Assim como OPENROWSET, as tabelas externas permitem consultar vários arquivos e pastas usando curingas. A inferência de esquema não é suportada com tabelas externas.

Fontes de dados externas

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.

Para obter mais tutoriais sobre várias fontes de dados externas, consulte:

Conteúdo relacionado