search

sqlalchemy / alembic

A database migrations tool for SQLAlchemy.
MIT License
2.61k stars 234 forks source link

running migrations on clickhouse #1421

Closed kar-pev closed 5 months ago

kar-pev commented 5 months ago

Describe the bug

I'm trying to config revisions pipepline for my clickhouse database with alembic 1.7.6 and clickhouse_sqlalchemy 0.3.0. It's implemented with class structure in my case. When I'm creating first revision, everything seems ok, but when I'm trying to upgrade my database by adding some columns to table, alembic revision autogeneration fails with provided error, caused by sqlalchemy. I think, that my metadata wasn't updated since first revision, but I'm not sure, where especially error is

Optional link from https://docs.sqlalchemy.org which documents the behavior that is expected

No response

SQLAlchemy Version in Use

2.0.25

DBAPI (i.e. the database driver)

clickhouse

Database Vendor and Major Version

24.1.3

Python Version

3.10.12

Operating system

Ubuntu 22.04.3 LTS

To Reproduce

Model file

from sqlalchemy import Column, create_engine, MetaData
from clickhouse_sqlalchemy import (
    make_session, get_declarative_base, types, engines
)
from datetime import datetime

from alembic import context

url = context.config.get_main_option("sqlalchemy.url")
engine = create_engine(url)
metadata = MetaData()
metadata.create_all(bind=engine)

Base = get_declarative_base(metadata=metadata)

class MetricLogs(Base):
    __tablename__ = "metric_logs"
    ts = Column(types.DateTime, primary_key=True, default=datetime.now)
    run_id = Column(types.Int64, nullable=False)
    wdoc_id = Column(types.Int64, nullable=False)
    metric_name = Column(types.String(100), nullable=False)
    value = Column(types.Float32, nullable=False)

    __table_args__ = (
        engines.MergeTree(order_by=ts),
    )

    def upload_metrics(self):
        session = make_session(engine)
        session.add(self)
        session.commit()
        session.close()

alembic env file:
```python
from alembic.ddl import impl
from alembic import context
from sqlalchemy import pool
from sqlalchemy import engine_from_config
from logging.config import fileConfig
from clickhouse_sqlalchemy import engines
from env import get_var
from clickhouse_sqlalchemy.alembic.dialect import include_object

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

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config
config.set_main_option(
    "sqlalchemy.url",
    "clickhouse+http://{user}:{pwd}@{host}:{port}/{db_name}".format(
        user=get_var("CLICKHOUSE_USER"),
        pwd=get_var("CLICKHOUSE_PASSWORD"),
        host=get_var("CLICKHOUSE_HOST"),
        port=get_var("CLICKHOUSE_PORT"),
        db_name=get_var("CLICKHOUSE_DB_NAME")
    )
)

# Interpret the config file for Python logging.
# This line sets up loggers basically.
if config.config_file_name is not None:
    fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.meeadata

def need():
    from db.models.models import MetricLogs
    return MetricLogs

target_metadata = need().__base__.metadata

def run_migrations_offline() -> None:
    """Run migrations in 'offline' mode.

    This configures the context with just a URL
    and not an Engine, though an Engine is acceptable
    here as well.  By skipping the Engine creation
    we don't even need a DBAPI to be availablee

    Calls to context.execute() here emit the given string to the
    script output.

    """
    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online() -> None:
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    print(target_metadata.schema)
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_object=include_object
        )

        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,
        )
        with context.begin_transaction():
            context.run_migrations()

if context.is_offline_mode():
    run_migrations_offline()
else:
    run_migrations_online()


### Error

INFO  [alembic.runtime.migration] Context impl ClickhouseImpl.                                                                                                         
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.                                                                                                   
Traceback (most recent call last):                                                                                                                                     
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4295, in _col_expressions                                                       
    return [                                                                                                                                                           
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4296, in <listcomp>                                                             
    parent.c[col] if isinstance(col, str) else col                                                                                                                     
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/base.py", line 1624, in __getitem__                                                              
    return self._index[key][1]                                                                                                                                         
KeyError: 'ts'                                                                                                                                                         

The above exception was the direct cause of the following exception:                                                                                                   

Traceback (most recent call last):                                                                                                                                     
  File "/usr/bin/alembic", line 33, in <module>                                                                                                                        
    sys.exit(load_entry_point('alembic==1.7.6.dev0', 'console_scripts', 'alembic')())                                                                                  
  File "/usr/lib/python3/dist-packages/alembic/config.py", line 588, in main                                                                                           
    CommandLine(prog=prog).main(argv=argv)                                                                                                                             
  File "/usr/lib/python3/dist-packages/alembic/config.py", line 582, in main                                                                                           
    self.run_cmd(cfg, options)                                                                                                                                         
  File "/usr/lib/python3/dist-packages/alembic/config.py", line 559, in run_cmd                                                                                        
    fn(                                                                                                                                                                
  File "/usr/lib/python3/dist-packages/alembic/command.py", line 227, in revision                                                                                      
    script_directory.run_env()                                                                                                                                         
  File "/usr/lib/python3/dist-packages/alembic/script/base.py", line 563, in run_env                                                                                   
    util.load_python_file(self.dir, "env.py")                                                                                                                          
  File "/usr/lib/python3/dist-packages/alembic/util/pyfiles.py", line 92, in load_python_file                                                                          
    module = load_module_py(module_id, path)                                                                                                                           
  File "/usr/lib/python3/dist-packages/alembic/util/pyfiles.py", line 108, in load_module_py                                                                           
    spec.loader.exec_module(module)  # type: ignore                                                                                                                    
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/home/ivan/ai-postprocessing/db/alembic/env.py", line 116, in <module>
    run_migrations_online()
  File "/home/ivan/ai-postprocessing/db/alembic/env.py", line 110, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/lib/python3/dist-packages/alembic/runtime/environment.py", line 851, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/lib/python3/dist-packages/alembic/runtime/migration.py", line 608, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/usr/lib/python3/dist-packages/alembic/command.py", line 203, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/api.py", line 525, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/api.py", line 572, in _run_environment
    compare._populate_migration_script(
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 53, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 87, in _produce_net_changes
    comparators.dispatch("schema", autogen_context.dialect.name)(
  File "/usr/lib/python3/dist-packages/alembic/util/langhelpers.py", line 265, in go
    fn(*arg, **kw)
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 126, in _autogen_for_tables
    _compare_tables(
  File "/usr/lib/python3/dist-packages/alembic/autogenerate/compare.py", line 243, in _compare_tables
    sqla_compat._reflect_table(inspector, t, None)
  File "/usr/lib/python3/dist-packages/alembic/util/sqla_compat.py", line 248, in _reflect_table
    return inspector.reflect_table(table, None)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/drivers/reflection.py", line 25, in reflect_table
    self._reflect_engine(ch_table.name, schema, ch_table)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/drivers/reflection.py", line 44, in _reflect_engine
    engine._set_parent(table)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/engines/mergetree.py", line 49, in _set_parent
    self.order_by._set_parent(table, **kwargs)
  File "/home/ivan/.local/lib/python3.10/site-packages/clickhouse_sqlalchemy/engines/base.py", line 53, in _set_parent
    ColumnCollectionMixin._set_parent(self, table)
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4309, in _set_parent
   for col in self._col_expressions(parent):
  File "/home/ivan/.local/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4300, in _col_expressions
    raise exc.ConstraintColumnNotFoundError(
sqlalchemy.exc.ConstraintColumnNotFoundError: Can't create KeysExpressionOrColumn on table 'metric_logs': no column named 'ts' is present.

### Additional context

_No response_
zzzeek commented 5 months ago

hi -

have you gotten support for the clickhouse dialect first? this error is likely to be part of that dialect's implementation