Mulai menggunakan PolyBase di SQL Server 2022
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:
- Instal PolyBase di Windows atau instal PolyBase di Linux.
- Aktifkan PolyBase di sp_configure jika perlu.
- Izinkan akses jaringan eksternal untuk mengakses penyimpanan Azure Blob yang tersedia untuk umum di
pandemicdatalake.blob.core.windows.net
danazureopendatastorage.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 WHERE
data 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.
OPENROWSET
hanya 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. KetikaDATA_SOURCE
digunakan dalamOPENROWSET
, ia menampilkan jalur relatif terhadapDATA_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:
- Hadoop
- Penyimpanan Blob Azure
- SQL Server
- Oracle
- Teradata
- MongoDB
- Jenis Generik ODBC
- Penyimpanan objek yang kompatibel dengan S3
- CSV
- Tabel Delta