【Kylin实战】邮件报表生成

在cube build完成后，我的工作是写sql生成数据分析邮件报表。但是，问题是这种重复劳动效率低、易出错、浪费时间。还好Kylin提供RESTful API，可以将这种数据分析需求转换成HTTP请求。

1. RESTful API

Kylin的认证是basic authentication，加密算法是Base64，加密的明文为username:password；在POST的header进行用户认证：

curl -c cookiefile.txt -X POST -H "Authorization: Basic QURNSU46S1lMSU4=" -H 'Content-Type: application/json' http://<host>:7070/kylin/api/user/authentication

在认证完成之后，可以复用cookie文件（不再需要重新认证），向Kylin发送GET或POST请求，比如，查询cube的信息：

curl -b cookiefile.txt -H 'Content-Type: application/json' http://<host>: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://<host>:7070/kylin/api/query

curl -b cookiefile.txt -X POST -H 'Content-Type: application/json' -d @sql.json http://<host>:7070/kylin/api/query

2. Python实践

Python的神模块requests已封装好了HTTP请求与返回，好用到爆！Session对象解决了认证、cookie持久化（persistent）的问题：

s = requests.session()
headers = {'Authorization': 'Basic QURNSU46S1lMSU4='}
s.post(url, headers=headers)

Session对象能复用TCP连接，不用生成cookie文件，而进行下一步HTTP请求：

# query cube info
url2 = 'http://<host>:7070/kylin/api/cubes/kylin_sales_cube'
r = s.get(url2)
r.json()

# sql query
url3 = 'http://<host>: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 requests


def authenticate():
    """
    authenticate user
    :return: session
    """
    url = 'http://<host>: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 s


def query(sql_str, session):
    """
    sql query
    :param sql_str: string of sql 
    :param session: session object
    :return: results(type is list)
    """
    url = 'http://<host>: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)