Ottomata has uploaded a new change for review.
https://gerrit.wikimedia.org/r/232644
Change subject: [WIP] Generate hourly aggregate statistics about webrequest
sequence stats
......................................................................
[WIP] Generate hourly aggregate statistics about webrequest sequence stats
This is added as another action in the webrequest load job.
Change-Id: I4193a70c2ef7c62a13d39bbc01b7429ff454574b
---
A hive/webrequest/create_webrequest_sequence_stats_hourly_table.hql
A oozie/webrequest/load/generate_sequence_statistics_hourly.hql
M oozie/webrequest/load/workflow.xml
3 files changed, 127 insertions(+), 0 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/refinery
refs/changes/44/232644/1
diff --git a/hive/webrequest/create_webrequest_sequence_stats_hourly_table.hql
b/hive/webrequest/create_webrequest_sequence_stats_hourly_table.hql
new file mode 100644
index 0000000..e9af292
--- /dev/null
+++ b/hive/webrequest/create_webrequest_sequence_stats_hourly_table.hql
@@ -0,0 +1,25 @@
+-- Create table statement for hourly aggregate sequence number
+-- statistics for the raw webrequest table.
+--
+-- Parameters:
+-- <none>
+--
+-- Usage
+-- hive -f create_webrequest_sequence_stats_hourly_table.hql \
+-- --database wmf_raw
+--
+
+CREATE TABLE `webrequest_sequence_stats_hourly`(
+ `webrequest_source` string,
+ `year` int,
+ `month` int,
+ `day` int,
+ `hour` int,
+ `count_actual` bigint COMMENT 'Actual number of records for this
hour',
+ `count_expected` bigint COMMENT 'Expected number of records for
this hour',
+ `count_null_sequence` bigint COMMENT 'Number of records where sequence
is NULL for this hour',
+ `count_duplicate` bigint COMMENT 'Number of duplicate sequences in
this hour',
+ `count_lost` bigint COMMENT 'Number of (approximate) missing
records in this hour',
+ `percent_duplicate` double COMMENT 'Percent duplicate records in this
hour',
+ `percent_lost` double COMMENT 'Percent lost records in this hour'
+)
diff --git a/oozie/webrequest/load/generate_sequence_statistics_hourly.hql
b/oozie/webrequest/load/generate_sequence_statistics_hourly.hql
new file mode 100644
index 0000000..7e7387e
--- /dev/null
+++ b/oozie/webrequest/load/generate_sequence_statistics_hourly.hql
@@ -0,0 +1,65 @@
+-- Aggregates statsistics from the webrequest_sequence_stats table across all
+-- webrequest originating servers for an hourly partition.
+--
+-- Parameters:
+-- source_table -- Fully qualified table name to compute the
+-- statistics for. This is probably
+-- 'webrequest_sequence_stats'.
+-- destination_table -- Fully qualified table name to stopre the
+-- computed statistics in. This table should
+-- have schema described in [2].
+-- webrequest_source -- webrequest_source of partition to compute
+-- statistics for.
+-- year -- year of partition to compute statistics
+-- for.
+-- month -- month of partition to compute statistics
+-- for.
+-- day -- day of partition to compute statistics
+-- for.
+-- hour -- hour of partition to compute statistics
+-- for.
+--
+-- [1] hive/webrequest/create_webrequest_sequence_stats_hourly_table.hql
+--
+-- Usage:
+-- hive -f generate_sequence_stats_hourly.hql \
+-- -d source_table=wmf_raw.webrequest_sequence_stats \
+-- -d destination_table=wmf_raw.webrequest_sequence_stats_hourly \
+-- -d webrequest_source=text \
+-- -d year=2015 \
+-- -d month=8 \
+-- -d day=11 \
+-- -d hour=1
+--
+
+INSERT OVERWRITE TABLE ${destination_table}
+SELECT webrequest_source, year, month, day, hour,
+ count_actual,
+ count_expected,
+ count_null_sequence,
+ count_duplicate,
+ count_lost,
+ ROUND(((count_duplicate / count_expected) * 100.0), 8) AS
percent_duplicate,
+ ROUND(((count_lost / count_expected) * 100.0), 8) AS percent_lost
+FROM (
+SELECT
+ webrequest_source,year, month, day, hour,
+ SUM(count_actual) AS count_actual,
+ SUM(count_expected) AS count_expected,
+ SUM(count_null_sequence) AS
count_null_sequence,
+ SUM(count_duplicate) AS count_duplicate,
+ SUM(count_different) + SUM(count_duplicate) AS count_lost
+FROM
+ wmf_raw.webrequest_sequence_stats
+WHERE
+ webrequest_source='${webrequest_source}' AND
+ year=${year} AND month=${month} AND day=${day} AND hour=${hour}
+ -- sequence_min == 0 means varnishkafka restarted.
+ -- Even though it skews results, don't include hosts
+ -- with reset seqeuence numbers in these results, as
+ -- they are a common cause of false postives in percent_loss and
+ -- percent_duplicate.
+ AND sequence_min <> 0
+ GROUP BY
+ webrequest_source, year, month, day, hour
+) a;
diff --git a/oozie/webrequest/load/workflow.xml
b/oozie/webrequest/load/workflow.xml
index f12e7ae..309025a 100644
--- a/oozie/webrequest/load/workflow.xml
+++ b/oozie/webrequest/load/workflow.xml
@@ -145,6 +145,43 @@
<param>hour=${hour}</param>
<param>webrequest_source=${webrequest_source}</param>
</hive>
+ <ok to="generate_sequence_statistics_hourly"/>
+ <error to="kill"/>
+ </action>
+
+
+ <!-- TODO: Use the result of the hourly query to mark a dataset as
_SUCCEEDED -->
+ <action name="generate_sequence_statistics_hourly">
+ <hive xmlns="uri:oozie:hive-action:0.3">
+ <job-tracker>${job_tracker}</job-tracker>
+ <name-node>${name_node}</name-node>
+ <job-xml>${hive_site_xml}</job-xml>
+ <configuration>
+ <!--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>mapreduce.job.queuename</name>
+ <value>${queue_name}</value>
+ </property>
+ </configuration>
+
+ <script>generate_sequence_statistics_hourly.hql</script>
+
+ <param>source_table=${statistics_table}</param>
+ <param>destination_table=${statistics_table}_hourly</param>
+ <param>year=${year}</param>
+ <param>month=${month}</param>
+ <param>day=${day}</param>
+ <param>hour=${hour}</param>
+ <param>webrequest_source=${webrequest_source}</param>
+ </hive>
<ok to="check_sequence_statistics"/>
<error to="kill"/>
</action>
--
To view, visit https://gerrit.wikimedia.org/r/232644
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I4193a70c2ef7c62a13d39bbc01b7429ff454574b
Gerrit-PatchSet: 1
Gerrit-Project: analytics/refinery
Gerrit-Branch: master
Gerrit-Owner: Ottomata <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
