How to use SQL to Query S3 files with AWS Athena | Step by Step Tutorial

[@1upgreenshroon]

Thanks for posting this. Watching a clear, concise youtube video saved me about 10 hours of reading AWS docs. Much appreciated.

[@BeABetterDev]

You're very welcome!

[@warrenb7450]

It's wonderful to see a real demo of how Athena and S3 works. Thank you!!

[@BeABetterDev]

Hi Peining, glad you found this helpful!

[@sagayaraji2143]

That was so quick and clear explanation

[@BeABetterDev]

Glad you enjoyed Sagayaraj!

[@zhengyangma1889]

much better than the recommended tutorials on my home page

[@ПавелКузнецов-з3е]

Fantastic video for beginners.

[@BeABetterDev]

Glad it was helpful!

[@irfan4701]

I really appreciate all the hardwork you do to bring such a wonderful content to us. Salute May be someday you get billions subs

[@BeABetterDev]

Thank you so much for your kind words!

[@skkkks2321]

excellent explanation and demo.Great job,keep it up

[@BeABetterDev]

Thanks a lot!

[@mosesg45]

Awesome video using Athena and Glue..

[@nicomelmo]

Thanks man, you showed my EXACTLY what I needed just with this short video!!

[@chrismcclure4264]

That was a great tutorial. Thank you

[@datag1199]

This is great! Thank you for posting these awesome videos.

[@BeABetterDev]

You're very welcome!

[@BuhlzI]

Good stuff. The crawler makes it stupid easy.

[@BeABetterDev]

Absolutely! As a lazy developer crawlers really help out :)

[@michaelhughes8413]

Nice tutorial !

[@BeABetterDev]

Thank you! Cheers!

[@rohitpatil6150]

Thank you for details explanation.

[@BeABetterDev]

Glad it was helpful!

[@PrathmeshMedhekar]

Thanks its Very helpful, keep going god bless you mate.

[@tomaszziss5481]

Thanks for nice tutorial.

[@BeABetterDev]

You're very welcome!

[@alpha_ray_burst]

This is a FANTASTIC video. So easy, quick, concise. Thank you for making this.

[@BeABetterDev]

You're very welcome!

[@saccherine]

This is superb

[@najibmestaoui5024]

Thank you for the tutorial and thumbs up for using Firefox

[@hikingcloud]

Good explanation!

[@BeABetterDev]

Thanks Manish!

[@hikingcloud]

Hey Daniel, I have use case to capture details of ec2 resources when it’s created like type of instance, tags, size etc for reporting purpose. I can’t find that details in OOTB cloud trail logs.. thinking of writing some custom function to capture those detail whenever an instance is created and stored in s3 and then query through Athena.. does it sound like a good approach?

[@ihebbibani7122]

Thanks for this video.I subscribed.Will look forward for your other videos ;)

[@BeABetterDev]

Welcome!

[@techTFQ]

Good video.. thank you..

[@KS-tf3ri]

Love the videos! Thank you for all the help and keep them coming!

[@BeABetterDev]

Glad you like them!

[@robindong3802]

Thank you, good job, simple and clear. love it.

[@evanfang6573]

Really good demo, easy and clear, thanks.

[@stutisharma594]

This is a really great tutorial for beginners with no exposure or access to AWS Consoles but want to learn. Thank you, Sir! :-)

[@Kumarthangasamy]

Good one...short and sweet...

[@BeABetterDev]

Thank you 🙂

[@nitam2238]

Thank you so much. This very helpfull

[@BeABetterDev]

You're very welcome Nita!

[@srirajjayaprakash3852]

Good job man

[@donamallik2011]

Excellent video. I followed it to create table in Athena with Glue. The only thing that stumped me was the Role. I had to refer to Amazon docs to create the Role. I then used it in creating the crawler. Without that the table wasn't getting created.

[@nanwang4765]

The video is awesome.

[@BeABetterDev]

Thanks nan!

[@sweatyguysteve8087]

great video

[@BeABetterDev]

Thanks Steve!

[@bakozen891]

Thanks bro it's very useful to begin ;)

[@zwc76]

The data structure that you have is called NDJSON (the one without brackets and commas at the end).

[@Canda-fh4xc]

Thank you for the great tutorial. is the Json Files available for download

[@hectornavarrete3350]

excellent video, I wonder if after read a text file with athena I can insert into a Oracle database that use (TLS)

[@BeABetterDev]

Hi Hector, you definitely can!

[@omkarhaldankar2940]

thanku so much

[@BeABetterDev]

You're very welcome Omkar!

[@xxy8118]

hi, can you please have a tutorial on how to add, edit and delete entries as well. thank you so much.

[@sachavanweeren9578]

Thanks for the great video. Can you share the resources you use as well (e.g. the json file you use here). That makes following along a bit more easy

[@BeABetterDev]

Hi Sacha, I checked my disk and unfortunately lost the file used in this demo. However I'm fairly certain I used the online tool Mockaroo to generate the fake data. You can give it a try to emulate. Daniel

[@alik.8061]

amazing

[@uchiha_sasuke_48]

Simple and easy tutorial. But in the previous videos you told that, Athena won't return the query results due to its asynchronous nature, instead it saves the query results back in the S3 as a new object. Is it true, or I've listened it wrong?

[@freakinmonkey85]

No fucking way, I was just looking for a video on this when “boom, AWS Simplified...” Awesome

[@BeABetterDev]

Glad I was able to help!

[@vitache1276]

Waooo perfect

[@BeABetterDev]

Thanks 😊

[@csmatyi]

Hello, do you think I can query an sqlite3 db file that is being stored in an AWS S3 bucket? How would I do that? Thanks.

[@ganganegi6932]

It would have been great if you had shown the output S3 bucket again after running this query.

[@duket.8598]

would/could the next step be to throw that 2nd Query (purchase data > $50) into Quickinsight to visualize the data -- or -- what would be the rationale to leave it in this state/final, curious. Thank you. Aloha

[@bhakta_rg]

thanks for the Simplified explanation. Liked and subscribed! What is the "default" database with Glue? I should be able to use the default database with as many s3 buckets I scan? thanks again

[@BeABetterDev]

Thanks Bhakta! Not sure what the default one is - i think its just created as part of your account. You can certainly use it as your default database.

[@royalhorusbernal9629]

Amazon s3 and amazon fsx windows file server is the,considering that i want to up an application common with diferents enviroments like DEV,QA and PROD (bd,app, and server) o i need to consider both to begin? and price for year it is very expensive?

[@chidambaranathanmanian4183]

Hi, Could you Please provide the video on how to query Postgresql data in Athena (Postgres as a data source in Athena)

[@muhammadaliuppal1892]

when configuring output of crawler, I do not see the default database. What could be the reason for that?

[@stephenpalfreyman4755]

Time to redo this video for the new UI as it has completely changed now

[@BeABetterDev]

Thanks for letting me know!

[@birdsculptures]

thanks. Would this work for nested json files?

[@thomas-sinkala]

Any way I can use Athena to query Minio (S3 compatible storage)?

[@YuanDong]

This json is a simple flattened one. I wonder how it works with a more complex json schema.

[@debasishdeb0812]

Can we generate the SQL query using python script and how to query the data that are present in csv files that we upload in the S3 bucket

[@zuu2051]

How I can create multiple tables from multiple files (CSV) which contains in the same folder. Please help

[@pavansinghkushwa495]

Hello there, I want to extract data from redshift cluster using JDBC end point of it to AWS Athena to run SQL queries.. can we implement this..? if yes can you please make a video on this..?

[@gustavemuhoza4212]

Is it still the case that classifiers work with that particular type of json format? Tried to use s crawler and DataBrew on some deeply nested json and it did not work

[@richagoyal4681]

if i have added new file in s3 path and want to see the new data using athena query without crawler because crawler already exist then what command i can use in athena so that the data will refresh automatically without creating new crawler of re running existing crawler?

[@richagoyal4681]

Please reply if anyone knows

[@BeABetterDev]

Hi Richa, If you're just updating the data, you don't need to do anything to have it reflected in your athena query. The next time you query, the data will be present. However, if you change the schema (add a field, remove a field, update field), it won't be present until you re-run the crawler or change the athena table schema. Hope this helps.

[@krishind99]

Super cool. Is the process same for CSV file too? Can you extend this idea a little more and include RedShift in the process. Also a simple tutorial on EMR with these steps will be a delight. Thanks & keep up the great work

[@tonyme7426]

Nice

[@BeABetterDev]

Thanks tony!

[@csoutsource]

Strange, I do not have the option to "Connect Data Source"

[@elahehseyedain9056]

Me too!😞😧

[@marjan61717]

how can have a sample long JSON file to test it ?

[@collimarco]

Does AWS Athena uses S3 SELECT for the queries on S3?

[@joshuanicolas2002]

Can this be applied if i want to retrieved huge amount of files (jpg)

[@sangarajusukanya334]

hi can you explain if we delete the file in s3 path but still we need the data in athena table .

[@BeABetterDev]

Hi Sanagarju, if you delete the file in the s3 path it will no longer be queryable in Athena.

[@Aearewn]

Thank you for this channel. It's helped me fill huge gaps in my knowledge about AWS. Quick question: how did you create a .json array file without square brackets?

[@BeABetterDev]

Thanks so much for your support! A great website to generate JSON is Mockaroo (google it). After you generate your JSON, just delete the first [ and last ]

[@divyatirthadas]

Thanks for sharing this, somehow I don't see any data being returned. I checked my json file has the data. What am I missing?

[@BeABetterDev]

Hi Deepak, is this via the Athena AWS console? Or are you calling programmatically?

[@divyatirthadas]

@@BeABetterDev it's through the console.

[@yashshinde108]

Same. Zero records returned.

[@saleemibrahim5984]

Hey, I do not have the connect to data source option and it's written (No databases or tables found). I do not know what is the problem

[@BeABetterDev]

Hi Saleem, Did you accidentally delete the default database?

[@balipavankalyan5008]

In my case i'm getting the table.. but data is not populating

[@marbas9215]

Hi, thank you for the video, it was very helpful for me to set up my AWS Athena. I have a question: if the files on S3 that are linked to the Crawler are updated daily, and I set up the crawler to run daily, will Athena update the output daily as well? Or is that another process that I need to schedule? I don't do anything in Athena, i only use it to access the data on S3 through Tableau by connecting it to Athena

[@BeABetterDev]

Hi Mar, great question! If your crawler pulls in new data daily, then your Athena cluster should automatically pull this data in during its queries. You can easily recreate this scenario to test it out with a couple of small files. Have a great day!

[@ahmedabdellatif5845]

What if my JSON files in S3 are not simple formatted like yours? anything i can do to me it work? i have over 10000 json files i need to query. the format { "StartTime":1596475018000, "FileName":"testfile", "Info":{"language":"english"},{"location":"usa"}, . . . }

[@BeABetterDev]

Hey there Ahmed, Check out the answer on stackoverflow here: stackoverflow.com/questions/53314808/querying-nested-json-structures-in-aws-athena Hope this helps!

[@jasonwilson3329]

Thank you for the video. I have a couple of questions. Does Glue have to continue to run to pick up new files or does it just need to run once to establish the schema? Also I got the point testing the data with json that looks like this { "id": "05b10b2a-7dee-11ec-90d6-0242ac120031", "time": "2021-01-19T18:43:48Z", "source": "testing", "resources": [], "detailType": "Test Event 31", "detail": { "eventid": "05b10b2a-7dee-11ec-90d6-0242ac120031", "eventName": "test_event 31", "claimCheck": false, "eventTime": "2021-01-19T18:43:48Z", "version": "1.0" } } And I receive the error "Row is not a valid JSON Object - JSONException: A JSONObject text must end with '}' at 2 [character 3 line 1]". Is the because of the embedded object "detail". Glue seemed to detect the schema correctly

[@jasonwilson3329]

Nevermind -- I realized it was because my JSON spanned multiple lines. I also realized that I was able to query new files once Glue was set up without running the crawler again. Looking at the S3 results bucket, I see that there "unsaved" result files. Do these persistent until I manually delete them or are they cleared out regularly?

[@catherineaugustine7769]

@@jasonwilson3329 So, can you query using athena here ? My schema is similar to this, one object would span multiple lines. How do I query in this case ?

[@DiscoverWithHassan]

Will it work with .PDF files?

[@mohamedifthikhar7821]

How the Query results will be exported to S3 bucket?

[@BeABetterDev]

Hi, this is an automatic feature when using athena. Your query results are stored in the S3 bucket you specify during athena database setup.

[@mohamedbousselham2173]

do u have a course abt AWS Hands on on udemy ur skills explaining are the best

[@lidavid9781]

Is it possible to share your json file in your this video to let us to practice?

[@BeABetterDev]

Hi David, I unfortunately can't find the JSON file I used for this video. However there is a neat tool called 'Mockaroo' that lets you quickly created formatted JSON files filled with content. Check it out here: www.mockaroo.com/ Cheers

[@lidavid9781]

Thank you for your information. Your video is great and helpful.

[@vitache1276]

do you offer trainings

[@BeABetterDev]

Hi Vita, not at this time but I am currently have plans for a Lambda training course offering. Stay tuned!

[@vitache1276]

Thank you for the update.

[@DeviantDeveloper]

Sorry I was totally lost from the beginning. A more detailed step by step process would be useful. Where did you get the path from, what page was that? I can't find it (for example).

[@SelvarajuManivannan]

u quickly went too further without tell us about how to create bucket

[@siddharthsrivastava4491]

Great tutorial buddy!! Thank you very much