Virtualisasi file CSV 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 file CSV. 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, file CSV 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 CSV_Demo digunakan.

CREATE DATABASE [CSV_Demo];

3. Buat kunci master dan kredensial lingkup database

Kunci master database dalam database pengguna diperlukan untuk mengenkripsi rahasia kredensial cakupan database, blob_storage.

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

CREATE DATABASE SCOPED CREDENTIAL blob_storage
WITH IDENTITY = '<user_name>', Secret = '<password>';

4. Buat sumber data eksternal

Kredensial lingkup database digunakan untuk sumber data eksternal. Dalam contoh ini, file CSV berada di Azure Blob Storage, jadi gunakan awalan absSHARED 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 Blob_CSV
WITH
(
 LOCATION = 'abs://<container>@<storage_account>.blob.core.windows.net'
,CREDENTIAL = blob_storage
);

Misalnya, jika akun penyimpanan Anda diberi nama s3sampledata dan kontainer diberi nama import, kodenya adalah:

CREATE EXTERNAL DATA SOURCE Blob_CSV
WITH
(
 LOCATION = 'abs://import@s3sampledata.blob.core.windows.net'
,CREDENTIAL = blob_storage
)

Menggunakan OPENROWSET untuk mengakses data

Dalam contoh ini, file diberi nama call_center.csv, dan data dimulai pada baris kedua.

Karena sumber Blob_CSV data eksternal dipetakan ke tingkat kontainer. call_center.csv terletak di subfolder yang disebut 2022 di akar kontainer. Untuk mengkueri file dalam struktur folder, berikan pemetaan folder relatif terhadap parameter LOCATION sumber data eksternal.

SELECT * FROM OPENROWSET
(
    BULK '/2022/call_center.csv',
    FORMAT = 'CSV',
    DATA_SOURCE = 'Blob_CSV',
    FIRSTROW = 2
)
WITH (
    cc_call_center_sk INT,
    cc_call_center_id CHAR(16),
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(40),
    cc_division INT,
    cc_division_name VARCHAR(50),
    cc_company INT,
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(2),
    cc_zip CHAR(10),
    cc_country VARCHAR(20),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
) AS [cc];

Mengkueri data dengan tabel eksternal

CREATE EXTERNAL TABLE juga dapat digunakan untuk memvirtualisasi data CSV 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.

Dalam contoh berikut, data dimulai pada baris kedua.

CREATE EXTERNAL FILE FORMAT csv_ff
WITH (
    FORMAT_TYPE = DELIMITEDTEXT,
    FORMAT_OPTIONS(
        FIELD_TERMINATOR = ',',
        STRING_DELIMITER = '"',
        FIRST_ROW = 2)
);

2. Buat tabel eksternal

LOCATION adalah folder dan jalur call_center.csv file file relatif terhadap jalur lokasi di sumber data eksternal, yang ditentukan oleh DATA_SOURCE. Dalam hal ini, file terletak di subfolder yang disebut 2022. Gunakan FILE_FORMAT untuk menentukan jalur ke csv_ff format file eksternal di SQL Server.

CREATE EXTERNAL TABLE extCall_Center_csv (
    cc_call_center_sk INT NOT NULL,
    cc_call_center_id CHAR(16) NOT NULL,
    cc_rec_start_date DATE,
    cc_rec_end_date DATE,
    cc_closed_date_sk INT,
    cc_open_date_sk INT,
    cc_name VARCHAR(50),
    cc_class VARCHAR(50),
    cc_employees INT,
    cc_sq_ft INT,
    cc_hours CHAR(20),
    cc_manager VARCHAR(40),
    cc_mkt_id INT,
    cc_mkt_class CHAR(50),
    cc_mkt_desc VARCHAR(100),
    cc_market_manager VARCHAR(MAX),
    cc_division VARCHAR(50),
    cc_division_name VARCHAR(50),
    cc_company VARCHAR(60),
    cc_company_name CHAR(50),
    cc_street_number CHAR(10),
    cc_street_name VARCHAR(60),
    cc_street_type CHAR(15),
    cc_suite_number CHAR(10),
    cc_city VARCHAR(60),
    cc_county VARCHAR(30),
    cc_state CHAR(20),
    cc_zip CHAR(20),
    cc_country VARCHAR(MAX),
    cc_gmt_offset DECIMAL(5, 2),
    cc_tax_percentage DECIMAL(5, 2)
    )
WITH (
    LOCATION = '/2022/call_center.csv',
    DATA_SOURCE = Blob_CSV,
    FILE_FORMAT = csv_ff
);
GO

Konten terkait