OPENROWSET BULK (Transact-SQL)

2025-07-15

Applies to:SQL Server Azure SQL Database Azure SQL Managed Instance

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

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

OPENROWSET without the BULK operator is available on SQL Server only, for more information, see OPENROWSET (Transact-SQL).

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

Для синтаксиса хранилища данных Microsoft Fabric выберите Fabric в раскрывающемся списке версий.
Примеры Управляемый экземпляр SQL Azure см. в статье "Запрос источников данных" с помощью OPENROWSET.
Сведения и примеры с бессерверными пулами 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 параметра позволяют значительно контролировать, где начать и завершить чтение данных, как справиться с ошибками и способом интерпретации данных. For example, you can specify that the data file is read as a single-row, single-column rowset of type varbinary, varchar, or nvarchar. Поведение по умолчанию описано в следующем далее описании аргументов.

For information about how to use the BULK option, see the Remarks section later in this article. For information about the permissions that the BULK option requires, see the Permissions section, later in this article.

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 игнорирует каждую плохую строку, а не загружает ее, и подсчитывает плохую строку как одну ошибку.

The default for maximum_errors is 10.

Note

MAX_ERRORS doesn't apply to CHECK constraints, or to converting money and bigint data types.

Параметры обработки данных 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 всегда импортирует файл данных в одном пакете. However, if you specify rows_per_batch with a value > 0, the query processor uses the value of rows_per_batch as a hint for allocating resources in the query plan.

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

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

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

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

UNIQUE

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

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

Псевдонимы столбцов требуются при ORDER использовании. Список псевдонимов столбцов должен ссылаться на производную таблицу, доступ к которой осуществляется предложением BULK . Имена столбцов, указанные в ORDER предложении, ссылаются на этот список псевдонимов столбцов. Large value types (varchar(max), nvarchar(max), varbinary(max), and xml) and large object (LOB) types (text, ntext, and image) columns can't be specified.

SINGLE_BLOB

Returns the contents of data_file as a single-row, single-column rowset of type varbinary(max).

Important

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

SINGLE_CLOB

By reading data_file as ASCII, returns the contents as a single-row, single-column rowset of type varchar(max), using the collation of the current database.

SINGLE_NCLOB

By reading data_file as Unicode, returns the contents as a single-row, single-column rowset of type nvarchar(max), using the collation of the current database.

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

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

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

Указывает кодовую страницу данных в файле данных. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values more than 127 or less than 32.

Important

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

Note

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

CODEPAGE value	Description
`ACP`	Converts columns of char, varchar, or text data type from the ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server code page.
`OEM` (по умолчанию)	Converts columns of char, varchar, or text data type from the system OEM code page to the SQL Server code page.
`RAW`	Преобразование из одной кодовой страницы в другую не выполняется. Это наиболее быстрый параметр.
`code_page`	Показывает исходную кодовую страницу, в которой представлены символы в файле данных, например 850. Important Versions before SQL Server 2016 (13.x) don't support code page 65001 (UTF-8 encoding).

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

Beginning with SQL Server 2017 (14.x), this argument specifies a comma separated values file compliant to the RFC 4180 standard.

Начиная с 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-файле. If not specified, the quote character (") is used as the quote character as defined in the RFC 4180 standard. В качестве значения этого параметра можно указать только один символ.

Remarks

OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. Если эти параметры не заданы, поведение по умолчанию не разрешает нерегламентированный доступ.

При доступе к удаленным источникам данных 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 байт. If the data being imported is in a LOB data field that contains any varchar(max), nvarchar(max), or varbinary(max) objects that exceed 8,000 bytes, you must use an XML format file that defines the maximum length for the data field. Чтобы указать максимальную длину, измените файл форматирования и объявите атрибут 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)

Applies to: SQL Server only.

The following example creates a small table for demonstration purposes, and inserts file data from a file named Text1.txt located in the C: root directory into a varbinary(max) column.

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 с файлом форматирования для извлечения строк из текстового файла

Applies to: SQL Server only.

В следующем примере используется файл форматирования для получения строк, разделенных символами табуляции, из файла 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. Указание файла форматирования и кодовой страницы

Applies to: SQL Server only.

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

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

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

Applies to: SQL Server 2017 (14.x) and later versions only.

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-файла без файла форматирования

Applies to: SQL Server only.

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-разрядным. Open the Drivers tab of the Connect to an ODBC Data Source (SQL Server Import and Export Wizard) application in Windows to verify this. Существует 32-разрядная версия, которая не будет работать с 64-разрядной Microsoft Text Driver (*.txt, *.csv) версией sqlservr.exe.

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

Applies to: SQL Server 2017 (14.x) and later versions only.

В 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. Использование управляемого удостоверения для внешнего источника

Applies to: Azure SQL Managed Instance and Azure SQL Database

В следующем примере создаются учетные данные с помощью управляемого удостоверения, также создается внешний источник, а затем данные загружаются из 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

Applies to: SQL Server 2022 (16.x) and later versions.

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

Applies to: SQL Server 2022 (16.x) and later versions.

В этом примере контейнер таблицы данных называется 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://[email protected]');

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

Applies to:SQL analytics endpoint and Warehouse in Microsoft Fabric

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

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

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

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

Для синтаксиса SQL Server выбрать версию SQL Server в раскрывающемся списке.
Примеры Управляемый экземпляр SQL Azure см. в статье "Запрос источников данных" с помощью OPENROWSET.
База данных SQL Azure поддерживает только OPENROWSET BULK (Transact-SQL).
Сведения и примеры с бессерверными пулами SQL в Azure Synapse см. в статье "Использование OPENROWSET с использованием бессерверного пула SQL в Azure Synapse Analytics".
Выделенные пулы SQL в Azure Synapse не поддерживают функцию OPENROWSET .

Syntax

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

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

    -- Text/CSV formatting options
    [, ROWTERMINATOR = 'row_terminator' ]
    [, FIELDTERMINATOR =  'field_terminator' ]
    [, FIELDQUOTE = 'string_delimiter' ]
    [, 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 с шаблоном.

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

FORMAT = { "CSV" | 'PARQUET' }

Указывает формат файла, на который ссылается ссылка. Если расширение файла в пути заканчивается .csv, PARQUET или PARQ, FORMAT параметр не требуется указывать. 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'
);

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`	Converts columns of char, varchar, or text data type from the ANSI/Microsoft Windows code page (ISO 1252) to the SQL Server code page.
`OEM` (по умолчанию)	Converts columns of char, varchar, or text data type from the system OEM code page to the SQL Server code page.
`RAW`	Преобразование из одной кодовой страницы в другую не выполняется. Это наиболее быстрый параметр.
`code_page`	Показывает исходную кодовую страницу, в которой представлены символы в файле данных, например 850. Important Versions before SQL Server 2016 (13.x) don't support code page 65001 (UTF-8 encoding).

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

ROWTERMINATOR = 'row_terminator'

Specifies the row terminator to be used for char and widechar data files. По умолчанию признаком конца строки является символ \r\n (символ новой строки). Дополнительные сведения см. в разделе "Указание полей и терминаторов строк".

FIELDTERMINATOR = 'field_terminator'

Specifies the field terminator to be used for char and widechar data files. Терминатор поля по умолчанию — , (запятая). Дополнительные сведения см. в разделе "Указание полей и терминаторов строк".

FIELDQUOTE = 'field_quote'

Задает символ, используемый в качестве символа кавычки в CSV-файле. If not specified, the quote character (") is used as the quote character as defined in the RFC 4180 standard.

ESCAPE_CHAR = char

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

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

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

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

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. Он включает определения столбцов для каждого столбца, возвращаемого в результате, и описывает правила сопоставления, которые привязывают базовые столбцы файлов к столбцам в результирующем наборе.

<column_name>

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

<column_type>

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

<column_path>

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

<column_ordinal>

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

Remarks

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

Feature	Supported	Not available
File formats	Parquet, CSV	Delta, Azure Cosmos DB
Authentication	Сквозное руководство EntraID, общедоступное хранилище	SAS/SAK, SPN, Управляемый доступ
Storage	Хранилище BLOB-объектов Azure, Azure Data Lake Storage	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';

Установите терминаторы полей и строк (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 ( { column [ 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' }

DATAFILETYPE = { "char" | 'widechar' }

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

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

ROWTERMINATOR = 'row_terminator'

FIELDTERMINATOR = 'field_terminator'

FIELDQUOTE = 'field_quote'

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. Чтение секционированных наборов данных

Related content

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

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