Untitled

CREATE TABLE IF NOT EXISTS {{bronze_excite_rg_database}}.spend_limit_v1
(
 
    id                                     BIGINT,
    account_id                             INT,
    period_id                              INT,
    allowance                              STRING,
    total_spent                            STRING,
    currency_id                            INT,
    start_time                             STRING,
    pending_allowance                      STRING,
    expiry_time                            STRING,
    pending_period_id                      INT,
    created_time                           STRING,
    modified_time                          STRING,
    pending_active_from                    STRING,
    time_zone_id                           STRING,
    jurisdiction                           STRING,
    limit_type                             INT,
    audit_effective_from_timestamp         STRING,
    audit_modified_process_id              STRING,
    audit_data_source_code                 STRING,
    audit_source_timezone                  STRING,
    audit_change_sequence_id               STRING,
    audit_source_territory                 STRING,
    year                                   INT,
    month                                  INT,
    day                                    INT,
    hour                                   INT
)
USING PARQUET
 
PARTITIONED BY (audit_source_territory, year, month, day, hour)
 
 
LOCATION "s3://{{vitruvian_excite_domain}}/data_lake/bronze/rg/spend_limit/v1/"
TBLPROPERTIES (
    business_keys = '{
        "business_keys":[ "audit_source_territory", "id"]
    }',
    partitioning = '{
        "partitions":[
            { "field":"audit_source_territory", "ordinal":"0", "baseField":"audit_source_territory","functionKey": "none" },
            { "field":"year", "ordinal":"1", "baseField":"audit_effective_from_timestamp", "functionKey": "year", "functionArgs":{ "precision":4 } },
            { "field":"month", "ordinal":"2", "baseField":"audit_effective_from_timestamp", "functionKey": "month", "functionArgs":{ "precision":2 } },
            { "field":"day", "ordinal":"3", "baseField":"audit_effective_from_timestamp", "functionKey": "day", "functionArgs":{ "precision":2 } },
            { "field":"hour", "ordinal":"4", "baseField":"audit_effective_from_timestamp", "functionKey": "hour", "functionArgs":{ "precision":2 } }
        ]
    }'
);