Referensi bahasa Delta Live Tables SQL

  • Artikel

Artikel ini menyediakan detail untuk antarmuka pemrograman SQL Tabel Langsung Delta.

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.

Batasan

klausul PIVOT tidak didukung. pivot Operasi di Spark memerlukan pemuatan data input yang bersemangat untuk menghitung skema output. Kemampuan ini tidak didukung dalam Tabel Langsung Delta.

Membuat tampilan terwujud atau tabel streaming Tabel Langsung Delta

Anda menggunakan sintaks SQL dasar yang sama saat mendeklarasikan tabel streaming atau tampilan materialisasi (juga disebut sebagai LIVE TABLE).

Anda hanya dapat mendeklarasikan tabel streaming menggunakan kueri yang dibaca terhadap sumber streaming. Databricks merekomendasikan penggunaan Auto Loader untuk streaming penyerapan file dari penyimpanan objek cloud. Lihat Sintaks SQL Auto Loader.

Anda harus menyertakan fungsi di STREAM() sekitar nama himpunan data saat menentukan tabel atau tampilan lain di alur Anda sebagai sumber streaming.

Berikut ini menguraikan sintaks untuk mendeklarasikan tampilan materialisasi dan tabel streaming dengan SQL:

CREATE OR REFRESH [TEMPORARY] { STREAMING TABLE | LIVE TABLE } table_name
  [(
    [
    col_name1 col_type1 [ GENERATED ALWAYS AS generation_expression1 ] [ COMMENT col_comment1 ] [ column_constraint ],
    col_name2 col_type2 [ GENERATED ALWAYS AS generation_expression2 ] [ COMMENT col_comment2 ] [ column_constraint ],
    ...
    ]
    [
    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, ... )]
  [LOCATION path]
  [COMMENT table_comment]
  [TBLPROPERTIES (key1 [ = ] val1, key2 [ = ] val2, ... )]
  AS select_statement

Membuat tampilan Tabel Langsung Delta

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

Sintaks SQL Auto Loader

Berikut ini menjelaskan sintaksis untuk bekerja dengan Auto Loader di SQL:

CREATE OR REFRESH STREAMING TABLE table_name
AS SELECT *
  FROM cloud_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 sejumlah opsi ke metode cloud_files(). Opsi adalah pasangan nilai kunci, 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, tambahkan kata kunci LIVE ke nama himpunan data: Contoh berikut menetapkan dua himpunan data yang berbeda: tabel bernama taxi_raw yang menggunakan file JSON sebagai sumber input dan tabel bernama filtered_data yang menggunakan tampilan taxi_raw sebagai input:

CREATE OR REFRESH LIVE TABLE taxi_raw
AS SELECT * FROM json.`/databricks-datasets/nyctaxi/sample/json/`

CREATE OR REFRESH LIVE TABLE filtered_data
AS SELECT
  ...
FROM LIVE.taxi_raw

Contoh: Membaca dari sumber streaming

Untuk membaca data dari sumber streaming, misalnya, Auto Loader atau himpunan data internal, tentukan STREAMING tabel:

CREATE OR REFRESH STREAMING TABLE customers_bronze
AS SELECT * FROM cloud_files("/databricks-datasets/retail-org/customers/", "csv")

CREATE OR REFRESH STREAMING TABLE customers_silver
AS SELECT * FROM STREAM(LIVE.customers_bronze)

Untuk informasi selengkapnya tentang data streaming, lihat Mengubah data dengan Tabel Langsung Delta.

Mengontrol bagaimana tabel diwujudkan

Tabel juga menawarkan kontrol tambahan atas materialisasinya:

  • Tentukan bagaimana tabel dipartisi menggunakan PARTITIONED BY. Anda dapat menggunakan partisi untuk mempercepat kueri.
  • Anda dapat mengatur properti tabel menggunakan TBLPROPERTIES. Lihat Properti tabel Tabel Langsung Delta.
  • Atur lokasi penyimpanan menggunakan pengaturan LOCATION. Secara default, data tabel disimpan di lokasi penyimpanan alur jika LOCATION tidak diatur.
  • Anda dapat menggunakan kolom yang dihasilkan dalam definisi skema Anda. Lihat Contoh: Menentukan skema dan kolom partisi.

Catatan

Untuk tabel berukuran kurang dari 1 TB, Databricks merekomendasikan untuk mengizinkan Delta Live Table mengontrol organisasi data. Kecuali Anda mengharapkan tabel Anda tumbuh di luar terabyte, Anda umumnya tidak boleh menentukan kolom partisi.

Contoh: Tentukan skema dan kolom partisi

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 partisi untuk tabel:

CREATE OR REFRESH LIVE TABLE 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 ...

Secara default, Delta Live Tables menyimpulkan skema dari definisi table jika Anda tidak menentukan skema.

Contoh: Menentukan batasan tabel

Catatan

Dukungan batasan tabel ada di Pratinjau Umum. Untuk menentukan batasan tabel, alur Anda harus berupa alur yang mendukung Katalog Unity dan dikonfigurasi untuk menggunakan preview saluran.

Saat menentukan skema, Anda dapat menentukan kunci primer dan kunci asing. Batasan bersifat informasi dan tidak diberlakukan. Contoh berikut mendefinisikan tabel dengan batasan kunci primer dan asing:

CREATE OR REFRESH LIVE TABLE 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 ...

Mengatur nilai konfigurasi untuk tabel atau tampilan

Gunakan SET untuk menentukan nilai konfigurasi untuk tabel atau tampilan, termasuk konfigurasi Spark. Tabel atau tampilan apa pun yang Anda tentukan di buku catatan setelah SET pernyataan memiliki akses ke nilai yang ditentukan. Konfigurasi Spark apa pun yang ditentukan menggunakan SET pernyataan digunakan saat menjalankan kueri Spark untuk tabel atau tampilan apa pun setelah pernyataan SET. Untuk membaca nilai konfigurasi dalam kueri, gunakan sintaks interpolasi untai (karakter)${}. Contoh berikut menetapkan nilai konfigurasi Spark yang diberi nama startDate dan menggunakan nilai tersebut dalam kueri:

SET startDate='2020-01-01';

CREATE OR REFRESH LIVE TABLE filtered
AS SELECT * FROM src
WHERE date > ${startDate}

Untuk menentukan beberapa nilai konfigurasi, gunakan pernyataan terpisah SET untuk setiap nilai.

Properti SQL

BUAT TABEL atau LIHAT
TEMPORARY

Buat tabel tetapi jangan terbitkan metadata untuk tabel. Klausa TEMPORARY menginstruksikan Tabel Langsung Delta untuk membuat tabel yang tersedia untuk alur tetapi tidak boleh diakses di luar alur. 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 STREAMING tabel.
PARTITIONED BY

Daftar opsional dari satu atau beberapa kolom yang digunakan untuk mempartisi tabel.
LOCATION

Lokasi penyimpanan opsional untuk data tabel. Jika tidak diatur, sistem akan default ke lokasi penyimpanan alur.
COMMENT

Deskripsi opsional untuk tabel.
column_constraint

Kunci primer informasi opsional atau batasan kunci asing pada kolom.
table_constraint

Kunci primer informasi opsional atau batasan kunci asing pada tabel.
TBLPROPERTIES

Daftar opsional properti tabel untuk tabel.
select_statement

Kueri Delta Live Tables yang menentukan himpunan data untuk tabel.
Klausa BATASAN
EXPECT expectation_name

Tentukan expectation_name batasan kualitas data. Jika batasan ON VIOLATION tidak ditentukan, tambahkan baris yang melanggar batasan ke himpunan data target.
ON VIOLATION

Tindakan opsional yang harus diambil untuk baris yang gagal:

* FAIL UPDATE: Segera hentikan eksekusi alur.
* DROP ROW: Hilangkan catatan dan lanjutkan pemrosesan.

Mengubah pengambilan data dengan SQL di Tabel Langsung Delta

APPLY CHANGES INTO Gunakan pernyataan untuk menggunakan fungsionalitas CDC Delta Live Tables, seperti yang dijelaskan dalam hal berikut:

CREATE OR REFRESH STREAMING TABLE table_name;

APPLY CHANGES INTO LIVE.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 APPLY CHANGES target menggunakan klausul yang sama CONSTRAINT dengan non-kueriAPPLY CHANGES . Lihat Mengelola kualitas data dengan Tabel Langsung Delta.

Catatan

Perilaku default untuk INSERT dan UPDATE peristiwa adalah untuk upsert peristiwa CDC dari sumber: memperbarui baris apa pun dalam tabel target yang cocok dengan kunci yang ditentukan atau menyisipkan baris baru saat rekaman yang cocok tidak ada di tabel target. Penanganan untuk DELETE peristiwa dapat ditentukan dengan kondisi tersebut APPLY AS DELETE WHEN.

Penting

Anda harus mendeklarasikan 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 jenis data yang sama dengan bidang sequence_by.

Lihat Pengambilan data perubahan yang disederhanakan dengan APPLY CHANGE API di Tabel Langsung Delta.

Klausul
KEYS

Kolom atau kombinasi kolom yang secara unik mengidentifikasi baris dalam data sumber. Ini digunakan untuk mengidentifikasi peristiwa CDC mana yang berlaku untuk catatan tertentu dalam tabel target.

Klausul ini diperlukan.
IGNORE NULL UPDATES

Izinkan menyerap pembaruan yang berisi subset kolom target. Ketika peristiwa CDC cocok dengan baris yang ada dan ABAIKAN PEMBARUAN NULL ditentukan, kolom dengan null akan mempertahankan nilai yang ada di target. Ini juga berlaku untuk kolom bersarang dengan nilai null.

Klausa ini opsional.

Defaultnya adalah menimpa kolom yang ada dengan null nilai.
APPLY AS DELETE WHEN

Menentukan kapan peristiwa CDC harus diperlakukan sebagai DELETE bukan upsert. Untuk menangani data yang tidak sesuai urutan, baris yang dihapus untuk sementara disimpan sebagai penanda di tabel Delta yang mendasarinya, dan tampilan dibuat di metastore yang menyaring penanda ini. Interval retensi dapat dikonfigurasi dengan
pipelines.cdc.tombstoneGCThresholdInSecondsproperti tabel.

Klausa ini opsional.
APPLY AS TRUNCATE WHEN

Menentukan kapan acara CDC harus diperlakukan sebagai tabel lengkap TRUNCATE. Karena klausa ini memicu pemotongan penuh tabel target, klausa ini harus digunakan hanya untuk kasus penggunaan tertentu yang memerlukan fungsionalitas ini.

Klausa APPLY AS TRUNCATE WHEN hanya didukung untuk SCD jenis 1. SCD jenis 2 tidak mendukung pemotongan.

Klausa ini opsional.
SEQUENCE BY

Nama kolom yang menentukan urutan logis peristiwa CDC dalam data sumber. Delta Live Tables menggunakan pengurutan ini untuk menangani peristiwa perubahan yang tidak sesuai pesanan.

Klausul ini diperlukan.
COLUMNS

Menentukan subset kolom untuk disertakan dalam tabel target. Anda dapat:

* Tentukan daftar lengkap kolom untuk menyertakan: COLUMNS (userId, name, city).
* Tentukan daftar kolom untuk mengecualikan: COLUMNS * EXCEPT (operation, sequenceNum)

Klausa ini opsional.

Defaultnya adalah memasukkan semua kolom dalam tabel target saat COLUMNS klausul tidak ditentukan.
STORED AS

Apakah Anda akan menyimpan rekaman sebagai SCD jenis 1 atau SCD jenis 2.

Klausa ini opsional.

Defaultnya adalah SCD jenis 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 opsional. Defaultnya adalah riwayat trek untuk semua kolom output ketika ada perubahan apa pun, setara dengan TRACK HISTORY ON *.