Joal has submitted this change and it was merged.
Change subject: Add oozie job to compute browser usage reports
......................................................................
Add oozie job to compute browser usage reports
It computes 2 reports: mobile-web and desktop+mobile-web.
As both use the same hql template and are triggered using
the same coordinator, the workflow is the one that lauches
one report first, and then the other one. The output is
stored in tsv weekly files.
Bug: T88504
Change-Id: I106a9853b07dac5a0efabaf92a9987de2b8a7ec2
---
A oozie/browser/general/README.md
A oozie/browser/general/browser_general.hql
A oozie/browser/general/coordinator.properties
A oozie/browser/general/coordinator.xml
A oozie/browser/general/workflow.xml
5 files changed, 397 insertions(+), 0 deletions(-)
Approvals:
Joal: Verified; Looks good to me, approved
diff --git a/oozie/browser/general/README.md b/oozie/browser/general/README.md
new file mode 100644
index 0000000..d2388e3
--- /dev/null
+++ b/oozie/browser/general/README.md
@@ -0,0 +1,31 @@
+# Browser usage
+
+This job computes weekly browser usage reports from the table:
+pageview_hourly. It generates 2 TSV datasets: mobile report, and
+desktop+mobile report.
+
+Output is archived in the folders:
+```archive/browser/general/mobile_web-{year}-{month}-{day}``` and
+```archive/browser/general/desktop_and_mobile_web-{year}-{month}-{day}```
+
+# Outline
+
+* ```browser_general.hql``` is the hive query that collects the
+ data from the tables, aggregates it, and writes the reports in
+ the given destination directory. It is actually a template with
+ some dynamic parameters: input and output paths, time info and
+ a filter for access methods to include in the report.
+
+* ```workflow.xml``` declares the actions that oozie will take
+ when calling the hive query. In this case, it executes the
+ hive query twice, once for mobile and once for desktop+mobile.
+ It also sets up some oozie-specific configuration parameters.
+
+* ```coordinator.xml``` determines when the workflow shoud be
+ executed and on which frequency. In this case, it depends on
+ 2 datasets: projectviews and pageviews. Both of them need a
+ full week of data, starting on Sunday.
+
+* ```coordinator.properties``` defines the default parameters
+ for the pipeline. They will be passed implicitly or explicitly
+ down the coordinator, the workflow and the query.
diff --git a/oozie/browser/general/browser_general.hql
b/oozie/browser/general/browser_general.hql
new file mode 100644
index 0000000..6962cd7
--- /dev/null
+++ b/oozie/browser/general/browser_general.hql
@@ -0,0 +1,76 @@
+
+-- Parameters:
+-- projectview_source -- Table containing hourly projectviews.
+-- pageview_source -- Table containing hourly pageviews.
+-- destination_directory -- Directory where to write the report.
+-- access_methods -- Comma-separated list of access methods
+-- to include: 'xxx', 'yyy', ...
+-- year -- Year of interval's start date.
+-- month -- Month of interval's start date.
+-- day -- Day of interval's start date.
+-- time_window -- Time window to compute in days. The end
+-- date will be calculated adding this to
+-- the start date. Start date is included
+-- in the report, but end date is not.
+--
+-- Usage:
+-- hive -f browser_general.hql \
+-- -d projectview_source=wmf.projectview_hourly \
+-- -d pageview_source=wmf.pageview_hourly \
+-- -d destination_directory=/tmp/foo \
+-- -d access_methods='desktop' \
+-- -d year=2015 \
+-- -d month=10 \
+-- -d day=11 \
+-- -d time_window=7
+--
+
+-- Permits cartesian join of small enough table.
+SET hive.mapred.mode = nonstrict;
+
+SET start_date = CONCAT('${year}-', LPAD(${month}, 2, '0'), '-', LPAD(${day},
2, '0'));
+SET end_date = DATE_ADD(${hiveconf:start_date}, ${time_window});
+
+WITH total AS (
+ SELECT
+ SUM(view_count) as view_count_total
+ FROM
+ ${projectview_source}
+ WHERE
+ CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) >=
${hiveconf:start_date}
+ AND CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) <
${hiveconf:end_date}
+ AND agent_type = 'user'
+ AND access_method IN (${access_methods})
+)
+INSERT OVERWRITE DIRECTORY '${destination_directory}'
+ SELECT
+ tsv_line
+ FROM (
+ SELECT
+ -- Since "ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'" only
+ -- works for hive>=1.2.0 (see HIVE-5672), we have to prepare the
+ -- lines by hand through concatenation.
+ CONCAT_WS(
+ '\t',
+ CONCAT(user_agent_map['os_family'], ' ',
user_agent_map['os_major']),
+ CONCAT(user_agent_map['browser_family'], ' ',
user_agent_map['browser_major']),
+ CAST(SUM(view_count) * 100 / total.view_count_total AS string)
+ ) AS tsv_line,
+ SUM(view_count) * 100 / total.view_count_total AS percent
+ FROM
+ ${pageview_source}
+ JOIN total
+ WHERE
+ CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) >=
${hiveconf:start_date}
+ AND CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))
< ${hiveconf:end_date}
+ AND agent_type = 'user'
+ AND access_method IN (${access_methods})
+ GROUP BY
+ CONCAT(user_agent_map['os_family'], ' ',
user_agent_map['os_major']),
+ CONCAT(user_agent_map['browser_family'], ' ',
user_agent_map['browser_major']),
+ total.view_count_total
+ HAVING
+ (SUM(view_count) * 100 / total.view_count_total) > 0.5
+ ORDER BY percent DESC
+ ) AS tsv_lines
+;
diff --git a/oozie/browser/general/coordinator.properties
b/oozie/browser/general/coordinator.properties
new file mode 100644
index 0000000..5cf8c64
--- /dev/null
+++ b/oozie/browser/general/coordinator.properties
@@ -0,0 +1,64 @@
+# Configures a coordinator to generate browser usage reports from the
+# pageview_houly data. Any of the following properties are overridable
+# with -D. Usage:
+# oozie job -Duser=$USER -Dstart_time=2015-10-11T00:00Z -submit \
+# -config oozie/browser/general/coordinator.properties
+#
+# NOTE: The $oozie_directory must be synced to HDFS so that all
+# relevant .xml files exist there when this job is submitted.
+
+name_node = hdfs://analytics-hadoop
+job_tracker = resourcemanager.analytics.eqiad.wmnet:8032
+queue_name = default
+user = hdfs
+
+# Base path in HDFS to refinery.
+# When submitting this job for production, you should
+# override this to point directly at a deployed
+# directory name, and not the 'symbolic' 'current' directory.
+# E.g. /wmf/refinery/2015-01-05T17.59.18Z--7bb7f07
+refinery_directory = ${name_node}/wmf/refinery/current
+
+# Base path in HDFS to oozie files.
+# Other files will be used relative to this path.
+oozie_directory = ${refinery_directory}/oozie
+
+# HDFS path to coordinator to run.
+coordinator_file =
${oozie_directory}/browser/general/coordinator.xml
+
+# HDFS path to workflow to run.
+workflow_file =
${oozie_directory}/browser/general/workflow.xml
+
+# HDFS path to projectview dataset definitions
+projectview_datasets_file = ${oozie_directory}/projectview/datasets.xml
+projectview_data_directory = ${name_node}/wmf/data/wmf/projectview
+
+# HDFS path to pageview dataset definitions
+pageview_datasets_file = ${oozie_directory}/pageview/datasets.xml
+pageview_data_directory = ${name_node}/wmf/data/wmf/pageview
+
+# HDFS path where to write the reports.
+output_base_path =
${name_node}/wmf/data/archive/browser/general
+
+# Initial import time of the datasets.
+start_time = 2015-10-11T00:00Z
+
+# Time to stop running this coordinator. Year 3000 == never!
+stop_time = 3000-01-01T00:00Z
+
+# Frequency and granularity of the reports in days (in this case: weekly).
+time_window = 7
+
+# HDFS path to hive-site.xml file. This is needed to run hive actions.
+hive_site_xml = ${oozie_directory}/util/hive/hive-site.xml
+
+# Fully qualified Hive table name for projectviews.
+projectview_source = wmf.projectview_hourly
+
+# Fully qualified Hive table name for pageviews.
+pageview_source = wmf.pageview_hourly
+
+# Coordinator to start.
+oozie.coord.application.path = ${coordinator_file}
+oozie.use.system.libpath = true
+oozie.action.external.stats.write = true
diff --git a/oozie/browser/general/coordinator.xml
b/oozie/browser/general/coordinator.xml
new file mode 100644
index 0000000..f4c7746
--- /dev/null
+++ b/oozie/browser/general/coordinator.xml
@@ -0,0 +1,78 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<coordinator-app xmlns="uri:oozie:coordinator:0.4"
+ name="browser_general-coord"
+ frequency="${coord:days(time_window)}"
+ start="${start_time}"
+ end="${stop_time}"
+ timezone="Universal">
+
+ <parameters>
+ <!-- Required properties. -->
+ <property><name>queue_name</name></property>
+ <property><name>name_node</name></property>
+ <property><name>job_tracker</name></property>
+ <property><name>user</name></property>
+
+ <property><name>workflow_file</name></property>
+ <property><name>start_time</name></property>
+ <property><name>stop_time</name></property>
+ <property><name>time_window</name></property>
+
+ <property><name>projectview_datasets_file</name></property>
+ <property><name>projectview_data_directory</name></property>
+
+ <property><name>pageview_datasets_file</name></property>
+ <property><name>pageview_data_directory</name></property>
+
+ <property><name>hive_site_xml</name></property>
+ <property><name>projectview_source</name></property>
+ <property><name>pageview_source</name></property>
+ <property><name>output_base_path</name></property>
+ </parameters>
+
+ <controls>
+ <timeout>-1</timeout>
+ <concurrency>2</concurrency>
+ <throttle>2</throttle>
+ </controls>
+
+ <datasets>
+ <include>${projectview_datasets_file}</include>
+ <include>${pageview_datasets_file}</include>
+ </datasets>
+
+ <input-events>
+ <data-in name="projectview_hourly_input" dataset="projectview_hourly">
+ <!-- 7 days of data in hours -->
+ <start-instance>${coord:current(0)}</start-instance>
+ <end-instance>${coord:current(time_window * 24 - 1)}</end-instance>
+ </data-in>
+ <data-in name="pageview_hourly_input" dataset="pageview_hourly">
+ <!-- 7 days of data in hours -->
+ <start-instance>${coord:current(0)}</start-instance>
+ <end-instance>${coord:current(time_window * 24 - 1)}</end-instance>
+ </data-in>
+ </input-events>
+
+ <action>
+ <workflow>
+ <app-path>${workflow_file}</app-path>
+ <configuration>
+
+ <property>
+ <name>year</name>
+ <value>${coord:formatTime(coord:nominalTime(),
"y")}</value>
+ </property>
+ <property>
+ <name>month</name>
+ <value>${coord:formatTime(coord:nominalTime(),
"M")}</value>
+ </property>
+ <property>
+ <name>day</name>
+ <value>${coord:formatTime(coord:nominalTime(),
"d")}</value>
+ </property>
+
+ </configuration>
+ </workflow>
+ </action>
+</coordinator-app>
diff --git a/oozie/browser/general/workflow.xml
b/oozie/browser/general/workflow.xml
new file mode 100644
index 0000000..7bcfd1b
--- /dev/null
+++ b/oozie/browser/general/workflow.xml
@@ -0,0 +1,148 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<workflow-app xmlns="uri:oozie:workflow:0.4"
+ name="browser_general-${year}-${month}-${day}-wf">
+
+ <parameters>
+ <!-- Default values for inner oozie settings -->
+ <property>
+ <name>oozie_launcher_queue_name</name>
+ <value>${queue_name}</value>
+ </property>
+ <property>
+ <name>oozie_launcher_memory</name>
+ <value>256</value>
+ </property>
+
+ <!-- Required properties -->
+ <property><name>queue_name</name></property>
+ <property><name>name_node</name></property>
+ <property><name>job_tracker</name></property>
+ <property><name>user</name></property>
+
+ <!-- Computation related properties-->
+ <property>
+ <name>hive_script</name>
+ <value>browser_general.hql</value>
+ <description>Hive script to run.</description>
+ </property>
+ <property>
+ <name>hive_site_xml</name>
+ <description>hive-site.xml file path in HDFS.</description>
+ </property>
+ <property>
+ <name>projectview_source</name>
+ <description>Table containing hourly projectviews.</description>
+ </property>
+ <property>
+ <name>pageview_source</name>
+ <description>Table containing hourly pageviews.</description>
+ </property>
+ <property>
+ <name>output_base_path</name>
+ <description>Directory where to write the reports.</description>
+ </property>
+ <property>
+ <name>year</name>
+ <description>Year of interval's start date.</description>
+ </property>
+ <property>
+ <name>month</name>
+ <description>Month of interval's start date.</description>
+ </property>
+ <property>
+ <name>day</name>
+ <description>Day of interval's start date.</description>
+ </property>
+ <property>
+ <name>time_window</name>
+ <description>Length of interval in days.</description>
+ </property>
+ </parameters>
+
+ <start to="compute_mobile_web"/>
+
+ <action name="compute_mobile_web">
+ <hive xmlns="uri:oozie:hive-action:0.2">
+ <job-tracker>${job_tracker}</job-tracker>
+ <name-node>${name_node}</name-node>
+ <job-xml>${hive_site_xml}</job-xml>
+ <configuration>
+ <property>
+ <name>mapreduce.job.queuename</name>
+ <value>${queue_name}</value>
+ </property>
+ <!--make sure oozie:launcher runs in a low priority queue -->
+ <property>
+ <name>oozie.launcher.mapred.job.queue.name</name>
+ <value>${oozie_launcher_queue_name}</value>
+ </property>
+ <property>
+ <name>oozie.launcher.mapreduce.map.memory.mb</name>
+ <value>${oozie_launcher_memory}</value>
+ </property>
+ <property>
+ <name>hive.exec.scratchdir</name>
+ <value>/tmp/hive-${user}</value>
+ </property>
+ </configuration>
+
+ <script>${hive_script}</script>
+ <param>projectview_source=${projectview_source}</param>
+ <param>pageview_source=${pageview_source}</param>
+
<param>destination_directory=${output_base_path}/mobile_web-${year}-${month}-${day}</param>
+ <param>access_methods='mobile web'</param>
+ <param>year=${year}</param>
+ <param>month=${month}</param>
+ <param>day=${day}</param>
+ <param>time_window=${time_window}</param>
+ </hive>
+
+ <ok to="compute_desktop_and_mobile_web"/>
+ <error to="kill"/>
+ </action>
+
+ <action name="compute_desktop_and_mobile_web">
+ <hive xmlns="uri:oozie:hive-action:0.2">
+ <job-tracker>${job_tracker}</job-tracker>
+ <name-node>${name_node}</name-node>
+ <job-xml>${hive_site_xml}</job-xml>
+ <configuration>
+ <property>
+ <name>mapreduce.job.queuename</name>
+ <value>${queue_name}</value>
+ </property>
+ <!--make sure oozie:launcher runs in a low priority queue -->
+ <property>
+ <name>oozie.launcher.mapred.job.queue.name</name>
+ <value>${oozie_launcher_queue_name}</value>
+ </property>
+ <property>
+ <name>oozie.launcher.mapreduce.map.memory.mb</name>
+ <value>${oozie_launcher_memory}</value>
+ </property>
+ <property>
+ <name>hive.exec.scratchdir</name>
+ <value>/tmp/hive-${user}</value>
+ </property>
+ </configuration>
+
+ <script>${hive_script}</script>
+ <param>projectview_source=${projectview_source}</param>
+ <param>pageview_source=${pageview_source}</param>
+
<param>destination_directory=${output_base_path}/desktop_and_mobile_web-${year}-${month}-${day}</param>
+ <param>access_methods='desktop','mobile web'</param>
+ <param>year=${year}</param>
+ <param>month=${month}</param>
+ <param>day=${day}</param>
+ <param>time_window=${time_window}</param>
+ </hive>
+
+ <ok to="end"/>
+ <error to="kill"/>
+ </action>
+
+ <kill name="kill">
+ <message>Action failed, error
message[${wf:errorMessage(wf:lastErrorNode())}]</message>
+ </kill>
+ <end name="end"/>
+</workflow-app>
--
To view, visit https://gerrit.wikimedia.org/r/246851
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I106a9853b07dac5a0efabaf92a9987de2b8a7ec2
Gerrit-PatchSet: 6
Gerrit-Project: analytics/refinery
Gerrit-Branch: master
Gerrit-Owner: Mforns <[email protected]>
Gerrit-Reviewer: Joal <[email protected]>
Gerrit-Reviewer: Mforns <[email protected]>
Gerrit-Reviewer: Nuria <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
