Aws athena- query both s3 and rds

Question

We are currently aws thena to analyze event data that we save as jaon to s3 using firehouse and it works well. Now we would like to perform queries that 'join' the s3 data with data from our rds mysql database. How can this be done? (Btw, we are using Tableau to query the data)

Answer

Hi,  
  
Amazon Athena is a service that enables a data analyst to perform interactive queries in the Amazon Web Services public cloud on data stored in Amazon Simple Storage Service (S3).   
  
Unfortunately, we cannot use schemas from RDS is Athena. As a work around you can use Glue ETL to join these two tables i.e one in Athena and one in RDS (both maintained in Glue Catalog).   
  
You can use glue crawler with connection to the RDS table to create the schema.   
https://docs.aws.amazon.com/glue/latest/dg/add-crawler.html  
https://docs.aws.amazon.com/glue/latest/dg/console-connections.html  
  
For example of joining tables in ETL please check on the below link as a reference:  
https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-python-samples-legislators.html  
  
I hope above information is helpful. If you are facing any issue and require technical guidance you can reach out to the AWS Support team.   
  
Have a good day :)

Answer

Hi there!

As of now, previous answer is not only option you have to query multiple data sources from Athena.
Now you can use Athena Federated Query that allows you to run queries with data from S3, Amazon DynamoDB, Apache HBase, Amazon DocumentDB, Amazon Redshift, Amazon CloudWatch Logs, AWS CloudWatch Metrics, and JDBC-compliant relational data sources such MySQL, and PostgreSQL under the Apache 2.0 license.

Refer to this link for more information about how to achieve this: https://aws.amazon.com/blogs/big-data/query-any-data-source-with-amazon-athenas-new-federated-query/

Aws athena- query both s3 and rds

Relevant content