topic Re: Change schema when writing to the Delta format in Data Engineering

Change schema when writing to the Delta format

Justine_Bieber — Thu, 22 Sep 2022 11:54:51 GMT

Is it possible to reapply schema in delta files? For example, we have a history with field string but from some point, we need to replace string with struct.

In my case merge option and overwrite schema don't work.

Re: Change schema when writing to the Delta format

Hubert-Dudek — Thu, 22 Sep 2022 12:18:19 GMT

You can change a column’s type or name or drop a column by rewriting the table. To do this, use the

overwriteSchema option. But please back up your table first.

spark.read.table("table_name") \
.write \
.format("delta") \
.mode("overwrite") \
.option("overwriteSchema", "true") \
.saveAsTable("table_name")

Re: Change schema when writing to the Delta format

jose_gonzalez — Mon, 03 Oct 2022 18:22:11 GMT

Hi @Mike Biber,

Just a friendly follow-up. Did Hubert's response helped you? Let us know if you still need help.

Re: Change schema when writing to the Delta format

Justine_Bieber — Sun, 09 Oct 2022 15:06:12 GMT

Hi guys!

Definitely, thank you for your support.

Re: Change schema when writing to the Delta format

Hubert-Dudek — Sun, 16 Oct 2022 16:15:13 GMT

Please select my answer as the best one.

After the last updates, it is also possible to rename columns using ALTER, but only when mapping is enabled. Check here https://docs.databricks.com/delta/delta-column-mapping.html

Re: Change schema when writing to the Delta format

elgeo — Fri, 11 Nov 2022 10:32:35 GMT

Hi @Hubert Dudek. Do you know if this works also for identity columns? Is there another way to do this? The below returns ParseException. Thank you

tt = spark.read.table("table_name") \

.withColumn("ID",col("ID").cast("BIGINT GENERATED ALWAYS")) \

.write \

.format("delta") \

.mode("overwrite") \

.option("overwriteSchema", "true") \

.saveAsTable("table_name")