Udostępnij za pośrednictwem

Importowanie danych do programu SQL Server za pomocą funkcji BULK INSERT lub OPENROWSET(BULK...)

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Ten artykuł zawiera omówienie sposobu używania instrukcji Transact-SQL BULK INSERT oraz instrukcji INSERT... SELECT * FROM OPENROWSET(BULK...) do masowego importowania danych z pliku do tabeli w SQL Server lub Azure SQL Database. W tym artykule opisano również zagadnienia dotyczące zabezpieczeń dotyczące używania funkcji BULK INSERT i OPENROWSET(BULK...) oraz używania tych metod do zbiorczego importowania ze zdalnego źródła danych.

Notatka

W przypadku używania funkcji BULK INSERT lub OPENROWSET(BULK...) ważne jest, aby zrozumieć, jak wersja programu SQL Server obsługuje personifikację. Aby uzyskać więcej informacji, zobacz sekcję "Zagadnienia dotyczące zabezpieczeń", w dalszej części tego tematu.

INSTRUKCJA BULK INSERT

FUNKCJA BULK INSERT ładuje dane z pliku danych do tabeli. Ta funkcja jest podobna do tej udostępnionej przez w opcji polecenia bcp; jednak plik danych jest odczytywany przez proces programu SQL Server. Aby uzyskać opis składni BULK INSERT, zobacz BULK INSERT (Transact-SQL).

Przykłady operacji BULK INSERT

Funkcja OPENROWSET(BULK...)

Dostęp do dostawcy zbiorczych zestawów wierszy OPENROWSET uzyskuje się przez wywołanie funkcji OPENROWSET i określenie opcji BULK. Funkcja OPENROWSET(BULK...) umożliwia dostęp do danych zdalnych, łącząc się ze zdalnym źródłem danych, takim jak plik danych, za pośrednictwem dostawcy OLE DB.

Aby zbiorczo zaimportować dane, wywołaj OPENROWSET(BULK...) z klauzuli SELECT...FROM w instrukcji INSERT. Podstawowa składnia zbiorczego importowania danych to:

WSTAWIAĆ... SELECT * FROM OPENROWSET(BULK...)

W przypadku użycia w instrukcji INSERT funkcja OPENROWSET(BULK...) obsługuje wskazówki dotyczące tabeli. Oprócz zwykłych wskazówek tabeli, takich jak TABLOCK, klauzula BULK może akceptować następujące wyspecjalizowane wskazówki tabeli: IGNORE_CONSTRAINTS (ignoruje tylko ograniczenia CHECK), IGNORE_TRIGGERS, KEEPDEFAULTS i KEEPIDENTITY. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące tabel (Transact-SQL).

Aby uzyskać informacje o dodatkowych zastosowaniach opcji BULK, zobacz OPENROWSET (Transact-SQL).

INSERT... SELECT * FROM OPENROWSET(BULK...) — przykłady

Zagadnienia dotyczące zabezpieczeń

Jeśli użytkownik loguje się przy pomocy SQL Server, używany jest profil zabezpieczeń konta procesu SQL Server. Nie można uwierzytelnić logowania przy użyciu uwierzytelniania SQL Server poza silnikiem bazy danych. W związku z tym po zainicjowaniu polecenia BULK INSERT przy użyciu uwierzytelniania programu SQL Server połączenie z danymi odbywa się przy użyciu kontekstu zabezpieczeń konta procesu programu SQL Server (konta używanego przez usługę aparatu bazy danych programu SQL Server).

Aby pomyślnie odczytać dane źródłowe, musisz przyznać kontu używanemu przez silnik bazy danych SQL Server dostęp do danych źródłowych. Natomiast jeśli użytkownik programu SQL Server loguje się przy użyciu uwierzytelniania systemu Windows, użytkownik może odczytywać tylko te pliki, do których można uzyskać dostęp przy użyciu konta użytkownika, niezależnie od profilu zabezpieczeń procesu programu SQL Server.

Rozważmy na przykład użytkownika, który zalogował się do instancji SQL Server przy użyciu uwierzytelniania Windows. Aby użytkownik mógł użyć funkcji BULK INSERT lub OPENROWSET do zaimportowania danych z pliku danych do tabeli programu SQL Server, konto użytkownika wymaga dostępu do odczytu do pliku danych. Mając dostęp do pliku danych, użytkownik może zaimportować dane z pliku do tabeli, nawet jeśli proces programu SQL Server nie ma uprawnień dostępu do pliku. Użytkownik nie musi udzielać uprawnień dostępu do plików procesowi programu SQL Server.

Program SQL Server i system Microsoft Windows można skonfigurować tak, aby umożliwić wystąpieniu programu SQL Server łączenie się z innym wystąpieniem programu SQL Server przez przekazanie poświadczeń uwierzytelnionego użytkownika systemu Windows. Ten układ jest znany jako podszywanie się lub delegowanie . Zrozumienie, jak wersja programu SQL Server obsługuje zabezpieczenia personifikacji użytkownika, jest ważne w przypadku używania funkcji BULK INSERT lub OPENROWSET. Personifikacja użytkownika umożliwia, aby plik danych znajdował się na innym komputerze niż proces programu SQL Server lub użytkownik. Jeśli na przykład użytkownik na Computer_A ma dostęp do pliku danych w Computer_B, a delegowanie poświadczeń zostało ustawione odpowiednio, użytkownik może nawiązać połączenie z wystąpieniem programu SQL Server uruchomionym w Computer_C, uzyskać dostęp do pliku danych w Computer_B, i zbiorcze importowanie danych z tego pliku do tabeli w Computer_C.

