Zero Downtime options for table materialization

[nicor88]

See this PR: Tomme/dbt-athena#95

Also using iceberg should be quit easy:

ALTER TABLE target RENAME TO target_bkp;
ALTER TABLE target_tmp RENAME TO target;

DROP target_bkp; -- only if all is fine

It's not 100% downtime, but almost.

Regarding not iceberg table we can do something like this:

• if the table do not exist we use a normal cta in the right location
• if the table exist we:
  • crate the table via cta in a location that is unique
  • call an glue api update table to use the new location and make a new table version
  • drop the table create in step 1
    So pretty much we leverage a table update to add a total new data schema and a new location. https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/glue.html#Glue.Client.update_table

[Jrmyy]

Right now it is not possible to use the rename command since the rename actually works but an error is raised because results can't be found according to athena API.

A case has been opened by my company on AWS. They are investigation.

[hiltercoty]

This can be done by using views but Athena views are not supported by either Spark or Redshift, which causes trouble in my use-case.

Another approach could be using the ALTER TABLE SET LOCATION 'new location' command. The same table can be created in a different folder like a partition and once the data is ready, the table location can be updated.

With this approach metadata (partition information) would mostly be the same but to cover everything MSCK REPAIR TABLE can be executed after the alter command.

[nicor88]

@hiltercoty we merged the feature to address ha for hive tables.
Iceberg support for ha is still on on-hold as we are missing an alter table rename working properly.

[nicor88]

As found by @Jrmyy this issue is not longer blocked as the athena team fixed the ALTER TABLE target RENAME TO statement.