def dataTo_matchedVideo(): logger.info("load data target matchedVideo start") sendToMe(subject = "matchedVideo start", body = "matchedVideo start") try: target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = "FOX_DASHBOARD", port = target_port, charset = 'utf8') sql = """ insert into FOX_DASHBOARD.matchedVideo (id, company_id, trackingMeta_id, trackingWebsite_id, view_count, count_send_notice, first_send_notice_date, takeoff_time, hide_flag, clip_duration, matchedFile_id, meta_title, created_at, updated_at) select a.id, a.company_id, trackingMeta_id, trackingWebsite_id, view_count, count_send_notice, first_send_notice_date, takeoff_time, hide_flag, clip_duration, matchedFile_id, meta_title, created_at, updated_at from FOX_DASHBOARD.matchedVideoTmp as a, FOX_DASHBOARD.matchedVideoSequence as b where a.id = b.id and a.company_id = b.company_id """ target_mysql.queryNoData("delete from FOX_DASHBOARD.matchedVideo") target_mysql.queryNoData(sql) target_mysql.commit() except Exception, e: logger.debug("extract data from 192.168.111.235 tracker2 error, %s" %e) sendToMe(subject = "extract data from 192.168.111.235 tracker2", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def dataTo_matchedVideo(): logger.info("load data target matchedVideo start") sendToMe(subject = "matchedVideo start", body = "matchedVideo start") try: target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = "tracker2", port = target_port, charset = 'utf8') sql = """ insert into tracker2.matchedVideo (id, company_id, trackingMeta_id, trackingWebsite_id, view_count, count_send_notice, first_send_notice_date, takeoff_time, hide_flag, clip_duration, matchedFile_id, meta_title, created_at, updated_at) select a.id, a.company_id, trackingMeta_id, trackingWebsite_id, view_count, count_send_notice, first_send_notice_date, takeoff_time, hide_flag, clip_duration, matchedFile_id, meta_title, created_at, updated_at from tracker2.matchedVideoTmp as a, tracker2.matchedVideoSequence as b where a.id = b.id and a.company_id = b.company_id """ target_mysql.queryNoData("delete from tracker2.matchedVideo") target_mysql.queryNoData(sql) target_mysql.commit() except Exception, e: logger.debug("extract data from 192.168.111.235 tracker2 error, %s" %e) sendToMe(subject = "extract data from 192.168.111.235 tracker2", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def loadDataToTitleBasedRemoveNum1(): logger.info(":extract data from TitleBasedRemoveNum start") target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) try: target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, db_name = target_db, port = target_port, charset = 'utf8') aggregate_SQL = """ select a.reportDate, a.takeoffDate, a.trackingWebsite_id, ifnull(c.displayName, a.websiteName) as websiteName, a.websiteType, ifnull(b.mapTitle, a.title) as title, sum(removedNum) as removedNum, sum(complianceTime) as complianceTime, current_timestamp as ETLDate from (select a.reportDate, a.takeoffDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, sum(removedNum) as removedNum, sum(complianceTime) as complianceTime from TitleBasedRemoveNumTmp as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id group by 1, 2, 3, 4, 5, 6) as a left join MetaTitleMapTitle as b on a.title = b.metaTitle left join SiteMap as c on a.trackingWebsite_id = c.trackingWebsite_id group by 1, 2, 3, 4, 5, 6 """ target_mysql.queryNoData("delete from TitleBasedRemoveNum") aggregate_result = target_mysql.queryCMD(aggregate_SQL) insertUpdate_SQL = """ INSERT INTO TitleBasedRemoveNum (reportDate, takeoffDate, trackingWebsite_id, websiteName, websiteType, title, removedNum, complianceTime, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE removedNum = VALUES(removedNum), complianceTime = VALUES(complianceTime), ETLDate = VALUES(ETLDate) """ target_mysql.queryNoData("delete from TitleBasedRemoveNum") commitInTurn(commit_num = 50000, data = aggregate_result, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insertUpdate_SQL) #target_mysql.insertUpdateCMD(insertUpdate_SQL, aggregate_result) #target_mysql.commit() except Exception, e: logger.debug(" load data to TitleBasedRemoveNum, %s" %e) sendToMe(subject = "TitleBasedRemove ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def loadDataToTitleBasedRemoveNum(vtweb_data): logger.info(":load data to TitleBasedRemoveNum start") target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) try: target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, db_name = target_db, port = target_port, charset = 'utf8') insert_SQL = """ INSERT INTO TitleBasedRemoveNum(reportDate, takeoffDate, trackingWebsite_id, trackingMeta_id, removedNum, complianceTime, ETLDate) VALUES(%s, %s, %s, %s, %s, %s, %s) """ target_mysql.queryNoData("delete from TitleBasedRemoveNum") commitInTurn(commit_num = 50000, data = vtweb_data, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL = insert_SQL) #target_mysql.executeManyCMD(insert_SQL, result) #target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBasedRemoveNum, %s" %e) sendToMe(subject = "TitleBasedRemove ERROR", body = e) sys.exit(0)
def dataTo_matchedVideoSequence(): logger.info("load data target matchedVideoSequence start") sendToMe(subject="matchedVideoSequence start", body="matchedVideoSequence start") #delete from matchedVideoSequence target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db = getConfMysqlInfo( target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = "FOX_DASHBOARD", port = target_port, charset = 'utf8') target_mysql.queryNoData("delete from matchedVideoSequence") target_mysql.commit() target_mysql.closeConn() target_mysql.closeCur() # get arch matchedVideo id and insert into matchedVideoSequence get_data_arch_sql = """ select id, company_id from archTracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ arch_data = getData(get_data_arch_sql, section="vtweb-arch") insert_arch_sql = """insert into matchedVideoSequence (id, company_id) values(%s, %s)""" dataToTarget(arch_data, "FOX_DASHBOARD", insert_arch_sql) # get matchedVideo id and insert into matchedVideoSequence get_data_sql = """ select id, company_id from tracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ data = getData(get_data_sql, section="vtweb") insert_sql = """ insert into matchedVideoSequence (id, company_id) values(%s, %s) ON DUPLICATE KEY UPDATE id = values(id), company_id = values(company_id) """ dataToTarget(data, "FOX_DASHBOARD", insert_sql) sendToMe(subject="matchedVideoSequence end", body="matchedVideoSequence end") logger.info("load data target matchedVideoSequence end")
def dataTo_matchedVideoSequence(): logger.info("load data target matchedVideoSequence start") sendToMe(subject = "matchedVideoSequence start", body = "matchedVideoSequence start") #delete from matchedVideoSequence target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = "FOX_DASHBOARD", port = target_port, charset = 'utf8') target_mysql.queryNoData("delete from matchedVideoSequence") target_mysql.commit() target_mysql.closeConn() target_mysql.closeCur() # get arch matchedVideo id and insert into matchedVideoSequence get_data_arch_sql = """ select id, company_id from archTracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ arch_data = getData(get_data_arch_sql, section = "vtweb-arch") insert_arch_sql = """insert into matchedVideoSequence (id, company_id) values(%s, %s)""" dataToTarget(arch_data, "FOX_DASHBOARD", insert_arch_sql) # get matchedVideo id and insert into matchedVideoSequence get_data_sql = """ select id, company_id from tracker2.matchedVideo where company_id = 10 and created_at >= "2015-03-01" """ data = getData(get_data_sql, section = "vtweb") insert_sql = """ insert into matchedVideoSequence (id, company_id) values(%s, %s) ON DUPLICATE KEY UPDATE id = values(id), company_id = values(company_id) """ dataToTarget(data, "FOX_DASHBOARD", insert_sql) sendToMe(subject = "matchedVideoSequence end", body = "matchedVideoSequence end") logger.info("load data target matchedVideoSequence end")
sum(clipDurationInfringingSum) as clipDurationInfringingSum, sum(reportedViews) as reportedViews, sum(infringingViews) as infringingViews, sum(reportedViewsCMS) as reportedViewsCMS, CURRENT_TIMESTAMP as ETLDate from TitleBased1 as a, TitleBasedTrackingWebsite as b, TitleBasedCountry as c where a.trackingWebsite_id = b.trackingWebsite_id and b.country_id = c.country_id and(a.websiteType = "ugc" or a.websiteType = 'cyberlocker' or a.websiteType = 'hybrid') group by 1, 2, 3, 4, 5, 6, 7, 8) as a left join SiteMap as b on a.trackingWebsite_id = b.trackingWebsite_id group by 1, 2, 3, 4, 5, 6, 7, 8 """ aggregate_TitleBased1_result = target_mysql.queryCMD(aggregate_TitleBased1_SQL) target_mysql.queryNoData("delete from SiteBased") insert_SiteBased_SQL = """ INSERT INTO SiteBased (reportDate, trackingWebsite_id, websiteName, websiteType, websiteDomain, country_id, hostCountry, title, matchedNum, matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero, infringingNumCMS, clipDurationSum, clipDurationInfringingSum, reportedViews, infringingViews, reportedViewsCMS, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE websiteName = VALUES(websiteName), websiteDomain = VALUES(websiteDomain), country_id = VALUES(country_id), hostCountry = VALUES(hostCountry), matchedNum = VALUES(matchedNum), matchedNumDurationNoZero = values(matchedNumDurationNoZero), infringingNum = VALUES(infringingNum), infringingNumDurationNoZero = values(infringingNumDurationNoZero), infringingNumCMS = VALUES(infringingNumCMS),
def aggregateDataToTitleBased1(): logger.info(" aggregate data from TitleBased to TitleBased1 start") target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) try: target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, db_name = target_db, port = target_port, charset = 'utf8') aggregate_TitleBased1_SQL = """ select a.reportDate, a.trackingWebsite_id, ifnull(c.displayName, a.websiteName) as websiteName, a.websiteType, ifnull(b.mapTitle, a.title) title, min(a.tier) as tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from (select a.reportDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, min(a.tier) tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from TitleBasedTmp as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id group by 1, 2, 3, 4, 5) as a left join MetaTitleMapTitle as b on a.title = b.metaTitle left join SiteMap as c on a.trackingWebsite_id = c.trackingWebsite_id group by 1, 2, 3, 4, 5 """ #%date_para_TitleBased1_dict TitleBased1_result = target_mysql.queryCMD(aggregate_TitleBased1_SQL) target_mysql.queryNoData("delete from TitleBased1;") insert_TitleBased1_SQL = """ INSERT INTO TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, tier, matchedNum, matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero, clipDurationSum, clipDurationInfringingSum, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE matchedNum = VALUES(matchedNum), clipDurationSum = VALUES(clipDurationSum), infringingNum = VALUES(infringingNum), ETLDate = VALUES(ETLDate), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), infringingNumDurationNoZero = VALUES(infringingNumDurationNoZero), clipDurationInfringingSum = VALUES(clipDurationInfringingSum), websiteName = VALUES(websiteName), tier = VALUES(tier) """ commitInTurn(commit_num = 50000, data = TitleBased1_result, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insert_TitleBased1_SQL) #target_mysql.insertUpdateCMD(insert_TitleBased1_SQL, TitleBased1_result) #target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBased1, %s" %e) sendToMe(subject = "titleBased_titleBased ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
try: target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db = getConfMysqlInfo( target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, db_name=target_db, port=target_port, charset='utf8') target_TitleBased_SQL = """ insert into TitleBasedTmp (reportDate, trackingWebsite_id, trackingMeta_id, tier, matchedNum, matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero, clipDurationSum, clipDurationInfringingSum, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """ target_mysql.queryNoData("delete from TitleBasedTmp") commitInTurn(commit_num = 50000, data = result_report, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =target_TitleBased_SQL) except Exception, e: logger.debug(": mysql data to TitleBasedTmp, %s" % e) sendToMe(subject="TitleBased ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0) finally: target_mysql.closeCur() target_mysql.closeConn() logger.info(": data to TitleBasedTmp reportDate end") sendToMe(subject="TitleBasedTmp end", body="data to TitleBasedTmp end") #################################################################################################################################
vtweb_mysql.closeConn() logger.info(": extract data from tracker2 reportDate end") # put data to TitleBased logger.info(":load data to TitleBasedTmp reportDate start") try: target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db= getConfMysqlInfo(target_server_section) target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, db_name = target_db, port = target_port, charset = 'utf8') target_TitleBased_SQL = """ insert into TitleBasedTmp (reportDate, trackingWebsite_id, trackingMeta_id, tier, matchedNum, matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero, clipDurationSum, clipDurationInfringingSum, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """ target_mysql.queryNoData("delete from TitleBasedTmp") commitInTurn(commit_num = 50000, data = result_report, executeFun = target_mysql.executeManyCMD, \ commitFun = target_mysql.commit, executeSQL =target_TitleBased_SQL) except Exception, e: logger.debug(": mysql data to TitleBasedTmp, %s" %e) sendToMe(subject = "TitleBased ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0) finally: target_mysql.closeCur() target_mysql.closeConn() logger.info(": data to TitleBasedTmp reportDate end") sendToMe(subject = "TitleBasedTmp end", body = "data to TitleBasedTmp end") #################################################################################################################################
def aggregateDataToTitleBased1(): logger.info(" aggregate data from TitleBased to TitleBased1 start") target_server_section = "target_server_staging" target_host, target_user, target_passwd, target_port, target_db = getConfMysqlInfo( target_server_section) try: target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, db_name=target_db, port=target_port, charset='utf8') aggregate_TitleBased1_SQL = """ select a.reportDate, a.trackingWebsite_id, ifnull(c.displayName, a.websiteName) as websiteName, a.websiteType, ifnull(b.mapTitle, a.title) title, min(a.tier) as tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from (select a.reportDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, min(a.tier) tier, sum(matchedNum) as matchedNum, sum(matchedNumDurationNoZero) as matchedNumDurationNoZero, sum(infringingNum) as infringingNum, sum(infringingNumDurationNoZero) as infringingNumDurationNoZero, sum(clipDurationSum) as clipDurationSum, sum(clipDurationInfringingSum) as clipDurationInfringingSum, current_timestamp as ETLDate from TitleBasedTmp as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id group by 1, 2, 3, 4, 5) as a left join MetaTitleMapTitle as b on a.title = b.metaTitle left join SiteMap as c on a.trackingWebsite_id = c.trackingWebsite_id group by 1, 2, 3, 4, 5 """ #%date_para_TitleBased1_dict TitleBased1_result = target_mysql.queryCMD(aggregate_TitleBased1_SQL) target_mysql.queryNoData("delete from TitleBased1;") insert_TitleBased1_SQL = """ INSERT INTO TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, tier, matchedNum, matchedNumDurationNoZero, infringingNum, infringingNumDurationNoZero, clipDurationSum, clipDurationInfringingSum, ETLDate) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE matchedNum = VALUES(matchedNum), clipDurationSum = VALUES(clipDurationSum), infringingNum = VALUES(infringingNum), ETLDate = VALUES(ETLDate), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), matchedNumDurationNoZero = VALUES(matchedNumDurationNoZero), infringingNumDurationNoZero = VALUES(infringingNumDurationNoZero), clipDurationInfringingSum = VALUES(clipDurationInfringingSum), websiteName = VALUES(websiteName), tier = VALUES(tier) """ commitInTurn(commit_num = 50000, data = TitleBased1_result, executeFun = target_mysql.insertUpdateCMD, \ commitFun = target_mysql.commit, executeSQL = insert_TitleBased1_SQL) #target_mysql.insertUpdateCMD(insert_TitleBased1_SQL, TitleBased1_result) #target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBased1, %s" % e) sendToMe(subject="titleBased_titleBased ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0)