ОБЪЕМ OPENROWSET (Transact-SQL)

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureКонечная точка аналитики SQL в Microsoft FabricХранилище в Microsoft FabricБаза данных SQL в Microsoft Fabric

Функция OPENROWSET считывает данные из одного или нескольких файлов и возвращает содержимое в виде набора строк. В зависимости от службы файл может храниться в хранилище BLOB-объектов Azure, хранилище Azure Data Lake, локальный диск, сетевые ресурсы и т. д. Вы можете читать различные форматы файлов, такие как текст/CSV, Parquet или строки JSON.

На OPENROWSET функцию можно ссылаться в FROM предложении запроса, как если бы это было имя таблицы. Его можно использовать для чтения данных в SELECT инструкции или для обновления целевых данных в инструкциях, UPDATEINSERTDELETEа также для обновления целевых данных в MERGEинструкциях , CTASили CETAS инструкциях.

• OPENROWSET(BULK) предназначен для чтения данных из внешних файлов данных.
• OPENROWSET без BULK нее предназначено для чтения из другого ядра СУБД. Дополнительные сведения см. в разделе OPENROWSET (Transact-SQL).

Эта статья и аргумент, заданные в OPENROWSET(BULK) разных платформах.

• Для синтаксиса Microsoft Fabric выберите Fabric в раскрывающемся списке версий.
• Для SQL Server, Базы данных SQL Azure и синтаксиса Управляемого экземпляра SQL Azure выберите платформу в раскрывающемся списке версий.

Подробные сведения и ссылки на аналогичные примеры на других платформах:

• Дополнительные сведения о OPENROWSET базе данных SQL Azure см. в статье "Виртуализация данных" с помощью Базы данных SQL Azure.
• Дополнительные сведения об OPENROWSET управляемом экземпляре SQL Azure см. в статье "Виртуализация данных с помощью Управляемого экземпляра SQL Azure".
• Сведения и примеры с бессерверными пулами SQL в Azure Synapse см. в статье "Использование OPENROWSET с использованием бессерверного пула SQL в Azure Synapse Analytics".
• Выделенные пулы SQL в Azure Synapse не поддерживают функцию OPENROWSET .

Соглашения о синтаксисе Transact-SQL

Синтаксис

Для SQL Server, Azure SQL Database, SQL Database in Fabric и Azure SQL Managed Instance:

OPENROWSET( BULK 'data_file_path',
            <bulk_option> ( , <bulk_option> )*
)
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]

<bulk_option> ::=
   DATA_SOURCE = 'data_source_name' |

   -- file format options
   CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } |
   DATAFILETYPE = { 'char' | 'widechar' } |
   FORMAT = <file_format> |

   FORMATFILE = 'format_file_path' |
   FORMATFILE_DATA_SOURCE = 'data_source_name' |

   SINGLE_BLOB |
   SINGLE_CLOB |
   SINGLE_NCLOB |

   -- Text/CSV options
   ROWTERMINATOR = 'row_terminator' |
   FIELDTERMINATOR =  'field_terminator' |
   FIELDQUOTE = 'quote_character' |

   -- Error handling options
   MAXERRORS = maximum_errors |
   ERRORFILE = 'file_name' |
   ERRORFILE_DATA_SOURCE = 'data_source_name' |

   -- Execution options
   FIRSTROW = first_row |
   LASTROW = last_row |

   ORDER ( { column [ ASC | DESC ] } [ , ...n ] ) [ UNIQUE ] ] |

   ROWS_PER_BATCH = rows_per_batch

Arguments

Аргументы BULK параметра позволяют значительно контролировать, где начать и завершить чтение данных, как справиться с ошибками и способом интерпретации данных. Например, можно указать, что файл данных считывается в виде однострочного набора строк типа varbinary, varchar или nvarchar. Поведение по умолчанию описано в следующем далее описании аргументов.

Дополнительные сведения об использовании параметра см. в разделе BULK" далее в этой статье. Сведения о необходимых разрешениях BULK см. в разделе "Разрешения " далее в этой статье.

Сведения о подготовке данных для массового импорта см. в разделе "Подготовка данных для массового экспорта или импорта".

BULK "data_file_path"

Путь или универсальный код ресурса (URI) файлов данных, данные которых должны быть считываются и возвращаются в виде набора строк.

