OPENROWSET BULK (Transact-SQL)

2025-07-29

Область применения:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Команда OPENROWSET T-SQL включает все сведения о подключении, необходимые для доступа к удаленным данным из внешнего источника данных. OPENROWSET также поддерживает массовые операции через встроенный поставщик BULK, который позволяет считывать и возвращать данные из файла в виде набора строк. OPENROWSET BULK предназначен для чтения из внешних файлов данных без OPENROWSET массового чтения из другого ядра СУБД.

На OPENROWSET функцию можно ссылаться в FROM предложении запроса, как если бы это было имя таблицы. Функцию OPENROWSET можно также ссылаться в качестве целевого INSERTUPDATEобъекта или DELETE инструкции, в зависимости от возможностей поставщика данных. Хотя запрос может возвращать несколько результирующих наборов, OPENROWSET возвращает только первую.

OPENROWSET BULK без оператора доступен только в SQL Server, дополнительные сведения см. в разделе OPENROWSET (Transact-SQL).

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

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

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

Syntax

OPENROWSET(BULK) синтаксис используется для чтения внешних файлов:

OPENROWSET( BULK 'data_file' ,
            { FORMATFILE = 'format_file_path' [ <bulk_options> ]
              | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }
)

<bulk_options> ::=
   [ , DATA_SOURCE = 'data_source_name' ]

   -- bulk_options related to input file format
   [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
   [ , FORMAT = { 'CSV' | 'PARQUET' | 'DELTA' } ]
   [ , FIELDQUOTE = 'quote_character' ]
   [ , FORMATFILE = 'format_file_path' ]
   [ , FORMATFILE_DATA_SOURCE = 'data_source_name' ]

   [ , FIRSTROW = first_row ]
   [ , LASTROW = last_row ]

   [ , MAXERRORS = maximum_errors ]
   [ , ERRORFILE = 'file_name' ]
   [ , ERRORFILE_DATA_SOURCE = 'data_source_name' ]

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

Arguments

BULK arguments

BULK Использует поставщик набора строк для OPENROWSET чтения данных из файла. В SQL Server OPENROWSET можно считывать из файла данных без загрузки данных в целевую таблицу. Это позволяет использовать OPENROWSET с базовой SELECT инструкцией.

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

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

Note

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

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

BULK 'data_file'

Полный путь к файлу данных, данные которого необходимо скопировать в целевую таблицу.

SELECT * FROM OPENROWSET(
   BULK 'C:\DATA\inv-2017-01-19.csv',
   SINGLE_CLOB
) AS DATA;

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

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

ERRORFILE = "file_name"

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

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

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

ERRORFILE_DATA_SOURCE_NAME

Начиная с SQL Server 2017 (14.x), этот аргумент является именованным внешним источником данных, указывающим на расположение хранилища BLOB-объектов Azure файла ошибок, который будет содержать ошибки, обнаруженные во время импорта. Внешний источник данных должен быть создан с помощью .TYPE = BLOB_STORAGE Дополнительные сведения см. в статье CREATE EXTERNAL DATA SOURCE (Transact-SQL).

MAXERRORS = maximum_errors

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

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

Note

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

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

DATA_SOURCE

DATA_SOURCE — это внешнее расположение, созданное с помощью CREATE EXTERNAL DATA SOURCE.

FIRSTROW = first_row

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

LASTROW = last_row

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

ROWS_PER_BATCH = rows_per_batch

Указывает примерное количество строк данных в файле данных. Значение должно быть того же порядка, что и реальное количество строк.

OPENROWSET всегда импортирует файл данных в одном пакете. Однако если указать rows_per_batch со значением > 0, обработчик запросов использует значение rows_per_batch в качестве указания для выделения ресурсов в плане запроса.

По умолчанию ROWS_PER_BATCH неизвестно. Указание ROWS_PER_BATCH = 0 совпадает с опущением ROWS_PER_BATCH.

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;

Параметры форматирования входного файла BULK

CODEPAGE = { "ACP" | 'OEM' | 'RAW' | 'code_page' }

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

Important

CODEPAGE не поддерживается в Linux.

Note

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

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

FORMAT = { "CSV" | "PARQUET" | 'DELTA' }

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

Начиная с SQL Server 2022 (16.x), поддерживаются форматы Parquet и Delta.

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

FORMATFILE = "format_file_path"

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

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

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

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

FIELDQUOTE = "field_quote"

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

Remarks

OPENROWSET можно использовать для доступа к удаленным данным из источников данных OLE DB, только если параметр реестра DisallowAdhocAccess явно задан 0 для указанного поставщика, а параметр расширенной конфигурации "Специальные распределенные запросы" включен. Если эти параметры не заданы, поведение по умолчанию не разрешает нерегламентированный доступ.

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

Имена каталогов и схем требуются, если поставщик данных поддерживает несколько каталогов и схем в указанном источнике данных. Значения и catalogschema могут быть опущены, если поставщик данных не поддерживает их. Если поставщик поддерживает только имена схем, необходимо указать двух частей формы schema.object . Если поставщик поддерживает только имена каталогов, необходимо указать трехкомпонентное имя формы catalog.schema.object . Дополнительные сведения см . в соглашениях о синтаксисе Transact-SQL.

Для сквозных запросов, использующих поставщик OLE DB собственного клиента SQL Server, необходимо указать три части.

OPENROWSET не принимает переменные для своих аргументов.

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

Использование OPENROWSET с параметром BULK

Следующие улучшения Transact-SQL поддерживают функцию OPENROWSET(BULK...) :

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

Функции OPENROWSET с параметром BULK требуется корреляционное имя, также известное как переменная диапазона или псевдоним в предложении FROM. Могут быть указаны псевдонимы столбцов. Если список псевдонимов столбцов не указан, файл форматирования должен иметь имена столбцов. Указание псевдонимов столбцов переопределяет имена столбцов в файле форматирования, такие как:
- FROM OPENROWSET(BULK...) AS table_alias
- FROM OPENROWSET(BULK...) AS table_alias(column_alias,...n)
Important

Ошибка при добавлении AS <table_alias> приведет к ошибке: msg 491, Level 16, State 1, Line 20 A Необходимо указать имя корреляции для набора массовых строк в предложении from.
Инструкция SELECT...FROM OPENROWSET(BULK...) запрашивает данные в файле напрямую, не импортируя их в таблицу. Кроме того, инструкции SELECT...FROM OPENROWSET(BULK...) могут перечислять псевдонимы массовых столбцов, используя файл форматирования для указания имен столбцов и типов данных.
Использование OPENROWSET(BULK...) в качестве исходной таблицы в инструкции INSERT или MERGE массово импортирует данные из файла данных в таблицу SQL Server. Дополнительные сведения см. в статье "Использование 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). Сведения о том, когда операции вставки строк, выполняемые массовым импортом, регистрируются в журнале транзакций, см. в разделе "Предварительные требования для минимального ведения журнала в массовом импорте".

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-данных используйте один из следующих типов данных в файле форматирования:

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

Permissions

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

ADMINISTER DATABASE BULK OPERATIONS

or
ADMINISTER BULK OPERATIONS

В следующем примере предоставляется ADMINISTEER DATABASE BULK OPERATIONS субъекту.

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

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

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.CTP1_1\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;

More examples

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

Применимо к:Конечная точка аналитики SQL и хранилище в Microsoft Fabric

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

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

Эта статья относится только к хранилищу Microsoft Fabric. Существуют функциональные различия между функцией OPENROWSET в хранилище Fabric и элементами конечной точки аналитики SQL.

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

Для синтаксиса в других службах выберите версию в раскрывающемся списке версии.

Syntax

SELECT <columns>
FROM OPENROWSET(
    BULK 'https://<storage>.blob.core.windows.net/path/folder1=*/folder2=*/<filename>'
    [, FORMAT = ('PARQUET' | 'CSV' | 'JSONL') ]

    -- execution options
    [, ROWS_PER_BATCH=number_of_rows]

    -- Text/CSV encoding options
    [, DATAFILETYPE = {'char' | 'widechar' }     ]
    [, CODEPAGE = {'ACP' | 'OEM' | 'raw' | '<code_page>' } ]

    -- Text/CSV formatting options
    [, ROWTERMINATOR = 'row_terminator' ]
    [, FIELDTERMINATOR =  'field_terminator' ]
    [, FIELDQUOTE = 'string_delimiter' ]
    [ , PARSER_VERSION = 'parser_version' ]
    [, ESCAPECHAR = 'escape_char' ]
    [, HEADER_ROW = [true|false] ]
    [, FIRSTROW = first_row ]
    [, LASTROW = last_row ]

    -- execution options
    [, ROWS_PER_BATCH=number_of_rows]
) 
[
    WITH (  ( <column_name> <sql_datatype> [ '<column_path>' | <column_ordinal> ] )+ )
]
[ AS <alias> ]

Arguments

BULK 'data_file'

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

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

Вы можете считывать OPENROWSET(BULK) данные непосредственно из файлов, хранящихся в Fabric OneLake, в частности из папки "Файлы " в Fabric Lakehouse. Это устраняет потребность во внешних промежуточных учетных записях (таких как ADLS 2-го поколения или хранилище BLOB-объектов) и обеспечивает прием, управляемый рабочей областью, с помощью разрешений Fabric. Эта функция поддерживает следующие возможности:

Чтение папок Files в Lakehouses
Загрузка рабочей области в хранилище в одном клиенте
Принудительное применение собственных удостоверений с помощью идентификатора Microsoft Entra

Note

Хранилище Fabric OneLake находится в предварительной версии. Ознакомьтесь с ограничениями , применимыми как к COPY INTO , так и OPENROWSET(BULK).

Поддерживаемые форматы URI:

https://{storage}.blob.core.windows.net/[container}/{file path}
https://{storage}.dfs.core.windows.net/[container}/{file path}
abfss://[container}@{storage}.dfs.core.windows.net/{file path}
https://onelake.dfs.fabric.microsoft.com/<workspaceId>/<lakehouseId>/Files/{file path} — в настоящее время в предварительной версии

For example:

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

Параметры форматирования входного файла BULK

FORMAT = { "CSV" | "PARQUET" | "JSONL" }

Указывает формат файла, на который ссылается ссылка. Если расширение файла в пути заканчивается .csv, .tsv, .parquet, .parq, .jsonl.ldjsonили .ndjson, FORMAT не нужно указывать параметр.

Note

Функция OPENROWSET может читать только формат JSON с разделителями новой строки . Эта функция сейчас доступна в предварительной версии. Новый символ строки должен использоваться в качестве разделителя между документами JSON и не может быть помещен в середину документа JSON.

For example:

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

Если путь к файлу не заканчивается одним из этих расширений, необходимо указать FORMAT, например:

SELECT TOP 10 *
FROM OPENROWSET(
      BULK 'abfss://nyctlc@azureopendatastorage.blob.core.windows.net/yellow/**',
      FORMAT='PARQUET'
)

ROWS_PER_BATCH = rows_per_batch

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

For example:

SELECT TOP 10 *
FROM OPENROWSET(
    BULK 'abfss://public@pandemicdatalake.dfs.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
    ROWS_PER_BATCH = 100000
);

Параметры кодирования текста и CSV

DATAFILETYPE = { "char" | 'widechar' }

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

DATAFILETYPE value	Представление данных
char (default)	Character format. Дополнительные сведения см. в разделе Использование символьного формата для импорта или экспорта данных.
widechar	Unicode characters. Дополнительные сведения см. в разделе Использование символьного формата Юникода для импорта или экспорта данных.

CODEPAGE = { "ACP" | 'OEM' | 'RAW' | 'code_page' }

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

Параметры форматирования текста и CSV

ROWTERMINATOR = "row_terminator"

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

FIELDTERMINATOR = "field_terminator"

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

Например, для чтения данных с разделителями табуляции из файла:

SELECT *
FROM OPENROWSET(
    BULK '{file path}',
    ROWTERMINATOR = '\t'
);

FIELDQUOTE = "field_quote"

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

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

SELECT *
FROM OPENROWSET(
    BULK '{file path}',
    FIELDQUOTE = '"',
    FIELDTERMINATOR = ','
);

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"

PARSER_VERSION = "версия_парсера"

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

PARSER_VERSION = "1.0"
PARSER_VERSION = "2.0"

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

Особенности средства синтаксического анализа CSV версии 1.0:

Следующие параметры не поддерживаются: HEADER_ROW.
Терминаторы по умолчанию: \r\n\n и \r.
При указании \n (newline) в качестве конца строки он будет автоматически префиксирован символом \r (возвращаемого каретки), что приводит к прекращению \r\nстроки.

Особенности средства синтаксического анализа для CSV-файлов версии 2.0:

Поддерживаются не все типы данных.
Максимальная длина символьного столбца — 8000.
Размер строки не может превышать 8 МБ.
Следующие параметры не поддерживаются. DATA_COMPRESSION
Пустая строка в кавычках ("") интерпретируется как пустая строка.
Параметр DATEFORMAT SET не учитывается.
Поддерживаемый формат для типа данных даты : YYYY-MM-DD
Поддерживаемый формат для типа данных времени : HH:MM:SS[.fractional seconds]
Поддерживаемый формат для типа данных datetime2 : YYYY-MM-DD HH:MM:SS[.fractional seconds]
Терминаторы по умолчанию и \r\n\n.

ESCAPE_CHAR = char

Задает символ в файле, используемый для экранирования его и всех значений разделителей в файле. Если за escape-символом следует значение, отличное от него самого или какого-либо из значений разделителей, при считывании этого значения escape-символ пропускается.

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

В следующем примере запятая (,) и обратная косая черта (\) экранируются и представлены как \, и \\:

SELECT *
FROM OPENROWSET(
    BULK '{file path}',
    ESCAPECHAR = '\'
);

Place,Address,Icon
Empire State Building,20 W 34th St\, New York\, NY 10118,\\icons\\sol.png
Statue of Liberty,Liberty Island\, New York\, NY 10004,\\icons\\sol.png

HEADER_ROW = { TRUE | FALSE } // заголовочная строка = { ИСТИНА | ЛОЖЬ }

Указывает, содержит ли CSV-файл строку заголовка. По умолчанию — FALSE. Поддерживается в PARSER_VERSION='2.0'. Если TRUEимена столбцов будут считываться из первой строки в соответствии с аргументом FIRSTROW . Если TRUE и схема указаны с помощью WITH, привязка имен столбцов будет выполняться по имени столбца, а не порядковых позиций.

Указывает, содержит ли CSV-файл строку заголовка, которая не должна возвращаться с другими строками данных. По умолчанию — FALSE. Поддерживается в PARSER_VERSION='2.0'. Если TRUEимена столбцов будут считываться из первой строки в соответствии с аргументом FIRSTROW . Если TRUE и схема указаны с помощью WITH, привязка имен столбцов будет выполняться по имени столбца, а не порядковых позиций.

SELECT *
FROM OPENROWSET(
    BULK '{file path}',
    HEADER_ROW = TRUE
);

Place,Latitude,Longitude,Address,Area,State,Zipcode
Empire State Building,40.748817,-73.985428,20 W 34th St,New York,NY,10118
Statue of Liberty,40.689247,-74.044502,Liberty Island,New York,NY,10004

FIRSTROW = first_row

LASTROW = last_row

Execution options

ROWS_PER_BATCH = rows_per_batch

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

WITH Schema

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

В следующем примере :

Столбец country_region имеет тип varchar(50) и ссылается на базовый столбец с тем же именем.
Столбец date ссылается на столбец CSV/Parquet или свойство JSONL с другим физическим именем.
Столбец cases ссылается на третий столбец в файле
Столбец fatal_cases ссылается на вложенное свойство Parquet или вложенный объект JSONL

SELECT *
FROM OPENROWSET(<...>) 
WITH (
        country_region varchar(50), --> country_region column has varchar(50) type and referencing the underlying column with the same name
        [date] DATE '$.updated',   --> date is referencing a CSV/Parquet column or JSONL property with a different physical name
        cases INT 3,             --> cases is referencing third column in the file
        fatal_cases INT '$.statistics.deaths'  --> fatal_cases is referencing a nested Parquet property or JSONL sub-object
     );

<column_name>

Имя столбца, возвращаемого в результирующем наборе строк. Данные для этого столбца считываются из базового столбца файлов с тем же именем, если не переопределяется <column_path> или <column_ordinal>. Имя столбца должно соответствовать правилам идентификаторов имени столбца.

<column_type>

Тип T-SQL столбца в результирующем наборе. Значения из базового файла преобразуются в этот тип, когда OPENROWSET возвращает результаты. Дополнительные сведения см. в разделе "Типы данных" в хранилище Fabric.

<column_path>

Разделенный точками путь (например, $.description.location.lat) используется для ссылки на вложенные поля в сложных типах, таких как Parquet.

<column_ordinal>

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

Remarks

Поддерживаемые функции приведены в таблице:

Feature	Supported	Not available
File formats	Parquet, CSV, JSONL (предварительная версия)	Delta, Azure Cosmos DB, JSON, реляционные базы данных
Authentication	Сквозное руководство entraID/SPN, общедоступное хранилище	SAS/SAK, SPN, Управляемый доступ
Storage	Хранилище BLOB-объектов Azure, Azure Data Lake Storage, Fabric OneLake (предварительная версия)
Options	Только полный или абсолютный URI в `OPENROWSET`	Относительный ПУТЬ URI в `OPENROWSET`, `DATA_SOURCE`
Partitioning	Функцию `filepath()` можно использовать в запросе.

Examples

A. Чтение файла parquet из хранилища BLOB-объектов Azure

В следующем примере показано, как считывать 100 строк из файла Parquet:

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

B. Чтение пользовательского CSV-файла

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

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv',
 HEADER_ROW = TRUE,
 ROW_TERMINATOR = '\n',
 FIELD_TERMINATOR = ',');

