如下代码的作用是可以利用hsql将hive中指定的数据写入到钉钉群通知中,并且可以@指定人员查看数据,详见如下代码,代码关键内容展示,像sql、url、secret没有展示,需要自己去钉钉群中新建机器人来获取。
# coding=UTF-8
import datetime
import time
import hmac
import hashlib
import base64
import urllib.parse
import requests
import sys
import json
import os
def run_sql_get_sendinfo(sql):
print("@@@ sql @@@:" + sql)
cmd = "hive -e \"{exec_sql}\"".format(exec_sql=sql)
print("### cmd ###:", cmd)
message = os.popen(cmd)
return message
def sign(title, content):
headers = {'Content-Type': 'application/json;charset=UTF-8'}
ding_robot_url = '{url}×tamp={timestamp}&sign={sign}'
timestamp = str(round(time.time() * 1000))
# secret = secret
secret_enc = secret.encode('utf-8')
string_to_sign = '{}\n{}'.format(timestamp, secret)
string_to_sign_enc = string_to_sign.encode('utf-8')
hmac_code = hmac.new(secret_enc, string_to_sign_enc, digestmod=hashlib.sha256).digest()
sign = urllib.parse.quote_plus(base64.b64encode(hmac_code))
# timestamp, sign = sign()
ding_robot_url = ding_robot_url.format(url=url, timestamp=timestamp, sign=sign)
post_data ={
"at": {
"atMobiles":[
"13000000000"
],
"isAtAll": False
},
"text": {
"content":"剔除数据" + content
},
"msgtype":"text"
}
print(ding_robot_url)
response = requests.post(ding_robot_url, json=post_data, headers=headers).json()
code = response["errcode"]
if code == 0:
print(str(time) + ":消息发送成功 返回码:" + str(code) + "\n")
else:
print(str(time) + ":消息发送失败 返回码:" + str(code) + "\n")
exit(3)
return code
print(response.status_code)
print(response.json())
print(timestamp)
print(sign)
def main():
now_time=datetime.datetime.now()
to_time = (now_time+datetime.timedelta(days=-1)).strftime("%Y-%m-%d")
add_info = """"""
add_company = run_sql_get_sendinfo(add_info)
# for a in add_company.readlines() :
# print(a)
# sign('剔除数据', a)
sign('剔除数据',add_company.read())
if __name__ == '__main__':
url = """"""
secret = """"""
main()
main方法中。url和secret参数需要到钉钉群中去获取,add_info参数为你要执行的hive sql
要注意的是不要频繁的调用机器人接口,或造成请求失败,详情见钉钉机器人开发文档