How to Query Your DynamoDB Table with SQL using Athena

DynamoDB is a NoSQL database that provides exceptional performance for OLTP workloads. However, it isn't suitable when trying to query datasets using broad criteria. In this video, I show you how to export your DynamoDB table and query it using SQL. You'll learn how to export your table into S3, set up a glue crawler, and finally interact with your data through Athena using SQL. I also show you how to query nested objects like DynamoDB Maps and Lists.
Athena queries - gist.github.com/beabetterdevv...
Sign up for my Newsletter to receive regular AWS updates mailinglist.beabetterdev.com
🎉SUPPORT BE A BETTER DEV🎉
Become a Patron: / beabetterdev
📚 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 Driven 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
🌎 Find me here:
Twitter - / beabetterdevv
Instagram - / beabetterdevv
Patreon - Donations help fund additional content - / beabetterdev
#SoftwareEngineer
#SoftwareDeveloper

[@marian6040]

Great explanation. Really appreciate it. Thanks

[@christianibiri]

Great video Daniel! thank you

[@BuildOnAWS]

Great video!

[@DisasterFixion]

Thanks man, great video, good catch with the upper/lowercase field names!

[@BeABetterDev]

Thanks and glad you enjoyed!

[@SergeyGrebenkin]

Very helpful, thanks!

[@Amanullah-lt6fq]

very helpful. Thank you

[@mariumbegum7325]

Fantastic explanation!

[@BeABetterDev]

Thanks Marium, Cheers!

[@mmurray4043]

Great video, I think I would have a source bucket with the json and normalise the data using glue into another bucket that I would point Athena to. This way you can just use standard SQL

[@Youtoo138]

Well done.Anyway I can be getting your POCs on weekly basis on par titular topics. Quite pay what you will be comfortable for the extra help

[@fehmianac]

Great video Thank you for your effort. I have a question, When we use single table design in DynamoDB, How we can separate partitionso glue table.?

[@dipuroy5613]

My DDB table was empty. Then I exported DDB to S3. Inserted some records in DDB. But not getting those new records into S3. Shouldn't it automatically sync the updated data to s3? If not, what to do to get the latest data into s3

[@florianlobady605]

Hello Daniel, great content as usual. What would you recommend to have it real time? Glue crawler is really asynchrone from what I know. Also to flatten Dynamodb schemas have you already used dynamic frame + relationalize? It does an amazing job

[@BeABetterDev]

Hi @flobady Thanks for commenting. For real time, I would suggest using DynamoDB Streams + Kinesis Firehose. Streams to capture change events or "NEW_IMAGES" of your records, and Kinesis firehose to buffer your data and deliver to S3 in chunks. Then, you can set up a glue crawler on a recurring 1min timer to periodically update the new data. Note that there is a new featue Glue that allows it to detect only the records that changed in your bucket and not the entire bucket. This makes it much more cost effective for incremental additions. Also, never tried dynamic frames yet, but thanks for sharing - I'll have to look into it. Cheers and hope this helps, Daniel

[@florianlobady605]

@@BeABetterDev thks for your feedback

[@sarahwbs]

I don't understand why you would do this. Why have a NoSQL database if you want to use SQL?

[@BeABetterDev]

Hi Sarah, this is a good question. Often times when working with NoSQL, we may still want to perform some analytics or auditing of our data. This type of setup allows you the best of both worlds - NoSQL for great key value performance, and also an ad-hoc data auditing ability with Athena. Hope this helped clarify, Daniel

[@akhan3682]

Excellent content but holy crap that white background [in every video] is brutal on the ole eyes for people with larger 4K gaming monitors. I can't do more than 20mins of yr content a day due to that.