Aktualizace schémat tabulek s využitím vývoje schématu

Tabulky podporují vývoj schématu, což umožňuje úpravy struktury tabulek při změně požadavků na data. Jsou podporovány následující typy změn:

Tyto změny proveďte explicitně pomocí DDL nebo implicitně pomocí DML.

Important

Aktualizace schématu jsou v konfliktu se všemi souběžnými operacemi zápisu. Databricks doporučuje koordinovat změny schématu, aby nedocházelo ke konfliktům zápisu.

Aktualizace schématu tabulky ukončí všechny datové proudy, které se z této tabulky čtou. Pokud chcete pokračovat ve zpracování, restartujte stream pomocí metod popsaných v aspektech produkce strukturovaného streamování.

Ruční změny schématu

Pomocí příkazů ALTER TABLE explicitně změňte schéma tabulky bez zápisu nových dat.

Přidání sloupců

Umožňuje ALTER TABLE ... ADD COLUMNS přidat jeden nebo více sloupců do existující tabulky, volitelně zadat pozici a komentář:

ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name], ...)

Ve výchozím nastavení je "nullability" true.

Příklad: Přidání vnořených polí

Přidávání vnořených sloupců je podporováno pouze pro struktury. Pole a mapy nejsou podporovány.

Pokud chcete přidat sloupec do vnořeného pole, použijte:

ALTER TABLE table_name ADD COLUMNS (col_name.nested_col_name data_type [COMMENT col_comment] [FIRST|AFTER colA_name], ...)

Pokud je například schéma před spuštěním ALTER TABLE boxes ADD COLUMNS (colB.nested STRING AFTER field1) následující:

- root
| - colA
| - colB
| +-field1
| +-field2

schéma je následující:

- root
| - colA
| - colB
| +-field1
| +-nested
| +-field2

Změna komentářů a řazení sloupců

Slouží ALTER TABLE ... ALTER COLUMN k aktualizaci komentáře sloupce nebo jeho uspořádání vzhledem k jiným sloupcům:

ALTER TABLE table_name ALTER [COLUMN] col_name (COMMENT col_comment | FIRST | AFTER colA_name)

Příklad: Změna vnořených polí

Pokud chcete změnit sloupec v vnořeném poli, použijte:

ALTER TABLE table_name ALTER [COLUMN] col_name.nested_col_name (COMMENT col_comment | FIRST | AFTER colA_name)

Pokud je například schéma před spuštěním ALTER TABLE boxes ALTER COLUMN colB.field2 FIRST následující:

- root
| - colA
| - colB
| +-field1
| +-field2

schéma je následující:

- root
| - colA
| - colB
| +-field2
| +-field1

Nahrazení sloupců

Slouží ALTER TABLE ... REPLACE COLUMNS k předefinování úplného seznamu sloupců tabulky, včetně přidání, odebrání, změna pořadí nebo přejmenování sloupců v rámci jedné operace:

ALTER TABLE table_name REPLACE COLUMNS (col_name1 col_type1 [COMMENT col_comment1], ...)

Příklad: Nahrazení vnořených polí

Například při spuštění následujícího DDL:

ALTER TABLE boxes REPLACE COLUMNS (colC STRING, colB STRUCT<field2:STRING, nested:STRING, field1:STRING>, colA STRING)

pokud je schéma předtím:

- root
| - colA
| - colB
| +-field1
| +-field2

schéma je následující:

- root
| - colC
| - colB
| +-field2
| +-nested
| +-field1
| - colA

Přejmenování sloupců

Pokud chcete přejmenovat sloupce bez přepsání existujících dat sloupců, musíte pro tabulku povolit mapování sloupců. Viz Přejmenování a vyřazení sloupců v rámci mapování sloupců Delta Lake.

Přejmenování sloupce:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name

Příklad: Přejmenování vnořených polí

Chcete-li přejmenovat vnořené pole:

ALTER TABLE table_name RENAME COLUMN col_name.old_nested_field TO new_nested_field

