I looked at the code and have some comments, but not that many given the complexity of the analysis :) Good job @MGerlach!

The config file I've is here https://github.com/srish/wmcs-edits/blob/master/config.yaml.

One comment on the value you picked for lagin the config. Reportupdater will by default run a query having granularity month after the month is done. For instance it will run query for the month of 2019-11 on 2019-12-01, with start_date = 2019-11-01 and end_date = 2019-12-01 (inclusive start-date, exclusive end-date). The lag parameter is time you add to the default running date for data availability (or any other reason). In our case, data is being copied from mysql databases onto the hadoop cluster on the 1st of the month, and in past months it has been very regular in taking just a few hours. I therefore suggest using a lag of 1 day, also noticing that rerunning reportupdater queries is just a matter of removing the file with incorrect data.

There are a couple of jobs I'd like to check (mediawiki-history, checker, mobile-app-session jobs and wikidata jobs). If we can't run in yarn, I'll do smaller versions in local.

Hi @srishakatux - Please excuse me again for another delayed answer - I do hope my personal issues are over now :)

Here is my understanding of the different things at play here:

• Caching is made by the broker by query - per-segment. This means for a given set query-parameters (excluding interval boundaries), the cache uses/stores results for this query for every segment required by the query interval.
• When using a line chart and a groupBy with a single dimension, Superset issues 2 queries, the first one to gather the list of values to use (topN over the given time-period, with ALL granularity), and then another one to get split-by-time results for the precomputed list of values (topN over the given time-period, with the selected granularity and a OR filter for the values list). It is interesting to notice that when using groupBy with 2 dimensions, superset issues a single groupBy query since the double-query optimization can only be done over one dimension.
• When the query for a long time-period fails, it fails on the second query (the one with the time-split and value-filter). The first one that precomputes values is always successful (at least with my tests). Something else to notice is that this is this first query that supersert displays when using "View query".
• Preloading the segment-result cache for a long time-period (4 years for instance) means running the second query for a small-enough time period (1 year worked for me in our case), allowing the query to be successful and therefore the cache to be loaded for the queried segments. Then reissuing the query for a longer time-period (2 years for instance), and again, up to having cached data for most of the needed segments. I confirm that this strategy works, I have managed to have a 4 years chart in superset.
• The gotcha here is that this strategy cannot usually be transfered to superset: when asking superset for a 1 year chart, then a 2 years chart, it will issue 2 queries each time, and it is reasonable that the list of values parameterizing the second query will vary from the 1 year chart to the 2 years chart, meaning no caching is actually made for the 2 years chart when querying for the 1 year chart as query parameters are not the same. I have overcome this limitation and managed to draw a 4 years chart in superset by issuing manual queries to druid having the correct parameters.

Understood @bd808 - Thanks for the answer :)

this is done @GoranSMilovanovic.
Raw data is here /user/joal/wmf/data/raw/mediawiki/wikidata/all_jsondumps/20190902 and parquet data is here /user/joal/wmf/data/wmf/mediawiki/wikidata_parquet/20190902

In T226663#5542860, @srishakatux wrote:

@Milimetric @JAllemandou Hello! Thank you both for your help. This project is a learning project for me :) So, for the pieces that are feasible to do by the folks outside your team, I would love to give a shot at them. I might be a bit slow and reach out to you via IRC for questions if that is okay with you.
Perhaps setting the ReportUpdater (I have a task for it already here T232671), or configuration file that needs to be created on Meta for generating the dashboard.