C. Указание схемы столбца файла при чтении файла

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

SELECT *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.blob.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet') 
WITH (
        updated DATE
        ,confirmed INT
        ,deaths INT
        ,iso2 VARCHAR(8000)
        ,iso3 VARCHAR(8000)
        );

D. Чтение секционированных наборов данных

В следующем примере показано, как использовать функцию filepath() для чтения частей URI из соответствующего пути к файлу:

SELECT TOP 10 
  files.filepath(2) AS area
, files.*
FROM OPENROWSET(
BULK 'https://synapseaisolutionsa.blob.core.windows.net/public/NYC_Property_Sales_Dataset/*_*.csv',
 HEADER_ROW = TRUE) 
AS files
WHERE files.filepath(1) = '2009';

E. Указание схемы столбца файла при чтении JSONL-файла

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

SELECT TOP 10 *
FROM OPENROWSET(
BULK 'https://pandemicdatalake.dfs.core.windows.net/public/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.jsonl') 
WITH (
        country_region varchar(50),
        date DATE '$.updated',
        cases INT '$.confirmed',
        fatal_cases INT '$.deaths'
     );

Если имя столбца не соответствует физическому имени столбца в свойствах, если JSONL-файл, можно указать физическое имя в пути JSON после определения типа. Можно использовать несколько свойств. Например, $.location.latitude чтобы ссылаться на вложенные свойства в сложных типах parquet или вложенных объектах JSON.

