topic Re: Partition pruning with generated columns in Data Engineering

Partition pruning with generated columns

andrej — Thu, 14 Jul 2022 14:28:02 GMT

I have a large table which contains a date_time column.

The table contains 2 generated columns year, and month which are extracted from the date_time values and are used for partitioning.

I have the following question.

If I run the query

SELECT *

FROM table

WHERE date_time > '2022-07-01' and date_time < '2022-07-09'

This query will scan all the files

If I modify the query to

SELECT *

FROM table

WHERE date_time > '2022-07-01' and date_time < '2022-07-09'

AND year = 2022 and month = 7

Now pruning will get applied and the query will run ~ 20 times faster.

I would be expecting that given that there is a relationship defined between date_time and columns year and month, pruning would be applied even if only date_time is specified in the where clause.

Am I missing something in my config or is my understanding incorrect?

Thanks,

Andrej

Re: Partition pruning with generated columns

Anonymous — Thu, 14 Jul 2022 14:56:26 GMT

Partition pruning will only happen when using the generated columns i.e. ‘year’ and ‘month’ as predicates.

You can consider file pruning by zordering or using bloom filter index.

Re: Partition pruning with generated columns

-werners- — Thu, 14 Jul 2022 14:58:11 GMT

no your understanding is correct.

However there are some restrictions, which you can find here (the interesting part starts at the paragraph starting with "In Databricks Runtime 8.4 and above with Photon support, Delta Lake may be able to generate partition filters...")

Re: Partition pruning with generated columns

andrej — Thu, 14 Jul 2022 15:14:25 GMT

Hi, thank you for replies.

@Werner Stinckens i read that exact article, but after re-reading it I realise that Photon support is required.

Will try again with that. Thanks!

Re: Partition pruning with generated columns

Vidula — Sun, 04 Sep 2022 14:04:54 GMT

Hi @Andrej Znidarsic

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!