Partilhar via

Consultar arquivos de armazenamento usando o pool SQL sem servidor

  • Artigo

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

Para consultar os dados de origem do 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

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' Especifica o caractere no arquivo que é usado para escapar de si mesmo e todos os valores de delimitador no arquivo. 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 ESCAPE_CHAR parâmetro é aplicado quer o esteja ou não habilitado FIELDQUOTE . Ele não é usado para escapar do personagem citador. O caractere de citação deve ser escapado com outro caractere de citação. O caractere de citação pode aparecer dentro do 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).

Formato de consulta 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. Para obter exemplos de uso, consulte Consultar arquivos Delta Lake (v1).

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

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

  • Se houver arquivos de dados CSV, forneça nomes de colunas e seus tipos de dados para ler todas as colunas. Se desejar um subconjunto de colunas, use números ordinais para escolher as colunas dos arquivos de dados de origem por ordinal. As colunas são vinculadas pela 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 sã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, você precisa especificar o nome da coluna e digitar a WITH cláusula. Para exemplos, consulte Ler arquivos CSV sem especificar todas as colunas.

Inferência do esquema

Ao omitir a WITHOPENROWSET cláusula da instrução, você pode instruir o serviço a detetar automaticamente (inferir) o esquema dos arquivos 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.
  • Se houver vários curingas, os arquivos dentro de 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

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 você usar o tipo de dados de caracteres, verifique se o comprimento 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.
  • Quando chamado com parâmetro, ele retorna parte do caminho que corresponde ao curinga na posição especificada no parâmetro. Por exemplo, o valor do parâmetro 1 retornaria parte do caminho que corresponde ao primeiro curinga.

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 você usar o tipo de dados de caracteres, verifique se o comprimento 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.

Dados aninhados ou repetidos do projeto

Para projetar dados, execute uma SELECT instrução sobre o arquivo Parquet que contém colunas de tipos de dados aninhados. Na saída, os valores aninhados sã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 mais informações, consulte a seção Project nested or repeated data do artigo Query Parquet nested types .

Acessar elementos de colunas aninhadas

Para acessar elementos aninhados de uma coluna aninhada, como Struct, use a notação de pontos para concatenar nomes de campo no caminho. Forneça o caminho como column_name na WITH cláusula 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 padrão, a OPENROWSET função corresponde ao nome do campo de origem e ao caminho com os nomes de coluna fornecidos na WITH cláusula. Elementos contidos em diferentes níveis de aninhamento dentro do mesmo arquivo Parquet de origem podem ser acessados usando a WITH cláusula.

Valores de retorno

  • A função retorna um valor escalar, como int, decimale 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 apontar para um elemento que é de um tipo aninhado, a função retorna um fragmento JSON a partir do elemento superior no caminho especificado. 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.
  • Se a propriedade não puder ser encontrada no , especificado column_path, dependendo do modo Path, a função retornará um erro quando estiver no modo estrito ou null quando estiver no modo lax.

Para obter exemplos de consulta, consulte a seção Ler propriedades de colunas de objeto aninhado no artigo Tipos aninhados do Parquet de Consulta.

Acessar elementos de colunas repetidas

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
  • Um caminho JSON que especifica o elemento ou propriedade a ser acessado, 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 necessário para projetar e forneça:

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

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]

Você pode encontrar exemplos de consulta para acessar elementos de colunas repetidas no artigo Tipos aninhados do Parquet de Consulta.

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: