search

jayvynl / django-clickhouse-backend

Django clickhouse database backend.
MIT License
123 stars 21 forks source link

[QUESTION] Django.db.utils.OperationalError: Code: 81. DB::Exception: Database INFORMATION_SCHEMA doesn't exist. #47

Closed Hunter-99 closed 1 year ago

Hunter-99 commented 1 year ago

Describe the bug When I try to apply migrations (python src/manage.py migrate --database clickhouse) for clickhose database I catch error like this:

File "/usr/local/lib/python3.11/site-packages/clickhouse_driver/dbapi/cursor.py", line 117, in execute
    raise OperationalError(orig)
django.db.utils.OperationalError: Code: 81.
DB::Exception: Database INFORMATION_SCHEMA doesn't exist. Stack trace:

To Reproduce

Project structure

docker/
    scripts/
        core_app_startup.sh
    docker-compose.env
    docker-compose.yml
    Dockerfile
src/
    core/
    parser/
       migrations/
           0001_initial.py
       models.py
    project/
        settings.py
        dbrouters.py
    manage.py
core_app_startup.sh
``` python src/manage.py migrate --database default python src/manage.py migrate --database clickhouse python src/manage.py runserver 0.0.0.0:8000 ```
docker-compose.env
``` POSTGRES_HOST="postgres_database" POSTGRES_PORT="5432" POSTGRES_DB="postgres" POSTGRES_USER="postgres" POSTGRES_PASSWORD="postgres" API_SECRET_KEY="***" CLICKHOUSE_DB="clickhouse_database" CLICKHOUSE_USER="clickhouse" CLICKHOUSE_PASSWORD="clickhouse" CLICKHOUSE_HOST="clickhouse" CLICKHOUSE_PORT="9000" ```
docker-compose.yml
``` version: '3.8' services: api: container_name: dt_api_container build: dockerfile: docker/Dockerfile context: .. command: [ "/app/docker/scripts/core_app_startup.sh" ] ports: - "8000:8000" volumes: - ${PWD}/src:/app/src depends_on: - postgres_db postgres_db: image: postgres:15.4 container_name: dt_postgres_db_container env_file: - ./docker-compose.env volumes: - ${PWD}/.postgres_data:/var/lib/postgresql/data/ ports: - "5432:5432" clickhouse_db: image: yandex/clickhouse-server:21.3 container_name: dt_clickhouse_db_container hostname: clickhouse env_file: - ./docker-compose.env volumes: - ${PWD}/.clickhouse_data:/var/lib/clickhouse ports: - "8123:8123" - "9011:9000" ```
Dockerfile
``` FROM python:3.11.4 ENV PYTHONDONTWRITEBYTECODE 1 ENV PYTHONUNBUFFERED 1 ENV PIP_ROOT_USER_ACTION=ignore WORKDIR /app COPY ../requirements.txt ./requirements.txt COPY ../docker/docker-compose.env ./docker/docker-compose.env COPY ../docker/scripts/*.sh ./docker/scripts/ RUN pip install --upgrade pip RUN pip install -r requirements.txt RUN chmod a+x docker/scripts/*.sh ```
parser/migrations/0001_initial.py
``` # Generated by Django 4.2.4 on 2023-09-13 14:54 import clickhouse_backend.models from django.db import migrations, models import django.utils.timezone class Migration(migrations.Migration): dependencies = [ ] operations = [ migrations.CreateModel( name='Event', fields=[ ('id', models.BigAutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')), ('ip', clickhouse_backend.models.GenericIPAddressField(default='::')), ('ipv4', clickhouse_backend.models.GenericIPAddressField(default='127.0.0.1')), ('ip_nullable', clickhouse_backend.models.GenericIPAddressField(null=True)), ('port', clickhouse_backend.models.UInt16Field(default=0)), ('protocol', clickhouse_backend.models.StringField(default='', low_cardinality=True)), ('content', clickhouse_backend.models.StringField(default='')), ('timestamp', clickhouse_backend.models.DateTime64Field(default=django.utils.timezone.now)), ('created_at', clickhouse_backend.models.DateTime64Field(auto_now_add=True)), ('action', clickhouse_backend.models.EnumField(choices=[(1, 'Pass'), (2, 'Drop'), (3, 'Alert')], default=1)), ], options={ 'verbose_name': 'Network event', 'db_table': 'event', 'ordering': ['-id'], 'engine': clickhouse_backend.models.ReplacingMergeTree(enable_mixed_granularity_parts=1, index_granularity=1024, index_granularity_bytes=1048576, order_by=['id'], partition_by=models.Func('timestamp', function='toYYYYMMDD')), 'indexes': [clickhouse_backend.models.Index(fields=['ip'], granularity=4, name='ip_set_idx', type=clickhouse_backend.models.Set(1000)), clickhouse_backend.models.Index(fields=['ipv4'], granularity=1, name='ipv4_bloom_idx', type=clickhouse_backend.models.BloomFilter(0.001))], }, ), migrations.AddConstraint( model_name='event', constraint=models.CheckConstraint(check=models.Q(('port__gte', 0), ('port__lte', 65535)), name='port_range'), ), ] ```
parser/models.py
``` from clickhouse_backend import models from django.db.models import CheckConstraint, Func, IntegerChoices, Q from django.utils import timezone class Event(models.ClickhouseModel): class Action(IntegerChoices): PASS = 1 DROP = 2 ALERT = 3 ip = models.GenericIPAddressField(default="::") ipv4 = models.GenericIPAddressField(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: verbose_name = "Network event" ordering = ["-id"] db_table = "event" engine = models.ReplacingMergeTree( order_by=["id"], partition_by=Func("timestamp", function="toYYYYMMDD"), 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), ), ) ```
project/settings.py
``` import os import sys from pathlib import Path from dotenv import load_dotenv # Базовая директория проекта (== корневая папка репозитория) ----------------------------------------------------------# BASE_DIR = Path(__file__).resolve().parent.parent.parent # Загрузка переменных окружения ---------------------------------------------------------------------------------------# if 'test' in sys.argv: load_dotenv(dotenv_path=f'{BASE_DIR}/docker/docker-compose.example.env') else: load_dotenv(dotenv_path=f'{BASE_DIR}/docker/docker-compose.env') # Основные настройки --------------------------------------------------------------------------------------------------# SECRET_KEY = os.getenv('API_SECRET_KEY') DEBUG = True ALLOWED_HOSTS = [ '127.0.0.1', '0.0.0.0' ] CORS_ALLOWED_ORIGINS = [ 'http://127.0.0.1:8080', ] INSTALLED_APPS = [ 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'rest_framework', 'rest_framework.authtoken', 'dj_rest_auth', 'django.contrib.sites', 'allauth', 'allauth.account', 'dj_rest_auth.registration', 'corsheaders', 'core.apps.CoreConfig', 'parser.apps.ParserConfig' ] SITE_ID = 1 EMAIL_BACKEND = 'django.core.mail.backends.console.EmailBackend' ACCOUNT_AUTHENTICATION_METHOD = "email" ACCOUNT_EMAIL_REQUIRED = True ACCOUNT_USERNAME_REQUIRED = False ACCOUNT_EMAIL_VERIFICATION = 'optional' AUTHENTICATION_BACKENDS = [ 'django.contrib.auth.backends.ModelBackend', 'allauth.account.auth_backends.AuthenticationBackend', ] MIDDLEWARE = [ 'django.middleware.security.SecurityMiddleware', 'django.contrib.sessions.middleware.SessionMiddleware', 'corsheaders.middleware.CorsMiddleware', 'django.middleware.common.CommonMiddleware', 'django.middleware.csrf.CsrfViewMiddleware', 'django.contrib.auth.middleware.AuthenticationMiddleware', 'django.contrib.messages.middleware.MessageMiddleware', 'django.middleware.clickjacking.XFrameOptionsMiddleware', ] ROOT_URLCONF = 'project.urls' WSGI_APPLICATION = 'project.wsgi.application' # Шаблоны (HTML) ------------------------------------------------------------------------------------------------------# TEMPLATES = [ { 'BACKEND': 'django.template.backends.django.DjangoTemplates', 'DIRS': [ os.path.join(BASE_DIR, 'templates') ], 'APP_DIRS': True, 'OPTIONS': { 'context_processors': [ 'django.template.context_processors.debug', 'django.template.context_processors.request', 'django.contrib.auth.context_processors.auth', 'django.contrib.messages.context_processors.messages', ], }, }, ] # Подключаемые базы данных ------------------------------------------------------------------------------------------- # DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': os.getenv('POSTGRES_DB'), 'USER': os.getenv('POSTGRES_USER'), 'PASSWORD': os.getenv('POSTGRES_PASSWORD'), 'HOST': os.getenv('POSTGRES_HOST'), 'PORT': os.getenv('POSTGRES_PORT') }, "clickhouse": { "ENGINE": "clickhouse_backend.backend", "NAME": os.getenv('CLICKHOUSE_DB'), "USER": os.getenv('CLICKHOUSE_USER'), "PASSWORD": os.getenv('CLICKHOUSE_PASSWORD'), "HOST": os.getenv('CLICKHOUSE_HOST'), 'PORT': os.getenv('CLICKHOUSE_PORT') } } DATABASE_ROUTERS = ["project.dbrouters.ClickHouseRouter"] # Валидации паролей -------------------------------------------------------------------------------------------------- # AUTH_PASSWORD_VALIDATORS = [ { 'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator', }, { 'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator', }, { 'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator', }, { 'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator', }, ] # Локализация (язык, формат даты и времени) -------------------------------------------------------------------------- # LANGUAGE_CODE = 'ru-ru' DATETIME_FORMAT = 'd-m-Y H:i:s' TIME_ZONE = 'Europe/Chisinau' USE_L10N = False USE_I18N = True USE_TZ = True # Статические файлы (CSS, JavaScript, Images) ------------------------------------------------------------------------ # STATIC_URL = 'static/' # Тип первичного ключа по умолчанию для таблиц, созданных на основе моделей -------------------------------------------# DEFAULT_AUTO_FIELD = 'django.db.models.BigAutoField' # REST фреймворк ----------------------------------------------------------------------------------------------------- # REST_FRAMEWORK = { 'DEFAULT_PAGINATION_CLASS': 'rest_framework.pagination.PageNumberPagination', 'PAGE_SIZE': 10, 'DEFAULT_PERMISSION_CLASSES': [ 'rest_framework.permissions.DjangoModelPermissionsOrAnonReadOnly', ], 'DEFAULT_AUTHENTICATION_CLASSES': [ 'rest_framework.authentication.TokenAuthentication', ], } REST_AUTH = { 'SESSION_LOGIN': False, } ```
project/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 ```

Note: Clickhouse database container works fine (i can get access from PyCharm UI to database)

Expected behavior

Created table event

Versions

Hunter-99 commented 1 year ago

Maybe I made some mistake in configuration?

jayvynl commented 1 year ago

INFORMATION_SCHEMA is not available until ClickHouse v21.11. Refer ClickHouse v21.11 changelog