简介:
使用Python 实现对数据库表的监控告警功能, 并将告警信息通过钉钉机器人发送到钉钉群
实现DataWorks中数据质量的基本功能, 当然 DW的数据质量的规则类型很多, 用起来比较方便, 这里只简单实现了其中两个规则类型的功能, 仅供参考;
初次使用Python, 请多指教
使用工具: MaxCompute
CREATE TABLE IF NOT EXISTS puture_bigdata.tmp_monitor_tbl_info (
`id` STRING COMMENT '表编号id'
, `tbl_name` STRING COMMENT '表名'
, `pt_format` STRING COMMENT '分区格式: yyyy-MM-dd,yyyyMMdd 等'
, `val_type` STRING COMMENT '值类型: 表行数,周期值等'
, `monitor_flag` int COMMENT '监控标识: 0:不监控, 1:监控;'
, `rule_code` int COMMENT '规则编码: 1:表行数，上周期差值, 2:表行数，固定值 等'
, `rule_type` STRING COMMENT '规则类型: 表行数，上周期差值; 表行数，固定值; 与固定值比较 等'
, `expect_val` int COMMENT '期望值'
, `tbl_sort_code` int COMMENT '表类型编码: 0:其它(维表类), 1:亚马逊, 2:中小平台, 3:市场数据 等'
, `tbl_sort_name` STRING COMMENT '表类型名字: 0:其它(维表类), 1:亚马逊, 2:中小平台, 3:市场数据 等'
, `pt_num` INT COMMENT '分区日期差值'
) COMMENT '数据监控表信息'
tblproperties ("transactional"="true")
;
-- 插入数据
INSERT INTO TABLE puture_bigdata_dev.tmp_monitor_tbl_info
SELECT * FROM (
VALUES (1 , 'ods_amazon_amz_customer_returns_df', 'yyyyMMdd', '表行数', 1, 1, '表行数，上周期差值', 0, 1, '亚马逊' , -1)
, (2 , 'ods_amazon_amz_flat_file_all_orders_df', 'yyyyMMdd', '表行数', 1, 1, '表行数，上周期差值', 0, 1, '亚马逊' , -1)
, (3 , 'dim_sys_salesman_info_df', 'yyyyMMdd', '表行数', 1, 1, '表行数，上周期差值', 0, 0, '其它' , -1)
) AS table_name(id, tbl_name, pt_format, val_type, monitor_flag, rule_code, rule_type, expect_val, tbl_sort_code, tbl_sort_name, pt_num) ;
CREATE TABLE IF NOT EXISTS puture_bigdata_dev.tmp_monitor_tbl_info_log_di (
`id` STRING COMMENT '监控id编码:md5(表名_分区)_小时'
, `tbl_name` STRING COMMENT '表名'
, `stat_time` STRING COMMENT '统计时间'
, `pt_format` STRING COMMENT '分区格式: yyyy-MM-dd,yyyyMMdd 等'
, `stat_pt` STRING COMMENT '统计分区'
, `val_type` STRING COMMENT '值类型: 表行数,周期值等'
, `val` int COMMENT '统计值'
, `rule_code` int COMMENT '规则编码: 1:表行数，上周期差值, 2:表行数，固定值 等'
, `rule_type` STRING COMMENT '规则类型: 表行数，上周期差值; 表行数，固定值; 与固定值比较 等'
, `expect_val` int COMMENT '期望值'
, `is_exc` int COMMENT '是否异常: 0:否,1:是,默认值0'
, `tbl_sort_code` int COMMENT '表类型编码: 0:其它(维表类), 1:亚马逊, 2:中小平台, 3:市场数据 等'
, `tbl_sort_name` STRING COMMENT '表类型名字: 0:其它(维表类), 1:亚马逊, 2:中小平台, 3:市场数据 等'
) COMMENT '数据监控信息记录表'
PARTITIONED BY (pt STRING COMMENT '数据日期, yyyy-MM-dd') ;
'''PyODPS 3
请确保不要使用从 MaxCompute下载数据来处理。下载数据操作常包括Table/Instance的open_reader以及 DataFrame的to_pandas方法。
推荐使用 PyODPS DataFrame（从 MaxCompute 表创建）和MaxCompute SQL来处理数据。
更详细的内容可以参考：https://help.aliyun.com/document_detail/90481.html
'''
import os
from odps import ODPS, DataFrame
from datetime import datetime, timedelta
from dateutil import parser
options.tunnel.use_instance_tunnel = True
# 获取当前时间
now_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print(now_time)
pt = args['date']
print(pt)
date = datetime.strptime(pt, "%Y-%m-%d")
# 监控表列表 tbl_sort_code -> 0:其它(维表类), 1:亚马逊, 2:中小平台, 3:市场数据
sql_tbl_info = """
SELECT * FROM puture_bigdata.tmp_monitor_tbl_info
WHERE monitor_flag = 1 AND tbl_sort_code = 3
"""
# 结果表
res_tbl_name = "puture_bigdata.tmp_monitor_tbl_info_log_di"
# 统计sql代码 -- 表行数,上周期差值
def sql_upper_period_diff():
sql = f"""
set odps.sql.hive.compatible=true ;
INSERT INTO TABLE {res_tbl_name} PARTITION (pt='{pt}')
SELECT
a.id
, a.tbl_name
, a.stat_time
, a.pt_format
, a.stat_pt
, a.val_type
, a.val
, a.rule_code
, a.rule_type
, a.expect_val
, IF (a.val = 0, 1, (IF ((a.val - NVL(b.val,0)) >= {expect_val}, 0, 1 ))) AS is_exc
, a.tbl_sort_code
, a.tbl_sort_name
FROM (
SELECT
concat( md5(concat('{tbl_name}', '_', date_format('{date_str}' ,'{pt_format}')) ), '_', {rule_code}, '_', HOUR('{now_time}') ) AS id
, '{tbl_name}' AS tbl_name
, '{now_time}' AS stat_time
, '{pt_format}' AS pt_format
, date_format('{date_str}' ,'{pt_format}') AS stat_pt
, '{val_type}' AS val_type
, COUNT(1) AS val
, '{rule_code}' AS rule_code
, '{rule_type}' AS rule_type
, {expect_val} AS expect_val
, {tbl_sort_code} AS tbl_sort_code
, '{tbl_sort_name}' AS tbl_sort_name
FROM puture_bigdata.{tbl_name}
WHERE pt = date_format('{date_str}' ,'{pt_format}')
) a
LEFT JOIN
(
SELECT tbl_name, val FROM (
SELECT tbl_name, val
, ROW_NUMBER() OVER(PARTITION BY tbl_name ORDER BY stat_time DESC ) AS rn
FROM {res_tbl_name}
WHERE pt = DATE_ADD('{date_str}', -1)
) WHERE rn = 1
) b
ON a.tbl_name = b.tbl_name
;
"""
return sql
# 表行数, 固定值
def sql_line_fixed_val():
sql = f"""
set odps.sql.hive.compatible=true ;
INSERT INTO TABLE {res_tbl_name} PARTITION (pt='{pt}')
SELECT
concat( md5(concat('{tbl_name}', '_', date_format('{date_str}' ,'{pt_format}')) ), '_', {rule_code}, '_', HOUR('{now_time}') ) AS id
, '{tbl_name}' AS tbl_name
, '{now_time}' AS stat_time
, '{pt_format}' AS pt_format
, date_format('{date_str}' ,'{pt_format}') AS stat_pt
, '{val_type}' AS val_type
, COUNT(1) AS val
, '{rule_code}' AS rule_code
, '{rule_type}' AS rule_type
, {expect_val} AS expect_val
, IF (COUNT(1) >= {expect_val}, 0, 1 ) AS is_exc
, {tbl_sort_code} AS tbl_sort_code
, '{tbl_sort_name}' AS tbl_sort_name
FROM puture_bigdata.{tbl_name}
WHERE pt = date_format('{date_str}' ,'{pt_format}') ;
"""
return sql
# 执行监控统计代码
def ex_monitor(sql: str):
try :
# print (sql)
o.execute_sql(sql, hints={'odps.sql.hive.compatible': True , "odps.sql.submit.mode":"script"})
print("{}: 运行成功".format(tbl_name) )
except Exception as e:
print('{}: 运行异常 ======> '.format(tbl_name) + str(e))
if __name__ == '__main__':
try :
with o.execute_sql(sql_tbl_info, hints={'odps.sql.hive.compatible': True}).open_reader() as reader:
for row_record in reader:
# print(row_record) # 打印一条数据值
tbl_name = row_record.tbl_name
pt_format = row_record.pt_format
val_type = row_record.val_type
monitor_flag = row_record.monitor_flag
rule_code = row_record.rule_code
rule_type = row_record.rule_type
expect_val = row_record.expect_val
tbl_sort_code = row_record.tbl_sort_code
tbl_sort_name = row_record.tbl_sort_name
pt_num = row_record.pt_num
date_str = (date + timedelta(days=pt_num)).strftime('%Y-%m-%d')
if rule_code == 1 :
ex_monitor(sql_upper_period_diff())
elif rule_code == 2 :
ex_monitor(sql_line_fixed_val())
else :
print("未知规则!!!")
except Exception as e:
print('异常 ======> ' + str(e))
'''PyODPS 3
请确保不要使用从 MaxCompute下载数据来处理。下载数据操作常包括Table/Instance的open_reader以及 DataFrame的to_pandas方法。
推荐使用 PyODPS DataFrame（从 MaxCompute 表创建）和MaxCompute SQL来处理数据。
更详细的内容可以参考：https://help.aliyun.com/document_detail/90481.html
'''
import json
import requests
from datetime import datetime
import os
from odps import ODPS, DataFrame
date_str = args['date']
# 接口地址和token信息
url = 'https://oapi.dingtalk.com/robot/send?access_token=***********************'
now_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print (now_time)
sql_query = f"""
SELECT tbl_name, stat_time, stat_pt, val_type, val, rule_type, expect_val, is_exc
FROM (
SELECT tbl_name, stat_time, stat_pt, val_type, val, rule_type, expect_val, is_exc
, ROW_NUMBER() OVER(PARTITION BY tbl_name ORDER BY stat_time DESC) AS rn
FROM puture_bigdata_dev.tmp_monitor_tbl_info_log_di
WHERE pt = '{date_str}'
AND tbl_sort_code = 1 -- 表种类
) a
WHERE rn = 1 AND is_exc = 1
"""
# 钉钉机器人,发送消息
def dd_robot(url:str, content: str):
HEADERS = {"Content-Type": "application/json;charset=utf-8"}
#content里面要设置关键字
data_info = {
"msgtype": "text",
"text": {
"content": content
},
"isAtAll": False
#这是配置需要@的人
# ,"at": {"atMobiles": ["15xxxxxx06",'18xxxxxx1']}
}
value = json.dumps(data_info)
response = requests.post(url,data=value,headers=HEADERS)
if response.json()['errmsg']!='ok':
print(response.text)
# 主函数
if __name__ == '__main__': # py3可以省略
try :
with o.execute_sql(sql_query, hints={'odps.sql.hive.compatible': True}).open_reader() as reader:
result_rows = list(reader) # 读取所有的结果行
result_count = len(result_rows) # 获取结果条数
#print("结果条数:", result_count) # 打印结果条数
if result_count > 0 :
for row in result_rows:
tbl_name = row.tbl_name
stat_time = row.stat_time
stat_pt = row.stat_pt
val_type = row.val_type
val = row.val
rule_type = row.rule_type
expect_val = row.expect_val
#print (tbl_name)
content = "数据质量(DQC)校验告警 \n "
content = content + "【对象名称】：" + tbl_name + " \n "
content = content + "【实际分区】：pt=" + stat_pt + " \n "
content = content + "【触发规则】: " + rule_type + " | 当前样本值: " + val + " | 阈值: " + expect_val + " \n "
content = content + now_time + " \n "
dd_robot(url, content)
else :
print ("无异常情况;")
except Exception as e:
print ('异常 ========>' + str(e) )
数据质量(DQC)校验告警
【对象名称】：dws_amazon_market_sales_stat_di
【实际分区】：pt=20240103
【触发规则】: 表行数，固定值 | 当前样本值: 617 | 阈值: 650
2024-01-04 02:54:44