Migration Support about clickhouse-sqlalchemy

[xzkostyan]

I've pushed draft of alembic support to feature-alembic-support branch. You can try it installing directly from github:

pip install git+https://github.com/xzkostyan/clickhouse-sqlalchemy@feature-alembic-support#egg=clickhouse-sqlalchemy

Configure DSN in alembic.ini with one of the following ways:

sqlalchemy.url = clickhouse://default:@localhost/testdb
sqlalchemy.url = clickhouse+native://default:@localhost/testdb
sqlalchemy.url = clickhouse+http://default:@localhost/testdb

You need perform a few extra steps for enabling alembic support:

1. Register custom dialect in env.py at file after imports:

from clickhouse_sqlalchemy import engines
from alembic.ddl import impl

class CustomDialectImpl(impl.DefaultImpl):
    __dialect__ = "clickhouse"
    transactional_ddl = False

2. Add hacks for alembic_version table creation. Place following piece of code after context.configure in run_migrations_online function:

        from sqlalchemy import func, Column
        from clickhouse_sqlalchemy import types
        version = context._proxy._migration_context._version
        dt = Column('dt', types.DateTime, server_default=func.now())
        version.append_column(dt)
        version.engine = engines.ReplacingMergeTree(version=dt, order_by=func.tuple())

Example project is attached. Use pip installfrom README and run following command for database upgrade.

clickhouse-client --query 'DROP DATABASE IF EXISTS alembic' && clickhouse-client --query 'CREATE DATABASE alembic' && alembic upgrade head

clickhouse-alembic.tar.gz

[xzkostyan]

@RaySkarken yes, there is and error with inserting literals.

You should patch visit_insert before any imports. This should work flawlessly for 0.1.8.

from flask import Flask

fixup_clickhouse_sqlalchemy()

app = Flask(__name__)

from clickhouse_sqlalchemy.drivers.native.base import ClickHouseNativeCompiler


def fixup_clickhouse_sqlalchemy():
    def visit_insert_fix(self, insert_stmt, asfrom=False, **kw):
        rv = super(ClickHouseNativeCompiler, self).visit_insert(
            insert_stmt, asfrom=asfrom, **kw)

        pos = rv.lower().rfind('values (%')
        # Remove (%s)-templates from VALUES clause if exists.
        # ClickHouse server since version 19.3.3 parse query after VALUES and
        # allows inplace parameters.
        # Example: INSERT INTO test (x) VALUES (1), (2).
        if pos != -1:
            rv = rv[:pos + 6]
        return rv

    ClickHouseNativeCompiler.visit_insert = visit_insert_fix

I suppose the same patch with little modifications can be applied for 0.2.x branch.

[xzkostyan]

@anatoly-scherbakov I don't think it would make any sense. alembic_version just stores current revision, ClickHouse right now doesn't support transactional DDL. Any migration can break in the middle of migration and we will not "roll back". clickhouse-sqlalchemy integration with alembic is more like automation query generation by schema in your project.

[nekprj]

Just find out how to add partition_by into migration file:

from clickhouse_sqlalchemy import engines, types
from sqlalchemy import func, text

def upgrade():

    op.create_table(
        'account',
        sa.Column('id', types.Int8, primary_key=True),
        sa.Column('created_at', types.DateTime),
        engines.MergeTree(
            order_by='id',
            partition_by=func.toQuarter(text('created_at')),
            )
    )

[xzkostyan]

Hi.

It's hard to talk about migrations with no transactions in ClickHouse. But it seems that migration problem is actual now. Maybe it's real to use alembic for it. It's not in roadmap yet.

Of course additional code is required for integration with alembic.

[Jarvis1Tube]

I had the same need with migration support. That's how I connected alembic migrations.

# alembic/env.py
from alembic.ddl import impl

class ClickhouseImpl(impl.DefaultImpl):
    """
        Implimentation of Alembic migration behavior for Clickhouse DB
    """
    __dialect__ = "clickhouse"

This thing could be added to clickhouse-sqlalchemy, If you would like the idea.

[aamalev]

@xzkostyan Thank you for solving that problem. But don't work on http with second run
sqlalchemy.url = clickhouse://default:@localhost/testdb
sqlalchemy.url = clickhouse+http://default:@localhost/testdb

$ alembic upgrade head
INFO [alembic.runtime.migration] Context impl CustomDialectImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> c9227d190e8c, test
INFO [alembic.runtime.migration] Running upgrade c9227d190e8c -> b4cfae3bfa90, Add a column
$ alembic upgrade head
INFO [alembic.runtime.migration] Context impl CustomDialectImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [alembic.util.messaging] Can't locate revision identified by 'b4cfae3bfa90'
FAILED: Can't locate revision identified by 'b4cfae3bfa90'

(Server version 20.11.3.3 (official build))

[xzkostyan]