Například při spuštění následujícího příkazu:

ALTER TABLE boxes RENAME COLUMN colB.field1 TO field001

Pokud je schéma předchozí:

- root
| - colA
| - colB
| +-field1
| +-field2

Potom schéma následuje:

- root
| - colA
| - colB
| +-field001
| +-field2

Viz Přejmenování a vyřazení sloupců v rámci mapování sloupců Delta Lake.

Odstranit sloupce

Pokud chcete sloupce vypustit jako operaci jen pro metadata, aniž byste museli přepisovat datové soubory, musíte pro tabulku povolit mapování sloupců. Viz Přejmenování a vyřazení sloupců v rámci mapování sloupců Delta Lake.

Note

Vyřazení sloupce z metadat neodstraní podkladová data sloupce v souborech. Chcete-li vymazat data odstraněného sloupce:

  • Slouží REORG TABLE k přepsání souborů.
  • Pak slouží VACUUM k fyzickému odstranění souborů, které obsahují vyřazená data sloupce.

Odstranění sloupce:

ALTER TABLE table_name DROP COLUMN col_name

Odstranění více sloupců:

ALTER TABLE table_name DROP COLUMNS (col_name_1, col_name_2)

Změna typu nebo názvu sloupce

Typ nebo název sloupce můžete změnit, nebo sloupec odstranit přepsáním tabulky. K tomu použijte overwriteSchema možnost.

Následující příklad ukazuje změnu typu sloupce:

(spark.read.table(...)
  .withColumn("birthDate", col("birthDate").cast("date"))
  .write
  .mode("overwrite")
  .option("overwriteSchema", "true")
  .saveAsTable(...)
)

Následující příklad ukazuje změnu názvu sloupce:

(spark.read.table(...)
  .withColumnRenamed("dateOfBirth", "birthDate")
  .write
  .mode("overwrite")
  .option("overwriteSchema", "true")
  .saveAsTable(...)
)

Povolení vývoje schématu

Použijte WITH SCHEMA EVOLUTION nebo nastavte mergeSchema na true, abyste mohli provést změny schématu na základě schématu dat, která chcete INSERT nebo MERGE do existující tabulky.

Povolte vývoj schématu pomocí jedné z následujících metod:

Databricks doporučuje povolit vývoj schématu pro každou operaci zápisu pomocí syntaxe WITH SCHEMA EVOLUTION nebo možnosti mergeSchema, místo nastavení konfigurace Sparku.

Pokud používáte možnosti nebo syntaxi k povolení vývoje schématu v operaci zápisu, má přednost před konfigurací Sparku.

Povolení vývoje schématu pro zápisy pro přidání nových sloupců

Pokud je povolen vývoj schématu, sloupce, které jsou přítomné ve zdrojovém dotazu, ale chybí v cílové tabulce, se automaticky přidají jako součást transakce zápisu. Vizte Povolení evoluce schématu.

Vezměte v úvahu následující skutečnosti:

  • Zachování velikosti písmen při přidání nového sloupce.
  • Na konec schématu tabulky se přidají nové sloupce.
  • Pokud jsou další sloupce ve struktuře, připojí se ke konci struktury v cílové tabulce.

INSERT s vývojem schématu s využitím SQL

Použijte klauzuli WITH SCHEMA EVOLUTION v příkazech INSERT k povolení evoluce schématu:

INSERT WITH SCHEMA EVOLUTION INTO target_table
SELECT * FROM source_table

Pokud dotaz na source_table vrátí sloupce, které v cílové tabulce neexistují, tyto sloupce se automaticky přidají do schématu target_table. Existující řádky přijímají NULL hodnoty pro nové sloupce.

INSERT s vývojem schématu s využitím rozhraní DATAFrame API

Následující příklad ukazuje použití mergeSchema možnosti s dávkovou operací zápisu:

