I was trying to optimize my DataBricks delta tables in order to improve query performance. I am having few doubts to be clarified, asking here since I did not get answer from the documentation.
I am having few tables which are not partitioned but adding data incrementally. Is there any way to only optimize only the incrementally adding data at each week. (I understood optimization happens at partitions and if it is not partitioned, optimization can be made on complete table data, currently we are doing like that. but just to confirm that there are no other work arounds.)
I have tables which are partitioned, and i try to optimize the table by providing a subquery to fetch the partition to be optimized. Below is the query used where load_date is the partition column.
OPTMIZE database.table where
load_date > (select to_date(max(load_date)) as load_date
from audit.delta_optimization_audit
where source = 'abc' and job_status = 'success')
But the optimization failed with ERROR
org.apache.spark.sql.AnalysisException: Subquery is not supported in partition predicates.
What and all conditions can be added in the OPTIMIZE WHERE clause? Is subquery not allowed in WHERE clause for OPTIMIZE command?
If there are multiple partitions like YEAR then MONTH then DATE, How should I provide Partitions in WHERE clause to OPTIMIZE data incrementally?
Is it useful to run OPTIMIZE query on a table which is not partitioned ?
Any Leads Appreciated. Thanks in Advance!