Установите терминаторы полей и строк (SQL Server)

Поделиться через

OPENROWSET BULK (Transact-SQL)

Syntax

Arguments

BULK arguments

BULK 'data_file'

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

ERRORFILE = "file_name"

ERRORFILE_DATA_SOURCE_NAME

MAXERRORS = maximum_errors

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

DATA_SOURCE

FIRSTROW = first_row

LASTROW = last_row

ROWS_PER_BATCH = rows_per_batch

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

UNIQUE

SINGLE_BLOB

SINGLE_CLOB

SINGLE_NCLOB

Параметры форматирования входного файла BULK

CODEPAGE = { "ACP" | 'OEM' | 'RAW' | 'code_page' }

FORMAT = { "CSV" | "PARQUET" | 'DELTA' }

FORMATFILE = "format_file_path"

FIELDQUOTE = "field_quote"

Remarks

Использование OPENROWSET с параметром BULK

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

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

Permissions

Examples

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

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

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

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

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

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

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

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

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

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

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

More examples

Related content

Syntax

Arguments

BULK 'data_file'

Параметры форматирования входного файла BULK

FORMAT = { "CSV" | "PARQUET" | "JSONL" }

ROWS_PER_BATCH = rows_per_batch

Параметры кодирования текста и CSV

DATAFILETYPE = { "char" | 'widechar' }

CODEPAGE = { "ACP" | 'OEM' | 'RAW' | 'code_page' }

Параметры форматирования текста и CSV

ROWTERMINATOR = "row_terminator"

FIELDTERMINATOR = "field_terminator"

FIELDQUOTE = "field_quote"

PARSER_VERSION = "версия_парсера"

ESCAPE_CHAR = char

HEADER_ROW = { TRUE | FALSE } // заголовочная строка = { ИСТИНА | ЛОЖЬ }

FIRSTROW = first_row

LASTROW = last_row

Execution options

ROWS_PER_BATCH = rows_per_batch

WITH Schema

<column_name>

<column_type>

<column_path>

<column_ordinal>

Remarks

Examples

A. Чтение файла parquet из хранилища BLOB-объектов Azure

B. Чтение пользовательского CSV-файла

C. Указание схемы столбца файла при чтении файла

D. Чтение секционированных наборов данных

E. Указание схемы столбца файла при чтении JSONL-файла

Related content

Обратная связь

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