Is it possible to use it for our special engine "Graph"? about clickhouse-sqlalchemy

[xzkostyan]

Yes, you can create custom Engine in following way:

Without parameters following code:

class Graph(Engine):
    def get_params(self):
        return []

class TestTable(get_declarative_base()):
    date = Column(types.Date, primary_key=True)
    x = Column(types.Int32)

    __table_args__ = (Graph(), )

Will render into:

CREATE TABLE test_table (date Date, x Int32) ENGINE = Graph

Variant with parameters is a little bit tricky. See rendering Engine method clickhouse_sqlalchemy.drivers.base.ClickHouseDDLCompiler.visit_engine.

There is name method of Engine clickhouse_sqlalchemy.engines.Engine.name that now produces name of the engine.

You can extend it in following way:

class Graph(Engine):
    def __init__(self, unique, symmetric):
        self.unique = unique
        self.symmetric = symmetric
        super(Graph, self).__init__()

    def get_params(self):
        return []

    def name(self):
        return (
            super(Graph, self).name() +
            ' setting unique={}, symmetric={}'.format(
                self.unique, self.symmetric
            )
        )

class TestTable(get_declarative_base()):
    date = Column(types.Date, primary_key=True)
    x = Column(types.Int32)

    __table_args__ = (
        Graph(1, 0),
    )

And voila:

CREATE TABLE test_table (date Date, x Int32) ENGINE = Graph setting unique=1, symmetric=0

[dalek-who]

I know I can execute raw sql to do so, but could I use table_args ?

[dalek-who]

Also, we use such statement to set the graph property :
create table graph1 (w Float64) engine=Graph setting unique=1,symmetric=0;

[dalek-who]

Thanks，That's what I want！
And after define my engine，how could I view the sql?

CREATE TABLE test_table (date Date, x Int32) ENGINE = Graph setting unique=1, symmetric=0

[dalek-who]

Also，could you show me an example about how to use your engine MergeTree to create such a table?

CREATE TABLE edge (src String, dst String, time Date, year String) engine=MergeTree order by src partition by year;

[xzkostyan]

@dalek-who you can view sql after compiling CreateTable expression. See tests for example: tests.test_engines.EnginesDeclarativeTestCase.

Currently modern syntax (ENGINE = MergeTree ... order by ... partition by...) of engine definition is not supported. Dialect now produce old syntax: ENGINE = MergeTree(date, (date, x), 8192).

[dalek-who]

Is clickhouse-sqlalchemy capable with flask-sqlalchemy? I'm hesitating between clickhouse-sqlalchemy and flask-sqlalchemy. clickhouse-sqlalchemy have engine, but flask-sqlalchemy helps me deal with session automatically.
I don't know if flask-sqlalchemy's session could use clickhouse-sqlalchemy's model with engine, or if flask -sqlalchemy's model without engine could be used for clickhouse's insert/update/select/delete (giving up create table automatically). Next week I will have a try.

[xzkostyan]

Unfortunately clickhouse-sqlalchemy is not capable with flask-sqlalchemy. The thing is ClickHouse dialect is more than traditional SQL. Some expressions must be rendered explicitly.

In out project we use both flask-sqlalchemy and clickhouse-sqlalchemy. The first is used for OLTP and the second for OLAP (ClickHouse).

Here we create singletone session object:

from sqlalchemy import create_engine, MetaData
from clickhouse_sqlalchemy import make_session, get_declarative_base

from project import app

engine = create_engine(app.config['CLICKHOUSE_DATABASE_URI'])
session = make_session(engine)
metadata = MetaData(bind=engine)
Base = get_declarative_base(metadata=metadata)

from clickhouse_sqlalchemy import engines, types as Types
from sqlalchemy import Column

from ... import Base

class Model(Base):
   clicks = Column(Types.Int64)
   ...
    __table_args__ = ( engines.MergeTree(...), )

When we need to query data from ClickHouse we just use session object.

from ... import session as clickhouse_session

query = clickhouse_session.query(Model.clicks)

In other words clickhouse-sqlalchemy can be used for building queries with sqlalchemy engine. But it can't be used from flask-sqlalchemy.

[dalek-who]

session = make_session(engine)

Is this session a scoped_session like sqlalchemy?

[xzkostyan]

No, currently it is not scoped, just usual Session instance. You can create own scoped-session wrapper and try it. PR is appreciated if everything will go well.