关键字：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，

caravel

该字段表达式为字符串常量：
‘$|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语句为：

<pre class="prettyprint linenums" style="padding: 8px; color: rgb(68, 68, 68); border-radius: 2px; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; display: block; margin: 0px 0px 20px; font-size: 14px; line-height: 20px; word-break: break-all; word-wrap: break-word; white-space: pre-wrap; background-color: rgb(248, 248, 248); border: 1px solid rgb(238, 238, 238); overflow: hidden; box-shadow: rgb(238, 238, 238) 40px 0px 0px inset, rgb(51, 183, 150) 42px 0px 0px inset; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 30px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">

1. 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|/pre> AS ad_name,
2. SUM(imp_pv) AS sum__imp_pv
3. FROM liuxiaowen.AD_REPORT2
4. WHERE pt >= '2015-06-13'
5. AND pt <= '2016-06-13'
6. 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|/pre>
7. ORDER BY SUM(imp_pv) DESC
8. LIMIT 50

</pre>

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

<pre class="prettyprint linenums" style="padding: 8px; color: rgb(68, 68, 68); border-radius: 2px; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; display: block; margin: 0px 0px 20px; font-size: 14px; line-height: 20px; word-break: break-all; word-wrap: break-word; white-space: pre-wrap; background-color: rgb(248, 248, 248); border: 1px solid rgb(238, 238, 238); overflow: hidden; box-shadow: rgb(238, 238, 238) 40px 0px 0px inset, rgb(51, 183, 150) 42px 0px 0px inset; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 30px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">

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

</pre>

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

<pre class="prettyprint linenums" style="padding: 8px; color: rgb(68, 68, 68); border-radius: 2px; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; display: block; margin: 0px 0px 20px; font-size: 14px; line-height: 20px; word-break: break-all; word-wrap: break-word; white-space: pre-wrap; background-color: rgb(248, 248, 248); border: 1px solid rgb(238, 238, 238); overflow: hidden; box-shadow: rgb(238, 238, 238) 40px 0px 0px inset, rgb(51, 183, 150) 42px 0px 0px inset; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 30px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">

1. 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|/pre> AS ad_name,
2. pt AS _timestamp,
3. SUM(imp_pv) AS sum__imp_pv
4. FROM liuxiaowen.AD_REPORT2
5. JOIN (
6. 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|/pre> AS __ad_name
7. FROM liuxiaowen.AD_REPORT2
8. WHERE pt >= '2015-06-13' AND pt <= '2016-06-13'
9. 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|/pre>
10. ORDER BY SUM(imp_pv) DESC
11. LIMIT 50
12. ) AS anon_1
13. 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|/pre> = __ad_name
14. WHERE pt >= '2015-06-13' AND pt <= '2016-06-13'
15. 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|/pre>, pt
16. ORDER BY SUM(imp_pv) DESC
17. LIMIT 50000

</pre>

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

<pre class="prettyprint linenums" style="padding: 8px; color: rgb(68, 68, 68); border-radius: 2px; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; display: block; margin: 0px 0px 20px; font-size: 14px; line-height: 20px; word-break: break-all; word-wrap: break-word; white-space: pre-wrap; background-color: rgb(248, 248, 248); border: 1px solid rgb(238, 238, 238); overflow: hidden; box-shadow: rgb(238, 238, 238) 40px 0px 0px inset, rgb(51, 183, 150) 42px 0px 0px inset; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-indent: 30px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">

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

</pre>

该配置方法对于关联一张维度表获取多个字段，以及关联多张维度表获取多个维度字段同样适用，只需要在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