How to materialise injected partition projections?

[igoichuk]

Hi, I am trying to use dbt over hive Athena tables with various types of partition projections. Particularly for injected partitions. The query requires at least one exact value for that partition in WHERE clause. I understand there could be some incremental materialisation that splits available values into chucks and generates multiple insert statements, similar to how 100 partition problem was solved. Does anything like that exist? Is there any other way?

[nicor88]

I don't fully understand the last part of your issue.

In order to us partition projection we need to setup table properties e.g.

TABLEPROPERTIES (
    'projection.enabled' = true
    'projection.date_created.type' = 'date',
    'projection.date_created.format' = 'yyyy/MM/dd',
    'projection.date_created.interval' = '1',
    'projection.date_created.interval.unit' = 'DAYS',
    'projection.date_created.range' = '2021/01/01,NOW',
    'storage.location.template' = 's3://mybucket/data/${date_created}/'

this means that we could extend the adatper to setup those properties - e.g. as we do for iceberg.
Doing so you can add those properties in your model config.

I think that we support something like that -> https://github.com/dbt-athena/dbt-athena/blob/main/dbt/include/athena/macros/materializations/models/table/create_table_as.sql#L13C48-L13C64 at least for full loads.

I believe that we need to add something for incremental loads -as it seems totally missing https://github.com/dbt-athena/dbt-athena/blob/main/dbt/include/athena/macros/materializations/models/incremental/incremental.sql

[igoichuk]

@nicor88, the problem is with the source table having an injected partition, not with the target table. According to documentation each injected column must have exact values specified in the WHERE clause, so I cannot omit it and I cannot specify the whole range of values in it (too long). Hence I cannot materialize from such a table.

The range of values for an injected column is in S3 prefixes for that column. Similarly to a "100 partitions fix" or "insert_by_period" where materialisation happens in batches, it can be done for partition projection where S3 prefixes are enumerated and injected into a query replacing some INJECTED_FILTER placeholder with another batch of "column" in ('value1',... 'valueN'). Potentially I could narrow down a range of values to materialize similar to start/end dates in insert_by_period.

Is there any better way you could think of to materialize from injected column without implementing the above or giving up on injected partitions?

[nicor88]

AH! I think that I got it now, but let me correct if I'm wrong, you are not able to query entirely the source table because you use partition projections, therefore you need to have specific where statements.

Did you considered to use a custom macro that first spit out the partition values, then it assemble a query based on those values?