Hi @srishakatux - I'm sorry for not answering sooner, I've been sick the whole past week :(
Let's synchronize on how to generate the data.
In my mind the plan is to:

1. have the network_origin column added to geoeditors-daily (T233504 and associated CR https://gerrit.wikimedia.org/r/#/c/analytics/refinery/+/538613/)
2. Create a reportupdater query generating the aggregated data with desired dimensions every month, storing results in a new file per month

Hi @MGerlach and @leila - kids and I have been sick almost full last week, explaining me not answering fast.
I have spent time trying to get a precise answer to the memory issue, but couldn't get as precise as would have expected :(

Provided 2 patches based on the request above as examples. More discussion is probably needed based on needed retention.
Example results for enwiki for 2019-08:

Hi @Ottomata - I like the annotations subobject - Explicit for the win. I however have no good reason not to use the other solution.

Hi @MGerlach,
Awesome results :)

Documentation updated:

• https://wikitech.wikimedia.org/w/index.php?title=Analytics%2FData_Lake%2FTraffic%2FWebrequest&type=revision&diff=1838198&oldid=1830820
• https://wikitech.wikimedia.org/w/index.php?title=Analytics%2FData_Lake%2FTraffic%2FPageview_hourly&type=revision&diff=1838199&oldid=1833218
• https://wikitech.wikimedia.org/wiki/Analytics/Systems/ua-parser
• https://wikitech.wikimedia.org/wiki/Analytics/Systems/ua-parser/2019-09-18_Update

This is great finding!
This file does not contain redirect only, but also every other aliases that might be usefull :)
Awesome

+1 to that :)

I mildly disaggre with @Nuria for ORES scores - I think it could be very cool to have them (some models only, one model version only). Maybe in a separate table, or in different dumps. As for parsoid, I don't really understand what that is.
Edited for typos.

After yesterday's talk in tasking, here is the plan:

• • Use uap-core upstream master for regexes. This means we no longer need to maintain our own uap-core fork -- Actions:
    • Delete https://gerrit.wikimedia.org/r/#/admin/projects/analytics/ua-parser/uap-core
  • Update uap-java to latest in our fork, rebase wmf branch and update it for submodule freshness and cache-size parameterization -- Actions:
    • Update https://gerrit.wikimedia.org/r/#/admin/projects/analytics/ua-parser/uap-java to match upstream https://github.com/ua-parser/uap-java (23 commits)
    • Push force a clean wmf branch from new master
    • Add 3 patches to wmf branch:
      • Update pom.xml (jar name to match and other configuration changes)
      • update uap-core submodule to upstream master
      • add cache-size parameterization (this updtream PR: https://github.com/ua-parser/uap-java/pull/45)
• Update uap-python to latest in our fork and rebase the debian branch on top of it -- Actions:
  • Update https://gerrit.wikimedia.org/r/#/admin/projects/operations/debs/python-ua-parser to match upstream https://github.com/ua-parser/uap-python (23 commits)
  • Rebase debian branch on top of updated master, and add a commit for a new debian package version release

I recall that as well @Ottomata - My note was purely theoretical :) I'm fully in favor of having a map keyed by model names.

I like the idea of having the model-name as map-key. Only limitation I can think of is that only one model version can be reported on a revision, except if we put an array (or a version map !) as the map value ... Seems overkill.

Snapshot 2019-07 manually fixed using a manually uncompressed version of the problematic file.
Leaving this task open in pause for possible future similar problem.

Doc updated here: https://wikitech.wikimedia.org/wiki/Analytics/Systems/Superset#Usage_notes

Update with investigation results so far:
August job failed for dewiki only, with a decompression error:

