def getInsightViews(start_date, end_date): logger.info(" extral data from Dashboard.matchedVideoViewCountCompletion start") date_dict ={"start_date": start_date, "end_date": end_date} try: get_data_sql = """ select matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, current_timestamp as ETLDate from matchedVideoViewCountCompletion where report_at > "%(start_date)s" and report_at <= "%(end_date)s" """ %date_dict insight_mysql = MySQLHelper(host=insight_host, user=insight_user,passwd=insight_passwd, port = insight_port, db_name = insight_db) data = insight_mysql.queryCMD(get_data_sql) 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 matchedVideoViewCountCompletion (matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, ETLDate) values(%s, %s, %s, %s, %s, %s, %s) """ target_mysql.executeManyCMD(insert_sql, data) target_mysql.commit() except Exception, e: logger.debug("extral data from Dashboard.matchedVideoViewCountCompletion, %s" %e) sendToMe(subject = "titleBased_matchedVideoViewCountCompletio ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def getInsightViews(start_date, end_date): logger.info(" extral data from Dashboard.matchedVideoViewCountCompletion start") date_dict ={"start_date": start_date, "end_date": end_date} try: get_data_sql = """ select matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, current_timestamp as ETLDate from matchedVideoViewCountCompletion where report_at > "%(start_date)s" and report_at <= "%(end_date)s" """ %date_dict insight_mysql = MySQLHelper(host=insight_host, user=insight_user,passwd=insight_passwd, port = insight_port, db_name = insight_db) data = insight_mysql.queryCMD(get_data_sql) 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 matchedVideoViewCountCompletion (matchedVideo_id, trackingWebsite_id, trackingMeta_id, company_id, report_at, view_count, ETLDate) values(%s, %s, %s, %s, %s, %s, %s) """ target_mysql.executeManyCMD(insert_sql, data) target_mysql.commit() except Exception, e: logger.debug("extral data from Dashboard.matchedVideoViewCountCompletion, %s" %e) sendToMe(subject = "titleBased_infringAllViews ERROR", body = str(e).replace("\"", "").replace("'", "").replace("!", "")) sys.exit(0)
def updateViews(start_date, end_date): logger.info(" aggregate data from matchedVideoViewCountCompletion start") date_dict = {"start_date": start_date, "end_date": end_date} try: get_data_sql = """ select a.reportDate, a.trackingWebsite_id, a.websiteName, a.websiteType, ifnull(b.mapTitle, a.title) title, sum(a.infringingViews) as infringingViews, current_timestamp as ETLDate from (select a.report_at as reportDate, a.trackingWebsite_id, c.websiteName, c.websiteType, b.title, sum(a.view_count) as infringingViews from matchedVideoViewCountCompletion as a, TitleBasedMeta as b, TitleBasedTrackingWebsite as c where a.trackingWebsite_id = c.trackingWebsite_id and a.trackingMeta_id = b.trackingMeta_id and c.websiteType = 'ugc' and a.report_at > "%(start_date)s" and a.report_at <= "%(end_date)s" group by 1, 2, 3, 4, 5) as a left join MetaTitleMapTitle as b on a.title = b.metaTitle group by 1, 2, 3, 4, 5 """ % date_dict target_mysql = MySQLHelper(host=target_host, user=target_user, passwd=target_passwd, \ db_name = target_db, port = target_port, charset = 'utf8') data = target_mysql.queryCMD(get_data_sql) insert_sql = """insert into TitleBased1 (reportDate, trackingWebsite_id, websiteName, websiteType, title, infringingViews, ETLDate) values(%s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE infringingViews = values(infringingViews), ETLDate = values(ETLDate) """ target_mysql.executeManyCMD(insert_sql, data) target_mysql.commit() except Exception, e: logger.debug("aggregate data to TitleBased1 ERROR , %s" % e) sendToMe(subject="titleBased_infringViews ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def getMatchedVideo(start_date, end_date): logger.info(" extral data from tracker2.matchedVideo start") date_dict ={"start_date": start_date, "end_date": end_date} try: get_data_sql = """ select a.id as matchedVideo_id, trackingMeta_id, trackingWebsite_id, date_format(first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate, date_format(a.created_at, "%%Y-%%m-%%d") as created_at, CURRENT_TIMESTAMP as ETLDate from matchedVideo as a, tracker2.metaExtraInfo as b , mddb.trackingWebsite as c where a.company_id =14 and b.company_id = 14 and c.website_type = "ugc" and a.trackingMeta_id = b.meta_id and a.trackingWebsite_id = c.id and a.hide_flag = 2 and a.created_at >= "2015-03-01" and count_send_notice > 0 and date_format(a.first_send_notice_date, "%%Y-%%m-%%d") > "%(start_date)s" and date_format(a.first_send_notice_date, "%%Y-%%m-%%d") <= "%(end_date)s" """ %date_dict vtweb_mysql = MySQLHelper(host=vt_host, user=vt_user,passwd=vt_passwd, port = vt_port, db_name = vt_db) vtweb_mysql.queryCMD("set time_zone = '-8:00'") data = vtweb_mysql.queryCMD(get_data_sql) 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 matchedVideo (matchedVideo_id, trackingMeta_id, trackingWebsite_id, firstSendNoticeDate, reportDate, ETLDate) values(%s, %s, %s, %s, %s, %s) """ target_mysql.executeManyCMD(insert_sql, data) target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.matchedVideo, %s" %e) sendToMe(subject = "titleBased_infringAllViews ERROR", body = str(e).replace("\"", "").replace("'", "").replace("!", "")) sys.exit(0)
vtweb_mysql.closeCur() vtweb_mysql.closeConn() logger.info(": extract data from tracker2 end") 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.executeManyCMD(insert_SQL, result) target_mysql.commit() except Exception, e: logger.debug(": load data to TitleBasedRemoveNum, %s" %e) sys.exit(0) finally: target_mysql.closeCur() target_mysql.closeConn() logger.info(":load data to TitleBasedRemoveNum end") ################################################################################################################################# logger.info(":extract data from TitleBasedRemoveNum start") target_server_section = "target_server_staging" 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
def getMatchedVideo(min_reportDate, max_reportDate, min_updateDate): logger.info(" extral data from tracker2.matchedVideo start") date_dict = { "min_reportDate": min_reportDate, "max_reportDate": max_reportDate, "min_updateDate": min_updateDate } try: get_data_sql = """ select a.id as matchedVideo_id, a.trackingMeta_id, a.trackingWebsite_id, date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate, a.hide_flag as hideFlag, count_send_notice as countSendNotice, date_format(a.created_at, "%%Y-%%m-%%d") as reportDate, date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate, CURRENT_TIMESTAMP as ETLDate from matchedVideo as a, mddb.trackingWebsite as b where date_format(a.created_at, "%%Y-%%m-%%d") > "%(min_reportDate)s" and date_format(a.created_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s" and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s" and a.company_id =14 and b.website_type = "ugc" and a.created_at >= "2015-03-01" and a.trackingWebsite_id = b.id union all select a.id as matchedVideo_id, a.trackingMeta_id, a.trackingWebsite_id, date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate, a.hide_flag as hideFlag, count_send_notice as countSendNotice, date_format(a.created_at, "%%Y-%%m-%%d") as reportDate, date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate, CURRENT_TIMESTAMP as ETLDate from matchedVideo as a, mddb.trackingWebsite as b where date_format(a.created_at, "%%Y-%%m-%%d") <= "%(min_reportDate)s" and date_format(a.updated_at, "%%Y-%%m-%%d") > "%(min_updateDate)s" and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s" and a.company_id =14 and b.website_type = "ugc" and a.created_at >= "2015-03-01" and a.trackingWebsite_id = b.id """ % date_dict vtweb_mysql = MySQLHelper(host=vt_host, user=vt_user, passwd=vt_passwd, port=vt_port, db_name=vt_db) vtweb_mysql.queryCMD("set time_zone = '-8:00'") data = vtweb_mysql.queryCMD(get_data_sql) 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 matchedVideo (matchedVideo_id, trackingMeta_id, trackingWebsite_id, firstSendNoticeDate, hideFlag, countSendNotice, reportDate, updateDate, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE firstSendNoticeDate = values(firstSendNoticeDate), hideFlag = values(hideFlag), countSendNotice = values(countSendNotice), reportDate = values(reportDate), updateDate = values(updateDate), ETLDate =values(ETLDate) """ target_mysql.executeManyCMD(insert_sql, data) target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.matchedVideo, %s" % e) sendToMe(subject="titleBased_infringAllViews ERROR", body=re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
def getMatchedVideo(min_reportDate, max_reportDate, min_updateDate): logger.info(" extral data from tracker2.matchedVideo start") date_dict ={"min_reportDate": min_reportDate, "max_reportDate": max_reportDate, "min_updateDate": min_updateDate} try: get_data_sql = """ select a.id as matchedVideo_id, a.trackingMeta_id, a.trackingWebsite_id, date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate, a.hide_flag as hideFlag, count_send_notice as countSendNotice, date_format(a.created_at, "%%Y-%%m-%%d") as reportDate, date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate, CURRENT_TIMESTAMP as ETLDate from matchedVideo as a, mddb.trackingWebsite as b where date_format(a.created_at, "%%Y-%%m-%%d") > "%(min_reportDate)s" and date_format(a.created_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s" and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s" and a.company_id =14 and b.website_type = "ugc" and a.created_at >= "2015-03-01" union all select a.id as matchedVideo_id, a.trackingMeta_id, a.trackingWebsite_id, date_format(a.first_send_notice_date, "%%Y-%%m-%%d") as firstSendNoticeDate, a.hide_flag as hideFlag, count_send_notice as countSendNotice, date_format(a.created_at, "%%Y-%%m-%%d") as reportDate, date_format(a.updated_at, "%%Y-%%m-%%d") as updateDate, CURRENT_TIMESTAMP as ETLDate from matchedVideo as a, mddb.trackingWebsite as b where date_format(a.created_at, "%%Y-%%m-%%d") <= "%(min_reportDate)s" and date_format(a.updated_at, "%%Y-%%m-%%d") > "%(min_updateDate)s" and date_format(a.updated_at, "%%Y-%%m-%%d") <= "%(max_reportDate)s" and a.company_id =14 and b.website_type = "ugc" and a.created_at >= "2015-03-01" """ %date_dict vtweb_mysql = MySQLHelper(host=vt_host, user=vt_user,passwd=vt_passwd, port = vt_port, db_name = vt_db) vtweb_mysql.queryCMD("set time_zone = '-8:00'") data = vtweb_mysql.queryCMD(get_data_sql) 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 matchedVideo (matchedVideo_id, trackingMeta_id, trackingWebsite_id, firstSendNoticeDate, hideFlag, countSendNotice, reportDate, updateDate, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE firstSendNoticeDate = values(firstSendNoticeDate), hideFlag = values(hideFlag), countSendNotice = values(countSendNotice), reportDate = values(reportDate), updateDate = values(updateDate), ETLDate =values(ETLDate) """ target_mysql.executeManyCMD(insert_sql, data) target_mysql.commit() except Exception, e: logger.debug("extral data from tracker2.matchedVideo, %s" %e) sendToMe(subject = "titleBased_infringAllViews ERROR", body = re.sub(r'\'|"|!', "", str(e))) sys.exit(0)
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 TitleBased (reportDate, firstSendNoticeDate, trackingWebsite_id, trackingMeta_id, tier, matchedNum, infringingNum, clipDurationSum, ETLDate) values(%s, %s, %s, %s, %s, %s, %s, %s, %s) """ target_mysql.executeManyCMD(target_TitleBased_SQL, result_report) target_mysql.commit() except Exception, e: logger.debug(": mysql data to TitleBased, %s" % e) sys.exit(0) finally: target_mysql.closeCur() target_mysql.closeConn() logger.info(": data to TitleBased reportDate end") ################################################################################################################################# logger.info(": extract data from tracker2 firstSendNoticeDate start") # date_para_TitleBased_firstSendNoticeDate_min = getMinDatePara("TitleBased", "firstSendNoticeDate") if date_para_TitleBased_firstSendNoticeDate_min == None: date_para_TitleBased_firstSendNoticeDate_min = "2015-01-01" date_para_TitleBased_firstSendNoticeDate_max = time.strftime( "%Y-%m-%d", time.localtime(time.time() - 0 * 24 * 60 * 60))