Joal has uploaded a new change for review.
https://gerrit.wikimedia.org/r/202914
Change subject: Add access_method, client_type and is_zero fields to refined
webrequest table.
......................................................................
Add access_method, client_type and is_zero fields to refined webrequest table.
Change-Id: I7b0ace0d29aeecfd6974da28160e8b34cd69aff0
---
M hive/webrequest/create_webrequest_table.hql
M oozie/webrequest/refine/bundle.properties
M oozie/webrequest/refine/refine_webrequest.hql
3 files changed, 15 insertions(+), 3 deletions(-)
git pull ssh://gerrit.wikimedia.org:29418/analytics/refinery
refs/changes/14/202914/1
diff --git a/hive/webrequest/create_webrequest_table.hql
b/hive/webrequest/create_webrequest_table.hql
index c167f6a..fde5947 100644
--- a/hive/webrequest/create_webrequest_table.hql
+++ b/hive/webrequest/create_webrequest_table.hql
@@ -45,7 +45,10 @@
-- However such schema modification implies backward incompatibility.
-- We will replace once we feel confident enough that 'every' backward
incompatible change is done.
`user_agent_map` map<string, string> COMMENT 'User-agent map with
browser_name, browser_major, device, os_name, os_minor, os_major keys and
associated values',
- `x_analytics_map` map<string, string> COMMENT 'X_analytics map view of
the x_analytics field'
+ `x_analytics_map` map<string, string> COMMENT 'X_analytics map view of
the x_analytics field',
+ `access_method` string COMMENT 'Method used to accessing the site
(mobile app|mobile web|desktop)',
+ `client_type` string COMMENT 'Categorise client making the
webrequest as either user or spider (automatas to be added).',
+ `is_zero` boolean COMMENT 'Indicates if the webrequest is
accessed through a zero provider'
)
PARTITIONED BY (
`webrequest_source` string COMMENT 'Source cluster',
diff --git a/oozie/webrequest/refine/bundle.properties
b/oozie/webrequest/refine/bundle.properties
index c5ecb3c..86716ab 100644
--- a/oozie/webrequest/refine/bundle.properties
+++ b/oozie/webrequest/refine/bundle.properties
@@ -58,7 +58,7 @@
destination_table = wmf.webrequest
# Record version to keep track of changes
-record_version = 0.0.2
+record_version = 0.0.3
# HDFS path to directory where webrequest data is time bucketed.
webrequest_raw_data_directory = ${name_node}/wmf/data/raw/webrequest
diff --git a/oozie/webrequest/refine/refine_webrequest.hql
b/oozie/webrequest/refine/refine_webrequest.hql
index ab9a76e..f93095b 100644
--- a/oozie/webrequest/refine/refine_webrequest.hql
+++ b/oozie/webrequest/refine/refine_webrequest.hql
@@ -51,6 +51,9 @@
CREATE TEMPORARY FUNCTION client_ip as
'org.wikimedia.analytics.refinery.hive.ClientIpUDF';
CREATE TEMPORARY FUNCTION geocoded_data as
'org.wikimedia.analytics.refinery.hive.GeocodedDataUDF';
CREATE TEMPORARY FUNCTION ua_parser as
'org.wikimedia.analytics.refinery.hive.UAParserUDF';
+CREATE TEMPORARY FUNCTION get_access_method as
'org.wikimedia.analytics.refinery.hive.GetAccessMethodUDF';
+CREATE TEMPORARY FUNCTION is_crawler as
'org.wikimedia.analytics.refinery.hive.IsCrawlerUDF';
+
INSERT OVERWRITE TABLE ${destination_table}
PARTITION(webrequest_source='${webrequest_source}',year=${year},month=${month},day=${day},hour=${hour})
@@ -83,7 +86,13 @@
CASE COALESCE(x_analytics, '-')
WHEN '-' THEN NULL
ELSE str_to_map(x_analytics, '\;', '=')
- END as x_analytics_map
+ END as x_analytics_map,
+ get_access_method(uri_host, user_agent) as access_method,
+ CASE
+ WHEN ((ua_parser(user_agent)['device'] = 'Spider') OR
(is_crawler(user_agent))) THEN 'Spider'
+ ELSE 'User'
+ END as client_type,
+ (str_to_map(x_analytics, '\;', '=')['zero'] IS NOT NULL) as is_zero
FROM
${source_table}
WHERE
--
To view, visit https://gerrit.wikimedia.org/r/202914
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: I7b0ace0d29aeecfd6974da28160e8b34cd69aff0
Gerrit-PatchSet: 1
Gerrit-Project: analytics/refinery
Gerrit-Branch: master
Gerrit-Owner: Joal <[email protected]>
_______________________________________________
MediaWiki-commits mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
