在cube build完成后,我的工作是写sql生成数据分析邮件报表。但是,问题是这种重复劳动效率低、易出错、浪费时间。还好Kylin提供,可以将这种数据分析需求转换成HTTP请求。
1. RESTful API
Kylin的认证是basic authentication,加密算法是,加密的明文为username:password
;在POST的header进行用户认证:
curl -c cookiefile.txt -X POST -H "Authorization: Basic QURNSU46S1lMSU4=" -H 'Content-Type: application/json' http://:7070/kylin/api/user/authentication
在认证完成之后,可以复用cookie文件(不再需要重新认证),向Kylin发送GET或POST请求,比如,查询cube的信息:
curl -b cookiefile.txt -H 'Content-Type: application/json' http://:7070/kylin/api/cubes/kylin_sales_cube
若要向Kylin发送sql query,则POST请求中的data应遵从如下JSON规范:
{ "sql":"select * from TEST_KYLIN_FACT", "offset":0, "limit":50000, "acceptPartial":false, "project":"DEFAULT"}
其中,offset为sql中相对记录首行的偏移量,limit为限制记录条数;二者在后台处理时都会拼接到sql中去。发送sql query的curl命令:
curl -b cookiefile.txt -X POST -H 'Content-Type: application/json' -d '{"sql":"select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales group by part_dt", "offset":0, "limit":50000, "acceptPartial":false, "project":"learn_kylin"}' http://:7070/kylin/api/querycurl -b cookiefile.txt -X POST -H 'Content-Type: application/json' -d @sql.json http:// :7070/kylin/api/query
2. Python实践
Python的神模块已封装好了HTTP请求与返回,好用到爆!Session对象解决了认证、cookie持久化(persistent)的问题:
s = requests.session()headers = {'Authorization': 'Basic QURNSU46S1lMSU4='}s.post(url, headers=headers)
Session对象能复用TCP连接,不用生成cookie文件,而进行下一步HTTP请求:
# query cube infourl2 = 'http://:7070/kylin/api/cubes/kylin_sales_cube'r = s.get(url2)r.json()# sql queryurl3 = 'http:// :7070/kylin/api/query'sql_str = 'select part_dt, sum(price) as total_selled, count(distinct seller_id) as sellers from kylin_sales group by part_dt'json_str = '{"sql":"' + sql_str + '", "offset": 0, "limit": 50000, acceptPartial": false, "project": "learn_kylin"}'r = s.post(url3, data=json_str)results = r.json()['results']
Kylin的sql query的查询结果在results
,其类型为list[list]。因此,封装Kylin的认证与sql查询接口如下:
import requestsdef authenticate(): """ authenticate user :return: session """ url = 'http://:7070/kylin/api/user/authentication' headers = {'Authorization': 'Basic QURNSU46S1lMSU4='} s = requests.session() s.headers.update({'Content-Type': 'application/json'}) s.post(url, headers=headers) return sdef query(sql_str, session): """ sql query :param sql_str: string of sql :param session: session object :return: results(type is list) """ url = 'http:// :7070/kylin/api/query' json_str = '{"sql":"' + sql_str + '", "offset": 0, "limit": 50000, ' \ '"acceptPartial": false, "project": "xxx"}' r = session.post(url, data=json_str) results = r.json()['results'] return results
后面邮件报表的生成,得具体联系业务需求。这里,分享一下添加邮件附件的方法:
msg = MIMEMultipart()att1 = MIMEText(open('./resources/xxx.csv', 'rb').read(), 'base64', 'gb2312')att1["Content-Type"] = 'application/octet-stream'att1["Content-Disposition"] = 'attachment; filename="xxx.csv"'msg.attach(att1)