def main():
tracker2 = MySQLHelper(tracker2_host, tracker2_user, tracker2_pass,
tracker2_db)
countid = 0
countid1 = 1000000
while countid < 123000000:
select = """SELECT b.id, c.key_id FROM tracker2.matchedVideoP2PItem AS b, tracker2.matchedVideo AS c WHERE b.company_id = 14 AND c.company_id = 14 AND b.matchedVideo_id = c.id and b.id > %s and b.id <= %s""" % (
countid, countid1)
countid += 1000000
countid1 += 1000000
print select
select_result = tracker2.query_sql_cmd(select)
print "fetched!"
retrytracker2 = 0
while retrytracker2 < 10:
try:
print "retrytracker2:"
print retrytracker2
target = MySQLHelper(t_host, t_user, t_pass, t_db)
print "start insert!"
print datetime.datetime.now()
break
except Exception, e:
print e
retrytracker2 += 1
#target = MySQLHelper(t_host,t_user,t_pass,t_db)
insert_result = "insert into infback_Tmp " + " values " + str(
select_result)[1:-1].replace("L", "").replace("u", "")
target.insert_sql_cmd(insert_result)
print "finished!"
print datetime.datetime.now()
print "all fetched"
#print len(Res)
allP2P_Tmp_insert = "insert into allP2P_Tmp_150304 " + " values " + str(allP2P_result)[1:-1].replace("L", "").replace("u","")
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user, DASHBOARD_pass, DASHBOARD_db)
break
except Exception,e:
print e
retryVIADASHBOARD += 1
VIACOM_DASHBOARD.insert_sql_cmd(allP2P_Tmp_insert)
print datetime.datetime.now()
cursor = DASHBOARD.cursor()
cursor.execute("create index meta_isp on allP2P_Tmp_150304(meta_id, isp_id)")
cursor.close()
print "index created"
allinsert = """INSERT INTO ISPBased_allfixtime SELECT DATE_SUB('%s', INTERVAL + 1 DAY) AS dateID, ifnull(e.mapTitle, b.meta_title) AS title, max(b.priority_type) AS tier, c.isp_name AS isp, d.countryName AS country, CASE WHEN c.isp_name LIKE '%%Comcast%%' OR c.isp_name LIKE '%%Verizon%%' OR c.isp_name LIKE '%%AT&T%%' OR c.isp_name LIKE '%%SBC%%' OR c.isp_name LIKE '%%Cablevision%%' OR c.isp_name LIKE '%%Road Runner%%' OR c.isp_name LIKE '%%Time Warner%%' THEN 1 ELSE 0 END AS CASFlag, CASE WHEN d.country_id = 233 THEN 1 ELSE 0 END AS USFlag, 0 AS infringingFlag, sum(a.allIPs) AS IPs, count(DISTINCT a.protocol_hash) AS hashes, CURRENT_TIMESTAMP AS ETL_DTE FROM VIACOM_DASHBOARD.allP2P_Tmp_150304 AS a, VIACOM_DASHBOARD.meta_Tmp AS b left join VIACOM_DASHBOARD.MetaTitleMapTitle as e on b.meta_title = e.metaTitle, VIACOM_DASHBOARD.isp_Tmp AS c, VIACOM_DASHBOARD.TitleBasedCountry AS d WHERE a.meta_id = b.id AND a.isp_id = c.id AND c.country_id = d.country_id GROUP BY ifnull(e.mapTitle, b.meta_title), c.isp_name, d.countryName, CASE WHEN c.isp_name LIKE '%%Comcast%%' OR c.isp_name LIKE '%%Verizon%%' OR c.isp_name LIKE '%%AT&T%%' OR c.isp_name LIKE '%%SBC%%' OR c.isp_name LIKE '%%Cablevision%%' OR c.isp_name LIKE '%%Road Runner%%' OR c.isp_name LIKE '%%Time Warner%%' THEN 1 ELSE 0 END , CASE WHEN d.country_id = 233 THEN 1 ELSE 0 END""" %ETL_DTE
print allinsert
cursorall = DASHBOARD.cursor()
cursorall.execute(allinsert)
DASHBOARD.commit()
cursorall.close()
print "all finished"
break
except Exception, e:
print e
retryTime_ware += 1
# sql_set_time_zone = """set time_zone = '-8:00';"""
# db1.query_sql_cmd(sql_set_time_zone)
# print sql_set_time_zone
allP2P_Tmp_select = """SELECT a.trackingMeta_id AS meta_id, a.isp_id, a.protocol_hash, count(a.peer_ip_address) AS allIPs FROM p2pArchViacom.infringmentSummary20150304 AS a WHERE a.created_at >= DATE_SUB('%s', INTERVAL +1 DAY) AND a.created_at < DATE_SUB('%s', INTERVAL 0 DAY) GROUP BY a.trackingMeta_id, a.isp_id, a.protocol_hash;""" % (
ETL_DTE, ETL_DTE)
print allP2P_Tmp_select
allP2P_result = p2pwarehouse.query_sql_cmd(allP2P_Tmp_select)
print "all fetched"
#print len(Res)
allP2P_Tmp_insert = "insert into allP2P_Tmp " + " values " + str(
allP2P_result)[1:-1].replace("L", "").replace("u", "")
VIACOM_DASHBOARD.insert_sql_cmd(allP2P_Tmp_insert)
print datetime.datetime.now()
retryTime_afterdashboard = 0
while retryTime_afterdashboard < 10:
try:
print "retryTime_afterdashboard:"
print retryTime_afterdashboard
after_DASHBOARD = MySQLdb.connect(DASHBOARD_host, DASHBOARD_user,
DASHBOARD_pass, DASHBOARD_db)
cursor = after_DASHBOARD.cursor()
break
except Exception, e:
print e
retryTime_afterdashboard += 1
infP2P_result = tracker2.query_sql_cmd(infP2P_Tmp_select)
print "inf fetched"
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user,
DASHBOARD_pass, DASHBOARD_db)
break
except Exception, e:
print e
retryVIADASHBOARD += 1
infP2P_Tmp_insert = "insert into infringingP2P_Tmp " + " values " + str(
infP2P_result)[1:-1].replace("L", "").replace("u", "")
VIACOM_DASHBOARD.insert_sql_cmd(infP2P_Tmp_insert)
print datetime.datetime.now()
retry114 = 0
while retry114 < 10:
try:
print "retry114:"
print retry114
backup114 = MySQLHelper("192.168.110.114", "kettle", "k3UTLe",
"DM_VIACOM_TEST")
break
except Exception, e:
print e
retry114 += 1
#tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
print datetime.datetime.now()
infP2P_Tmp_select = """SELECT a.meta_id, a.isp_id, c.key_id AS protocol_hash, count(a.IP) AS infringingIPs FROM tracker2.takedownNoticeItemP2PDetail AS a, tracker2.matchedVideoP2PItem AS b, tracker2.matchedVideo AS c WHERE a.company_id = 14 AND b.company_id = 14 AND c.company_id = 14 AND a.matchedVideoP2PItem_id = b.id AND b.matchedVideo_id = c.id AND a.first_notice_send_time >= DATE_SUB('%s 08:00:00', INTERVAL +1 DAY) AND a.first_notice_send_time < DATE_SUB('%s 08:00:00', INTERVAL 0 DAY) GROUP BY a.meta_id, a.isp_id, c.key_id""" %(ETL_DTE,ETL_DTE)
print infP2P_Tmp_select
infP2P_result = tracker2.query_sql_cmd(infP2P_Tmp_select)
print "inf fetched"
retryVIADASHBOARD = 0
while retryVIADASHBOARD < 10:
try:
print "retryVIADASHBOARD:"
print retryVIADASHBOARD
VIACOM_DASHBOARD = MySQLHelper(DASHBOARD_host, DASHBOARD_user, DASHBOARD_pass, DASHBOARD_db)
break
except Exception,e:
print e
retryVIADASHBOARD += 1
infP2P_Tmp_insert = "insert into infringingP2P_Tmp " + " values " + str(infP2P_result)[1:-1].replace("L", "").replace("u","")
VIACOM_DASHBOARD.insert_sql_cmd(infP2P_Tmp_insert)
print datetime.datetime.now()
retry114 = 0
while retry114 < 10:
try:
print "retry114:"
print retry114
backup114 = MySQLHelper("192.168.110.114","kettle","k3UTLe","DM_VIACOM_TEST")
break
except Exception,e:
print e
retry114 += 1
#tracker2 = MySQLHelper(tracker2_host,tracker2_user,tracker2_pass,tracker2_db)
