Referensi bahasa DLT SQL
Artikel ini memiliki detail untuk antarmuka pemrograman DLT SQL.
- Untuk informasi tentang Python API, lihat referensi bahasa DLT Python.
- Untuk informasi selengkapnya tentang perintah SQL, lihat referensi bahasa SQL.
Anda dapat menggunakan fungsi yang ditentukan pengguna (UDF) Python dalam kueri SQL Anda, tetapi Anda harus menentukan UDF ini dalam file Python sebelum memanggilnya dalam file sumber SQL. Lihat Fungsi skalar yang ditentukan pengguna - Python.
Keterbatasan
Klausa PIVOT
tidak didukung. Operasi pivot
di Spark memerlukan pemuatan awal data input untuk menghitung skema output. Kemampuan ini tidak didukung di DLT.
Membuat tampilan terwujud DLT atau tabel streaming
Nota
Sintaks CREATE OR REFRESH LIVE TABLE
untuk membuat tampilan materialisasi tidak digunakan lagi. Sebagai gantinya, gunakan CREATE OR REFRESH MATERIALIZED VIEW
.
Anda menggunakan sintaks SQL dasar yang sama saat mendeklarasikan tabel streaming atau tampilan materialisasi.
Deklarasikan tampilan materialisasi DLT dengan SQL
Berikut ini menguraikan sintaks untuk mendeklarasikan tampilan materialisasi di DLT dengan SQL:
CREATE OR REFRESH MATERIALIZED VIEW view_name [CLUSTER BY (col_name1, col_name2, ... )]
[(
[
col_name1 col_type1 [ GENERATED ALWAYS AS generation_expression1 ] [ COMMENT col_comment1 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
col_name2 col_type2 [ GENERATED ALWAYS AS generation_expression2 ] [ COMMENT col_comment2 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
...
]
[
CONSTRAINT expectation_name_1 EXPECT (expectation_expr1) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
CONSTRAINT expectation_name_2 EXPECT (expectation_expr2) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
...
]
[ table_constraint ] [, ...]
)]
[USING DELTA]
[PARTITIONED BY (col_name1, col_name2, ... )]
CLUSTER BY clause
[LOCATION path]
[COMMENT table_comment]
[TBLPROPERTIES (key1 [ = ] val1, key2 [ = ] val2, ... )]
[ WITH { ROW FILTER func_name ON ( [ column_name | constant_literal [, ...] ] ) [...] } ]
AS select_statement
Mendeklarasikan tabel streaming DLT dengan SQL
Anda hanya dapat mendeklarasikan tabel streaming dengan menggunakan kueri yang mengakses sumber streaming. Databricks merekomendasikan penggunaan Auto Loader untuk pemasukan data secara streaming dari penyimpanan objek cloud. Lihat sintaks SQL Auto Loader.
Saat menentukan tabel atau tampilan lain di alur Anda sebagai sumber streaming, Anda harus menyertakan fungsi STREAM()
di sekitar nama himpunan data.
Berikut ini menguraikan sintaks untuk mendeklarasikan tabel Streaming di DLT dengan SQL:
CREATE OR REFRESH [TEMPORARY] STREAMING TABLE table_name [CLUSTER BY (col_name1, col_name2, ... )]
[(
[
col_name1 col_type1 [ GENERATED ALWAYS AS generation_expression1 ] [ COMMENT col_comment1 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
col_name2 col_type2 [ GENERATED ALWAYS AS generation_expression2 ] [ COMMENT col_comment2 ] [ column_constraint ] [ MASK func_name [ USING COLUMNS ( other_column_name | constant_literal [, ...] ) ] ],
...
]
[
CONSTRAINT expectation_name_1 EXPECT (expectation_expr1) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
CONSTRAINT expectation_name_2 EXPECT (expectation_expr2) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
...
]
[ table_constraint ] [, ...]
)]
[USING DELTA]
[PARTITIONED BY (col_name1, col_name2, ... )]
[CLUSTER BY clause]
[LOCATION path]
[COMMENT table_comment]
[TBLPROPERTIES (key1 [ = ] val1, key2 [ = ] val2, ... )]
[ WITH { ROW FILTER func_name ON ( [ column_name | constant_literal [, ...] ] ) [...] } ]
AS select_statement
Membuat tampilan DLT
Berikut ini menguraikan sintaks untuk mendeklarasikan tampilan dengan SQL:
CREATE TEMPORARY [STREAMING] LIVE VIEW view_name
[(
[
col_name1 [ COMMENT col_comment1 ],
col_name2 [ COMMENT col_comment2 ],
...
]
[
CONSTRAINT expectation_name_1 EXPECT (expectation_expr1) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
CONSTRAINT expectation_name_2 EXPECT (expectation_expr2) [ON VIOLATION { FAIL UPDATE | DROP ROW }],
...
]
)]
[COMMENT view_comment]
AS select_statement
Auto Loader SQL sintaks
Berikut ini menguraikan sintaks untuk bekerja dengan Auto Loader di SQL:
CREATE OR REFRESH STREAMING TABLE table_name
AS SELECT *
FROM read_files(
"<file-path>",
"<file-format>",
map(
"<option-key>", "<option_value>",
"<option-key>", "<option_value>",
...
)
)
Anda dapat menggunakan opsi format yang didukung dengan Auto Loader. Dengan menggunakan fungsi map()
, Anda dapat meneruskan opsi ke metode read_files()
. Opsi adalah pasangan kunci-nilai, di mana kunci dan nilai adalah string. Untuk detail tentang format dan opsi dukungan, lihat opsi format file .
Contoh: Menentukan tabel
Anda dapat membuat himpunan data dengan membaca dari sumber data eksternal atau dari himpunan data yang ditentukan dalam alur. Untuk membaca dari himpunan data internal, tentukan nama tabel yang akan menggunakan default alur yang dikonfigurasi untuk katalog dan skema. Contoh berikut mendefinisikan dua himpunan data yang berbeda: tabel yang disebut taxi_raw
yang mengambil file JSON sebagai sumber input dan tabel yang disebut filtered_data
yang mengambil tabel taxi_raw
sebagai input:
CREATE OR REFRESH MATERIALIZED VIEW taxi_raw
AS SELECT * FROM json.`/databricks-datasets/nyctaxi/sample/json/`
CREATE OR REFRESH MATERIALIZED VIEW filtered_data
AS SELECT
...
FROM taxi_raw
Contoh: Membaca dari sumber streaming
Untuk membaca data dari sumber streaming, misalnya, Auto Loader atau himpunan data internal, tentukan tabel STREAMING
:
CREATE OR REFRESH STREAMING TABLE customers_bronze
AS SELECT * FROM read_files("/databricks-datasets/retail-org/customers/", "csv")
CREATE OR REFRESH STREAMING TABLE customers_silver
AS SELECT * FROM STREAM(customers_bronze)
Untuk informasi selengkapnya tentang data streaming, lihat Mengubah data dengan alur.
Menghapus rekaman secara permanen dari tampilan terwujud atau tabel streaming
Untuk menghapus rekaman secara permanen dari tampilan materialisasi atau tabel streaming dengan vektor penghapusan diaktifkan, seperti untuk kepatuhan GDPR, operasi tambahan harus dilakukan pada tabel Delta dasar objek. Untuk memastikan penghapusan rekaman dari tampilan materialisasi, lihat Menghapus rekaman secara permanen dari tampilan materialisasi dengan vektor penghapusan diaktifkan. Untuk memastikan penghapusan rekaman dari tabel streaming, lihat Menghapus rekaman secara permanen dari tabel streaming.
Mengontrol bagaimana tabel diwujudkan
Tabel juga menawarkan kontrol tambahan atas materialisasinya:
- Tentukan cara untuk mengelompokkan tabel menggunakan
CLUSTER BY
. Anda dapat menggunakan pengelompokan cair untuk mempercepat pencarian. Lihat Menggunakan pengklusteran cair untuk tabel Delta. - Tentukan bagaimana tabel dipartisi menggunakan
PARTITIONED BY
. - Anda dapat mengatur properti tabel menggunakan
TBLPROPERTIES
. Lihat properti tabel DLT. - Atur lokasi penyimpanan menggunakan pengaturan
LOCATION
. Secara default, data tabel disimpan di lokasi penyimpanan alur jikaLOCATION
tidak diatur. - Anda dapat menggunakan kolom yang dihasilkan dalam definisi skema Anda. Lihat Contoh : Tentukan skema dan kolom kluster.
Nota
Untuk tabel berukuran kurang dari 1 TB, Databricks merekomendasikan untuk mengizinkan DLT mengontrol organisasi data. Kecuali Anda mengharapkan tabel Anda tumbuh melebihi satu terabyte, Databricks menyarankan agar Anda tidak menentukan kolom partisi.
Contoh : Tentukan skema dan kolom kluster
Anda dapat secara opsional menentukan skema saat menentukan tabel. Contoh berikut menentukan skema untuk tabel target, termasuk menggunakan kolom yang dihasilkan delta Lake dan menentukan kolom pengklusteran untuk tabel:
CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING,
customer_name STRING,
number_of_line_items STRING,
order_datetime STRING,
order_number LONG,
order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
) CLUSTER BY (order_day_of_week, customer_id)
COMMENT "Raw data on sales"
AS SELECT * FROM ...
Secara default, DLT menyimpulkan skema dari definisi table
jika Anda tidak menentukan skema.
Contoh : Tentukan kolom partisi
Anda dapat secara opsional menentukan kolom partisi untuk tabel:
CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING,
customer_name STRING,
number_of_line_items STRING,
order_datetime STRING,
order_number LONG,
order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
) PARTITIONED BY (order_day_of_week)
COMMENT "Raw data on sales"
AS SELECT * FROM ...
Pengklusteran cairan memberikan solusi yang fleksibel dan dioptimalkan untuk pengklusteran. Pertimbangkan untuk menggunakan CLUSTER BY
alih-alih PARTITIONED BY
untuk DLT.
Contoh : Menentukan batasan tabel
Nota
Dukungan DLT untuk batasan tabel ada di Pratinjau Umum . Untuk menentukan batasan tabel, alur Anda harus berupa alur yang mendukung Katalog Unity dan dikonfigurasi untuk menggunakan saluran preview
.
Saat menentukan skema, Anda dapat menentukan kunci primer dan asing. Batasan bersifat informasi dan tidak diberlakukan. Lihat klausa CONSTRAINT dalam referensi bahasa SQL.
Contoh berikut mendefinisikan tabel dengan batasan kunci primer dan asing:
CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING NOT NULL PRIMARY KEY,
customer_name STRING,
number_of_line_items STRING,
order_datetime STRING,
order_number LONG,
order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime)),
CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES main.default.customers(customer_id)
)
COMMENT "Raw data on sales"
AS SELECT * FROM ...
Membuat parameter nilai yang digunakan saat mendeklarasikan tabel atau tampilan dengan SQL
Gunakan SET
untuk menentukan nilai konfigurasi dalam kueri yang mendeklarasikan tabel atau tampilan, termasuk konfigurasi Spark. Tabel atau tampilan apa pun yang Anda tentukan dalam buku catatan setelah pernyataan SET
memiliki akses ke nilai yang ditentukan. Konfigurasi Spark apa pun yang ditentukan menggunakan pernyataan SET
digunakan saat menjalankan kueri Spark untuk tabel atau tampilan apa pun mengikuti pernyataan SET. Untuk membaca nilai konfigurasi dalam kueri, gunakan sintaks interpolasi string ${}
. Contoh berikut menetapkan nilai konfigurasi Spark bernama startDate
dan menggunakan nilai tersebut dalam kueri:
SET startDate='2020-01-01';
CREATE OR REFRESH MATERIALIZED VIEW filtered
AS SELECT * FROM src
WHERE date > ${startDate}
Untuk menentukan beberapa nilai konfigurasi, gunakan pernyataan SET
terpisah untuk setiap nilai.
Contoh : Menentukan filter baris dan masker kolom
Untuk membuat tampilan materialisasi atau Tabel streaming dengan filter baris dan masker kolom, gunakan klausa ROW FILTER dan klausa MASK . Contoh berikut menunjukkan cara menentukan tampilan materialisasi dan tabel Streaming dengan filter baris dan masker kolom:
CREATE OR REFRESH STREAMING TABLE customers_silver (
id int COMMENT 'This is the customer ID',
name string,
region string,
ssn string MASK catalog.schema.ssn_mask_fn COMMENT 'SSN masked for privacy'
)
WITH ROW FILTER catalog.schema.us_filter_fn ON (region)
AS SELECT * FROM STREAM(customers_bronze)
CREATE OR REFRESH MATERIALIZED VIEW sales (
customer_id STRING MASK catalog.schema.customer_id_mask_fn,
customer_name STRING,
number_of_line_items STRING COMMENT 'Number of items in the order',
order_datetime STRING,
order_number LONG,
order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime))
)
COMMENT "Raw data on sales"
WITH ROW FILTER catalog.schema.order_number_filter_fn ON (order_number)
AS SELECT * FROM sales_bronze
Untuk informasi selengkapnya tentang filter baris dan masker kolom, lihat Terbitkan tabel dengan filter baris dan masker kolom.
Properti SQL
CREATE TABLE atau LIHAT |
---|
TEMPORARY Buat tabel tetapi jangan terbitkan metadata untuk tabel. Klausa TEMPORARY menginstruksikan DLT untuk membuat tabel yang tersedia untuk jalur pemrosesan tetapi tidak boleh diakses di luar jalur tersebut. Untuk mengurangi waktu pemrosesan, tabel sementara bertahan selama masa pakai alur yang membuatnya, dan bukan hanya satu pembaruan. |
STREAMING Buat tabel yang membaca himpunan data input sebagai aliran. Himpunan data input harus berupa sumber data streaming, misalnya, Auto Loader atau tabel STREAMING . |
CLUSTER BY Aktifkan pengklusteran cair pada tabel dan tentukan kolom yang akan digunakan sebagai kunci pengklusteran. Lihat Menggunakan pengklusteran cair untuk tabel Delta. |
PARTITIONED BY Daftar opsional dari satu atau beberapa kolom yang akan digunakan untuk mempartisi tabel. |
LOCATION Lokasi penyimpanan opsional untuk data tabel. Jika tidak diatur, sistem akan secara otomatis beralih ke lokasi penyimpanan alur. |
COMMENT Deskripsi opsional untuk tabel. |
column_constraint Kunci primer informasi opsional atau batasan kunci asing pada kolom. |
MASK clause (Pratinjau Umum)Menambahkan fungsi masker kolom untuk menganonimkan data sensitif. Kueri di masa mendatang untuk kolom tersebut mengembalikan hasil fungsi yang dievaluasi alih-alih nilai asli kolom. Ini berguna untuk kontrol akses yang terperinci, karena fungsi dapat memeriksa identitas pengguna dan keanggotaan grup untuk memutuskan apakah akan menyunting nilai. Lihat Kolom mask . |
table_constraint Kunci primer informasi opsional atau batasan kunci asing pada tabel. |
TBLPROPERTIES Daftar opsional properti tabel untuk tabel. |
WITH ROW FILTER clause (Pratinjau Umum)Menambahkan fungsi filter baris ke tabel. Kueri di masa mendatang untuk tabel tersebut akan mendapatkan bagian dari baris-baris yang fungsinya bernilai BENAR. Ini berguna untuk kontrol akses terperintah, karena memungkinkan fungsi untuk memeriksa identitas dan keanggotaan grup pengguna yang memanggil untuk memutuskan apakah akan memfilter baris tertentu. Lihat klausa ROW FILTER . |
select_statement Kueri DLT yang menentukan himpunan data untuk tabel. |
klausa CONSTRAINT |
---|
EXPECT expectation_name Tentukan batasan kualitas data expectation_name . Jika batasan ON VIOLATION tidak ditentukan, tambahkan baris yang melanggar batasan ke himpunan data target. |
ON VIOLATION Langkah atau tindakan opsional untuk baris yang gagal:
|
Mengubah pengambilan data dengan SQL di DLT
Gunakan pernyataan APPLY CHANGES INTO
untuk menggunakan fungsionalitas DLT CDC, seperti yang dijelaskan dalam hal berikut:
CREATE OR REFRESH STREAMING TABLE table_name;
APPLY CHANGES INTO table_name
FROM source
KEYS (keys)
[IGNORE NULL UPDATES]
[APPLY AS DELETE WHEN condition]
[APPLY AS TRUNCATE WHEN condition]
SEQUENCE BY orderByColumn
[COLUMNS {columnList | * EXCEPT (exceptColumnList)}]
[STORED AS {SCD TYPE 1 | SCD TYPE 2}]
[TRACK HISTORY ON {columnList | * EXCEPT (exceptColumnList)}]
Anda menentukan batasan kualitas data untuk target APPLY CHANGES
menggunakan klausa CONSTRAINT
yang sama dengan kueri non-APPLY CHANGES
. Lihat Mengelola kualitas data dengan ekspektasi alur kerja.
Nota
Perilaku default untuk peristiwa INSERT
dan UPDATE
adalah melakukan upsert pada peristiwa CDC dari sumber: memperbarui setiap baris dalam tabel target yang cocok dengan kunci yang ditentukan atau menyisipkan baris baru jika tidak ada rekaman yang cocok dalam tabel target. Penanganan untuk peristiwa DELETE
dapat ditentukan dengan kondisi APPLY AS DELETE WHEN
.
Penting
Anda harus menentukan tabel streaming target untuk menerapkan perubahan. Anda dapat secara opsional menentukan skema untuk tabel target Anda. Saat menentukan skema tabel target APPLY CHANGES
, Anda juga harus menyertakan kolom __START_AT
dan __END_AT
dengan tipe data yang sama dengan bidang sequence_by
.
Lihat API APPLY CHANGES: Menyederhanakan pendeteksian perubahan data dengan DLT.
Klausul |
---|
KEYS Kolom atau kombinasi kolom yang secara unik mengidentifikasi baris dalam data sumber. Ini digunakan untuk mengidentifikasi peristiwa CDC mana yang berlaku untuk rekaman tertentu dalam tabel target. Untuk menentukan kombinasi kolom, gunakan daftar kolom yang dipisahkan koma. Klausa ini diperlukan. |
IGNORE NULL UPDATES Izinkan penerimaan pembaruan yang berisi sebagian kolom target. Ketika peristiwa CDC cocok dengan baris yang ada dan IGNORE NULL UPDATES ditentukan, kolom dengan null akan mempertahankan nilai yang ada di target. Ini juga berlaku untuk kolom berlapis dengan nilai null .Klausa ini bersifat opsional. Setelan bawaan adalah menimpa kolom yang ada dengan nilai null . |
APPLY AS DELETE WHEN Menentukan kapan peristiwa Change Data Capture (CDC) harus diperlakukan sebagai DELETE bukan sebagai upsert. Untuk menangani data yang tidak berurutan, baris yang dihapus untuk sementara dipertahankan sebagai batu nisan dalam tabel Delta yang mendasar, dan tampilan dibuat di metastore yang memfilter batu nisan ini. Interval retensi dapat dikonfigurasi denganpipelines.cdc.tombstoneGCThresholdInSeconds
atribut tabel.Klausa ini bersifat opsional. |
APPLY AS TRUNCATE WHEN Menentukan kapan suatu peristiwa CDC harus diperlakukan sebagai satu tabel lengkap TRUNCATE . Karena klausa ini memicu pemotongan penuh tabel target, klausul ini harus digunakan hanya untuk kasus penggunaan tertentu yang memerlukan fungsionalitas ini.Klausa APPLY AS TRUNCATE WHEN hanya didukung untuk SCD tipe 1. SCD tipe 2 tidak mendukung operasi pemangkasan.Klausa ini bersifat opsional. |
SEQUENCE BY Nama kolom yang menentukan urutan logis peristiwa CDC dalam data sumber. DLT menggunakan pengurutan ini untuk menangani peristiwa perubahan yang tiba tidak berurutan. Kolom yang ditentukan harus tipe data yang dapat diurutkan. Klausa ini diperlukan. |
COLUMNS Menentukan subset kolom untuk disertakan dalam tabel target. Anda dapat:
Klausa ini bersifat opsional. Defaultnya adalah menyertakan semua kolom dalam tabel target saat klausa COLUMNS tidak ditentukan. |
STORED AS Apakah akan menyimpan rekaman sebagai SCD tipe 1 atau SCD tipe 2. Klausa ini bersifat opsional. Defaultnya adalah SCD tipe 1. |
TRACK HISTORY ON Menentukan subset kolom output untuk menghasilkan rekaman riwayat saat ada perubahan pada kolom yang ditentukan. Anda dapat:
Klausa ini bersifat opsional. Defaultnya adalah melacak riwayat untuk semua kolom output ketika ada perubahan apa pun, setara dengan TRACK HISTORY ON * . |