Consultar ficheiros de armazenamento com conjunto de SQL sem servidor no Azure Synapse Analytics

  • Artigo

O conjunto de SQL sem servidor permite-lhe consultar dados no data lake. Oferece uma área de superfície de consulta T-SQL que acomoda consultas de dados semiestruturadas e não estruturadas. Para consulta, são suportados os seguintes aspetos do T-SQL:

  • Área completa da superfície SELECT , incluindo a maioria das funções e operadores SQL.
  • CRIAR TABELA EXTERNA COMO SELECT (CETAS) cria uma tabela externa e, em seguida, exporta, em paralelo, os resultados de uma instrução SELECT transact-SQL para o Armazenamento do Azure.

Para obter mais informações sobre o que é vs. o que não é atualmente suportado, leia o artigo de descrição geral do conjunto de SQL sem servidor ou os seguintes artigos:

Descrição Geral

Para suportar uma experiência suave para a consulta no local de dados localizados em ficheiros de Armazenamento do Azure, o conjunto de SQL sem servidor utiliza a função OPENROWSET com capacidades adicionais:

Consultar ficheiros PARQUET

Para consultar os dados de origem parquet, utilize FORMAT = "PARQUET":

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Veja o artigo Consultar ficheiros Parquet para obter exemplos de utilização.

Consultar ficheiros CSV

Para consultar dados de origem CSV, utilize FORMAT = "CSV". Pode especificar o esquema do ficheiro CSV como parte da OPENROWSET função quando consulta ficheiros CSV:

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.csv', FORMAT = 'CSV', PARSER_VERSION='2.0') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

Existem algumas opções adicionais que podem ser utilizadas para ajustar as regras de análise ao formato CSv personalizado:

  • ESCAPE_CHAR = 'caráter' Especifica o caráter no ficheiro que é utilizado para escapar a si próprio e a todos os valores delimitadores no ficheiro. Se o caráter de escape for seguido por um valor diferente de si ou por qualquer um dos valores delimitadores, o caráter de escape é removido ao ler o valor. O parâmetro ESCAPE_CHAR será aplicado quer o FIELDQUOTE esteja ou não ativado. Não será utilizado para escapar ao caráter de citação. O caráter de citação tem de ser escapado com outro caráter de citação. O caráter de citação só pode aparecer dentro do valor da coluna se o valor for encapsulado com carateres de citação.
  • FIELDTERMINATOR ='field_terminator' Especifica o terminador de campo a ser utilizado. O terminador de campo predefinido é uma vírgula (",")
  • ROWTERMINATOR ='row_terminator' Especifica o terminador de linha a ser utilizado. O terminador de linha predefinido é um caráter de nova linha: \r\n.

Query DELTA LAKE format (Formato DELTA LAKE de consulta)

Para consultar dados de origem do Delta Lake, utilize FORMAT = "DELTA" e faça referência à pasta raiz que contém os seus ficheiros do Delta Lake.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder', FORMAT = 'DELTA') 
WITH (C1 int, C2 varchar(20), C3 varchar(max)) as rows

A pasta raiz tem de conter uma subpasta chamada _delta_log. Veja o artigo de formatação do Delta Lake da consulta para obter exemplos de utilização.

Esquema de ficheiro

A linguagem SQL no SQL do Synapse permite-lhe definir o esquema do ficheiro como parte da OPENROWSET função e ler todo ou subconjunto de colunas, ou tenta determinar automaticamente os tipos de coluna do ficheiro através da inferência de esquema.

Ler um subconjunto de colunas escolhido

Para especificar as colunas que pretende ler, pode fornecer uma cláusula WITH opcional na sua OPENROWSET instrução.

  • Se existirem ficheiros de dados CSV, para ler todas as colunas, forneça os nomes das colunas e os respetivos tipos de dados. Se quiser um subconjunto de colunas, utilize números ordinais para escolher as colunas dos ficheiros de dados de origem por ordinal. As colunas serão vinculadas pela designação ordinal.
  • Se existirem ficheiros de dados parquet, forneça nomes de colunas que correspondam aos nomes das colunas nos ficheiros de dados de origem. As colunas serão vinculadas pelo nome.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET') 
