Describe the bug
Updating Athena database table schema via CloudFormation fails since the table already exists and currently requires manual SQL updates to add/delete table columns.
To Reproduce
Run geocore-stack.yml in CloudFormation with a change in the Athena database table schema. Even when the schema has changed, it will not be updated
Expected behavior
Athena database table schema to be updated.
Considerations and potential fix
It is important that the table is generated on a brand new AWS account, so that when the table doesn't exist, then it is created.
We cannot DROP the existing table and run the CREATE in case a CloudFormation rollback fails.
Unsure if there is a IF/THEN/ELSE logic available to us.
Maybe is it okay to CREATE IF NOT EXIST and append an ADD/DELETE COLUMN IF NOT EXISTS to capture the latest updates to the schema.
bo-lu
commented
1 year ago
Describe the bug Updating Athena database table schema via CloudFormation fails since the table already exists and currently requires manual SQL updates to add/delete table columns.
To Reproduce Run geocore-stack.yml in CloudFormation with a change in the Athena database table schema. Even when the schema has changed, it will not be updated
Expected behavior Athena database table schema to be updated.
Considerations and potential fix
DROPthe existing table and run theCREATEin case a CloudFormation rollback fails.IF/THEN/ELSElogic available to us.CREATE IF NOT EXISTand append anADD/DELETE COLUMN IF NOT EXISTSto capture the latest updates to the schema.