Referenční dokumentace jazyka SQL DLT

Tento článek obsahuje podrobnosti o programovacím rozhraní DLT SQL.

• Informace o jazyce Python API najdete v referenční příručce DLT pro Python .
• Další informace o příkazech SQL naleznete v tématu referenční informace jazyka SQL.

Uživatelem definované funkce Pythonu (UDF) můžete použít v dotazech SQL, ale tyto uživatelem definované funkce musíte definovat v souborech Pythonu, než je budete volat ve zdrojových souborech SQL. Viz uživatelem definované skalární funkce – Python.

Omezení

Klauzule PIVOT není podporována. Operace pivot ve Sparku vyžaduje dychtivé načítání vstupních dat pro výpočet výstupního schématu. Tato funkce není v DLT podporována.

Vytvoření materializovaného zobrazení DLT nebo tabulky streamování

Poznámka

Syntaxe CREATE OR REFRESH LIVE TABLE pro vytvoření materializovaného zobrazení je zastaralá. Místo toho použijte CREATE OR REFRESH MATERIALIZED VIEW.

Stejnou základní syntaxi SQL použijete při deklarování streamované tabulky nebo materializovaného zobrazení.

Deklarace materializovaného zobrazení DLT pomocí SQL

Následující popis syntaxe pro deklarování materializovaného zobrazení v DLT pomocí 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

Deklarace streamovací tabulky DLT pomocí SQL

Streamované tabulky můžete deklarovat pouze pomocí dotazů, které se čtou proti zdroji streamování. Databricks doporučuje používat Auto Loader pro streamovaný příjem souborů z cloudového objektového úložiště. Viz syntaxi SQL automatického načítání.

Při specifikování jiných tabulek nebo zobrazení v pracovním procesu jako streamovacích zdrojů musíte zahrnout funkci STREAM() kolem názvu datové sady.

Následující popis syntaxe pro deklarování tabulky streamování v DLT pomocí 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

Vytvoření zobrazení DLT

Následující popis syntaxe pro deklarování zobrazení pomocí 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

Syntaxe SQL automatického zavaděče

Následující popisuje syntaxi pro práci s Auto Loaderem v 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>",
      ...
    )
  )

U Automatického Loaderu můžete použít podporované možnosti formátování. Pomocí funkce map() můžete předat možnosti metodě read_files(). Možnosti jsou páry klíčů a hodnot, kde klíče a hodnoty jsou řetězce. Podrobnosti o formátech a možnostech podpory najdete v tématu Možnosti formátu souboru.

Příklad: Definování tabulek

Datovou sadu můžete vytvořit čtením z externího zdroje dat nebo z datových sad definovaných v datovém kanálu. Chcete-li číst z interní datové sady, zadejte název tabulky, která bude používat nakonfigurované výchozí hodnoty kanálu pro katalog a schéma. Následující příklad definuje dvě různé datové sady: tabulku s názvem taxi_raw, která jako vstupní zdroj přebírá soubor JSON a tabulku s názvem filtered_data, která přijímá taxi_raw tabulku jako vstup:

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

Příklad: Čtení ze streamovaného zdroje

Pro čtení dat ze zdroje streamování, například Auto Loaderu nebo interní datové sady, definujte tabulku 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)

Další informace o streamovaných datech najdete v tématu Transformace dat pomocí kanálů.

Trvalé odstranění záznamů z materializovaného zobrazení nebo tabulky streamování

Pokud chcete trvale odstranit záznamy z materializovaného zobrazení nebo tabulky streamování s povolenými vektory odstranění, jako je například dodržování předpisů GDPR, musí být u podkladových tabulek Delta objektu provedeny další operace. Pokud chcete zajistit odstranění záznamů z materializovaného zobrazení, přečtěte si Trvalé odstranění záznamů z materializovaného zobrazení s povolenými vektory odstranění. Pokud chcete zajistit odstranění záznamů z tabulky streamování, přečtěte si téma Trvalé odstranění záznamů z tabulky streamování.

