topic delta table grouping by key which is not partitioned by is very slow in Data Engineering

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

chanansh — Fri, 03 Feb 2023 00:15:48 GMT

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?

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

Anonymous — Tue, 07 Mar 2023 08:22:33 GMT

@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.

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

chanansh — Mon, 13 Mar 2023 06:44:09 GMT

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

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

Anonymous — Mon, 10 Apr 2023 06:48:29 GMT

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!