Consultar um ficheiro CSV utilizando piscinas SQL sem servidor Azure Synapse

Concluído

Os ficheiros CSV são um formato comum de ficheiros dentro de muitas empresas, e pode consultar um único ficheiro CSV usando o pool SQL sem servidor. Os ficheiros CSV podem ter formatos diferentes:

  • Com e sem uma linha de cabeçalho
  • Valores de vírgula e delimitados
  • Terminações de linha de estilo Windows e Unix
  • Valores não citados e citados, e personagens escapando

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

A função OPENROWSET permite-lhe ler o conteúdo do ficheiro CSV fornecendo o URL ao seu ficheiro.

Leia um ficheiro csv

A forma mais fácil de ver o conteúdo do seu ficheiro CSV é fornecer o URL de ficheiro à função OPENROWSET. Em seguida, inclui o formato csv , e 2.0 PARSER_VERSION. Se o ficheiro estiver disponível publicamente ou se a sua identidade AZure AD puder aceder a este ficheiro, deverá poder ver o conteúdo do ficheiro utilizando a consulta como a apresentada no seguinte exemplo:

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 primeira linha de opção é usada para saltar a primeira linha no ficheiro CSV que representa o cabeçalho neste caso. Certifique-se de que pode aceder a este ficheiro. Se o seu ficheiro estiver protegido com uma chave SAS ou identidade personalizada, terá de configurar a credencial de nível do servidor para iniciar sessão de sessão.

Utilização de fontes de dados

O exemplo anterior usa um caminho completo para o ficheiro. Como alternativa, pode criar uma fonte de dados externa com a localização 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' );

Uma vez que crie uma fonte de dados, pode utilizar essa fonte de dados e o caminho relativo para o ficheiro na função OPENROWSET:

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, pode configurar fonte de dados com credencialde área de dados .

Especificar explicitamente um esquema

A função OPENROWSET permite especificar explicitamente quais as colunas que pretende ler a partir do ficheiro utilizando a cláusula COM:

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 WITH representam a localização da coluna, conhecida como um índice de coluna no ficheiro CSV.

Nas seguintes secções, pode ver como consultar vários tipos de ficheiros CSV. Todos os exemplos que se seguem requerem ficheiros que tenham um delimiter específico de linha, delimiter de coluna, caráter de fuga, etc. Para poder executar os próximos exemplos, o seu primeiro passo é criar uma base de dados onde os objetos serão criados. Em seguida, inicialize os objetos executando o seguinte script de configuração nessa base de dados. Este script de configuração criará as fontes de dados, credenciais de base de dados e formatos de ficheiros externos que são utilizados nestas amostras.

Leia novos ficheiros de linha do estilo janela

A seguinte consulta mostra como ler um ficheiro CSV sem uma linha de cabeçalho, com uma nova linha ao estilo windows e colunas delimitadas em vírgula, como mostra o exemplo de pré-visualização do ficheiro:

O ficheiro de origem para criar um novo arquivo de linha estilo windows

O código para o conseguir é:

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;

Leia os novos ficheiros de linha estilo Unix

A seguinte consulta mostra como ler um ficheiro sem uma linha de cabeçalho, com uma nova linha estilo Unix e colunas delimitadas em vírgula, como mostrado na pré-visualização do ficheiro seguinte.

O ficheiro de origem para criar um ficheiro de linha nova estilo Unix

O código para o conseguir é o seguinte. Pode ver neste exemplo a localização diferente do ficheiro em comparação com os outros exemplos.

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;

Trabalhe com linhas de cabeçalho em um arquivo

A seguinte consulta mostra como ler um ficheiro com uma linha de cabeçalho, com uma nova linha estilo Unix e colunas delimitadas em vírgula, como mostrado na pré-visualização do ficheiro seguinte.

O ficheiro de origem para omitir cabeçalhos num ficheiro

O código para o conseguir é o seguinte quando se utiliza a variável de primeira sobrancelha para omitir a linha do cabeçalho.

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]

HEADER_ROW = TRUE resultará na leitura de nomes de colunas da linha do cabeçalho em ficheiro. É ótimo para fins de exploração quando não está familiarizado com o conteúdo do ficheiro. Para obter um melhor desempenho, utilize a secção de tipos de dados apropriados nas melhores práticas. Além disso, pode ler mais sobre a sintaxe OPENROWSET aqui.

Trabalhar com caráter de citação personalizada

A seguinte consulta mostra como ler um ficheiro com uma linha de cabeçalho, com uma nova linha de estilo Unix, colunas delimitadas em vírgula e valor citado como mostrado na pré-visualização do ficheiro seguinte.

O ficheiro de origem para trabalhar com caracteres de cotação personalizados em um arquivo

O código para o conseguir é o seguinte.

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

Esta consulta devolveria os mesmos resultados se omitisse o parâmetro FIELDQUOTE, uma vez que o valor padrão para FIELDQUOTE é uma cotação dupla.

Trabalhar com personagens de fuga

A seguinte consulta mostra como ler um ficheiro com uma linha de cabeçalho, com uma nova linha de estilo Unix, colunas delimitadas em vírgula, e um char de fuga usado para o delimiter de campo (vírgula) dentro dos valores, como mostrado na pré-visualização do ficheiro seguinte.

O ficheiro de origem para trabalhar com caracteres de fuga num ficheiro

O código para o conseguir é o seguinte.

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 o ESCAPECHAR não fosse especificado, uma vez que a vírgula em "Slov,enia" seria tratada como delimiter de campo em vez de parte do nome país/região. "Slov,enia" seria tratado como duas colunas. Portanto, a linha teria uma coluna mais do que as outras linhas, e uma coluna mais do que você definiu na cláusula COM.

Trabalhar com caracteres citando fuga

A seguinte consulta mostra como ler um ficheiro com uma linha de cabeçalho, com uma nova linha de estilo Unix, colunas delimitadas em vírgula, e um char de citação dupla escapado dentro de valores como mostrado na pré-visualização do ficheiro seguinte.

O ficheiro de origem para trabalhar com a fuga citando caracteres em um arquivo

O código para o conseguir é o seguinte.

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 personagem citando deve ser escapado com outro personagem citando. Citar o carácter só pode aparecer dentro do valor da coluna se o valor for encapsulado com caracteres citantes.

Trabalhar com ficheiros delimitados por separadores

A seguinte consulta mostra como ler um ficheiro com uma linha de cabeçalho, com uma nova linha estilo Unix e colunas delimitadas por separadores, como mostrado na pré-visualização do ficheiro seguinte.

O ficheiro de origem para trabalhar com delimiters de separador num ficheiro

O código para o conseguir é o seguinte.

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 = 'Slovenia'
    AND year = 2017

Devolva um subconjunto de colunas de um ficheiro

Até agora, especificou o esquema de ficheiros CSV usando WITH e listando todas as colunas. Só pode especificar as colunas de que necessita na sua consulta utilizando um número ordinal para cada coluna necessária. Também omitirá colunas sem interesse. A seguinte consulta devolve o número de nomes distintos de país/região num ficheiro, especificando apenas as colunas necessárias.

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]

Nota

Olhe para a cláusula COM na consulta abaixo e note que há "2" (sem orçamentos) no final da linha onde se define a coluna [country_name]. Significa que a coluna [country_name] é a segunda coluna do ficheiro. A consulta ignorará todas as colunas do ficheiro exceto a segunda.