infi.clickhouse-orm 0.5.1

Defining Models

Models are defined in a way reminiscent of Django’s ORM:

from infi.clickhouse_orm import models, fields, engines

class Person(models.Model):

    first_name = fields.StringField()
    last_name = fields.StringField()
    birthday = fields.DateField()
    height = fields.Float32Field()

    engine = engines.MergeTree('birthday', ('first_name', 'last_name', 'birthday'))

It is possible to provide a default value for a field, instead of its “natural” default (empty string for string fields, zero for numeric fields etc.).

See below for the supported field types and table engines.

Using Models

Once you have a model, you can create model instances:

>>> dan = Person(first_name='Dan', last_name='Schwartz')
>>> suzy = Person(first_name='Suzy', last_name='Jones')
>>> dan.first_name
u'Dan'

When values are assigned to model fields, they are immediately converted to their Pythonic data type. In case the value is invalid, a ValueError is raised:

>>> suzy.birthday = '1980-01-17'
>>> suzy.birthday
datetime.date(1980, 1, 17)
>>> suzy.birthday = 0.5
ValueError: Invalid value for DateField - 0.5
>>> suzy.birthday = '1922-05-31'
ValueError: DateField out of range - 1922-05-31 is not between 1970-01-01 and 2038-01-19

Inserting to the Database

To write your instances to ClickHouse, you need a Database instance:

from infi.clickhouse_orm.database import Database

db = Database('my_test_db')

This automatically connects to http://localhost:8123 and creates a database called my_test_db, unless it already exists. If necessary, you can specify a different database URL and optional credentials:

db = Database('my_test_db', db_url='http://192.168.1.1:8050', username='scott', password='tiger')

Using the Database instance you can create a table for your model, and insert instances to it:

db.create_table(Person)
db.insert([dan, suzy])

The insert method can take any iterable of model instances, but they all must belong to the same model class.

Reading from the Database

Loading model instances from the database is simple:

for person in db.select("SELECT * FROM my_test_db.person", model_class=Person):
    print person.first_name, person.last_name

Do not include a FORMAT clause in the query, since the ORM automatically sets the format to TabSeparatedWithNamesAndTypes.

It is possible to select only a subset of the columns, and the rest will receive their default values:

for person in db.select("SELECT first_name FROM my_test_db.person WHERE last_name='Smith'", model_class=Person):
    print person.first_name

for row in db.select("SELECT max(height) as max_height FROM my_test_db.person"):
    print row.max_height

Counting

The Database class also supports counting records easily:

>>> db.count(Person)
117
>>> db.count(Person, conditions="height > 1.90")
6

Field Types

Currently the following field types are supported:

Table Engines

Each model must have an engine instance, used when creating the table in ClickHouse.

To define a MergeTree engine, supply the date column name and the names (or expressions) for the key columns:

engine = engines.MergeTree('EventDate', ('CounterID', 'EventDate'))

You may also provide a sampling expression:

engine = engines.MergeTree('EventDate', ('CounterID', 'EventDate'), sampling_expr='intHash32(UserID)')

A CollapsingMergeTree engine is defined in a similar manner, but requires also a sign column:

engine = engines.CollapsingMergeTree('EventDate', ('CounterID', 'EventDate'), 'Sign')

For a SummingMergeTree you can optionally specify the summing columns:

engine = engines.SummingMergeTree('EventDate', ('OrderID', 'EventDate', 'BannerID'),
                                  summing_cols=('Shows', 'Clicks', 'Cost'))

engine = engines.MergeTree('EventDate', ('CounterID', 'EventDate'),
                           replica_table_path='/clickhouse/tables/{layer}-{shard}/hits',
                           replica_name='{replica}')