delta table grouping by key which is not partitioned by is very slow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
02-02-2023 04:15 PM
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?
- Labels:
-
Delta
-
Delta table
-
Simple Queries
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-07-2023 12:22 AM
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-12-2023 11:44 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-09-2023 11:48 PM
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!