Bagikan melalui


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:

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

Penting

Filter baris dan masker kolom berada di Pratinjau Umum .

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:
  • FAIL UPDATE: Segera hentikan eksekusi pipeline.
  • DROP ROW: Hilangkan rekaman dan lanjutkan pemrosesan.

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 dengan
pipelines.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:
  • Tentukan daftar lengkap kolom yang akan disertakan: COLUMNS (userId, name, city).
  • Tentukan daftar kolom yang akan dikecualikan: COLUMNS * EXCEPT (operation, sequenceNum)

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:
  • Tentukan daftar lengkap kolom yang akan dilacak: COLUMNS (userId, name, city).
  • Tentukan daftar kolom yang akan dikecualikan dari pelacakan: COLUMNS * EXCEPT (operation, sequenceNum)

Klausa ini bersifat opsional. Defaultnya adalah melacak riwayat untuk semua kolom output ketika ada perubahan apa pun, setara dengan TRACK HISTORY ON *.