Fail to create Athena table in Athena service: "Cannot find source column: requestTimestamp"

Hello There,

Hope you are well.

I understand that you are getting the error "Cannot find source column: requestTimestamp", when trying to create and IceBerg table.

In your query, you're trying to partition the table by month(requestTimestamp) and hubspotId. When I tried to replicate the error with the same query I was able to get the same error as well.

To resolve this issue, I tried to modify the CREATE TABLE statement to partition by columns with all lower case. Here's a corrected version of your query:

CREATE TABLE `tal-usage-test`.`Riki-Blinki` 
(requestId string, 
requestRoute string, 
apiKeyId string, 
responseStatusCode int, 
platform string, 
hubspotId string, 
requestTimestamp timestamp) 
PARTITIONED BY (month(`requesttimestamp`), hubspotid) 
LOCATION 's3://riki-blinders' 
TBLPROPERTIES 
('table_type'='iceberg', 
'format'='PARQUET', 
'write_compression'='zstd');

In this modified version, we've changed the PARTITIONED BY clause to use lower case for requesttimestamp and hubspotid.

Also, make sure that the S3 location you specified ('s3://riki-blinders') exists and that you have the necessary permissions to access it.

Remember that when working with Iceberg tables in Athena, there are some specific considerations and limitations. Always refer to the most recent Athena documentation for the most up-to-date information on working with Iceberg tables.

Sources
Troubleshoot Athena Apache Iceberg table errors | AWS re:Post
CREATE TABLE - Amazon Athena