Python
(spark.read
  .table("source_table")
  .write
  .option("mergeSchema", "true")
  .mode("append")
  .saveAsTable("target_table")
)
Scala
spark.read
  .table("source_table")
  .write
  .option("mergeSchema", "true")
  .mode("append")
  .saveAsTable("target_table")

INSERT s evolucí schématu ve Structured Streaming

Následující příklad demonstruje použití možnosti mergeSchema s Auto Loaderem pro strukturované streamování. Podívejte se na Co je to Auto Loader?

(spark.readStream
  .format("cloudFiles")
  .option("cloudFiles.format", "json")
  .option("cloudFiles.schemaLocation", "<path-to-schema-location>")
  .load("<path-to-source-data>")
  .writeStream
  .option("mergeSchema", "true")
  .option("checkpointLocation", "<path-to-checkpoint>")
  .trigger(availableNow=True)
  .toTable("table_name")
)

Automatický vývoj schématu pro sloučení

Vývoj MERGEschématu umožňuje vyřešit neshody schématu mezi cílovou a zdrojnou tabulkou. Zpracovává následující dva případy:

  1. Ve zdrojové tabulce existuje sloupec, ale ne v cílové tabulce, a je uveden podle názvu při vložení nebo aktualizaci. Případně je přítomna akce UPDATE SET * nebo INSERT *.

    Tento sloupec se přidá do cílového schématu a jeho hodnoty se vyplní z odpovídajícího sloupce ve zdroji.

    • To platí jenom v případě, že název a struktura sloupce ve zdroji sloučení přesně odpovídají cílovému přiřazení.

    • Nový sloupec musí být ve zdrojovém schématu. Přiřazení nového sloupce v klauzuli akce tento sloupec nedefinuje.

    Tyto příklady umožňují vývoj schématu:

    -- The column newcol is present in the source but not in the target. It will be added to the target.
UPDATE SET target.newcol = source.newcol

-- The field newfield doesn't exist in struct column somestruct of the target. It will be added to that struct column.
UPDATE SET target.somestruct.newfield = source.somestruct.newfield

-- The column newcol is present in the source but not in the target.
-- It will be added to the target.
UPDATE SET target.newcol = source.newcol + 1

-- Any columns and nested fields in the source that don't exist in target will be added to the target.
UPDATE SET *
INSERT *

    Tyto příklady neaktivují vývoj schématu, pokud sloupec newcol není ve schématu source :

    UPDATE SET target.newcol = source.someothercol
UPDATE SET target.newcol = source.x + source.y
UPDATE SET target.newcol = source.output.newcol

  2. V cílové tabulce existuje sloupec, který v zdrojové tabulce chybí.

    Cílové schéma se nezmění. Tyto sloupce:

    • Zůstávají beze změny pro UPDATE SET *.

    • Jsou nastaveny na NULL pro INSERT * hodnotu.

    • Stále může být explicitně změněna, pokud je přiřazena v klauzuli action.

    Příklad:

    UPDATE SET *  -- The target columns that are not in the source are left unchanged.
INSERT *  -- The target columns that are not in the source are set to NULL.
UPDATE SET target.onlyintarget = 5  -- The target column is explicitly updated.
UPDATE SET target.onlyintarget = source.someothercol  -- The target column is explicitly updated from some other source column.

Musíte ručně povolit automatický vývoj schématu. Vizte Povolení evoluce schématu.

Note

V Databricks Runtime 11.3 LTS a níže je možné použít pouze INSERT * nebo UPDATE SET * akce pro vývoj schématu při sloučení.

Ve službě Databricks Runtime 12.2 LTS a vyšších je možné při akcích vložení nebo aktualizace specifikovat sloupce a strukturovaná pole ze zdrojové tabulky podle názvu.

Ve službě Databricks Runtime 13.3 LTS a novějších můžete použít vývoj schématu s strukturami vnořenými uvnitř map, například map<int, struct<a: int, b: int>>.

MERGEs vývojem schématu s využitím SQL, Python a Scala

Ve verzi Databricks Runtime 15.4 LTS a vyšší můžete pomocí rozhraní SQL nebo table API určit vývoj schématu v příkazu sloučení:

SQL
MERGE WITH SCHEMA EVOLUTION INTO target
USING source
ON source.key = target.key
WHEN MATCHED THEN
  UPDATE SET *
WHEN NOT MATCHED THEN
  INSERT *
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
Python
from delta.tables import *

(targetTable
  .merge(sourceDF, "source.key = target.key")
  .withSchemaEvolution()
  .whenMatchedUpdateAll()
  .whenNotMatchedInsertAll()
  .whenNotMatchedBySourceDelete()
  .execute()
)
Scala
import io.delta.tables._

targetTable
  .merge(sourceDF, "source.key = target.key")
  .withSchemaEvolution()
  .whenMatched()
  .updateAll()
  .whenNotMatched()
  .insertAll()
  .whenNotMatchedBySource()
  .delete()
  .execute()

Příklady operací MERGE s vývojem schématu

Tady je několik příkladů vlivu MERGE operací s vývojem schématu a bez nich.

Columns Dotaz (v SQL) Chování bez vývoje schématu (výchozí) Chování při vývoji schématu
Cílové sloupce: key, value
Zdrojové sloupce: key, value, new_value		 MERGE INTO target_table t
USING source_table s
ON t.key = s.key
WHEN MATCHED
THEN UPDATE SET *
WHEN NOT MATCHED
THEN INSERT *		 Schéma tabulky zůstává beze změny; pouze sloupce key, value jsou aktualizovány/vloženy. Schéma tabulky se změní na (key, value, new_value). Existující záznamy se shodou se aktualizují ve zdroji pomocí value a new_value. Nové řádky jsou vloženy s použitím schématu (key, value, new_value).
Cílové sloupce: key, old_value
Zdrojové sloupce: key, new_value		 MERGE INTO target_table t
USING source_table s
ON t.key = s.key
WHEN MATCHED
THEN UPDATE SET *
WHEN NOT MATCHED
THEN INSERT *		 UPDATE a INSERT akce vyvolá chybu, protože cílový sloupec old_value není ve zdroji. Schéma tabulky se změní na (key, old_value, new_value). Existující záznamy se shodami jsou aktualizovány pomocí new_value, přičemž old_value zůstává beze změny. Nové záznamy se vkládají se zadaným key, new_value a NULL pro old_value.
Cílové sloupce: key, old_value
Zdrojové sloupce: key, new_value		 MERGE INTO target_table t
USING source_table s
ON t.key = s.key
WHEN MATCHED
THEN UPDATE SET new_value = s.new_value		 UPDATE vyvolá chybu, protože sloupec new_value v cílové tabulce neexistuje. Schéma tabulky se změní na (key, old_value, new_value). Existující záznamy se shodami jsou aktualizovány pomocí new_value ve zdroji, přičemž old_value zůstávají beze změny, a pro neshodující se záznamy se zadává NULL pro new_value. Viz poznámka (1).
Cílové sloupce: key, old_value
Zdrojové sloupce: key, new_value		 MERGE INTO target_table t
USING source_table s
ON t.key = s.key
WHEN NOT MATCHED
THEN INSERT (key, new_value) VALUES (s.key, s.new_value)		 INSERT vyvolá chybu, protože sloupec new_value v cílové tabulce neexistuje. Schéma tabulky se změní na (key, old_value, new_value). Nové záznamy se vkládají se zadaným key, new_value a NULL pro old_value. Existující záznamy mají zadáno NULL pro new_value, přičemž old_value zůstává beze změny. Viz poznámka (1).

(1) Toto chování je k dispozici v Databricks Runtime 12.2 LTS a vyšších verzích; v Databricks Runtime 11.3 LTS a nižších verzích se v tomto případě zobrazí chyba.

Vyloučení sloupců se sloučením

V Databricks Runtime 12.2 LTS a novějších můžete k explicitní vyloučení sloupců použít EXCEPT klauzule v podmínkách sloučení. Chování klíčového EXCEPT slova se liší v závislosti na tom, zda je povolen vývoj schématu.

Když je vývoj schématu vypnutý, EXCEPT klíčové slovo se vztahuje na seznam sloupců v cílové tabulce a umožňuje vyloučit sloupce z UPDATE nebo INSERT akce. Vyloučené sloupce jsou nastaveny na null.

S povoleným vývojem schématu EXCEPT se klíčové slovo vztahuje na seznam sloupců ve zdrojové tabulce a umožňuje vyloučit sloupce z vývoje schématu. Nový sloupec ve zdroji, který není v cílové tabulce, se nepřidá do cílového schématu, pokud je uvedený v klauzuli EXCEPT . Vyloučené sloupce, které jsou již v cíli, jsou nastaveny na null.

Příklady EXCLUDE s MERGE

Následující příklady ukazují tuto syntaxi:

Columns Dotaz (v SQL) Chování bez vývoje schématu (výchozí) Chování při vývoji schématu
Cílové sloupce: id, title, last_updated
Zdrojové sloupce: id, title, review, last_updated		 MERGE INTO target t
USING source s
ON t.id = s.id
WHEN MATCHED
THEN UPDATE SET last_updated = current_date()
WHEN NOT MATCHED
THEN INSERT * EXCEPT (last_updated)		 Odpovídající řádky se aktualizují nastavením last_updated pole na aktuální datum. Nové řádky se vkládají pomocí hodnot pro id a title. Vyloučené pole last_updated je nastaveno na nullhodnotu . Pole review je ignorováno, protože není v cíli. Odpovídající řádky se aktualizují nastavením last_updated pole na aktuální datum. Schéma se vyvíjí tak, aby přidalo pole review. Nové řádky se vloží pomocí všech zdrojových polí s výjimkou last_updated těch, které jsou nastaveny na null.
Cílové sloupce: id, title, last_updated
Zdrojové sloupce: id, title, review, internal_count		 MERGE INTO target t
USING source s
ON t.id = s.id
WHEN MATCHED
THEN UPDATE SET last_updated = current_date()
WHEN NOT MATCHED
THEN INSERT * EXCEPT (last_updated, internal_count)		 INSERT vyvolá chybu, protože sloupec internal_count v cílové tabulce neexistuje. Odpovídající řádky se aktualizují nastavením last_updated pole na aktuální datum. Pole review se přidá do cílové tabulky, ale internal_count pole se ignoruje. Nové vložené řádky mají last_updated nastaveno na null.

Povolení vývoje schématu pomocí konfigurace Sparku (starší verze)

Konfiguraci spark.databricks.delta.schema.autoMerge.enabled Sparku můžete nastavit tak, aby true umožňovala vývoj schématu pro všechny operace zápisu v aktuální SparkSession:

Python

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", True)

Scala

spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", true)

SQL

SET spark.databricks.delta.schema.autoMerge.enabled=true

Note

Databricks tento přístup nedoporučuje pro produkční prostředí. Nastavení konfigurace platné pro celou relaci může vést k nezamýšleným změnám schématu napříč více operacemi a ztěžuje určení, které operace schéma mění.

Místo toho povolte vývoj schématu pro každou operaci zápisu:

Pokud používáte možnosti nebo syntaxi k povolení vývoje schématu v operaci zápisu, má přednost před konfigurací Sparku.

Nahrazení schématu tabulky

Ve výchozím nastavení přepsání dat v tabulce schéma nepřepíše. Při přepsání tabulky pomocí mode("overwrite") bez replaceWhere můžete stále chtít přepsat schéma zapisovaných dat.

Chcete-li nahradit schéma a partitioning tabulky, nastavte možnost overwriteSchema na true:

df.write.option("overwriteSchema", "true")

Note

Nelze určit overwriteSchema jako true při použití dynamického přepsání oddílu. Viz Dynamické přepsání oddílů (partitionOverwriteModestarší verze).

  • Last updated on