Page MenuHomePhabricator
Paste P2408

Hide results and browser family from mobile FR campaign 2015-12-09 21:00:00-21:59:59 UTC
ActivePublic

Authored by AndyRussG on Dec 11 2015, 2:39 AM.
ive (wmf)> select
> count(*) as num,
> user_agent_map,
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'result') as result,
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device') as device
> from webrequest
> where
> year = 2015 and month = 12 and day = 09 and hour = 21
> and ( parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'country') in ('AU', 'CA', 'IE', 'NZ', 'UK', 'US') )
> and ( parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device') in ('iphone', 'ipad', 'android') )
> and webrequest_source = 'mobile'
> and uri_path = '/beacon/impression'
> group by
> user_agent_map['browser_family'],
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'result'),
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device');
FAILED: SemanticException [Error 10025]: Line 3:5 Expression not in GROUP BY key 'user_agent_map'
hive (wmf)> select
> count(*) as num,
> user_agent_map['browser_family'] as browser_family,
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'result') as result,
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device') as device
> from webrequest
> where
> year = 2015 and month = 12 and day = 09 and hour = 21
> and ( parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'country') in ('AU', 'CA', 'IE', 'NZ', 'UK', 'US') )
> and ( parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device') in ('iphone', 'ipad', 'android') )
> and webrequest_source = 'mobile'
> and uri_path = '/beacon/impression'
> group by
> user_agent_map['browser_family'],
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'result'),
> parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device');
Query ID = andyrussg_20151211023636_c84fe418-2594-42f5-8ae2-4a39bc60b824
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 45
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapreduce.job.reduces=<number>
Starting Job = job_1441303822549_266121, Tracking URL = http://analytics1001.eqiad.wmnet:8088/proxy/application_1441303822549_266121/
Kill Command = /usr/lib/hadoop/bin/hadoop job -kill job_1441303822549_266121
Hadoop job information for Stage-1: number of mappers: 35; number of reducers: 45
2015-12-11 02:36:24,806 Stage-1 map = 0%, reduce = 0%
2015-12-11 02:36:41,396 Stage-1 map = 4%, reduce = 0%, Cumulative CPU 687.74 sec
2015-12-11 02:36:42,430 Stage-1 map = 5%, reduce = 0%, Cumulative CPU 789.59 sec
2015-12-11 02:36:43,465 Stage-1 map = 6%, reduce = 0%, Cumulative CPU 832.36 sec
2015-12-11 02:36:44,499 Stage-1 map = 14%, reduce = 0%, Cumulative CPU 932.82 sec
2015-12-11 02:36:45,545 Stage-1 map = 22%, reduce = 0%, Cumulative CPU 1052.96 sec
2015-12-11 02:36:46,577 Stage-1 map = 25%, reduce = 0%, Cumulative CPU 1064.2 sec
2015-12-11 02:36:47,611 Stage-1 map = 30%, reduce = 0%, Cumulative CPU 1099.34 sec
2015-12-11 02:36:48,640 Stage-1 map = 40%, reduce = 0%, Cumulative CPU 1191.11 sec
2015-12-11 02:36:49,682 Stage-1 map = 41%, reduce = 0%, Cumulative CPU 1204.7 sec
2015-12-11 02:36:51,746 Stage-1 map = 45%, reduce = 0%, Cumulative CPU 1357.3 sec
2015-12-11 02:36:52,777 Stage-1 map = 52%, reduce = 0%, Cumulative CPU 1374.18 sec
2015-12-11 02:36:53,808 Stage-1 map = 56%, reduce = 0%, Cumulative CPU 1402.62 sec
2015-12-11 02:36:54,843 Stage-1 map = 60%, reduce = 0%, Cumulative CPU 1482.29 sec
2015-12-11 02:36:55,874 Stage-1 map = 66%, reduce = 4%, Cumulative CPU 1500.76 sec
2015-12-11 02:36:56,908 Stage-1 map = 70%, reduce = 10%, Cumulative CPU 1527.59 sec
2015-12-11 02:36:57,942 Stage-1 map = 71%, reduce = 14%, Cumulative CPU 1582.97 sec
2015-12-11 02:36:58,972 Stage-1 map = 71%, reduce = 17%, Cumulative CPU 1590.17 sec
2015-12-11 02:37:00,002 Stage-1 map = 71%, reduce = 18%, Cumulative CPU 1616.22 sec
2015-12-11 02:37:01,040 Stage-1 map = 77%, reduce = 19%, Cumulative CPU 1654.62 sec
2015-12-11 02:37:02,071 Stage-1 map = 79%, reduce = 20%, Cumulative CPU 1662.64 sec
2015-12-11 02:37:03,099 Stage-1 map = 87%, reduce = 22%, Cumulative CPU 1678.91 sec
2015-12-11 02:37:04,138 Stage-1 map = 89%, reduce = 23%, Cumulative CPU 1702.72 sec
2015-12-11 02:37:05,172 Stage-1 map = 90%, reduce = 26%, Cumulative CPU 1726.42 sec
2015-12-11 02:37:06,203 Stage-1 map = 94%, reduce = 28%, Cumulative CPU 1732.55 sec
2015-12-11 02:37:07,234 Stage-1 map = 98%, reduce = 29%, Cumulative CPU 1743.72 sec
2015-12-11 02:37:08,266 Stage-1 map = 100%, reduce = 45%, Cumulative CPU 1750.67 sec
2015-12-11 02:37:09,296 Stage-1 map = 100%, reduce = 95%, Cumulative CPU 1821.09 sec
2015-12-11 02:37:10,323 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 1829.36 sec
MapReduce Total cumulative CPU time: 30 minutes 29 seconds 360 msec
Ended Job = job_1441303822549_266121
MapReduce Jobs Launched:
Stage-Stage-1: Map: 35 Reduce: 45 Cumulative CPU: 1829.36 sec HDFS Read: 2683635479 HDFS Write: 1777 SUCCESS
Total MapReduce CPU Time Spent: 30 minutes 29 seconds 360 msec
OK
num browser_family result device
32 Puffin hide android
30 QQ Browser Mobile show android
11569 Android hide android
36542 Mobile Safari hide ipad
20 Safari hide android
34 Firefox hide android
8 Yandex Browser show android
274 Firefox show android
5395 Firefox Mobile show android
9003 Chrome hide android
6 Puffin show iphone
7 Opera Coast hide iphone
1 QQ Browser Mobile hide android
15 Opera Coast show iphone
67 Opera Mini hide iphone
115 Opera Mini show iphone
3 Yandex Browser hide android
1581 Firefox Mobile hide android
10 UC Browser hide iphone
28912 Chrome Mobile iOS show iphone
1 Puffin hide iphone
51 UC Browser show iphone
22 Other show android
693 Opera Mobile show android
16 Puffin show ipad
3 Chrome show iphone
8623 Chrome Mobile iOS hide iphone
1 Nokia Browser show android
2 QQ Browser Mobile hide iphone
4 Opera Coast show ipad
21 QQ Browser Mobile show iphone
77 Opera show android
1 Sleipnir hide iphone
43 Opera Mini show ipad
24 Other hide android
10144 Amazon Silk show android
8448 Chrome Mobile iOS show ipad
517 Opera Mobile hide android
3 Puffin hide ipad
3 UC Browser show ipad
299834 Chrome Mobile show android
8 Opera hide android
21 Opera Mini hide ipad
44 NetFront NX show iphone
5560 Amazon Silk hide android
2523 Chrome Mobile iOS hide ipad
156093 Mobile Safari hide iphone
141352 Chrome Mobile hide android
629636 Mobile Safari show iphone
1 QQ Browser Mobile show ipad
2 Spider show android
1 Sleipnir hide ipad
7574 IE Mobile show iphone
11 NetFront NX hide iphone
1005 UC Browser show android
2548 IE Mobile hide iphone
1 Kindle hide android
26 Puffin show android
36530 Android show android
138756 Mobile Safari show ipad
3 Safari show android
2 Chromium show android
24676 Chrome show android
4 Pale Moon (Firefox Variant) show android
484 UC Browser hide android
Time taken: 54.184 seconds, Fetched: 65 row(s)

Event Timeline

AndyRussG changed the title of this paste from untitled to Hide results and browser family from mobile FR campaign 2015-12-09 21:00:00-21:59:59 UTC.Dec 11 2015, 2:39 AM
AndyRussG edited the content of this paste. (Show Details)