Consultar ficheiros CSV

  • Artigo

Neste artigo, você aprenderá a consultar um único arquivo CSV usando o pool SQL sem servidor no Azure Synapse Analytics. Os ficheiros CSV podem ter diferentes formatos:

  • Com e sem linha de cabeçalho
  • Valores delimitados por vírgulas e tabulações
  • Terminações de linha no estilo Windows e Unix
  • Valores não cotados e cotados e caracteres que escapam

Todas as variações acima serão abordadas abaixo.

Exemplo de início rápido

OPENROWSET permite que você leia o conteúdo do arquivo CSV, fornecendo o URL para o seu arquivo.

Ler um arquivo csv

A maneira mais fácil de ver o conteúdo do seu CSV arquivo é fornecer URL de arquivo para OPENROWSET funcionar, especificar csv FORMATe 2.0 PARSER_VERSION. Se o arquivo estiver disponível publicamente ou se sua identidade do Microsoft Entra puder acessar esse arquivo, você poderá ver o conteúdo do arquivo usando a consulta como a mostrada no exemplo a seguir:

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    format = 'csv',
    parser_version = '2.0',
    firstrow = 2 ) as rows

A opção firstrow é usada para ignorar a primeira linha no arquivo CSV que representa o cabeçalho neste caso. Certifique-se de que consegue aceder a este ficheiro. Se o seu arquivo estiver protegido com chave SAS ou identidade personalizada, você precisará configurar a credencial no nível do servidor para login sql.

Importante

Se o arquivo CSV contiver caracteres UTF-8, verifique se você está usando um agrupamento de banco de dados UTF-8 (por exemplo Latin1_General_100_CI_AS_SC_UTF8). Uma incompatibilidade entre a codificação de texto no arquivo e o agrupamento pode causar erros de conversão inesperados. Você pode alterar facilmente o agrupamento padrão do banco de dados atual usando a seguinte instrução T-SQL: alter database current collate Latin1_General_100_CI_AI_SC_UTF8

Uso da fonte de dados

O exemplo anterior usa o caminho completo para o arquivo. Como alternativa, você pode criar uma fonte de dados externa com o local que aponta para a pasta raiz do armazenamento:

create external data source covid
with ( location = 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases' );

Depois de criar uma fonte de dados, você pode usar essa fonte de dados e o caminho relativo para o arquivo na OPENROWSET função:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.csv',
        data_source = 'covid',
        format = 'csv',
        parser_version ='2.0',
        firstrow = 2
    ) as rows

Se uma fonte de dados estiver protegida com chave SAS ou identidade personalizada, você poderá configurar a fonte de dados com credencial de escopo de banco de dados.

Especificar explicitamente o esquema

OPENROWSET Permite especificar explicitamente quais colunas você deseja ler do arquivo usando WITH a cláusula:

select top 10 *
from openrowset(
        bulk 'latest/ecdc_cases.csv',
        data_source = 'covid',
        format = 'csv',
        parser_version ='2.0',
        firstrow = 2
    ) with (
        date_rep date 1,
        cases int 5,
        geo_id varchar(6) 8
    ) as rows

Os números após um tipo de dados na cláusula representam o WITH índice da coluna no arquivo CSV.

Importante

Se o arquivo CSV contiver caracteres UTF-8, certifique-se de especificar detalhadamente algum agrupamento UTF-8 (por exemplo Latin1_General_100_CI_AS_SC_UTF8) para todas as colunas na WITH cláusula ou defina algum agrupamento UTF-8 no nível do banco de dados. A incompatibilidade entre a codificação de texto no arquivo e o agrupamento pode causar erros de conversão inesperados. Você pode alterar facilmente o agrupamento padrão do banco de dados atual usando a seguinte instrução T-SQL: alter database current collate Latin1_General_100_CI_AI_SC_UTF8 Você pode facilmente definir o agrupamento nos tipos de colum usando a seguinte definição: geo_id varchar(6) collate Latin1_General_100_CI_AI_SC_UTF8 8

Nas seções a seguir, você pode ver como consultar vários tipos de arquivos CSV.

Pré-requisitos

Seu primeiro passo é criar um banco de dados onde as tabelas serão criadas. Em seguida, inicialize os objetos executando o script de instalação nesse banco de dados. Esse script de instalação criará as fontes de dados, credenciais com escopo de banco de dados e formatos de arquivo externos usados nesses exemplos.

Nova linha estilo Windows

A consulta a seguir mostra como ler um arquivo CSV sem uma linha de cabeçalho, com uma nova linha no estilo do Windows e colunas delimitadas por vírgula.

Pré-visualização do ficheiro:

First 10 rows of the CSV file without header, Windows style new line.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

Nova linha estilo Unix

A consulta a seguir mostra como ler um arquivo sem uma linha de cabeçalho, com uma nova linha no estilo Unix e colunas delimitadas por vírgula. Observe o local diferente do arquivo em comparação com os outros exemplos.

Pré-visualização do ficheiro:

First 10 rows of the CSV file without header row and with Unix-Style new line.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a'
    )
WITH (
    [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
    [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
    [year] smallint,
    [population] bigint
) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

Linha de cabeçalho

A consulta a seguir mostra como ler um arquivo com uma linha de cabeçalho, com uma nova linha no estilo Unix e colunas delimitadas por vírgula. Observe o local diferente do arquivo em comparação com os outros exemplos.

Pré-visualização do ficheiro:

First 10 rows of the CSV file with header row and with Unix-Style new line.

SELECT *
FROM OPENROWSET(
    BULK 'csv/population-unix-hdr/population.csv',
    DATA_SOURCE = 'SqlOnDemandDemo',
    FORMAT = 'CSV', PARSER_VERSION = '2.0',
    FIELDTERMINATOR =',',
    HEADER_ROW = TRUE
    ) AS [r]

A opção HEADER_ROW = TRUE resultará na leitura de nomes de colunas da linha de cabeçalho no arquivo. É ótimo para fins de exploração quando você não está familiarizado com o conteúdo do arquivo. Para obter o melhor desempenho, consulte a seção Usar tipos de dados apropriados em Práticas recomendadas. Além disso, você pode ler mais sobre a sintaxe OPENROWSET aqui.

Caractere de cotação personalizada

A consulta a seguir mostra como ler um arquivo com uma linha de cabeçalho, com uma nova linha no estilo Unix, colunas delimitadas por vírgulas e valores entre aspas. Observe o local diferente do arquivo em comparação com os outros exemplos.

Pré-visualização do ficheiro:

First 10 rows of the CSV file with header row and with Unix-Style new line and quoted values.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-quoted/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2,
        FIELDQUOTE = '"'
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017;

Nota

Essa consulta retornaria os mesmos resultados se você omitisse o parâmetro FIELDQUOTE, já que o valor padrão para FIELDQUOTE é uma aspa dupla.

Carateres de escape

A consulta a seguir mostra como ler um arquivo com uma linha de cabeçalho, com uma nova linha no estilo Unix, colunas delimitadas por vírgulas e um caracter de escape usado para o delimitador de campo (vírgula) dentro de valores. Observe o local diferente do arquivo em comparação com os outros exemplos.

Pré-visualização do ficheiro:

First 10 rows of the CSV file with header row and with Unix-Style new line and escape char used for field delimiter.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-escape/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2,
        ESCAPECHAR = '\\'
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Slovenia';

Nota

Esta consulta falharia se ESCAPECHAR não fosse especificado, uma vez que a vírgula em "Slov,enia" seria tratada como delimitador de campo em vez de parte do nome do país/região. "Slov,enia" seria tratado como duas colunas. Portanto, a linha específica teria uma coluna a mais do que as outras linhas e uma coluna a mais do que você definiu na cláusula WITH.

Escape citando caracteres

A consulta a seguir mostra como ler um arquivo com uma linha de cabeçalho, com uma nova linha no estilo Unix, colunas delimitadas por vírgulas e um char de aspas duplas escapado dentro de valores. Observe o local diferente do arquivo em comparação com os outros exemplos.

Pré-visualização do ficheiro:

The following query shows how to read a file with a header row, with a Unix-style new line, comma-delimited columns, and an escaped double quote char within values.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-escape-quoted/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Slovenia';

Nota

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.

Arquivos delimitados por tabulação

A consulta a seguir mostra como ler um arquivo com uma linha de cabeçalho, com uma nova linha no estilo Unix e colunas delimitadas por tabulações. Observe o local diferente do arquivo em comparação com os outros exemplos.

Pré-visualização do ficheiro:

First 10 rows of the CSV file with header row and with Unix-Style new line and tab delimiter.

SELECT *
FROM OPENROWSET(
        BULK 'csv/population-unix-hdr-tsv/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR ='\t',
        ROWTERMINATOR = '0x0a',
        FIRSTROW = 2
    )
    WITH (
        [country_code] VARCHAR (5) COLLATE Latin1_General_BIN2,
        [country_name] VARCHAR (100) COLLATE Latin1_General_BIN2,
        [year] smallint,
        [population] bigint
    ) AS [r]
WHERE
    country_name = 'Luxembourg'
    AND year = 2017

Retornar um subconjunto de colunas

Até agora, você especificou o esquema de arquivo CSV usando WITH e listando todas as colunas. Você só pode especificar colunas que realmente precisa em sua consulta usando um número ordinal para cada coluna necessária. Você também omitirá colunas sem interesse.

A consulta a seguir retorna o número de nomes de país/região distintos em um arquivo, especificando apenas as colunas necessárias:

Nota

Dê uma olhada na cláusula WITH na consulta abaixo e observe que há "2" (sem aspas) no final da linha onde você define a coluna [country_name]. Isso significa que a coluna [country_name] é a segunda coluna do arquivo. A consulta ignorará todas as colunas no arquivo, exceto a segunda.

SELECT
    COUNT(DISTINCT country_name) AS countries
FROM OPENROWSET(
        BULK 'csv/population/population.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV', PARSER_VERSION = '2.0',
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
    )
WITH (
    --[country_code] VARCHAR (5),
    [country_name] VARCHAR (100) 2
    --[year] smallint,
    --[population] bigint
) AS [r]

Consultando arquivos anexáveis

Os arquivos CSV usados na consulta não devem ser alterados enquanto a consulta estiver em execução. Na consulta de longa execução, o pool SQL pode repetir leituras, ler partes dos arquivos ou até mesmo ler o arquivo várias vezes. Alterações no conteúdo do arquivo causariam resultados errados. Portanto, o pool SQL falhará na consulta se detetar que o tempo de modificação de qualquer arquivo é alterado durante a execução da consulta.

Em alguns cenários, talvez você queira ler os arquivos que são constantemente acrescentados. Para evitar as falhas de consulta devido a arquivos anexados constantemente, você pode permitir que a função ignore leituras potencialmente inconsistentes usando a OPENROWSETROWSET_OPTIONS configuração.

select top 10 *
from openrowset(
    bulk 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/ecdc_cases/latest/ecdc_cases.csv',
    format = 'csv',
    parser_version = '2.0',
    firstrow = 2,
    ROWSET_OPTIONS = '{"READ_OPTIONS":["ALLOW_INCONSISTENT_READS"]}') as rows

A ALLOW_INCONSISTENT_READS opção de leitura desativará a verificação do tempo de modificação do arquivo durante o ciclo de vida da consulta e lerá o que estiver disponível no arquivo. Nos arquivos anexáveis, o conteúdo existente não é atualizado e apenas novas linhas são adicionadas. Portanto, a probabilidade de resultados errados é minimizada em comparação com os arquivos atualizáveis. Essa opção pode permitir que você leia os arquivos anexados com freqüência sem manipular os erros. Na maioria dos cenários, o pool SQL simplesmente ignorará algumas linhas que são anexadas aos arquivos durante a execução da consulta.

Próximos passos

Os próximos artigos irão mostrar-lhe como: