Memperbarui skema tabel dengan evolusi skema

Tabel mendukung evolusi skema, memungkinkan modifikasi pada struktur tabel saat persyaratan data berubah. Jenis perubahan berikut ini didukung:

Buat perubahan ini secara eksplisit menggunakan DDL atau secara implisit menggunakan DML.

Important

Pembaruan skema bertentangan dengan semua operasi tulis bersamaan. Databricks merekomendasikan koordinasi perubahan skema untuk menghindari konflik tulis.

Memperbarui skema tabel mengakhiri pembacaan aliran apa pun dari tabel tersebut. Untuk melanjutkan pemrosesan, mulai ulang aliran menggunakan metode yang dijelaskan dalam Pertimbangan produksi untuk Streaming Terstruktur.

Perubahan skema manual

Gunakan ALTER TABLE pernyataan untuk secara eksplisit mengubah skema tabel tanpa menulis data baru.

Tambahkan kolom

Gunakan ALTER TABLE ... ADD COLUMNS untuk menambahkan satu atau beberapa kolom ke tabel yang sudah ada, secara opsional menentukan posisi dan komentar:

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

Secara default, nullability adalah true.

Contoh: Menambahkan bidang berlapis

Menambahkan kolom bertumpuk hanya didukung untuk struktur. Array dan peta tidak didukung.

Untuk menambahkan kolom ke bidang bertumpuk, gunakan:

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

Misalnya, jika skema sebelum berjalan ALTER TABLE boxes ADD COLUMNS (colB.nested STRING AFTER field1) adalah:

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

skema setelahnya adalah:

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

Mengubah komentar dan pengurutan kolom

Gunakan ALTER TABLE ... ALTER COLUMN untuk memperbarui komentar kolom atau menyusun ulang relatif terhadap kolom lain:

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

Contoh: Mengubah bidang berlapis

Untuk mengubah kolom di bidang bertumpuk, gunakan:

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

Misalnya, jika skema sebelum berjalan ALTER TABLE boxes ALTER COLUMN colB.field2 FIRST adalah:

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

skema setelahnya adalah:

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

Gantikan kolom

Gunakan ALTER TABLE ... REPLACE COLUMNS untuk menentukan ulang daftar kolom lengkap tabel, termasuk menambahkan, menghapus, menyusun ulang, atau mengganti nama kolom dalam satu operasi:

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

Contoh: Ganti kolom bertingkat

Misalnya, saat menjalankan DDL berikut:

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

jika skema sebelumnya adalah:

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

skema setelahnya adalah:

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

Ganti nama kolom

Untuk mengganti nama kolom tanpa menulis ulang data kolom yang ada, Anda harus mengaktifkan pemetaan kolom untuk tabel. Lihat mengganti nama dan menghapus kolom dengan menggunakan pemetaan kolom Delta Lake.

Untuk mengganti nama kolom:

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name

Contoh: Mengganti nama bidang berlapis

Untuk mengganti nama bidang bersarang:

ALTER TABLE table_name RENAME COLUMN col_name.old_nested_field TO new_nested_field

Misalnya, saat Anda menjalankan perintah berikut:

ALTER TABLE boxes RENAME COLUMN colB.field1 TO field001

Jika skema sebelumnya adalah:

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

Lalu skema setelahnya adalah:

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

Lihat mengganti nama dan menghapus kolom dengan menggunakan pemetaan kolom Delta Lake.

Hilangkan kolom

Untuk menghilangkan kolom sebagai operasi khusus metadata tanpa menulis ulang file data apa pun, Anda harus mengaktifkan pemetaan kolom untuk tabel. Lihat mengganti nama dan menghapus kolom dengan menggunakan pemetaan kolom Delta Lake.

Note

Menghilangkan kolom dari metadata tidak menghapus data yang mendasar untuk kolom dalam file. Untuk menghapus secara menyeluruh data dari kolom yang dihapus:

  • Gunakan REORG TABLE untuk menulis ulang file.
  • Kemudian gunakan VACUUM untuk menghapus file yang berisi data kolom yang dihilangkan secara fisik.

Untuk menghapus kolom:

ALTER TABLE table_name DROP COLUMN col_name

Untuk menghapus beberapa kolom:

ALTER TABLE table_name DROP COLUMNS (col_name_1, col_name_2)

Mengubah jenis atau nama kolom

Anda bisa mengubah jenis atau nama kolom atau menghilangkan kolom dengan menulis ulang tabel. Untuk melakukan ini, gunakan overwriteSchema opsi .

Contoh berikut menunjukkan perubahan jenis kolom:

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

Contoh berikut menunjukkan perubahan nama kolom:

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

Mengaktifkan evolusi skema

Gunakan WITH SCHEMA EVOLUTION atau atur mergeSchema ke true untuk membuat perubahan skema berdasarkan skema data yang anda inginkan INSERT atau MERGE ke dalam tabel yang sudah ada.

Aktifkan evolusi skema menggunakan salah satu metode berikut:

