### Install django-clickhouse-backend from source

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Clone the repository and install locally if you need to work with the source code or a specific version.

```shell
git clone https://github.com/jayvynl/django-clickhouse-backend
cd django-clickhouse-backend
python setup.py install
```

--------------------------------

### Install Project Dependencies

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

Install the project's dependencies in editable mode. This is typically done after cloning the repository and setting up the development environment.

```shell
pip install -e .
```

--------------------------------

### Install django-clickhouse-backend via pip

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Use this command to install the package directly from PyPI.

```shell
pip install django-clickhouse-backend
```

--------------------------------

### Install and configure pre-commit

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

Install pre-commit for managing pre-commit hooks and configure it to run automatically before commits. This helps maintain code quality and consistency.

```shell
pip install pre-commit
pre-commit install --install-hooks
```

--------------------------------

### Install tox

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

Install the tox testing tool, which is used for managing and running tests in different environments.

```shell
pip install tox
```

--------------------------------

### Django ClickHouse RunSQL INSERT Example

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Migrations.md

Demonstrates how to use `migrations.RunSQL` for INSERT operations in Django with ClickHouse, emphasizing the use of params and the VALUES clause. This example shows both forward and backward migration SQL.

```python
migrations.RunSQL(
      # forwards
      (
          [
              "INSERT INTO i_love_ponies (id, special_thing) VALUES;",
              [(1, "Django"), (2, "Ponies")],
          ],
          (
              "INSERT INTO i_love_ponies (id, special_thing) VALUES;",
              [(3, "Python")],
          ),
      ),
      # backwards
      [
          "ALTER TABLE i_love_ponies DELETE WHERE special_thing = 'Django';",
          ["ALTER TABLE i_love_ponies DELETE WHERE special_thing = 'Ponies';", None],
          (
              "ALTER TABLE i_love_ponies DELETE WHERE id = %s OR special_thing = %s;",
              [3, "Python"],
          ),
      ],
  )
```

--------------------------------

### Start ClickHouse Cluster with Docker Compose

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

Start the ClickHouse cluster using Docker Compose. This is a prerequisite for running the project's tests and development.

```shell
docker compose up -d
```

--------------------------------

### Configure Django Settings for ClickHouse Tests

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Example Django settings configuration for testing with ClickHouse, enabling synchronous mutations and fake transactions.

```python
# settings.py
DATABASES = {
    "clickhouse": {
        "ENGINE": "clickhouse_backend.backend",
        "NAME": "test_db",
        "OPTIONS": {
            "settings": {
                "mutations_sync": 1,  # Synchronous mutations for tests
            }
        },
        "TEST": {
            "fake_transaction": True  # Pretend to support transactions
        }
    }
}
```

--------------------------------

### Query Across All Shards

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Example of performing queries and aggregations on distributed ClickHouse tables using Django ORM.

```python
DistributedStudent.objects.filter(score__gte=50)
```

```python
DistributedStudent.objects.aggregate(avg_score=models.Avg('score'))
```

--------------------------------

### Query django_migrations Table

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Example of querying the `django_migrations` table to view applied migrations, including their ID, app name, migration name, and applied timestamp.

```sql
> select * from django_migrations;
```

--------------------------------

### Apply Django Migrations to ClickHouse

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Command to apply Django migrations to the ClickHouse database. For clustered setups, apply to all nodes.

```bash
python manage.py migrate --database clickhouse
```

```bash
python manage.py migrate --database clickhouse
python manage.py migrate --database s1r2
python manage.py migrate --database s2r1
python manage.py migrate --database s2r2
```

--------------------------------

### Django Test Case with ClickHouse

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Example Django test case demonstrating CRUD operations on ClickHouse models, utilizing synchronous mutation settings for tests.

```python
# tests.py
from django.test import TestCase


class EventTestCase(TestCase):
    databases = {"default", "clickhouse"}

    def setUp(self):
        Event.objects.create(ip="127.0.0.1", port=8080, protocol="HTTP")

    def test_event_query(self):
        self.assertEqual(Event.objects.count(), 1)
        event = Event.objects.get(port=8080)
        self.assertEqual(event.protocol, "HTTP")

    def test_event_update(self):
        # Use settings for synchronous mutation in test
        Event.objects.filter(port=8080).settings(mutations_sync=1).update(protocol="HTTPS")
        event = Event.objects.get(port=8080)
        self.assertEqual(event.protocol, "HTTPS")

    def test_event_delete(self):
        Event.objects.filter(port=8080).settings(mutations_sync=1).delete()
        self.assertEqual(Event.objects.count(), 0)
```

--------------------------------

### Custom Migration with RunSQL

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Example of a custom Django migration using RunSQL to execute forward and reverse SQL statements, including inserting data with a VALUES clause.

