-- coding: UTF-8 --
import sys
reload(sys)
sys.setdefaultencoding(‘utf-8’)
import httplib
import urllib
import json
import time
import os
import commands
import requests
import numpy as np
import schedule
import pymysql
from pyhive import hive
wx_url = ‘https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=dc776459-12e8-4272-a202-185385035d1d’ #把机器人的key
import pandas as pd
import datetime
if name == “main” :
current_time = datetime.datetime.now()
db_monitor = pymysql.connect(host='172.18.189.16',
port=3307,
user='db_monitor',
password='db_monitor%123',
database='db_monitor',
charset='utf8')
cursor_mo = db_monitor.cursor()
cursor_mo1 = db_monitor.cursor()
db_mysql = pymysql.connect(host='rr-wz990f7ie67nii33j.mysql.rds.aliyuncs.com',
port=3306,
user='bigdata',
password='zS^sNH4m%LjWQOMN',
database='open_order',
charset='utf8')
cursor_my = db_mysql.cursor()
hive_conn = hive.Connection(host="172.18.189.16",
port=10000,
username="wanghai",
password="Wanghai123!",
auth='LDAP',
database='ods')
cursor_hi = hive_conn.cursor()
# 获取需要计算的时间点
before = (datetime.datetime.now() - datetime.timedelta(minutes=20)).strftime("%Y-%m-%d %H:%M:%S")
# 获取监控列表源表名和hive表名
get_source_table = "select source_table,desc_table from monitor;"
cursor_mo.execute(get_source_table)
while True:
row = cursor_mo.fetchone()
if not row:
break
print("source_table: %s, desc_table: %s" % (row[0], row[1]))
# 查询源表条数
p_num=0
h_num=0
if row[0] =="open_user.oa_person":
print("select count(1) from %s where oau_gmt_create <= '%s';" % (row[0], before))
cursor_my.execute("select count(1) from %s where oau_gmt_create <= '%s';" % (row[0], before))
p_num=cursor_my.fetchone()[0]
# 查询hive表实际条数
print("select count(1) from %s where oau_gmt_create <='%s' " % (row[1], before))
cursor_hi.execute("select count(1) from %s where oau_gmt_create <= '%s'" % (row[1], before))
h_num = cursor_hi.fetchone()[0]
else :
print("select count(1) from %s where gmt_create <= '%s';" % (row[0], before))
cursor_my.execute("select count(1) from %s where gmt_create <= '%s';" % (row[0], before))
p_num=cursor_my.fetchone()[0]
# 查询hive表实际条数
print("select count(1) from %s where gmt_create <='%s' " % (row[1], before))
cursor_hi.execute("select count(1) from %s where gmt_create <= '%s' " % (row[1], before))
h_num = cursor_hi.fetchone()[0]
# 更新两表条数
print("update monitor set source_table_cnt = %s where source_table='%s' " % (p_num,row[0]))
cursor_mo1.execute("update monitor set source_table_cnt = %s where source_table='%s'" % (p_num,row[0]))
db_monitor.commit()
print("update monitor set desc_table_cnt = %s where source_table='%s'" % (h_num,row[0]))
cursor_mo1.execute("update monitor set desc_table_cnt = %s where source_table='%s'" % (h_num,row[0]))
db_monitor.commit()
if h_num!=p_num:
num = p_num - h_num
cursor_mo1.execute("update monitor set num = %s where source_table='%s' " % (num,row[0]))
db_monitor.commit()
cursor_mo1.execute("update monitor set status = 0 where source_table='%s' " % (row[0]))
db_monitor.commit()
else:
cursor_mo1.execute("update monitor set num = 0 where source_table='%s' " % (row[0]))
db_monitor.commit()
cursor_mo1.execute("update monitor set status = 1 where source_table='%s' " % (row[0]))
db_monitor.commit()
print("Number of rows returned: %s" % (cursor_mo.rowcount))
cursor_mo1.execute("select desc_table,source_table_cnt,desc_table_cnt,num from monitor where status=0")
data = cursor_mo1.fetchall()
columnDes = cursor_mo1.description #获取连接对象的描述信息
# columnNames = [columnDes[i][0] for i in range(len(columnDes))] #获取列名
df = pd.DataFrame([list(i) for i in data]) #得到的data为二维元组,逐行取出,转化为列表,再转化为df
res = df.to_csv(header=None)
if cursor_mo1.rowcount != 0:
header = {
"Content-Type": "application/json;charset=UTF-8"
}
message_body = {
"msgtype": "markdown",
"markdown": {
"content": str(res)+"\n 表数据异常,请检查"
},
"at": {
"atMobiles": [],
"isAtAll": False
}
}
send_data = json.dumps(message_body)
ChatBot = requests.post(url=wx_url, data=send_data, headers=header)
opener = ChatBot.json()
if opener["errmsg"] == "ok":
print(u"%s 通知消息发送成功!" % opener)
else:
print(u"通知消息发送失败,原因:{}".format(opener))
else:
header = {
"Content-Type": "application/json;charset=UTF-8"
}
message_body = {
"msgtype": "markdown",
"markdown": {
"content": str(res)+"所有表数据正常!"
},
"at": {
"atMobiles": [],
"isAtAll": False
}
}
send_data = json.dumps(message_body)
ChatBot = requests.post(url=wx_url, data=send_data, headers=header)
opener = ChatBot.json()
if opener["errmsg"] == "ok":
print(u"%s 通知消息发送成功!" % opener)
else:
print(u"通知消息发送失败,原因:{}".format(opener))
cursor_mo.close()
cursor_mo1.close()
cursor_my.close()
cursor_hi.close()
db_monitor.close()
db_mysql.close()
hive_conn.close()