topic Re: Is ZORDER required after table overwrite? in Data Engineering

Is ZORDER required after table overwrite?

numersoz — Thu, 24 Nov 2022 03:05:12 GMT

Hi,

After appending new values to a delta table, I need to delete duplicate rows.

After deleting duplicate rows using PySpark, I overwrite the table (keeping the schema).

My question is, do I have to do ZORDER again?

Another question, is there another way to drop duplicates? I tried drop duplicates using SQL with CTE but that didn't work. (Error: Delete is only supported with v2 tables.)

# Append new data:
data.write.mode("append").format("delta").saveAsTable("table_name")
 
# Read table:
df = spark.sql(f"SELECT * FROM {table_name}")
# Drop Duplicates:
df = df.dropDuplicates(["col1", "col2"])
# Re-write data:
df.write.format("delta").mode("overwrite").option("overwriteSchema", "false").saveAsTable(f"{table_name}")

Re: Is ZORDER required after table overwrite?

-werners- — Thu, 24 Nov 2022 09:55:32 GMT

Z-Ordering is not triggered by auto-optimize. So you will have to specifically run it.

A way to avoid dups is to use merge instead of append. But it is possible that the runtime will be bigger than the append + dropdups.

Re: Is ZORDER required after table overwrite?

yogu — Thu, 24 Nov 2022 14:40:12 GMT

the best way to avoid duplicates is the merge statements.

https://docs.databricks.com/sql/language-manual/delta-merge-into.html

Re: Is ZORDER required after table overwrite?

numersoz — Thu, 24 Nov 2022 23:25:30 GMT

@Werner Stinckens Thank you!

I am triggering Z-Order after table is created. But to be sure, if it is it required to re-do Z-Order after doing an overwrite to Delta Table (with schema overwrite set to false).

Re: Is ZORDER required after table overwrite?

-werners- — Fri, 25 Nov 2022 09:38:40 GMT

Z-ordering is never required, that also counts if you overwrite a z-ordered table.

The only thing that will happen is that the new data which is written is not z-ordered.

Re: Is ZORDER required after table overwrite?

DeepakMakwana74 — Sun, 27 Nov 2022 13:30:50 GMT

Hii @Nurettin Ersoz

try to use incremental load of data so it will avoid duplicate and you can use full load once if you have updation in your data