```python
from django.db import migrations

class Migration(migrations.Migration):
    dependencies = [
        ('myapp', '0001_initial'),
    ]

    operations = [
        migrations.RunSQL(
            # Forward SQL with VALUES clause
            (
                [
                    "INSERT INTO myapp_event (id, protocol, port) VALUES;",
                    [(1, "HTTP", 80), (2, "HTTPS", 443)],
                ],
            ),
            # Reverse SQL
            [
                "ALTER TABLE myapp_event DELETE WHERE id IN (1, 2);",
            ],
        ),
    ]
```

--------------------------------

### Define ReplicatedMergeTree Model

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Use ReplicatedMergeTree for clustered ClickHouse setups. Specify replication path, replica name, and optionally a cluster name in the Meta class.

```python
class ReplicatedLog(models.ClickhouseModel):
    timestamp = models.DateTime64Field()
    data = models.StringField()

    class Meta:
        engine = models.ReplicatedMergeTree(
            "/clickhouse/tables/{uuid}/{shard}",
            "{replica}",
            order_by="timestamp"
        )
        cluster = "my_cluster"
```

--------------------------------

### ClickHouse Schema Alter Exception Example

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Migrations.md

Illustrates a DB::Exception that can occur when attempting an unsafe ALTER operation on a primary key column in ClickHouse.

```shell
DB::Exception: ALTER of key column id from type FixedString(100) to type FixedString(99) is not safe because it can change the representation of primary key.
```

--------------------------------

### Perform CRUD Operations on Distributed Table

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Execute Create, Read, Update, and Delete operations on a distributed ClickHouse table using Django's ORM. This example demonstrates bulk creation, counting, updating, and deleting records.

```python
students = DistributedStudent.objects.bulk_create([DistributedStudent(name=f"Student{i}", score=i * 10) for i in range(10)])
assert DistributedStudent.objects.count() == 10
DistributedStudent.objects.filter(id__in=[s.id for s in students[5:]]).update(name="lol")
DistributedStudent.objects.filter(id__in=[s.id for s in students[:5]]).delete()
```

--------------------------------

### Enum Field Usage Example

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Demonstrates defining and using Enum fields with Django models. Supports integer and string values for choices and querying. Note that `return_int` defaults to `True`.

```python
from clickhouse_backend import models
from django.db.models import IntegerChoices

class EnumTest(models.ClickhouseModel):
    class Fruit(IntegerChoices):
        banana = 1, 'banana'
        pear = 2, 'pear'
        apple = 3, 'apple'

    enum = models.EnumField(choices=Fruit.choices, return_int=False)
    
    def __str__(self):
        return str(self.enum)


EnumTest.objects.bulk_create([
    EnumTest(enum=1),
    EnumTest(enum='pear'),
    EnumTest(enum=b'apple')
])
# [<EnumTest: 1>, <EnumTest: pear>, <EnumTest: b'apple'>]

EnumTest.objects.filter(enum=1)
# <QuerySet [<EnumTest: banana>]>
EnumTest.objects.filter(enum='pear')
# <QuerySet [<EnumTest: pear>]>
EnumTest.objects.filter(enum=b'apple')
# <QuerySet [<EnumTest: apple>]>
EnumTest.objects.filter(enum__gt=1)
# <QuerySet [<EnumTest: pear>, <EnumTest: apple>]>
EnumTest.objects.filter(enum__gte='pear')
# <QuerySet [<EnumTest: pear>, <EnumTest: apple>]>
EnumTest.objects.filter(enum__contains='ana')
# <QuerySet [<EnumTest: banana>]>
```

--------------------------------

### Run Project Tests

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Clone the repository, set up Docker, and run the project's tests. For comprehensive testing across different Python and Django versions, use tox.

```shell
$ git clone https://github.com/jayvynl/django-clickhouse-backend
$ cd django-clickhouse-backend
# docker and docker-compose are required.
$ docker-compose up -d
$ python tests/runtests.py
# run test for every python version and django version
$ pip install tox
$ tox
```

--------------------------------

### Utilize PREWHERE and SETTINGS for ClickHouse Optimizations

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Demonstrates using ClickHouse's PREWHERE clause for efficient pre-filtering and the SETTINGS clause for query hints. `mutations_sync=1` is useful for ensuring synchronous updates/deletes, especially in testing.

```python
from clickhouse_backend import models


class Event(models.ClickhouseModel):
    timestamp = models.DateTime64Field()
    category = models.StringField(low_cardinality=True)
    data = models.StringField()

    class Meta:
        engine = models.MergeTree(order_by="timestamp")
```

```python
# PREWHERE - filters data before reading columns (more efficient)
# Use for highly selective filters on indexed columns
Event.objects.prewhere(category="error").filter(data__contains="timeout")
```

```python
# Chain multiple prewhere conditions
Event.objects.prewhere(
    category="error"
).prewhere(
    timestamp__gte="2024-01-01"
).filter(
    data__contains="database"
)
```

```python
# SETTINGS - pass ClickHouse query settings
Event.objects.settings(max_threads=4).all()
```

```python
# mutations_sync for synchronous updates/deletes (important for testing)
Event.objects.filter(category="debug").settings(mutations_sync=1).delete()
```

```python
Event.objects.filter(category="info").settings(mutations_sync=1).update(category="debug")
```

```python
# Combine PREWHERE and SETTINGS
Event.objects.prewhere(
    category="error"
).settings(
    max_execution_time=30,
    max_memory_usage=1000000000
).filter(
    timestamp__gte="2024-01-01"
)
```

--------------------------------

### MapField Lookups: len

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Retrieves the number of key-value pairs in a map. Use this to get the size of the map for filtering or display.

```python
MapModel.objects.values('map__len')
```

--------------------------------

### Create and Query IP Address Data

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Demonstrates creating records with IP address fields and performing various queries using filters like equality, greater than, and contains.

```python
IPTest.objects.create([
    IPTest(ipv4='1.2.3.4',ipv6='1.2.3.4',ip='1.2.3.4'),
    IPTest(ipv4='2.3.4.5',ipv6='2.3.4.5',ip='2.3.4.5'),
])
# [<IPTest: 1.2.3.4>, <IPTest: 2.3.4.5>]
IPTest.objects.filter(ipv4='1.2.3.4')
# <QuerySet [<IPTest: 1.2.3.4>]>
IPTest.objects.filter(ipv6='1.2.3.4')
# <QuerySet [<IPTest: 1.2.3.4>]>
IPTest.objects.filter(ip='1.2.3.4')
# <QuerySet [<IPTest: 1.2.3.4>]>

IPTest.objects.filter(ipv6__gt='1.2.3.4')
# <QuerySet [<IPTest: 2.3.4.5>]>

ip = IPTest.objects.get(ipv6__contains='4.5')
ip.ipv4, ip.ipv6, ip.ip
# ('2.3.4.5', '::ffff:203:405', '2.3.4.5')
```

--------------------------------

### Create and Query User Preferences

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Demonstrates creating a UserPreferences object with nested dictionary fields and querying specific keys or values within those dictionaries. Use for managing user-specific settings and scores.

```python
UserPreferences.objects.create(
    user_id=1,
    settings={
        "theme": "dark",
        "language": "en",
        "notifications": "enabled"
    },
    scores={"math": 95, "science": 88, "history": 92}
)
```

```python
prefs = UserPreferences.objects.get(user_id=1)
print(prefs.settings)  # {'theme': 'dark', 'language': 'en', ...}
```

```python
UserPreferences.objects.filter(settings__has_key="theme")
```

```python
UserPreferences.objects.values('settings__keys')    # Get all keys
```

```python
UserPreferences.objects.values('settings__values')  # Get all values
```

```python
UserPreferences.objects.values('settings__len')     # Map size
```

```python
UserPreferences.objects.values('settings__theme')   # Get specific key value
```

```python
UserPreferences.objects.filter(scores__math__gt=90)
```

--------------------------------

### Show Create Table SQL for django_migrations

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

This SQL command displays the schema for the `django_migrations` table in ClickHouse, which is created upon the first migration.

```sql
> show create table django_migrations;
```

--------------------------------

### DateTime and Date Lookups

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Demonstrates filtering QuerySets by year, month, day, and hour for DateTime fields. Also shows how to extract dates and datetimes.

```python
TimeSeriesModel.objects.filter(event_time__year=2024)
TimeSeriesModel.objects.filter(event_time__month=1)
TimeSeriesModel.objects.filter(event_time__day=15)
TimeSeriesModel.objects.filter(event_time__hour=10)
```

```python
TimeSeriesModel.objects.dates('event_date', 'month')
TimeSeriesModel.objects.datetimes('event_time', 'hour')
```

--------------------------------

### List Available Tox Environments

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

List all available test environments configured in tox. This helps in understanding the different Python and Django version combinations supported for testing.

```shell
tox -l
```

--------------------------------

### Define Nested ArrayField in Django Model

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Example of defining a deeply nested ArrayField in a Django ClickHouse model. Note that Nullable and LowCardinality are not supported for ArrayField.

```python
from clickhouse_backend import models

class NestedArrayModel(models.ClickhouseModel):
    array = models.ArrayField(
        base_field=models.ArrayField(
            base_field=models.ArrayField(
                models.UInt32Field()
            )
        )
    )
```

--------------------------------

### Show Create Table SQL for Event Model

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

This SQL command displays the schema for the `event` table as defined by the Django model, including fields, indexes, and constraints.

```sql
> show create table event;
```

--------------------------------

### Configure ClickHouse Database in Django Settings

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Add the clickhouse_backend to INSTALLED_APPS and define the 'clickhouse' database connection in settings.py. Ensure connection pooling and necessary settings like 'mutations_sync' are configured.

```python
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",
        "PORT": 9000,
        "USER": "default",
        "PASSWORD": "",
        "OPTIONS": {
            "connections_min": 10,
            "connections_max": 100,
            "settings": {
                "mutations_sync": 1,  # Required for testing mutations
            }
        },
        "TEST": {
            "fake_transaction": True
        }
    }
}
```

--------------------------------

