Импорт данных в SQL Server при помощи инструкции BULK INSERT или OPENROWSET(BULK...)
Применимо к:
База данныхSQL Server Azure SQL Управляемый экземпляр SQL Azure
В этой статье представлен обзор использования инструкции Transact-SQL BULK INSERT и инструкции INSERT... ИНСТРУКЦИЯ SELECT * FROM OPENROWSET(BULK...) для массового импорта данных из файла данных в таблицу SQL Server или Azure SQL базы данных. В ней также описываются вопросы безопасности при использовании BULK INSERT и OPENROWSET(BULK…), а также применение этих инструкций для массового импорта из удаленного источника данных.
Примечание
При использовании инструкции BULK INSERT или OPENROWSET(BULK…) важно понимать, каким образом выполняется олицетворение в SQL Server и более поздних версиях. Дополнительные сведения см. в подразделе «Вопросы безопасности» далее в этом разделе.
BULK INSERT, инструкция
Инструкция BULK INSERT загружает данные из файла данных в таблицу. Эти функциональные возможности аналогичны тем, которые предоставляются параметром in команды bcp , но чтение файла данных выполняется процессом SQL Server . Описание синтаксиса BULK INSERT см. в разделе BULK INSERT (Transact-SQL).
Примеры BULK INSERT
- BULK INSERT (Transact-SQL)
- Примеры массового импорта и экспорта XML-документов (SQL Server)
- Сохранение значений идентификаторов при массовом импорте данных (SQL Server)
- Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных (SQL Server)
- Определение признаков конца поля и строки (SQL Server)
- Использование файла форматирования для массового импорта данных (SQL Server)
- Использование символьного формата для импорта или экспорта данных (SQL Server)
- Использование собственного формата для импорта или экспорта данных (SQL Server)
- Использование символьного формата Юникода для импорта и экспорта данных (SQL Server)
- Использование собственного формата Юникода для импорта или экспорта данных (SQL Server)
- Пропуск столбца таблицы с помощью файла форматирования (SQL Server)
- Использование файла форматирования для сопоставления столбцов таблицы с полями файла данных (SQL Server)
OPENROWSET(BULK...) Компонент
Доступ к поставщику больших наборов строк OPENROWSET осуществляется путем вызова функции OPENROWSET и задания параметра BULK. Функция OPENROWSET(BULK...) обеспечивает доступ к удаленным данным, производя соединение с удаленным источником данных, например файлом данных, через поставщик OLE DB.
Чтобы импортировать групповые данные, вызовите функцию OPENROWSET(BULK...) из предложения SELECT...FROM инструкции INSERT. Основной синтаксис массового импорта данных:
Инструкции INSERT ... SELECT * FROM OPENROWSET(BULK...).
При использовании инструкции INSERT функция OPENROWSET(BULK...) поддерживает табличные указания. Кроме обычных табличных указаний вроде TABLOCK, предложение BULK принимает следующие специальные табличные указания: IGNORE_CONSTRAINTS (не учитывается только ограничения CHECK), IGNORE_TRIGGERS, KEEPDEFAULTS и KEEPIDENTITY. Дополнительные сведения см. в разделе Указания по таблицам (Transact-SQL).
Дополнительные сведения об использовании параметра BULK см. в разделе OPENROWSET (Transact-SQL).
Инструкции INSERT...SELECT * FROM OPENROWSET(BULK...) — примеры:
- Примеры массового импорта и экспорта XML-документов (SQL Server)
- Сохранение значений идентификаторов при массовом импорте данных (SQL Server)
- Сохранение значений NULL или использование значений по умолчанию при массовом импорте данных (SQL Server)
- Использование файла форматирования для массового импорта данных (SQL Server)
- Использование символьного формата для импорта или экспорта данных (SQL Server)
- Пропуск столбца таблицы с помощью файла форматирования (SQL Server)
- Использование файла форматирования для пропуска поля данных (SQL Server)
- Использование файла форматирования для сопоставления столбцов таблицы с полями файла данных (SQL Server)
Вопросы безопасности
Если пользователь использует имя входа SQL Server , то применяется профиль безопасности учетной записи процесса SQL Server . За пределами компонента Database Engine невозможно выполнить проверку подлинности имени входа, проходящего проверку подлинности SQL Server. Поэтому, если имя входа, использующее проверку подлинности SQL Server, инициирует команду BULK INSERT, подключение к данным устанавливается с помощью контекста безопасности учетной записи процесса SQL Server (учетной записи, которая используется службой SQL Server Database Engine).
Для того чтобы прочитать исходные данные, учетной записи, которая используется службой SQL Server Database Engine, необходимо предоставить доступ к этим исходным данным. Если пользователь SQL Server входит в систему с проверкой подлинности Windows, то ему доступны только те файлы, к которым имеет доступ учетная запись пользователя, независимо от профиля безопасности процесса SQL Server .
Предположим, пользователь вошел в экземпляр SQL Server с проверкой подлинности Windows. Чтобы иметь возможность воспользоваться BULK INSERT или OPENROWSET для импорта данных из файла данных в таблицу SQL Server , учетная запись должна иметь доступ на чтение этого файла данных. Если же пользователь имеет доступ к файлу данных, то он может импортировать данные из файла в таблицу даже в том случае, когда процесс SQL Server не имеет прав доступа к файлу. Пользователь не должен предоставлять процессу SQL Server права на доступ к файлу.
SQL Server и Microsoft Windows могут быть настроены таким образом, чтобы экземпляр SQL Server мог выполнять соединение с другим экземпляром SQL Server посредством переадресации учетных данных пользователя Windows, прошедшего проверку подлинности. Такой подход называется олицетворением или делегированием. При использовании инструкции BULK INSERT или OPENROWSET очень важно понимать, каким образом в SQL Server и более поздних версиях обеспечивается безопасность при олицетворении пользователя. Это позволяет хранить файл данных не на том компьютере, на котором вошел пользователь или работает процесс SQL Server . Например, если пользователь на компьютере_A имеет доступ к файлу данных на компьютере_Bи делегирование учетных данных было соответствующим образом настроено, этот пользователь может подключиться к экземпляру SQL Server , запущенному на компьютере_C, получить доступ к файлу данных на компьютере_Bи выполнить массовый импорт данных из этого файла в таблицу на компьютере_C.
Массовый импорт в SQL Server из удаленного файла данных
Чтобы использовать инструкции BULK INSERT или INSERT...SELECT * FROM OPENROWSET(BULK...) для массового импорта данных с другого компьютера, необходимо, чтобы файл данных был доступен на обоих компьютерах. Чтобы указать общий файл данных, используйте его имя универсального соглашения об именовании (UNC), которое имеет общий вид Имя \\_сервера Имя_\общего ресурса\Путь\_файла. Кроме того, используемая учетная запись должна обладать разрешениями, необходимыми для чтения этого файла на удаленном диске.
Например, инструкция BULK INSERT производит массовый импорт в таблицу SalesOrderDetail базы данных AdventureWorks из файла данных с именем newdata.txt. Этот файл данных находится в общей папке \dailyorders, расположенной в общем сетевом каталоге salesforce компьютера с именем computer2.
BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
FROM '\\computer2\salesforce\dailyorders\neworders.txt';
Примечание
Это ограничение не применяется к служебной программе bcp, потому что клиент читает файл независимо от SQL Server.
Массовый импорт из хранилища BLOB-объектов Azure
При импорте данных, которые не являются общедоступными (анонимный доступ), из хранилища BLOB-объектов Azure создайте DATABASE SCOPED CREDENTIAL на основе ключа SAS, зашифрованного с помощью MASTER KEY, а затем создайте внешний источник базы данных для использования в команде BULK INSERT.
Кроме того, можно создать учетные данные УРОВНЯ БАЗЫ ДАННЫХ на MANAGED IDENTITY основе , чтобы авторизовать запросы на доступ к данным в учетных записях хранения, не являющихся общедоступными. При использовании MANAGED IDENTITYслужба хранилища Azure должна предоставлять разрешения управляемому удостоверению экземпляра путем добавления встроенной роли RBAC для управления доступом на основе ролей Хранилища, которая предоставляет доступ на чтение и запись к управляемому удостоверению для необходимых контейнеров Хранилище BLOB-объектов Azure. Управляемый экземпляр SQL Azure иметь управляемое удостоверение, назначаемое системой, а также может иметь одно или несколько управляемых удостоверений, назначаемых пользователем. Для авторизации запросов можно использовать управляемые удостоверения, назначаемые системой или пользователем. Для авторизации будет использоваться удостоверение управляемого экземпляра (это основное управляемое удостоверение, default назначаемое пользователем, или управляемое удостоверение, назначаемое системой, если управляемое удостоверение, назначаемое пользователем, не указано).
Важно!
Управляемое удостоверение применимо только к Azure SQL. SQL Server не поддерживает управляемое удостоверение.
Примечание
Не используйте явную транзакцию, чтобы не получить ошибку 4861.
Использование предложения BULK INSERT
В приведенном ниже примере показано, как с помощью команды BULK INSERT загрузить данные из CSV-файла в расположение хранилища BLOB-объектов Azure, для которого был создан ключ SAS. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных. Для этого требуются учетные данные для базы с подписанным URL-адресом, зашифрованным с помощью главного ключа в пользовательской базе данных.
--> 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 = 'YourStrongPassword1';
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***************';
-- NOTE: Make sure that you don't have a leading ? in 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/invoices'
, CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');
В следующем примере показано, как использовать команду BULK INSERT для загрузки данных из CSV-файла в хранилище BLOB-объектов Azure с помощью управляемого удостоверения. Расположение хранилища 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 = 'YourStrongPassword1';
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 = 'Managed Identity';
-- NOTE: Make sure you have granted Storage Bob Data Contributor RBAC on storage to provides read/write access to the managed identity for the necessary Azure Blob Storage containers.
CREATE EXTERNAL DATA SOURCE MyAzureBlobStorage
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://****************.blob.core.windows.net/invoices'
, CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
BULK INSERT Sales.Invoices
FROM 'inv-2017-12-08.csv'
WITH (DATA_SOURCE = 'MyAzureBlobStorage');
Важно!
Управляемое удостоверение применимо только к Azure SQL. SQL Server не поддерживает управляемое удостоверение.
Важно!
База данных SQL Azure не поддерживает чтение данных из файлов Windows.
Использование OPENROWSET
В приведенном ниже примере показано, как с помощью команды OPENROWSET загрузить данные из CSV-файла в расположение хранилища BLOB-объектов Azure, для которого был создан ключ SAS. Расположение хранилища BLOB-объектов Azure настроено как внешний источник данных. Для этого требуются учетные данные для базы с подписанным URL-адресом, зашифрованным с помощью главного ключа в пользовательской базе данных.
--> 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 = 'YourStrongPassword1';
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***************';
-- NOTE: Make sure that you don't have a leading ? in 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/invoices'
, CREDENTIAL= MyAzureBlobStorageCredential --> CREDENTIAL is not required if a blob is configured for public (anonymous) access!
);
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;
Важно!
База данных SQL Azure не поддерживает чтение данных из файлов Windows.