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