Mengubah file data dengan pernyataan CREATE EXTERNAL TABLE AS SELECT

5 menit

Bahasa komputer SQL mencakup banyak fitur dan fungsi yang memungkinkan Anda memanipulasi data. Misalnya, Anda dapat menggunakan SQL untuk:

Memfilter baris dan kolom dalam himpunan data.
Mengganti nama bidang data dan konversi antara tipe data.
Menghitung bidang data turunan.
Memanipulasi nilai string.
Mengelompokkan dan mengagregasi data.

Kumpulan SQL tanpa server Azure Synapse dapat digunakan untuk menjalankan pernyataan SQL yang mengubah data dan mempertahankan hasilnya sebagai file di data lake untuk pemrosesan atau kueri lebih lanjut. Jika terbiasa dengan sintaks Transact-SQL, Anda dapat membuat pernyataan SELECT yang menerapkan transformasi tertentu yang diminati, dan menyimpan hasil pernyataan SELECT dalam format file terpilih dengan skema tabel metadata yang dapat dikueri menggunakan SQL.

Anda dapat menggunakan pernyataan CREATE EXTERNAL TABLE AS SELECT (CETAS) di kumpulan SQL khusus atau kumpulan SQL tanpa server untuk mempertahankan hasil kueri dalam tabel eksternal, yang menyimpan datanya dalam file di data lake.

Pernyataan CETAS menyertakan pernyataan SELECT yang mengkueri dan memanipulasi data dari sumber data yang valid (yang dapat berupa tabel atau tampilan yang ada dalam database, atau fungsi OPENROWSET yang membaca data berbasis file dari data lake). Hasil pernyataan SELECT kemudian disimpan dalam tabel eksternal, yang merupakan objek metadata dalam database yang menyediakan abstraksi relasional atas data yang disimpan dalam file. Diagram berikut menggambarkan konsep ini secara visual:

Diagram yang menunjukkan pernyataan CREATE EXTERNAL TABLE AS SELECT yang menyimpan hasil kueri sebagai file.

Dengan menerapkan teknik ini, Anda dapat menggunakan SQL untuk mengekstrak dan mengubah data dari file atau tabel, dan menyimpan hasil yang diubah untuk pemrosesan atau analisis hilir. Operasi selanjutnya pada data yang diubah dapat dilakukan terhadap tabel relasional dalam database kumpulan SQL atau langsung terhadap file data yang mendasarinya.

Membuat objek database eksternal untuk mendukung CETAS

Untuk menggunakan ekspresi CETAS, Anda harus membuat jenis objek berikut dalam database untuk kumpulan SQL tanpa server atau khusus. Saat menggunakan kumpulan SQL tanpa server, buat objek ini dalam database kustom (dibuat menggunakan pernyataan CREATE DATABASE), bukan database bawaan.

Sumber data eksternal

Sumber data eksternal merangkum koneksi ke lokasi sistem file di data lake. Anda kemudian dapat menggunakan koneksi ini untuk menentukan jalur relatif tempat file data untuk tabel eksternal yang dibuat oleh pernyataan CETAS akan disimpan.

Jika data sumber untuk pernyataan CETAS berada dalam file di jalur data lake yang sama, Anda dapat menggunakan sumber data eksternal yang sama dalam fungsi OPENROWSET yang digunakan untuk mengkuerinya. Atau, Anda dapat membuat sumber data eksternal terpisah untuk file sumber atau menggunakan jalur file yang sepenuhnya memenuhi syarat dalam fungsi OPENROWSET.

Untuk membuat sumber data eksternal, gunakan pernyataan CREATE EXTERNAL DATA SOURCE, seperti yang ditunjukkan dalam contoh ini:

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

Contoh sebelumnya mengasumsikan bahwa pengguna yang menjalankan kueri yang menggunakan sumber data eksternal akan memiliki izin yang memadai untuk mengakses file. Pendekatan alternatif berguna untuk merangkum informasi masuk di sumber data eksternal sehingga dapat digunakan untuk mengakses data file tanpa memberikan izin kepada semua pengguna untuk membacanya secara langsung:

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

Petunjuk / Saran

Selain autentikasi SAS, Anda dapat menentukan kredensial yang menggunakan identitas terkelola (identitas Microsoft Entra yang digunakan oleh ruang kerja Azure Synapse Anda), perwakilan Microsoft Entra tertentu, atau autentikasi passthrough berdasarkan identitas pengguna yang menjalankan kueri (yang merupakan jenis autentikasi default). Untuk mempelajari selengkapnya tentang menggunakan kredensial di kumpulan SQL tanpa server, lihat artikel Mengontrol akses akun penyimpanan untuk kumpulan SQL tanpa server di Azure Synapse Analytics dalam dokumentasi Azure Synapse Analytics.

Format file eksternal

Pernyataan CETAS membuat tabel dengan datanya yang disimpan dalam file. Anda harus menentukan format file yang ingin dibuat sebagai format file eksternal.

Untuk membuat format file eksternal, gunakan pernyataan CREATE EXTERNAL FILE FORMAT, seperti yang ditunjukkan dalam contoh ini:

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

Petunjuk / Saran

Dalam contoh ini, file akan disimpan dalam format Parquet. Anda juga dapat membuat format file eksternal untuk jenis file lainnya. Lihat MEMBUAT FORMAT FILE EKSTERNAL (Transact-SQL) untuk selengkapnya.

Menggunakan pernyataan CETAS

Setelah membuat sumber data eksternal dan format file eksternal, Anda dapat menggunakan pernyataan CETAS untuk mengubah data dan menyimpan hasilnya dalam tabel eksternal.

Misalnya, katakanlah data sumber yang ingin Anda ubah terdiri dari pesanan penjualan dalam file teks yang dipisahkan koma yang disimpan dalam folder di data lake. Anda ingin memfilter data untuk hanya memuat pesanan yang ditandai sebagai "pesanan khusus", dan menyimpan data yang diubah sebagai file Parquet di folder berbeda dalam data lake yang sama. Anda dapat menggunakan sumber data eksternal yang sama untuk folder sumber dan tujuan seperti yang ditunjukkan dalam contoh ini:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Parameter LOCATION dan BULK dalam contoh sebelumnya adalah jalur relatif untuk masing-masing hasil dan file sumber masing-masing. Jalur tersebut bersifat relatif terhadap lokasi sistem file yang dirujuk oleh sumber data eksternal file.

Poin penting yang perlu dipahami adalah Anda harus menggunakan sumber data eksternal untuk menentukan lokasi penyimpanan data yang diubah untuk tabel eksternal. Saat data sumber berbasis file disimpan dalam hierarki folder yang sama, Anda dapat menggunakan sumber data eksternal yang sama. Jika tidak, Anda dapat menggunakan sumber data kedua untuk menentukan koneksi ke data sumber atau menggunakan jalur yang sepenuhnya memenuhi syarat, seperti yang ditunjukkan dalam contoh ini:

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

Menghapus tabel eksternal

Jika Anda tidak lagi memerlukan tabel eksternal yang berisi data yang ditransformasi, Anda bisa menghilangkannya dari database dengan menggunakan pernyataan , seperti yang diperlihatkan DROP EXTERNAL TABLE di sini:

DROP EXTERNAL TABLE SpecialOrders;

Namun, penting untuk dipahami bahwa tabel eksternal adalah abstraksi metadata atas file yang berisi data aktual. Menghapus tabel eksternal tidak akan menghapus file yang mendasarinya.

Saran dan Komentar

Apakah halaman ini membantu?