I'm wondering how we would compare this with the API Analytics Dashboard, and the underlying data product we want to introduce in T419522 (which also contains the same fields you need, as I understand it).

When that ticket is solved, would we:

1. Not need Turnilo, because we can support this kind of investigation via Superset (first as SQL Lab queries, then via the API Analytics Dashboard)
2. Still need Turnilo, because we also need this data to be populated in near-realtime (hence using the streaming job)
3. Still need Turnilo for other reasons (e.g. the investigation is hard to do in Superset for performance reasons or other)

I think we probably don't have an exact answer (or all the information for it), but I'm interested in @HCoplin-WMF 's thoughts on this if you have any.

I'm asking because I've been trying to experiment with replacing some of the analytical Turnilo use cases with curated dashboards. In other words, I'd have a bias for path 1 above: prioritizing T419522 so you can use SQL Lab instead of Logstash in the short term, then make this investigation user journey well-supported in the upcoming dashboard.

Definitely #2 and #3. My experience with Superset is that it is borderline unusable because the performance issues require incredibly narrow queries, unless you go through SSH + Jupyter notebook, which is also a pain to do for one off questions. I (and several other folks across our org) use Turnilo several times a week if not daily to answer adhoc questions and easily share findings with each other.

The specific use case we have here is more focused on answering emergent questions, so I'm not sure that a dashboard would necessarily completely solve our needs since we can't fully predict the types of questions that might emerge. However, if we're creating a dedicated data pipeline for said dashboard that would be easier to query in Superset, I would certainly be open to that as an alternative.

Thank you! Let's talk about the other uses you have for Turnilo later on :)

However, if we're creating a dedicated data pipeline for said dashboard that would be easier to query in Superset, I would certainly be open to that as an alternative.

Yes, that's the plan. T419522 creates the base data, but it will be further filtered/aggregated for the dashboard. I think that's still a reasonable medium-term plan but let's not block you now on this possibility, especially if you'll need Turnilo otherwise anyway.

So yeah, it makes sense to handle this ticket separately from the other.

Just adding another usecase to have this data in Turnilo.

The API analytics data product and the dashboard would be helpful for regular tracking and reporting.

As we observed during API ratelimiting rollout, often need to get top N users agents or IPs triggering a ratelimit class, at a certain endpoint etc. We had to do this multiple times during the first phase. I expect there will be such a need again during future roll-outs. I would run a query in JupyterLab, this needed for engineers if they would like to quickly check something. @daniel can share more and other uses he might have with this data in Turnilo.

Beyond the top-n use case KC mentioned, it would also be super helpful to have the rate limit class in Turnilo when investigating issues when clients get hit with unexpected/unintended rate limits, e.g. by searching for an IP address and then splitting by ratelimit class. Basically, when looking at rate limited requests, it tells me *why* these were rate limited.

@Ahoelzl & @GGoncalves-WMF -- do you have a rough idea for when you might be able to pull this in? The next wave of rate limiting enforcement is planned for late April, so we'd ideally like to have this in place soon.

Hey there 👋 Just following up on this again. I specifically needed this today, so hoping we can get the data into Turnilo soon 😁

Theoretically we can also answer most of these questions with Superset. However, Superset can be slow with webrequest and is prone to timeout. Something that could be helpful in the long term is T413952: [Request] Sample of refined webrequest table in data lake. It reduces dependency on fields being added to Turnilo each time.

Note: Druid webrequest_sampled_live is available to query on Superset, but not the refined version which has the required fields.

In T419736#11842680, @KCVelaga_WMF wrote:

Theoretically we can also answer most of these questions with Superset. However, Superset can be slow with webrequest and is prone to timeout. Something that could be helpful in the long term is T413952: [Request] Sample of refined webrequest table in data lake. It reduces dependency on fields being added to Turnilo each time.

While technically feasible, querying webrequest in Superset is not a good idea: the dataset is too big.
If you're investigating on a single hour, it'll work as the amount of data is manageable, but past that it's too much for the system. Even if requests can succeed for a day, they can overwhelm the system and prevent other queries to run correctly.
I wish we manage to make this a technical restriction at some point :)

Sorry for the delay here, @Ahoelzl is looking for an assignee.

Can we also get the value of the x-ua-contact header? Is that in the dataset? It's not absolutely needed, but if it is easy to include, it would make our lives easier.

Next steps from @JAllemandou:

I think there is one missing step:

• webrequest_sampled hourly load hive to druid should also load the new dimensions.

Change #1279465 had a related patch set uploaded (by CDanis; author: CDanis):

[operations/puppet@production] haproxy: webrequest: capture ratelimiting headers

https://gerrit.wikimedia.org/r/1279465

Change #1279489 had a related patch set uploaded (by Aleksandar Mastilovic; author: Aleksandar Mastilovic):

[operations/puppet@production] Add x_trusted_request and x_wmf_ratelimit_class to webrequest live streams

https://gerrit.wikimedia.org/r/1279489

Any progress on this? We ran into a situation with requests from the Wikipedia App getting blocked, and having the rate limit class in Turnilo would really help with investigating the problem...

In T419736#11885859, @daniel wrote:

Any progress on this? We ran into a situation with requests from the Wikipedia App getting blocked, and having the rate limit class in Turnilo would really help with investigating the problem...

At the moment we are configuring Benthos and HAProxy to relay these headers and their values to the webrequest_live stream, you can see the CRs by Chris Danis and myself a couple of messages above. Once that's in place, we will configure the Druid ingestion to take on these new fields, and then configure Turnilo to show them in UI.

Change #1279465 merged by CDanis:

[operations/puppet@production] haproxy: webrequest: capture ratelimiting headers

https://gerrit.wikimedia.org/r/1279465

Change #1279489 merged by CDanis:

[operations/puppet@production] Add x_trusted_request and x_wmf_ratelimit_class to webrequest live streams

https://gerrit.wikimedia.org/r/1279489

Change #1283833 had a related patch set uploaded (by Aleksandar Mastilovic; author: Aleksandar Mastilovic):

[analytics/refinery@master] Add two new rate-limiting fields to webrequest_sampled

https://gerrit.wikimedia.org/r/1283833

Change #1283833 merged by Aleksandar Mastilovic:

[analytics/refinery@master] Add two new rate-limiting fields to webrequest_sampled

https://gerrit.wikimedia.org/r/1283833

Change #1283862 had a related patch set uploaded (by CDanis; author: CDanis):

[operations/puppet@production] benthos/webrequest: fix rl_class/trusted_req names

https://gerrit.wikimedia.org/r/1283862

Change #1283862 merged by CDanis:

[operations/puppet@production] benthos/webrequest: fix rl_class/trusted_req names

https://gerrit.wikimedia.org/r/1283862

@amastilovic Please note the latest patch from CDanis: field names have changed! we need to update the webrequest table again.
We also need to update the realtime indexation job at refinery/druid/kafka/webrequest_sampled_supervisor.json :)

FWIW I kept the names unchanged on the past-Benthos side

Change #1284700 had a related patch set uploaded (by Aleksandar Mastilovic; author: Aleksandar Mastilovic):

[analytics/refinery@master] Add two new rate-limiting value fields to Druid's Kafka supervisor JSON

https://gerrit.wikimedia.org/r/1284700

Change #1284700 merged by Joal:

[analytics/refinery@master] Add two new rate-limiting value fields to Druid's Kafka supervisor JSON

https://gerrit.wikimedia.org/r/1284700

In T419736#11898635, @CDanis wrote:

FWIW I kept the names unchanged on the past-Benthos side

Wow I missed that! Sorry for the wrong info.

Change #1284776 had a related patch set uploaded (by Aleksandar Mastilovic; author: Aleksandar Mastilovic):

[analytics/refinery@master] Add two new rate-limiting fields to Druid load webrequest_sampled

https://gerrit.wikimedia.org/r/1284776

Change #1284776 merged by Aleksandar Mastilovic:

[analytics/refinery@master] Add two new rate-limiting fields to Druid load webrequest_sampled

https://gerrit.wikimedia.org/r/1284776

amastilovic opened https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2225

Add new Druid fields, some refactoring

Change #1284876 had a related patch set uploaded (by Aleksandar Mastilovic; author: Aleksandar Mastilovic):

[operations/deployment-charts@master] Add x_wmf_ratelimit_class and x_trusted_request to Turnilo

https://gerrit.wikimedia.org/r/1284876

amastilovic merged https://gitlab.wikimedia.org/repos/data-engineering/airflow-dags/-/merge_requests/2225

Add new Druid fields, some refactoring

OK so everything except Turnilo has been configured and deployed. We might have to wait for Monday for Turnilo changes to get applied, it's Friday and bank holiday in European countries so most people with permissions to merge are out of office.

New columns are in the Hive webrequest_sampled table, imported by Gobblin:

hive (wmf_raw)> SELECT x_cache, x_is_browser, x_wmf_ratelimit_class, x_trusted_request FROM webrequest_sampled WHERE year=2026 AND month=5 AND day=7 AND hour=1 LIMIT 20;
OK
x_cache x_is_browser    x_wmf_ratelimit_class   x_trusted_request
cp1111 hit, cp1111 hit/1804     70      -       E
cp1111 hit, cp1111 hit/13       70      -       E
cp1111 int      80      -       E
cp1100 hit, cp1100 hit/2        90      -       C
cp1100 miss, cp1100 hit/177124  70      -       E
cp1100 miss, cp1100 hit/1716    100     anon-browser    E
cp1100 miss, cp1100 hit/610238  100     -       E
cp1100 miss, cp1100 pass        60      -       E
cp1100 miss, cp1100 hit/1005    70      -       E
cp1100 miss, cp1100 pass        75      -       E
-       -       -       -
-       -       -       -
cp2053 miss, cp2053 miss        10      -       E
cp2053 miss, cp2053 pass        100     anon-browser    E
cp2053 miss, cp2053 hit/250113  80      -       E
-       -       -       -
cp3066 miss, cp3066 hit/3296020 -10     -       E

The new fields are also in the Druid webrequest_sampled_live table:

Change #1284876 merged by Brouberol:

[operations/deployment-charts@master] Add x_wmf_ratelimit_class and x_trusted_request to Turnilo

https://gerrit.wikimedia.org/r/1284876

Turnilo has been updated this morning, so I believe that completes this task.

Something is not right with the batch job: when looking for X-WMF-RateLimit-Class in turnilo, we get the last hour with values but not the the past days.
This seems to be because the refinery repo has not been released to hadoop (see After the deployment in the wikitech page).
I'm running the script as I write, it should solve the problem. I'll rerun a couple of hours of the Airflow job in order to have some historical data.

@JAllemandou true, I forgot to do that step :( Thanks for taking care of it!

This is amazing to have in place, thank you so much!

+1 to what @JTweed-WMF said! This is great and so much easier & more convenient to use -- thank you so much for responding and enabling it quickly :)