Examples

Create First Collection

Create a collection, add a JSON document to it and run a simple SQL query from the command-line:

# run this from your shell
rock create collection hello_world
echo '{"name": "Jim Gray"}' | rock upload hello_world -
rock sql 'select * from hello_world'

Run SQL queries using the Python Client:

#!/usr/bin/python3

# connect to rockset and run a query
from rockset import Client, Q
rs = Client()
q = Q('SELECT * FROM hello_world LIMIT 10')
print(rs.sql(q))

Or use our Python Client Query builder to build queries using Python code expressions:

#!/usr/bin/python3

# connect to rockset and run a query
from rockset import Client, Q, F
rs = Client()
q = Q('hello_world').where(F['name'] == 'Jim Gray').limit(1)
print(rs.sql(q))

Create Collection from AWS S3

Create a collection from an AWS S3 bucket “app-events-realtime”, where every object contains a set of JSON objects in JSON-per-line format:

# run this from your shell
rock create collection hello_s3 \
    --aws_access_key_id=$AWS_ACCESS_KEY_ID \
    --aws_secret_access_key=$AWS_SECRET_ACCESS_KEY \
    s3://app-events-realtime

Code Example

Let’s dive right in and look at a sample code to see how one would use Rockset. Download and run this sample code to see for yourself.

This example creates a collection, loads sample documents into it, and demonstrates many of the query capabilities of Rockset. You can read more about Rockset Python Client here.

from rockset import Client, Q, F

rs = Client()

collection_name = 'example_collection'
try:
    collection = rs.Collection.create(collection_name)
except:
    # already exists
    collection = rs.Collection.retrieve(collection_name)

print(collection.describe())

# Insert some documents
doc1 = {
    '_id':
        'lz',
    'artist':
        'Led Zeppelin',
    'albums':
        [
            {
                'name':
                    'Led Zeppelin',
                'year':
                    1969,
                'sold':
                    50000000,
                'samplesongs':
                    [
                        'Good Times Bad Times',
                        'Babe I\'m Gonna Leave You',
                    ]
            }, {
                'name':
                    'Led Zeppelin II',
                'year':
                    1969,
                'sold':
                    100000000,
                'samplesongs':
                    [
                        'Whole Lotta Love',
                        'What Is and What Should Never Be',
                    ]
            }
        ],
    'bio':
        (
            'Formed in London in 1968, the group consisted '
            'of guitarist Jimmy Page, singer Robert Plant, '
            'bass player John Paul Jones, and drummer John Bonham.'
        ),
    'activefrom':
        1968,
    'activeto':
        1980,
    'members':
        4
}
doc2 = {
    '_id':
        'ec',
    'artist':
        'Eric Clapton',
    'albums':
        [
            {
                'name':
                    'Eric Clapton',
                'year':
                    1970,
                'sold':
                    30000000,
                'samplesongs':
                    [
                        'Bad Boy',
                        'Lonesome and a Long Way from Home',
                    ]
            }, {
                'name': '461 Ocean Boulevard',
                'year': 1974,
                'sold': 80000000,
                'samplesongs': [
                    'Motherless Children',
                    'Give Me Strength',
                ]
            }
        ],
    'bio':
        (
            'Eric Patrick Clapton, CBE (born 30 March 1945), '
            'is an English rock and blues guitarist, singer, '
            'and songwriter. Clapton has been referred to as '
            'one of the most important and influential guitarists '
            'of all time.'
        ),
    'activefrom':
        1962,
    'activeto':
        'present',
    'members':
        1
}
collection.add_docs([doc1, doc2])

print('*** Simple queries:')
print(
    rs.sql(
        Q(collection_name).where(F['artist'] == 'Eric Clapton')
        .select(F['artist'], F['bio'])
    )
)
print(
    rs.sql(
        Q(collection_name).where(F['activefrom'] > 1965)
        .select(F['artist'], F['bio'])
    )
)

print(
    rs.sql(
        Q(collection_name).where((F['members'] > 1) & (F['members'] < 5))
        .select(F['artist'], F['members'])
    )
)

print('** Nested queries:')
print(
    rs.sql(
        Q(collection_name)
        .where(F['albums'][:]['name'] == '461 Ocean Boulevard')
        .select(F['albums'], F['artist'])
    )
)

print('** Aggreagate queries:')
print(rs.sql(Q(collection_name).aggregate(F['activefrom'].avg())))
print(rs.sql(Q(collection_name).aggregate(F['artist'], F['activefrom'].avg())))

print('** Sort queries:')
print(
    rs.sql(
        Q(collection_name).highest(1, F['members'])
        .select(F['artist'], F['members'])
    )
)
print(
    rs.sql(
        Q(collection_name).lowest(1, F['members'])
        .select(F['artist'], F['members'])
    )
)