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:
key | sortkey | details |
{"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
(json, json_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:
key | sortkey | usage |
key1 | sortkey1 | 32 |
key2 | sortkey1 | 32 |
key1 | sortkey2 | 21 |
It was really fun to use Athena.
what was the performance difference between the DynamoDB vs Athena + S3?
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
Hii… The Post is a Wonderful Opportunity for Beginners of Python Course. Take time To look at our Website.
aws training in hyderabad
Very excellent and informative
AWS Training in Chennai
AWS Training in Bangalore
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
Mumbai Escorts provides Independent Mumbai Escorts and stylish high class Mumbai
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
Meowchat apk Nomao camera apk download Cartoon Camera Android
The strategy you have posted on this technology helped me to get into the AWS Training in Hyderabad
Nice post. It is very useful and informative post.
CEH Training In Hyderbad
Great Article. Thanks for sharing info.
SAP ABAP Training in Hyderabad
SAP FICO Training in Hyderabad
AWS Training in Hyderabad
Salesforce Training in Hyderabad
Selenium Training in Hyderabad
Great Article. Thanks for sharing info.
Workday Training in Hyderabad
IELTS Coaching in Hyderabad
Salesforce Training in Hyderabad
SAP FICO Training in Hyderabad
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
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.
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.
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
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
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.
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.
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.
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
I have checked this link this is really important for the people to get benefit from tableau online training
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
Nice Post...I have learn some new information.thanks for sharing.
ExcelR data analytics course in Pune | business analytics course | data scientist course in Pune
Such a very useful article. I have learn some new information.thanks for sharing.
data scientist course in mumbai
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
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
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
Such a very useful Blog. Very interesting to read this article. I have learn some new information.thanks for sharing. know more about
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
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
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
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
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
Very nice blog here and thanks for post it.. Keep blogging...
ExcelR data science training
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
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
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
I have to search sites with relevant information ,This is a
wonderful blog,These type of blog keeps the users interest in
the website, i am impressed. thank you.
machine learning course in hyderabad
Attend The Data Science Training Bangalore From ExcelR. Practical Data Science Training Bangalore Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Science Training Bangalore.
Data Science Training Bangalore
Data Science Interview Questions
wow, great, I was wondering how to cure acne naturally. and found your site by google, learned a lot, now I am a bit clear. I’ve bookmarked your site. keep us updated.
<a href="https://www.excelr.com/business-analytics-training-in-pune/”> ExcelR Courses </a>
The blog has very nice information and amazing facts to read. Thanks for sharing this post! You can also check our blog :
Banquets in Indore
Islam, one among largest religions in the world also the one that is misunderstood all time. There are several sources including political, economic & media, framed Islam as misleading religion. And the truth about muslims are always hidden from the light.
Truth About Islam
Truth About Muslims
Islamic Sharia Law
Women In Islam
Why Wear Hijab
Quran And Science
Muslims Are Not Terrorists
Polygamy In Islam
the holy book of islam
Great Article
Cloud Computing Projects
Networking Projects
Final Year Projects for CSE
JavaScript Training in Chennai
JavaScript Training in Chennai
The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training
Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
workday studio online training
best workday studio online training
top workday studio online training
Attend The Data Analyst Course From ExcelR. Practical Data Analyst Course Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analyst Course.
Data Analyst Course
Great post i must say and thanks for the information. Education is definitely a sticky subject. However, is still among the leading topics of our time. I appreciate your post and look forward to more.data science course in Hyderabad
I have express a few of the articles on your website now, and I really like your style of blogging. I added it to my favorite’s blog site list and will be checking back soon…
Machine Learning Courses in Pune I really enjoy reading and also appreciate your work.
I have bookmarked your website because this site contains valuable information in it. I am really happy with articles quality and presentation. Thanks a lot for keeping great stuff. I am very much thankful for this site.
THANK YOU FOR VISITING MY WEBSITE:-
russian escorts in gurgaon
housewife escorts in gurgaon
gurgaon escort services
gurgaon escorts
escorts in gurgaon
escort services in gurgaon
gurgaon call girls
call girls in gurgaon
escorts in lucknow
lucknow escorts
call girls in lucknow
escort service in lucknow
russian escorts in lucknow
lucknow escort services
lucknow call girls
Impressive blog with lovely information. really very useful article for us thanks for sharing such a wonderful blog. data science training in Hyderabad
Very nice blogs!!! i have to learning for lot of information for this sites…Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, data sciecne course in hyderabad
Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking Best data science courses in hyerabad
Attend The Business Analytics Courses From ExcelR. Practical Business Analytics Courses Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Data Analytics Courses.
Business Analytics Courses
Very nice blogs!!! i have to learning for lot of information for this sites…Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, data sciecne course in hyderabad
This Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing, data science online training
Online casino
We have compiled online casino games from leading casino camps. Popular Slot Games Already here To keep the players entertained and to be able to use them fully, no matter what type of bet, ufabet is fully available. And you will find special promotions That we bring to you more than any other Just apply to be a member with ufabet, you will receive many privileges. ufabet
Online casino
We have compiled online casino games from leading casino camps. Popular Slot Games Already here To keep the players entertained and to be able to use them fully, no matter what type of bet, ufabet is fully available. And you will find special promotions That we bring to you more than any other Just apply to be a member with ufabet, you will receive many privileges. ufabet
I strongly believe in reading your blog. Thanks for taking the time to explain the useful information,
technical skills required for software testing
rpa in banking
data science languages
php languages
digital marketing interview questions and answers
Wonderful! Keep up the Good work.
hello sir,
thanks for giving that type of information. I am really happy to visit your blog.Leading Solar company in Andhra Pradesh
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!PMP Certification