Databricks merekomendasikan aktivasi evolusi skema pada setiap operasi tulis dengan menggunakan sintaks WITH SCHEMA EVOLUTION atau opsi mergeSchema daripada mengatur konfigurasi Spark.

Saat Anda menggunakan opsi atau sintaks untuk mengaktifkan evolusi skema dalam operasi tulis, ini lebih diutamakan daripada konfigurasi Spark.

Aktifkan evolusi skema untuk penulisan untuk menambahkan kolom baru

Ketika evolusi skema diaktifkan, kolom yang ada dalam kueri sumber tetapi hilang dari tabel target secara otomatis ditambahkan sebagai bagian dari transaksi tulis. Lihat Mengaktifkan evolusi skema.

Pertimbangkan hal berikut:

  • Kasus dipertahankan saat menambahkan kolom baru.
  • Kolom baru ditambahkan ke akhir skema tabel.
  • Jika kolom tambahan berada dalam struktur, kolom tersebut ditambahkan ke akhir struktur dalam tabel target.

INSERT dengan evolusi skema menggunakan SQL

WITH SCHEMA EVOLUTION Gunakan klausa dalam INSERT pernyataan untuk mengaktifkan evolusi skema:

INSERT WITH SCHEMA EVOLUTION INTO target_table
SELECT * FROM source_table

Jika kueri pada source_table mengembalikan kolom yang tidak ada dalam tabel target, kolom tersebut secara otomatis ditambahkan ke target_table skema. Baris yang ada menerima NULL nilai untuk kolom baru.

INSERT dengan evolusi skema menggunakan DATAFrame API

Contoh berikut menunjukkan penggunaan opsi mergeSchema dengan operasi penulisan kelompok:

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 dengan evolusi skema dengan Streaming Terstruktur

Contoh berikut menunjukkan penggunaan mergeSchema opsi dengan Auto Loader untuk Streaming Terstruktur. Lihat Apa itu 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")
)

Evolusi skema otomatis untuk penggabungan

Untuk MERGE, evolusi skema memungkinkan Anda menyelesaikan ketidakcocokan skema antara tabel target dan sumber. Ini menangani dua kasus berikut:

  1. Kolom ada di tabel sumber tetapi bukan tabel target, dan ditentukan berdasarkan nama dalam penetapan tindakan sisipkan atau perbarui. Ada tindakan UPDATE SET * atau INSERT *.

    Kolom tersebut akan ditambahkan ke skema target, dan nilainya akan diisi dari kolom terkait di sumber.

    • Ini hanya berlaku ketika nama kolom dan struktur di sumber penggabungan sama persis dengan penetapan target.

    • Kolom baru harus ada dalam skema sumber. Menetapkan kolom baru dalam klausa tindakan tidak menentukan kolom tersebut.

    Contoh-contoh ini memungkinkan evolusi skema:

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

    Contoh-contoh ini tidak memicu evolusi skema jika kolom newcol tidak ada dalam source skema:

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

  2. Kolom ada dalam tabel target tetapi bukan tabel sumber.

    Skema target tidak diubah. Kolom-kolom ini:

    • Dibiarkan tidak berubah untuk UPDATE SET *.

    • Diatur ke NULL untuk INSERT *.

    • Masih dapat dimodifikasi secara eksplisit jika ditetapkan dalam klausa aksi.

    Contohnya:

    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.

Anda harus mengaktifkan evolusi skema otomatis secara manual. Lihat Mengaktifkan evolusi skema.

Note

Dalam Databricks Runtime 11.3 LTS dan versi sebelumnya, hanya tindakan INSERT * atau UPDATE SET * yang dapat digunakan untuk evolusi skema dengan penggabungan.

Di Databricks Runtime 12.2 LTS ke atas, kolom dan bidang struktur yang ada dalam tabel sumber dapat ditentukan berdasarkan nama dalam tindakan sisipkan atau perbarui.

Dalam Databricks Runtime 13.3 LTS ke atas, Anda dapat menggunakan evolusi skema dengan struktur yang berlapis di dalam peta, seperti map<int, struct<a: int, b: int>>.

MERGEdengan evolusi skema menggunakan SQL, Python, dan Scala

Dalam Databricks Runtime 15.4 LTS ke atas, Anda dapat menentukan evolusi skema dalam pernyataan penggabungan menggunakan API SQL atau tabel:

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

Contoh operasi MERGE dengan evolusi skema

Berikut adalah beberapa contoh efek operasi MERGE dengan dan tanpa evolusi skema.

Columns Kueri (dalam SQL) Perilaku tanpa evolusi skema (default) Perilaku dengan evolusi skema
Kolom target: key, value
Kolom sumber: 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 *		 Skema tabel tetap tidak berubah; hanya kolom key, value saja yang diperbarui/disisipkan. Skema tabel diubah menjadi (key, value, new_value). Rekaman yang ada dengan kecocokan diperbarui dengan value dan new_value di sumber. Baris baru disisipkan dengan skema (key, value, new_value).
Kolom target: key, old_value
Kolom sumber: 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 *		 Tindakan UPDATE dan INSERT menampilkan kesalahan karena kolom target old_value tidak ada di sumber. Skema tabel diubah menjadi (key, old_value, new_value). Rekaman yang memiliki kecocokan dengan new_value diperbarui di sumber sementara old_value tetap tidak berubah. Rekaman baru disisipkan dengan key, new_value, dan NULL yang ditentukan untuk old_value.
Kolom target: key, old_value
Kolom sumber: 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 menampilkan kesalahan karena kolom new_value tidak ada di tabel target. Skema tabel diubah menjadi (key, old_value, new_value). Rekaman yang ada dengan kecocokan diperbarui dengan new_value di sumber tanpa mengubah old_value, dan untuk rekaman yang tidak cocok, NULL dimasukkan untuk new_value. Lihat catatan (1).
Kolom target: key, old_value
Kolom sumber: 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 menampilkan kesalahan karena kolom new_value tidak ada di tabel target. Skema tabel diubah menjadi (key, old_value, new_value). Rekaman baru disisipkan dengan key, new_value, dan NULL yang ditentukan untuk old_value. Rekaman yang ada telah NULL dimasukkan untuk new_value, sehingga old_value tetap tidak berubah. Lihat catatan (1).

(1) Perilaku ini tersedia di Databricks Runtime 12.2 LTS dan yang lebih tinggi; Databricks Runtime 11.3 LTS dan yang lebih rendah akan menghasilkan error dalam kondisi ini.

Mengecualikan kolom dari penggabungan

Di Databricks Runtime 12.2 LTS ke atas, Anda dapat menggunakan EXCEPT klausul dalam kondisi penggabungan untuk mengecualikan kolom secara eksplisit. Perilaku EXCEPT kata kunci bervariasi tergantung pada apakah evolusi skema diaktifkan atau tidak.

Ketika evolusi skema dinonaktifkan, kata kunci EXCEPT berlaku untuk daftar kolom pada tabel target dan memungkinkan kolom dikecualikan dari tindakan UPDATE atau INSERT. Kolom yang dikecualikan diatur ke null.

Dengan evolusi skema diaktifkan, EXCEPT kata kunci berlaku untuk daftar kolom dalam tabel sumber dan memungkinkan pengecualian kolom dari evolusi skema. Kolom baru pada sumber, yang tidak terdapat dalam tabel target, tidak akan ditambahkan ke skema target jika tercantum dalam klausa EXCEPT. Kolom yang dikecualikan yang sudah ada di target diatur ke null.

Contoh EXCLUDE dengan MERGE

Contoh berikut menunjukkan sintaks ini:

Columns Kueri (dalam SQL) Perilaku tanpa evolusi skema (default) Perilaku dengan evolusi skema
Kolom target: id, title, last_updated
Kolom sumber: 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)		 Baris yang cocok diperbarui dengan mengatur kolom last_updated ke tanggal saat ini. Baris baru disisipkan menggunakan nilai untuk id dan title. Bidang yang dikecualikan last_updated diatur ke null. Bidang review diabaikan karena tidak ada di target. Baris yang cocok diperbarui dengan mengatur kolom last_updated ke tanggal saat ini. Skema berevolusi untuk menambahkan bidang review. Baris baru disisipkan menggunakan semua bidang sumber kecuali last_updated yang diatur ke null.
Kolom target: id, title, last_updated
Kolom sumber: 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 menampilkan kesalahan karena kolom internal_count tidak ada di tabel target. Baris yang cocok diperbarui dengan mengatur kolom last_updated ke tanggal saat ini. Bidang review ditambahkan ke tabel target, tetapi internal_count bidang diabaikan. Baris baru yang disisipkan telah last_updated diatur ke null.

Mengaktifkan evolusi skema dengan konfigurasi Spark (warisan)

Anda dapat mengatur konfigurasi spark.databricks.delta.schema.autoMerge.enabled Spark ke true untuk mengaktifkan evolusi skema untuk semua operasi tulis di SparkSession saat ini.

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 tidak merekomendasikan pendekatan ini untuk produksi. Mengatur konfigurasi di seluruh sesi dapat menyebabkan perubahan skema yang tidak diinginkan di beberapa operasi dan membuatnya lebih sulit untuk alasan tentang operasi mana yang mengembangkan skema.

Sebagai gantinya, aktifkan evolusi skema untuk setiap operasi tulis:

Saat Anda menggunakan opsi atau sintaks untuk mengaktifkan evolusi skema dalam operasi tulis, ini lebih diutamakan daripada konfigurasi Spark.

Mengganti skema tabel

Secara bawaan, penimpaan data dalam tabel tidak menimpa skema. Saat menimpa tabel menggunakan mode("overwrite") tanpa replaceWhere, Anda mungkin masih ingin menimpa skema data yang sedang ditulis.

Untuk mengganti skema dan partisi tabel, atur opsi ke overwriteSchematrue:

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

Note

Anda tidak dapat menentukan overwriteSchema sebagai true saat menggunakan timpa partisi dinamis. Lihat Partisi dinamis ditimpa dengan partitionOverwriteMode (warisan).

  • Last updated on