@aamalev in http dialect trailing zero bytes are not truncated right now (it's a bug).

When alembic is trying to get version from db it couldn't locate version due to 'b4cfae3bfa90' vs 'b4cfae3bfa90\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00' comparison.

Should can alter whole _version table as workaround in the second step:

        ####################
        from sqlalchemy import func, Column, Table, MetaData
        from clickhouse_sqlalchemy import types

        migration_context = context._proxy._migration_context
        migration_context._version = Table(
            context._proxy._migration_context.version_table,
            MetaData(),
            Column("version_num", types.String(), nullable=False),
            Column('dt', types.DateTime, server_default=func.now()),
            engines.ReplacingMergeTree(version='dt', order_by=func.tuple()),
            schema=migration_context.version_table_schema,
        )
        ####################

[aamalev]

@xzkostyan it works! Thanx!
When can I expect a pypi patch feature-alembic-support ? (:

[xzkostyan]

It's hard to say. I'd like to test this feature well, but I don't have enough time for it.

You can install package directly from github as well.

[Ulminator]

I am trying to get this working locally and I am running into issues where the autogenerated table creation statements have no reference to the engine set in the model definition, and even when I add it manually to match like how it is in the clickhouse-alembic/alembic/versions/c9227d190e8c_create_table.py file in clickhouse-alembic.tar.gz I get the same error message regardless:

clickhouse_sqlalchemy/drivers/base.py", line 586, in post_create_table
    raise exc.CompileError("No engine for table '%s'" % table.name)
sqlalchemy.exc.CompileError: No engine for table 'test'

Any idea why this wasn't an issue a few months ago, but it is now?

Edit: This seems to be due to some change from alembic verison 1.5.8 to 1.6.0. There is still some weird behavior though such as ignoring the fact a column is not nullable in an initial revision and then trying to make it not nullable in a consecutive revision.

[dmitrysmirnov931]

Hey, @Ulminator
Have you already solved the engine issue?

[yoavo-datricks]

Hi Friends.
After some research I was able to create a table but had to override the dialect.
Add the following snippet to the env.py

from sqlalchemy.schema import CreateTable
from clickhouse_sqlalchemy.engines import MergeTree
from clickhouse_sqlalchemy import Table as chsa_table

@compiles(CreateTable, 'clickhouse')
def compile(element, compiler, **kw):       
    sa_table = element.element
    tgt_table_engine = MergeTree(primary_key=sa_table.primary_key)
    ch_table = chsa_table(sa_table.name, sa_table.metadata, *sa_table.c,
                                   tgt_table_engine, extend_existing=True)
    element.element = ch_table
    return compiler.visit_create_table(element)                  

@xzkostyan My solution is really bad code, no order by or even the option to select a different engine.
It would help to start by enabling an engine parameter in the construct_arguments (schema.table) object and overring the visit_create_table in a way will set the engine before the post_create_table method is called.

[anatoly-scherbakov]

@xzkostyan thank you for the great library, it makes working with Clickhouse from Python much more pleasant.

In an application which has a primary PostgreSQL database and secondary Clickhouse database, would it be possible to store migrations history for Clickhouse in a separate alembic_version_clickhouse PostgreSQL table? Thus, the app would have two migration histories, one for each DB; but the both alembic tables would reside in PostgreSQL because that one is OLTP and feels like a better place to store such information.

Would be interested to hear your opinion on this.

[RaySkarken]

Hello! I have a problem when running your example(or my own migrations). Table that stated in migration file is creating, but no information about this revision goes to alembic_version table and process of migration gets stuck and after 5 minutes time out error is raised. I guess, there is some tricks with sqlalchemy, clickhouse-sqlalchemy and alembic version. I tried to use different versions but unfortunately got failed. Can you suppose, what is the problem? I would be thankful for any help. Also thank you for your library - great work!

[mhconradt]

I've found that the native driver is not compatible with running DDL using Alembic op.execute.
HTTP works fine, but you must not put a semi-colon after your DDL statements, since the driver appends FORMAT TabSeparatedWithNamesAndTypes to the statements.

create table tabular (
  a DateTime64(9),
  b Float64
);

becomes

create table tabular (
  a DateTime64(9),
  b Float64
); FORMAT TabSeparatedWithNamesAndTypes

which is not a valid statement.

[mhconradt]

Another solution to "Can't location revision..." is to manually set revision id to a 32-character string, so there are no zeros padded to the end.
alembic revision -m "create another table" --rev-id 6ab63e6af8886356f52706b02c6bd47d
Pro-tip: hex(random.randint(2 ** 124, 2 ** 128))

[xzkostyan]

1. I've added documentation about migrations https://clickhouse-sqlalchemy.readthedocs.io/en/latest/migrations.html
2. Various patches including "hanging issue" are published in 0.2.1 (SA 1.4) and 0.1.10 (SA 1.3)
3. Modern alembic (1.6.+) is supported in versions mentioned above. Engine is subclass of Constraint now.
4. You can find a lot of Engine examples https://clickhouse-sqlalchemy.readthedocs.io/en/latest/features.html#table-engines

Native dialect is highly recommended. HTTP dialect in this package is not maintained anymore.

I suppose we can close this issue. Create another if you still have problems with migrations.