### Define IP Address Fields in Django Model

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Example of defining IPv4Field, IPv6Field, and GenericIPAddressField in a Django ClickHouse model. GenericIPAddressField supports unpack_ipv4 for IPv4 mapped addresses.

```python
from clickhouse_backend import models

class IPTest(models.ClickhouseModel):
    ipv4 = models.IPv4Field()
    ipv6 = models.IPv6Field()
    ip = models.GenericIPAddressField(unpack_ipv4=True)

    def __str__(self):
        return str(self.ip)
```

--------------------------------

### Generate Django Migrations

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Run `python manage.py makemigrations` to generate migration files. Then, execute `python manage.py migrate --database clickhouse` to apply these migrations to the ClickHouse database.

```shell
$ python manage.py makemigrations
```

```shell
$ python manage.py migrate --database clickhouse
```

--------------------------------

### EnumField with IntegerChoices

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Defines Enum8 and Enum16 fields integrated with Django's IntegerChoices. Supports creating and querying by integer value or string label. Use `return_int=False` to get string labels.

```python
from clickhouse_backend import models
from django.db.models import IntegerChoices


class LogEntry(models.ClickhouseModel):
    class Severity(IntegerChoices):
        DEBUG = 1, 'debug'
        INFO = 2, 'info'
        WARNING = 3, 'warning'
        ERROR = 4, 'error'
        CRITICAL = 5, 'critical'

    class Status(IntegerChoices):
        PENDING = 0, 'pending'
        PROCESSING = 1, 'processing'
        COMPLETED = 2, 'completed'
        FAILED = 3, 'failed'

    message = models.StringField()
    # return_int=False returns string label instead of int value
    severity = models.EnumField(choices=Severity.choices, return_int=False)
    status = models.Enum8Field(choices=Status.choices)  # -128 to 127

    class Meta:
        engine = models.MergeTree(order_by="id")
```

```python
# Create with int value or string label
LogEntry.objects.create(message="Test", severity=1, status=0)
LogEntry.objects.create(message="Test", severity='info', status='pending')
```

```python
# Query by value or label
LogEntry.objects.filter(severity=1)
LogEntry.objects.filter(severity='info')
LogEntry.objects.filter(severity__gt=2)  # WARNING and above
LogEntry.objects.filter(severity__contains='err')  # Contains 'err' in label
```

--------------------------------

### Run All Tox Supported Tests

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

Execute all supported Python and Django version combinations using tox. This provides a comprehensive test run across the project's compatibility matrix.

```shell
tox
```

--------------------------------

### Run Unit Tests

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

Execute the project's unit tests using the provided test runner script. Ensure all tests pass to confirm bug fixes or new features.

```shell
python tests/runtests.py
```

--------------------------------

### Implement ClickHouse Database Router

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Create a database router class that identifies ClickhouseModel subclasses and directs their read/write operations and migrations to the 'clickhouse' database. Add the router to settings.py.

```python
from clickhouse_backend.models import ClickhouseModel


def get_subclasses(class_):
    classes = class_.__subclasses__()
    index = 0
    while index < len(classes):
        classes.extend(classes[index].__subclasses__())
        index += 1
    return list(set(classes))


class ClickHouseRouter:
    def __init__(self):
        self.route_model_names = set()
        for model in get_subclasses(ClickhouseModel):
            if model._meta.abstract:
                continue
            self.route_model_names.add(model._meta.label_lower)

    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


# In settings.py
DATABASE_ROUTERS = ["dbrouters.ClickHouseRouter"]
```

--------------------------------

### Configure Data Skipping Indexes

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Implement various data skipping indexes like MinMax, Set, Bloom Filter, Token Bloom Filter, and N-gram Bloom Filter for query optimization. Specify fields, name, type, and granularity for each index.

```python
class SearchableLog(models.ClickhouseModel):
    timestamp = models.DateTime64Field()
    user_id = models.UInt64Field()
    ip_address = models.IPv4Field()
    message = models.StringField()
    tags = models.ArrayField(base_field=models.StringField())

    class Meta:
        engine = models.MergeTree(
            order_by=("timestamp", "id"),
            partition_by=models.toYYYYMMDD("timestamp")
        )
        indexes = [
            # MinMax index - stores min/max values per granule
            models.Index(
                fields=["user_id"],
                name="user_minmax_idx",
                type=models.MinMax(),
                granularity=4
            ),
            # Set index - stores unique values per granule
            models.Index(
                fields=["ip_address"],
                name="ip_set_idx",
                type=models.Set(1000),  # max 1000 unique values
                granularity=4
            ),
            # Bloom filter - probabilistic membership test
            models.Index(
                fields=["message"],
                name="message_bloom_idx",
                type=models.BloomFilter(0.01),  # 1% false positive rate
                granularity=1
            ),
            # Token bloom filter - for tokenized text search
            models.Index(
                fields=["message"],
                name="message_token_idx",
                type=models.TokenbfV1(256, 2, 0),
                granularity=4
            ),
            # N-gram bloom filter - for substring search
            models.Index(
                fields=["message"],
                name="message_ngram_idx",
                type=models.NgrambfV1(3, 256, 2, 0),  # 3-gram
                granularity=4
            ),
        ]
```

--------------------------------

### Define and Use IP Address Fields

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Illustrates defining models with IPv4, IPv6, and GenericIPAddress fields, including creation and querying with IP-specific lookups. The `unpack_ipv4` option is useful for handling IPv4-mapped IPv6 addresses.

```python
from clickhouse_backend import models


class NetworkLog(models.ClickhouseModel):
    source_ip = models.IPv4Field()
    dest_ip = models.IPv6Field()
    gateway = models.GenericIPAddressField(unpack_ipv4=True)

    class Meta:
        engine = models.MergeTree(order_by="id")
```

```python
NetworkLog.objects.create(
    source_ip="192.168.1.1",
    dest_ip="2001:0db8:85a3:0000:0000:8a2e:0370:7334",
    gateway="10.0.0.1"
)
```

```python
NetworkLog.objects.create(
    source_ip="10.0.0.1",
    dest_ip="::ffff:192.168.1.1",  # IPv4-mapped IPv6
    gateway="::ffff:10.0.0.1"
)
```

```python
log = NetworkLog.objects.first()
print(log.source_ip)  # '192.168.1.1'
print(log.gateway)    # '10.0.0.1' (unpacked from IPv6)
```

```python
NetworkLog.objects.filter(source_ip="192.168.1.1")
```

```python
NetworkLog.objects.filter(source_ip__gt="192.168.1.0")
```

```python
NetworkLog.objects.filter(dest_ip__contains="8a2e")
```

--------------------------------

### Define Local and Distributed Tables

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Set up a local replicated table on shards and a distributed table that queries across shards. The Distributed engine requires cluster name, database, local table name, and a sharding key.

```python
from clickhouse_backend import models


# Local table on each shard
class Student(models.ClickhouseModel):
    name = models.StringField()
    address = models.StringField()
    score = models.Int8Field()

    class Meta:
        engine = models.ReplicatedMergeTree(
            "/clickhouse/tables/{uuid}/{shard}",
            "{replica}",
            order_by="id"
        )
        cluster = "my_cluster"


# Distributed table that queries all shards
class DistributedStudent(models.ClickhouseModel):
    name = models.StringField()
    score = models.Int8Field()

    class Meta:
        engine = models.Distributed(
            "my_cluster",              # cluster name
            models.currentDatabase(),  # database
            Student._meta.db_table,    # local table name
            models.Rand()              # sharding key
        )
        cluster = "my_cluster"


# CRUD on distributed table
students = DistributedStudent.objects.bulk_create([
    DistributedStudent(name=f"Student{i}", score=i * 10)
    for i in range(10)
])
```

--------------------------------

### Django ClickHouse Backend Multi-Database Configuration

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Defines multiple database connections using the clickhouse_backend.engine. Specifies options for migration clusters, mutation sync, insert sync, insert quorum, and alter sync. Also configures test database settings including cluster, managed status, and dependencies.

```python
DATABASES = {
    "default": {
        "ENGINE": "clickhouse_backend.backend",
        "OPTIONS": {
            "migration_cluster": "cluster",
            "settings": {
                "mutations_sync": 2,
                "insert_distributed_sync": 1,
                "insert_quorum": 2,
                "alter_sync": 2,
            },
        },
        "TEST": {"cluster": "cluster"},
    },
    "s1r2": {
        "ENGINE": "clickhouse_backend.backend",
        "PORT": 9001,
        "OPTIONS": {
            "migration_cluster": "cluster",
            "settings": {
                "mutations_sync": 2,
                "insert_distributed_sync": 1,
                "insert_quorum": 2,
                "alter_sync": 2,
            },
        },
        "TEST": {"cluster": "cluster", "managed": False, "DEPENDENCIES": ["default"]},
    },
    "s2r1": {
        "ENGINE": "clickhouse_backend.backend",
        "PORT": 9002,
        "OPTIONS": {
            "migration_cluster": "cluster",
            "settings": {
                "mutations_sync": 2,
                "insert_distributed_sync": 1,
                "insert_quorum": 2,
                "alter_sync": 2,
            },
        },
        "TEST": {"cluster": "cluster", "managed": False, "DEPENDENCIES": ["default"]},
    },
    "s2r2": {
        "ENGINE": "clickhouse_backend.backend",
        "PORT": 9003,
        "OPTIONS": {
            "migration_cluster": "cluster",
            "settings": {
                "mutations_sync": 2,
                "insert_distributed_sync": 1,
                "insert_quorum": 2,
                "alter_sync": 2,
            },
        },
        "TEST": {"cluster": "cluster", "managed": False, "DEPENDENCIES": ["default"]},
    },
}
```

--------------------------------

### Configure Django DATABASES for ClickHouse

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Configurations.md

