Yurik has submitted this change and it was merged. Change subject: Generified scripts, extra script for new hive fmt ......................................................................
Generified scripts, extra script for new hive fmt Change-Id: If6a01ec549ec8d78efe27d0bce176bb694a71889 --- M scripts/run-hivezero.sh M scripts/weblogs2.py A scripts/zero-counts2.hql A scripts/zero-counts3.hql 4 files changed, 187 insertions(+), 15 deletions(-) Approvals: Yurik: Verified; Looks good to me, approved diff --git a/scripts/run-hivezero.sh b/scripts/run-hivezero.sh index 0081304..35c2855 100755 --- a/scripts/run-hivezero.sh +++ b/scripts/run-hivezero.sh @@ -1,8 +1,9 @@ #!/bin/bash -# $1 $2 $3 $4 %5 $6 -# ./run-hivezero.sh wmf_raw.webrequest 2014 10 1 31 -# ./run-hivezero.sh webreq_archive 2014 10 1 31 overwrite +# $1 $2 $3 $4 %5 $6 $7 $8 +# ./run-hivezero.sh wmf_raw.webrequest 2014 10 1 31 zero_webstats zero-counts.hql +# ./run-hivezero.sh wmf.webrequest 2014 10 1 31 zero_webstats2 zero-counts2.hql +# ./run-hivezero.sh webreq_archive 2014 10 1 31 zero_webstats__ zero-counts.hql overwrite set -e @@ -23,6 +24,19 @@ monthTo=$3 fi +if [[ -z "$6" ]]; then + dsttable=zero_webstats2 +else + dsttable=$6 +fi + +if [[ -z "$7" ]]; then + script=zero-counts2.hql +else + script=$7 +fi + + for ((month = $monthFrom; month <= $monthTo; month++)); do for ((day = $4; day <= $last; day++)); do @@ -32,6 +46,8 @@ if [[ "$table" == 'wmf_raw.webrequest' ]]; then path="/mnt/hdfs/wmf/data/raw/webrequest/webrequest_upload/hourly/$year/$(printf "%02d" $month)/$(printf "%02d" $day)/23" + elif [[ "$table" == 'wmf.webrequest' ]]; then + path="/mnt/hdfs/wmf/data/wmf/webrequest/webrequest_source=mobile/year=$year/month=$month/day=$day/hour=23" else path="/mnt/hdfs/user/hive/warehouse/yurik.db/$table/year=$year/month=$month/day=$day" fi @@ -45,20 +61,20 @@ continue fi - path="/mnt/hdfs/user/hive/warehouse/yurik.db/zero_webstats/date="$date + path="/mnt/hdfs/user/hive/warehouse/yurik.db/"$dsttable"/date="$date echo "***** Checking if '$path' exists" if [ -d $path ]; then - if [ "$6" == "overwrite" ]; then + if [ "$8" == "overwrite" ]; then echo "***** Droping partition '$date'" - hive -e "use yurik; ALTER TABLE zero_webstats DROP IF EXISTS PARTITION(date = '$date');" + hive -e "use yurik; ALTER TABLE "$dsttable" DROP IF EXISTS PARTITION(date = '$date');" else echo "***** Skipping '$date'" continue fi fi echo -e "*****\n*****\n*****\n*****" - echo "*****" hive -f zero-counts.hql -d "table="$table -d "year="$year -d "month="$month -d "day="$day -d "date="$date - export HADOOP_HEAPSIZE=2048 && hive -f zero-counts.hql -d "table="$table -d "year="$year -d "month="$month -d "day="$day -d "date="$date + echo "*****" hive -f $script -d "table="$table -d "dsttable="$dsttable -d "year="$year -d "month="$month -d "day="$day -d "date="$date + export HADOOP_HEAPSIZE=2048 && hive -f $script -d "table="$table -d "dsttable="$dsttable -d "year="$year -d "month="$month -d "day="$day -d "date="$date fi diff --git a/scripts/weblogs2.py b/scripts/weblogs2.py index f2367df..43023e6 100644 --- a/scripts/weblogs2.py +++ b/scripts/weblogs2.py @@ -117,7 +117,8 @@ class WebLogProcessor2(LogProcessor): - def __init__(self, settingsFile='settings/weblogs2.json'): + def __init__(self, settingsFile): + print('Using settings %s' % settingsFile) super(WebLogProcessor2, self).__init__(settingsFile, 'web2') self._configs = None @@ -129,6 +130,10 @@ def defaultSettings(self, suffix): s = super(WebLogProcessor2, self).defaultSettings(suffix) s.checkAfterTs = False + s.hiveTable = 'wmf_raw.webrequest' + s.dstTable = 'zero_webstats' + s.hqlScript = 'zero-counts.hql' + s.wikiPageSuffix = '' return s def onSavingSettings(self): @@ -146,9 +151,17 @@ def runHql(self): os.environ["HADOOP_HEAPSIZE"] = "2048" - for date in dateRange(self.settings.checkAfterTs, datetime.today()): - path = '/mnt/hdfs/wmf/data/raw/webrequest/webrequest_upload/hourly/%s/23' \ + if self.settings.hiveTable == 'wmf_raw.webrequest': + pathFunc = lambda dt: '/mnt/hdfs/wmf/data/raw/webrequest/webrequest_upload/hourly/%s/23' \ % strftime("%Y/%m/%d", date.timetuple()) + elif self.settings.hiveTable == 'wmf.webrequest': + pathFunc = lambda dt: '/mnt/hdfs/wmf/data/wmf/webrequest/webrequest_source=mobile/year=%s/month=%s/day=%s/hour=23' \ + % (date.year, date.month, date.day) + else: + raise 'Unknown hiveTable = ' + str(self.settings.hiveTable) + + for date in dateRange(self.settings.checkAfterTs, datetime.today()): + path = pathFunc(date) if not os.path.exists(path): continue size = sum(os.path.getsize(os.path.join(path, f)) for f in os.listdir(path) if @@ -162,9 +175,10 @@ continue cmd = ['hive', - '-f', 'zero-counts.hql', + '-f', self.settings.hqlScript, '-S', # --silent - '-d', 'table=wmf_raw.webrequest', + '-d', 'table=' + self.settings.hiveTable, + '-d', 'dsttable=' + self.settings., '-d', 'year=' + strftime("%Y", date.timetuple()), '-d', 'month=' + strftime("%m", date.timetuple()), '-d', 'day=' + strftime("%d", date.timetuple()), @@ -474,7 +488,7 @@ wiki = self.getWiki() wiki( 'edit', - title=wikiTitle, + title=wikiTitle + self.settings.wikiPageSuffix, summary='refreshing data', text=text, token=wiki.token() @@ -496,4 +510,5 @@ if __name__ == '__main__': # WebLogProcessor2('settings/weblogs2.local.json').manualRun() - WebLogProcessor2().safeRun() + import sys + WebLogProcessor2('settings/weblogs2.json' if len(sys.argv) < 2 else sys.argv[1]).safeRun() diff --git a/scripts/zero-counts2.hql b/scripts/zero-counts2.hql new file mode 100644 index 0000000..4c89b6c --- /dev/null +++ b/scripts/zero-counts2.hql @@ -0,0 +1,79 @@ +set hive.exec.dynamic.partition.mode=nonstrict; +SET hive.exec.compress.output=false; +--^ To work around HIVE-3296, we have SETs before any comments + +-- Extracts zero stats from webrequests into a separate table +-- +-- Usage: +-- hive -f zero-counts2.hql -d year=2014 -d month=9 -d day=15 -d date=2014-09-15 -d dsttable=zero_webstats3 +-- Date is duplicated because I haven't figured an easy way to set date=printf() +-- +-- set hivevar:year=2014; +-- set hivevar:month=10; +-- set hivevar:day=21; +-- set hivevar:date=2014-10-21; +-- set hivevar:dsttable=zero_webstats3; + +use yurik; + + +CREATE TABLE IF NOT EXISTS ${dsttable} ( + xcs string, + via string, + ipset string, + https string, + lang string, + subdomain string, + site string, + count bigint) +PARTITIONED BY ( + date string) +ROW FORMAT DELIMITED + FIELDS TERMINATED BY '\t'; + + +-- ALTER TABLE ${dsttable} DROP IF EXISTS PARTITION(date < '${date}') + + +INSERT OVERWRITE TABLE ${dsttable} + PARTITION(date="${date}") IF NOT EXISTS + SELECT + xcs, via, ipset, https, lang, subdomain, site, COUNT(*) count + FROM ( + SELECT + COALESCE(regexp_extract(x_analytics, 'zero=([^\;]+)'), '') xcs, + COALESCE(regexp_extract(x_analytics, 'proxy=([^\;]+)'), '') via, + COALESCE(regexp_extract(x_analytics, 'zeronet=([^\;]+)'), '') ipset, + if (x_analytics LIKE '%https=1%', 'https', '') https, + COALESCE(regexp_extract(uri_host, '^([A-Za-z0-9-]+)(\\.(zero|m))?\\.([a-z]*)\\.org$', 1), '') lang, + COALESCE(regexp_extract(uri_host, '^([A-Za-z0-9-]+)(\\.(zero|m))?\\.([a-z]*)\\.org$', 3), '') subdomain, + COALESCE(regexp_extract(uri_host, '^([A-Za-z0-9-]+)(\\.(zero|m))?\\.([a-z]*)\\.org$', 4), '') site + + FROM wmf.webrequest + WHERE + webrequest_source IN ('text', 'mobile') + AND year=${year} + AND month=${month} + AND day=${day} + AND x_analytics LIKE '%zero=%' + AND SUBSTR(uri_path, 1, 6) = '/wiki/' + AND ( + ( + SUBSTR(ip, 1, 9) != '10.128.0.' + AND SUBSTR(ip, 1, 11) NOT IN ( + '208.80.152.', + '208.80.153.', + '208.80.154.', + '208.80.155.', + '91.198.174.' + ) + ) OR x_forwarded_for != '-' + ) + AND SUBSTR(uri_path, 1, 31) != '/wiki/Special:CentralAutoLogin/' + AND http_status NOT IN ( '301', '302', '303' ) + AND uri_host RLIKE '^[A-Za-z0-9-]+(\\.(zero|m))?\\.[a-z]*\\.org$' + AND NOT (SPLIT(TRANSLATE(SUBSTR(uri_path, 7), ' ', '_'), '#')[0] RLIKE '^[Uu]ndefined$') + + ) prepared + GROUP BY xcs, via, ipset, https, lang, subdomain, site + DISTRIBUTE BY printf('%d-%02d-%02d', ${year}, ${month}, ${day}); diff --git a/scripts/zero-counts3.hql b/scripts/zero-counts3.hql new file mode 100644 index 0000000..8315b49 --- /dev/null +++ b/scripts/zero-counts3.hql @@ -0,0 +1,62 @@ +set hive.exec.dynamic.partition.mode=nonstrict; +SET hive.exec.compress.output=false; +--^ To work around HIVE-3296, we have SETs before any comments + +-- Extracts zero stats from webrequests into a separate table +-- +-- Usage: +-- hive -f zero-counts3.hql -d year=2015 -d month=4 -d day=9 -d date=2015-04-09 -d dsttable=zero_webstats3 +-- Date is duplicated because I haven't figured an easy way to set date=printf() +-- +-- set hivevar:year=2014; +-- set hivevar:month=10; +-- set hivevar:day=21; +-- set hivevar:date=2014-10-21; +-- set hivevar:dsttable=zero_webstats3; + +use yurik; + + +CREATE TABLE IF NOT EXISTS ${dsttable} ( + xcs string, + via string, + ipset string, + https string, + lang string, + subdomain string, + site string, + count bigint) +PARTITIONED BY ( + date string) +ROW FORMAT DELIMITED + FIELDS TERMINATED BY '\t'; + + +-- ALTER TABLE ${dsttable} DROP IF EXISTS PARTITION(date < '${date}') + + +INSERT OVERWRITE TABLE ${dsttable} + PARTITION(date="${date}") IF NOT EXISTS + SELECT + xcs, via, ipset, https, lang, subdomain, site, COUNT(*) count + FROM ( + SELECT + COALESCE(regexp_extract(x_analytics, 'zero=([^\;]+)'), '') xcs, + COALESCE(regexp_extract(x_analytics, 'proxy=([^\;]+)'), '') via, + COALESCE(regexp_extract(x_analytics, 'zeronet=([^\;]+)'), '') ipset, + if (x_analytics LIKE '%https=1%', 'https', '') https, + COALESCE(regexp_extract(uri_host, '^([A-Za-z0-9-]+)(\\.(zero|m))?\\.([a-z]*)\\.org$', 1), '') lang, + COALESCE(regexp_extract(uri_host, '^([A-Za-z0-9-]+)(\\.(zero|m))?\\.([a-z]*)\\.org$', 3), '') subdomain, + COALESCE(regexp_extract(uri_host, '^([A-Za-z0-9-]+)(\\.(zero|m))?\\.([a-z]*)\\.org$', 4), '') site + + FROM wmf.webrequest + WHERE + webrequest_source IN ('text', 'mobile') + AND year=${year} + AND month=${month} + AND day=${day} + AND x_analytics LIKE '%zero=%' -- use is_zero field instead, once db is fixed + AND is_pageview + ) prepared + GROUP BY xcs, via, ipset, https, lang, subdomain, site + DISTRIBUTE BY printf('%d-%02d-%02d', ${year}, ${month}, ${day}); -- To view, visit https://gerrit.wikimedia.org/r/203523 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: If6a01ec549ec8d78efe27d0bce176bb694a71889 Gerrit-PatchSet: 2 Gerrit-Project: analytics/zero-sms Gerrit-Branch: master Gerrit-Owner: Yurik <yu...@wikimedia.org> Gerrit-Reviewer: Yurik <yu...@wikimedia.org> _______________________________________________ MediaWiki-commits mailing list MediaWiki-commits@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits