在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)