Импорт данных в SQL Server при помощи инструкции BULK INSERT или OPENROWSET(BULK...)

  • Статья

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

В этой статье представлен обзор использования инструкции Transact-SQL BULK INSERT и INSERT... ИНСТРУКЦИЯ SELECT * FROM OPENROWSET(BULK...) для массового импорта данных из файла данных в таблицу SQL Server или База данных SQL Azure. В ней также описываются вопросы безопасности при использовании BULK INSERT и OPENROWSET(BULK…), а также применение этих инструкций для массового импорта из удаленного источника данных.

Примечание.

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

Инструкция BULK INSERT

Инструкция BULK INSERT загружает данные из файла данных в таблицу. Эта функция аналогична функции, предоставленной командой bcp. Однако файл данных считывается процессом SQL Server. Описание синтаксиса BULK INSERT см. в статье BULK INSERT (Transact-SQL).

Примеры BULK INSERT

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

ВСТАВКА... ИНСТРУКЦИИ SELECT * FROM OPENROWSET(BULK...) — примеры

Вопросы безопасности

Если пользователь использует имя входа SQL Server, используется профиль безопасности учетной записи процесса SQL Server. За пределами ядра СУБД нельзя выполнить проверку подлинности имени входа, проходящего проверку подлинности SQL Server. Поэтому, если имя входа, использующее проверку подлинности SQL Server, инициирует команду BULK INSERT, подключение к данным устанавливается с помощью контекста безопасности учетной записи процесса SQL Server (учетной записи, которая используется службой SQL Server Database Engine).

Чтобы успешно считывать исходные данные, необходимо предоставить учетную запись, используемую ядро СУБД SQL Server, доступ к исходным данным. В отличие от этого, если пользователь 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. Такой подход называется олицетворением или делегированием. Понимание того, как версия SQL Server обрабатывает безопасность олицетворения пользователей, важно при использовании BULK INSERT или OPENROWSET. Олицетворение пользователя позволяет файлу данных находиться на другом компьютере, отличном от процесса SQL Server или пользователя. Например, если пользователь на Computer_A имеет доступ к файлу данных Computer_B, а делегирование учетных данных задано соответствующим образом, пользователь может подключиться к экземпляру SQL Server, работающему на Computer_C, получить доступ к файлу данных Computer_B и массово импортировать данные из этого файла в таблицу Computer_C.

Массовый импорт в SQL Server из удаленного файла данных

Использование BULK INSERT или INSERT... SELECT * FROM OPENROWSET(BULK...) для массового импорта данных с другого компьютера файл данных должен быть предоставлен общий доступ между двумя компьютерами. Укажите общий файл данных в формате UNC, то есть в следующем формате: \\Имя сервера\Общая папка\Путь\Имя файла. Кроме того, используемая учетная запись должна обладать разрешениями, необходимыми для чтения этого файла на удаленном диске.

Например, инструкция BULK INSERT производит массовый импорт в таблицу SalesOrderDetail базы данных AdventureWorks из файла данных с именем newdata.txt. Этот файл данных находится в общей папке \dailyorders, расположенной в общем сетевом каталоге salesforce компьютера с именем computer2.

BULK INSERT AdventureWorks2022.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';

Примечание.

Это ограничение не применяется к служебной программе bcp , так как клиент считывает файл независимо от SQL Server.

Массовый импорт из хранилища BLOB-объектов Azure

При импорте из хранилища BLOB-объектов Azure и данных не является общедоступным (анонимным доступом), создайте учетные данные DATABASE SCOPED на основе ключа SAS, зашифрованного с помощью MASTER KEY, а затем создайте внешний источник базы данных для использования в команде BULK INSERT.

Кроме того, создайте УЧЕТНЫе данные DATABASE SCOPED на MANAGED IDENTITY основе авторизации запросов на доступ к данным в учетных записях хранения, отличных от публикации. При использовании MANAGED IDENTITYхранилище Azure должно предоставить разрешения управляемому удостоверению экземпляра, добавив встроенную роль управления доступом на основе ролей Azure (RBAC), которая предоставляет доступ на чтение и запись к управляемому удостоверению для необходимых контейнеров Хранилище BLOB-объектов Azure. Управляемый экземпляр SQL Azure иметь управляемое удостоверение, назначаемое системой, а также может иметь одно или несколько управляемых удостоверений, назначаемых пользователем. Для авторизации запросов можно использовать управляемые удостоверения, назначаемые системой или пользователем. Для авторизации default будет использоваться удостоверение управляемого экземпляра (это основное управляемое удостоверение, назначаемое пользователем, или управляемое удостоверение, назначаемое системой, если назначаемое пользователем управляемое удостоверение не указано).

Внимание

Управляемое удостоверение применимо только к SQL Azure. 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');

Внимание

Управляемое удостоверение применимо только к SQL Azure. 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.

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