ОБЪЕМ 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 операторе или для обновления целевых данных в UPDATE, INSERT, CTASDELETEMERGEили CETAS операторах.

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

Tip

Эта статья и синтаксис OPENROWSET(BULK) различаются на разных платформах SQL ядро СУБД.

Для Microsoft Fabric Data Warehouse синтаксиса выберите Fabric Data Warehouse в выпадающем списке версий.

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

• Дополнительные сведения о 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, База данных SQL Azure, SQL Database in Fabric и Управляемый экземпляр SQL Azure:

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

Некоторые OPENROWSET варианты относятся к формату, а другие являются универсальными. Например, разделители строк и полей имеют смысл только для текста с разделителями (CSV/TSV), в то время как такие параметры, как DATA_SOURCE и MAXERRORS, применяются ко всем форматам. В следующей таблице показано, какие опции поддерживаются для наиболее распространённых форматов.

Options	CSV(1.0)	CSV(2.0)	PARQUET;	JSONL
DATA_SOURCE, ROWS_PER_BATCH, MAXERRORS	Supported	Supported	Supported	Supported
ERRORFILE, ERRORFILE_DATA_SOURCE, FORMATFILE FORMATFILE_DATA_SOURCE	Supported	Supported	Не поддерживается	Supported
CODEPAGE, ТИП ФАЙЛА ДАННЫХ	Supported	Supported	Не поддерживается	Supported
ПЕРВЫЙ РЯД	Supported	Supported	Не поддерживается	Supported
ROWTERMINATOR, FIELDTERMINATOR, FIELDQUOTE, ESCAPECHAR	Supported	Supported	Не поддерживается	Не поддерживается
Версия_Парсера	Supported	Supported	Не поддерживается	Не поддерживается
ЛАСТРОУ	Supported	Не поддерживается	Не поддерживается	Не поддерживается
HEADER_ROW	Не поддерживается	Supported	Не поддерживается	Не поддерживается
SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB	Не поддерживается	Не поддерживается	Не поддерживается	Не поддерживается

Arguments

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

Дополнительные сведения об использовании параметра см. в разделе BULK" далее в этой статье. Для получения информации о разрешениях, требуемых для BULK этой опции, см. раздел «Разрешения » позже в этой статье.

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

BULK

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

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 Data Warehouse, выберите Fabric Data Warehouse в выпадающем списке версий.

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

URI может включать * символ для соответствия любой последовательности символов, поэтому OPENROWSET может сопоставлять шаблоны с URI. Кроме того, 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 в конечной точке Аналитики Microsoft Fabric и SQL в Microsoft Fabric	нет	Да	Да	Да, использование 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, или конкретная кодовая страница:

Значение CODEPAGE	Description
ACP	Преобразует столбцы char, varchar или текстовых данных из кодовой страницы ANSI/Microsoft Windows (ISO 1252) на кодовую страницу SQL Server.
OEM (по умолчанию)	Преобразует столбцы типа данных char, varchar или text из кодовой страницы системного изготовителя оборудования на кодовую страницу SQL Server.
RAW	Преобразование из одной кодовой страницы в другую не выполняется. Это наиболее быстрый параметр.
Integer	Показывает исходную кодовую страницу, в которой представлены символы в файле данных, например 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 в конечной точке Аналитики Microsoft Fabric и SQL в Microsoft Fabric	Да	Да	нет	Да
База данных 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_CLOB, SINGLE_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'
);

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

ПОЛЕВЫЦИТАТА

Начиная с 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 = '"'
);

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

ФАЙЛ ОШИБКИ

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

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

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

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

Note

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

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

ПЕРВЫЙ РЯД

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

ЛАСТРОУ

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

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) и столбцы с большим объектом (LOB).

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

SINGLE_BLOB

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

Important

Импортируйте XML-данные только с SINGLE_BLOB помощью опции , а не SINGLE_CLOB , SINGLE_NCLOBпоскольку поддерживается только SINGLE_BLOB все конвертации кодирования Windows.

SINGLE_CLOB

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

SINGLE_NCLOB

Читается data_file как Unicode и возвращает содержимое как однострокный, одностолбочный набор строк типа 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>];

Если целевой аккаунт хранилища закрыт, вы также должны назначить членство в роли Storage Blob Data Reader (или выше) принципалу на уровне контейнера или аккаунта хранения.

Remarks

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

• Функции OPENROWSET с параметром BULK требуется корреляционное имя, также известное как переменная диапазона или псевдоним в предложении FROM. Если не добавить AS <table_alias>, вы получите сообщение ошибки 491: «Имя корреляции должно быть указано для основного набора строк в предложении from (from clause»).

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

  • 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 обрабатывает олицетворение. Для информации о соображениях безопасности см. разделы Use BULK INSERT или OPENROWSET (BULK...) для импорта данных в SQL Server.

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

OPENROWSET(BULK...) предполагает, что, если вы не указали иное, максимальная длина SQLCHAR, SQLNCHAR, или SQLBINARY данных не превышает 8 000 байт. Если вы импортируете данные в поле данных LOB, содержащее любые объекты varchar(max), nvarchar(max) или varbinary(max ), превышающие 8 000 байт, вам нужно использовать файл в формате 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];

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

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

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

Тип возвратных данных функции filepath — 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-битная Microsoft Text Driver (*.txt, *.csv) версия, которая не работает с 64-битной версией 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-файла в хранилище Azure Blob, где вы создали ключ SAS. Вы настраиваете место хранения Azure Blob как внешний источник данных. Для этого процесса требуется учетная точка данных с ограничением базы данных, использующая подпись общего доступа (SAS), зашифрованную с помощью мастер-ключа в базе данных пользователя.

-- 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)

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

• DELETE (Transact-SQL)
• Предложение FROM плюс JOIN, APPLY, PIVOT (Transact-SQL)
• INSERT (Transact-SQL)
• OPENDATASOURCE (Transact-SQL)
• OPENQUERY (Transact-SQL)
• SELECT (Transact-SQL)
• UPDATE (Transact-SQL)
• ГДЕ (Transact-SQL)