Comece a usar o PolyBase no SQL Server 2022

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores para Windows SQL Server 2017 (14.x) e versões posteriores para Linux

Este artigo guia-o através de um tutorial sobre como trabalhar com múltiplas pastas e ficheiros com PolyBase no SQL Server 2022 (16.x). Este conjunto de consultas tutoriais demonstra várias funcionalidades do PolyBase.

A virtualização de dados com PolyBase no SQL Server permite-lhe tirar partido das funções de ficheiros de metadados para consultar múltiplas pastas, ficheiros ou realizar a eliminação de pastas. A combinação da descoberta de esquemas com a eliminação de pastas e ficheiros é uma capacidade poderosa que permite ao SQL obter apenas os dados necessários de qualquer Conta de Armazenamento Azure ou solução de armazenamento de objetos compatível com S3.

Pré-requisitos

Antes de usar o PolyBase neste tutorial, deves:

1. Instala o PolyBase no Windows ou instala o PolyBase no Linux.
2. Ativa o PolyBase no sp_configure se necessário.
3. Permitir o acesso a rede externa para aceder ao armazenamento Azure Blob publicamente disponível em pandemicdatalake.blob.core.windows.net e azureopendatastorage.blob.core.windows.net.

Conjuntos de dados de exemplo

Se é novo na virtualização de dados e quer testar rapidamente funcionalidades, comece por consultar conjuntos de dados públicos disponíveis nos Azure Open Datasets, como o conjunto de dados COVID-19 do Bing que permite acesso anónimo.

Use as seguintes interfaces para consultar os conjuntos de dados do Bing COVID-19.

• Parquet: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
• VCS: 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 as primeiras informações sobre o conjunto de dados. Esta 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;

Pode continuar a exploração de conjuntos de dados adicionando WHERE, GROUP BY e outras cláusulas T-SQL com base no conjunto de resultados da primeira consulta.

Se a primeira consulta falhar na sua instância do SQL Server, é provável que o acesso à rede seja impedido para a conta pública de armazenamento do Azure. Fale com o seu especialista em redes para permitir o acesso antes de avançar com as consultas.

Depois de se familiarizar com a consulta de conjuntos de dados públicos, considere mudar para conjuntos de dados não públicos que exijam 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 a referência fácil de 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' 
);

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

Ao acessar contas de armazenamento não públicas, juntamente com o local, você também precisa fazer referência a uma credencial com escopo de banco de dados com parâmetros de autenticação encapsulados. O script a seguir cria uma fonte de dados externa apontando para a localização do ficheiro e fazendo referência a uma credencial delimitada para o 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 consultas ad hoc instantâneas enquanto cria apenas o número mínimo de objetos de banco de dados necessários.

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 DATA_SOURCE valor do parâmetro é automaticamente anexado ao parâmetro BULK 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 OPENROWSET comando também permite consultar vários arquivos ou pastas usando curingas no caminho MASS.

O exemplo seguinte utiliza o conjunto de dados abertos dos registos de viagens dos táxis amarelos de 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 do esquema

A inferência automática de esquema ajuda você a escrever consultas e explorar dados rapidamente quando você não conhece esquemas de arquivo. A inferência de esquema só funciona 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. Tal pode levar a um fraco desempenho da consulta. Por exemplo, ficheiros parquet não contêm metadados sobre o comprimento máximo da coluna de caracteres, por isso a instância infere como varchar(8000).

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

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 para melhorar o WITH 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 ser sempre especificadas usando a WITH cláusula:

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 múltiplos ficheiros ou pastas, pode usar as funções filepath() e filename() para ler metadados de ficheiros e obter parte do caminho ou o caminho completo e o nome do ficheiro de onde a linha no conjunto de resultados se origina. No exemplo seguinte, consulte todos os ficheiros e a informação do caminho e nome dos ficheiros do 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 filepath() função retorna o caminho do arquivo do qual a linha se origina. Quando DATA_SOURCE usado no OPENROWSET, ele retorna o caminho relativo ao DATA_SOURCE, caso contrário, retorna o caminho completo do arquivo.

• Quando chamada com um parâmetro, a função filepath() retorna parte do caminho que corresponde ao caracter coringa na posição especificada no parâmetro. Por exemplo, o valor do primeiro parâmetro devolveria parte do caminho que corresponde ao primeiro coringa.

A filepath() função 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 vista na parte superior de OPENROWSET

Pode criar vistas para encapsular consultas, permitindo assim reutilizar facilmente a consulta subjacente. As vistas também permitem que ferramentas de relatórios e análise, como o Power BI, consumam resultados do OPENROWSET.

Por exemplo, considere a seguinte perspetiva baseada num OPENROWSET comando:

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 de localização do arquivo a uma visualização usando a filepath() função para uma filtragem mais fácil e mais eficiente. 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 do modo de 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 ficheiros, tornando a experiência de consulta quase idêntica à consulta de dados relacionais locais armazenados em tabelas de utilizador. A criação de uma tabela externa requer a existência da fonte de dados externa e dos objetos de formato de arquivo externo:

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

Uma vez que a tabela externa é criada, você pode consultá-la como qualquer outra tabela:

SELECT TOP 10 * 
FROM tbl_TaxiRides; 

Tal como no OPENROWSET, tabelas externas permitem consultar múltiplos ficheiros e pastas usando wildcards. A inferência de esquema não é suportada com tabelas externas.

Fontes de dados externas

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

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

• Hadoop
• Armazenamento de Blobs do Azure
• Servidor SQL
• Oracle
• Teradata
• MongoDB
• Tipos Genéricos ODBC
• Armazenamento de objetos compatível com S3
• CSV
• Tabela delta

Conteúdo relacionado

• Virtualização de dados com o PolyBase no SQL Server
• Perguntas frequentes sobre o PolyBase
• Considerações de desempenho no PolyBase para SQL Server
• Configuração e segurança do PolyBase para Hadoop