Consultar arquivos usando um pool SQL sem servidor

10 minutos

Você pode usar um pool SQL sem servidor para consultar arquivos de dados em vários formatos de arquivo comuns, incluindo:

Texto delimitado, como arquivos CSV (valores separados por vírgula).
Arquivos de notação de objeto JavaScript (JSON).
Arquivos de parquet.

A sintaxe básica para consulta é a mesma para todos esses tipos de arquivo e é criada na função SQL OPENROWSET; que gera um conjunto de linhas tabular a partir de dados em um ou mais arquivos. Por exemplo, a consulta a seguir pode ser usada para extrair dados de arquivos CSV.

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv') AS rows

A função OPENROWSET inclui mais parâmetros que determinam fatores como:

O esquema do conjunto de linhas resultante
Opções de formatação adicionais para arquivos de texto delimitados.

Gorjeta

Você encontrará a sintaxe completa para a função OPENROWSET na documentação do Azure Synapse Analytics.

A saída de OPENROWSET é um conjunto de linhas ao qual um alias deve ser atribuído. No exemplo anterior, as linhas de alias são usadas para nomear o conjunto de linhas resultante.

O parâmetro BULK inclui a URL completa para o local no data lake que contém os arquivos de dados. Pode ser um arquivo individual ou uma pasta com uma expressão curinga para filtrar os tipos de arquivo que devem ser incluídos. O parâmetro FORMAT especifica o tipo de dados que estão sendo consultados. O exemplo acima lê texto delimitado de todos os arquivos .csv na pasta de arquivos .

Nota

Este exemplo pressupõe que o usuário tenha acesso aos arquivos no armazenamento subjacente, Se os arquivos estiverem protegidos com uma chave SAS ou identidade personalizada, você precisará criar uma credencial com escopo de servidor.

Como visto no exemplo anterior, você pode usar curingas no parâmetro BULK para incluir ou excluir arquivos na consulta. A lista a seguir mostra alguns exemplos de como isso pode ser usado:

https://mydatalake.blob.core.windows.net/data/files/file1.csv: Inclua apenas file1.csv na pasta de arquivos .
https://mydatalake.blob.core.windows.net/data/files/file*.csv: Todos os arquivos .csv na pasta de arquivos com nomes que começam com "arquivo".
https://mydatalake.blob.core.windows.net/data/files/*: Todos os arquivos na pasta de arquivos .
https://mydatalake.blob.core.windows.net/data/files/**: Todos os arquivos na pasta de arquivos e, recursivamente, suas subpastas.

Você também pode especificar vários caminhos de arquivo no parâmetro BULL , separando cada caminho com uma vírgula.

Consultando arquivos de texto delimitados

Os arquivos de texto delimitados são um formato de arquivo comum em muitas empresas. A formatação específica usada em arquivos delimitados pode variar, por exemplo:

Com e sem linha de cabeçalho.
Valores delimitados por vírgulas e tabulações.
Terminações de linha no estilo Windows e Unix.
Valores não cotados e cotados e caracteres que escapam.

Independentemente do tipo de arquivo delimitado que você está usando, você pode ler dados deles usando a função OPENROWSET com o parâmetro csv FORMAT e outros parâmetros conforme necessário para lidar com os detalhes de formatação específicos para seus dados. Por exemplo:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    FIRSTROW = 2) AS rows

O PARSER_VERSION é usado para determinar como a consulta interpreta a codificação de texto usada nos arquivos. A versão 1.0 é o padrão e suporta uma ampla gama de codificações de arquivos, enquanto a versão 2.0 suporta menos codificações, mas oferece melhor desempenho. O parâmetro FIRSTROW é usado para ignorar linhas no arquivo de texto, eliminar qualquer texto de preâmbulo não estruturado ou ignorar uma linha que contém títulos de coluna.

Os parâmetros adicionais que você pode precisar ao trabalhar com arquivos de texto delimitados incluem:

