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

In this video, I show you how to submit an Athena query and retrieve the results from a Lambda Function.
Looking to get hands on experience building on AWS with a REAL project? Check out my course - The AWS Learning Accelerator! courses.beabet...
📚 MY RECOMMENDED READING LIST FOR SOFTWARE DEVELOPERS📚
Clean Code - amzn.to/37T7xdP
Clean Architecture - amzn.to/3sCEGCe
Head First Design Patterns - amzn.to/37WXAMy
Domain Driver Design - amzn.to/3aWSW2W
Code Complete - amzn.to/3ksQDrB
The Pragmatic Programmer - amzn.to/3uH4kaQ
Algorithms - amzn.to/3syvyP5
Working Effectively with Legacy Code - amzn.to/3kvMza7
Refactoring - amzn.to/3r6FQ8U
🎙 MY RECORDING EQUIPMENT 🎙
Shure SM58 Microphone - amzn.to/3r5Hrf9
Behringer UM2 Audio Interface - amzn.to/2MuEllM
XLR Cable - amzn.to/3uGyZFx
Acoustic Sound Absorbing Foam Panels - amzn.to/3ktIrY6
Desk Microphone Mount - amzn.to/3qXMVIO
Logitech C920s Webcam - amzn.to/303zGu9
Fujilm XS10 Camera - amzn.to/3uGa30E
Fujifilm XF 35mm F2 Lens - amzn.to/3rentPe
Neewer 2 Piece Studio Lights - amzn.to/3uyoa8p
💻 MY DESKTOP EQUIPMENT 💻
Dell 34 inch Ultrawide Monitor - amzn.to/2NJwph6
Autonomous ErgoChair 2 - bit.ly/2YzomEm
Autonomous SmartDesk 2 Standing Desk - bit.ly/2YzomEm
MX Master 3 Productivity Mouse - amzn.to/3aYwKVZ
Das Keyboard Prime 13 MX Brown Mechanical- amzn.to/3uH6VBF
Veikk A15 Drawing Tablet - amzn.to/3uBRWsN
📚 References:
Part 1 - Athena Databasa Setup - • How to use SQL to Quer...
Athena Overview - • What is AWS Athena? Se...
Lambda Dead Letter Queue Overview - • Failure Handling Using...
Getting started with AWS: • Introduction to AWS | ...
☁Topics covered include:
Lambda Athena Query
Lambda Athena IAM Permissions
Lambda Athena Query Submission
Lambda Athena Query Results
🌎 Find me here:
Twitter - / beabetterdevv
Instagram - / beabetterdevv
Patreon - Donations help fund additional content - / beabetterdev
#AWS
#Serverless
#Lambda
#Athena
#CloudComputing

[@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!

[@haneulkim4902]

Amazing video! Thanks!

[@BeABetterDev]

Glad you liked it!

[@withtheengineer-hamza-3255]

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

[@dojocoding1341]

May be we use async await instead of sleep ?

[@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": "*" } ] }

[@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.

[@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?

[@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.

[@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.

[@davidalexander8786]

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

[@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 !

[@carlosmaturanapardo213]

How can I transform the results to json format?

[@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?

[@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!

[@thanhnguyentrung9544]

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

[@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!

[@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