java.lang.ArrayIndexOutOfBoundsException: 18002
	at org.apache.hadoop.io.compress.bzip2.CBZip2InputStream.recvDecodingTables(CBZip2InputStream.java:732)
	at org.apache.hadoop.io.compress.bzip2.CBZip2InputStream.getAndMoveToFrontDecode(CBZip2InputStream.java:803)
	at org.apache.hadoop.io.compress.bzip2.CBZip2InputStream.initBlock(CBZip2InputStream.java:506)
	at org.apache.hadoop.io.compress.bzip2.CBZip2InputStream.changeStateToProcessABlock(CBZip2InputStream.java:335)
	at org.apache.hadoop.io.compress.bzip2.CBZip2InputStream.read(CBZip2InputStream.java:425)
	at org.apache.hadoop.io.compress.BZip2Codec$BZip2CompressionInputStream.read(BZip2Codec.java:485)
	at org.apache.hadoop.io.compress.BZip2Codec$BZip2CompressionInputStream.read(BZip2Codec.java:504)
	at java.io.FilterInputStream.read(FilterInputStream.java:83)
	at org.wikimedia.wikihadoop.newapi.ByteMatcher.readUntilMatch(ByteMatcher.scala:116)
	at org.wikimedia.wikihadoop.newapi.ByteMatcher.readUntilMatch(ByteMatcher.scala:110)
	at org.wikimedia.wikihadoop.newapi.MediawikiXMLRevisionInputFormat$MediawikiXMLRevisionRecordReader.readNextRevision(MediawikiXMLRevisionInputFormat.scala:256)
	at org.wikimedia.wikihadoop.newapi.MediawikiXMLRevisionInputFormat$MediawikiXMLRevisionRecordReader.nextKeyValue(MediawikiXMLRevisionInputFormat.scala:208)
	at org.apache.spark.rdd.NewHadoopRDD$$anon$1.hasNext(NewHadoopRDD.scala:214)
	at org.apache.spark.InterruptibleIterator.hasNext(InterruptibleIterator.scala:37)
	at org.apache.spark.util.Utils$.getIteratorSize(Utils.scala:1836)
	at org.apache.spark.rdd.RDD$$anonfun$count$1.apply(RDD.scala:1162)
	at org.apache.spark.rdd.RDD$$anonfun$count$1.apply(RDD.scala:1162)
	at org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:2074)
	at org.apache.spark.SparkContext$$anonfun$runJob$5.apply(SparkContext.scala:2074)
	at org.apache.spark.scheduler.ResultTask.runTask(ResultTask.scala:87)
	at org.apache.spark.scheduler.Task.run(Task.scala:109)
	at org.apache.spark.executor.Executor$TaskRunner.run(Executor.scala:345)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

I have tracked down the error to that precise file: /wmf/data/raw/mediawiki/xmldumps/pages_meta_history/20190801/dewiki/dewiki-20190801-pages-meta-history6.xml-p9471825p9637791.bz2. All other dewiki files don't cause problem.

Thanks for reporting @kzimmerman :)
The reason for the discrepancy is the aggregation function of the generated druid query when using superset metric-aggregation. When choosing the view_count metric in a superset chart, then picking the SUM aggregation function, the aggregation is managed by superset, and uses floatSum which leads to innacuracies due to big numbers worked with 32 bits instead of 64. The predefined SUM(view_count) should be picked instead, which is manually defined to use doubleSum.
Druid queries run from a stat machine showing different results using floatSum or doubleSum:

• doubleSum (correct - matches turnilo and wikistats2)

curl -X POST -H 'content-type: application/json' -d '
{
  "queryType": "timeseries",
  "dataSource": "pageviews_daily",
  "aggregations": [
    {
      "fieldName": "view_count",
      "fieldNames": [
        "view_count"
      ],
      "type": "doubleSum",
      "name": "sum_view_count"
    }
  ],
  "granularity": "all",
  "postAggregations": [],
  "intervals": "2019-07-01/2019-08-01",
  "filter": {
    "type": "selector",
    "dimension": "agent_type",
    "value": "user"
  }
}
' http://druid1001.eqiad.wmnet:8082/druid/v2/?pretty

Good point @nettrom_WMF, there is indeed a discrepancy between doc and reality, introduced with the database change of using a normalized table for user information (the actor table) instead of storing all user information (mostly user_id and user_text) in the main tables (revision, logging, etc). With this change user_id of anonymous users is always null in mediawiki_history (as in original database), meaning you can't rely on it as a proxy for deleted revisions. You should use revision_deleted_parts instead, even if not as easy (sorry :S). Updating the docs right now.

Question for @Ottomata and @Nuria : Do we prefer to move old jars to new ones and get rid of every jar older than version X, or do we get rid of currently-unused jars only (which already represents a huge win)?

Audit of versioned jar files needed (non-versioned files should always be there):

• In puppet repo:

Changing sqoop to join a base table to the comment one in the sqoop-SQL query has been tested for mediawiki-history and has lead to non-acceptable performance (too long).
We now sqoop the comment and actor tables from analytics production replicas for acceptable performance time, and join base tables to the actor and comment ones in Spark for mediawiki-history.
I suggest the same approach should be used for cu_changes, meaning waiting for actor and comment tables to be present, and join to them.
Waiting for comments before starting to work in that direction.

Could we change oozie jobs and hdfs path and keep druid name?