微信搜索lxw1234bigdata | 邀请体验:数阅–数据管理、OLAP分析与可视化平台 | 赞助作者:赞助作者

开源OLAP+数据可视化工具–For Apache Kylin

Kylin lxw1234@qq.com 27676℃ 5评论

关键字:caravel、olap、kylin、数据可视化

经过我和好友Rocky一段时间的修改和调试,现已将初版的Caravel For Kylin上传至Github,供大家使用,如有发现任何的问题,可通过本博客或github联系我们。

GitHub地址:

https://github.com/rocky1001/pykylin/tree/caravel-kylin

https://github.com/rocky1001/caravel/tree/caravel-kylin

基于Caravel 0.8.9

关于Caravel:

http://airbnb.io/caravel/

http://lxw1234.com/archives/2016/06/681.htm

关于PyKylin:
https://github.com/wxiang7/pykylin (感谢作者@Wu Xiang)

修改说明

PyKylin:
修复where及having条件中的中文问题。
支持kylin中多表关联查询(实现方式很粗糙,但功能基本实现);
优化kylin多表查询时候时间序列相关的几个图表生成的SQL。

Caravel:
修复kylin中不支持的部分语法,比如使用timestamp关键字作为时间字段的别名。

安装与启动

Caravel:
按照官网的安装文档即可 http://airbnb.io/caravel/installation.html
Pykylin:

https://github.com/rocky1001/pykylin/tree/caravel-kylin

强烈推荐使用Python3,避免中文问题。

启动Caravel:
nohup gunicorn -w 16 –timeout 60 -b 0.0.0.0:8080 caravel:app >> /tmp/caravel.log 2>&1 &

注意:不建议使用官网给出的启动命令,caravel runserver -d
避免因查询页面关闭造成的Caravel Server挂掉。

使用说明

对于普通单表的使用,请参考:
http://lxw1234.com/archives/2016/06/681.htm

配置Kylin多表查询

为了支持Kylin中多表查询,一般是一个事实表关联多张维度表,并获取衍生维度,我们通过在Caravel Table中添加自定义字段,并对该字段定义一定规则的表达式(字段串常量),提交到PyKylin之后,PyKylin解析该字符串常量,转换成与维度表关联的SQL查询并返回结果。
这里做法有些粗糙,但功能基本实现,你可以参考该思路做进一步的优化和修改。

这里以事实表AD_REPORT2为例,其中有维度ID字段AD_ID,在Kylin中构建Cube时候,通过INNER JOIN维度表AD_DIM来获取维度名称AD_NAME。

在Caravel的AD_REPORT2中添加字段ad_name,

kylin

该字段表达式为字符串常量:
‘$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$’

字符串以$开头和结尾,以|分隔。
INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)
定义了维度表、JOIN类型、ON条件,该字符串会直接添加到原始SQL中,作为JOIN子句;
在维度表中的字段前面加上__为了和事实表中的字段区分而不用考虑表的别名。
b.__ad_name
定义了使用该字段作为最终的字段取值。

当选择ad_name作为维度查询时,Caravel提交给PyKylin的SQL语句为:

SELECT '$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$' AS ad_name, 
SUM(imp_pv) AS sum__imp_pv 
FROM liuxiaowen.AD_REPORT2 
WHERE pt >= '2015-06-13' 
AND pt <= '2016-06-13' 
GROUP BY '$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$' 
ORDER BY SUM(imp_pv) DESC
LIMIT 50

PyKylin经过转换后提交给Kylin的SQL语句为:

SELECT b.__ad_name as ad_name,
SUM(imp_pv) AS sum__imp_pv
FROM liuxiaowen.ad_report2
inner join (SELECT ad_id AS __ad_id,ad_name AS __ad_name FROM liuxiaowen.ad_dim) AS b 
ON (ad_id = __ad_id) 
WHERE pt >= '2015-06-13' AND pt <= '2016-06-13' 
GROUP BY b.__ad_name 
ORDER BY SUM(imp_pv) DESC

对于时间序列类的图表,Caravel提交给PyKylin的SQL语句为:

SELECT '$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$' AS ad_name,
pt AS _timestamp, 
SUM(imp_pv) AS sum__imp_pv 
FROM liuxiaowen.AD_REPORT2 
JOIN (
        SELECT '$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$' AS __ad_name 
        FROM liuxiaowen.AD_REPORT2 
        WHERE pt >= '2015-06-13' AND pt <= '2016-06-13'          
        GROUP BY '$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$'          
        ORDER BY SUM(imp_pv) DESC         
        LIMIT 50       
     ) AS anon_1  
ON '$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$' = __ad_name  
WHERE pt >= '2015-06-13' AND pt <= '2016-06-13' 
GROUP BY '$|INNER JOIN (select ad_id as __ad_id,ad_name as __ad_name from LIUXIAOWEN.AD_DIM) as b ON (ad_id = __ad_id)|b.__ad_name|$', pt 
ORDER BY SUM(imp_pv) DESC
LIMIT 50000

PyKylin经过优化后的SQL语句为:

SELECT b.__ad_name AS ad_name, 
pt AS _timestamp,
SUM(imp_pv) AS sum__imp_pv
FROM liuxiaowen.ad_report2 
inner join (SELECT ad_id AS __ad_id,ad_name AS __ad_name FROM liuxiaowen.ad_dim) AS b 
ON (ad_id = __ad_id) 
WHERE pt >= '2015-06-13' AND pt <= '2016-06-13' 
GROUP by b.__ad_name, pt 
ORDER by sum(imp_pv) desc
limit 50

该配置方法对于关联一张维度表获取多个字段,以及关联多张维度表获取多个维度字段同样适用,只需要在Caravel Table中添加多个字段,表达式的写法一样即可。

另外,该修改只针对数据源类型为kylin,对于Caravel使用其他数据源不受影响。

Enjoy it!


 

20160812更新:

很多网友在按照上面的方法(基于源码离线安装)安装之后,都遇到了js无法正常加载,页面无法正常显示的问题,该问题是由于node/npm环境未正确安装而导致的。

解决方法请参考官方说明 https://github.com/airbnb/caravel/blob/master/CONTRIBUTING.md#setting-up-the-node–npm-javascript-environment

或者 http://www.cnblogs.com/kevin19900306/p/5701401.html

 

如果觉得本博客对您有帮助,请 赞助作者

转载请注明:lxw的大数据田地 » 开源OLAP+数据可视化工具–For Apache Kylin

喜欢 (11)
分享 (0)
发表我的评论
取消评论
表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(5)个小伙伴在吐槽
  1. 赞赞赞!!!
    rocky2016-06-13 10:34 回复
  2. 厉害!
    木子简2016-08-10 13:36 回复
  3. 博主,您好,安装好caravel之后,caravel与kylin进行整合的时候,出现如下错误: ERROR:Traceback (most recent call last): … … … NoSuchModuleError: Can’t load plugin: sqlalchemy.dialects:kylin 请问怎么解决啊?
    icichacici2016-09-30 14:47 回复
  4. LZ你好,看了你文章后,也尝试在一张FACT表上增加一个DIM表的名称字段,可是经过Pykylin优化的语句好像有问题,原先是想在订单的FACT表上加个产品名称的字段如下: ``` '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$' ``` 然后就抛出异常了: ``` 2017-05-19 18:57:19,645 - Kylin - DEBUG - RAW SQL=SELECT '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$' AS product_name, COUNT(*) AS count FROM fact_order GROUP BY '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$' ORDER BY count DESC LIMIT 50000 2017-05-19 18:57:19,645:DEBUG:Kylin:RAW SQL=SELECT '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$' AS product_name, COUNT(*) AS count FROM fact_order GROUP BY '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$' ORDER BY count DESC LIMIT 50000 2017-05-19 18:57:19,645 - Kylin - DEBUG - Trans SQL=SELECT b.__product_name AS product_name, COUNT(*) AS count FROM fact_order GROUP BY b.__product_name ORDER BY count DESC LIMIT 50000 2017-05-19 18:57:19,645:DEBUG:Kylin:Trans SQL=SELECT b.__product_name AS product_name, COUNT(*) AS count FROM fact_order GROUP BY b.__product_name ORDER BY count DESC LIMIT 50000 2017-05-19 18:57:19,646:INFO:requests.packages.urllib3.connectionpool:Starting new HTTP connection (1): 172.16.10.171 2017-05-19 18:57:19,674:DEBUG:requests.packages.urllib3.connectionpool:"POST /kylin/api/query HTTP/1.1" 500 908 2017-05-19 18:57:19,675 - Kylin - DEBUG - Cursor close called 2017-05-19 18:57:19,675:DEBUG:Kylin:Cursor close called 2017-05-19 18:57:19,675 - Kylin - WARNING - Transactional rollback is not supported 2017-05-19 18:57:19,675:WARNING:Kylin:Transactional rollback is not supported 2017-05-19 18:57:19,675 - Kylin - WARNING - Transactional rollback is not supported 2017-05-19 18:57:19,675:WARNING:Kylin:Transactional rollback is not supported 2017-05-19 18:57:19,675:ERROR:root:(pykylin.errors.Error) Error when requesting: "query", exception: "Encountered "AS count" at line 2, column 17. Was expecting one of: "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "FROM" ... "," ... "AS" ... "AS" ... "AS" ... "AS" ... "AS" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "NOT" ... "IN" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "=" ... ">" ... "<" ... "=" ... "" ... "+" ... "-" ... "*" ... "/" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "MULTISET" ... "[" ... "FILTER" ... "OVER" ... " [SQL: u"SELECT '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$' AS product_name,\n COUNT(*) AS count\nFROM fact_order\nGROUP BY '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$'\nORDER BY count DESC\nLIMIT 50000"] Traceback (most recent call last): File "/usr/lib/python2.7/site-packages/superset/connectors/sqla/models.py", line 550, in query df = self.database.get_df(sql, self.schema) File "/usr/lib/python2.7/site-packages/superset/models/core.py", line 578, in get_df df = pd.read_sql(sql, eng) File "/usr/lib64/python2.7/site-packages/pandas/io/sql.py", line 415, in read_sql chunksize=chunksize) File "/usr/lib64/python2.7/site-packages/pandas/io/sql.py", line 1084, in read_query result = self.execute(*args) File "/usr/lib64/python2.7/site-packages/pandas/io/sql.py", line 975, in execute return self.connectable.execute(*args, **kwargs) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 2064, in execute return connection.execute(statement, *multiparams, **params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 939, in execute return self._execute_text(object, multiparams, params) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1097, in _execute_text statement, parameters File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1189, in _execute_context context) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1402, in _handle_dbapi_exception exc_info File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 203, 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 1182, in _execute_context context) File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 470, in do_execute cursor.execute(statement, parameters) File "/usr/lib/python2.7/site-packages/pykylin/cursor.py", line 83, in execute resp = self.connection.proxy.post('query', json=data) File "/usr/lib/python2.7/site-packages/pykylin/proxy.py", line 54, in post return self.request('post', route, **kwargs) File "/usr/lib/python2.7/site-packages/pykylin/proxy.py", line 49, in request raise Error('Error when requesting: "%s", exception: "%s"' % (route, exception)) DBAPIError: (pykylin.errors.Error) Error when requesting: "query", exception: "Encountered "AS count" at line 2, column 17. Was expecting one of: "ORDER" ... "LIMIT" ... "OFFSET" ... "FETCH" ... "FROM" ... "," ... "AS" ... "AS" ... "AS" ... "AS" ... "AS" ... "UNION" ... "INTERSECT" ... "EXCEPT" ... "NOT" ... "IN" ... "BETWEEN" ... "LIKE" ... "SIMILAR" ... "=" ... ">" ... "<" ... "=" ... "" ... "+" ... "-" ... "*" ... "/" ... "||" ... "AND" ... "OR" ... "IS" ... "MEMBER" ... "SUBMULTISET" ... "MULTISET" ... "[" ... "FILTER" ... "OVER" ... " [SQL: u"SELECT '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$' AS product_name,\n COUNT(*) AS count\nFROM fact_order\nGROUP BY '$|INNER JOIN (select product_key as __product_key, product_name as __product_name from start_dw.dim_procut) as b ON (product_key= __product_key)|b.__product_name|$'\nORDER BY count DESC\nLIMIT 50000"] 2017-05-19 18:57:19,677:INFO:root:Caching for the next 86400 seconds ``` 从堆栈信息来看是优化的语句有问题吧,不知道是否哪里配置有问题呢? PS: Kylin: 1.6.0 Superset: 0.18.2
    凡梦星尘2017-05-19 18:59 回复