Bagikan melalui

Virtualisasi data dengan Azure SQL Database (Pratinjau)

Berlaku untuk:Azure SQL Database

Fitur virtualisasi data Azure SQL Database memungkinkan Anda menjalankan kueri Transact-SQL (T-SQL) pada file yang menyimpan data dalam format data umum seperti CSV (tanpa perlu menggunakan Teks Berbatas), Parquet, dan Delta (1.0). Anda dapat mengkueri data ini di Azure Data Lake Storage Gen2 atau Azure Blob Storage, dan menggabungkannya dengan data relasional yang disimpan secara lokal menggunakan gabungan. Dengan cara ini Anda dapat secara transparan mengakses data eksternal (dalam mode baca-saja) sambil menyimpannya dalam format dan lokasi aslinya - juga dikenal sebagai virtualisasi data.

Gambaran Umum

Virtualisasi data menyediakan dua cara untuk mengkueri file yang ditujukan untuk serangkaian skenario yang berbeda:

  • Sintaks OPENROWSET – dioptimalkan untuk kueri ad hoc file. Biasanya digunakan untuk menjelajahi konten dan struktur set file baru dengan cepat.
  • SINTAKS CREATE EXTERNAL TABLE – dioptimalkan untuk kueri file berulang menggunakan sintaksis yang identik seolah-olah data disimpan secara lokal dalam database. Tabel eksternal memerlukan beberapa langkah persiapan dibandingkan dengan sintaks OPENROWSET, tetapi memberikan kontrol yang lebih besar atas akses data. Tabel eksternal biasanya digunakan untuk beban kerja analitis dan pelaporan.

Dalam kedua kasus, sumber data eksternal harus dibuat menggunakan sintaksIS CREATE EXTERNAL DATA SOURCE T-SQL, seperti yang ditunjukkan dalam artikel ini.

Format Berkas

Format file parquet dan teks berbatas (CSV) didukung secara langsung. Format file JSON secara tidak langsung didukung dengan menentukan format file CSV di mana kueri mengembalikan setiap dokumen sebagai baris terpisah. Anda dapat mengurai baris lebih lanjut menggunakan JSON_VALUE dan OPENJSON.

Tipe penyimpanan

File dapat disimpan di Azure Data Lake Storage Gen2 atau Azure Blob Storage. Untuk mengkueri file, Anda perlu menyediakan lokasi dalam format tertentu dan menggunakan awalan jenis lokasi yang sesuai dengan jenis sumber eksternal dan titik akhir/protokol, seperti contoh berikut:

--Blob Storage endpoint
abs://<container>@<storage_account>.blob.core.windows.net/<path>/<file_name>.parquet
--or
abs://<storage_account_name>.blob.core.windows.net/<container_name>/

--Data Lake endpoint
adls://<container>@<storage_account>.dfs.core.windows.net/<path>/<file_name>.parquet
--or
adls://<storage_account_name>.dfs.core.windows.net/<container_name>/

Penting

Selalu gunakan awalan khusus titik akhir. Awalan jenis Lokasi yang disediakan digunakan untuk memilih protokol optimal untuk komunikasi dan untuk memanfaatkan kemampuan lanjutan yang ditawarkan oleh jenis penyimpanan tertentu.

Awalan generik https:// hanya didukung untuk BULK INSERT, tetapi tidak untuk kasus penggunaan lain termasuk OPENROWSET atau EXTERNAL TABLE.

Mulai sekarang!

Jika Anda baru menggunakan virtualisasi data dan ingin menguji fungsionalitas dengan cepat, mulailah dengan mengkueri himpunan data publik yang tersedia di Azure Open Datasets, seperti himpunan data Bing COVID-19 yang memungkinkan akses anonim.

Gunakan titik akhir berikut untuk mengkuerikan himpunan data COVID-19 Bing:

  • Parket: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet
  • CSV: abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.csv

Untuk memulai cepat, jalankan kueri T-SQL sederhana ini untuk mendapatkan wawasan pertama tentang himpunan data. Kueri ini menggunakan OPENROWSET untuk mengkueri file yang disimpan di akun penyimpanan yang tersedia untuk umum:

--Quick query on a file stored in a publicly available storage account:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest/bing_covid-19_data.parquet',
 FORMAT = 'parquet'
) AS filerows;

Anda dapat melanjutkan eksplorasi himpunan WHEREdata dengan menambahkan , GROUP BY dan klausul lainnya berdasarkan kumpulan hasil kueri pertama.

Setelah Anda terbiasa mengkueri himpunan data publik, pertimbangkan untuk beralih ke himpunan data nonpublik yang memerlukan penyediaan kredensial, memberikan hak akses dan mengonfigurasi aturan firewall. Dalam banyak skenario dunia nyata Anda akan beroperasi terutama dengan himpunan data privat.

Akses ke akun penyimpanan nonpublik

Pengguna yang masuk ke Azure SQL Database harus berwenang untuk mengakses dan mengkueri file yang disimpan di akun penyimpanan non-publik. Langkah-langkah otorisasi bergantung pada cara Azure SQL Database mengautentikasi penyimpanan. Jenis autentikasi dan parameter terkait tidak disediakan langsung dengan setiap kueri. Mereka dienkapsulasi dalam objek kredensial lingkup database yang disimpan dalam database pengguna. Kredensial digunakan oleh database untuk mengakses akun penyimpanan kapan saja kueri dijalankan.

Azure SQL Database mendukung jenis autentikasi berikut:

  • Tanda Tangan Akses Bersama (SAS)
  • Identitas yang dikelola
  • Autentikasi lintas langsung Microsoft Entra melalui Identitas Pengguna

Tanda tangan akses bersama (SAS) menyediakan akses yang didelegasikan ke file di akun penyimpanan. SAS memberikan kontrol terperinci atas jenis akses yang Anda berikan, termasuk interval validitas, izin yang diberikan, dan rentang alamat IP yang dapat diterima. Setelah token SAS dibuat, token tersebut tidak dapat dicabut atau dihapus, dan memungkinkan akses hingga periode validitasnya kedaluwarsa.

  1. Anda bisa mendapatkan beberapa cara token SAS:

  2. Berikan izin Baca dan Daftar melalui SAS untuk mengakses data eksternal. Saat ini, virtualisasi data dengan Azure SQL Database bersifat baca-saja.

  3. Untuk membuat kredensial lingkup database di Azure SQL Database, Anda harus terlebih dahulu membuat kunci master database, jika belum ada. Kunci master database diperlukan ketika kredensial memerlukan SECRET.

    -- Create MASTER KEY if it doesn't exist in the database:
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = '<Some Very Strong Password Here>';

  4. Ketika token SAS dihasilkan, token tersebut menyertakan tanda tanya (?) di awal token. Untuk menggunakan token, Anda harus menghapus tanda tanya (?) saat membuat kredensial. Contohnya:

    CREATE DATABASE SCOPED CREDENTIAL MyCredential
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=secret string here';

Akses ke penyimpanan publik melalui akun anonim

Jika himpunan data yang diinginkan memungkinkan akses publik (juga dikenal sebagai akses anonim), tidak ada kredensial yang diperlukan selama Azure Storage dikonfigurasi dengan benar, lihat Mengonfigurasi akses baca anonim untuk kontainer dan blob.

Sumber data eksternal

Sumber data eksternal adalah abstraksi yang memungkinkan referensi lokasi file yang mudah di beberapa kueri. Untuk mengkueri lokasi publik, yang perlu Anda tentukan saat membuat sumber data eksternal adalah lokasi file:

CREATE EXTERNAL DATA SOURCE MyExternalDataSource
WITH (
    LOCATION = 'abs://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest'
);

Saat mengakses akun penyimpanan nonpublik, bersama dengan lokasi, Anda juga perlu mereferensikan kredensial cakupan database dengan parameter autentikasi yang dienkapsulasi. Skrip berikut membuat sumber data eksternal yang menunjuk ke jalur file, dan merujuk kredensial cakupan database.

--Create external data source pointing to the file path, and referencing database-scoped credential:
CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource
WITH (
    LOCATION = 'abs://<privatecontainer>@privatestorageaccount.blob.core.windows.net/dataset/' 
       CREDENTIAL = [MyCredential]
);

Mengkueri sumber data menggunakan OPENROWSET

Sintaks OPENROWSET memungkinkan kueri ad hoc instan sambil hanya membuat jumlah minimal objek database yang diperlukan.

OPENROWSEThanya memerlukan pembuatan sumber data eksternal (dan mungkin kredensial) dibandingkan dengan pendekatan tabel eksternal, yang memerlukan format file eksternal dan tabel eksternal itu sendiri.

Nilai parameter DATA_SOURCE secara otomatis ditambahkan sebelumnya ke parameter BULK untuk membentuk jalur penuh ke file.

Saat menggunakan OPENROWSET, berikan format file, seperti contoh berikut, yang mengkuerikan satu file:

SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'bing_covid-19_data.parquet',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Mengkueri beberapa file dan folder

Perintah OPENROWSET juga memungkinkan pengkuerian beberapa file atau folder dengan menggunakan wildcard di jalur BULK.

Contoh berikut menggunakan rekaman perjalanan taksi kuning NYC membuka himpunan data.

Pertama, buat sumber data eksternal:

--Create the data source first:
CREATE EXTERNAL DATA SOURCE NYCTaxiExternalDataSource
WITH (LOCATION = 'abs://nyctlc@azureopendatastorage.blob.core.windows.net');

Sekarang kita dapat mengkueri semua file dengan ekstensi .parquet di folder. Misalnya, di sini kita hanya akan mengkueri file-file yang cocok dengan pola nama:

--Query all files with .parquet extension in folders matching name pattern:
SELECT TOP 10 *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Saat mengkueri beberapa file atau folder, semua file yang diakses dengan satu OPENROWSET harus memiliki struktur yang sama (seperti jumlah kolom dan jenis data yang sama). Folder tidak dapat dilalui secara rekursif.

Inferensi skema

Inferensi skema otomatis membantu Anda menulis kueri dengan cepat dan menjelajahi data tanpa mengetahui skema file. Inferensi skema hanya berfungsi dengan file parket.

Meskipun nyaman, jenis data yang disimpulkan mungkin lebih besar dari jenis data aktual karena mungkin ada cukup informasi dalam file sumber untuk memastikan jenis data yang sesuai digunakan. Hal ini dapat menyebabkan kinerja kueri memburuk. Misalnya, file parquet tidak berisi metadata tentang panjang kolom karakter maksimum, sehingga instans menyimpulkannya sebagai varchar(8000).

Gunakan prosedur tersimpan sp_describe_first_results_set untuk memeriksa hasil jenis data kueri Anda, seperti contoh berikut:

EXEC sp_describe_first_result_set N'
 SELECT
 vendorID, tpepPickupDateTime, passengerCount
 FROM
 OPENROWSET(
  BULK ''yellow/*/*/*.parquet'',
  DATA_SOURCE = ''NYCTaxiExternalDataSource'',
  FORMAT=''parquet''
 ) AS nyc';

Setelah mengetahui jenis data, Anda kemudian dapat menentukannya menggunakan klausa WITH untuk meningkatkan performa:

SELECT TOP 100
 vendorID, tpepPickupDateTime, passengerCount
FROM
OPENROWSET(
 BULK 'yellow/*/*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT='PARQUET'
 )
WITH (
vendorID varchar(4), -- we're using length of 4 instead of the inferred 8000
tpepPickupDateTime datetime2,
passengerCount int
) AS nyc;

Karena skema file CSV tidak dapat ditentukan secara otomatis, kolom harus selalu ditentukan menggunakan WITH klausa:

SELECT TOP 10 id, updated, confirmed, confirmed_change
FROM OPENROWSET(
 BULK 'bing_covid-19_data.csv',
 DATA_SOURCE = 'MyExternalDataSource',
 FORMAT = 'CSV',
 FIRSTROW = 2
)
WITH (
 id int,
 updated date,
 confirmed int,
 confirmed_change int
) AS filerows;

Fungsi metadata file

Saat mengkueri beberapa file atau folder, Anda dapat menggunakan fungsi filepath() dan filename() untuk membaca metadata file dan mendapatkan bagian dari jalur atau jalur lengkap dan nama file yang merupakan asal baris dalam kumpulan hasil:

--Query all files and project file path and file name information for each row:
SELECT TOP 10 filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder],
filerows.filename() as [File_name], filerows.filepath() as [Full_Path], *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet') AS filerows;

Ketika dipanggil tanpa parameter, fungsi filepath() menampilkan jalur file yang merupakan asal dari baris tersebut. Ketika DATA_SOURCE digunakan dalam OPENROWSET, ia menampilkan jalur relatif terhadap DATA_SOURCE, atau mengembalikan jalur file lengkap.

Ketika dipicu dengan parameter, fungsi ini menampilkan bagian dari jalur yang cocok dengan wildcard pada posisi yang ditentukan dalam parameter. Misalnya, parameter value 1 akan menampilkan bagian dari jalur yang cocok dengan wildcard pertama.

Fungsi filepath() juga dapat digunakan untuk memfilter dan menggabungkan baris:

SELECT
 r.filepath() AS filepath
 ,r.filepath(1) AS [year]
 ,r.filepath(2) AS [month]
 ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
DATA_SOURCE = 'NYCTaxiExternalDataSource',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2017')
 AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
 r.filepath()
 ,r.filepath(1)
 ,r.filepath(2)
ORDER BY
 filepath;

Membuat tampilan di atas OPENROWSET

Anda dapat membuat dan menggunakan tampilan untuk membungkus kueri OPENROWSET, sehingga Anda dapat dengan mudah menggunakan kembali kueri yang mendasarinya:

CREATE VIEW TaxiRides AS
SELECT *
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Bisa juga menambahkan kolom yang berisi data lokasi file ke tampilan menggunakan fungsi filepath() untuk pemfilteran yang lebih mudah dan lebih efisien. Penggunaan tampilan dapat mengurangi jumlah file dan jumlah data yang perlu dibaca dan diproses oleh kueri di atas tampilan saat difilter oleh salah satu kolom tersebut:

CREATE VIEW TaxiRides AS
SELECT *
 , filerows.filepath(1) AS [year]
 , filerows.filepath(2) AS [month]
FROM OPENROWSET(
 BULK 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = 'NYCTaxiExternalDataSource',
 FORMAT = 'parquet'
) AS filerows;

Tampilan juga memungkinkan pelaporan dan alat analitik seperti Power BI untuk memakai hasil OPENROWSET.

Tabel eksternal

Tabel eksternal merangkum akses ke file yang menjadikan pengalaman kueri hampir identik dengan pengkuerian data relasional lokal yang disimpan dalam tabel pengguna. Pembuatan tabel eksternal memerlukan adanya sumber data eksternal dan objek format file eksternal:

--Create external file format
CREATE EXTERNAL FILE FORMAT DemoFileFormat
WITH (
 FORMAT_TYPE=PARQUET
);

--Create external table:
CREATE EXTERNAL TABLE tbl_TaxiRides(
 vendorID VARCHAR(100) COLLATE Latin1_General_BIN2,
 tpepPickupDateTime DATETIME2,
 tpepDropoffDateTime DATETIME2,
 passengerCount INT,
 tripDistance FLOAT,
 puLocationId VARCHAR(8000),
 doLocationId VARCHAR(8000),
 startLon FLOAT,
 startLat FLOAT,
 endLon FLOAT,
 endLat FLOAT,
 rateCodeId SMALLINT,
 storeAndFwdFlag VARCHAR(8000),
 paymentType VARCHAR(8000),
 fareAmount FLOAT,
 extra FLOAT,
 mtaTax FLOAT,
 improvementSurcharge VARCHAR(8000),
 tipAmount FLOAT,
 tollsAmount FLOAT,
 totalAmount FLOAT
)
WITH (
 LOCATION = 'yellow/puYear=*/puMonth=*/*.parquet',
 DATA_SOURCE = NYCTaxiExternalDataSource,
 FILE_FORMAT = DemoFileFormat
);

Setelah tabel eksternal dibuat, Anda dapat mengkuerikannya seperti tabel lainnya:

SELECT TOP 10 *
FROM tbl_TaxiRides;

Sama seperti OPENROWSET, tabel eksternal memungkinkan pengkuerian beberapa file dan folder menggunakan wildcard. Inferensi skema tidak didukung dengan tabel eksternal.

Pertimbangan performa

Tidak ada batasan keras untuk jumlah file atau jumlah data yang dapat dikueri, tetapi performa kueri tergantung pada jumlah data, format data, cara data diatur, dan kompleksitas kueri dan gabungan.

Kueri data yang telah dipartisi

Data sering diatur dalam subfolder yang juga disebut partisi. Anda dapat menginstruksikan kueri untuk membaca hanya folder dan file tertentu. Melakukannya mengurangi jumlah file dan jumlah data yang perlu dibaca dan diproses kueri, sehingga menghasilkan performa yang lebih baik. Jenis pengoptimalan kueri ini dikenal sebagai pemangkasan partisi atau eliminasi partisi. Anda dapat menghilangkan partisi dari eksekusi kueri dengan menggunakan fungsi filepath() metadata dalam WHERE klausa kueri.

Contoh kueri berikut membaca file data Taksi Kuning NYC hanya selama tiga bulan terakhir 2017:

SELECT
    r.filepath() AS filepath
    ,r.filepath(1) AS [year]
    ,r.filepath(2) AS [month]
    ,COUNT_BIG(*) AS [rows]
FROM OPENROWSET(
        BULK 'yellow/puYear=*/puMonth=*/*.parquet',
        DATA_SOURCE = 'NYCTaxiExternalDataSource',
        FORMAT = 'parquet'
    )
WITH (
    vendorID INT
) AS [r]
WHERE
    r.filepath(1) IN ('2017')
    AND r.filepath(2) IN ('10', '11', '12')
GROUP BY
    r.filepath()
    ,r.filepath(1)
    ,r.filepath(2)
ORDER BY
    filepath;

Jika data tersimpan Anda tidak dipartisi, pertimbangkan untuk mempartisinya untuk meningkatkan performa kueri.

Jika Anda menggunakan tabel eksternal, filepath() dan filename() fungsi didukung tetapi tidak dalam WHERE klausa.

Pemecahan Masalah

Masalah dengan eksekusi kueri biasanya disebabkan oleh Azure SQL Database tidak dapat mengakses lokasi file. Pesan kesalahan terkait mungkin melaporkan hak akses yang tidak mencukupi, lokasi atau jalur file yang tidak ada, file yang digunakan oleh proses lain, atau direktori tersebut tidak dapat dicantumkan. Dalam sebagian besar kasus, masalah ini menunjukkan bahwa akses ke file diblokir oleh kebijakan kontrol lalu lintas jaringan atau karena kurangnya hak akses. Hal inilah yang harus diperiksa:

  • Jalur lokasi yang salah atau salah ketik.
  • Validitas kunci SAS: dapat kedaluwarsa, berisi kesalahan ketik, dimulai dengan tanda tanya.
  • Izin kunci SAS diizinkan: Baca minimal, dan Daftar apakah wildcard digunakan.
  • Lalu lintas masuk yang diblokir pada akun penyimpanan. Periksa Mengelola aturan jaringan virtual untuk Azure Storage.
  • Hak akses Identitas Terkelola: pastikan identitas terkelola Azure SQL Database diberikan hak akses ke akun penyimpanan.
  • Tingkat kompatibilitas database harus 130 atau lebih tinggi agar kueri virtualisasi data berfungsi.

Keterbatasan

  • Saat ini, statistik pada tabel eksternal tidak didukung di Azure SQL Database.
  • Saat ini, CREATE EXTERNAL TABLE AS SELECT tidak tersedia di Azure SQL Database.
  • Fitur keamanan tingkat baris tidak didukung dengan tabel eksternal.
  • Aturan masking data dinamis tidak dapat ditentukan untuk kolom dalam tabel eksternal.
  • Identitas Terkelola tidak mendukung skenario lintas penyewa, jika Akun Azure Storage Anda berada di penyewa yang berbeda, Tanda tangan akses bersama adalah metode yang didukung.

Masalah yang diketahui

Konten terkait

  • Last updated on