Uppdatera tabellscheman med schemautveckling

Tabeller stöder schemautveckling, vilket gör det möjligt att ändra tabellstrukturen när datakraven ändras. Följande typer av ändringar stöds:

Gör dessa ändringar explicit med DDL eller implicit med hjälp av DML.

Important

Schemauppdateringarna är i konflikt med alla samtidiga skrivåtgärder. Databricks rekommenderar att du samordnar schemaändringar för att undvika skrivkonflikter.

När du uppdaterar ett tabellschema avslutas alla strömmar som läser från tabellen. Om du vill fortsätta bearbetningen startar du om strömmen med hjälp av de metoder som beskrivs i Produktionsöverväganden för strukturerad direktuppspelning.

Manuella schemaändringar

Använd ALTER TABLE instruktioner för att uttryckligen ändra en tabells schema utan att skriva nya data.

Lägg till kolumner

Använd ALTER TABLE ... ADD COLUMNS för att lägga till en eller flera kolumner i en befintlig tabell, om du vill ange position och en kommentar:

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

Som standard är "nullability" true.

Exempel: Lägg till kapslade fält

Det går bara att lägga till kapslade kolumner för structs. Matriser och kartor stöds inte.

Om du vill lägga till en kolumn i ett kapslat fält använder du:

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

Om schemat till exempel innan du kör ALTER TABLE boxes ADD COLUMNS (colB.nested STRING AFTER field1) är:

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

schemat efter är:

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

Ändra kolumnkommentar och ordning

Använd ALTER TABLE ... ALTER COLUMN för att uppdatera en kolumns kommentar eller ändra ordning på den i förhållande till andra kolumner:

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

Exempel: Ändra kapslade fält

Om du vill ändra en kolumn i ett kapslat fält använder du:

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

Om schemat till exempel innan du kör ALTER TABLE boxes ALTER COLUMN colB.field2 FIRST är:

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

schemat efter är:

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

Ersätt kolumner

Använd ALTER TABLE ... REPLACE COLUMNS för att omdefiniera den fullständiga kolumnlistan för en tabell, inklusive att lägga till, ta bort, ändra ordning på eller byta namn på kolumner i en enda åtgärd:

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

Exempel: Ersätt kapslade fält

Till exempel när du kör följande DDL:

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

om schemat innan är:

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

schemat efter är:

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

Byt namn på kolumner

Om du vill byta namn på kolumner utan att skriva om någon av kolumnernas befintliga data måste du aktivera kolumnmappning för tabellen. Se Byt namn på och ta bort kolumner med kolumnmappning i Delta Lake.

Så här byter du namn på en kolumn:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name

Exempel: Byt namn på kapslade fält

Så här byter du namn på ett kapslat fält:

ALTER TABLE table_name RENAME COLUMN col_name.old_nested_field TO new_nested_field

När du till exempel kör följande kommando:

ALTER TABLE boxes RENAME COLUMN colB.field1 TO field001

Om schemat innan är:

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

Sedan ser schemat ut så här:

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

Se Byt namn på och ta bort kolumner med kolumnmappning i Delta Lake.

Ta bort kolumner

Om du vill släppa kolumner som en endast metadataåtgärd utan att skriva om några datafiler måste du aktivera kolumnmappning för tabellen. Se Byt namn på och ta bort kolumner med kolumnmappning i Delta Lake.

Note

Om du tar bort en kolumn från metadata tas inte underliggande data bort för kolumnen i filer. Så här rensar du borttagna kolumndata:

  • Använd REORG TABLE för att skriva om filer.
  • Använd VACUUM sedan för att fysiskt ta bort de filer som innehåller borttagna kolumndata.

Så här släpper du en kolumn:

ALTER TABLE table_name DROP COLUMN col_name

Så här släpper du flera kolumner:

ALTER TABLE table_name DROP COLUMNS (col_name_1, col_name_2)

Ändra kolumntyp eller namn

Du kan ändra en kolumns typ eller namn eller släppa en kolumn genom att skriva om tabellen. Använd alternativet overwriteSchema för att göra detta.

I följande exempel visas hur du ändrar en kolumntyp:

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

I följande exempel visas hur du ändrar ett kolumnnamn:

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

Aktivera schemautveckling

Använd WITH SCHEMA EVOLUTION eller ange mergeSchema till true för att göra schemaändringar baserat på schemat för de data som du vill INSERT eller MERGE i en befintlig tabell.

Aktivera schemautveckling med någon av följande metoder:

Databricks rekommenderar att du aktiverar schemautveckling för varje skrivåtgärd med hjälp av syntaxen WITH SCHEMA EVOLUTIONmergeSchema eller alternativet i stället för att ange en Spark-konfiguration.

När du använder alternativ eller syntax för att aktivera schemautveckling i en skrivåtgärd har detta företräde framför Spark-konfigurationen.

Aktivera schemautveckling för skrivningar för att lägga till nya kolumner

När schemautvecklingen är aktiverad läggs kolumner som finns i källfrågan men som saknas i måltabellen automatiskt till som en del av en skrivtransaktion. Se Aktivera schemautveckling.

Tänk på följande:

  • Skiftläge bevaras när en ny kolumn läggs till.
  • Nya kolumner läggs till i slutet av tabellschemat.
  • Om de ytterligare kolumnerna finns i en struct läggs de till i slutet av structen i måltabellen.

INSERT med schemautveckling med SQL

Använd WITH SCHEMA EVOLUTION-satsen i INSERT-satser för att aktivera schemautveckling:

INSERT WITH SCHEMA EVOLUTION INTO target_table
SELECT * FROM source_table

Om frågan på source_table returnerar kolumner som inte finns i måltabellen läggs dessa kolumner automatiskt till i target_table schemat. Befintliga rader tar emot NULL värden för de nya kolumnerna.

INSERT med schemautveckling med hjälp av DataFrame API

I följande exempel visas hur du använder mergeSchema alternativet med en batchskrivningsåtgärd:

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 med schemautveckling med strukturerad direktuppspelning

I följande exempel visas hur du använder mergeSchema alternativet med Automatisk inläsning för strukturerad direktuppspelning. Se Vad är en automatisk inläsare?.

(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")
)

Automatisk schemautveckling för sammanslagning

För MERGEkan du med schemautveckling lösa schemamatchningar mellan måltabellen och källtabellen. Den hanterar följande två fall:

  1. Det finns en kolumn i källtabellen men inte i måltabellen och anges med namn i en tilldelning av infognings- eller uppdateringsåtgärder. Alternativt finns en UPDATE SET *- eller INSERT *-åtgärd.

    Den kolumnen läggs till i målschemat och dess värden fylls i från motsvarande kolumn i källan.

    • Detta gäller endast när kolumnnamnet och strukturen i sammanslagningskällan exakt matchar måltilldelningen.

    • Den nya kolumnen måste finnas i källschemat. När du tilldelar den nya kolumnen i åtgärdssatsen definieras inte den kolumnen.

    De här exemplen tillåter schemautveckling:

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

    De här exemplen utlöser inte schemautveckling om kolumnen newcol inte finns i source schemat:

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

  2. Det finns en kolumn i måltabellen men inte i källtabellen.

    Målschemat ändras inte. Följande kolumner:

    • Lämnas oförändrade för UPDATE SET *.

    • Är inställda på NULL för INSERT *.

    • Kan fortfarande ändras uttryckligen om det tilldelas i åtgärdssatsen.

    Ett exempel:

    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.

Du måste aktivera automatisk schemautveckling manuellt. Se Aktivera schemautveckling.

Note

I Databricks Runtime 11.3 LTS och nedan kan endast INSERT * eller UPDATE SET * åtgärder användas för schemautveckling med sammanslagning.

I Databricks Runtime 12.2 LTS och senare kan kolumner och structfält som finns i källtabellen anges med namn i infognings- eller uppdateringsåtgärder.

I Databricks Runtime 13.3 LTS och senare kan du använda schemautveckling med structs kapslade inuti kartor, till exempel map<int, struct<a: int, b: int>>.

MERGEmed schemautveckling med hjälp av SQL, Python och Scala

I Databricks Runtime 15.4 LTS och senare kan du ange schemautveckling i en kopplingsinstruktion med hjälp av SQL- eller tabell-API:er:

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()

Exempelåtgärder för MERGE med schemautveckling

Här följer några exempel på effekterna av MERGE åtgärd med och utan schemautveckling.

Columns Fråga (i SQL) Beteende utan schemautveckling (standard) Beteende med schemautveckling
Målkolumner: key, value
Källkolumner: 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 *		 Tabellschemat förblir oförändrat. endast kolumner key, value uppdateras/infogas. Tabellschemat ändras till (key, value, new_value). Befintliga poster med matchningar uppdateras med value och new_value i källan. Nya rader infogas med schemat (key, value, new_value).
Målkolumner: key, old_value
Källkolumner: 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- och INSERT-åtgärder utlöser ett fel eftersom målkolumnen old_value inte finns i källan. Tabellschemat ändras till (key, old_value, new_value). Befintliga poster med matchningar uppdateras med new_value i källan, medan old_value lämnas oförändrade. Nya poster infogas med angiven key, new_valueoch NULL för old_value.
Målkolumner: key, old_value
Källkolumner: 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 utlöser ett fel eftersom kolumn new_value inte finns i måltabellen. Tabellschemat ändras till (key, old_value, new_value). Befintliga poster med matchningar uppdateras med new_value i källan, medan old_value lämnas oförändrade, och omatchade poster får NULL angivet för new_value. Se anteckning (1).
Målkolumner: key, old_value
Källkolumner: 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 utlöser ett fel eftersom kolumn new_value inte finns i måltabellen. Tabellschemat ändras till (key, old_value, new_value). Nya poster infogas med angiven key, new_valueoch NULL för old_value. Befintliga poster har NULL angivits för new_value och har lämnats oförändrade för old_value. Se anteckning (1).

(1) Det här beteendet är tillgängligt i Databricks Runtime 12.2 LTS och senare versioner. Databricks Runtime 11.3 LTS och tidigare versioner ger ett fel i det här fallet.

Exkludera kolumner med sammanslagning

I Databricks Runtime 12.2 LTS och senare kan du använda EXCEPT-satser i kopplingsvillkor för att uttryckligen exkludera kolumner. Beteendet för nyckelordet EXCEPT varierar beroende på om schemautvecklingen är aktiverad eller inte.

När schemautvecklingen är inaktiverad gäller nyckelordet EXCEPT för listan med kolumner i måltabellen och tillåter exkludering av kolumner från UPDATE eller INSERT åtgärder. Exkluderade kolumner är inställda på null.

När schemautvecklingen är aktiverad gäller nyckelordet EXCEPT för listan över kolumner i källtabellen och tillåter exkludering av kolumner från schemautveckling. En ny kolumn i källan, som inte finns i måltabellen, läggs inte till i målschemat om den visas i EXCEPT -satsen. Exkluderade kolumner som redan finns i målet ställs in på null.

Exempel på EXCLUDE med MERGE

Följande exempel visar den här syntaxen:

Columns Fråga (i SQL) Beteende utan schemautveckling (standard) Beteende med schemautveckling
Målkolumner: id, title, last_updated
Källkolumner: 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)		 Matchade rader uppdateras genom att ställa in fältet last_updated till det aktuella datumet. Nya rader infogas med värden för id och title. Det exkluderade fältet last_updated är inställt på null. Fältet review ignoreras eftersom det inte finns i målet. Matchade rader uppdateras genom att ställa in fältet last_updated till det aktuella datumet. Schemat har utvecklats för att lägga till fältet review. Nya rader infogas med alla källfält utom last_updated, som är inställd på null.
Målkolumner: id, title, last_updated
Källkolumner: 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 utlöser ett fel eftersom kolumn internal_count inte finns i måltabellen. Matchade rader uppdateras genom att ställa in fältet last_updated till det aktuella datumet. Fältet review läggs till i måltabellen, men fältet internal_count ignoreras. Nya infogade rader har last_updated inställt på null.

Aktivera schemautveckling med Spark-konfiguration (äldre)

Du kan ange Spark-konfigurationen spark.databricks.delta.schema.autoMerge.enabled till true för att aktivera schemautveckling för alla skrivåtgärder i den aktuella 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 rekommenderar inte den här metoden för produktion. Om du ställer in en sessionsomfattande konfiguration kan det leda till oavsiktliga schemaändringar i flera åtgärder och gör det svårare att resonera om vilka åtgärder som utvecklar schemat.

Aktivera i stället schemautveckling för varje skrivåtgärd:

När du använder alternativ eller syntax för att aktivera schemautveckling i en skrivåtgärd har detta företräde framför Spark-konfigurationen.

Ersätt tabellschema

Som standard skriver överskrivning av data i en tabell inte över schemat. När du skriver över en tabell med mode("overwrite") utan replaceWherekanske du fortfarande vill skriva över schemat för de data som skrivs.

Om du vill ersätta schemat och partitioneringen av tabellen anger du overwriteSchema alternativet till true:

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

Note

Du kan inte ange overwriteSchema som true när du använder dynamisk partitionsöverskrivning. Se Dynamisk partitionsöverskrivning med partitionOverwriteMode (äldre).

  • Last updated on