Řízení způsobu materializace tabulek

Tabulky také nabízejí další kontrolu nad jejich materializací:

• Určete, jak seskupit tabulky pomocí CLUSTER BY. K rychlejšímu provádění dotazů můžete použít techniku zvanou „liquid clustering“. Viz Použijte clustering typu liquid pro Delta tabulky.
• Určete, jak jsou tabulky particionovány pomocí PARTITIONED BY.
• Vlastnosti tabulky můžete nastavit pomocí TBLPROPERTIES. Viz vlastnosti tabulky DLT.
• Nastavte umístění úložiště pomocí nastavení LOCATION. Ve výchozím nastavení se data tabulky ukládají do umístění úložiště kanálu, pokud LOCATION není nastaveno.
• V definici svého schématu můžete použít vygenerované sloupce . Viz Příklad: Určení schématu a sloupců clusteru.

Poznámka

U tabulek, které mají velikost menší než 1 TB, databricks doporučuje řídit organizaci dat DLT. Pokud neočekáváte, že se tabulka bude zvětšovat nad rámec terabajtu, databricks doporučuje, abyste nezadávejte sloupce oddílů.

příklad : Zadání schématu a sloupců clusteru

Volitelně můžete zadat schéma při definování tabulky. Následující příklad určuje schéma cílové tabulky, včetně použití Delta Lake vygenerovaných sloupců a definuje sloupce clusteringu pro tabulku:

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 ...

Ve výchozím nastavení DLT odvodí schéma z definice table, pokud nezadáte schéma.

příklad : Zadání sloupců oddílů

Volitelně můžete pro tabulku zadat sloupce rozdělení.

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 ...

Liquid clustering poskytuje flexibilní a optimalizované řešení pro shlukování. Zvažte použití CLUSTER BY místo PARTITIONED BY pro DLT.

příklad : Definování omezení tabulky

Poznámka

Podpora DLT pro omezení tabulek je v Public Preview. Pokud chcete definovat omezení tabulky, vaše pipeline musí být kanálem s podporou katalogu Unity a nakonfigurována tak, aby používala kanál preview.

Při zadávání schématu můžete definovat primární a cizí klíče. Omezení jsou informativní a nevynucují se. Viz CONSTRAINT klauzuli v referenční příručce jazyka SQL.

Následující příklad definuje tabulku s omezením primárního a cizího klíče:

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 ...

Parametrizace hodnot použitých při deklarování tabulek nebo zobrazení pomocí SQL

Pomocí SET můžete zadat hodnotu konfigurace v dotazu, který deklaruje tabulku nebo zobrazení, včetně konfigurací Sparku. Jakákoli tabulka nebo zobrazení, které definujete v poznámkovém bloku po příkazu SET, má přístup k definované hodnotě. Všechny konfigurace Sparku zadané pomocí příkazu SET se použijí při provádění dotazu Spark pro libovolnou tabulku nebo zobrazení za příkazem SET. Ke čtení konfigurační hodnoty v dotazu použijte syntaxi interpolace řetězců ${}. Následující příklad nastaví konfigurační hodnotu Sparku s názvem startDate a použije ji v dotazu:

SET startDate='2020-01-01';

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

Pokud chcete zadat více hodnot konfigurace, použijte pro každou hodnotu samostatný příkaz SET.

Příklad: Definování filtru řádků a masky sloupců

Důležitý

Filtry řádků a masky sloupců jsou ve verzi Public Preview.

Pokud chcete vytvořit materializované zobrazení nebo tabulku Streamování s filtrem řádků a maskou sloupců, použijte klauzuli ROW FILTER a klauzuli MASK. Následující příklad ukazuje, jak definovat materializované zobrazení a streamovací tabulku s filtrem řádků i maskou sloupce:

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