WITH (
      C1 int, 
      C2 varchar(20),
      C3 varchar(max)
) as rows

Para cada coluna, tem de especificar o nome da coluna e escrever a WITH cláusula. Para obter exemplos, veja Ler ficheiros CSV sem especificar todas as colunas.

Inferência do esquema

Ao omitir a cláusula WITH da OPENROWSET instrução, pode instruir o serviço a detetar automaticamente (inferir) o esquema a partir de ficheiros subjacentes.

SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/mycontainer/mysubfolder/data.parquet', FORMAT = 'PARQUET')

Certifique-se de que os tipos de dados inferidos adequados são utilizados para um desempenho ideal.

Consultar vários ficheiros ou pastas

Para executar uma consulta T-SQL através de um conjunto de ficheiros numa pasta ou conjunto de pastas enquanto os trata como uma única entidade ou conjunto de linhas, forneça um caminho para uma pasta ou padrão (utilizando carateres universais) através de um conjunto de ficheiros ou pastas.

Aplicam-se as seguintes regras:

  • Os padrões podem aparecer em parte de um caminho de diretório ou num nome de ficheiro.
  • Vários padrões podem aparecer no mesmo passo de diretório ou nome de ficheiro.
  • Se existirem vários carateres universais, os ficheiros dentro de todos os caminhos correspondentes serão incluídos no conjunto de ficheiros resultante.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Veja Pastas de consulta e vários ficheiros para obter exemplos de utilização.

Funções de metadados de ficheiros

Função nome de ficheiro

Esta função devolve o nome de ficheiro do qual a linha tem origem.

Para consultar ficheiros específicos, leia a secção Nome do ficheiro no artigo Consultar ficheiros específicos .

O tipo de dados devolvido é nvarchar(1024). Para um desempenho ideal, crie sempre o resultado da função filename para o tipo de dados adequado. Se utilizar o tipo de dados de carateres, certifique-se de que é utilizado o comprimento adequado.

Função Filepath

Esta função devolve um caminho completo ou uma parte do caminho:

  • Quando chamado sem parâmetro, devolve o caminho de ficheiro completo do qual uma linha tem origem.
  • Quando chamado com parâmetro, devolve parte do caminho que corresponde ao caráter universal na posição especificada no parâmetro. Por exemplo, o valor do parâmetro 1 devolveria parte do caminho que corresponde ao primeiro caráter universal.

Para obter informações adicionais, leia a secção Filepath do artigo Consultar ficheiros específicos .

O tipo de dados devolvido é nvarchar(1024). Para um desempenho ideal, crie sempre o resultado da função filepath para o tipo de dados adequado. Se utilizar o tipo de dados de carateres, certifique-se de que é utilizado o comprimento adequado.

Trabalhar com tipos complexos e estruturas de dados aninhadas ou repetidas

Para permitir uma experiência suave com dados armazenados em tipos de dados aninhados ou repetidos, como em ficheiros Parquet , o conjunto de SQL sem servidor adicionou as extensões que se seguem.

Dados aninhados ou repetidos do projeto

Para projetar dados, execute uma instrução SELECT sobre o ficheiro Parquet que contém colunas de tipos de dados aninhados. Na saída, os valores aninhados serão serializados em JSON e devolvidos como um tipo de dados SQL varchar(8000).

    SELECT * FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Para obter informações mais detalhadas, consulte a secção Dados aninhados ou repetidos do Projeto do artigo Tipos aninhados do Parquet de Consulta .

Aceder a elementos de colunas aninhadas

Para aceder a elementos aninhados a partir de uma coluna aninhada, como Struct, utilize "notação de pontos" para concatenar nomes de campos no caminho. Indique o caminho como column_name na cláusula WITH da OPENROWSET função.

O exemplo de fragmento de sintaxe é o seguinte:

    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    WITH ({'column_name' 'column_type',})
    [AS alias]
    'column_name' ::= '[field_name.] field_name'

Por predefinição, a OPENROWSET função corresponde ao nome e caminho do campo de origem com os nomes de coluna fornecidos na cláusula WITH. Os elementos contidos em diferentes níveis de aninhamento no mesmo ficheiro Parquet de origem podem ser acedidos através da cláusula WITH.