FIELDTERMINATOR - o caractere usado para separar valores de campo em cada linha. Por exemplo, um arquivo delimitado por tabulação separa campos com um caractere TAB (\t). O terminador de campo padrão é uma vírgula (,).
ROWTERMINATOR - o caractere usado para significar o fim de uma linha de dados. Por exemplo, um arquivo de texto padrão do Windows usa uma combinação de retorno de carro (CR) e alimentação de linha (LF), que é indicado pelo código \n, enquanto os arquivos de texto no estilo UNIX usam um caractere de alimentação de linha única, que pode ser indicado usando o código 0x0a.
FIELDQUOTE - o caractere usado para incluir valores de cadeia de caracteres entre aspas. Por exemplo, para garantir que a vírgula no valor do campo de endereço 126 Main St, apt 2 não seja interpretada como um delimitador de campo, você pode colocar todo o valor do campo entre aspas como este: "126 Main St, apt 2". A aspa dupla (") é o caractere de aspas de campo padrão.

Gorjeta

Para obter detalhes de parâmetros adicionais ao trabalhar com arquivos de texto delimitados, consulte a documentação do Azure Synapse Analytics.

Especificando o esquema do conjunto de linhas

É comum que arquivos de texto delimitados incluam os nomes das colunas na primeira linha. A função OPENROWSET pode usar isso para definir o esquema para o conjunto de linhas resultante e inferir automaticamente os tipos de dados das colunas com base nos valores que elas contêm. Por exemplo, considere o seguinte texto delimitado:

product_id,product_name,list_price
123,Widget,12.99
124,Gadget,3.99

Os dados consistem nas seguintes três colunas:

product_id (número inteiro)
product_name (string)
list_price (número decimal)

Você pode usar a consulta a seguir para extrair os dados com os nomes de coluna corretos e os tipos de dados do SQL Server inferidos apropriadamente (neste caso, INT, NVARCAR e DECIMAL)

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0',
    HEADER_ROW = TRUE) AS rows

O parâmetro HEADER_ROW (que só está disponível ao usar o analisador versão 2.0) instrui o mecanismo de consulta a usar a primeira linha de dados em cada arquivo como os nomes das colunas, da seguinte forma:

product_id	product_name	list_price
123	Widget	12.9900
124	Gadget	3.9900

Agora considere os seguintes dados:

123,Widget,12.99
124,Gadget,3.99

Desta vez, o arquivo não contém os nomes das colunas em uma linha de cabeçalho; portanto, enquanto os tipos de dados ainda podem ser inferidos, os nomes das colunas serão definidos como C1, C2, C3 e assim por diante.

C1	C2	C3
123	Widget	12.9900
124	Gadget	3.9900

Para especificar nomes de coluna explícitos e tipos de dados, você pode substituir os nomes de coluna padrão e os tipos de dados inferidos fornecendo uma definição de esquema em uma cláusula COM , da seguinte forma:

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.csv',
    FORMAT = 'csv',
    PARSER_VERSION = '2.0')
WITH (
    product_id INT,
    product_name VARCHAR(20) COLLATE Latin1_General_100_BIN2_UTF8,
    list_price DECIMAL(5,2)
) AS rows

Esta consulta produz os resultados esperados:

product_id	product_name	list_price
123	Widget	12.99
124	Gadget	3.99

Gorjeta

Ao trabalhar com arquivos de texto, você pode encontrar alguma incompatibilidade com dados codificados UTF-8 e o agrupamento usado no banco de dados mestre para o pool SQL sem servidor. Para superar isso, você pode especificar um agrupamento compatível para colunas VARCHAR individuais no esquema. Consulte as diretrizes de solução de problemas para obter mais detalhes.

Consultando arquivos JSON

JSON é um formato popular para aplicativos Web que trocam dados por meio de interfaces REST ou usam armazenamentos de dados NoSQL, como o Azure Cosmos DB. Portanto, não é incomum persistir dados como documentos JSON em arquivos em um data lake para análise.

