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