How to get Athena create Iceberg using the partition that only exists on folder name

To create an Iceberg table in Athena that uses a partition based on the folder name (date={YYYY-MM-DD}) rather than a column in the data, you can use Iceberg's hidden partitioning feature. This allows you to partition the data based on a derived value without storing it as an additional column in the dataset. Here's how you can modify your CREATE TABLE statement to achieve this:

1. Remove 'date' from the column list in your CREATE TABLE statement, as it doesn't exist in the actual data files.

2. Use the date_parse function in the PARTITIONED BY clause to extract the date from the folder name.

3. Adjust the LOCATION to point to the parent folder that contains all the company_id folders.

Here's an example of how your CREATE TABLE statement could look:

CREATE TABLE IF NOT EXISTS `test`.`status_raw` (
    `company_id` string,
    `triggertype` string,
    `context` string,
    `createddatetime` string,
    `partition_date` timestamp
)
PARTITIONED BY (
    company_id,
    date_parse(substring(\"$path\", -10), '%Y-%m-%d')
)
LOCATION 's3://my_bucket/'
TBLPROPERTIES (
    'table_type' = 'ICEBERG',
    'format' = 'parquet',
    'write_compression' = 'SNAPPY'
)

In this modified statement:

• We've kept company_id as a partition column since it appears in the folder structure.
• We've used date_parse(substring("$path", -10), '%Y-%m-%d') to extract and parse the date from the folder name. This assumes the date is always the last 10 characters of the path.
• The LOCATION is set to the bucket root, allowing Athena to traverse the entire folder structure.

This approach uses Iceberg's hidden partitioning, where the partition values are derived from the file path rather than stored as separate columns in the data files. When you query this table, Athena will use these derived partition values for filtering and optimization, but they won't appear as actual columns in your SELECT results unless you explicitly include the partition columns in your query.

Remember that for this to work correctly, your S3 folder structure must be consistent, with the date always in the format 'date=YYYY-MM-DD' at the end of the path for each data file.
Sources
Create Iceberg tables - Amazon Athena
Getting started with Apache Iceberg tables in Amazon Athena SQL - AWS Prescriptive Guidance