T152122 Hive queries to extract impression data for outages
No One
Actions

Authored By

	AndyRussG
	Dec 7 2016, 6:00 PM

Size

1 KB

Referenced Files

None

Subscribers

None

T152122 Hive queries to extract impression data for outages
View Options

	use andyrussg;

	CREATE TABLE
	t152122bannerloader20161201
	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
	FROM
	wmf.webrequest
	WHERE
	year=2016
	AND month=12
	AND day=1
	AND agent_type = 'user'
	AND uri_query LIKE "%BannerLoader%";

	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%';

Mime Type: text/plain; charset=utf-8
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 4191670
Default Alt Text: T152122 Hive queries to extract impression data for outages (1 KB)