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.

Exemplo de início rápido

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

Leia a pasta Delta Lake

A maneira mais fácil de ver o conteúdo do seu DELTA arquivo é fornecer a URL do arquivo para a função OPENROWSET e especificar DELTA o 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 'https://sqlondemandstorage.blob.core.windows.net/delta-lake/covid/',
    FORMAT = 'delta') as rows;

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

O URI na função deve fazer referência à OPENROWSET pasta Delta Lake raiz 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 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/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.

Uso da fonte de dados

Os exemplos anteriores usavam o caminho completo para o arquivo. Como alternativa, 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.

Para usar os exemplos abaixo, você precisará concluir a seguinte etapa:

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 você criou seu banco de dados e alternou o contexto para seu banco de dados (usando USE database_name instrução ou lista suspensa para selecionar banco de dados em algum editor de consultas), você pode criar sua fonte de dados externa contendo o URI raiz para seu conjunto de dados e usá-lo para consultar arquivos Delta Lake:

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

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

Se uma fonte de dados estiver protegida com chave SAS ou identidade personalizada, você poderá configurar a fonte de dados com credencial de escopo de banco de dados.

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, você pode minimizar os tamanhos de tipo e usar os tipos mais precisos VARCHAR(6) para colunas de cadeia de caracteres em vez de 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 OPENROWSET função eliminará partições que não correspondem à year cláusula 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.

O nome da OPENROWSET pasta na função (yellow neste exemplo) é concatenado usando a LOCATION fonte de dados in DeltaLakeStorage e 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 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 de função representa as colunas de DeltaTable.convertToDeltaLake particionamento (ano e mês) que fazem parte do padrão de pasta (year=*/month=* neste exemplo) e 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

Avance para o próximo artigo para saber como consultar tipos aninhados do Parquet. 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