topic Re: Delta table acceleration for group by on key columns using ZORDER does not work in Data Engineering

Delta table acceleration for group by on key columns using ZORDER does not work

chanansh — Fri, 03 Feb 2023 13:02:06 GMT

What is the best practice for accelerating queries which looks like the following?

win = Window.partitionBy('key1','key2').orderBy('timestamp') 
df.select('timestamp', (F.col('col1') - F.lag('col1').over(win)).alias('col1_diff'))

I have tried to use OPTIMIZE table ZORDER key1, key2 but it does not work well.

There is a lot of shuffling going on. The data is partitioned by date which is a generated column from timestamp.

Re: Delta table acceleration for group by on key columns using ZORDER does not work

Anonymous — Wed, 08 Mar 2023 06:21:44 GMT

@Hanan Shteingart : To optimize queries with window functions, you should try multiple things like - filter the data, reduce the number of columns used in the window, optimize the window partitioning and ordering, and increase the cluster size if needed. Please try the below options

FIlter the dataset df before applying the window function
Reduce the number of columns in df before applying the window function
Partition the dataframe df by choosing the right partition key that reduces the number of partitions and distributes the data evenly across the partitions
Choose an ordering key that reduces the amount of data that needs to be sorted and processed
Final resort, increase the size of your cluster to allocate more computing resources to the query

Re: Delta table acceleration for group by on key columns using ZORDER does not work

Anonymous — Thu, 06 Apr 2023 06:55:40 GMT

Hi @Hanan Shteingart

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance!