Page MenuHomePhabricator
Paste P4584

T152122 Hive queries to extract impression data for outages
ActivePublic
Actions

Authored by AndyRussG on Dec 7 2016, 6:00 PM.
use andyrussg;
CREATE TABLE
t152122beaconimpression20161201
AS SELECT
*,
SUBSTR(ts,1,16) as time,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'campaign') as campaign,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'banner') as banner,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'uselang') as uselang,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'debug') as debug,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'db') as db,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device') as device,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'statusCode') as statusCode,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'country') as country,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'anonymous') as anonymous
FROM
wmf.webrequest
WHERE
year=2016
AND month=12
AND day=1
AND agent_type = 'user'
AND uri_path LIKE '%beacon%impression%';
CREATE TABLE
t152122beaconimpression20161202
AS SELECT
*,
SUBSTR(ts,1,16) as time,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'campaign') as campaign,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'banner') as banner,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'uselang') as uselang,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'debug') as debug,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'db') as db,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'device') as device,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'statusCode') as statusCode,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'country') as country,
parse_url(concat('http://bla.org/woo/', uri_query), 'QUERY', 'anonymous') as anonymous
FROM
wmf.webrequest
WHERE
year=2016
AND month=12
AND day=2
AND agent_type = 'user'
AND uri_path LIKE '%beacon%impression%';
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL