Запрашивание CSV-файлов

Статья
04/27/2023

Из этой статьи вы узнаете, как запросить один CSV-файл с помощью бессерверного пула SQL в Azure Synapse Analytics. CSV-файлы могут иметь разные форматы:

Со строкой заголовка и без нее
Со значениями, разделенными запятыми и символами табуляции
Завершение строк в стиле Windows и UNIX
Значения, не заключенные в кавычки и заключенные в кавычки, и символы экранирования

Все приведенные выше варианты будут рассмотрены ниже.

Пример для быстрого начала

Функция OPENROWSET позволяет считывать содержимое CSV-файла, предоставляя URL-адрес к файлу.

Чтение CSV-файла

Чтобы увидеть содержимое файла CSV, проще всего вызвать функцию OPENROWSET, передав ей URL-адрес нужного файла, значение "csv" для параметра FORMAT и значение "2.0" для параметра PARSER_VERSION. Если файл доступен в общедоступном виде или если удостоверение Microsoft Entra может получить доступ к этому файлу, вы сможете просмотреть содержимое файла с помощью запроса, как показано в следующем примере:

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

Параметр firstrow используется для пропуска в CSV-файле первой строки, представляющей в этом случае заголовок. Убедитесь, что у вас есть доступ к этому файлу. Если файл защищен с помощью ключа SAS или пользовательского удостоверения, необходимо настроить учетные данные на уровне сервера для входа в SQL.

Важно!

Если файл CSV содержит символы UTF-8, убедитесь, что используются параметры сортировки базы данных UTF-8 (например, Latin1_General_100_CI_AS_SC_UTF8). Несоответствие кодировки текста в файле и параметров сортировки может привести к непредвиденным ошибкам преобразования текста. Параметры сортировки по умолчанию для текущей базы данных можно легко изменить с помощью такой инструкции T-SQL: alter database current collate Latin1_General_100_CI_AI_SC_UTF8.

Использование источника данных

В предыдущем примере используется полный путь к файлу. Или можно создать внешний источник данных с расположением, которое указывает на корневую папку хранилища:

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

Созданный источник данных и относительный путь к файлу можно использовать в функции OPENROWSET.

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

Если источник данных защищен с помощью ключа SAS или пользовательского удостоверения, можно настроить источник данных с учетными данными для базы данных.

Явное указание схемы

Функция OPENROWSET позволяет явным образом указывать, какие столбцы вы хотите считать из файла, с помощью предложения WITH:

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

Числа после типа данных в предложении WITH представляют индекс столбца в CSV-файле.

Важно!

Если CSV-файл содержит символы UTF-8, обязательно укажите явным образом любой параметр сортировки для кодировки UTF-8 (например, Latin1_General_100_CI_AS_SC_UTF8) для всех столбцов в предложении WITH или на уровне базы данных. Несоответствие кодировки текста в файле и параметров сортировки может привести к непредвиденным ошибкам преобразования. Параметры сортировки по умолчанию для текущей базы данных можно легко изменить с помощью такой инструкции T-SQL: .alter database current collate Latin1_General_100_CI_AI_SC_UTF8Вы можете легко задать параметры сортировки для определенных типов столбцов, используя следующее определение: geo_id varchar(6) collate Latin1_General_100_CI_AI_SC_UTF8 8

В следующих разделах показано, как выполнять запросы к различным типам CSV-файлов.

Необходимые компоненты

Для начала создайте базу данных, в которой будут созданы таблицы. Затем инициализируйте объекты, выполнив сценарий установки для этой базы данных. Этот сценарий установки создает источники данных, учетные данные области базы данных и форматы внешних файлов, которые используются в этих примерах.

Новая строка в стиле Windows

Следующий запрос показывает, как считать CSV-файл без строки заголовка, с новой строкой в стиле Windows и столбцами с разделителями-запятыми.

Предварительный просмотр файла:

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;

Новая строка в стиле Unix

Следующий запрос показывает, как считать CSV-файл без строки заголовка, с новой строкой в стиле Unix и столбцами с разделителями-запятыми. Обратите внимание на другое расположение файла по сравнению с другими примерами.

Предварительный просмотр файла:

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;

Строка заголовка

Следующий запрос показывает, как считать CSV-файл со строкой заголовка, с новой строкой в стиле Unix и столбцами с разделителями-запятыми. Обратите внимание на другое расположение файла по сравнению с другими примерами.

Предварительный просмотр файла:

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]

Если задать HEADER_ROW = TRUE, в файле будут считываться имена столбцов из строки заголовка. Это удобно для изучения, когда вы не знакомы с содержимым файла. Сведения о том, как получить оптимальную производительность, см. в разделе Использование соответствующих типов данных статьи с рекомендациями. Кроме того, подробнее о синтаксисе OPENROWSET можно прочитать здесь.

Пользовательский символ кавычек

Следующий запрос показывает, как считать CSV-файл со строкой заголовка, с новой строкой в стиле Unix, столбцами с разделителями-запятым и значениями в кавычках. Обратите внимание на другое расположение файла по сравнению с другими примерами.

Предварительный просмотр файла:

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;

Примечание.

Этот запрос возвращает те же результаты, если пропущен параметр FIELDQUOTE, поскольку значение по умолчанию для FIELDQUOTE является двойной кавычкой.

Escape-символы

Следующий запрос показывает, как считать CSV-файл со строкой заголовка, с новой строкой в стиле Unix, столбцами с разделителями-запятым, а также экранированием символа для разделителя полей (запятой) внутри значений. Обратите внимание на другое расположение файла по сравнению с другими примерами.

Предварительный просмотр файла:

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';

Примечание.

Этот запрос завершится ошибкой, если ESCAPECHAR не указан, так как запятая в "Slov,enia" будет рассматриваться как разделитель полей, а не как часть названия страны или региона. "Slov,enia" будет обработано как два столбца. Таким образом, в конкретной строке будет на один столбец больше, чем в других строках, и на один столбец больше, чем определено в условии WITH.

Кавычка в качестве escape-символа

В следующем примере запроса показано чтение файла со строкой заголовка, с новой строкой в стиле Unix, столбцами с разделителями-запятыми и двойной кавычкой в качестве escape-символа внутри значений. Обратите внимание на другое расположение файла по сравнению с другими примерами.

Предварительный просмотр файла:

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';

Примечание.

Символ кавычек нужно экранировать другим символом кавычек. Такой символ может использоваться в значении столбца только в том случае, если значение инкапсулировано с помощью символов кавычек.

Файлы с разделителями-табуляциями

Следующий запрос показывает, как считать CSV-файл со строкой заголовка, с новой строкой в стиле Unix и столбцами с разделителями-табуляциями. Обратите внимание на другое расположение файла по сравнению с другими примерами.

Предварительный просмотр файла:

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

Возвращение подмножества столбцов

До сих пор вы создавали схему CSV-файла с помощью WITH и перечисления всех столбцов. В запросе можно указать только те столбцы, которые действительно необходимы, используя порядковый номер для каждого столбца. Вы также пропускаете неважные столбцы.

Следующий запрос возвращает количество уникальных имен стран или регионов в файле, указывая только необходимые столбцы:

Примечание.

Взгляните на условие WITH в приведенном ниже запросе и обратите внимание на "2" (без кавычек) в конце строки, где определяется столбец [country_name]. Это означает, что столбец [country_name] является вторым столбцом в файле. Запрос будет игнорировать все столбцы файла, за исключением второго.

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]

Запрос добавляемых файлов

CSV-файлы, используемые в запросе, во время выполнения запроса изменяться не должны. При длительных запросах пул SQL может повторять попытки чтения, читать части файлов или даже считывать файл несколько раз. Изменения содержимого файла приведут к неверным результатам. Таким образом, запрос пула SQL завершается ошибкой, если он обнаруживает, что время изменения какого-либо файла изменено во время выполнения запроса.

В некоторых сценариях может требоваться чтение файлов, которые добавляются постоянно. Чтобы избежать сбоев запросов из-за постоянно добавляемых файлов, можно разрешить функции OPENROWSET игнорировать потенциальное несогласованное чтение, используя для этого параметр ROWSET_OPTIONS.

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

Параметр чтения ALLOW_INCONSISTENT_READS отключит проверку времени изменения файлов во время жизненного цикла запроса и будет считывать все содержимое каждого файла. В добавляемых файлах существующее содержимое не обновляется, только добавляются новые строки. Таким образом, вероятность неверного результата сводится к минимуму по сравнению с обновляемыми файлами. Этот параметр позволяет считывать часто добавляемые файлы без обработки ошибок. В большинстве сценариев пул SQL просто игнорирует некоторые строки, добавляемые к файлам при выполнении запросов.

Следующие шаги

В следующих статьях будет показано:

Запрашивание CSV-файлов

Пример для быстрого начала

Чтение CSV-файла

Использование источника данных

Явное указание схемы

Необходимые компоненты

Новая строка в стиле Windows

Новая строка в стиле Unix

Строка заголовка

Пользовательский символ кавычек

Escape-символы

Кавычка в качестве escape-символа

Файлы с разделителями-табуляциями

Возвращение подмножества столбцов

Запрос добавляемых файлов

Следующие шаги

Дополнительные ресурсы