How to Query AWS Athena from a Lambda Function | Step by Step Tutorial

[@AbdurrahmanKocukcu]

Pleasant and concise walkthrough. Thanks :)

[@BeABetterDev]

You're very welcome

[@Spaz667]

Quality video with quality information! I'm going to be looking at more of your videos!

[@BeABetterDev]

Thanks scott! Glad you enjoyed.

[@bassethoundgang2800]

Thanks for the video. Very helpful

[@BeABetterDev]

Welcome!

[@manideepj6927]

Please provide the IAM roles used in this code deploy. If it provided in any cloud formation script can be appreciated

[@naderga]

According to the video the IAM policy should be { "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": [ "athena:StartQueryExecution", "athena:GetQueryExecution", "athena:GetQueryResults", "glue:GetTables", "glue:GetTable" ], "Resource": "*" } ] }

[@141-shivanisarikonda2]

Hi thanks for the video. I am working on this for my intern project and I am not able to view the query result csv in the output bucket passed in the lambda code. Isn't the query result csv should be in that bucket?

[@ovapny]

Hi, don't you need to add some permissions for the S3 bucket?

[@withtheengineer-hamza-3255]

Was very helpful, Thanks, the IAM permissions was to important too be detailed I guess

[@haneulkim4902]

Amazing video! Thanks!

[@BeABetterDev]

Glad you liked it!

[@thewisearchitect]

Very nice videos. Thanks for all the tutorials. I do have one question though.... Imagine we have a DynamoDB table which can get Inserts as well as Updates several times a day. We want to store all the inserts and updates (with historic versions of the same record over time) in S3. What is the best pattern that we should use to store these records in S3. Shall we always treat DB Updates as Inserts to S3. Do we also need to structure the S3 in hive-style folders hierarchy (e.g., date wise folders)? Will AWS Glue and Athena be able to query this data easily if the S3 files are placed in pre-structured date-organised folders? For instance, imagine a Customer's record underwent 3 changes at three different dates. We will end up saving his/her data within three different JSON/Parquet files and within different folders (due to different dates). Will Athena be able to return all versions of this Customer record if needed? Apologies for the long question.

[@christojose438]

Thank you so much for the video. Great learning opportunity for me!! Can we give a custom name for the resulting CSV generated by the Athena query? Currently, it is like "735bdf75-d313-4c98-a890-14e179f6f441.csv". Thanks in advance.

[@dojocoding1341]

May be we use async await instead of sleep ?

[@P0LGARIS]

I have been trying to do the same, but I get a 403 when Athena tries to write to the output S3 bucket. The bucket itself allows all actions. Is there any access to S3 required to be configured?

[@thanhnguyentrung9544]

Hi Bro, Please show source code of Role LambdaAthenaRole? 02:28

[@davidalexander8786]

why waiting for the query to be resolved in the lambda instead of triggering a s3 event for the result bucket ?

[@mdhruv1]

Can we also add a video of how to use aws cli ssm and putty to ssh into an ec2 . I have seen how to get to it from the aws console but I cannot get to it using aws cli using windows

[@BeABetterDev]

Coming soon! Thanks for the suggestion.

[@carlosmaturanapardo213]

How can I transform the results to json format?

[@banusreemohankumar3395]

I can see you use BOTO3, is there a specific reason for python runtime preference over Java? Can you share some java code samples if possible.thanks !

[@universallocal]

Hi . Cant we create New databse and its tables from Boto3 in athena?. I am not finding any document of that .. On official Boto3 website also no mention of creating Tables and database

[@BeABetterDev]

Hi there, Not that I know of. Databases and tables are creating through scanning data in S3. Hope this helps!

[@wtfzalgo]

Athena is just a query layer upon the AWS Data Catalog, which is under the Glue stack. If you want to create and manage databases and tables using the AWS SDK (boto3), you need to use the Glue APIs. see: boto3.amazonaws.com/v1/documentation/api/latest/reference/services/glue.html

[@charlotteh7588]

I've been struggling with finding a way to get a presigned url for an athena query output triggered by a lambda via API Gateway back as API response. Any chance you could make a video on that? :)

[@BeABetterDev]

Hi Charlotte, I found some useful information for you from the boto3 documentation here: boto3.amazonaws.com/v1/documentation/api/latest/reference/services/athena.html#Athena.Client.start_query_execution Take a look at the ResultConfiguration -> OutputLocation ("The location in Amazon S3 where your query results are stored, such as s3://path/to/query/bucket/". It looks like you can specify the location where the result will be delivered and return that back from your lambda api. Keep in mind the result will be eventually to s3 and could have some delay.

[@charlotteh7588]

@@BeABetterDev Thank you so much for your help and getting back so quickly! Will start from there. :) Just one more question: Do you know it it is possible to return a presigned url for that location as API response without waiting for athena to finish saving the query to S3? You mentioned in another video that making Lambda wait for athena to finish can get expensive/be unpredictable due to the varying time it requires.

[@BeABetterDev]

Hi Charlotte, Not sure if you found an answer yet, but I found some more documentation that may be helpful. What you can do is execute your query using the start-query-execution command. This will return a string called query execution Id. With this ID, you can re-query Athena with the get-query-execution command. This call will return metadata about the query and tell you where the result will be stored (see docs.aws.amazon.com/athena/latest/ug/querying.html). Keep in mind though that this doesn't mean you can query the S3 file right away as the query may not yet be completed. Hope this helps and thanks for watching my channel!

[@skippy1234459]

What is the name of the diagram software that you use at the beginning of this video?

[@BeABetterDev]

Hi there, the software is called draw.io and its free to use!

[@skippy1234459]

@@BeABetterDev Thanks!