Por exemplo, um arquivo JSON que define um produto individual pode ter esta aparência:

{
    "product_id": 123,
    "product_name": "Widget",
    "list_price": 12.99
}

Para retornar dados do produto de uma pasta que contém vários arquivos JSON nesse formato, você pode usar a seguinte consulta SQL:

SELECT doc
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

OPENROWSET não tem formato específico para arquivos JSON, portanto, você deve usar o formato csv com FIELDTERMINATOR, FIELDQUOTE e ROWTERMINATOR definidos como 0x0b e um esquema que inclua uma única coluna NVARCHAR(MAX). O resultado dessa consulta é um conjunto de linhas contendo uma única coluna de documentos JSON, como este:

doc
{"product_id":123,"product_name":"Widget","list_price": 12.99}
{"product_id":124,"product_name":"Gadget","list_price": 3.99}

Para extrair valores individuais do JSON, você pode usar a função JSON_VALUE na instrução SELECT, conforme mostrado aqui:

SELECT JSON_VALUE(doc, '$.product_name') AS product,
           JSON_VALUE(doc, '$.list_price') AS price
FROM
    OPENROWSET(
        BULK 'https://mydatalake.blob.core.windows.net/data/files/*.json',
        FORMAT = 'csv',
        FIELDTERMINATOR ='0x0b',
        FIELDQUOTE = '0x0b',
        ROWTERMINATOR = '0x0b'
    ) WITH (doc NVARCHAR(MAX)) as rows

Essa consulta retornaria um conjunto de linhas semelhante aos seguintes resultados:

produto	preço
Widget	12.99
Gadget	3.99

Consultando arquivos do Parquet

Parquet é um formato comumente usado para processamento de big data em armazenamento de arquivos distribuídos. É um formato de dados eficiente que é otimizado para compressão e consultas analíticas.

Na maioria dos casos, o esquema dos dados é incorporado dentro do arquivo Parquet, então você só precisa especificar o parâmetro BULK com um caminho para o(s) arquivo(s) que deseja ler e um parâmetro FORMAT de parquet;

SELECT TOP 100 *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/files/*.*',
    FORMAT = 'parquet') AS rows

Consultar dados particionados

É comum em um data lake particionar dados dividindo vários arquivos em subpastas que refletem critérios de particionamento. Isso permite que os sistemas de processamento distribuído trabalhem em paralelo em várias partições dos dados ou eliminem facilmente as leituras de dados de pastas específicas com base em critérios de filtragem. Por exemplo, suponha que você precise processar dados de ordem de venda de forma eficiente e, muitas vezes, precise filtrar com base no ano e no mês em que os pedidos foram feitos. Você pode particionar os dados usando pastas, como esta:

/encomendas
- /ano=2020
  - /mês=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /mês=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /ano=2021
  - /mês=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Para criar uma consulta que filtre os resultados para incluir apenas os pedidos de janeiro e fevereiro de 2020, você pode usar o seguinte código:

SELECT *
FROM OPENROWSET(
    BULK 'https://mydatalake.blob.core.windows.net/data/orders/year=*/month=*/*.*',
    FORMAT = 'parquet') AS orders
WHERE orders.filepath(1) = '2020'
    AND orders.filepath(2) IN ('1','2');

Os parâmetros de caminho de arquivo numerados na cláusula WHERE fazem referência aos curingas nos nomes das pastas no caminho BULK - portanto, o parâmetro 1 é o * no nome da pasta year=* e o parâmetro 2 é o * no nome da pasta month=* .

Consultar arquivos usando um pool SQL sem servidor

Consultando arquivos de texto delimitados

Especificando o esquema do conjunto de linhas

Consultando arquivos JSON

Consultando arquivos do Parquet

Consultar dados particionados

Comentários