T152122 Hive queries to extract impression data for outages
ActivePublic
Actions

Authored by AndyRussG on Dec 7 2016, 6:00 PM.

Tags

Subscribers

None

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

Event Timeline

AndyRussG created this paste.Dec 7 2016, 6:00 PM

AndyRussG edited the content of this paste. (Show Details)

AndyRussG mentioned this in T152122: Central Notice: possible CN issue early on December 1st UTC.Dec 7 2016, 6:28 PM

T152122 Hive queries to extract impression data for outagesActivePublicActions

Event Timeline

T152122 Hive queries to extract impression data for outages
ActivePublic
Actions