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

Artigo
10/18/2023

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

Exemplo de início rápido

OPENROWSET permite que você leia o conteúdo do arquivo parquet, fornecendo o URL para o seu arquivo.

Ler arquivo parquet

A maneira mais fácil de ver o conteúdo do seu PARQUET arquivo é fornecer URL do arquivo para OPENROWSET funcionar e especificar parquet FORMAT. 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 a consulta como a mostrada no exemplo a seguir:

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

Certifique-se de que consegue aceder a este ficheiro. Se o arquivo estiver protegido com chave SAS ou identidade personalizada do Azure, você precisará configurar a credencial no nível do servidor para logon 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 PARQUET são codificados usando 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 agrupamento suportados para Synapse SQL.

Se você usar o Latin1_General_100_BIN2_UTF8 agrupamento, obterá um aumento de desempenho adicional em comparação com os outros agrupamentos. O Latin1_General_100_BIN2_UTF8 agrupamento é compatível com as regras de classificação de cadeia de caracteres de parquet. O pool SQL é capaz de eliminar algumas partes dos arquivos de parquet que não conterão os dados necessários nas consultas (remoção de segmento de arquivo/coluna). Se você usar outros agrupamentos, todos os dados dos arquivos parquet serão carregados no Synapse SQL e a filtragem está acontecendo dentro do processo SQL. O Latin1_General_100_BIN2_UTF8 agrupamento 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 insensibilidade a 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 o local que aponta para a pasta raiz do armazenamento e usar essa fonte de dados e o caminho relativo para o arquivo na OPENROWSET função:

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 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 'latest/ecdc_cases.parquet',
        data_source = 'covid',
        format = 'parquet'
    ) with ( date_rep date, cases int, geo_id varchar(6) ) as rows

Importante

Certifique-se de que você está especificando detalhadamente algum agrupamento UTF-8 (por exemplo Latin1_General_100_BIN2_UTF8) para todas as colunas de cadeia de caracteres na WITH cláusula ou defina algum 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 colo, por exemplo: geo_id varchar(6) collate Latin1_General_100_BIN2_UTF8Para obter mais informações sobre agrupamentos, consulte Tipos de agrupamento suportados para Synapse SQL.

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

Pré-requisitos

Seu primeiro passo é criar um banco de dados com uma fonte de dados que faça referência à conta de armazenamento do NYC Yellow Taxi. 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, credenciais com escopo de banco de dados e formatos de arquivo externos usados nesses exemplos.

Conjunto de dados

O conjunto de dados NYC Yellow Taxi é usado neste exemplo. Você pode consultar arquivos do Parquet da mesma forma que lê arquivos CSV. A única diferença é que o FILEFORMAT parâmetro deve ser definido como PARQUET. Exemplos neste artigo mostram as especificidades da leitura de arquivos Parquet.

Conjunto de consultas de arquivos parquet

Você pode especificar apenas as colunas de interesse ao consultar arquivos do 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 automática de esquema

Você não precisa usar a cláusula OPENROWSET WITH ao ler arquivos do Parquet. Os nomes das colunas e os tipos de dados são lidos automaticamente a partir dos ficheiros Parquet.

Tenha em mente que, se você estiver lendo o número de arquivos ao mesmo tempo, o esquema, os nomes das colunas e os tipos de dados serão inferidos a partir do primeiro serviço de arquivos obtido do armazenamento. Isso pode significar que algumas das colunas esperadas são omitidas, tudo porque o arquivo usado pelo serviço para definir o esquema não continha essas colunas. Para especificar explicitamente o esquema, use a cláusula OPENROWSET WITH.

O exemplo a seguir mostra os recursos de inferência automática de esquema para arquivos Parquet. Ele retorna o número de linhas em setembro de 2018 sem especificar um esquema.

Nota

Não é necessário especificar colunas na cláusula OPENROWSET WITH ao ler arquivos Parquet. Nesse caso, o serviço de consulta de pool SQL sem servidor utilizará metadados no arquivo Parquet e vinculará 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 é dividido (particionado) em subpastas separadas. Você pode direcionar partições específicas usando a função filepath. Este exemplo mostra os valores das tarifas por ano, mês e payment_type para os primeiros três meses de 2017.

Nota

A consulta de pool SQL sem servidor é compatível com o esquema de particionamento 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 mapeamento de tipo Parquet para tipo nativo SQL, verifique o mapeamento de tipo para Parquet.

Próximos passos

Avance para o próximo artigo para saber como Consultar tipos aninhados do Parquet.