Join support for sqlalchemy core api about clickhouse-sqlalchemy HOT 10 CLOSED

@AcLr I'm using your patch for join. The expected text should be a ANY LEFT JOIN b on a.col = b.col , but the actually-rendered text is a ANY LEFT JOIN b on col = col . Then I realized it is due to this line

@xzkostyan Is it still the case that "# Columns prefixed with table name are not supported" ?

from clickhouse-sqlalchemy.

Hi.

Can you provide minimal example of query that should work?

Now joins work only if you use session than provides following query attribute (session.query()): clickhouse_sqlalchemy.orm.query.Query.

from clickhouse-sqlalchemy.

Example:

from sqlalchemy import  select, MetaData, create_engine, join

url = 'clickhouse+native://default:@localhost/default'
engine = create_engine(url)
metadata = MetaData(bind=engine)
metadata.reflect()

t0 = metadata.sorted_tables[0]
t1 = metadata.sorted_tables[1]

q0 = select([t0]).join(t1, onclause=(t0.c['_id'] == t1.c['_id']))
print(q0)

from clickhouse-sqlalchemy.

Well, the thing is join should be custom (dialect-specific), not generic. This is required in order to support all the dialect features (GLOBAL, ARRAY, etc.).

You can try to use join from following snippet (I haven't tested it, but I think it's valid):

from sqlalchemy import join as generic_join 

def join(left, right, onclause=None, isouter=False, full=False, *kwargs):
    global_ = kwargs.pop('global_', False)

    any_ = kwargs.pop('any', None)
    all_ = kwargs.pop('all', None)

    j = generic_join(left, right, onclause=onclause, isouter=isouter, full=full)

    j.any = any_
    j.all = all_
    j.global_ = global_

    return j

from clickhouse-sqlalchemy.

After running your new join, the old code gives the same Error:

>>> q0 = select([t0]).join(t1, onclause=(t0.c['_id'] == t1.c['_id']))
>>> print(q0)
    131         join_type = " "
    132 
--> 133         if join.global_:
    134             join_type += "GLOBAL "
    135 

AttributeError: 'Join' object has no attribute 'global_'

So I changed it a bit, without using classs method 'join', but now another Error shows up:

>>>q1 = join(sqlalchemy.select([t0]), t1,onclause=(t0.c['_id'] == t1.c['_id']))
>>>print(q1)
<ipython-input-13-69e47fd8b005> in <module>
----> 1 q1 = join(sqlalchemy.select([c0]), t1, onclause=(c0==c1))

<ipython-input-2-8895d0db2cd6> in join(left, right, onclause, isouter, full, *kwargs)
      2 
      3 def join(left, right, onclause=None, isouter=False, full=False, *kwargs):
----> 4     global_ = kwargs.pop('global_', False)
      5 
      6     any_ = kwargs.pop('any', None)

AttributeError: 'tuple' object has no attribute 'pop'

from clickhouse-sqlalchemy.

Oh, I see. Join here is method of the query constructed by select, not imported from sqlalchemy package. As dirty-dirty hack you can patch select result before join.

query= select(...)
query.join = join
query = query.join(...)

May be there is more elegant way to use patched version of join.

from clickhouse-sqlalchemy.

@xzkostyan

I've done that feature and ready to make PR but got a question.

I found that declarative join parameners much more convenient than standard sqlalchemy joins. Examples are here.

Is it ok? Or do you want to save standard mechanism with several join arguments? In that case it will be several parameters for joins: isouter (bool), isfull (bool), isright (bool), any (bool), all (bool), global (bool), iscross (bool).
Several of them will be conflicted (any and all, isfull and cross and so on).

There is another way: provide the both mechanisms: sqla-standard and declarative (like in my examples).

from clickhouse-sqlalchemy.

I think that the second way is better (with both mechanisms: sqla-standard and declarative). There are many tools that use SA to generate SQL. If we want make these tools ClickHouse compatible sqlalchemy standard is required. But declarative way is more elegant.

from clickhouse-sqlalchemy.

@ccaapton see #87. It is fixed in latest master.

from clickhouse-sqlalchemy.

Awesome! Thanks a lot!

from clickhouse-sqlalchemy.