Partilhar via

Consultar arquivos de armazenamento usando o pool SQL sem servidor

O pool SQL sem servidor permite que você consulte dados em seu data lake. Ele oferece uma área de superfície de consulta Transact-SQL (T-SQL) que acomoda consultas de dados semiestruturadas e não estruturadas. Para consulta, os seguintes aspetos T-SQL são suportados:

Para obter mais informações sobre o que é ou não suportado atualmente, leia a visão geral do pool SQL sem servidor ou os seguintes artigos:

  • Desenvolva o acesso ao armazenamento onde você pode usar tabelas externas e a função OPENROWSET para ler dados do armazenamento.
  • Controle o acesso ao armazenamento onde você pode aprender a habilitar o Synapse SQL para acessar o armazenamento usando a autenticação SAS ou a Identidade Gerenciada do espaço de trabalho.

Descrição geral

Para dar suporte a uma experiência suave para consulta in-loco de dados localizados em arquivos de Armazenamento do Azure, o pool SQL sem servidor usa a função OPENROWSET com mais recursos:

Consultar arquivos PARQUET

To query Parquet source data, 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

Para obter exemplos de uso, consulte Arquivos do Query Parquet.

Consultar ficheiros CSV

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

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

  • ESCAPE_CHAR = 'char' Specifies the character in the file that's used to escape itself and all delimiter values in the file. Se o caractere de escape for seguido por um valor diferente de si mesmo, ou qualquer um dos valores do delimitador, o caractere de escape será descartado ao ler o valor. O parâmetro ESCAPE_CHAR é aplicado quer o FIELDQUOTE esteja ou não habilitado. It isn't used to escape the quoting character. The quoting character must be escaped with another quoting character. Quoting character can appear within column value only if value is encapsulated with quoting characters.
  • 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. The default row terminator is a newline character (\r\n).

Consultar o formato DELTA LAKE

Para consultar os dados de origem do Delta Lake, use FORMAT = 'DELTA' e faça referência à pasta raiz que contém os arquivos 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 deve conter uma subpasta chamada _delta_log. For usage examples, see Query Delta Lake (v1) files.

Esquema de arquivo

A linguagem SQL no Synapse SQL permite definir o esquema do arquivo como parte da OPENROWSET função e ler todas ou subconjuntos de colunas, ou tenta determinar automaticamente os tipos de coluna do arquivo usando inferência de esquema.

Ler um subconjunto de colunas escolhido

To specify columns that you want to read, you can provide an optional WITH clause within your OPENROWSET statement.

  • Se houver arquivos de dados CSV, forneça nomes de colunas e seus tipos de dados para ler todas as colunas. If you want a subset of columns, use ordinal numbers to pick the columns from the originating data files by ordinal. Columns are bound by the ordinal designation.
  • Se existirem ficheiros de dados Parquet, forneça nomes de colunas que correspondam aos nomes de colunas dos ficheiros de dados de origem. Columns are bound by name.
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 WITH cláusula. Para exemplos, consulte Ler arquivos CSV sem especificar todas as colunas.

Schema inference

Ao omitir a cláusula WITH da instrução OPENROWSET, pode-se instruir o serviço a detetar automaticamente (inferir) o esquema dos 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 apropriados sejam usados para um desempenho ideal.

Consultar vários arquivos ou pastas

Para executar uma consulta T-SQL sobre um conjunto de arquivos dentro de uma pasta ou conjunto de pastas, tratando-os como uma única entidade ou conjunto de linhas, forneça um caminho para uma pasta ou um padrão (usando curingas) em um conjunto de arquivos ou pastas.

Aplicam-se as seguintes regras:

  • 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.
  • If there are multiple wildcards, then files within all matching paths are included in the resulting file set.
SELECT * FROM
OPENROWSET( BULK N'https://myaccount.dfs.core.windows.net/myroot/*/mysubfolder/*.parquet', FORMAT = 'PARQUET' ) as rows

Para obter exemplos de uso, consulte Consultar pastas e vários arquivos.

Funções de metadados de arquivo

Função de nome de arquivo

Esta função retorna o nome do arquivo do qual a linha se origina.

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

O tipo de dados de retorno é nvarchar(1024). Para um desempenho ideal, sempre converta o resultado da função de nome de arquivo para o tipo de dados apropriado. Se utilizares o tipo de dados de caracteres, assegura-te de que o tamanho apropriado é usado.

Função Filepath

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

  • Quando chamado sem parâmetro, retorna o caminho completo do arquivo do qual uma linha se origina.
  • When called with parameter, it returns part of path that matches the wildcard on position specified in the parameter. For example, parameter value 1 would return part of path that matches the first wildcard.

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

O tipo de dados de retorno é nvarchar(1024). Para um desempenho ideal, sempre converta o resultado da função filepath para o tipo de dados apropriado. Se utilizares o tipo de dados de caracteres, assegura-te de que o tamanho apropriado é usado.

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 arquivos Parquet , o pool SQL sem servidor adicionou as seguintes extensões.

Projetar dados aninhados ou repetidos

To project data, run a SELECT statement over the Parquet file that contains columns of nested data types. Na saída, os valores aninhados são serializados para JSON e retornados como um tipo de dados SQL varchar(8000).

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

For more information, see the Project nested or repeated data section of the Query Parquet nested types article.

Access elements from nested columns

To access nested elements from a nested column, such as Struct, use dot notation to concatenate field names into the path. Provide the path as column_name in the WITH clause of the OPENROWSET function.

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'

By default, the OPENROWSET function matches the source field name and path with the column names provided in the WITH clause. Elements contained at different nesting levels within the same source Parquet file can be accessed using the WITH clause.

Valores de retorno

  • Function returns a scalar value, such as int, decimal, and varchar, from the specified element, and on the specified path, for all Parquet types that aren't in the Nested Type group.
  • If the path points to an element that is of a Nested Type, the function returns a JSON fragment starting from the top element on the specified path. O fragmento JSON é do tipo varchar(8000).
  • Se a propriedade não puder ser encontrada no especificado column_name, a função retornará um erro.
  • If the property can't be found at the specified column_path, depending on Path mode, the function returns an error when in strict mode or null when in lax mode.

For query samples, see the Read properties from nested object columns section in the Query Parquet nested types article.

Access elements from repeated columns

Para acessar elementos de uma coluna repetida, como um elemento de uma matriz ou 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
  • A JSON path that specifies the element or property to access, as a second parameter

Para acessar elementos não escalares de uma coluna repetida, use a função JSON_QUERY para cada elemento não escalar necessário para projetar e forneça:

  • Coluna aninhada ou repetida, como o primeiro parâmetro
  • A JSON path that specifies the element or property to access, as a second parameter

Consulte o seguinte fragmento de sintaxe:

    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]

You can find query samples for accessing elements from repeated columns in the Query Parquet nested types article.

Conteúdos relacionados

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