AWS Athena to read Dynamodb backup data

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.