AWS Tutorials - Amazon Athena Federated Query with Redshift

Amazon Athena is a serverless and interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Amazon Athena Federated Query can be used to run SQL queries across data stored in relational, non-relational, object, and custom data sources. Athena uses data source connectors that run on AWS Lambda to execute federated queries. The data source connectors help connect with data sources like CloudWatch, DocumentDB, DynamoDB, HBase, JDBC data sources (like Redshift, MySql, SQL Server) etc.

[@sabelomuzimsimango8467]

you are the best Dojo. All thanks from South Africa

[@yustiono]

This is very clear and helpful. Good work and tthank you for sharing!

[@AWSTutorialsOnline]

Thanks for appreciation,

[@varmasagi123]

Wow, that's a really nice tutorial. It helped me to exactly what I am looking for. Thank you very much.

[@tarvinder91]

very helpful to understand the connections. I wanted to know how to setup them and how that data catalog is created. Got all of it. Thanks!

[@AWSTutorialsOnline]

Glad it was helpful!

[@tarap333]

Your videos are great and very practical

[@AWSTutorialsOnline]

Glad you like them!

[@1711anand]

Thanks it worked for me.

[@AWSTutorialsOnline]

You're welcome!

[@hsz7338]

Thank you so much for the tutorial. It is great. I have a question about where the actual heavy lifting (in terms of the compute) takes place. In the demo, the query in Athena is passed onto the Redshift cluster, therefore the actual query compute will be on Redshift rather than Athena or the Lambda connector. Is my understanding correct?

[@AWSTutorialsOnline]

Query runs on Redshift. But result parsing and request / response handling happens on connector. So most heavy lifting done by Redshift, then connector and finally Athena.

[@hsz7338]

@@AWSTutorialsOnline Thank you for this. A follow-up question on the Lambda Connector, as we know there is a memory and timeout limitation with Lambda, therefore should we try and avoid pulling billions of rows of data across through Athena Federated query, instead try and aggregate as much as we can within Redshift?

[@sajalgour8114]

This was really helpful, thank you. Can you please do a video on Cost for a federated query ? I am trying to understand if it cost effective to actually copy data across if we have multiple users quering this external data source using federated query?

[@AWSTutorialsOnline]

Sounds good to me. I will plan about it

[@puneet87m]

how does the federated queries cost us ? does all the services(lambda, athena, redshift) will cost you

[@AWSTutorialsOnline]

yeah, there is no additional cost for Athena Federated Query itself. But you will have to pay for the services used like Athena, Lambda and Data Source such as Redshift, DynamoDB etc.

[@ankitaggarwal4016]

How do we enable sso users with athena fedrated query ? Also lambda max timeout limit is 15 mins, if the query is taking longer than 15 mins, should we use athena fedrated querh in those cases?

[@AWSTutorialsOnline]

AWS SSO or IAM level federation should be used for SSO users. Since Lambda has 15 mins timeout, don't use federated queries where query execution can take more than 15 mins.

[@mallikarjunsangannavar907]

@@AWSTutorialsOnline Is there any doc/video on connecting to redshift without using federated queries, even my query is taking more than 15 mins.

[@picklu1079]

I am still getting "Failed to invoke lambda function due to com.amazonaws.services.lambda.invoke.LambdaFunctionException: 0: java.sql.SQLException: No suitable driver found for ".

[@AWSTutorialsOnline]

you are using Redshift as the backend?

[@picklu1079]

@@AWSTutorialsOnline yes

[@marliebooysen2168]

Any further feedback on this. I have the same issue.

[@sivasai83]

Why should we use Athena to connect to Redshift instead of using Redshift query editor? What are the use cases ? Because connecting to Athena would cost more right? Please clarify??

[@AWSTutorialsOnline]

if querying Redshift only then you should use Query Editor. Use Athena when you want to query across different data sources (Redshift, RDS, DynamoDB, S3) in the same query .

[@sushantkamkar5481]

@@AWSTutorialsOnline Does querying from Athena will utilize compute of Redshift?