Menyerap data dengan metode SQL

7 menit

Perintah SQL menyediakan pendekatan deklaratif untuk penyerapan data di Azure Databricks. Jika Anda sudah nyaman dengan sintaks SQL, metode ini memungkinkan Anda membuat dan mengisi tabel tanpa menulis kode prosedural. Tiga teknik penyerapan SQL utama—CREATE TABLE AS SELECT (CTAS), CREATE OR REPLACE TABLE, dan COPY INTO—masing-masing membahas skenario penyerapan yang berbeda sambil mempertahankan kompatibilitas penuh dengan Katalog Unity.

Membuat tabel dari kueri dengan CTAS

Pernyataan ini CREATE TABLE AS SELECT menggabungkan pembuatan tabel dan populasi data ke dalam satu operasi. Anda menentukan tabel baru berdasarkan hasil SELECT kueri, membuatnya ideal untuk mengubah data selama penyerapan.

Pertimbangkan skenario di mana Anda perlu menyerap data pelanggan dari sumber eksternal dan menerapkan transformasi. Dengan CTAS, Anda menulis kueri yang membaca dari sumber, menerapkan transformasi Anda, dan menyimpan hasilnya ke tabel terkelola baru:

CREATE TABLE catalog.schema.customers AS
SELECT 
    customer_id,
    UPPER(customer_name) AS customer_name,
    email,
    created_date
FROM external_staging.raw_customers
WHERE customer_status = 'active';

Skema tabel secara otomatis berasal dari hasil kueri. Azure Databricks membuat tabel menggunakan format Delta secara default, yang menyediakan transaksi ACID, perjalanan waktu, dan performa yang dioptimalkan.

CTAS berfungsi dengan baik untuk pemuatan data awal dan migrasi satu kali. Saat Anda perlu membaca data dari file, gabungkan CTAS dengan fungsi tabel bernilai read_files.

CREATE TABLE catalog.schema.sales_data AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/sales/*.parquet',
    format => 'parquet'
);

Nota

CTAS membuat tabel baru setiap kali dijalankan. Jika tabel sudah ada, perintah gagal kecuali Anda menggunakan IF NOT EXISTS klausul —tetapi klausul tersebut melompati eksekusi sepenuhnya daripada memperbarui tabel.

Perbarui tabel dengan CREATE OR REPLACE TABLE

Saat Anda perlu me-refresh konten tabel sepenuhnya, CREATE OR REPLACE TABLE berikan solusi yang bersih. Perintah ini membuat tabel baru atau sepenuhnya menggantikan tabel yang sudah ada, mempertahankan riwayat tabel, memberikan hak istimewa, dan filter baris atau masker kolom apa pun yang telah Anda konfigurasi.

Pendekatan ini terbukti berharga untuk refresh data berkala di mana Anda ingin mengganti semua data yang ada:

CREATE OR REPLACE TABLE catalog.schema.daily_metrics AS
SELECT 
    report_date,
    SUM(revenue) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM catalog.schema.transactions
WHERE report_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP BY report_date;

Tidak seperti menghilangkan dan membuat ulang tabel, CREATE OR REPLACE mempertahankan metadata dan izin tabel. Pengguna dan aplikasi hilir terus mengakses tabel tanpa konfigurasi ulang.

Anda juga dapat menggunakan perintah ini untuk memuat data langsung dari file. Skema secara otomatis disimpulkan dari hasil kueri:

CREATE OR REPLACE TABLE catalog.schema.products AS
SELECT * FROM read_files(
    '/Volumes/catalog/schema/volume/products.csv',
    format => 'csv',
    header => true
);

Penting

CREATE OR REPLACE melakukan penggantian tabel secara menyeluruh. Untuk pembaruan inkremental di mana Anda hanya ingin menambahkan rekaman baru, gunakan COPY INTO sebagai gantinya.

Muat file secara bertahap dengan COPY INTO

COPY INTO mengatasi tantangan penyerapan umum: memuat file dari penyimpanan cloud dengan cara yang andal dan dapat diulang. Tidak seperti CTAS, yang berjalan sekali dan membuat tabel, COPY INTO dirancang untuk alur kerja penyerapan yang sedang berlangsung di mana file baru tiba secara teratur.

Perintah membaca file dari lokasi tertentu dan menambahkannya ke tabel Delta yang ada. Fitur utamanya adalah idempotensi—file yang telah dimuat secara otomatis diabaikan, bahkan pada beberapa eksekusi.

COPY INTO catalog.schema.events
FROM '/Volumes/catalog/schema/volume/events/'
FILEFORMAT = JSON
FORMAT_OPTIONS ('multiline' = 'true');

Sebelum menjalankan COPY INTO, tabel target harus sudah ada. Buat dengan skema yang sesuai:

CREATE TABLE IF NOT EXISTS catalog.schema.events (
    event_id STRING,
    event_type STRING,
    event_timestamp TIMESTAMP,
    payload STRING
);

Mengonfigurasi pilihan file

Saat direktori sumber Anda berisi file dengan pola penamaan yang berbeda atau Anda perlu memuat file tertentu, gunakan PATTERN opsi atau FILES :

-- Load only files matching a pattern
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
PATTERN = 'orders_2024*.parquet';

-- Load specific files by name
COPY INTO catalog.schema.orders
FROM '/Volumes/catalog/schema/volume/orders/'
FILEFORMAT = PARQUET
FILES = ('orders_001.parquet', 'orders_002.parquet');

Menangani skema dan kualitas data

COPY INTO menyediakan opsi untuk menangani perubahan skema dan memvalidasi data sebelum memuat:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
FORMAT_OPTIONS (
    'header' = 'true',
    'inferSchema' = 'true'
)
COPY_OPTIONS ('mergeSchema' = 'true');

Opsi ini mergeSchema memungkinkan skema tabel berkembang saat kolom baru muncul di file sumber. Untuk memvalidasi data tanpa memuatnya, tambahkan klausa VALIDATE:

COPY INTO catalog.schema.sensor_data
FROM '/Volumes/catalog/schema/volume/sensors/'
FILEFORMAT = CSV
VALIDATE ALL;

Validasi ini memeriksa apakah data dapat diuraikan, sesuai dengan skema tabel, dan memenuhi sifat nullability serta batasan pemeriksaan.

Pilih metode yang tepat

Setiap metode penyerapan SQL melayani tujuan yang berbeda dalam alur kerja rekayasa data Anda:

Metode	Paling cocok untuk	Perilaku
CTAS	Pemuatan data awal, migrasi satu kali, membuat tabel dari kueri	Membuat tabel baru; gagal jika tabel ada
MEMBUAT ATAU MENGGANTI	Refresh penuh berkala, mengganti tabel tahap	Mengganti seluruh tabel sambil mempertahankan izin
SALIN KE DALAM	Penyerapan file yang sedang berlangsung, beban inkremental	Menambahkan ke tabel yang sudah ada; melewati file yang sudah dimuat

Untuk pemrosesan berbasis file yang memerlukan inferensi skema otomatis, pemberitahuan file, atau jaminan tepat satu kali, pertimbangkan untuk menggunakan Auto Loader sebagai pendekatan pelengkap. Ketika kebutuhan penyerapan Anda mudah dan Anda lebih suka SQL deklaratif daripada kode prosedural, ketiga metode ini menyediakan toolkit lengkap untuk mengelola aliran data ke Unity Catalog.

Nota

COPY INTO bekerja dengan baik saat memproses file yang berjumlah ribuan seiring waktu. Untuk sumber yang akan tumbuh hingga jutaan file atau lebih, Auto Loader (tercakup dalam unit berikutnya) adalah pendekatan yang direkomendasikan—menemukan file lebih efisien, mendukung evolusi skema yang lebih kaya, dan menskalakan tanpa overhead daftar direktori. Databricks juga merekomendasikan tabel streaming yang didukung oleh Auto Loader sebagai alternatif jangka panjang yang dapat diskalakan untuk penyerapan file berbasis SQL.

Saran dan Komentar

Apakah halaman ini membantu?