Compartilhar via

Consultar os arquivos de armazenamento com o pool de SQL sem servidor no Azure Synapse Analytics

  • Artigo

O pool de SQL sem servidor permite que você consulte dados no data lake. Ele oferece uma área de superfície de consulta T-SQL que acomoda consultas de dados semiestruturados e não estruturados. Para consulta, os seguintes aspectos do T-SQL são compatíveis:

  • Área de superfície de SELECT completa, incluindo a maioria das funções e operadores SQL.
  • CETAS (CREATE EXTERNAL TABLE AS SELECT) cria uma tabela externa e, em seguida, exporta em paralelo os resultados de uma instrução SELECT de Transact-SQL para o Armazenamento do Azure.

Para obter mais informações sobre o que é e o que não é compatível atualmente, leia o artigo Visão geral do pool de SQL sem servidor ou os seguintes artigos:

Visão geral

Para dar suporte a uma experiência tranquila para a consulta no local de dados localizados em arquivos do Armazenamento do Azure, o pool de SQL sem servidor usa a função OPENROWSET com funcionalidades adicionais:

Consultar arquivos PARQUET

Para consultar dados de origem Parquet, use 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

Examine o artigo Consultar arquivos Parquet para obter exemplos de uso.

Consultar arquivos CSV

Para consultar dados de origem CSV, use FORMAT = 'CSV'. Você pode especificar o esquema do arquivo CSV como parte da função OPENROWSET ao consultar arquivos 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

Há algumas outras opções que podem ser usadas para ajustar as regras de análise para o formato CSV personalizado:

  • ESCAPE_CHAR = 'char' especifica o caractere que é usado no arquivo para escapar a si mesmo e todos os valores de delimitador no arquivo. Se o caractere de escape for seguido por um valor diferente dele mesmo ou por um dos valores delimitadores, o caractere de escape será removido durante a leitura do valor. O parâmetro ESCAPE_CHAR será aplicado quer FIELDQUOTE esteja habilitado ou não. Ele não será usado para fazer escape do caractere de aspas. O caractere de aspas deve ter escape com outro caractere de aspas. O caractere de aspas poderá aparecer no valor da coluna somente se o valor for encapsulado com caracteres de aspas.
  • FIELDTERMINATOR ='field_terminator' Especifica o terminador de campo a ser usado. O terminador de campo padrão é uma vírgula (" , ")
  • ROWTERMINATOR ='row_terminator' Especifica o terminador de linha a ser usado. O terminador de linha padrão um caractere de nova linha: \r\n.

Consultar o formato DELTA LAKE

Para consultar dados de origem Delta Lake, use FORMAT = 'DELTA' e faça referência à pasta raiz que contém os arquivos 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 deve conter uma subpasta chamada _delta_log. Consulte o artigo consultar o formato Delta Lake para ver exemplos de uso.

Esquema de arquivo

A linguagem SQL no SQL do Synapse permite que você defina o esquema do arquivo como parte da função OPENROWSET e leia todas as colunas ou um subconjunto delas ou tente determinar automaticamente os tipos de coluna do arquivo usando a inferência de esquema.

Ler um subconjunto escolhido de colunas

Para especificar as colunas que você deseja ler, você pode fornecer uma cláusula WITH opcional dentro da instrução OPENROWSET.

  • Se houver arquivos de dados CSV para ler todas as colunas, forneça nomes de coluna e seus tipos de dados. Se você quiser um subconjunto de colunas, use números ordinais para escolher as colunas dos arquivos de dados de origem por ordinal. As colunas serão vinculadas à designação ordinal.
  • Se houver arquivos de dados Parquet, forneça nomes de coluna que correspondam aos nomes de coluna nos arquivos de dados de origem. As colunas serão vinculadas ao 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, você precisa especificar o nome da coluna e digitar a cláusula WITH. Para obter exemplos, veja Ler arquivos CSV sem especificar todas as colunas.

Inferência de esquema

Ao omitir a cláusula WITH da instrução OPENROWSET, você pode instruir o serviço a detectar automaticamente (inferir) o esquema dos arquivos subjacentes.

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

Verifique se os tipos de dados inferidos apropriados são usados para um desempenho ideal.

Consultar vários arquivos ou pastas

Para executar uma consulta T-SQL em um conjunto de arquivos dentro de uma pasta ou conjunto de pastas e tratá-los como uma entidade ou um conjunto de linhas, forneça um caminho para uma pasta ou um padrão (usando curingas) em um conjunto de arquivos ou pastas.

As seguintes regras se aplicam:

  • Os padrões podem aparecer em parte de um caminho de diretório ou em um nome de arquivo.
  • Vários padrões podem aparecer na mesma etapa de diretório ou nome de arquivo.
  • Se houver vários curingas, os arquivos em todos os caminhos correspondentes serão incluídos no conjunto de arquivos resultante.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Veja Consultar pastas e vários arquivos para obter exemplos de uso.

Funções de metadados de arquivo

Função filename

Essa função retorna o nome do arquivo de origem da linha.

Para consultar arquivos específicos, leia a seção Filename no artigo Consultar arquivos específicos.

O tipo de dados retornado é nvarchar (1024). Para obter um desempenho ideal, sempre converta o resultado da função filename para o tipo de dados apropriado. Se você usar o tipo de dados de caractere, use o comprimento apropriado.

Função filepath

Essa função retorna um caminho completo ou uma parte do caminho:

  • Quando chamada sem parâmetro, retorna o caminho de arquivo completo da origem de uma linha.
  • Quando chamada com parâmetro, ela retorna parte do caminho que corresponda ao curinga na posição especificada no parâmetro. Por exemplo, o valor de parâmetro 1 retornaria a parte do caminho que corresponde ao primeiro caractere curinga.

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

O tipo de dados retornado é nvarchar (1024). Para obter um desempenho ideal, sempre converta o resultado da função filepath para o tipo de dados apropriado. Se você usar o tipo de dados de caractere, use o comprimento apropriado.

Trabalhar com tipos complexos e estruturas de dados aninhadas ou repetidas

Para permitir uma experiência tranquila com os dados armazenados em tipos de dados aninhados ou repetidos, como em arquivos Parquet, o pool de SQL sem servidor adicionou as extensões a seguir.

Dados de projeto aninhados ou repetidos

Para projetar dados, execute uma instrução SELECT no arquivo Parquet que contém colunas de tipos de dados aninhados. Na saída, os valores aninhados serão serializados em JSON e retornados 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, veja a seção Dados de projeto aninhados ou repetidos do artigo Consulta de tipos aninhados de Parquet.

Elementos de acesso de colunas aninhadas

Para acessar elementos aninhados de uma coluna aninhada, como Struct, use "notação de ponto" para concatenar nomes de campo no caminho. Forneça o caminho como column_name na cláusula WITH da função OPENROWSET.

O exemplo de fragmento da sintaxe fica como segue:

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

Por padrão, a função OPENROWSET corresponde ao nome e ao caminho do campo de origem com os nomes de coluna fornecidos na cláusula WITH. Os elementos contidos em diferentes níveis de aninhamento dentro do mesmo arquivo Parquet de origem podem ser acessados por meio da cláusula WITH.

Valores retornados

  • A função retorna um valor escalar como int, decimal e varchar, do elemento especificado e no caminho especificado, para todos os tipos de Parquet que não estão no grupo Tipo Aninhado.
  • Se o caminho aponta para um elemento que é de um Tipo Aninhado, a função retorna um fragmento JSON começando do elemento superior no caminho especificado. O fragmento JSON é do tipo varchar (8000).
  • Se a propriedade não puder ser encontrada na column_name especificada, a função retornará um erro.
  • Se a propriedade não puder ser encontrada no column_path especificado, dependendo do Modo de caminho, a função retornará um erro quando estiver no modo estrito ou nulo quando estiver no modo lax.

Para obter exemplos de consulta, examine a seção Elementos de acesso de colunas aninhadas no artigo Consultar tipos aninhados de Parquet.

Acessar elementos de colunas repetidas

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

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

Para acessar elementos não escalares de uma coluna repetida, use a função JSON_QUERY para cada elemento não escalar que você precisa projetar e fornecer:

  • Coluna aninhada ou repetida, como o primeiro parâmetro
  • Um caminho JSON que especifica o elemento ou a propriedade a ser acessada, como um 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]

Você pode encontrar exemplos de consulta para acessar elementos de colunas repetidas no artigo Consultar tipos aninhados Parquet.

Exemplos de consulta

Você pode aprender mais sobre como consultar vários tipos de dados usando as consultas de exemplo.

Ferramentas

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

Configuração de demonstração

A primeira etapa é criar um banco de dados no qual você executará as consultas. Em seguida, você vai inicializar os objetos executando o script de instalação nesse banco de dados.

Esse script de instalação criará as fontes de dados, as credenciais no escopo do banco de dados e os formatos de arquivo externos que são usados para ler os dados nestas amostras.

Observação

Os bancos de dados são usados apenas para exibição de metadados, não para dados propriamente ditos. Anote o nome do banco de dados que você usa, você 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 de NYC – registros de corrida de táxi amarelo – parte do conjunto de dados públicos de NYC no formato CSV e Parquet
  • Conjunto de dados populacionais no formato CSV
  • Arquivos Parquet de exemplo com colunas aninhadas
  • Livros no formato JSON
Caminho da pasta Descrição
/csv/ Pasta pai para dados no formato CSV
/csv/population/
/csv/population-unix/
/csv/population-unix-hdr/
/csv/population-unix-hdr-escape
/csv/population-unix-hdr-quoted		 Pastas com arquivos de dados de população em formatos CSV diferentes.
/csv/taxi/ Pasta com arquivos de dados públicos de Nova York no formato CSV
/parquet/ Pasta pai para dados no formato Parquet
/parquet/taxi Arquivos de dados públicos de Nova York no formato Parquet, particionados por ano e mês, usando o esquema de particionamento do Hive/Hadoop.
/parquet/nested/ Arquivos Parquet de exemplo com colunas aninhadas
/json/ Pasta pai para dados no formato JSON
/json/books/ Arquivos JSON com os dados de livros

Próximas etapas

Para obter mais informações sobre como consultar diferentes tipos de arquivo e para criar e usar exibições, confira os seguintes artigos: