Mulai menggunakan PolyBase di SQL Server 2022

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) - Windows dan versi yang lebih baru SQL Server 2017 (14.x) - Linux dan versi yang lebih baru

Artikel ini memandu Anda melalui tutorial bekerja dengan beberapa folder dan file dengan PolyBase di SQL Server 2022 (16.x). Serangkaian kueri tutorial ini menunjukkan berbagai fitur PolyBase.

Virtualisasi data dengan PolyBase di SQL Server memungkinkan Anda memanfaatkan fungsi file metadata untuk mengkueri beberapa folder, file, atau melakukan eliminasi folder. Kombinasi penemuan skema dengan folder dan penghapusan file adalah kemampuan canggih yang memungkinkan SQL mengambil hanya data yang diperlukan dari Akun Azure Storage atau solusi penyimpanan objek yang kompatibel dengan S3.

Prasyarat

Sebelum menggunakan PolyBase dalam tutorial ini, Anda harus:

  1. Instal PolyBase di Windows atau instal PolyBase di Linux.
  2. Aktifkan PolyBase di sp_configure jika perlu.
  3. Izinkan akses jaringan eksternal untuk mengakses penyimpanan Azure Blob yang tersedia untuk umum di pandemicdatalake.blob.core.windows.net dan azureopendatastorage.blob.core.windows.net.

Sampel himpunan data

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:

  • Parquet: 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:

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 T-SQL lainnya berdasarkan kumpulan hasil kueri pertama.

Jika kueri pertama gagal pada instans SQL Server Anda, akses jaringan kemungkinan dicegah ke akun penyimpanan Azure publik. Bicaralah dengan pakar jaringan Anda untuk mengaktifkan akses sebelum Anda dapat melanjutkan kueri.

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.

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

Catatan

Jika Anda menerima pesan kesalahan 46530, External data sources are not supported with type GENERIC, periksa opsi PolyBase Enabled konfigurasi di instans SQL Server Anda. Properti tersebut seharusnya 1.

Jalankan hal berikut untuk mengaktifkan PolyBase di instans SQL Server Anda:

EXEC sp_configure @configname = 'polybase enabled', @configvalue = 1;
RECONFIGURE;

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://public@pandemicdatalake.blob.core.windows.net/curated/covid-19/bing_covid-19_data/latest' 
        CREDENTIAL = [MyCredential]);

Sumber data kueri 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 himpunan data terbuka rekaman perjalanan taksi kuning NYC:

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:

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 parket tidak berisi metadata tentang panjang kolom karakter maksimum, sehingga instans menyimpulkannya sebagai varchar(8000).

sys.sp_describe_first_results_set Gunakan prosedur tersimpan untuk memeriksa tipe data kueri Anda yang dihasilkan, 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 filepath() fungsi dan filename() untuk membaca metadata file dan mendapatkan bagian dari jalur atau jalur lengkap dan nama file tempat baris dalam kumpulan hasil berasal. Dalam contoh berikut, kueri semua file dan jalur file proyek dan informasi nama file untuk setiap baris:

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

--List all paths: 
SELECT DISTINCT filerows.filepath(1) as [Year_Folder], filerows.filepath(2) as [Month_Folder] 
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 dipanggil dengan parameter, filepath() fungsi mengembalikan bagian dari jalur yang cocok dengan kartubebas pada posisi yang ditentukan dalam parameter . Misalnya, nilai parameter pertama akan mengembalikan 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 bisa membuat tampilan untuk membungkus OPENROWSET kueri sehingga Anda dapat dengan mudah menggunakan kembali kueri yang mendasarinya. Tampilan juga memungkinkan alat pelaporan dan analitik seperti Power BI untuk menggunakan hasil OPENROWSET.

Misalnya, pertimbangkan tampilan berikut berdasarkan OPENROWSET perintah:

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;

Tabel eksternal

Tabel eksternal merangkum akses ke file yang membuat pengalaman kueri hampir identik dengan kueri 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 
) 
GO 
 
--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 kueri beberapa file dan folder dengan menggunakan wildcard. Inferensi skema tidak didukung dengan tabel eksternal.

Sumber data eksternal

Untuk tutorial selengkapnya tentang membuat sumber data eksternal dan tabel eksternal ke berbagai sumber data, lihat referensi PolyBase Transact-SQL.

Untuk tutorial selengkapnya tentang berbagai sumber data eksternal, tinjau:

Konten terkait