URI может ссылаться на хранилище Azure Data Lake или хранилище BLOB-объектов Azure. Универсальный код ресурса (URI) файлов данных, данные которых должны быть считываются и возвращаются в виде набора строк.

Поддерживаемые форматы путей:

• <drive letter>:\<file path> доступ к файлам на локальном диске
• \\<network-share\<file path> доступ к файлам в сетевых ресурсах
• adls://<container>@<storage>.dfs.core.windows.net/<file path> доступ к Azure Data Lake Storage
• abs://<storage>.blob.core.windows.net/<container>/<file path> доступ к хранилищу BLOB-объектов Azure
• s3://<ip-address>:<port>/<file path> доступ к хранилищу, совместимом с S3

Note

Эта статья и поддерживаемые шаблоны URI отличаются на разных платформах. Для шаблонов URI, доступных в хранилище данных Microsoft Fabric, выберите Fabric в раскрывающемся списке версий.

Начиная с SQL Server 2017 (14.x), data_file можно использовать в хранилище BLOB-объектов Azure. Примеры массового доступа к данным см. в Хранилище BLOB-объектов Azure.

Универсальный * код ресурса (URI) может включать символ для сопоставления любой последовательности символов, что позволяет OPENROWSET сопоставить шаблон с универсальным кодом ресурса (URI). Кроме того, он может завершиться для /** включения рекурсивного обхода по всем вложенным папкам. В SQL Server это поведение доступно начиная с SQL Server 2022 (16.x).

Рассмотрим пример.

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<scheme:>//pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/*.parquet'
);

Типы хранилища, на которые можно ссылаться URI, отображаются в следующей таблице:

Версия	On-premises	Хранилище Azure	OneLake в Fabric	S3	Google Cloud (GCS)
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Да	Да	нет	нет	нет
SQL Server 2022 (16.x)	Да	Да	нет	Да	нет
База данных SQL Azure	нет	Да	нет	нет	нет
Управляемый экземпляр SQL Azure	нет	Да	нет	нет	нет
Бессерверный пул SQL в Azure Synapse Analytics	нет	Да	Да	нет	нет
Конечная точка хранилища Microsoft Fabric и аналитики SQL	нет	Да	Да	Да, использование OneLake в ярлыках Fabric	Да, использование OneLake в ярлыках Fabric
База данных SQL в Microsoft Fabric	нет	Да, использование OneLake в ярлыках Fabric	Да	Да, использование OneLake в ярлыках Fabric	Да, использование OneLake в ярлыках Fabric

DATA_SOURCE

DATA_SOURCE определяет корневое расположение пути к файлу данных. Он позволяет использовать относительные пути в пути BULK. Источник данных создается с помощью CREATE EXTERNAL DATA SOURCE.

Помимо корневого расположения, он может определять пользовательские учетные данные, которые можно использовать для доступа к файлам в этом расположении.

Рассмотрим пример.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<scheme:>//pandemicdatalake.blob.core.windows.net/public')
GO
SELECT *
FROM OPENROWSET(
    BULK '/curated/covid-19/bing_covid-19_data/latest/*.parquet',
    DATA_SOURCE = 'root'
);

Параметры формата файла

CODEPAGE

Указывает кодовую страницу данных в файле данных. CODEPAGE имеет значение, только если данные содержат символьные столбцы, varchar или текстовые столбцы со значениями символов более 127 или менее 32. Допустимые значения: ACP, OEM, RAW или code_page:

Значение CODEPAGE	Description
ACP	Преобразует столбцы char, varchar или текстовых данных из кодовой страницы ANSI/Microsoft Windows (ISO 1252) на кодовую страницу SQL Server.
OEM (по умолчанию)	Преобразует столбцы типа данных char, varchar или text из кодовой страницы системного изготовителя оборудования на кодовую страницу SQL Server.
RAW	Преобразование из одной кодовой страницы в другую не выполняется. Это наиболее быстрый параметр.
code_page	Показывает исходную кодовую страницу, в которой представлены символы в файле данных, например 850.

Important

Версии до SQL Server 2016 (13.x) не поддерживают кодовую страницу 65001 (кодировка UTF-8). CODEPAGE не поддерживается в Linux.

Note

Рекомендуется указывать имя параметра сортировки для каждого столбца в файле форматирования, кроме случаев, когда параметр 65001 должен иметь приоритет над спецификацией параметров сортировки или кодовой страницы.

DATAFILETYPE

Указывает, что OPENROWSET(BULK) следует считывать однобайтовое содержимое файла (ASCII, UTF8) или с несколькими байтами (UTF16). Допустимые значения : char и widechar:

Значение DATAFILETYPE	Представление данных
char (по умолчанию)	Формат символов. Дополнительные сведения см. в разделе Использование символьного формата для импорта или экспорта данных.
widechar	Символы Юникода. Дополнительные сведения см. в разделе Использование символьного формата Юникода для импорта или экспорта данных.

FORMAT

Указывает формат файла, на который ссылается ссылка, например:

SELECT *
FROM OPENROWSET(BULK N'<data-file-path>',
                FORMAT='CSV') AS cars;

Допустимые значения : CSV (файл значений, разделенных запятыми, совместимый со стандартом RFC 4180 ), PARQUET, DELTA (версия 1.0) и JSONL, в зависимости от версии:

Версия	CSV	PARQUET;	РАЗНИЦА	JSONL
SQL Server 2017 (14.x), SQL Server 2019 (15.x)	Да	нет	нет	нет
SQL Server 2022 (16.x) и более поздних версий	Да	Да	Да	нет
База данных SQL Azure	Да	Да	Да	нет
Управляемый экземпляр SQL Azure	Да	Да	Да	нет
Бессерверный пул SQL в Azure Synapse Analytics	Да	Да	Да	нет
Конечная точка хранилища Microsoft Fabric и аналитики SQL	Да	Да	нет	Да
База данных SQL в Microsoft Fabric	Да	Да	нет	нет

FORMATFILE

Указывает полный путь к файлу форматирования. SQL Server поддерживает два типа файлов форматирования: XML и не XML.

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'D:\XChange\test-csv.csv',
      FORMATFILE= 'D:\XChange\test-format-file.xml'
)

Файл форматирования необходим для определения типов столбцов в результирующем наборе. Единственным исключением является, когда SINGLE_CLOBSINGLE_BLOBили SINGLE_NCLOB указан, в этом случае файл форматирования не требуется.

Сведения о файлах форматирования см. в статье "Использование файла форматирования для массового импорта данных (SQL Server)".

Начиная с SQL Server 2017 (14.x), format_file_path может находиться в Хранилище BLOB-объектов Azure. Примеры массового доступа к данным см. в Хранилище BLOB-объектов Azure.

FORMATFILE_DATA_SOURCE

FORMATFILE_DATA_SOURCE определяет корневое расположение пути к файлу форматирования. Он позволяет использовать относительные пути в параметре FORMATFILE.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '//pandemicdatalake/public/curated')
GO
SELECT *
FROM OPENROWSET(
    BULK '//pandemicdatalake/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv'
    FORMATFILE = 'covid-19/bing_covid-19_data/latest/bing_covid-19_data.fmt',
    FORMATFILE_DATA_SOURCE = 'root'
);

Источник данных формата создается с помощью CREATE EXTERNAL DATA SOURCE. Помимо корневого расположения, он может определять пользовательские учетные данные, которые можно использовать для доступа к файлам в этом расположении.

Параметры текста и CSV

ROWTERMINATOR

Указывает терминатор строки, используемый для файлов данных char и widechar , например:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWTERMINATOR = '\n'
);

По умолчанию признаком конца строки является символ \r\n (символ новой строки). Дополнительные сведения см. в разделе "Указание полей и терминаторов строк".

FIELDTERMINATOR

Указывает конечный элемент поля, используемый для файлов данных char и widechar , например:

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDTERMINATOR = '\t'
);

Терминатор поля по умолчанию — , (запятая). Дополнительные сведения см. в разделе Указание полей и терминаторов строк. Например, для чтения данных с разделителями табуляции из файла:

FIELDQUOTE = "field_quote"

Начиная с SQL Server 2017 (14.x), этот аргумент указывает символ, используемый в качестве символа кавычки в CSV-файле, как показано в следующем примере в Нью-йорке:

Empire State Building,40.748817,-73.985428,"20 W 34th St, New York, NY 10118","\icons\sol.png"
Statue of Liberty,40.689247,-74.044502,"Liberty Island, New York, NY 10004","\icons\sol.png"

В качестве значения этого параметра можно указать только один символ. Если не указано, символ кавычки (") используется в качестве символа кавычки, как определено в стандарте RFC 4180 . Символ FIELDTERMINATOR (например, запятая) можно поместить в кавычки полей, и он будет считаться обычным символом в ячейке, завернутой символами FIELDQUOTE .

Например, чтобы прочитать предыдущий пример набора данных CSV в Нью-Йорке, используйте FIELDQUOTE = '"'. Значения поля адреса будут храниться в виде одного значения, а не разделены на несколько значений запятыми в " символах (кавычки).

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    FIELDQUOTE = '"'
);

Параметры обработки ошибок

ERRORFILE = "file_name"

Указывает файл, используемый для сбора строк, содержащих ошибки форматирования, которые не могут быть преобразованы в набор строк OLE DB. Эти строки без изменений копируются из файла данных в файл ошибок.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<error-file-path>'
);

Файл ошибок создается в начале выполнения команды. Ошибка возникает, если файл уже существует. Дополнительно создается управляющий файл с расширением ERROR.txt. Этот файл ссылается на каждую строку в файле ошибок и позволяет провести их диагностику. После исправления ошибок данные можно загрузить.

Начиная с SQL Server 2017 (14.x), error_file_path можно использовать Хранилище BLOB-объектов Azure.

Ошибочный источник данных

Начиная с SQL Server 2017 (14.x), этот аргумент является именованным внешним источником данных, указывающим на расположение файла ошибки, который будет содержать ошибки, обнаруженные во время импорта.

CREATE EXTERNAL DATA SOURCE root
WITH (LOCATION = '<root-error-file-path>')
GO
SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ERRORFILE = '<relative-error-file-path>',
    ERRORFILE_DATA_SOURCE = 'root'
);

Дополнительные сведения см. в статье CREATE EXTERNAL DATA SOURCE (Transact-SQL).

MAXERRORS = maximum_errors

Указывает максимальное количество синтаксических ошибок или несообразующих строк, как определено в файле форматирования, которое может возникать перед OPENROWSET созданием исключения. Пока MAXERRORS не будет достигнуто, OPENROWSET игнорирует каждую плохую строку, а не загружает ее, и подсчитывает плохую строку как одну ошибку.

SELECT *
FROM OPENROWSET(
    BULK '<data-file-path>',
    MAXERRORS = 0
);

Значение по умолчанию для maximum_errors равно 10.

Note

MAX_ERRORS не применяется к CHECK ограничениям или к преобразованию денежных и больших типов данных.

Параметры обработки данных

ПЕРВЫЙ РЯД = first_row

Указывает номер первой строки для загрузки. Значение по умолчанию — 1. Значение по умолчанию — первая строка указанного файла данных. Номера строк определяются подсчетом разделителей строк. FIRSTROW — 1 на основе.

LASTROW = last_row

Указывает номер последней строки для загрузки. Значение по умолчанию — 0. Оно указывает на последнюю строку в используемом файле данных.

ROWS_PER_BATCH = rows_per_batch

Указывает примерное количество строк данных в файле данных. Это значение является оценкой и должно быть приблизительный (в пределах одного порядка величины) фактического числа строк. По умолчанию ROWS_PER_BATCH оценивается на основе характеристик файла (количество файлов, размер файлов, размер возвращаемых типов данных). Указание ROWS_PER_BATCH = 0 совпадает с опущением ROWS_PER_BATCH. Рассмотрим пример.

SELECT TOP 10 *
FROM OPENROWSET(
    BULK '<data-file-path>',
    ROWS_PER_BATCH = 100000
);

ORDER ( { столбец [ ASC | DESC ] } [ ,... n ] [ UNIQUE ] )

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

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

UNIQUE

Указывает, что файл данных не содержит повторяющихся записей.

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

Псевдонимы столбцов требуются при ORDER использовании. Список псевдонимов столбцов должен ссылаться на производную таблицу, доступ к которой осуществляется предложением BULK . Имена столбцов, указанные в ORDER предложении, ссылаются на этот список псевдонимов столбцов. Большие типы значений (varchar(max), nvarchar(max), varbinary(max), xml) и большие типы объектов (text, ntext и image) не могут быть указаны.

Параметры содержимого

SINGLE_BLOB

Возвращает содержимое data_file в виде набора строк с одним столбцом типа varbinary(max).

Important

Рекомендуется импортировать XML-данные только с помощью SINGLE_BLOB параметра, а не SINGLE_CLOBSINGLE_NCLOB, так как поддерживается только SINGLE_BLOB все преобразования кодировки Windows.

SINGLE_CLOB

Считывая data_file as ASCII, возвращает содержимое в виде однострочного набора строк типа varchar(max), используя параметры сортировки текущей базы данных.

SINGLE_NCLOB

Считывая data_file как Юникод, возвращает содержимое в виде однострочного набора строк типа nvarchar(max), используя параметры сортировки текущей базы данных.

SELECT * FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_NCLOB
) AS Document;

Permissions

OPENROWSET для внешних источников данных требуются следующие разрешения:

• ADMINISTER DATABASE BULK OPERATIONS или
• ADMINISTER BULK OPERATIONS

Следующий пример T-SQL предоставляет ADMINISTER DATABASE BULK OPERATIONS субъекту.

GRANT ADMINISTER DATABASE BULK OPERATIONS TO [<principal_name>];

Если целевая учетная запись хранения является частной, субъект также должен иметь роль чтения данных BLOB-объектов хранилища (или выше) на уровне контейнера или учетной записи хранения.

Remarks

• Предложение FROM, используемое в инструкции SELECT, может вызывать OPENROWSET(BULK...) вместо имени таблицы с полной функциональностью инструкции SELECT.

• Функции OPENROWSET с параметром BULK требуется корреляционное имя, также известное как переменная диапазона или псевдоним в предложении FROM. Не удается добавить AS <table_alias> результаты в ошибке Msg 491: "Имя корреляции должно быть указано для набора массовых строк в предложении from".

• Могут быть указаны псевдонимы столбцов. Если список псевдонимов столбцов не указан, файл форматирования должен иметь имена столбцов. Указание псевдонимов столбцов переопределяет имена столбцов в файле форматирования, такие как:

  • FROM OPENROWSET(BULK...) AS table_alias
  • FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)

• Инструкция SELECT...FROM OPENROWSET(BULK...) запрашивает данные в файле напрямую, не импортируя их в таблицу.

• Инструкция SELECT...FROM OPENROWSET(BULK...) может перечислять псевдонимы массового столбца с помощью файла форматирования для указания имен столбцов, а также типов данных.

• Использование OPENROWSET(BULK...) в качестве исходной таблицы в инструкции INSERT или MERGE массово импортирует данные из файла данных в таблицу. Дополнительные сведения см. в статье "Использование BULK INSERT" или OPENROWSET(BULK...) для импорта данных в SQL Server.
• OPENROWSET BULK Если параметр используется с операторомINSERT, BULK предложение поддерживает указания таблиц. Кроме обычных табличных указаний, таких как TABLOCK, предложение BULK принимает следующие специальные табличные указания: IGNORE_CONSTRAINTS (пропускает только ограничения CHECK и FOREIGN KEY), IGNORE_TRIGGERS, KEEPDEFAULTS и KEEPIDENTITY. Дополнительные сведения см. в статье Указания по таблицам (Transact-SQL).
• Сведения об использовании инструкций INSERT...SELECT * FROM OPENROWSET(BULK...) см. в статье Массовый импорт и экспорт данных (SQL Server). Сведения о том, когда операции вставки строк, выполняемые массовым импортом, регистрируются в журнале транзакций, см. в разделе "Предварительные требования для минимального ведения журнала в массовом импорте".
• При использовании для импорта данных с полной моделью OPENROWSET (BULK ...) восстановления не оптимизирует ведение журнала.

Note

При использовании OPENROWSETважно понимать, как SQL Server обрабатывает олицетворение. Дополнительные сведения о безопасности см. в статье "Использование BULK INSERT" или OPENROWSET(BULK...) для импорта данных в SQL Server.

Массовый импорт данных SQLCHAR, SQLNCHAR или SQLBINARY

OPENROWSET(BULK...) Предполагает, что, если не указано, максимальная длина SQLCHAR, SQLNCHARили SQLBINARY данные не превышают 8000 байт. Если импортируемые данные хранятся в поле данных бизнес-объекта, содержащего любые объекты varchar(max),nvarchar(max)или varbinary(max), превышающие 8000 байт, необходимо использовать XML-файл форматирования, определяющий максимальную длину поля данных. Чтобы указать максимальную длину, измените файл форматирования и объявите атрибут MAX_LENGTH.

Note

Автоматически созданный файл форматирования не указывает длину или максимальную длину для поля бизнес-приложения. Однако можно изменить файл форматирования и указать длину или максимальную длину вручную.

Массовый экспорт или импорт документов SQLXML

Чтобы выполнить массовый экспорт или импорт SQLXML-данных используйте один из следующих типов данных в файле форматирования:

Тип данных	Effect
SQLCHAR или SQLVARYCHAR	Данные отправляются на клиентской кодовой странице или на кодовой странице, подразумеваемой параметрами сортировки.
SQLNCHAR или SQLNVARCHAR	Данные отправляются в Юникоде.
SQLBINARY или SQLVARYBIN	Данные отправляются без преобразования.

Функции метаданных файлов

Иногда нужно знать, какой файл или папка коррелирует с конкретной строкой в наборе результатов.

Вы можете использовать функции filepath и filename возвращать имена файлов и/или путь в наборе результатов. Или можно использовать их для фильтрации данных по имени файла и/или пути к папке. В следующих разделах вы найдёте краткие описания вместе с образцами.

Функция имён файла

Эта функция возвращает имя файла, откуда происходит строка.

Тип возвратных данных — nvarchar(1024). Для оптимальной производительности всегда передавайте результат функции имени файла в соответствующий тип данных. Если вы используете тип символа, убедитесь, что используется подходящая длина.

Следующий пример читает файлы данных NYC Yellow Taxi за последние три месяца 2017 года и показывает количество поездок в каждом файле. OPENROWSET Часть запроса указывает, какие файлы будут читаться.

SELECT
    nyc.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM  
    OPENROWSET(
        BULK 'parquet/taxi/year=2017/month=9/*.parquet',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT='PARQUET'
    ) nyc
GROUP BY nyc.filename();

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

Ваши результаты будут такими же, как в предыдущем примере.

SELECT
    r.filename() AS [filename]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
    BULK 'csv/taxi/yellow_tripdata_2017-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2) 
        WITH (C1 varchar(200) ) AS [r]
WHERE
    r.filename() IN ('yellow_tripdata_2017-10.csv', 'yellow_tripdata_2017-11.csv', 'yellow_tripdata_2017-12.csv')
GROUP BY
    r.filename()
ORDER BY
    [filename];

Функция пути файла

Эта функция возвращает полный путь или его часть:

• При вызове без параметра возвращается полный путь файла, откуда исходит строка.
• При вызове с параметром возвращает часть пути, которая совпадает с джокером на позиции, указанной в параметре. Например, значение параметра 1 возвращает часть пути, которая совпадает с первым джокером.

Тип возвратных данных — nvarchar(1024). Для оптимальной производительности всегда отражайте результат filepath функции в соответствующий тип данных. Если вы используете тип символа, убедитесь, что используется подходящая длина.

Следующая выборка содержит файлы данных NYC Yellow Taxi за последние три месяца 2017 года. Он возвращает количество поездок на путь к файлу. OPENROWSET Часть запроса указывает, какие файлы будут читаться.

SELECT
    r.filepath() AS filepath
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_2017-1*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
    WITH (
        vendor_id INT
    ) AS [r]
GROUP BY
    r.filepath()
ORDER BY
    filepath;

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

Вы можете использовать джокеры в OPENROWSET части запроса и фильтровать файлы в оговорке WHERE . Ваши результаты будут такими же, как в предыдущем примере.

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'csv/taxi/yellow_tripdata_*-*.csv',
        DATA_SOURCE = 'SqlOnDemandDemo',
        FORMAT = 'CSV',
        FIRSTROW = 2
    )
WITH (
    vendor_id INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Examples

В этом разделе приведены общие примеры использования OPENROWSET BULK синтаксиса.

A. Использование OPENROWSET для данных ФАЙЛА BULK INSERT в столбец varbinary(max)

Применимо к: Только SQL Server.

В следующем примере создается небольшая таблица для демонстрационных целей и вставляет данные файла из файла с именемText1.txt, расположенного в корневом каталоге, в C: столбец varbinary(max).

CREATE TABLE myTable (
    FileName NVARCHAR(60),
    FileType NVARCHAR(60),
    Document VARBINARY(MAX)
);
GO

INSERT INTO myTable (
    FileName,
    FileType,
    Document
)
SELECT 'Text1.txt' AS FileName,
    '.txt' AS FileType,
    *
FROM OPENROWSET(
    BULK N'C:\Text1.txt',
    SINGLE_BLOB
) AS Document;
GO

B. Использование поставщика OPENROWSET BULK с файлом форматирования для извлечения строк из текстового файла

Применимо к: Только SQL Server.

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

1     Data Item 1
2     Data Item 2
3     Data Item 3

Файл форматирования values.fmt описывает столбцы в файле values.txt:

9.0
2
1  SQLCHAR  0  10 "\t"    1  ID           SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  40 "\r\n"  2  Description  SQL_Latin1_General_Cp437_BIN

Этот запрос извлекает эти данные:

SELECT a.* FROM OPENROWSET(
    BULK 'C:\test\values.txt',
   FORMATFILE = 'C:\test\values.fmt'
) AS a;

C. Указание файла форматирования и кодовой страницы

Применимо к: Только SQL Server.

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

INSERT INTO MyTable
SELECT a.* FROM OPENROWSET (
    BULK N'D:\data.csv',
    FORMATFILE = 'D:\format_no_collation.txt',
    CODEPAGE = '65001'
) AS a;

D. Доступ к данным из CSV-файла с помощью файла форматирования

Применимо к: ТОЛЬКО SQL Server 2017 (14.x) и более поздних версий.

SELECT * FROM OPENROWSET(
    BULK N'D:\XChange\test-csv.csv',
    FORMATFILE = N'D:\XChange\test-csv.fmt',
    FIRSTROW = 2,
    FORMAT = 'CSV'
) AS cars;

E. Доступ к данным из CSV-файла без файла форматирования

Применимо к: Только SQL Server.

SELECT * FROM OPENROWSET(
   BULK 'C:\Program Files\Microsoft SQL Server\MSSQL14\MSSQL\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

SELECT *
FROM OPENROWSET('MSDASQL',
    'Driver={Microsoft Access Text Driver (*.txt, *.csv)}',
    'SELECT * FROM E:\Tlog\TerritoryData.csv'
);

Important

Драйвер ODBC должен быть 64-разрядным. Откройте вкладку "Драйверы" приложения "Подключение к источнику данных ODBC" (мастер импорта и экспорта SQL Server) в Windows, чтобы проверить это. Существует 32-разрядная версия, которая не будет работать с 64-разрядной Microsoft Text Driver (*.txt, *.csv) версией sqlservr.exe.

F. Доступ к данным из файла, хранящегося на Хранилище BLOB-объектов Azure

Применимо к: ТОЛЬКО SQL Server 2017 (14.x) и более поздних версий.

В SQL Server 2017 (14.x) и более поздних версиях в следующем примере используется внешний источник данных, указывающий на контейнер в учетной записи хранения Azure и учетные данные базы данных, созданные для подписанного URL-адреса.

SELECT * FROM OPENROWSET(
   BULK 'inv-2017-01-19.csv',
   DATA_SOURCE = 'MyAzureInvoices',
   SINGLE_CLOB
) AS DataFile;

Полные OPENROWSET примеры, включая настройку учетных данных и внешнего источника данных, см. в примерах массового доступа к данным в Хранилище BLOB-объектов Azure.

G. Импорт в таблицу из файла, хранящегося на Хранилище BLOB-объектов Azure

В следующем примере показано, как использовать OPENROWSET команду для загрузки данных из CSV-файла в расположении хранилища BLOB-объектов Azure, в котором вы создали ключ SAS. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных. Для этого требуются учетные данные базы данных с ограниченной областью действия с использованием общей подписи доступа, зашифрованной с помощью основного ключа в пользовательской базе данных.

-- Optional: a MASTER KEY is not required if a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';
GO

-- Optional: a DATABASE SCOPED CREDENTIAL is not required because the blob is configured for public (anonymous) access!
CREATE DATABASE SCOPED CREDENTIAL MyAzureBlobStorageCredential
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
    SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=2016-12-29T16:55:34Z***************';

-- Make sure that you don't have a leading ? in the SAS token, and that you
-- have at least read permission on the object that should be loaded srt=o&sp=r,
-- and that expiration period is valid (all dates are in UTC time)
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH (
    TYPE = BLOB_STORAGE,
    LOCATION = 'https://****************.blob.core.windows.net/curriculum',
    -- CREDENTIAL is not required if a blob is configured for public (anonymous) access!
    CREDENTIAL = MyAzureBlobStorageCredential
);

INSERT INTO achievements
WITH (TABLOCK) (
    id,
    description
)
SELECT * FROM OPENROWSET(
    BULK 'csv/achievements.csv',
    DATA_SOURCE = 'MyAzureBlobStorage',
    FORMAT = 'CSV',
    FORMATFILE = 'csv/achievements-c.xml',
    FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage'
) AS DataFile;

H. Использование управляемого удостоверения для внешнего источника

Применимо к: Управляемый экземпляр SQL Azure и База данных SQL Azure

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

Сначала создайте учетные данные и укажите хранилище BLOB-объектов в качестве внешнего источника:

CREATE DATABASE SCOPED CREDENTIAL sampletestcred
WITH IDENTITY = 'MANAGED IDENTITY';

CREATE EXTERNAL DATA SOURCE SampleSource
WITH (
    LOCATION = 'abs://****************.blob.core.windows.net/curriculum',
    CREDENTIAL = sampletestcred
);

Затем загрузите данные из CSV-файла, размещенного в хранилище BLOB-объектов:

SELECT * FROM OPENROWSET(
    BULK 'Test - Copy.csv',
    DATA_SOURCE = 'SampleSource',
    SINGLE_CLOB
) as test;

I. Использование OPENROWSET для доступа к нескольким файлам Parquet с помощью хранилища объектов, совместимого с S3

Область применения: SQL Server 2022 (16.x) и более поздних версий.

В следующем примере используется доступ к нескольким файлам Parquet из разных расположений, которые хранятся в хранилище объектов, совместимом с S3:

CREATE DATABASE SCOPED CREDENTIAL s3_dsc
WITH IDENTITY = 'S3 Access Key',
SECRET = 'contosoadmin:contosopwd';
GO

CREATE EXTERNAL DATA SOURCE s3_eds
WITH
(
    LOCATION = 's3://10.199.40.235:9000/movies',
    CREDENTIAL = s3_dsc
);
GO

SELECT * FROM OPENROWSET(
    BULK (
        '/decades/1950s/*.parquet',
        '/decades/1960s/*.parquet',
        '/decades/1970s/*.parquet'
    ),
    FORMAT = 'PARQUET',
    DATA_SOURCE = 's3_eds'
) AS data;

J. Использование OPENROWSET для доступа к нескольким таблицам Delta из Azure Data Lake 2-го поколения

Область применения: SQL Server 2022 (16.x) и более поздних версий.

В этом примере контейнер таблицы данных называется Contosoи находится в учетной записи хранения Azure Data Lake 2-го поколения.

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

SELECT *
FROM OPENROWSET(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS result;

K. Использование OPENROWSET для запроса общедоступного анонимного набора данных

В следующем примере используется общедоступный желтый набор данных для поездки в такси Нью-Йорка.

Сначала создайте источник данных:

CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Запросите все файлы с расширением в папках, .parquet соответствующих шаблону имен:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Дополнительные примеры

Дополнительные примеры использования OPENROWSET(BULK...)см. в следующих статьях:

• Массовый импорт и экспорт данных (SQL Server)
• Примеры массового импорта и экспорта XML-документов (SQL Server)
• Сохранение значений идентификаторов при массовом импорте данных (SQL Server)
• Сохранение значений NULL или значений по умолчанию во время массового импорта (SQL Server)
• Использование файла форматирования для массового импорта данных (SQL Server)
• Использование формата символов для импорта или экспорта данных (SQL Server)
• Использование файла форматирования для пропуска столбца таблицы (SQL Server)
• Использование файла форматирования для пропуска поля данных (SQL Server)
• Использование файла форматирования для сопоставления столбцов таблиц с полями файлов данных (SQL Server)
• Запрос источников данных с помощью OPENROWSET в Управляемый экземпляр SQL Azure
• Установите терминаторы полей и строк (SQL Server)

Связанный контент

• УДАЛИТЬ (Transact-SQL)
• Предложение FROM плюс JOIN, APPLY, PIVOT (Transact-SQL)
• ВСТАВЬТЕ (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• ОБНОВЛЕНИЕ (Transact-SQL)
• ГДЕ (Transact-SQL)