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))
