Page Menu
Home
Phabricator
Search
Configure Global Search
Log In
Paste
P9757
T236834_merge_new_old_logs_1.hql
Active
Public
Actions
Authored by
AndyRussG
on Nov 27 2019, 5:40 AM.
Edit Paste
Archive Paste
View Raw File
Subscribe
Mute Notifications
Award Token
Flag For Later
Tags
None
Referenced Files
F31312219: raw.txt
Nov 27 2019, 5:40 AM
2019-11-27 05:40:52 (UTC+0)
Subscribers
None
SELECT
new
.
dt
,
new
.
ip
,
new
.
cache_status
,
new
.
http_status
,
new
.
http_method
,
new
.
uri_host
,
new
.
uri_path
,
new
.
uri_query
,
new
.
content_type
,
new
.
referer
,
new
.
x_forwarded_for
,
new
.
user_agent
,
new
.
accept_language
,
new
.
x_analytics
,
new
.
range
,
new
.
is_pageview
,
new
.
record_version
,
new
.
client_ip
,
new
.
geocoded_data
,
new
.
x_cache
,
new
.
user_agent_map
,
new
.
x_analytics_map
,
new
.
ts
,
new
.
access_method
,
new
.
agent_type
,
new
.
is_zero
,
new
.
referer_class
,
new
.
normalized_host
,
new
.
pageview_info
,
new
.
page_id
,
new
.
namespace_id
,
new
.
tags
,
new
.
isp_data
,
new
.
random_campaign
AS
new_random_campaign
,
old
.
random_campaign
AS
old_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
=
20
AND
uri_query
LIKE
"%CentralNoticeImpression%"
AND
uri_path
=
'/beacon/event'
AND
dt
>=
'2019-11-20T01:00:00Z'
AND
dt
<=
'2019-11-20T22:59:59Z'
AND
(
uri_query
LIKE
'%C19_WMDE_Mobile_Test_02%'
OR
uri_query
LIKE
'%C19_WMDE_Test_11%'
)
)
new
LEFT
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
=
20
AND
uri_path
=
'/beacon/impression'
AND
dt
>=
'2019-11-20T01:00:00Z'
AND
dt
<=
'2019-11-20T22:59:59Z'
AND
(
uri_query
LIKE
'%C19_WMDE_Mobile_Test_02%'
OR
uri_query
LIKE
'%C19_WMDE_Test_11%'
)
)
old
ON
(
new
.
random_campaign
=
old
.
random_campaign
);
Event Timeline
AndyRussG
created this paste.
Nov 27 2019, 5:40 AM
2019-11-27 05:40:52 (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