Consultar ficheiros do Delta Lake com o conjunto de 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.
Importante
Consultar o formato Delta Lake usando o pool SQL sem servidor é a funcionalidade Geralmente disponível . No entanto, a consulta às tabelas do Spark Delta ainda está em pré-visualização pública e não está pronta para produção. Há problemas conhecidos que podem acontecer se você consultar tabelas Delta criadas usando os pools do Spark. Consulte os problemas conhecidos em Autoajuda do pool SQL sem servidor.
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.
Nota
O pool Synapse SQL sem servidor usa inferência de esquema para determinar automaticamente colunas e seus tipos. As regras para inferência de esquema são as mesmas usadas para arquivos Parquet. Para mapeamento de tipo Delta Lake para tipo nativo SQL, verifique o mapeamento de tipo para Parquet.
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.
Importante
Verifique se você está usando um agrupamento de banco de dados UTF-8 (por exemplo Latin1_General_100_BIN2_UTF8) porque os valores de cadeia de caracteres em arquivos Delta Lake são codificados usando codificação UTF-8.
Uma incompatibilidade entre a codificação de texto no arquivo Delta Lake e o agrupamento pode causar erros de conversão inesperados.
Você pode alterar facilmente o agrupamento padrão do banco de dados atual usando a seguinte instrução T-SQL: ALTER DATABASE CURRENT COLLATE Latin1_General_100_BIN2_UTF8;Para obter mais informações sobre agrupamentos, consulte Tipos de agrupamento suportados para Synapse 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.
Importante
As fontes de dados podem ser criadas somente em bancos de dados personalizados (não no banco de dados mestre ou nos bancos de dados replicados de pools do Apache Spark).
Para usar os exemplos abaixo, você precisará concluir a seguinte etapa:
- Crie um banco de dados com uma fonte de dados que faça referência à conta de armazenamento do NYC Yellow Taxi.
- 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 menu suspenso 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.
Importante
Certifique-se de que você está especificando explicitamente um agrupamento UTF-8 (por exemplo Latin1_General_100_BIN2_UTF8) para todas as colunas de cadeia de caracteres na WITH cláusula ou defina um agrupamento UTF-8 no nível do banco de dados.
A incompatibilidade entre a codificação de texto no arquivo e o agrupamento de colunas de cadeia de caracteres pode causar erros de conversão inesperados.
Você pode alterar facilmente o agrupamento padrão do banco de dados atual usando a seguinte instrução T-SQL: alter database current collate Latin1_General_100_BIN2_UTF8 Você pode facilmente definir o agrupamento nos tipos de colum usando a seguinte definição: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8
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 particionamento (ano e mês) que fazem parte do padrão de DeltaTable.convertToDeltaLake 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.
Próximos passos
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.