Bagikan melalui

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

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:

  • Fungsi OPENROWSET yang memfasilitasi kueri ad-hoc melalui file di Azure Storage.
  • Tabel eksternal yang merupakan struktur data yang telah ditentukan sebelumnya yang dibangun di atas kumpulan file eksternal.

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

Melakukan kueri file 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.

Prinsipal SQL juga dapat menggunakan OPENROWSET untuk langsung mengkueri file yang dilindungi dengan token SAS atau Identitas Terkelola ruang kerja. Jika pengguna SQL menjalankan fungsi ini, pengguna daya dengan izin harus membuat kredensial cakupan server yang cocok dengan ALTER ANY CREDENTIAL URL dalam fungsi (menggunakan nama penyimpanan dan kontainer) dan memberikan izin REFERENSI untuk kredensial ini kepada pemanggil 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 INFO MASUK tingkat server yang cocok dengan URL, atau pengguna SQL tidak memiliki izin referensi untuk kredensial ini, kesalahan akan dikembalikan. Pengguna SQL tidak dapat menyamar menggunakan identitas apapun dari Microsoft Entra.

Nota

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.

Mengkueri sumber data menggunakan OPENROWSET

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

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

Pengguna yang menjalankan kueri ini harus dapat mengakses file. Pengguna harus diwakili menggunakan token SAS atau Identitas Terkelola dari ruang kerja jika mereka tidak dapat langsung mengakses file menggunakan identitas Microsoft Entra atau akses anonim milik mereka.

DATABASE SCOPED CREDENTIAL menentukan cara mengakses file pada sumber data yang dirujuk (saat ini SAS dan Identitas Terkelola). Pengguna tingkat lanjut dengan CONTROL DATABASE perizinan 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 dalam EXTERNAL DATA SOURCE.

TABEL EKSTERNAL

Pengguna dengan izin untuk 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) dapat 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 disamarkan menggunakan token SAS atau Identitas Terkelola dari ruang kerja jika mereka tidak dapat langsung mengakses file menggunakan identitas Microsoft Entra atau akses anonim mereka.

KREDENSIAL CAKUPAN DATABASE menentukan cara mengakses file pada sumber data yang direferensikan. Pengguna dengan izin CONTROL DATABASE perlu membuat KREDENSIAL BERLINGKUP DATABASE yang akan digunakan untuk mengakses penyimpanan dan SUMBER DATA EKSTERNAL yang menentukan URL sumber data serta 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 menggunakan EXTERNAL TABLE

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

SELECT *
FROM dbo.DimProductsExternal

Pemanggil harus memiliki izin berikut untuk membaca data:

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

Hak akses

Tabel berikut ini mencantumkan izin yang diperlukan untuk operasi yang tercantum di atas.

Pertanyaan Izin yang diperlukan
OPENROWSET(BULK) tanpa sumber data ADMINISTER BULK OPERATIONS, ADMINISTER DATABASE BULK OPERATIONS, atau login SQL harus memiliki REFERENSI KREDENSIAL::<URL> untuk penyimpanan yang terlindungi oleh 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 dari ADMINISTER BULK OPERATIONS atau ADMINISTER DATABASE BULK OPERATIONS
Membuat Sumber Data Eksternal ALTER ANY EXTERNAL DATA SOURCE dan REFERENCES DATABASE SCOPED CREDENTIAL
BUAT TABEL EKSTERNAL CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY EXTERNAL FILE FORMAT, dan ALTER ANY EXTERNAL DATA SOURCE
PILIH DARI TABEL EKSTERNAL 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 untuk setiap tabel/tampilan/fungsi dalam kueri + izin Baca/Tulis pada penyimpanan

Langkah berikutnya

Anda sekarang siap untuk melanjutkan dengan artikel Cara berikut:

  • Last updated on