Wednesday, January 25, 2017

AWS Athena to read Dynamodb backup data

39 comments
Our data scientists wanted to read some data from Dynamodb. In order to do this, we had to increase the Read Capacity of the table to a high value and it was cost consuming. So we architected a system using Athena where data scientist could run their query whenever they want.

We used AWS datapipeline template to backup the dynamodb table. So, we had our data archived into S3 in multiple json formatted files. Here Athena came into picture. Athena is a new tool created by AWS which runs presto underneath. So we created a table in Athena using the Dynamodb backup location and started exploring data:

CREATE EXTERNAL TABLE IF NOT EXISTS default.test ( key string, sortkey string, data string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true')
LOCATION 's3://bucket-name/DynamodbDBBackup/2016-12-23-20-28-20/'

It created a table in Athena. It's time to run query.

select * from default.test limit 3;
Sample Data:

keysortkeydetails
{"s":"key1"}{"s":"sortkey1"}{"s":"{""keys"":[""abc""],""usage"":32,""temparature"":89,""size"":21}
{"s":"key2"}{"s":"sortkey1"}{"s":"{""keys"":[""abc""],""usage"":32,""temparature"":67,""size"":42}
{"s":"key1"}{"s":"sortkey2"}{"s":"{""keys"":[""abc""],""usage"":21,""temparature"":69,""size"":45}


Now we had to extract these data in such a way that we would get only values for key and sortkey and from details attribute we had to extract only usage.

As I said, Athena is a service for presto, we used presto function to extract data from json. There is a function is presto described at presto functions:
json_extract(jsonjson_path)

json_path expression can use the dot-notation and it takes $ as root path. More details are available at JsonPath

Using the above formula, we wrote our query:

SELECT cast(json_extract(key,'$.s') as varchar) as key ,
cast(json_extract(sortkey,'$.s') as varchar) as sortkey
, json_extract(replace(cast(json_extract(details,'$.s') as varchar),'""','"'),'$.usage') as usage
FROM test
limit 10;​

And that gave us the data as:


keysortkeyusage
key1sortkey132
key2sortkey132
key1sortkey221

It was really fun to use Athena.




39 Responses so far

  1. Unknown says:

    what was the performance difference between the DynamoDB vs Athena + S3?

  2. The actual time and effort taken to create this wonderful article were really great, I really appreciate that...
    Best Online Software Training Institute | AWS Training

  3. Hii… The Post is a Wonderful Opportunity for Beginners of Python Course. Take time To look at our Website.
    aws training in hyderabad

  4. very valuable information available in the blog.For AWS information visit this site.aws online training | aws training in hyderabad | aws online training in hyderabad

  5. Mumbai Escorts provides Independent Mumbai Escorts and stylish high class Mumbai

  6. Unknown says:

    Kolkata Escorts Service To Make Your Sex Life Memorabel

    Our escort and call girls in Kolkata are expertly qualified and known for their
    beauty and supportive characteristics.These girls can provide you with actual escort
    and call girl like sensation or first evening encounter.They can provide you with
    100% actual sweetheart as well sensation and you can have greatest fun.
    kolkata Escorts

  7. zara says:

    The strategy you have posted on this technology helped me to get into the AWS Training in Hyderabad

  8. Unknown says:


    Nice post. It is very useful and informative post.

    CEH Training In Hyderbad

  9. Amazon has a simple web services interface that you can use to store and retrieve any amount of data, at any time, from anywhere on the web. Amazon Web Services (AWS) is a secure cloud services platform, offering compute power, database storage, content delivery and other functionality to help businesses scale and grow.For more information visit : aws online training

  10. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. aws certification training is one of the best equipping skill in 2019.

  11. Anonymous says:

    I finally found great post here.I will get back here. I just added your blog to my bookmark sites. thanks.Quality posts
    amazon web services training is the crucial to invite the visitors to visit the web page, that's what this web page is providing.

  12. likitha says:

    We are tied directly into the sate’s renewal database which allows us to process your request almost instantly. buy essaysAmazon web services training in Hyderabad

  13. markson says:

    We have fabricated vocations of thousands of Data Science experts with 97.3% situation record in different MNCs in India and abroad. "Preparing to Job Placement" – is our specialty.data science course in pune

  14. This comment has been removed by the author.
  15. I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you! Coming to the area of focus. I would like to add one more interesting concept Amazon Web Services. Amazon Web Services is the best equip able course in 2019. Specifically AWS training is the right destination to master the course.

  16. ravali says:

    Well, the most on top staying topic is Data Science. Data science is one of the most promising technique in the growing world. I would like to add Data science training to the preference list. Out of all, Data science course in Mumbai
    is making a huge difference all across the country. Thank you so much for showing your work and thank you so much for this wonderful article.

  17. ameer says:

    Just saying thanks will not just be sufficient, for the fantastic lucidity in your writing. I will instantly grab your articles to get deeper into the topic. And as the same way ExcelR also helps organisations by providing
    Data science course based on practical knowledge and theoretical concepts. It offers the best value in training services combined with the support of our creative staff to provide meaningful solution that suits your learning needs.

  18. markson says:

    Most online business chiefs put resources into chatbots believing that their clients would not have the option to differentiate among programming and people.artificial intelligence course

  19. Anonymous says:

    I have checked this link this is really important for the people to get benefit from tableau online training

  20. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    pmp training

  21. seoexpert says:

    Such a very useful article. I have learn some new information.thanks for sharing.
    data scientist course in mumbai

  22. ravali says:

    I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!

    Data Analytics Course in Mumbai

  23. ravali says:

    I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.
    Data science course in mumbai

  24. Priyanka says:

    Attend The Data Science Course From ExcelR. Practical Data Science Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Course.
    ExcelR Data Science Course

  25. Manikanta says:

    Such a very useful Blog. Very interesting to read this article. I have learn some new information.thanks for sharing. know more about

  26. Priyanka says:

    Attend The PMP Certification From ExcelR. Practical PMP Certification Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The PMP Certification.
    ExcelR PMP Certification

  27. Priyanka says:

    Attend The Data Analytics Course in Bangalore From ExcelR. Practical Data Analytics Course in Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Course in Bangalore.
    ExcelR Data Analytics Course in Bangalore

  28. Priyanka says:

    Attend The PMP Certification in Abu Dhabi From ExcelR. Practical PMP Certification in Abu Dhabi Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The PMP Certification in Abu Dhabi.
    ExcelR PMP Certification in Abu Dhabi

  29. ravali says:

    Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    ExcelR Business Analytics Course

  30. Priyanka says:

    Attend The PMP Certification From ExcelR. Practical PMP Certification Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The PMP Certification.
    ExcelR PMP Certification

  31. Very nice blog here and thanks for post it.. Keep blogging...
    ExcelR data science training

  32. Really nice and interesting post. I was looking for this kind of information and enjoyed reading this one. Keep posting. Thanks for sharing.
    ExcelR data science course in mumbai

  33. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    data science course in mumbai

  34. Nice information, valuable and excellent work, as share good stuff with good ideas and concepts, lots of great information and inspiration, both of which I need, thanks to offer such a helpful information here. data science course

Leave a Reply

Labels