Set the 'ENGINE' to 'clickhouse_backend.backend' to use ClickHouse. Other parameters like 'NAME', 'HOST', 'USER', 'PASSWORD', 'OPTIONS', and 'TEST' can be customized as needed.

```python
DATABASES = {
    'default': {
        'ENGINE': 'clickhouse_backend.backend',
        'NAME': 'default',
        'HOST': 'localhost',
        'USER': 'DB_USER',
        'PASSWORD': 'DB_PASSWORD',
        'OPTIONS': {
            'settings': {'mutations_sync': 1}
        },
        'TEST': {
            'fake_transaction': True
        }
    }
}
```

--------------------------------

### Define Basic MergeTree Model

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Use MergeTree for ordered data storage. Specify order_by, partition_by, and primary_key in the Meta class.

```python
class BasicLog(models.ClickhouseModel):
    timestamp = models.DateTime64Field()
    message = models.StringField()

    class Meta:
        engine = models.MergeTree(
            order_by=("timestamp", "id"),
            partition_by=models.toYYYYMMDD("timestamp"),
            primary_key="timestamp"
        )
```

--------------------------------

### Sample Django Test Case

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

A basic Django test case using `TestCase` to verify the initial state of the `Event` model. Ensure your test database is configured correctly.

```python
from django.test import TestCase


class TestEvent(TestCase):
    databases = {"default", "clickhouse"}

    def test_spam(self):
        assert Event.objects.count() == 0
```

--------------------------------

### Django Database Configuration for ClickHouse and PostgreSQL

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Configure your Django settings to include the ClickHouse backend and define connection details for both ClickHouse and a default RDBMS like PostgreSQL. Ensure 'clickhouse_backend' is in INSTALLED_APPS.

```python
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"]
```

--------------------------------

### Create ArrayField Data

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Demonstrates creating a record with a nested ArrayField, populating it with multi-dimensional lists of integers.

```python
NestedArrayModel.objects.create(
    array=[
        [[12, 13, 0, 1], [12]],
        [[12, 13, 0, 1], [12], [13, 14]]
    ]
)
```

--------------------------------

### Run Django Migrations

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Apply database migrations for your Django project. When using clusters, migrations should be run on specific nodes or with a specified migration cluster to ensure consistency.

```shell
python manage.py migrate
python manage.py migrate --database s1r2
python manage.py migrate --database s2r1
python manage.py migrate --database s2r2
```

--------------------------------

### Configure Query Execution for Columnar and NumPy Results

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Use the `connection.cursor().cursor.set_query_execution_args()` context manager to fetch query results as columnar data and deserialize them into NumPy objects. This is useful for performance-intensive data analysis.

```python
import numpy as np
from django.db import connection

sql = """
    SELECT toDateTime32('2022-01-01 01:00:05', 'UTC'), number, number*2.5
    FROM system.numbers
    LIMIT 3
"""
with connection.cursor() as cursorWrapper:
    with cursorWrapper.cursor.set_query_execution_args(
        columnar=True, use_numpy=True
    ) as cursor:
        cursor.execute(sql)
        np.testing.assert_equal(
            cursor.fetchall(),
            [
                np.array(
                    [
                        np.datetime64("2022-01-01T01:00:05"),
                        np.datetime64("2022-01-01T01:00:05"),
                        np.datetime64("2022-01-01T01:00:05"),
                    ],
                    dtype="datetime64[s]",
                ),
                np.array([0, 1, 2], dtype=np.uint64),
                np.array([0, 2.5, 5.0], dtype=np.float64),
            ],
        )

        cursor.execute(sql)
        np.testing.assert_equal(
            cursor.fetchmany(2),
            [
                np.array(
                    [
                        np.datetime64("2022-01-01T01:00:05"),
                        np.datetime64("2022-01-01T01:00:05"),
                        np.datetime64("2022-01-01T01:00:05"),
                    ],
                    dtype="datetime64[s]",
                ),
                np.array([0, 1, 2], dtype=np.uint64),
            ],
        )
```

--------------------------------

### Define ClickHouse Model with MergeTree Engine

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Define a ClickHouse model by inheriting from ClickhouseModel and specifying table engine details in the Meta class. Includes primary key, ordering, partitioning, and index configurations.