Valores de retorno

  • A função devolve um valor escalar, como int, decimal e varchar, do elemento especificado e no caminho especificado, para todos os tipos parquet que não estão no grupo Tipo Aninhado.
  • Se o caminho apontar para um elemento que é de um Tipo Aninhado, a função devolve um fragmento JSON a partir do elemento superior no caminho especificado. O fragmento JSON é do tipo varchar(8000).
  • Se não for possível encontrar a propriedade no column_name especificado, a função devolve um erro.
  • Se não for possível encontrar a propriedade no column_path especificado, dependendo do modo Caminho, a função devolve um erro quando estiver em modo estrito ou nulo quando estiver no modo laxista.

Para obter exemplos de consulta, reveja a secção Elementos do Access de colunas aninhadas no artigo Tipos aninhados do Parquet de Consulta .

Aceder a elementos de colunas repetidas

Para aceder a elementos de uma coluna repetida, como um elemento de uma Matriz ou Mapa, utilize a função JSON_VALUE para cada elemento escalar que precisa de projetar e fornecer:

  • Coluna aninhada ou repetida, como o primeiro parâmetro
  • Um caminho JSON que especifica o elemento ou propriedade a aceder, como segundo parâmetro

Para aceder a elementos não escalares a partir de uma coluna repetida, utilize a função JSON_QUERY para cada elemento não escalar que precise de projetar e fornecer:

  • Coluna aninhada ou repetida, como o primeiro parâmetro
  • Um caminho JSON que especifica o elemento ou propriedade a aceder, como segundo parâmetro

Veja o fragmento de sintaxe abaixo:

    SELECT
       { JSON_VALUE (column_name, path_to_sub_element), }
       { JSON_QUERY (column_name [ , path_to_sub_element ]), )
    FROM
    OPENROWSET
    (   BULK 'unstructured_data_path' ,
        FORMAT = 'PARQUET' )
    [AS alias]

Pode encontrar exemplos de consulta para aceder a elementos de colunas repetidas no artigo Tipos aninhados do Parquet de Consulta .

Exemplos de consulta

Pode saber mais sobre como consultar vários tipos de dados com as consultas de exemplo.

Ferramentas

As ferramentas necessárias para emitir consultas: - Azure Synapse Studio - Azure Data Studio - SQL Server Management Studio

Configuração da demonstração

O primeiro passo é criar uma base de dados onde irá executar as consultas. Em seguida, irá inicializar os objetos ao executar o script de configuração nessa base de dados.

Este script de configuração irá criar as origens de dados, as credenciais no âmbito da base de dados e os formatos de ficheiro externos que são utilizados para ler dados nestes exemplos.

Nota

As bases de dados são utilizadas apenas para visualizar metadados e não para dados reais. Anote o nome da base de dados que utiliza. Irá precisar dele mais tarde.

CREATE DATABASE mydbname;

Dados de demonstração fornecidos

Os dados de demonstração contêm os seguintes conjuntos de dados:

  • Táxi NYC - Yellow Taxi Trip Records - parte do conjunto de dados públicos de NYC no formato CSV e Parquet
  • Conjunto de dados da população no formato CSV
  • Exemplo de ficheiros Parquet com colunas aninhadas
  • Livros no formato JSON
Folder path Descrição
/csv/ Pasta principal para dados no formato CSV
/csv/população/
/csv/population-unix/
/csv/population-unix-hdr/
/csv/population-unix-hdr-escape
/csv/population-unix-hdr-quoted		 Pastas com ficheiros de dados População em diferentes formatos CSV.
/csv/taxi/ Pasta com ficheiros de dados públicos de NYC no formato CSV
/parquet/ Pasta principal para dados no formato Parquet
/parquet/táxi Ficheiros de dados públicos de NYC no formato Parquet, particionados por ano e mês com o esquema de criação de partições hive/Hadoop.
/parquet/aninhado/ Exemplo de ficheiros Parquet com colunas aninhadas
/json/ Pasta principal para dados no formato JSON
/json/books/ Ficheiros JSON com dados de livros

Passos seguintes

Para obter mais informações sobre como consultar diferentes tipos de ficheiro e criar e utilizar vistas, veja os seguintes artigos: