Consultar arquivos Delta Lake (v1) usando o pool SQL sem servidor no Azure Synapse Analytics

Neste artigo, você aprenderá como escrever uma consulta usando o pool SQL Synapse sem servidor para ler arquivos Delta Lake. O Delta Lake é uma camada de armazenamento de código aberto que traz transações ACID (atomicidade, consistência, isolamento e durabilidade) para cargas de trabalho do Apache Spark e de big data. Você pode aprender mais no vídeo Como consultar tabelas do lago delta.

O pool SQL sem servidor no espaço de trabalho Synapse permite ler os dados armazenados no formato Delta Lake e servi-los para ferramentas de relatório. Um pool SQL sem servidor pode ler arquivos Delta Lake criados usando Apache Spark, Azure Databricks ou qualquer outro produtor do formato Delta Lake.

Os pools do Apache Spark no Azure Synapse permitem que os engenheiros de dados modifiquem arquivos Delta Lake usando Scala, PySpark e .NET. Os pools SQL sem servidor ajudam os analistas de dados a criar relatórios sobre arquivos Delta Lake criados por engenheiros de dados.

Pré-requisitos

Para usar os exemplos neste artigo, você precisará concluir as seguintes etapas:

1. Crie um banco de dados com uma fonte de dados que faça referência à conta de armazenamento do NYC Yellow Taxi .
2. Inicialize os objetos executando o script de instalação no banco de dados criado na etapa 1. Esse script de instalação criará as fontes de dados, credenciais com escopo de banco de dados e formatos de arquivo externos usados nesses exemplos.

Se criou a sua base de dados e alternou o contexto para a sua base de dados (usando a instrução USE database_name ou um menu pendente para selecionar a base de dados em algum editor de consultas), pode criar a sua fonte de dados externa que contém o URI raiz para o seu conjunto de dados e utilizá-lo para consultar ficheiros Delta Lake. Por exemplo:

CREATE EXTERNAL DATA SOURCE DeltaLakeStorage
WITH ( LOCATION = 'https://<yourstorageaccount>.blob.core.windows.net/delta-lake/' );
GO

SELECT TOP 10 *
FROM OPENROWSET(
        BULK 'covid',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT = 'delta'
    ) as rows;

If a data source is protected with SAS key or custom identity, you can configure data source with database scoped credential.

Você pode criar uma fonte de dados externa com o local que aponta para a pasta raiz do armazenamento. Depois de criar a fonte de dados externa, use a fonte de dados e o caminho relativo para o arquivo na OPENROWSET função. Desta forma, não precisa de utilizar o URI absoluto completo para os seus ficheiros. Em seguida, você também pode definir credenciais personalizadas para acessar o local de armazenamento.

Read Delta Lake folder

A função OPENROWSET permite que você leia o conteúdo dos arquivos Delta Lake fornecendo a URL para sua pasta raiz.

A maneira mais fácil de ver o conteúdo do seu ficheiro DELTA é fornecer a URL do ficheiro à função OPENROWSET e especificar o DELTA formato. Se o arquivo estiver disponível publicamente ou se sua identidade do Microsoft Entra puder acessar esse arquivo, você poderá ver o conteúdo do arquivo usando uma consulta como a mostrada no exemplo a seguir:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '/covid/',
    DATA_SOURCE = 'DeltaLakeStorage',
    FORMAT = 'delta') as rows;

Os nomes das colunas e os tipos de dados são lidos automaticamente a partir dos ficheiros Delta Lake. A função OPENROWSET usa os tipos de melhor estimativa, como VARCHAR(1000), para as colunas de texto.

O URI na função OPENROWSET deve fazer referência à pasta raiz Delta Lake que contém uma subpasta chamada _delta_log.

Se você não tiver essa subpasta, não está usando o formato Delta Lake. Você pode converter seus arquivos Parquet simples na pasta para o formato Delta Lake usando um script como o seguinte exemplo Apache Spark Python script:

%%pyspark
from delta.tables import DeltaTable
deltaTable = DeltaTable.convertToDelta(spark, "parquet.`abfss://delta-lake@sqlondemandstorage.dfs.core.windows.net/covid`")

Para melhorar o desempenho de suas consultas, considere especificar tipos explícitos na WITH cláusula.

Certifique-se de que consegue aceder ao seu ficheiro. Se o seu ficheiro estiver protegido com chave SAS ou identidade personalizada do Azure, terá de configurar uma credencial de nível de servidor para início de sessão sql.

Especificar explicitamente o esquema

OPENROWSET Permite especificar explicitamente quais colunas você deseja ler do arquivo usando WITH a cláusula:

SELECT TOP 10 *
FROM OPENROWSET(
        BULK 'covid',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT = 'delta'
    )
    WITH ( date_rep date,
           cases int,
           geo_id varchar(6)
           ) as rows;

Com a especificação explícita do esquema do conjunto de resultados, pode-se minimizar os tamanhos de tipo e usar tipos mais precisos, como VARCHAR(6) para colunas de texto, em vez do pessimista VARCHAR(1000). A minimização de tipos pode melhorar significativamente o desempenho de suas consultas.

Conjunto de dados

O conjunto de dados NYC Yellow Taxi é usado neste exemplo. O conjunto de dados original PARQUET é convertido em DELTA formato e a DELTA versão é usada nos exemplos.

Consultar dados particionados

O conjunto de dados fornecido neste exemplo é dividido (particionado) em subpastas separadas.

Ao contrário do Parquet, você não precisa direcionar partições específicas usando a FILEPATH função. O OPENROWSET identificará colunas de particionamento em sua estrutura de pastas Delta Lake e permitirá que você consulte dados diretamente usando essas colunas. Este exemplo mostra os valores das tarifas por ano, mês e payment_type para os primeiros três meses de 2017.

SELECT
        YEAR(pickup_datetime) AS year,
        passenger_count,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'yellow',
        DATA_SOURCE = 'DeltaLakeStorage',
        FORMAT='DELTA'
    ) nyc
WHERE
    nyc.year = 2017
    AND nyc.month IN (1, 2, 3)
    AND pickup_datetime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passenger_count,
    YEAR(pickup_datetime)
ORDER BY
    YEAR(pickup_datetime),
    passenger_count;

A função OPENROWSET eliminará partições que não correspondem aos year e month na cláusula where. Essa técnica de remoção de arquivos/partições reduzirá significativamente seu conjunto de dados, melhorará o desempenho e reduzirá o custo da consulta.

The folder name in the OPENROWSET function (yellow in this example) is concatenated using the LOCATION in DeltaLakeStorage data source, and must reference the root Delta Lake folder that contains a subfolder called _delta_log.

Se você não tiver essa subpasta, não está usando o formato Delta Lake. Você pode converter seus arquivos Parquet simples na pasta para o formato Delta Lake usando o seguinte script Apache Spark Python:

%%pyspark
from delta.tables import DeltaTable
deltaTable = DeltaTable.convertToDelta(spark, "parquet.`abfss://delta-lake@sqlondemandstorage.dfs.core.windows.net/yellow`", "year INT, month INT")

O segundo argumento da função DeltaTable.convertToDeltaLake representa as colunas de particionamento (ano e mês) que fazem parte do esquema de pastas (year=*/month=* neste exemplo) e os seus tipos.

Limitações

• Analise as limitações e os problemas conhecidos na página de autoajuda do pool SQL sem servidor Synapse.

Conteúdos relacionados

Advance to the next article to learn how to Query Parquet nested types. Se quiser continuar a criar a solução Delta Lake, saiba como criar vistas ou tabelas externas na pasta Delta Lake.

• O que é Delta Lake
• Saiba como usar o Delta Lake em pools do Apache Spark para o Azure Synapse Analytics
• Práticas recomendadas do Azure Databricks Delta Lake
• Página de documentação do Lago Delta
• Conhecer problemas e limitações