superset连接kylin2.4 坑！pykylin与kylinpy

解决办法

只安装官方介绍的kylinpy

不要自行安装pykylin

superset连接kylin2.4 uri

成功：

kylin://ADMIN:XXXXXXXXXX@192.168.10.200:7070/learn_kylin?prefix=/kylin/api&version=v1

失败

kylin://ADMIN:XXXXXXXXXX@192.168.10.200:7070/learn_kylin?prefix=/kylin/api&version=v2

参考

http://kylin.apache.org/cn/docs21/tutorial/kylin_client_tool.html

Apache Kylin SQLAlchemy方言

任何一个使用SQLAlchemy的应用程序都可以通过此方言访问到Kylin, 您之前如果已经安装了kylinpy那么现在就已经集成好了SQLAlchemy Dialect. 请使用如下DSN模板访问Kylin

kylin://<username>:<password>@<hostname>:<port>/<project>?version=<v1|v2>&prefix=</kylin/api>

穷举测试后成功

安装

请确保您python解释器版本在2.7+, 或者3.4+以上. 最方便安装Apache Kylin python客户端工具库的方法是使用pip命令
pip install --upgrade kylinpy

注意，如果单独从github下载安装过pykylin的话要重新运行pip install --upgrade kylinpy，貌似github上pykylin比较杂。

Kylinpy命令行工具测试

1. 访问Apache Kylin
   kylinpy -h hostname -P 7070 -u ADMIN -p KYLIN --project learn_kylin --api1 --debug auth

如果已经安装了kylinpy那么现在就已经集成好了SQLAlchemy Dialect. 请使用如下DSN模板访问Kylin

 

sqlalchemy 连接kylin报错

sqlalchemy.exc.DBAPIError: (pykylin.errors.Error) Login failed with username: "ADMIN" 

import sqlalchemy as sa
kylin_engine = sa.create_engine('kylin://ADMIN:KYLIN@192.168.10.200:7070/learn_kylin?version=v2&prefix=/kylin/api')
results = kylin_engine.execute('SELECT count(*) FROM KYLIN_SALES')

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2074, in execute
    connection = self.contextual_connect(close_with_result=True)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
    e, dialect, self)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
    exc_info
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 413, in connect
    return _ConnectionFairy._checkout(self, self._threadconns)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 791, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 1099, in _do_get
    c = self._create_connection()
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 350, in _create_connection
    return _ConnectionRecord(self)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 477, in __init__
    self.__connect(first_connect_check=True)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/pool.py", line 674, in __connect
    connection = pool._invoke_creator(self)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "pykylin/connection.py", line 47, in connect
    return Connection(username, password, endpoint, project, **kwargs)
  File "pykylin/connection.py", line 17, in __init__
    self.proxy.login(self.username, self.password)
  File "pykylin/proxy.py", line 29, in login
    raise Error('Login failed with username: "%s"' % self.user)
sqlalchemy.exc.DBAPIError: (pykylin.errors.Error) Login failed with username: "ADMIN" (Background on this error at: http://sqlalche.me/e/dbapi)

版本

kylin.home: /opt/apache-kylin-2.4.0-bin-hbase1x
kylin.version:2.4.0.20500
commit:b450cc52f976ddafba7c6625d2440670af94332b;
os.name:Linux
os.arch:amd64
os.version:3.10.0-693.el7.x86_64
java.version:1.8.0_171
java.vendor:Oracle Corporation

最终连接成功