delta table grouping by key which is not partitioned by is very slow

[chanansh]

I have a big data delta table with timestamp, key and metric(s) columns (e.g. m1, m2, ...).

I often will group by the key (e.g. select max(m1) group by timestamp, key).

I cannot partition by `key` because there are too many values( ~200K).

I have tried to optimize the table with ZORDER on key, but it does not help.

Bottom line, simple queries take several minutes.

Any idea what to do?

[Anonymous]

@Hanan Shteingart​ : We suggest you to use Delta Lake's OPTIMIZE command. Delta Lake's OPTIMIZE command can help improve query performance by reorganizing the table's data files and statistics. The OPTIMIZE command can automatically determine the best way to organize the table's data files based on your query patterns, and can help eliminate data skew and improve data locality.

Use Delta Lake's CLUSTER BY command: If you are unable to partition your table on the key

column due to a large number of unique values, you can use Delta Lake's CLUSTER BY command instead.

[chanansh]

Been there done that 😊 still super slow for anything interactive.

[Anonymous]

Hi @Hanan Shteingart​ 

Hope all is well! Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help. 

We'd love to hear from you.

Thanks!