Partilhar via

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

  • Artigo

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

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

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

  • Desenvolva o acesso ao armazenamento onde você pode aprender a usar a tabela externa 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 consultas in-loco de dados localizados em arquivos de Armazenamento do Azure, o pool SQL sem servidor usa a função OPENROWSET com recursos adicionais:

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

Consulte o artigo Arquivos do Query Parquet para obter exemplos de uso.

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 adicionais que podem ser usadas para ajustar as regras de análise ao formato CSv personalizado:

  • ESCAPE_CHAR = 'char' Especifica o caractere no arquivo que é usado para escapar de si mesmo e todos os valores do 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 parâmetro ESCAPE_CHAR será aplicado independentemente de o FIELDQUOTE estar ou não habilitado. Ele não será 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. Analise o artigo de formato Delta Lake de consulta para obter exemplos de uso.

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 a inferência de esquema.

Ler um subconjunto de colunas escolhido

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

  • Se houver arquivos de dados CSV, para ler todas as colunas, forneça nomes de colunas e seus tipos de dados. Se desejar 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 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 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, 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 cláusula WITH da OPENROWSET instrução, você pode instruir o serviço a detetar automaticamente (inferir) o esquema a partir de 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

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

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 extensões a seguir.

Dados aninhados ou repetidos do projeto

Para projetar dados, execute uma instrução SELECT sobre o 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, 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 "notação de ponto" para concatenar nomes de campo no caminho. Forneça o caminho conforme 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 padrão, a OPENROWSET função corresponde ao nome do campo de origem e ao caminho com os nomes de coluna fornecidos na cláusula COM. 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 de retorno

  • 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 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 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 Path, a função retornará um erro quando estiver no modo estrito ou null quando estiver no modo lax.

Para exemplos de consulta, revise a seção Elementos do Access de colunas aninhadas 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 a partir de uma coluna repetida, use a função JSON_QUERY para cada elemento não escalar que você precisa 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

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 Tipos aninhados do Parquet de Consulta.

Próximos passos

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: