Virtualisasi tabel delta dengan PolyBase

  • Artikel

Berlaku untuk: SQL Server 2022 (16.x) dan versi yang lebih baru

SQL Server 2022 (16.x) dapat mengkueri data langsung dari folder tabel delta. Konsep ini, umumnya disebut sebagai virtualisasi data, memungkinkan data untuk tetap berada di lokasi aslinya, tetapi dapat dikueri dari instans SQL Server dengan perintah T-SQL seperti tabel lainnya. Fitur ini menggunakan konektor PolyBase, dan meminimalkan kebutuhan untuk menyalin data melalui proses ETL.

Dalam contoh berikut, folder tabel delta disimpan di Azure Blob Storage dan diakses melalui OPENROWSET atau tabel eksternal.

Untuk informasi selengkapnya tentang virtualisasi data, Memperkenalkan virtualisasi data dengan PolyBase.

Prakonfigurasi

1. Aktifkan PolyBase di sp_configure

exec sp_configure @configname = 'polybase enabled', @configvalue = 1;

RECONFIGURE;

2. Membuat database pengguna

Latihan ini membuat database sampel dengan pengaturan dan lokasi default. Anda menggunakan database sampel kosong ini untuk bekerja dengan data dan menyimpan kredensial cakupan. Dalam contoh ini, database kosong baru bernama Delta_demo digunakan.

CREATE DATABASE [Delta_demo];

3. Buat kunci master dan kredensial lingkup database

Kunci master database dalam database pengguna diperlukan untuk mengenkripsi rahasia kredensial cakupan database, delta_storage_dsc. Untuk contoh ini, tabel delta berada di Azure Data Lake Storage Gen2.

USE [Delta_demo];
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL delta_storage_dsc
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<SAS Token>';

4. Buat sumber data eksternal

Kredensial lingkup database digunakan untuk sumber data eksternal. Dalam contoh ini, tabel delta berada di Azure Data Lake Storage Gen2, jadi gunakan awalan adlsSHARED ACCESS SIGNATURE dan metode identitas. Untuk informasi selengkapnya tentang konektor dan awalan, termasuk pengaturan baru untuk SQL Server 2022 (16.x), lihat MEMBUAT SUMBER DATA EKSTERNAL.

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://<container>@<storage_account>.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Misalnya, jika akun penyimpanan Anda diberi nama delta_lake_sample dan kontainer diberi nama sink, kodenya adalah:

CREATE EXTERNAL DATA SOURCE Delta_ED
WITH (
    LOCATION = 'adls://sink@delta_lake_sample.dfs.core.windows.net',
    CREDENTIAL = delta_storage_dsc
);

Menggunakan OPENROWSET untuk mengakses data

Dalam contoh ini, folder Tabel Data diberi nama Contoso.

Karena sumber Delta_ED data eksternal dipetakan ke tingkat kontainer. Folder Contoso tabel delta terletak di akar. Untuk mengkueri file dalam struktur folder, berikan pemetaan folder relatif terhadap parameter LOCATION sumber data eksternal.

SELECT * FROM OPENROWSET
(
    BULK '/Contoso',
    FORMAT = 'DELTA',
    DATA_SOURCE = 'Delta_ED'
) AS [result];

Mengkueri data dengan tabel eksternal

CREATE EXTERNAL TABLE juga dapat digunakan untuk memvirtualisasi data tabel delta di SQL Server. Kolom harus didefinisikan dan dititik dengan kuat. Meskipun tabel eksternal membutuhkan lebih banyak upaya untuk dibuat, tabel tersebut juga memberikan manfaat tambahan daripada mengkueri sumber data eksternal dengan OPENROWSET. Anda dapat:

  • Memperkuat definisi pengetikan data untuk kolom tertentu
  • Tentukan nullability
  • Tentukan KOLADASI
  • Membuat statistik untuk kolom untuk mengoptimalkan kualitas rencana kueri
  • Membuat model yang lebih terperinci dalam SQL Server untuk akses data guna meningkatkan model keamanan Anda

Untuk informasi selengkapnya, lihat MEMBUAT TABEL EKSTERNAL.

Untuk contoh berikut, sumber data yang sama digunakan.

1. Buat format file eksternal

Untuk menentukan pemformatan file, format file eksternal diperlukan. Format file eksternal juga direkomendasikan karena penggunaan kembali. Untuk informasi selengkapnya, lihat MEMBUAT FORMAT FILE EKSTERNAL.

CREATE EXTERNAL FILE FORMAT DeltaTableFormat WITH(FORMAT_TYPE = DELTA);

2. Buat tabel eksternal

File tabel delta terletak di dan sumber data eksternal untuk contoh ini adalah penyimpanan objek yang kompatibel dengan S3, yang sebelumnya dikonfigurasi /delta/Delta_yob/ di bawah sumber s3_edsdata . PolyBase dapat menggunakan sebagai LOCATION folder tabel delta atau file absolut itu sendiri, yang akan terletak di delta/Delta_yob/_delta_log/00000000000000000000.json.

-- Create External Table using delta
CREATE EXTERNAL TABLE extCall_Center_delta (
    id INT,
    name VARCHAR(200),
    dob DATE
)
WITH (
        LOCATION = '/delta/Delta_yob/',
        FILE_FORMAT = DeltaTableFormat,
        DATA_SOURCE = s3_eds
);
GO

Pembatasan

Jika Anda membuat tabel eksternal yang menunjuk ke tabel delta yang dipartisi, kolom yang digunakan untuk pemartisian akan kembali NULL saat mengkueri tabel eksternal. Namun, jika Anda menggunakan OPENROWSET kueri, nilai kolom akan ditampilkan dengan benar. Untuk mengatasi batasan ini, buat tampilan pada OPENROWSET kueri, lalu kueri tampilan untuk mendapatkan nilai kolom yang dipartisi agar dikembalikan dengan benar.

Anda mungkin mengalami kesalahan berikut saat mengkueri tabel Delta eksternal:

Msg 2571, Level 14, State 3, Line 1
User '<user>' does not have permission to run DBCC TRACEON.
Msg 16513, Level 16, State 0, Line 1
Error reading external metadata.

Ini bisa terjadi karena ada QUERYTRACEON petunjuk kueri yang dapat ditambahkan ke kueri metadata file Delta dan yang memerlukan sysadmin peran server untuk dijalankan. Jika ini terjadi, Anda dapat mengatasi masalah dengan mengaktifkan bendera pelacakan 14073 secara global dan ini akan mencegah petunjuk kueri ditambahkan.

Konten terkait