Consultar arquivos usando um pool de SQL sem servidor

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írgulas).
• Arquivos JSON (notação de objeto JavaScript).
• 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 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.

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ê o texto delimitado de todos os arquivos .csv na pasta de arquivos .

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 BULK , separando cada caminho com uma vírgula.

Consultando 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 e sem uma linha de cabeçalho.
• Valores delimitados por vírgula e tabulação.
• Terminações de linha de estilo Do Windows e Unix.
• Valores não citados e entre 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, eliminar qualquer texto preâmbulo não estruturado ou ignorar uma linha que contém títulos de coluna.

Os parâmetros adicionais que você pode exigir 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 final de uma linha de dados. Por exemplo, um arquivo de texto padrão do Windows usa uma combinação de cr (retorno de carro) e LF (feed de linha), que é indicado pelo código \n; enquanto os arquivos de texto no estilo UNIX usam um único caractere de feed de linha, que pode ser indicado usando o código 0x0a.
• FIELDQUOTE - o caractere usado para colocar entre os 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 aspa de campo padrão.

Especificando o esquema de conjunto de linhas

É comum que arquivos de texto delimitados incluam os nomes de coluna 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 três colunas a seguir:

• 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 tipos de dados do SQL Server apropriadamente inferidos (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, desta forma:

product_id	product_name	list_price
123	Widget	12.9900
124	Engenhoca	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	Engenhoca	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 WITH , assim:

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	Engenhoca	3,99

Consultando 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 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 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 uma única coluna NVARCHAR(MAX). O resultado dessa consulta é um conjunto de linhas que contém uma única 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:

produto	price
Widget	12.99
Engenhoca	3,99

Consultando arquivos Parquet

Parquet é um formato comumente usado para processamento de Big Data no armazenamento de arquivos distribuído. É um formato de dados eficiente 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 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-se entre vários arquivos em subpastas que refletem critérios de particionamento. Isso permite que os sistemas de processamento distribuído funcionem em paralelo em várias partições dos dados ou eliminem facilmente as leituras de dados de pastas específicas com base nos critérios de filtragem. Por exemplo, suponha que você precise processar com eficiência os dados do pedido de vendas e, muitas vezes, precisa filtrar com base no ano e no mês em que os pedidos foram feitos. Você pode particionar os dados usando pastas, desta forma:

• /Ordens
  • /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 de caminho de arquivo numerados na cláusula WHERE fazem referência aos curingas nos nomes de pasta no caminho BULK -so o parâmetro 1 é o nome da pasta * no ano=* e o parâmetro 2 é o * no nome da pasta month=* .