Share via

Mengakses penyimpanan eksternal menggunakan kumpulan SQL tanpa server di Azure Synapse Analytics

  • Artikel

Artikel ini menjelaskan bagaimana pengguna dapat membaca data dari file yang disimpan di Azure Storage di kumpulan SQL tanpa server. Pengguna memiliki opsi berikut untuk mengakses penyimpanan:

  • OPENROWSET berfungsi yang memungkinkan kueri ad-hoc atas file di Azure Storage.
  • Tabel eksternal yang merupakan struktur data yang sudah ditentukan sebelumnya yang dibangun di atas kumpulan file eksternal.

Pengguna dapat menggunakan metode autentikasi yang berbeda seperti autentikasi passthrough Microsoft Entra (default untuk perwakilan Microsoft Entra) dan autentikasi SAS (default untuk prinsipal SQL).

File kueri menggunakan OPENROWSET

OPENROWSET memungkinkan pengguna untuk mengkueri file eksternal di penyimpanan Azure jika mereka memiliki akses ke penyimpanan. Pengguna yang tersambung ke kumpulan SQL tanpa server harus menggunakan kueri berikut untuk membaca konten file di penyimpanan Azure:

SELECT * FROM
 OPENROWSET(BULK 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/*.parquet', format= 'parquet') as rows

Pengguna dapat mengakses penyimpanan menggunakan aturan akses berikut:

  • Pengguna Microsoft Entra - OPENROWSET akan menggunakan identitas Pemanggil Microsoft Entra untuk mengakses Azure Storage atau mengakses penyimpanan dengan akses anonim.
  • Pengguna SQL - OPENROWSET akan mengakses penyimpanan dengan akses anonim atau dapat ditiru menggunakan token SAS atau identitas terkelola ruang kerja.

Prinsip SQL juga dapat menggunakan OPENROWSET untuk secara langsung meminta file yang dilindungi dengan token SAS atau Identitas Terkelola ruang kerja. Jika pengguna SQL menjalankan fungsi ini, pengguna daya dengan ALTER ANY CREDENTIAL izin harus membuat kredensial lingkup server yang cocok dengan URL dalam fungsi (menggunakan nama penyimpanan dan kontainer) dan memberikan izin REFERENSI untuk kredensial ini kepada penelepon fungsi OPENROWSET:

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>]
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

Jika tidak ada KREDENSIAL tingkat server yang cocok dengan URL, atau pengguna SQL tidak memiliki izin referensi untuk kredensial ini, kesalahan akan dikembalikan. Prinsipal SQL tidak dapat meniru menggunakan beberapa identitas Microsoft Entra.

Catatan

Versi OPENROWSET ini dirancang untuk eksplorasi data yang cepat dan mudah menggunakan autentikasi default. Untuk memanfaatkan peniruan identitas atau Identitas Terkelola, gunakan OPENROWSET dengan DATA_SOURCE yang dijelaskan di bagian berikutnya.

Sumber data kueri menggunakan OPENROWSET

OPENROWSET memungkinkan pengguna untuk mengkueri file yang ditempatkan di beberapa sumber data eksternal:

SELECT * FROM
 OPENROWSET(BULK 'file/path/*.parquet',
 DATA_SOURCE = MyAzureInvoices,
 FORMAT= 'parquet') as rows

Pengguna yang menjalankan kueri ini harus bisa mengakses berkas tersebut. Pengguna harus ditiru menggunakan token SAS atau Identitas Terkelola ruang kerja jika mereka tidak dapat langsung mengakses file menggunakan identitas Microsoft Entra atau akses anonim mereka.

DATABASE SCOPED CREDENTIAL menentukan cara mengakses file pada sumber data yang direferensikan (saat ini SAS dan Identitas Terkelola). Pengguna daya dengan CONTROL DATABASE izin perlu membuat DATABASE SCOPED CREDENTIAL yang akan digunakan untuk mengakses penyimpanan dan EXTERNAL DATA SOURCE yang menentukan URL sumber data dan kredensial yang harus digunakan:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL AccessAzureInvoices
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&amp;sp=rwac&amp;se=2017-02-01T00:55:34Z&amp;st=201********' ;

CREATE EXTERNAL DATA SOURCE MyAzureInvoices
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>/' ,
 CREDENTIAL = AccessAzureInvoices) ;

Penelepon harus memiliki salah satu izin berikut untuk menjalankan fungsi OPENROWSET:

  • Salah satu izin untuk menjalankan OPENROWSET:
    • ADMINISTER BULK OPERATIONS memungkinkan login untuk menjalankan fungsi OPENROWSET.
    • ADMINISTER DATABASE BULK OPERATIONS memungkinkan pengguna lingkup database untuk menjalankan fungsi OPENROWSET.
  • REFERENCES DATABASE SCOPED CREDENTIAL ke kredensial yang direferensikan di EXTERNAL DATA SOURCE.

TABEL EKSTERNAL

Pengguna dengan izin membaca tabel dapat mengakses file eksternal menggunakan TABEL EKSTERNAL yang dibuat di atas kumpulan folder dan file Azure Storage.

Pengguna yang memiliki izin untuk membuat tabel eksternal (misalnya CREATE TABLE dan ALTER ANY CREDENTIAL atau REFERENCES DATABASE SCOPED CREDENTIAL) bisa menggunakan skrip berikut untuk membuat tabel di atas sumber data Azure Storage:

CREATE EXTERNAL TABLE [dbo].[DimProductexternal]
( ProductKey int, ProductLabel nvarchar, ProductName nvarchar )
WITH
(
LOCATION='/DimProduct/year=*/month=*' ,
DATA_SOURCE = AzureDataLakeStore ,
FILE_FORMAT = TextFileFormat
) ;

Pengguna yang membaca data dari tabel ini harus dapat mengakses file. Pengguna harus ditiru menggunakan token SAS atau Identitas Terkelola ruang kerja jika mereka tidak dapat langsung mengakses file menggunakan identitas Microsoft Entra atau akses anonim mereka.

DATABASE SCOPED CREDENTIAL menentukan cara mengakses file pada sumber data yang direferensikan. Pengguna dengan izin CONTROL DATABASE perlu membuat DATABASE SCOPED CREDENTIAL yang akan digunakan untuk mengakses penyimpanan dan DATA SOURCE yang menentukan URL sumber data dan kredensial yang harus digunakan:

EXECUTE AS somepoweruser;

-- Create MASTER KEY if it doesn't exists in database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'some very strong password';

CREATE DATABASE SCOPED CREDENTIAL cred
 WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = '******srt=sco&sp=rwac&se=2017-02-01T00:55:34Z&st=201********' ;

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
 WITH ( LOCATION = 'https://<storage_account>.dfs.core.windows.net/<container>/<path>' ,
 CREDENTIAL = cred
 ) ;

Membaca file eksternal dengan EXTERNAL TABLE

EXTERNAL TABLE memungkinkan Anda membaca data dari file yang dirujuk melalui sumber data menggunakan pernyataan SQL SELECT standar:

SELECT *
FROM dbo.DimProductsExternal

Penelepon harus memiliki izin berikut untuk membaca data:

  • SELECT izin ON tabel eksternal
  • REFERENCES DATABASE SCOPED CREDENTIAL izin jika DATA SOURCE memiliki CREDENTIAL

Izin

Daftar tabel berikut ini memerlukan izin untuk operasi yang tercantum di atas.

Kueri Memerlukan izin
OPENROWSET(BULK) tanpa sumber data ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, atau data masuk SQL harus memiliki REFERENCES CREDENTIAL::<URL> untuk penyimpanan yang dilindungi SAS
OPENROWSET(BULK) dengan sumber data tanpa kredensial ADMINISTER BULK OPERATIONS atau ADMINISTER DATABASE BULK OPERATIONS,
OPENROWSET(BULK) dengan sumber data dengan kredensial REFERENCES DATABASE SCOPED CREDENTIAL dan salah satu ADMINISTER BULK OPERATIONS atau ADMINISTER DATABASE BULK OPERATIONS
CREATE EXTERNAL DATA SOURCE ALTER ANY EXTERNAL DATA SOURCE dan REFERENCES DATABASE SCOPED CREDENTIAL
CREATE EXTERNAL TABLE CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT, dan ALTER ANY EXTERNAL DATA SOURCE
SELECT FROM EXTERNAL TABLE SELECT TABLE dan REFERENCES DATABASE SCOPED CREDENTIAL
CETAS Untuk membuat tabel - CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY DATA SOURCE, dan ALTER ANY EXTERNAL FILE FORMAT. Untuk membaca data: ADMINISTER BULK OPERATIONS atau REFERENCES CREDENTIAL atau SELECT TABLE per setiap tabel/tampilan/fungsi dalam kueri + izin R/W pada penyimpanan

Langkah berikutnya

Sekarang Anda siap untuk melanjutkan artikel Cara Menggunakan berikut ini: