Page MenuHomePhabricator
Paste P9756

T236834_merge_old_new_logs_1.hql
ActivePublic

Authored by AndyRussG on Wed, Nov 27, 5:32 AM.
SELECT
old.dt,
old.ip,
old.cache_status,
old.http_status,
old.http_method,
old.uri_host,
old.uri_path,
old.uri_query,
old.content_type,
old.referer,
old.x_forwarded_for,
old.user_agent,
old.accept_language,
old.x_analytics,
old.range,
old.is_pageview,
old.record_version,
old.client_ip,
old.geocoded_data,
old.x_cache,
old.user_agent_map,
old.x_analytics_map,
old.ts,
old.access_method,
old.agent_type,
old.is_zero,
old.referer_class,
old.normalized_host,
old.pageview_info,
old.page_id,
old.namespace_id,
old.tags,
old.isp_data,
old.random_campaign AS old_random_campaign,
new.random_campaign AS new_random_campaign
FROM
( SELECT
*,
regexp_extract( uri_query, 'randomcampaign%22%3A([0-9.]*)', 1 ) AS random_campaign
FROM
wmf.webrequest
WHERE
year = 2019
AND month = 11
AND day = 5
AND uri_query LIKE "%CentralNoticeImpression%"
AND uri_path = '/beacon/event'
AND dt >= '2019-11-05T01:00:00Z'
AND dt <= '2019-11-05T22:59:59Z'
AND uri_query LIKE '%WMDE_Authors_Campaign_2019_Austria_Switzerland%' ) new
RIGHT JOIN
( SELECT
*,
parse_url( concat( 'http://bla.org/woo/', uri_query ), 'QUERY', 'randomcampaign' ) AS random_campaign
FROM
wmf.webrequest
WHERE
year = 2019
AND month = 11
AND day = 5
AND uri_path = '/beacon/impression'
AND dt >= '2019-11-05T01:00:00Z'
AND dt <= '2019-11-05T22:59:59Z'
AND uri_query LIKE '%WMDE_Authors_Campaign_2019_Austria_Switzerland%' ) old
ON
( new.random_campaign = old.random_campaign );