因為最近上線了samza實時流平臺，實時從源資料端實時傳輸資料到資料倉儲，於是就需要檢查samza資料傳輸的準確性，則透過下面的python指令碼定時檢查兩個資料端表的行數，以簡單的檢測下資料一致性。下面的指令碼每天在早上6點執行，檢查昨天以前的所有資料行數是否一致，並郵件通知。因為要檢測的表比較多，並且清洗規則不一致，所以只能一張表一張表的進行比較，下面只是指令碼的部分程式碼

#coding=utf-8

import MySQLdb
import psycopg2
import smtplib
import time
from email.mime.text import MIMEText

#通用的郵件函式

def mail(sub,content):
mailto_list=["hzwuj@tairanchina.com"]
mail_host="smtp.tairanchina.com"
mail_uer="trcloud@tairanchina.com"
mail_pass="r!dHE#3OAGs5TGeh"
message = MIMEText(content,_charset='utf-8')
message['Subject'] = sub
message['From']=mail_uer
message['To'] = ";".join(mailto_list)
try:
s = smtplib.SMTP()
s.connect(mail_host)
s.login(mail_uer,mail_pass)
s.sendmail(mail_uer, mailto_list, message.as_string())
s.close()
return True
except Exception:
print 'filed'
return False

#設定變數為今天的日期
datetime=time.strftime("%Y%m%d", time.localtime())

#從源資料庫mysql去得出清洗後的總條數
conn=MySQLdb.connect(host='115.231.97.10',port=3306,user='select',passwd='123456',db='cms')
cursor1=conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
cursor1.execute("select 'biz_account' as tablename,count(*) as sum from biz_account where del_flag='0' and date(create_date)<%s" % datetime)
rows1=cursor1.fetchall()

for list1 in rows1:
sum1=list1['sum']

tablename1=list1['tablename']

#從資料庫倉庫greenplum從獲取samza清洗後的表總行數
conn2 = psycopg2.connect(database="dw", user="admin", password="123456", host="172.30.248.24", port="5432")
cursor2 = conn2.cursor()
cursor2.execute("select 'dw_biz_account' as tablename,count(*) as sum from dw.dw_biz_account where create_day<'%s'" % datetime )
rows2=cursor2.fetchall()

for list2 in rows2:
sum2=list2[1]
tablename2=list2[0]

#總條數比較
if sum1!=sum2:
mail('資料不一致',"%s:%s %s:%s"%(tablename1,sum1,tablename2,sum2))

else:

mail('資料一致','資料一致'）

conn.close()
conn2.close()

python跨庫檢查資料一致性

相關文章