表配置类
compare_table_names = [
't_dpay_loan_order',
]
处理逻辑类
import pymysql
import need_compared_table
import smtplib
from email.mime.text import MIMEText
from email.header import Header
loan_mysql_db = pymysql.connect(host="10.148.XXX.XXX", port=3306, user="XXX", passwd="XXX", db="test_db")
hive_mysql_db = pymysql.connect(host="10.148.XXX.XXX", port=3306, user="XXX", passwd="XXX", db="hive")
mysql_cursor = loan_mysql_db.cursor()
hive_mysql_cursor = hive_mysql_db.cursor()
different_table_names = []
for table_name in need_compared_table.compare_table_names:
mysql_cursor.execute("""
SELECT
TABLE_NAME,
COLUMN_NAME
FROM
information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'dfpay'
and TABLE_NAME = '%s'
ORDER BY
2
""" % table_name)
mysql_metal_data = mysql_cursor.fetchall()
hive_mysql_cursor.execute("""
SELECT
tb2.TBL_NAME,
tb4.COLUMN_NAME
FROM
DBS tb1
LEFT JOIN TBLS tb2 on tb1.DB_ID = tb2.DB_ID
LEFT JOIN SDS tb3 on tb2.SD_ID = tb3.SD_ID
LEFT JOIN COLUMNS_V2 tb4 on tb3.CD_ID = tb4.CD_ID
WHERE
tb1.NAME = 'dwl_test_ods'
and tb2.TBL_NAME = '%s'
""" % table_name)
hive_mysql_metal_data = hive_mysql_cursor.fetchall()
if mysql_metal_data == hive_mysql_metal_data:
print('%s 元数据一致' % table_name)
elif mysql_metal_data != hive_mysql_metal_data:
print('%s 元数据不一致!!!!!' % table_name)
different_table_names.append(table_name)
print('different_table_names内容为 %s ' % different_table_names)
print('最终不一致的表名为 %s' % different_table_names)
loan_mysql_db.close()
try:
if different_table_names == []:
print('没有元数据不一致的表')
else:
print('有元数据不一致的表,准备发送邮件')
finally:
receiver = 'XXXXX@qq.com'
sender = 'xxxx@qq.com'
smtpserver = 'smtp.qq.com'
username = 'xxxxx@qq.com'
password = 'xxxxxxxx'
mail_title = '测试邮件(元数据监控)'
mail_body = '最终不一致的表名为 %s' % different_table_names
message = MIMEText(mail_body, 'plain', 'utf-8')
message['From'] = sender
message['To'] = receiver
message['Subject'] = Header(mail_title, 'utf-8')
smtp = smtplib.SMTP()
smtp.connect(smtpserver)
smtp.login(username, password)
smtp.sendmail(sender, receiver, message.as_string())