repository·develop·Indexed 19 days ago
https://github.com/infinidat/infi.clickhouse_ormA simple Object-Relational Mapper (ORM) for the ClickHouse database. It enables developers to define Python classes as database models to facilitate data insertion and querying via a Pythonic query builder or raw SQL. Key features include dynamic model creation from existing tables, queryset filtering, and aggregation.
infi.clickhouse_orm is a simple Object-Relational Mapper (ORM) designed for working with ClickHouse. It enables you to define Python model classes that represent ClickHouse tables, allowing you to read from and write instances of these classes directly to the database.System models are read-only abstractions used to implement system functionality and provide access to information about how the ClickHouse system is operating. They map to ClickHouse system tables.
Currently, the SystemPart class is supported, which maps to the system.parts table and provides methods to manage table partitions.
You can define fields that are calculated by the database using the alias or materialized arguments. These arguments must take an expression (either via the F object or an SQL string).
alias: Calculated on the fly during query execution.materialized: Calculated at insertion time and stored on disk.Important Constraints & Behaviors:
alias and materialized are mutually exclusive with default and readonly.Database.insert().infi.clickhouse_orm.utils.NO_VALUE because their values are only known after database processing.SELECT *. You must explicitly list the field names in your SQL query to retrieve them.class Event(Model):
created = DateTimeField()
# Materialized: stored on disk
created_date = DateTimeField(materialized=F.toDate(created))
name = StringField()
# Alias: calculated on the fly
normalized_name = StringField(alias=F.upper(F.trim(name)))
engine = Memory()
# For older ORM versions, use SQL strings:
# created_date = DateTimeField(materialized="toDate(created)")A queryset is a lazy object representing a database query for a specific Model. It does not execute the query until you iterate over it. To create a base queryset for a model, use Model.objects_in(database).
Example:
qs = Person.objects_in(database)
for person in qs:
print(person.first_name, person.last_name)qs = Person.objects_in(database)
for person in qs:
print(person.first_name, person.last_name)The library provides specialized model types for specific ClickHouse engines:
Extends Model and is designed for use with the ClickHouse Buffer engine. It shares most functionality with the standard Model class, including to_dict, to_tsv, and from_tsv methods.
Extends Model and is designed for the Merge engine. It predefines a virtual _table column and controls row insertion behavior to match the requirements of the Merge engine.
Both types support standard ORM operations like objects_in(database) and schema generation via create_table_sql(db).
The Full Text Search implementation uses a Fragment model to store individual words and their metadata. This allows for efficient searching by matching normalized 'stems' rather than raw words.
language: LowCardinalityField(StringField) (default: 'EN')document: LowCardinalityField(StringField) (Identifies the source text)idx: UInt64Field (The running word number within the document)word: StringField (The original word as it appears in text)stem: StringField (The normalized version of the word used for matching)MergeTree engine uses order_by=(stem, document, idx) to allow efficient lookups of stems. It is partitioned by language.(document, idx) using Index.minmax() with a granularity of 1 to speed up searches by document and fragment position.class Fragment(Model):
language = LowCardinalityField(StringField(default='EN'))
document = LowCardinalityField(StringField())
idx = UInt64Field()
word = StringField()
stem = StringField()
# An index for faster search by document and fragment idx
index = Index((document, idx), type=Index.minmax(), granularity=1)
# The primary key allows efficient lookup of stems
engine = MergeTree(order_by=(stem, document, idx), partition_key=('language',))The DB Explorer example serves as a reference for the following infi.clickhouse_orm patterns:
Database.get_model_for_table to generate ORM models from existing ClickHouse tables at runtime.Expressions are core building blocks composed of functions, operators, and model fields. They are used to define logic in several key areas of the ORM:
default, alias, and materialized values.MergeTree family engines.filter(), exclude(), order_by(), aggregate(), and limit_by().Expressions can be inspected by calling the .to_sql() method or by converting the object to a string to see the underlying ClickHouse SQL representation.
The DistributedModel is used when working with ClickHouse Distributed engines. Since distributed tables do not store data themselves, they must be linked to a storage model.
Automatic Fix: If your DistributedModel does not explicitly define a storage table, you can call fix_engine() to automatically find the first non-distributed model in the superclass hierarchy and set it as the engine's storage table.
Explicit Configuration: Alternatively, you can explicitly pass the storage model to the Distributed engine during definition.
class Foo(Model):
id = UInt8Field(1)
# Option 1: Automatic fix
class FooDistributed(Foo, DistributedModel):
engine = Distributed('my_cluster')
FooDistributed.engine.table
# None
FooDistributed.fix_engine()
FooDistributed.engine.table
# <class '__main__.Foo'>
# Option 2: Explicit definition
class FooDistributedVerbose(Foo, DistributedModel):
engine = Distributed('my_cluster', Foo)Fields in a model can be customized with several options:
default parameter to override the natural default (e.g., StringField(default="anonymous")).NullableField to allow null values in the database.materialized=expression to create read-only fields calculated from other fields. These are not sent during inserts.alias=expression to create read-only fields calculated by ClickHouse on the fly. These are not physically stored.# Default value
first_name = StringField(default="anonymous")
# Nullable field
birthday = NullableField(DateField())
# Materialized field (read-only, calculated from other fields)
year_born = Int16Field(materialized=F.toYear(birthday))
# Alias field (read-only, calculated on the fly by ClickHouse)
weekday_born = UInt8Field(alias=F.toDayOfWeek(birthday))To write data, first create the table using db.create_table(ModelClass), then use db.insert() with an iterable of model instances. All instances in the list must belong to the same model class.
db.create_table(Person)
db.insert([dan, suzy])By default, filter and exclude conditions are added to the WHERE clause. For better aggregation performance in ClickHouse, you can add conditions to the PREWHERE section by passing prewhere=True to the filter/exclude method.
Example:
qs = Person.objects_in(database).filter(F.like(Person.first_name, 'V%'), prewhere=True)qs = Person.objects_in(database).filter(F.like(Person.first_name, 'V%'), prewhere=True)