```python
from django.db.models import CheckConstraint, IntegerChoices, Q
from django.utils import timezone
from clickhouse_backend import models


class Event(models.ClickhouseModel):
    class Action(IntegerChoices):
        PASS = 1
        DROP = 2
        ALERT = 3

    ip = models.GenericIPAddressField(default=":")
    ipv4 = models.IPv4Field(default="127.0.0.1")
    ip_nullable = models.GenericIPAddressField(null=True)
    port = models.UInt16Field(default=0)
    protocol = models.StringField(default="", low_cardinality=True)
    content = models.StringField(default="")
    timestamp = models.DateTime64Field(default=timezone.now)
    created_at = models.DateTime64Field(auto_now_add=True)
    action = models.EnumField(choices=Action.choices, default=Action.PASS)

    class Meta:
        ordering = ["-timestamp"]
        engine = models.MergeTree(
            primary_key="timestamp",
            order_by=("timestamp", "id"),
            partition_by=models.toYYYYMMDD("timestamp"),
            index_granularity=1024,
            index_granularity_bytes=1 << 20,
            enable_mixed_granularity_parts=1,
        )
        indexes = [
            models.Index(
                fields=["ip"],
                name="ip_set_idx",
                type=models.Set(1000),
                granularity=4
            ),
            models.Index(
                fields=["ipv4"],
                name="ipv4_bloom_idx",
                type=models.BloomFilter(0.001),
                granularity=1
            )
        ]
        constraints = (
            CheckConstraint(
                name="port_range",
                check=Q(port__gte=0, port__lte=65535),
            ),
        )
```

--------------------------------

### Define ClickHouse Model with Fields and Meta Options

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Define a ClickHouse model inheriting from `clickhouse_backend.models.ClickhouseModel`. Use ClickHouse-specific fields and configure `Meta` options like `engine`, `order_by`, `partition_by`, and `indexes`. Ensure imports are from `clickhouse_backend.models`, not `django.db.models`.

```python
from django.db.models import CheckConstraint, IntegerChoices, Q
from django.utils import timezone

from clickhouse_backend import models


class Event(models.ClickhouseModel):
    class Action(IntegerChoices):
        PASS = 1
        DROP = 2
        ALERT = 3
    ip = models.GenericIPAddressField(default="::")
    ipv4 = models.IPv4Field(default="127.0.0.1")
    ip_nullable = models.GenericIPAddressField(null=True)
    port = models.UInt16Field(default=0)
    protocol = models.StringField(default="", low_cardinality=True)
    content = models.StringField(default="")
    timestamp = models.DateTime64Field(default=timezone.now)
    created_at = models.DateTime64Field(auto_now_add=True)
    action = models.EnumField(choices=Action.choices, default=Action.PASS)

    class Meta:
        ordering = ["-timestamp"]
        engine = models.MergeTree(
            primary_key="timestamp",
            order_by=("timestamp", "id"),
            partition_by=models.toYYYYMMDD("timestamp"),
            index_granularity=1024,
            index_granularity_bytes=1 << 20,
            enable_mixed_granularity_parts=1,
        )
        indexes = [
            models.Index(
                fields=["ip"],
                name="ip_set_idx",
                type=models.Set(1000),
                granularity=4
            ),
            models.Index(
                fields=["ipv4"],
                name="ipv4_bloom_idx",
                type=models.BloomFilter(0.001),
                granularity=1
            )
        ]
        constraints = (
            CheckConstraint(
                name="port_range",
                check=Q(port__gte=0, port__lte=65535),
            ),
        )
```

--------------------------------

### ClickHouse Cluster Behind Load Balancer Configuration

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Configures the Django ClickHouse backend for a cluster behind a load balancer. Enables distributed migrations by setting 'distributed_migrations' to True in OPTIONS. Includes host, port, engine, and specific settings for mutations, inserts, and alters.

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

--------------------------------

### Generate Django Migrations

Source: https://context7.com/jayvynl/django-clickhouse-backend/llms.txt

Command to generate Django migration files for ClickHouse database schema changes.

```bash
python manage.py makemigrations
```

--------------------------------

### Define ClickHouse Models with Replication

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Define Django models that map to ClickHouse tables, specifying replication using ReplicatedMergeTree engine. Ensure cluster configuration is set in the Meta class.

```python
from clickhouse_backend import models


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

    class Meta:
        engine = models.ReplicatedMergeTree(
            "/clickhouse/tables/{uuid}/{shard}",
            # Or if you want to use database name or table name, you should also use macro instead of hardcoded name.
            # "/clickhouse/tables/{database}/{table}/{shard}",
            "{replica}",
            order_by="id"
        )
        cluster = "cluster"
```

--------------------------------

### Run Ruff Code Style Checks

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/CONTRIBUTING.md

Manually run Ruff, a linter and formatter, across the entire project to ensure code style consistency. This command applies all configured checks.

```shell
pre-commit run -a
```

--------------------------------

### Use Settings in SELECT Query for Synchronous Mutations

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Alternatively, apply `settings(mutations_sync=1)` directly to a query for synchronous mutation processing. This method is useful for specific operations without altering global settings.

```python
Event.objects.filter(protocol="UDP").settings(mutations_sync=1).delete()
```

--------------------------------

### TupleField Usage

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Demonstrates how to define and use TupleField for storing tuple data in ClickHouse.

```APIDOC
## TupleField

### Description
Stores tuple or named tuple data. Neither Nullable nor LowCardinality is supported. Requires a `base_fields` parameter.

### Parameters
#### base_fields
- **base_fields** (iterable) - Required - An iterable containing field instances or (field name, field instance) tuples. Field names must be valid Python identifiers.

### Usage Example
```python
from clickhouse_backend import models

