Create table in spark taking a lot of time

user_16396174 0
Viewed 150

We have a table creation databricks script like this,

finalDF.write.format('delta').option("mergeSchema", "true").mode('overwrite').save(table_path)
          spark.sql("CREATE TABLE IF NOT EXISTS {}.{} USING DELTA LOCATION '{}' ".format('GOLDDB', table, table_path))

So in the table_path initially during first load we just have 1 file.. So this runs as incremental and everyday files accumulates.. So after 10 incremental loads, this takes around 10 hours to complete. Could you please help me on how to optimise the load? Is it possible to merge files?

I just tried removing some files for testing purpose but it failed with error that some files present in log file is missing and this occurs when you manually delete the files..

please suggest on how to optimize this query

delta-lake pyspark databricks
1 Answers

Instead of write + create table you can just do everything in one step using the path option + saveAsTable:

finalDF.write.format('delta')\
  .option("mergeSchema", "true")\
  .option("path", table_path)\
  .mode('overwrite')\
  .saveAsTable(table_name) # like 'GOLDDB.name'

To cleanup old data you need to use VACUUM command (doc), maybe you may need to decrease retention from default 30 days (see doc on delta.logRetentionDuration option)

Alex_Ott_186270
Related