django-clickhouse-backend

Migrations in django-clickhouse-backend differ from standard RDBMS (like PostgreSQL) due to ClickHouse's architecture. Be aware of the following constraints:

• Constraints: Unique constraints and database-level Foreign Key (FK) constraints are not supported. While you can use ForeignKey in Django models, no enforcement happens at the database level.
• Indexes: Anonymous indexes or constraints (e.g., db_index, unique, index_together, unique_together) are not supported. You must provide explicit names for all indexes and constraints.
• Data Migrations: Data migrations are not automatically rolled back if an exception occurs.
• Field Type Alterations:
  • Changing a field type used in a PRIMARY KEY or ORDER BY clause is only allowed if the change is 'compatible' (i.e., value length is preserved and ordering is maintained). Incompatible changes will raise a DB::Exception.
  • Changing a field type used in an index is restricted. While changing from NOT NULL to Nullable is supported, the reverse (from Nullable to NOT NULL) is not supported and will raise a DB::Exception.

Since ClickHouse does not have auto-incrementing columns or unique constraints, Django's AutoField, SmallAutoField, and BigAutoField are mapped to the ClickHouse Int64 type.

When a primary key is not specified during insertion, the backend uses clickhouse_driver.idworker.id_worker to generate a unique ID using the Twitter Snowflake algorithm.

Configuring Snowflake ID Workers

To ensure uniqueness across multiple datacenters or processes, configure the following environment variables:

• CLICKHOUSE_WORKER_ID: An integer between 0 and 31 (default: 0).
• CLICKHOUSE_DATACENTER_ID: An integer between 0 and 31 (defaults to a random value if not provided).

If you need a custom, thread-safe ID worker, you can inherit from clickhouse.idworker.base.BaseIDWorker and set the CLICKHOUSE_ID_WORKER setting in your Django settings.py to the dotted import path of your instance.

Since ClickHouse is often used alongside an RDBMS, use a Django database router to automatically direct queries. A common pattern is to route all models that inherit from clickhouse_backend.models.ClickhouseModel to the ClickHouse database.

Example router implementation:

from clickhouse_backend.models import ClickhouseModel

class ClickHouseRouter:
    def __init__(self):
        self.route_model_names = set()
        # Logic to find all non-abstract subclasses of ClickhouseModel
        # ... (implementation details omitted for brevity)

    def db_for_read(self, model, **hints):
        if (model._meta.label_lower in self.route_model_names
                or hints.get("clickhouse")):
            return "clickhouse"
        return None

    def db_for_write(self, model, **hints):
        if (model._meta.label_lower in self.route_model_names
                or hints.get("clickhouse")):
            return "clickhouse"
        return None

    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if (f"{app_label}.{model_name}" in self.route_model_names
                or hints.get("clickhouse")):
            return db == "clickhouse"
        elif db == "clickhouse":
            return False
        return None

To use ClickHouse in Django, add clickhouse_backend to your INSTALLED_APPS and define the database in DATABASES. The ENGINE must be set to clickhouse_backend.backend.

Available configuration keys:

• ENGINE (required): clickhouse_backend.backend
• NAME: database name (default: default)
• HOST: database host (default: localhost)
• PORT: database port (default: 9000)
• USER: database user (default: default)
• PASSWORD: database password (default: empty)

Example configuration for a multi-db setup with PostgreSQL and ClickHouse:

INSTALLED_APPS = [
    # ...
    "clickhouse_backend",
    # ...
]
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "HOST": "localhost",
        "USER": "postgres",
        "PASSWORD": "123456",
        "NAME": "postgres",
    },
    "clickhouse": {
        "ENGINE": "clickhouse_backend.backend",
        "NAME": "default",
        "HOST": "localhost",
        "USER": "DB_USER",
        "PASSWORD": "DB_PASSWORD",
    }
}
DATABASE_ROUTERS = ["dbrouters.ClickHouseRouter"]

For distributed environments, use the following configuration options in DATABASES[...]['OPTIONS']:

• migration_cluster: Specifies the cluster name where the migration table should be created. If not specified, migrations are local.
• distributed_migrations: (Boolean) If True, a distributed migration table is created on all nodes, allowing migrations to be performed via a load balancer.
• TEST: Use this key to configure test database behavior for specific cluster nodes.

Distributed Table Example: To create a distributed table, define the Distributed engine in your model's Meta class and specify the cluster attribute.

class DistributedStudent(models.ClickhouseModel):
    name = models.StringField()
    score = models.Int8Field()

    class Meta:
        engine = models.Distributed(
            "cluster", 
            models.currentDatabase(), 
            Student._meta.db_table, 
            models.Rand()
        )
        cluster = "cluster"

DATABASES = {
    "default": {
        "ENGINE": "clickhouse_backend.backend",
        "HOST": "clickhouse-load-balancer",
        "OPTIONS": {
            "migration_cluster": "cluster",
            "distributed_migrations": True,
            "settings": {
                "mutations_sync": 2,
                "insert_distributed_sync": 1,
            },
        },
    }
}