class TupleModel(models.ClickhouseModel):
    tuple = models.TupleField(
        base_fields=[
            models.Int8Field(),
            models.StringField(),
            models.GenericIPAddressField(unpack_ipv4=True)
        ]
    )


class NamedTupleModel(models.ClickhouseModel):
    tuple = models.TupleField(base_fields=[
        ("int", models.Int8Field()),
        ("str", models.StringField()),
        ("ip", models.GenericIPAddressField(unpack_ipv4=True))
    ])

v = [100, "test", "::ffff:3.4.5.6"]
TupleModel.objects.create(tuple=v)
NamedTupleModel.objects.create(tuple=v)

# Accessing tuple data
print(TupleModel.objects.get().tuple)
# Expected output: (100, 'test', '3.4.5.6')
print(NamedTupleModel.objects.get().tuple)
# Expected output: Tuple(int=100, str='test', ip='3.4.5.6')

# Index lookup
print(TupleModel.objects.filter(tuple__1="test").exists())
# Expected output: True
print(NamedTupleModel.objects.filter(tuple__str="test").exists())
# Expected output: True
print(NamedTupleModel.objects.filter(tuple__ip__startswith="3.4").exists())
# Expected output: True
```
```

--------------------------------

### Define and Use JSONField

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Demonstrates defining a ClickHouseModel with a JSONField and saving/retrieving JSON data. Note that the JSONField value may change after being saved to the database due to ClickHouse's internal storage mechanisms.

```python
from clickhouse_backend import models

class JSONModel(models.ClickhouseModel):
    json = models.JSONField()

v = {'a': [1, 2, 3], 'b': [{'c': 1}, {'d': 2}], 'c': {'d': 'e'}}
instance = JSONModel.objects.create(json=v)
instance.refresh_from_db()
instance.json
# {'a': [1, 2, 3], 'b': [{'c': 1, 'd': 0}, {'c': 0, 'd': 2}], 'c': {'d': 'e'}}
```

--------------------------------

### Custom Django Database Router for ClickHouse

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Implement a database router to automatically direct queries for models inheriting from ClickhouseModel or those with a 'clickhouse' hint to the ClickHouse database. Other queries default to the primary database.

```python
# dbrouters.py
from clickhouse_backend.models import ClickhouseModel


def get_subclasses(class_):
    classes = class_.__subclasses__()

    index = 0
    while index < len(classes):
        classes.extend(classes[index].__subclasses__())
        index += 1

    return list(set(classes))


class ClickHouseRouter:
    def __init__(self):
        self.route_model_names = set()
        for model in get_subclasses(ClickhouseModel):
            if model._meta.abstract:
                continue
            self.route_model_names.add(model._meta.label_lower)

    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
```

--------------------------------

### JSONField

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Information about using JSONField, including experimental status and configuration.

```APIDOC
## JSONField

### Description
Stores JSON data. This is an experimental feature and is deprecated in favor of the native JSON type in newer ClickHouse versions. When queried, it returns data as a dictionary or list. Requires enabling experimental features in database settings.

### Configuration
To use JSONField, set `allow_experimental_object_type = 1` (for older versions) or `allow_experimental_json_type = 1` (from ClickHouse 24.8 LTS) in your database engine options.

### Example Configuration
```python
DATABASES = {
    'default': {
        'ENGINE': 'clickhouse_backend.backend',
        'OPTIONS': {
            'settings': {
                'allow_experimental_json_type': 1, # Or 'allow_experimental_object_type': 1
            }
        }
    }
}
```
```

--------------------------------

### Create Events using Django ORM

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Use the `Event.objects.create()` method to insert new records into the ClickHouse table. Assert the total count of events after creation.

```python
for i in range(10):
    Event.objects.create(ip_nullable=None, port=i,
                         protocol="HTTP", content="test",
                         action=Event.Action.PASS.value)
assert Event.objects.count() == 10
```

--------------------------------

### Configure Django for Synchronous Mutations in Testing

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/README.md

Set `mutations_sync=1` in your Django settings to ensure data mutations are processed synchronously during testing. This is crucial for verifying delete and update operations immediately.

```python
DATABASES = {
    "default": {
        "ENGINE": "clickhouse_backend.backend",
        "OPTIONS": {
            "settings": {
                "mutations_sync": 1,
            }
        }
    }
}
```

--------------------------------

### Define and Use MapField

Source: https://github.com/jayvynl/django-clickhouse-backend/blob/main/docs/Fields.md

Use MapField for key-value data. Requires `key_fields` and `value_field` parameters. Supports various lookups like `has_key`, `len`, `keys`, `values`, and accessing specific keys.

```python
from clickhouse_backend import models

class MapModel(models.ClickhouseModel):
    map = models.MapField(
        models.StringField(low_cardinality=True),
        models.GenericIPAddressField(unpack_ipv4=True)
    )

MapModel.objects.create(
    map={
        "baidu": "39.156.66.10",
        "bing.com": "13.107.21.200",
        "google.com": "172.217.163.46"
    }
)
```