DQL - A Query Language for DynamoDB

27 February 2014


One of the main pain-points of DynamoDB is the lack of a CLI. Banging out one-off queries is a standard part of being a DBA, so what happens when you don't have a CLI? Well, like me, you'll probably end up writing short scripts that call the API.

Looking up a user record:

import boto
from pprint import pprint

conn = boto.dynamodb2.connect_to_region('us-west-1')
table = Table('users', connection=conn)
result = table.batch_get(keys=[{'userid': 'dsa'}])
pprint(dict(list(result)[0]))

Count number of users with a last name starting with 'A':

import boto

conn = boto.dynamodb2.connect_to_region('us-west-1')
table = Table('users', connection=conn)
count = table.query_count(lastname__beginswith='A', index='name-index')
print count

Update a value:

import boto
from pprint import pprint

conn = boto.dynamodb2.connect_to_region('us-west-1')
key = {
    'userid': {
        'S': 'dsa'
    }
}
data = {
    'Action': 'ADD',
    'Value': {
        'N': '1',
    },
}
result = conn.update_item('users', key, data, return_values='ALL_NEW')
pprint(dict(list(result)[0]))

That gets old real fast.

Fortunately, DynamoDB has released a newer version of the API based on expressions that looks a lot like SQL. The pieces are all there, but there isn't anything to glue together the UpdateExpression, ExpressionAttributeValues, etc. So I wrote DQL to be that glue

Update 2015-11-24: The syntax of the queries below have been updated for the 0.5.x releases of DQL

Looking up a user record:

us-west-1> SELECT * FROM users WHERE id = 'dsa';

Count number of users with a last name starting with 'A':

us-west-1> SELECT count(*) FROM users WHERE begins_with(lastname, 'A');

Update a value:

us-west-1> UPDATE users SET popular = true ADD followers 1 WHERE id = 'dsa';

And since it's a CLI, there's inline help:

us-west-1> help select

    Select items from a table by querying an index

    SELECT
        [ CONSISTENT ]
        attributes
        FROM tablename
        [ KEYS IN primary_keys | WHERE expression ]
        [ USING index ]
        [ LIMIT limit ]
        [ ORDER BY field ]
        [ ASC | DESC ]
        [ SAVE file.json ]

    Examples
    --------
    SELECT * FROM foobars SAVE out.json;
    SELECT * FROM foobars WHERE foo = 'bar';
    SELECT count(*) FROM foobars WHERE foo = 'bar';
    SELECT * FROM foobars KEYS IN 'id1', 'id2';
    SELECT * FROM foobars KEYS IN ('hkey', 'rkey1'), ('hkey', 'rkey2');
    SELECT CONSISTENT * foobars WHERE foo = 'bar' AND baz >= 3;
    SELECT * foobars WHERE foo = 'bar' AND attribute_exists(baz);
    SELECT * foobars WHERE foo = 1 AND NOT (attribute_exists(bar) OR attribute_exists(baz));
    SELECT foo, bar FROM foobars WHERE id = 'a' AND ts < 100 USING 'ts-index';
    SELECT * FROM foobars WHERE foo = 'bar' LIMIT 50 DESC;

DQL is a python package, so to install just run pip install dql and then dql. Feedback welcome. If you'd like to request features or file bugs, do so on github.

Code: github.com/mathcamp/dql
Docs: dql.rtfd.org

comments powered by Disqus
© 2014 Steven Arcangeli - Hosted by Github Pages