AWS Athena table with bucket in different region failing

Question

I need to query data in regions other than the region where Athena running.

Trying this: https://docs.aws.amazon.com/athena/latest/ug/json.html Followed the docs, And Even I can't query bucket in the same region.

Say my current S3 and Athena both are us-east-1, then

LOCATION 's3://mybucket/data/' . (Working)

LOCATION 's3://mybucket-us-east-1/data/' (Not Working)

I need to query data from eu-central-1 LOCATION 's3://my-another-bucket-eu-central-1/data/' (Not Working)

Any Help?

Error : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. com.amazon.ws.emr.hadoop.fs.shaded.com.amazonaws.services.s3.model.AmazonS3Exception: The specified bucket does not exist (Service: Amazon S3; Status Code: 404; Error Code: NoSuchBucket;

Answer 1

I believe the note in JSON SerDe Libraries page is specific to Amazon's example data for Athena in buckets at s3://athena-examples-us-east-1 and other regions:

Note

You can query data in regions other than the region where you run Athena. Standard inter-region data transfer rates for Amazon S3 apply in addition to standard Athena charges. To reduce data transfer charges, replace myregion in s3://athena-examples-myregion/path/to/data/ with the region identifier where you run Athena, for example, s3://athena-examples-us-east-1/path/to/data/.

You should reference your own buckets by their name, and they should work even if they are in another region.