Consultar arquivos Parquet usando o pool de SQL sem servidor no Azure Synapse Analytics

Artigo
03/17/2023

Neste artigo, você aprenderá a escrever uma consulta usando o pool de SQL sem servidor que lerá arquivos Parquet.

Exemplo de Início Rápido

A função OPENROWSET permite que você leia o conteúdo do arquivo parquet fornecendo a URL para o arquivo.

Ler o arquivo Parquet

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

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.parquet',
    format = 'parquet') as rows

Verifique se você pode acessar este arquivo. Se seu arquivo estiver protegido com uma chave SAS ou identidade personalizada do Azure, você precisará configurar uma credencial de nível do servidor para logon do SQL.

Importante

Você deve usar um agrupamento de banco de dados UTF-8 (por exemplo Latin1_General_100_BIN2_UTF8) porque os valores de cadeia de caracteres em arquivos PARQUET são codificados usando a codificação UTF-8. Uma incompatibilidade entre a codificação de texto no arquivo PARQUET 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 agrupamentos com suporte para SQL do Synapse.

Se você usar o agrupamento Latin1_General_100_BIN2_UTF8, obterá outro aumento de desempenho em comparação com os outros agrupamentos. O agrupamento Latin1_General_100_BIN2_UTF8 é compatível com as regras de classificação de cadeia de caracteres parquet. O pool de SQL pode eliminar algumas partes dos arquivos parquet que não conterão os dados necessários nas consultas (remoção do segmento de arquivo/coluna). Se você usar outros agrupamentos, todos os dados dos arquivos parquet serão carregados no SQL do Synapse e a filtragem ocorrerá no processo de SQL. O agrupamento Latin1_General_100_BIN2_UTF8 tem otimização de desempenho adicional que funciona apenas para parquet e Cosmos DB. A desvantagem é que você perde regras de comparação refinadas, como a não diferenciação de maiúsculas e minúsculas.

Uso da fonte de dados

O exemplo anterior usa o caminho completo para o arquivo. Como alternativa, você pode criar uma fonte de dados externa com a localização que aponta para a pasta raiz do armazenamento e usar essa fonte de dados e o caminho relativo para o arquivo na função OPENROWSET:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );
go
select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) as rows

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

Especificar explicitamente o esquema

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

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Importante

Especifique explicitamente algum agrupamento UTF-8 (por exemplo Latin1_General_100_BIN2_UTF8) para todas as colunas de cadeia de caracteres na cláusula WITH ou defina algum agrupamento UTF-8 no nível do banco de dados. Uma incompatibilidade entre a codificação de texto no arquivo e o agrupamento da coluna de cadeia de caracteres pode causar erros inesperados de conversão. É possível 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 definir facilmente o agrupamento dos tipos de coluna, por exemplo: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8 Para obter mais informações sobre agrupamentos, consulte Tipos de agrupamentos com suporte para SQL do Synapse.

Nas seções seguintes você pode ver como consultar vários tipos de arquivos PARQUET.

Pré-requisitos

A primeira etapa é criar um banco de dados com uma fonte de dados que faça referência à conta de armazenamento Táxis amarelos em NYC. Em seguida, inicialize os objetos executando o script de instalação nesse banco de dados. Esse script de instalação criará as fontes de dados, as credenciais no escopo do banco de dados e os formatos de arquivo externos que são usados nestas amostras.

Dataset

Neste exemplo, é usado o conjunto dados de Táxis amarelos em NYC. A consulta a arquivos Parquet pode ser feita da mesma maneira que os arquivos CSV são lidos. A única diferença é que o parâmetro FILEFORMAT deve ser definido como PARQUET. Os exemplos neste artigo mostram as especificidades de leitura de arquivos Parquet.

Conjunto de consultas de arquivos Parquet

É possível especificar somente as colunas desejadas ao consultar arquivos Parquet.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) WITH (
        tpepPickupDateTime DATETIME2,
        passengerCount INT
    ) AS nyc
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Inferência de esquema automática

Não é necessário usar a cláusula OPENROWSET WITH para realizar a leitura de arquivos Parquet. Os nomes de coluna e os tipos de dados são lidos automaticamente por meio dos arquivos Parquet.

Se você ler o número de arquivos de uma vez, o esquema, os nomes das colunas e os tipos de dados serão inferidos com base no primeiro serviço de arquivo obtido no armazenamento. Isso pode significar que algumas das colunas esperadas serão omitidas porque o arquivo usado pelo serviço para definir o esquema não as continha. Para especificar explicitamente o esquema, use a cláusula OPENROWSET WITH.

A amostra seguinte mostra as funcionalidades de inferência automática de esquemas para arquivos Parquet. Ele retorna o número de linhas em setembro de 2018 sem especificar um esquema.

Observação

Não é necessário especificar colunas na cláusula OPENROWSET WITH ao realizar a leitura de arquivos Parquet. Nesse caso, o serviço de consulta do pool de SQL sem servidor utilizará metadados no arquivo Parquet e associará colunas por nome.

SELECT TOP 10 *
FROM  
    OPENROWSET(
        BULK 'puYear=2018/puMonth=9/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) AS nyc

Consultar dados particionados

O conjunto de dados fornecido neste exemplo foi dividido (particionado) em subpastas separadas. Com a função de linguagem é possível destinar partições específicas. Este exemplo mostra os valores das tarifas por ano, mês e tipo de pagamento nos três primeiros meses de 2017.

Observação

A consulta do pool de SQL sem servidor é compatível com o esquema de particionamento do Hive/Hadoop.

SELECT
        YEAR(tpepPickupDateTime),
        passengerCount,
        COUNT(*) AS cnt
FROM  
    OPENROWSET(
        BULK 'puYear=*/puMonth=*/*.snappy.parquet',
        DATA_SOURCE = 'YellowTaxi',
        FORMAT='PARQUET'
    ) nyc
WHERE
    nyc.filepath(1) = 2017
    AND nyc.filepath(2) IN (1, 2, 3)
    AND tpepPickupDateTime BETWEEN CAST('1/1/2017' AS datetime) AND CAST('3/31/2017' AS datetime)
GROUP BY
    passengerCount,
    YEAR(tpepPickupDateTime)
ORDER BY
    YEAR(tpepPickupDateTime),
    passengerCount;

Mapeamento de tipo

Para o mapeamento de tipo Parquet para o tipo nativo do SQL, confira mapeamento de tipo para Parquet.

Próximas etapas

Acesse o próximo artigo para saber como Consultar tipos aninhados de Parquet.

Compartilhar via