Další informace o filtrech řádků a maskách sloupců najdete v tématu Publikování tabulek s filtry řádků a maskami sloupců.

Vlastnosti SQL

CREATE TABLE nebo VIEW
TEMPORARY Vytvořte tabulku, ale nepublikujte metadata tabulky. Klauzule TEMPORARY dává DLT pokyn k vytvoření tabulky, která je dostupná pro kanál, ale neměla by být přístupná mimo kanál. Aby se zkrátila doba zpracování, dočasná tabulka zůstává po celou dobu trvání datového kanálu, který ji vytvoří, a ne pouze během jedné aktualizace.
STREAMING Vytvořte tabulku, která čte vstupní datovou sadu jako datový proud. Vstupní datová sada musí být streamovaným zdrojem dat, například Auto Loader nebo tabulka STREAMING.
CLUSTER BY Povolte tekuté shlukování v tabulce a definujte sloupce, které se mají použít jako klíče shlukování. Viz Použijte clustering typu liquid pro Delta tabulky.
PARTITIONED BY Volitelný seznam jednoho nebo více sloupců, které se mají použít k dělení tabulky.
LOCATION Volitelné umístění úložiště pro data tabulky. Pokud není nastaveno, systém použije výchozí umístění úložiště zásobníku.
COMMENT Volitelný popis tabulky.
column_constraint Volitelné informační omezení primárního nebo cizího klíče u sloupce.
MASK clause (Veřejná ukázka) Přidá funkci masky sloupce pro anonymizaci citlivých dat. Budoucí dotazy na tento sloupec vrátí výsledek vyhodnocené funkce místo původní hodnoty sloupce. To je užitečné pro jemně odstupňované řízení přístupu, protože funkce může zkontrolovat identitu uživatele a členství ve skupinách a rozhodnout se, jestli se má hodnota redigovat. Viz Klauzule mask sloupec.
table_constraint Volitelné informační omezení primárního nebo cizího klíče v tabulce.
TBLPROPERTIES Volitelný seznam vlastností tabulky pro tabulku.
WITH ROW FILTER clause (Veřejná ukázka) Přidá do tabulky funkci filtru řádků. Budoucí dotazy na danou tabulku obdrží podmnožinu řádků, pro které se funkce vyhodnotí jako PRAVDA. To je užitečné pro jemně odstupňované řízení přístupu, protože umožňuje funkci kontrolovat identitu a členství ve skupinách vyvolávajícího uživatele, aby rozhodla, jestli se mají určité řádky filtrovat. Viz ROW FILTER .
select_statement Dotaz DLT, který definuje datovou sadu pro tabulku.

klauzule CONSTRAINT
EXPECT expectation_name Definujte omezení kvality dat expectation_name. Pokud není definováno omezení ON VIOLATION, přidejte řádky, které porušují omezení cílové datové sady.
ON VIOLATION Volitelná akce, kterou lze provést pro neúspěšné řádky: - FAIL UPDATE: Okamžitě ukončete provádění pipeline. - DROP ROW: Zahoďte záznam a pokračujte ve zpracování.

Změna zachycení dat pomocí SQL v DLT

Pomocí příkazu APPLY CHANGES INTO použijte funkci DLT CDC, jak je popsáno v následujících příkladech:

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)}]

Omezení kvality dat pro cíl APPLY CHANGES definujete pomocí stejné klauzule CONSTRAINT jako dotazy, které nejsouAPPLY CHANGES. Viz Správa kvality dat s očekáváním pro datové toky.

Poznámka

Výchozím chováním událostí INSERT a UPDATE je upsert událostí CDC ze zdroje: aktualizujte všechny řádky v cílové tabulce, které odpovídají zadaným klíčům, nebo vložte nový řádek, pokud v cílové tabulce neexistuje odpovídající záznam. Zpracování událostí DELETE lze zadat pomocí podmínky APPLY AS DELETE WHEN.

Důležitý

Pokud chcete použít změny, musíte deklarovat cílovou streamovací tabulku. Volitelně můžete zadat schéma cílové tabulky. Při zadávání schématu cílové tabulky APPLY CHANGES musíte zahrnout také sloupce __START_AT a __END_AT se stejným datovým typem jako pole sequence_by.

Viz API APPLY CHANGES: Zjednodušení zachytávání změn v datech pomocí DLT.

Klauzule
KEYS Sloupec nebo kombinace sloupců, které jednoznačně identifikují řádek ve zdrojových datech. Slouží k identifikaci událostí CDC, které se vztahují na konkrétní záznamy v cílové tabulce. Pokud chcete definovat kombinaci sloupců, použijte čárkami oddělený seznam sloupců. Tato klauzule je povinná.
IGNORE NULL UPDATES Povolit příjem aktualizací obsahujících podmnožinu cílových sloupců Pokud se událost CDC shoduje s existujícím řádkem a je zadána možnost IGNOROVAT AKTUALIZACE s hodnotou NULL, sloupce s null si zachovají své stávající hodnoty v cíli. To platí také pro vnořené sloupce s hodnotou null. Tato klauzule je nepovinná. Výchozí hodnotou je přepsání existujících sloupců null hodnotami.
APPLY AS DELETE WHEN Určuje, kdy se má událost CDC považovat za DELETE spíše než za upsert. Aby bylo možné zpracovat data mimo pořadí, odstraněný řádek se dočasně zachová jako náhrobek v podkladové tabulce Delta a v metastoru se vytvoří zobrazení, které vyfiltruje tyto náhrobky. Interval uchovávání informací je možné nakonfigurovat pomocí pipelines.cdc.tombstoneGCThresholdInSeconds vlastnost tabulky. Tato klauzule je nepovinná.
APPLY AS TRUNCATE WHEN Určuje, kdy má být událost CDC považována za kompletní tabulku TRUNCATE. Vzhledem k tomu, že tato klauzule aktivuje úplné zkrácení cílové tabulky, měla by být použita pouze pro konkrétní případy použití vyžadující tuto funkci. Klauzule APPLY AS TRUNCATE WHEN je podporována pouze pro SCD typu 1. ScD typu 2 nepodporuje operaci zkrácení. Tato klauzule je nepovinná.
SEQUENCE BY Název sloupce určující logické pořadí událostí CDC ve zdrojových datech. DlT používá toto sekvencování ke zpracování událostí změn, které přicházejí mimo pořadí. Zadaný sloupec musí být datový typ, který lze řadit. Tato klauzule je povinná.
COLUMNS Určuje podmnožinu sloupců, které se mají zahrnout do cílové tabulky. Můžete provést jednu z těchto akcí: - Zadejte úplný seznam sloupců, které mají být zahrnuty: COLUMNS (userId, name, city). – Zadejte seznam sloupců, které chcete vyloučit: COLUMNS * EXCEPT (operation, sequenceNum) Tato klauzule je nepovinná. Výchozí hodnota je zahrnout všechny sloupce v cílové tabulce, pokud není zadaná klauzule COLUMNS.
STORED AS Určuje, zda se mají ukládat záznamy jako SCD typu 1 nebo SCD typu 2. Tato klauzule je nepovinná. Výchozí hodnota je SCD typu 1.
TRACK HISTORY ON Určuje podmnožinu výstupních sloupců pro generování záznamů historie, pokud dojde k nějakým změnám těchto zadaných sloupců. Můžete provést jednu z těchto akcí: - Zadejte úplný seznam sloupců, které chcete sledovat: COLUMNS (userId, name, city). – Zadejte seznam sloupců, které mají být vyloučeny ze sledování: COLUMNS * EXCEPT (operation, sequenceNum) Tato klauzule je nepovinná. Výchozí hodnota je sledovat historii všech výstupních sloupců, pokud dojde k nějakým změnám, což odpovídá TRACK HISTORY ON *.