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

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 jika LOCATION tidak diatur.
• Anda dapat menggunakan kolom yang dihasilkan dalam definisi skema Anda. Lihat Contoh : Tentukan skema dan kolom kluster.

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

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

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.

Lihat API APPLY CHANGES: Menyederhanakan pendeteksian perubahan data dengan DLT.