CODEWITHSUNDEEP
amazon-athenadbtapache-iceberg
Valerio Auricchio
Valerio Auricchio

Reputation: 25

Do partition (yar(month/date) table iceberg with dbt

I created this model in dbt, with this config for partition:

{{ config(
    materialized='incremental',
    incremental_strategy='append',
    partitioned_by=["year('sale_date')", "month('sale_date')", "day('sale_date')"],
    schema='ga_public_sale',
    pre_hook = [
        """{% if is_incremental() %}
        delete from {{this}}
        where
        sale_date >= current_date - interval '90' day
        {% endif %}
        """
    ]
) }}

SELECT
  sale_tend.transaction_number
, sale_tend.sale_date
, sale_tend.store_code
, sale_tend.transaction_index
, sale_tend.row_key
, sale_tend.sale_currency
, sale_tend.transaction_type_code
, sale_tend.transaction_type_group
, sale_tend.tender_number
, sale_tend.tender_type_code
, sale_tend.tender_amount
, sale_tend.tender_currency
, sale_tend.tender_code
, sale_tend.closure_cashier_code cashier_code
, sale_tend.consumer_code
, tender_type_text.tender_type_desc
, tender_type.tender_type_group_code
, tender_type_group_text.tender_type_group_desc
FROM
  ((((ga_curated_sale.car_sale_transaction sale_tend
LEFT JOIN ga_curated_customer.car_store_profile store_profile ON (sale_tend.store_code = store_profile.store_code))
LEFT JOIN ga_curated_tender.car_tender_type_text tender_type_text ON ((store_profile.profile_code = tender_type_text.profile_code) AND (sale_tend.tender_type_code = tender_type_text.tender_type_code) AND (tender_type_text.language = 'E')))
LEFT JOIN ga_curated_tender.car_tender_type tender_type ON ((store_profile.profile_code = tender_type.profile_code) AND (sale_tend.tender_type_code = tender_type.tender_type_code)))
LEFT JOIN ga_curated_tender.car_tender_type_group_text tender_type_group_text ON ((store_profile.profile_code = tender_type_group_text.profile_code) AND (tender_type.tender_type_group_code = tender_type_group_text.tender_type_group_code) AND (tender_type_group_text.language = 'E')))
WHERE (((sale_tend.transaction_type_code = '1001') OR (sale_tend.transaction_type_code = '1004')) AND (sale_tend.record_type_code = 21) AND (NOT (sale_tend.task_canceled_flg IN ('X'))))
{% if is_incremental()  %}
        AND sale_date >= current_date - interval '90' day
{% endif %}

the column sale_dat is DATE and I get this error "INVALID_TABLE_PROPERTY: Unable to parse partitioning value: Invalid partition field declaration: year(\u0027sale_date\u0027)."

I tried to change some configuration for partitioned_by, but still no results

Upvotes: 0

Views: 494

Answers (1)

Rockie Yang
Rockie Yang

Reputation: 4925

Based on the error description and dbt document on partitioned.

partitioned_by shall be a specific field, so the model definition shall be.

{{ config(
    materialized='incremental',
    incremental_strategy='append',
    partitioned_by=["year", "month", "day"],
    schema='ga_public_sale',
    pre_hook = [
        """{% if is_incremental() %}
        delete from {{this}}
        where
        sale_date >= current_date - interval '90' day
        {% endif %}
        """
    ]
) }}

SELECT
  year(sale_tend.sale_date) as year
, month(sale_tend.sale_date) as month
, day(sale_tend.sale_date) as day
, sale_tend.transaction_number
, sale_tend.sale_date
, sale_tend.store_code
, sale_tend.transaction_index
, sale_tend.row_key
, sale_tend.sale_currency
, sale_tend.transaction_type_code
, sale_tend.transaction_type_group
, sale_tend.tender_number
, sale_tend.tender_type_code
, sale_tend.tender_amount
, sale_tend.tender_currency
, sale_tend.tender_code
, sale_tend.closure_cashier_code cashier_code
, sale_tend.consumer_code
, tender_type_text.tender_type_desc
, tender_type.tender_type_group_code
, tender_type_group_text.tender_type_group_desc
FROM
  ((((ga_curated_sale.car_sale_transaction sale_tend
LEFT JOIN ga_curated_customer.car_store_profile store_profile ON (sale_tend.store_code = store_profile.store_code))
LEFT JOIN ga_curated_tender.car_tender_type_text tender_type_text ON ((store_profile.profile_code = tender_type_text.profile_code) AND (sale_tend.tender_type_code = tender_type_text.tender_type_code) AND (tender_type_text.language = 'E')))
LEFT JOIN ga_curated_tender.car_tender_type tender_type ON ((store_profile.profile_code = tender_type.profile_code) AND (sale_tend.tender_type_code = tender_type.tender_type_code)))
LEFT JOIN ga_curated_tender.car_tender_type_group_text tender_type_group_text ON ((store_profile.profile_code = tender_type_group_text.profile_code) AND (tender_type.tender_type_group_code = tender_type_group_text.tender_type_group_code) AND (tender_type_group_text.language = 'E')))
WHERE (((sale_tend.transaction_type_code = '1001') OR (sale_tend.transaction_type_code = '1004')) AND (sale_tend.record_type_code = 21) AND (NOT (sale_tend.task_canceled_flg IN ('X'))))
{% if is_incremental()  %}
        AND sale_date >= current_date - interval '90' day
{% endif %}

Upvotes: 0

Related Questions