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)