How to easily replicate Athena tables from one region to another

Question

Hello,

We are trying to replicate our existing tables to live in another region and I was wondering if there is an established process to accomplish this easily and quickly (and ideally to update the tables on a continuing schedule as the existing tables are updated)?

I am able to create the external table with the same DDL as I was in the other region, but running MSCK REPAIR TABLE to try load the partitions into the new table fails with:

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

We have created Quicksight dashboards based on these tables and we would like to move them to us-east-1 so the user management and actual datasets and dashboards all live in the same region. Our understanding was that to do this we will need to migrate our Athena tables as well.

Answer

Hi,

We can programmatically copy the tables by executing the DDL statements in different regions
https://docs.aws.amazon.com/athena/latest/ug/code-samples.html

Also since the Athena tables are basically Glue data catalogs, you can also refer the following Github link for migration of glue data catalog to another data catalog, and see if that helps with your requirement.
https://github.com/aws-samples/aws-glue-samples/tree/master/utilities/Hive_metastore_migration#aws-glue-data-catalog-to-another-aws-glue-data-catalog.

I understand that you are getting the following error while running MSCK REPAIR TABLE command.
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask
There can be different reasons for this error, but the most common one is with permissions issue. I would recommend you to check the IAM user/role permissions and S3 bucket permissions (if it is an S3 source).
https://aws.amazon.com/premiumsupport/knowledge-center/access-denied-athena/
https://aws.amazon.com/premiumsupport/knowledge-center/quicksight-access-denied-athena-data/

How to easily replicate Athena tables from one region to another

관련 콘텐츠