Zbiorcze importowanie do programu SQL Server z pliku danych zdalnych

Aby użyć operacji BULK INSERT lub INSERT...SELECT * FROM OPENROWSET(BULK...), aby masowo zaimportować dane z innego komputera, plik danych musi być współużytkowany między dwoma komputerami. Aby określić plik danych udostępnionych, użyj jego nazwy uniwersalnej konwencji nazewnictwa (UNC), która przyjmuje formę ogólną, \\Nazwa_serwera\Nazwa_udziału\Ścieżka\Nazwa pliku. Ponadto konto używane do uzyskiwania dostępu do pliku danych musi mieć uprawnienia wymagane do odczytu pliku na dysku zdalnym.

Na przykład następująca instrukcja BULK INSERT zbiorczo importuje dane do tabeli SalesOrderDetail bazy danych AdventureWorks z pliku danych o nazwie newdata.txt. Ten plik danych znajduje się w folderze udostępnionym o nazwie \dailyorders w katalogu udziału sieciowego o nazwie salesforce w systemie o nazwie computer2.

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

Notatka

To ograniczenie nie ma zastosowania do narzędzia bcp, ponieważ klient odczytuje plik niezależnie od programu SQL Server.

Zbiorcze importowanie z usługi Azure Blob Storage

Podczas importowania z usługi Azure Blob Storage, kiedy dane nie są publiczne (dostęp anonimowy), utwórz POŚWIADCZENIE O ZAKRESIE BAZY DANYCH oparte na kluczu SAS zaszyfrowanym przy użyciu KLUCZA GŁÓWNEGO, a następnie utwórz zewnętrzne źródło bazy danych do użycia w poleceniu BULK INSERT.

Alternatywnie utwórz CREDENCIJA BAZY DANYCH na podstawie MANAGED IDENTITY, aby autoryzować żądania dostępu do danych na kontach magazynu niepublicznego. W przypadku korzystania z MANAGED IDENTITY, usługa Azure Storage musi udzielić uprawnień tożsamości zarządzanej wystąpienia, dodając wbudowaną rolę Współautora Danych Obiektu Blob w Storage. Ta rola dostępu opartego na rolach (RBAC) zapewnia dostęp do odczytu/zapisu tożsamości zarządzanej, zapewniając dostęp do niezbędnych kontenerów usługi Azure Blob Storage. Usługa Azure SQL Managed Instance ma przypisaną przez system tożsamość zarządzaną i może również mieć co najmniej jedną tożsamość zarządzaną przypisaną przez użytkownika. Do autoryzowania żądań można użyć tożsamości zarządzanych przypisanych przez system lub tożsamości zarządzanych przypisanych przez użytkownika. W przypadku autoryzacji zostanie użyta tożsamość default wystąpienia zarządzanego (tj. podstawowa tożsamość zarządzana przypisana przez użytkownika lub tożsamość zarządzana przypisana przez system, jeśli nie określono tożsamości zarządzanej przypisanej przez użytkownika).

Ważny

Tożsamość zarządzana ma zastosowanie tylko do usługi Azure SQL. Program SQL Server nie obsługuje tożsamości zarządzanej.

Notatka

Nie używaj jawnej transakcji, bo w przeciwnym razie pojawi się błąd 4861.

Używanie funkcji BULK INSERT

W poniższym przykładzie pokazano, jak za pomocą polecenia BULK INSERT załadować dane z pliku CSV z lokalizacji magazynu obiektów blob w Azure, gdzie utworzono sygnaturę dostępu współdzielonego. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych. Wymaga to poświadczenia o zakresie bazy danych przy użyciu sygnatury dostępu współdzielonego szyfrowanej przy użyciu klucza głównego w bazie danych użytkownika.

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

W poniższym przykładzie pokazano, jak za pomocą polecenia BULK INSERT załadować dane z pliku CSV w lokalizacji magazynu obiektów blob platformy Azure przy użyciu tożsamości zarządzanej. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych.

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

Ważny

Tożsamość zarządzana ma zastosowanie tylko do usługi Azure SQL. Program SQL Server nie obsługuje tożsamości zarządzanej.

Usługa Azure SQL Database nie obsługuje odczytu z plików systemu Windows.

Korzystanie z OPENROWSET

W poniższym przykładzie przedstawiono, jak za pomocą polecenia OPENROWSET załadować dane z pliku CSV w lokalizacji magazynu blobów Azure, na której utworzono klucz SAS. Lokalizacja usługi Azure Blob Storage jest skonfigurowana jako zewnętrzne źródło danych. Wymaga to poświadczenia w zakresie bazy danych za pomocą współdzielonej sygnatury dostępu, która jest szyfrowana za pomocą klucza głównego w bazie danych użytkownika.

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

Ważny

Usługa Azure SQL Database nie obsługuje odczytu z plików systemu Windows.

Powiązana zawartość