By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

How to easily replicate Athena tables from one region to another

0

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.

Topics
Analytics
Tags
Amazon Athena
Language
English
rePost-User-1768423
asked 2 years ago852 views
1 Answer
1

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/

AWS
SUPPORT ENGINEER
Santosh_S_S
answered 2 years ago
AWS
EXPERT
Fabrizio@AWS
reviewed 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content