Skip to content

MERGE INTO pushdown filter is not applied to a table defined with partition transform #10108

Closed as not planned
Closed as not planned
MERGE INTO pushdown filter is not applied to a table defined with partition transform#10108
Labels
bugSomething isn't workingstale

Description

@bk-mz
bk-mz
opened on Apr 9, 2024

Apache Iceberg version

1.4.2

Query engine

Spark

Please describe the bug 🐞

Table is defined with partitioned by (hour(data_load_ts)) where data_load_ts is timestamp column.

So, a record with data_load_ts = timestamp '2024-04-04 01:15:53' Iceberg will put it into data_load_ts_hour=2024-04-04-01 partition.

Now, imagine I need to upsert a batch to data_load_ts_hour=2024-04-04-01 partition.

I need to write a query of such:

merge into table as t
from batch as b
on batch.id = table.id and system.hour(table.data_load_ts) = system.hour(timestamp '2024-04-04 01:00:00')
when matched then update *
when not matched then insert *

It's observed in the physical plan that in this case no pushdown filter is applied and iceberg will traverse the whole table.

+- BatchScan glue.prod.table[id#1938, data_load_ts#1959, _file#1962, _pos#1963L, _spec_id#1960, _partition#1961] glue.prod.table (branch=null) [filters=, groupedBy=] RuntimeFilters: []

Is there any way to somehow ask iceberg not to traverse the whole table?

Activity

  1. bk-mz
    added
    bugSomething isn't working
    on Apr 9, 2024

  2. ludlows commented on Apr 29, 2024

    @ludlows
    ludlows
    on Apr 29, 2024
    Contributor

    @bk-mz did you solve it ? which version of the spark are you using?

  3. bk-mz commented on Apr 29, 2024

    @bk-mz
    bk-mz
    on Apr 29, 2024
    Author

    @ludlows no, we didn't, we have to trim timestamp column to have filters working.

    spark 3.5.0

  4. aokolnychyi commented on Apr 29, 2024

    @aokolnychyi
    aokolnychyi
    on Apr 29, 2024
    Contributor

    This has been fixed in #9873 but not released yet.

  5. bk-mz commented on Apr 29, 2024

    @bk-mz
    bk-mz
    on Apr 29, 2024
    Author

    @aokolnychyi would this fix also work on merge-on-read tables?

  6. aokolnychyi commented on Apr 29, 2024

    @aokolnychyi
    aokolnychyi
    on Apr 29, 2024
    Contributor

    I actually misread the issue. I think the problem in your case is that are manually comparing the hours. Instead, you should let Iceberg do this for you. Your predicate should be against the timestamp column without the partition transforms.

  7. bk-mz commented on Apr 29, 2024

    @bk-mz
    bk-mz
    on Apr 29, 2024
    Author

    How the query would look like? So I need to update specific partition and I have a virtual partition hour on date_time_ts.

    How would I target that partition?

    merge into table as t
from batch as b
on batch.id = table.id and table.data_load_ts = ???????
when matched then update *
when not matched then insert *

  8. github-actions commented on Oct 27, 2024

    @github-actions
    github-actionsbot
    on Oct 27, 2024 – with GitHub Actions

    This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.

  9. github-actions
    added
    stale
    on Oct 27, 2024

  10. bk-mz commented on Oct 28, 2024

    @bk-mz
    bk-mz
    on Oct 28, 2024
    Author

    cc @aokolnychyi @RussellSpitzer Folks can we discuss this issue? It's still clear that joining by actual value of the table partition way worse (performance wise) than specifying the partition manually.

  11. github-actions
    removed
    stale
    on Oct 29, 2024

  12. github-actions commented on Apr 27, 2025

    @github-actions
    github-actionsbot
    on Apr 27, 2025 – with GitHub Actions

    This issue has been automatically marked as stale because it has been open for 180 days with no activity. It will be closed in next 14 days if no further activity occurs. To permanently prevent this issue from being considered stale, add the label 'not-stale', but commenting on the issue is preferred when possible.

  13. github-actions
    added
    stale
    on Apr 27, 2025

  14. github-actions commented on May 11, 2025

    @github-actions
    github-actionsbot
    on May 11, 2025 – with GitHub Actions

    This issue has been closed because it has not received any activity in the last 14 days since being marked as 'stale'

  15. github-actions
    closed this as not plannedon May 11, 2025

  16. bk-mz commented on May 11, 2025

    @bk-mz
    bk-mz
    on May 11, 2025
    Author

    see here: #3607

    Pushdown filters are not applicable for tables in Iceberg, instead runtime filters are used after BatchScan

    +- BroadcastHashJoin [id#2818], [id#2003], Inner, BuildRight, false
 :- Filter isnotnull(id#2818)
 :  +- Project [id#2818, _file#2821, _pos#2822L, _spec_id#2819, _partition#2820, monotonically_increasing_id() AS __row_id#2825L]
 :     +- Filter cast(timestamp#2816 as string) INSET 2025-05-08 04:00:00.0, 2025-05-08 05:00:00.0, 2025-05-08 06:00:00.0, 2025-05-08 07:00:00.0, 2025-05-08 08:00:00.0, 2025-05-08 09:00:00.0, 2025-05-08 10:00:00.0, 2025-05-08 11:00:00.0, 2025-05-08 12:00:00.0, 2025-05-08 13:00:00.0, 2025-05-08 14:00:00.0, 2025-05-08 15:00:00.0, 2025-05-08 16:00:00.0, 2025-05-08 17:00:00.0, 2025-05-08 18:00:00.0
 :        +- BatchScan local.db.table[timestamp#2816, id#2818, _file#2821, _pos#2822L, _spec_id#2819, _partition#2820] local.db.table (branch=null) [filters=, groupedBy=] RuntimeFilters: []

    So it's working like that by design.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingstale

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions