Join support for sqlalchemy core api

[ccaapton]

Hi, it seems the current join support is only applied for orm api. I'm using sqlalchemy core api to do some query-generation task, and I need to use join in my querys. But I always get this error in drivers/base.py:

join has no attribute global_

Could you add support for core api queries? Thx!

[xzkostyan]

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.

[ccaapton]

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)

[xzkostyan]

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

[ccaapton]

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'

[xzkostyan]

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.

[antonio-antuan]

@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).

[xzkostyan]

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.

[ccaapton]

@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" ?

[xzkostyan]

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

[ccaapton]

Awesome! Thanks a lot!