Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Paste
P9756
T236834_merge_old_new_logs_1.hql
Active
Public
Actions
Authored by
•
AndyRussG
on Nov 27 2019, 5:32 AM.
Edit Paste
Archive Paste
View Raw File
Subscribe
Mute Notifications
Award Token
Flag For Later
Tags
None
Referenced Files
F31312099: raw.txt
Nov 27 2019, 5:32 AM
2019-11-27 05:32:38 (UTC+0)
Subscribers
None
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
);
Event Timeline
•
AndyRussG
created this paste.
Nov 27 2019, 5:32 AM
2019-11-27 05:32:38 (UTC+0)
•
AndyRussG
mentioned this in
T236834: FRUEC: Detailed comparison of events in old and new log files for banner impression pipeline
.
Nov 27 2019, 5:47 AM
2019-11-27 05:47:37 (UTC+0)
Log In to Comment