def ungroup(db_name, table_name, priority = True):
query = """
CREATE TABLE %(db_name)s.%(table_name)s
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE AS
SELECT
ip,
ua,
geocoded_data,
user_agent_map,
CONCAT_WS('REQUEST_DELIM', COLLECT_LIST(requests)) AS requests
FROM
%(db_name)s.%(table_name)s_by_day
WHERE
year = 2016
GROUP BY
ip,
ua,
geocoded_data,
user_agent_map
"""
params = {'db_name': db_name, 'table_name': table_name}
exec_hive_stat2(query % params, priority = priority)
def make_flat_will_hive_table(day,
db,
path='/user/ellery/wikidata_dumps',
table='wikidata_will'):
flat_will_path = os.path.join(path, day + '_flat_will')
query = """
CREATE DATABASE IF NOT EXISTS %(db)s;
DROP TABLE If EXISTS %(db)s.%(table)s;
CREATE EXTERNAL TABLE %(db)s.%(table)s (
id STRING,
lang STRING,
title STRING
) ROW FORMAT
DELIMITED FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '%(path)s';
"""
params = {'db': db, 'table': table, 'path': flat_will_path}
print(query % params)
exec_hive_stat2(query % params)
def create_hive_trace_table(db_name, table_name, priority = True):
"""
Create a Table partitioned by day and host
"""
query = """
CREATE TABLE IF NOT EXISTS %(db_name)s.%(table_name)s_by_day (
ip STRING,
ua STRING,
geocoded_data MAP<STRING,STRING>,
user_agent_map MAP<STRING,STRING>,
requests STRING
)
PARTITIONED BY (year INT, month INT, day INT, host STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
"""
params = {'db_name': db_name, 'table_name': table_name}
exec_hive_stat2(query % params, priority = priority)
def get_clickstream(table, lang, start, stop, priority = False, min_count = 10):
params = { 'time_conditions': get_hive_timespan(start, stop, hour = False),
'table': table,
'lang': lang,
'min_count': min_count,
}
query = """
-- ############################################
-- create helper tables
-- create copy of page table and insert rows for our special prev pages
-- this will let us work with ids instead of titles later, which is much less error prone
DROP TABLE IF EXISTS clickstream.%(table)s_page_helper;
CREATE TABLE clickstream.%(table)s_page_helper AS
SELECT
*
FROM
clickstream.%(lang)s_page
;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-1 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-empty' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-2 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-internal' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-3 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-external' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-4 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-search' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-5 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-other' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
-- create pagelinks table that resolves links that end in a redirect
-- this means that if A links to B, and B redirects to C, we replace the link (A,B) with (A,C)
-- this lets us properly annotate link types after resolving redirects in the clickstream, since
-- a user will experience following A as if it linked to C
-- the group be ensures that each link only occurs once
DROP TABLE IF EXISTS clickstream.%(table)s_pagelinks_helper;
CREATE TABLE clickstream.%(table)s_pagelinks_helper AS
SELECT
pl_from_page_id,
pl_to_page_id
FROM
(SELECT
pl_from_page_id,
CASE
WHEN r.rd_to_page_id IS NULL THEN pl_to_page_id
ELSE rd_to_page_id
END AS pl_to_page_id
FROM
clickstream.%(lang)s_pagelinks l
LEFT JOIN
clickstream.%(lang)s_redirect r ON (r.rd_from_page_id = l.pl_to_page_id)
) a
GROUP BY
pl_from_page_id,
pl_to_page_id
;
-- ############################################
-- extract raw prev, curr pairs
DROP VIEW IF EXISTS clickstream.%(table)s_temp1;
CREATE VIEW clickstream.%(table)s_temp1 AS
SELECT
CASE
-- empty or malformed referer
WHEN referer IS NULL THEN 'other-empty'
WHEN referer == '' THEN 'other-empty'
WHEN referer == '-' THEN 'other-empty'
WHEN parse_url(referer,'HOST') is NULL THEN 'other-empty'
-- internal referer from the same wikipedia
WHEN
parse_url(referer,'HOST') in ('%(lang)s.wikipedia.org', '%(lang)s.m.wikipedia.org')
AND LENGTH(REGEXP_EXTRACT(parse_url(referer,'PATH'), '/wiki/(.*)', 1)) > 1
THEN REGEXP_EXTRACT(parse_url(referer,'PATH'), '/wiki/(.*)', 1)
-- other referers
WHEN referer_class = 'internal' THEN 'other-internal'
WHEN referer_class = 'external' THEN 'other-external'
WHEN referer_class = 'external (search engine)' THEN 'other-search'
ELSE 'other-other'
END as prev,
pageview_info['page_title'] as curr
FROM
wmf.webrequest
WHERE
%(time_conditions)s
AND webrequest_source = 'text'
AND normalized_host.project_class = 'wikipedia'
AND normalized_host.project = '%(lang)s'
AND is_pageview
AND agent_type = 'user'
;
-- count raw prev, curr pairs, this speeds up later queries
DROP TABLE IF EXISTS clickstream.%(table)s_temp2;
CREATE TABLE clickstream.%(table)s_temp2 AS
SELECT
prev, curr, COUNT(*) as n
FROM
clickstream.%(table)s_temp1
GROUP BY
prev, curr
;
-- we enforce that curr and prev are main namespace pages
-- the joins accomplish this because, in the logs, the non main namespace pages have the namespace prepended
-- at this point curr and prev are ids
DROP TABLE IF EXISTS clickstream.%(table)s_temp3;
CREATE TABLE clickstream.%(table)s_temp3 AS
SELECT
pp.page_id as prev,
pc.page_id as curr,
n
FROM
clickstream.%(table)s_temp2
JOIN
clickstream.%(table)s_page_helper pp ON (prev = pp.page_title)
JOIN
clickstream.%(table)s_page_helper pc ON (curr = pc.page_title)
WHERE
pp.page_namespace = 0
AND pc.page_namespace = 0
;
-- resolve curr redirects, one step
-- note that prev should not be a redirect, so we do not bother resolving it
-- and prev redirects will be filtered out at the end
DROP TABLE IF EXISTS clickstream.%(table)s_temp4;
CREATE TABLE clickstream.%(table)s_temp4 AS
SELECT
prev,
CASE
WHEN rd_to_page_id IS NULL THEN curr
ELSE rd_to_page_id
END AS curr,
n
FROM
clickstream.%(table)s_temp3
LEFT JOIN
clickstream.%(lang)s_redirect ON (curr = rd_from_page_id)
;
-- re-aggregate after resolving redirects and filter out pairs that occur infrequently
DROP TABLE IF EXISTS clickstream.%(table)s_temp5;
CREATE TABLE clickstream.%(table)s_temp5 AS
SELECT
prev, curr, SUM(n) as n
FROM
clickstream.%(table)s_temp4
GROUP BY
prev, curr
HAVING
SUM(n) > %(min_count)s
;
-- annotate link types
DROP TABLE IF EXISTS clickstream.%(table)s_temp6;
CREATE TABLE clickstream.%(table)s_temp6 AS
SELECT
prev,
curr,
CASE
WHEN prev < 0 THEN 'external'
WHEN (pl_from_page_id IS NOT NULL AND pl_to_page_id IS NOT NULL) THEN 'link'
ELSE 'other'
END AS type,
n
FROM
clickstream.%(table)s_temp5
LEFT JOIN
clickstream.%(table)s_pagelinks_helper ON (prev = pl_from_page_id AND curr = pl_to_page_id)
;
-- create final table
-- remove self loops
-- restrict prev and curr to main namespace, no redirects
-- get page titles
DROP TABLE IF EXISTS clickstream.%(table)s;
CREATE TABLE clickstream.%(table)s
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE AS
SELECT
pp.page_title as prev,
pc.page_title as curr,
a.type,
a.n
FROM
clickstream.%(table)s_temp6 a
JOIN
clickstream.%(table)s_page_helper pp ON (prev = pp.page_id)
JOIN
clickstream.%(table)s_page_helper pc ON (curr = pc.page_id)
WHERE
pp.page_is_redirect = false
AND pp.page_namespace = 0
AND pc.page_is_redirect = false
AND pc.page_namespace = 0
AND a.curr != a.prev
;
DROP VIEW clickstream.%(table)s_temp1;
DROP TABLE clickstream.%(table)s_temp2;
DROP TABLE clickstream.%(table)s_temp3;
DROP TABLE clickstream.%(table)s_temp4;
DROP TABLE clickstream.%(table)s_temp5;
DROP TABLE clickstream.%(table)s_temp6;
DROP TABLE clickstream.%(table)s_page_helper;
DROP TABLE clickstream.%(table)s_pagelinks_helper;
"""
exec_hive_stat2(query % params, priority = priority)
def get_requests(start, stop, table, trace_db = 'a2v', prod_db = 'prod', priority = True, min_count=50):
query = """
SET mapreduce.input.fileinputformat.split.maxsize=200000000;
SET hive.mapred.mode=nonstrict;
-- get pageviews, resolve redirects, add wikidata ids
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_pageviews;
CREATE TABLE %(trace_db)s.%(trace_table)s_pageviews AS
SELECT
year,month,day,
client_ip,
user_agent,
x_forwarded_for,
ts,
pv2.lang,
pv2.title,
id
FROM
(SELECT
year,month,day,
client_ip,
user_agent,
x_forwarded_for,
ts,
pv1.lang,
CASE
WHEN rd_to_page_title IS NULL THEN raw_title
ELSE rd_to_page_title
END AS title
FROM
(SELECT
year,month,day,
client_ip,
user_agent,
x_forwarded_for,
ts,
normalized_host.project AS lang,
REGEXP_EXTRACT(reflect('java.net.URLDecoder', 'decode', uri_path), '/wiki/(.*)', 1) as raw_title
FROM
wmf.webrequest
WHERE
is_pageview
AND webrequest_source = 'text'
AND normalized_host.project_class = 'wikipedia'
AND agent_type = 'user'
AND %(time_conditions)s
AND LENGTH(REGEXP_EXTRACT(reflect('java.net.URLDecoder', 'decode', uri_path), '/wiki/(.*)', 1)) > 0
) pv1
LEFT JOIN
(SELECT
*
FROM
prod.redirect
WHERE
rd_from_page_namespace = 0
AND rd_to_page_namespace = 0
AND lang RLIKE '.*'
) r
ON
pv1.raw_title = r.rd_from_page_title AND pv1.lang = r.lang
) pv2
INNER JOIN
%(prod_db)s.wikidata_will w ON pv2.title = w.title AND pv2.lang = w.lang;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_editors;
CREATE TABLE %(trace_db)s.%(trace_table)s_editors AS
SELECT
client_ip,
user_agent,
x_forwarded_for
FROM
wmf.webrequest
WHERE
uri_query RLIKE 'action=edit'
AND %(time_conditions)s
GROUP BY
client_ip,
user_agent,
x_forwarded_for;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_reader_pageviews;
CREATE TABLE %(trace_db)s.%(trace_table)s_reader_pageviews AS
SELECT
p.*
FROM
%(trace_db)s.%(trace_table)s_pageviews p
LEFT JOIN
%(trace_db)s.%(trace_table)s_editors e
ON (
p.client_ip = e.client_ip
AND p.user_agent = e.user_agent
AND p.x_forwarded_for = e.x_forwarded_for
)
WHERE
e.client_ip is NULL
AND e.user_agent is NULL
AND e.x_forwarded_for is NULL;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_clients_per_item;
CREATE TABLE %(trace_db)s.%(trace_table)s_clients_per_item AS
SELECT
id,
COUNT(*) as n
FROM
(SELECT
client_ip,
user_agent,
x_forwarded_for,
id
FROM
%(trace_db)s.%(trace_table)s_reader_pageviews
GROUP BY
client_ip,
user_agent,
x_forwarded_for,
id
) a
GROUP BY
id;
-- remove disambiguation pages and pages with colon in title
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_eligible_reader_pageviews;
CREATE TABLE %(trace_db)s.%(trace_table)s_eligible_reader_pageviews AS
SELECT pv.*
FROM
(SELECT
p.*
FROM
%(trace_db)s.%(trace_table)s_reader_pageviews p
JOIN
%(trace_db)s.%(trace_table)s_clients_per_item c
ON (p.id = c.id)
WHERE
c.n >= %(min_count)s
) pv
LEFT JOIN
(SELECT
lang,
page_title
FROM
%(prod_db)s.page_props
WHERE
propname = 'disambiguation'
AND lang RLIKE '.*'
AND page_namespace = 0
GROUP BY
lang,
page_title
) d
ON
(pv.lang = d.lang and pv.title = d.page_title)
WHERE
d.page_title IS NULL
AND pv.title NOT RLIKE 'disambig'
AND pv.title NOT RLIKE ':';
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_requests;
CREATE TABLE %(trace_db)s.%(trace_table)s_requests AS
SELECT
CONCAT_WS('||', COLLECT_LIST(request)) AS requests
FROM
(SELECT
client_ip,
user_agent,
x_forwarded_for,
CONCAT('ts|', ts, '|id|', id, '|title|', title, '|lang|', lang ) AS request
FROM %(trace_db)s.%(trace_table)s_eligible_reader_pageviews
) a
GROUP BY
client_ip,
user_agent,
x_forwarded_for
HAVING
COUNT(*) <= 1000
AND COUNT(*) > 1;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_eligible_reader_pageviews;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_clients_per_item;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_reader_pageviews;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_editors;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_pageviews;
"""
params = { 'time_conditions': get_hive_timespan(start, stop, hour = False),
'trace_db': trace_db,
'prod_db': prod_db,
'trace_table': table,
'min_count': min_count
}
exec_hive_stat2(query % params, priority = priority)
def add_day_to_hive_trace_table(db_name, table_name, day, priority = True):
query = """
INSERT OVERWRITE TABLE %(db_name)s.%(table_name)s_by_day
PARTITION(year=%(year)d, month=%(month)d, day =%(day)d, host)
SELECT
client_ip,
user_agent,
geocoded_data,
user_agent_map,
CONCAT_WS('REQUEST_DELIM', COLLECT_LIST(request)) AS requests,
uri_host AS host
FROM
(SELECT
client_ip,
user_agent,
geocoded_data,
user_agent_map,
CONCAT( 'ts|', ts,
'|referer|', referer,
'|title|', title,
'|uri_path|', reflect('java.net.URLDecoder', 'decode', uri_path),
'|uri_query|', reflect('java.net.URLDecoder', 'decode', uri_query),
'|is_pageview|', is_pageview,
'|access_method|', access_method,
'|referer_class|', referer_class,
'|project|', normalized_host.project_class,
'|lang|', normalized_host.project
) AS request,
uri_host
FROM
(SELECT
c.*,
CASE
WHEN NOT is_pageview THEN NULL
WHEN rd_to IS NULL THEN raw_title
ELSE rd_to
END AS title
FROM
(SELECT
w.*,
CASE
WHEN is_pageview THEN pageview_info['page_title']
ELSE round(RAND(), 5)
END AS raw_title
FROM
wmf.webrequest w
WHERE
webrequest_source = 'text'
AND agent_type = 'user'
AND %(time_conditions)s
AND hour = 1
AND access_method != 'mobile app'
AND uri_host in ('en.wikipedia.org', 'en.m.wikipedia.org')
) c
LEFT JOIN
traces.en_redirect r
ON c.raw_title = r.rd_from
) b
) a
GROUP BY
client_ip,
user_agent,
geocoded_data,
user_agent_map,
uri_host
HAVING
COUNT(*) < 500;
"""
day_dt = dateutil.parser.parse(day)
params = { 'time_conditions': get_hive_timespan(day, day, hour = False),
'db_name': db_name,
'table_name': table_name,
'year' : day_dt.year,
'month': day_dt.month,
'day': day_dt.day
}
exec_hive_stat2(query % params, priority = priority)
def get_requests(start,
stop,
table,
trace_db='a2v',
prod_db='prod',
priority=True,
min_count=50):
query = """
SET mapreduce.input.fileinputformat.split.maxsize=200000000;
SET hive.mapred.mode=nonstrict;
-- get pageviews, resolve redirects, add wikidata ids
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_pageviews;
CREATE TABLE %(trace_db)s.%(trace_table)s_pageviews AS
SELECT
year,month,day,
client_ip,
user_agent,
x_forwarded_for,
ts,
pv2.lang,
pv2.title,
id
FROM
(SELECT
year,month,day,
client_ip,
user_agent,
x_forwarded_for,
ts,
pv1.lang,
CASE
WHEN rd_to_page_title IS NULL THEN raw_title
ELSE rd_to_page_title
END AS title
FROM
(SELECT
year,month,day,
client_ip,
user_agent,
x_forwarded_for,
ts,
normalized_host.project AS lang,
REGEXP_EXTRACT(reflect('java.net.URLDecoder', 'decode', uri_path), '/wiki/(.*)', 1) as raw_title
FROM
wmf.webrequest
WHERE
is_pageview
AND webrequest_source = 'text'
AND normalized_host.project_class = 'wikipedia'
AND agent_type = 'user'
AND %(time_conditions)s
AND LENGTH(REGEXP_EXTRACT(reflect('java.net.URLDecoder', 'decode', uri_path), '/wiki/(.*)', 1)) > 0
) pv1
LEFT JOIN
(SELECT
*
FROM
prod.redirect
WHERE
rd_from_page_namespace = 0
AND rd_to_page_namespace = 0
AND lang RLIKE '.*'
) r
ON
pv1.raw_title = r.rd_from_page_title AND pv1.lang = r.lang
) pv2
INNER JOIN
%(prod_db)s.wikidata_will w ON pv2.title = w.title AND pv2.lang = w.lang;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_editors;
CREATE TABLE %(trace_db)s.%(trace_table)s_editors AS
SELECT
client_ip,
user_agent,
x_forwarded_for
FROM
wmf.webrequest
WHERE
uri_query RLIKE 'action=edit'
AND %(time_conditions)s
GROUP BY
client_ip,
user_agent,
x_forwarded_for;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_reader_pageviews;
CREATE TABLE %(trace_db)s.%(trace_table)s_reader_pageviews AS
SELECT
p.*
FROM
%(trace_db)s.%(trace_table)s_pageviews p
LEFT JOIN
%(trace_db)s.%(trace_table)s_editors e
ON (
p.client_ip = e.client_ip
AND p.user_agent = e.user_agent
AND p.x_forwarded_for = e.x_forwarded_for
)
WHERE
e.client_ip is NULL
AND e.user_agent is NULL
AND e.x_forwarded_for is NULL;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_clients_per_item;
CREATE TABLE %(trace_db)s.%(trace_table)s_clients_per_item AS
SELECT
id,
COUNT(*) as n
FROM
(SELECT
client_ip,
user_agent,
x_forwarded_for,
id
FROM
%(trace_db)s.%(trace_table)s_reader_pageviews
GROUP BY
client_ip,
user_agent,
x_forwarded_for,
id
) a
GROUP BY
id;
-- remove disambiguation pages and pages with colon in title
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_eligible_reader_pageviews;
CREATE TABLE %(trace_db)s.%(trace_table)s_eligible_reader_pageviews AS
SELECT pv.*
FROM
(SELECT
p.*
FROM
%(trace_db)s.%(trace_table)s_reader_pageviews p
JOIN
%(trace_db)s.%(trace_table)s_clients_per_item c
ON (p.id = c.id)
WHERE
c.n >= %(min_count)s
) pv
LEFT JOIN
(SELECT
lang,
page_title
FROM
%(prod_db)s.page_props
WHERE
propname = 'disambiguation'
AND lang RLIKE '.*'
AND page_namespace = 0
GROUP BY
lang,
page_title
) d
ON
(pv.lang = d.lang and pv.title = d.page_title)
WHERE
d.page_title IS NULL
AND pv.title NOT RLIKE 'disambig'
AND pv.title NOT RLIKE ':';
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_requests;
CREATE TABLE %(trace_db)s.%(trace_table)s_requests AS
SELECT
CONCAT_WS('||', COLLECT_LIST(request)) AS requests
FROM
(SELECT
client_ip,
user_agent,
x_forwarded_for,
CONCAT('ts|', ts, '|id|', id, '|title|', title, '|lang|', lang ) AS request
FROM %(trace_db)s.%(trace_table)s_eligible_reader_pageviews
) a
GROUP BY
client_ip,
user_agent,
x_forwarded_for
HAVING
COUNT(*) <= 1000
AND COUNT(*) > 1;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_eligible_reader_pageviews;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_clients_per_item;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_reader_pageviews;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_editors;
DROP TABLE IF EXISTS %(trace_db)s.%(trace_table)s_pageviews;
"""
params = {
'time_conditions': get_hive_timespan(start, stop, hour=False),
'trace_db': trace_db,
'prod_db': prod_db,
'trace_table': table,
'min_count': min_count
}
exec_hive_stat2(query % params, priority=priority)
def get_clickstream(table, lang, start, stop, priority = False, min_count = 10):
params = { 'time_conditions': get_hive_timespan(start, stop, hour = False),
'table': table,
'lang': lang,
'min_count': min_count,
}
query = """
-- extract raw prev, curr pairs
DROP VIEW IF EXISTS west1.clickstream_%(table)s_temp1;
CREATE VIEW west1.clickstream_%(table)s_temp1 AS
SELECT
CASE
-- empty or malformed referer
WHEN referer IS NULL THEN 'other-empty'
WHEN referer == '' THEN 'other-empty'
WHEN referer == '-' THEN 'other-empty'
WHEN parse_url(referer,'HOST') is NULL THEN 'other-empty'
-- internal referer from the same wikipedia
WHEN
parse_url(referer,'HOST') in ('%(lang)s.wikipedia.org', '%(lang)s.m.wikipedia.org')
AND LENGTH(REGEXP_EXTRACT(parse_url(referer,'PATH'), '/wiki/(.*)', 1)) > 1
THEN REGEXP_EXTRACT(parse_url(referer,'PATH'), '/wiki/(.*)', 1)
-- other referers
WHEN referer_class = 'internal' THEN 'other-internal'
WHEN referer_class = 'external' THEN 'other-external'
WHEN referer_class = 'external (search engine)' THEN 'other-search'
ELSE 'other-other'
END as prev,
pageview_info['page_title'] as curr
FROM
wmf.webrequest
WHERE
%(time_conditions)s
AND webrequest_source = 'text'
AND normalized_host.project_class = 'wikipedia'
AND normalized_host.project = '%(lang)s'
AND is_pageview
AND agent_type = 'user';
-- count raw prev, curr pairs
DROP VIEW IF EXISTS west1.clickstream_%(table)s_temp2;
CREATE VIEW west1.clickstream_%(table)s_temp2 AS
SELECT
curr, prev, COUNT(*) as n
FROM
west1.clickstream_%(table)s_temp1
GROUP BY
curr, prev;
-- resolve redirects
DROP VIEW IF EXISTS west1.clickstream_%(table)s_temp3;
CREATE VIEW west1.clickstream_%(table)s_temp3 AS
SELECT
CASE
WHEN prev in ('other-empty', 'other-internal', 'other-external', 'other-search', 'other-other') THEN prev
WHEN pr.rd_to IS NULL THEN prev
ELSE pr.rd_to
END AS prev,
CASE
WHEN cr.rd_to IS NULL THEN curr
ELSE cr.rd_to
END AS curr,
curr AS curr_unresolved,
n
FROM
west1.clickstream_%(table)s_temp2
LEFT JOIN
west1.%(lang)s_redirect pr ON (prev = pr.rd_from)
LEFT JOIN
west1.%(lang)s_redirect cr ON (curr = cr.rd_from);
-- re-aggregate after resolving redirects and filter out pairs that occur infrequently
DROP VIEW IF EXISTS west1.clickstream_%(table)s_temp4;
CREATE VIEW west1.clickstream_%(table)s_temp4 AS
SELECT
curr, curr_unresolved, prev, SUM(n) as n
FROM
west1.clickstream_%(table)s_temp3
GROUP BY
curr, curr_unresolved, prev
HAVING
SUM(n) > %(min_count)s;
-- only include main namespace articles
DROP VIEW IF EXISTS west1.clickstream_%(table)s_temp5;
CREATE VIEW west1.clickstream_%(table)s_temp5 AS
SELECT
curr, curr_unresolved, prev, n
FROM
west1.clickstream_%(table)s_temp4
LEFT JOIN
west1.%(lang)s_page_raw pp ON (prev = pp.page_title)
LEFT JOIN
west1.%(lang)s_page_raw cp ON (curr = cp.page_title)
WHERE
cp.page_title is not NULL
AND ( pp.page_title is NOT NULL
OR prev in ('other-empty', 'other-internal', 'other-external', 'other-search', 'other-other')
);
-- annotate link types
DROP VIEW IF EXISTS west1.clickstream_%(table)s_temp6;
CREATE VIEW west1.clickstream_%(table)s_temp6 AS
SELECT
prev,
curr,
curr_unresolved,
CASE
WHEN prev in ('other-empty', 'other-internal', 'other-external', 'other-search', 'other-other') THEN 'external'
WHEN l.pl_from IS NOT NULL AND l.pl_to IS NOT NULL THEN 'link'
ELSE 'other'
END AS type,
n
FROM
west1.clickstream_%(table)s_temp5
LEFT JOIN
west1.%(lang)s_pagelinks l ON (prev = l.pl_from AND curr = l.pl_to);
-- create table
DROP TABLE IF EXISTS west1.clickstream_%(table)s;
CREATE TABLE west1.clickstream_%(table)s
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE AS
SELECT
*
FROM
west1.clickstream_%(table)s_temp6
WHERE
curr != prev;
DROP VIEW west1.clickstream_%(table)s_temp1;
DROP VIEW west1.clickstream_%(table)s_temp2;
DROP VIEW west1.clickstream_%(table)s_temp3;
DROP VIEW west1.clickstream_%(table)s_temp4;
DROP VIEW west1.clickstream_%(table)s_temp5;
DROP VIEW west1.clickstream_%(table)s_temp6;
"""
print(query % params)
exec_hive_stat2(query % params, priority = priority)
def get_clickstream(table, lang, start, stop, priority=False, min_count=10):
params = {
'time_conditions': get_hive_timespan(start, stop, hour=False),
'table': table,
'lang': lang,
'min_count': min_count,
}
query = """
-- ############################################
-- create helper tables
-- create copy of page table and insert rows for our special prev pages
-- this will let us work with ids instead of titles later, which is much less error prone
DROP TABLE IF EXISTS clickstream.%(table)s_page_helper;
CREATE TABLE clickstream.%(table)s_page_helper AS
SELECT
*
FROM
clickstream.%(lang)s_page
;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-1 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-empty' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-2 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-internal' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-3 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-external' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-4 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-search' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
INSERT INTO TABLE clickstream.%(table)s_page_helper
SELECT
-5 AS page_id,
0 AS page_namespace,
false AS page_is_redirect,
'other-other' AS page_title
FROM clickstream.%(table)s_page_helper
LIMIT 1;
-- create pagelinks table that resolves links that end in a redirect
-- this means that if A links to B, and B redirects to C, we replace the link (A,B) with (A,C)
-- this lets us properly annotate link types after resolving redirects in the clickstream, since
-- a user will experience following A as if it linked to C
-- the group be ensures that each link only occurs once
DROP TABLE IF EXISTS clickstream.%(table)s_pagelinks_helper;
CREATE TABLE clickstream.%(table)s_pagelinks_helper AS
SELECT
pl_from_page_id,
pl_to_page_id
FROM
(SELECT
pl_from_page_id,
CASE
WHEN r.rd_to_page_id IS NULL THEN pl_to_page_id
ELSE rd_to_page_id
END AS pl_to_page_id
FROM
clickstream.%(lang)s_pagelinks l
LEFT JOIN
clickstream.%(lang)s_redirect r ON (r.rd_from_page_id = l.pl_to_page_id)
) a
GROUP BY
pl_from_page_id,
pl_to_page_id
;
-- ############################################
-- extract raw prev, curr pairs
DROP VIEW IF EXISTS clickstream.%(table)s_temp1;
CREATE VIEW clickstream.%(table)s_temp1 AS
SELECT
CASE
-- empty or malformed referer
WHEN referer IS NULL THEN 'other-empty'
WHEN referer == '' THEN 'other-empty'
WHEN referer == '-' THEN 'other-empty'
WHEN parse_url(referer,'HOST') is NULL THEN 'other-empty'
-- internal referer from the same wikipedia
WHEN
parse_url(referer,'HOST') in ('%(lang)s.wikipedia.org', '%(lang)s.m.wikipedia.org')
AND LENGTH(REGEXP_EXTRACT(parse_url(referer,'PATH'), '/wiki/(.*)', 1)) > 1
THEN REGEXP_EXTRACT(parse_url(referer,'PATH'), '/wiki/(.*)', 1)
-- other referers
WHEN referer_class = 'internal' THEN 'other-internal'
WHEN referer_class = 'external' THEN 'other-external'
WHEN referer_class = 'external (search engine)' THEN 'other-search'
ELSE 'other-other'
END as prev,
pageview_info['page_title'] as curr
FROM
wmf.webrequest
WHERE
%(time_conditions)s
AND webrequest_source = 'text'
AND normalized_host.project_class = 'wikipedia'
AND normalized_host.project = '%(lang)s'
AND is_pageview
AND agent_type = 'user'
;
-- count raw prev, curr pairs, this speeds up later queries
DROP TABLE IF EXISTS clickstream.%(table)s_temp2;
CREATE TABLE clickstream.%(table)s_temp2 AS
SELECT
prev, curr, COUNT(*) as n
FROM
clickstream.%(table)s_temp1
GROUP BY
prev, curr
;
-- we enforce that curr and prev are main namespace pages
-- the joins accomplish this because, in the logs, the non main namespace pages have the namespace prepended
-- at this point curr and prev are ids
DROP TABLE IF EXISTS clickstream.%(table)s_temp3;
CREATE TABLE clickstream.%(table)s_temp3 AS
SELECT
pp.page_id as prev,
pc.page_id as curr,
n
FROM
clickstream.%(table)s_temp2
JOIN
clickstream.%(table)s_page_helper pp ON (prev = pp.page_title)
JOIN
clickstream.%(table)s_page_helper pc ON (curr = pc.page_title)
WHERE
pp.page_namespace = 0
AND pc.page_namespace = 0
;
-- resolve curr redirects, one step
-- note that prev should not be a redirect, so we do not bother resolving it
-- and prev redirects will be filtered out at the end
DROP TABLE IF EXISTS clickstream.%(table)s_temp4;
CREATE TABLE clickstream.%(table)s_temp4 AS
SELECT
prev,
CASE
WHEN rd_to_page_id IS NULL THEN curr
ELSE rd_to_page_id
END AS curr,
n
FROM
clickstream.%(table)s_temp3
LEFT JOIN
clickstream.%(lang)s_redirect ON (curr = rd_from_page_id)
;
-- re-aggregate after resolving redirects and filter out pairs that occur infrequently
DROP TABLE IF EXISTS clickstream.%(table)s_temp5;
CREATE TABLE clickstream.%(table)s_temp5 AS
SELECT
prev, curr, SUM(n) as n
FROM
clickstream.%(table)s_temp4
GROUP BY
prev, curr
HAVING
SUM(n) > %(min_count)s
;
-- annotate link types
DROP TABLE IF EXISTS clickstream.%(table)s_temp6;
CREATE TABLE clickstream.%(table)s_temp6 AS
SELECT
prev,
curr,
CASE
WHEN prev < 0 THEN 'external'
WHEN (pl_from_page_id IS NOT NULL AND pl_to_page_id IS NOT NULL) THEN 'link'
ELSE 'other'
END AS type,
n
FROM
clickstream.%(table)s_temp5
LEFT JOIN
clickstream.%(table)s_pagelinks_helper ON (prev = pl_from_page_id AND curr = pl_to_page_id)
;
-- create final table
-- remove self loops
-- restrict prev and curr to main namespace, no redirects
-- get page titles
DROP TABLE IF EXISTS clickstream.%(table)s;
CREATE TABLE clickstream.%(table)s
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE AS
SELECT
pp.page_title as prev,
pc.page_title as curr,
a.type,
a.n
FROM
clickstream.%(table)s_temp6 a
JOIN
clickstream.%(table)s_page_helper pp ON (prev = pp.page_id)
JOIN
clickstream.%(table)s_page_helper pc ON (curr = pc.page_id)
WHERE
pp.page_is_redirect = false
AND pp.page_namespace = 0
AND pc.page_is_redirect = false
AND pc.page_namespace = 0
AND a.curr != a.prev
;
DROP VIEW clickstream.%(table)s_temp1;
DROP TABLE clickstream.%(table)s_temp2;
DROP TABLE clickstream.%(table)s_temp3;
DROP TABLE clickstream.%(table)s_temp4;
DROP TABLE clickstream.%(table)s_temp5;
DROP TABLE clickstream.%(table)s_temp6;
DROP TABLE clickstream.%(table)s_page_helper;
DROP TABLE clickstream.%(table)s_pagelinks_helper;
"""
exec_hive_stat2(query % params, priority=priority)