Consultar arquivos usando um pool de SQL sem servidor

10 minutos

Você pode usar um pool de 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 JSON (JavaScript Object Notation).
Arquivos 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 por meio dos dados de 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.

Dica

Você vai 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 files.

Observação

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

Como visto no exemplo anterior, você pode usar caracteres curinga no parâmetro BULK para incluir ou excluir arquivos na consulta. A seguinte lista 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 files.
https://mydatalake.blob.core.windows.net/data/files/file*.csv: todos os arquivos .csv da pasta files com nomes que comecem com "file".
https://mydatalake.blob.core.windows.net/data/files/*: todos os arquivos da pasta files.
https://mydatalake.blob.core.windows.net/data/files/**: todos os arquivos da pasta files e, recursivamente, as respectivas subpastas.

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

Como consultar arquivos de texto delimitados

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 ou sem a linha de cabeçalho.
Valores delimitados por tabulação e vírgula.
Terminações de linha no estilo do Windows e do Unix.
Valores com e sem aspas e caracteres de escape.

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 dá suporte a uma ampla gama de codificações de arquivo, enquanto a versão 2.0 dá suporte a menos codificações, mas oferece melhor desempenho. O parâmetro FIRSTROW é usado para ignorar linhas no arquivo de texto, para eliminar qualquer texto preâmbulo não estruturado ou para ignorar uma linha que já contenha títulos de coluna.

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

FIELDTERMINATOR – 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 – caractere usado para significar o fim de uma linha de dados. Por exemplo, um arquivo de texto padrão Windows usa uma combinação de CR (retorno de carro) e LF (alimentação de linha), que é indicado pelo código \n; enquanto arquivos de texto no estilo UNIX usam apenas um caractere de alimentação de linha, que pode ser indicado usando o código 0x0a.
FIELDQUOTE - 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 assim: "126 Main St, apt 2". A aspa dupla (") é o caractere de citação de campo padrão.

Dica

Para obter detalhes de parâmetros adicionais ao trabalhar com arquivos de texto delimitados, confira 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 do 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 três colunas seguintes:

product_id (número inteiro)
product_name (cadeia de caracteres)
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 adequadamente (nesse caso, INT, NVARCHAR 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 de coluna, da seguinte maneira:

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 de coluna em uma linha de cabeçalho; portanto, embora os tipos de dados ainda possam ser inferidos, os nomes de coluna serão definidos como C1, C2, C3 e assim por diante.

C1	C2	C3
123	Widget	12.9900
124	Gadget	3.9900

Para especificar tipos de dados e nomes de coluna explícitos, 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 WITH, da seguinte maneira:

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

Essa consulta produz os resultados esperados:

product_id	product_name	list_price
123	Widget	12.99
124	Gadget	3,99

Dica

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

Como consultar arquivos JSON

O 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 BD. 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 um 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 inclui apenas uma coluna NVARCHAR(MAX). O resultado dessa consulta é um conjunto de linhas que contém apenas uma coluna de documentos JSON, assim:

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:

product	price
Widget	12.99
Gadget	3,99

Consultar arquivos Parquet

Parquet é um formato normalmente usado para processamento de Big Data no armazenamento de arquivos distribuídos. É um formato de dados eficiente que é otimizado para compactação e consulta analítica.

Na maioria dos casos, o esquema dos dados é inserido no arquivo Parquet, portanto, você só precisa especificar o parâmetro BULK com um caminho para os arquivos que você deseja ler e um parâmetro FORMAT do parquet; Assim:

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ídos 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 com eficiência os dados do pedido de vendas 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, assim:

/orders
- /year=2020
  - /month=1
    - /01012020.parquet
    - /02012020.parquet
    - ...
  - /month=2
    - /01022020.parquet
    - /02022020.parquet
    - ...
  - ...
- /year=2021
  - /month=1
    - /01012021.parquet
    - /02012021.parquet
    - ...
  - ...

Para criar uma consulta que filtra 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 filepath numerados na cláusula WHERE fazem referência aos caracteres curinga nos nomes de pasta do 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 de SQL sem servidor

Como consultar arquivos de texto delimitados

Especificando o esquema do conjunto de linhas

Como consultar arquivos JSON

Consultar arquivos Parquet

Consultar dados particionados

Comentários