Virtualizar tabela delta com PolyBase

  • Artigo

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores

O SQL Server 2022 (16.x) pode consultar dados diretamente de uma pasta de tabela delta. Este processo, comumente chamado de virtualização de dados, permite que os dados permaneçam em sua localização original, mas possam ser consultados de uma instância do SQL Server com comandos do T-SQL, como qualquer outra tabela. Este recurso usa conectores do PolyBase e minimiza a necessidade de copiar dados por meio de processos do ETL.

No exemplo a seguir, a pasta de tabela delta está armazenada no Armazenamento de Blobs do Azure e é acessada por meio de OPENROWSET ou de uma tabela externa.

Para mais informações sobre a virtualização de dados, consulte Introdução à virtualização de dados com o PolyBase.

Pré-configuração

1. Habilitar o PolyBase em sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Criar um banco de dados de usuário

Este exercício cria um banco de dados de exemplo com configurações e localização padrão. Você vai usar esse banco de dados de exemplo vazio para trabalhar com os dados e armazenar a credencial no escopo. Neste exemplo, um novo banco de dados vazio chamado Delta_demo é usado.

CREATE DATABASE [Delta_demo];

3. Criar uma chave mestra e uma credencial com escopo de banco de dados

A chave mestra do banco de dados no banco de dados de usuário é necessária para criptografar o segredo da credencial com escopo de banco de dados, delta_storage_dsc. Para este exemplo, a tabela delta reside no Azure Data Lake Storage Gen2.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Criar uma fonte de dados externa

A credencial com escopo de banco de dados é usada para a fonte de dados externa. Neste exemplo, a tabela delta reside no Azure Data Lake Storage Gen2, portanto, use o prefixo adls e o método de identidade SHARED ACCESS SIGNATURE. Para obter mais informações sobre os conectores e prefixos, incluindo novas configurações para o SQL Server 2022 (16.x), consulte CREATE EXTERNAL DATA SOURCE.

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Por exemplo, se a sua conta de armazenamento for nomeada delta_lake_sample e o contêiner for nomeado sink, o código será:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Usar OPENROWSET para acessar os dados

Neste exemplo, a pasta de Tabela de Dados é chamada Contoso.

Uma vez que a fonte de dados externa Delta_ED é mapeada para um nível de contêiner. A pasta da tabela delta Contoso está localizada em uma raiz. Para consultar um arquivo em uma estrutura de pastas, forneça um mapeamento de pasta relativo ao parâmetro LOCATION da fonte de dados externa.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Consultar dados com uma tabela externa

CREATE EXTERNAL TABLE também pode ser usado para virtualizar os dados da tabela delta no SQL Server. As colunas devem ser definidas e fortemente tipadas. Embora a criação de tabelas externas exija mais esforço, elas também oferecem benefícios adicionais em relação à consulta de uma fonte de dados externa com OPENROWSET. Você poderá:

  • Fortalecer a definição da digitação de dados para uma determinada coluna
  • Definir nulidade
  • Definir COLLATION
  • Criar estatísticas para uma coluna para otimizar a qualidade do plano de consulta
  • Criar um modelo mais granular no SQL Server para acesso a dados para aprimorar o modelo de segurança

Para obter mais informações, consulte CREATE EXTERNAL TABLE.

Para o exemplo a seguir, a mesma fonte de dados é usada.

1. Criar um formato de arquivo externo

Para definir a formatação do arquivo, é necessário um formato de arquivo externo. Formatos de arquivo externos também são recomendados devido à reutilização. Para obter mais informações, consulte CREATE EXTERNAL FILE FORMAT.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Criar uma tabela externa

Os arquivos de tabela delta estão localizados em /delta/Delta_yob/ e a fonte de dados externa para este exemplo é o armazenamento de objetos compatível com o S3, configurado anteriormente na fonte de dados s3_eds. O PolyBase pode usar como LOCATION a pasta da tabela delta ou o próprio arquivo absoluto, que está localizado em delta/Delta_yob/_delta_log/00000000000000000000.json.

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Limitações

Se você criar uma tabela externa que direciona para a tabela delta particionada, a coluna usada para o particionamento retornará NULL ao consultar a tabela externa. Entretanto, se você usar uma consulta OPENROWSET, o valor da coluna será retornado corretamente. Para contornar essa limitação, crie uma exibição na consulta OPENROWSET e, em seguida, consulte a exibição para que os valores da coluna particionada sejam retornados corretamente.

Você pode encontrar os seguintes erros ao consultar uma tabela Delta externa:

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

Isso pode acontecer porque há uma dica de consulta QUERYTRACEON que pode ser adicionada à consulta de metadados de arquivo Delta e que requer a função de servidor sysadmin para ser executada. Se isso ocorrer, você poderá resolver o problema habilitando globalmente o sinalizador de rastreamento 14073 e isso impedirá que a dica de consulta seja adicionada.

Conteúdo relacionado