The event_user_text field in wmf.mediawiki_history is what can be called the "current" value of the username, by opposition to the value it had at the time the edit was made which is stored in event_user_text_historical. Since IPs are stored at edit-time and we don't build user-history for IPs, the values are stored in `event_user_text_historical:

select count(distinct event_user_text) from wmf.mediawiki_history where snapshot='2018-09' and event_entity = 'revision' and event_user_is_anonymous and SUBSTR(event_timestamp, 0, 7) >= '2018-08'

@Ottomata - After standup please, as today is kids-day for me :)

Right, I get it now :)
We discussed withbthe team and our plan is to change how we detect/filter pageviews from a domain perspective. We'll update the doc as needed when we're done refactoring.
In the meantime, we're going to add wikitech.wikimedia.org to the regex, providing pageviews for wikitech site.

Why is this not documented on the wiki creation page?

I don't underdstand what the 'wiki creation page' is, but I think the current doc is correct about wikitech not being a pageview (https://meta.wikimedia.org/wiki/Research:Page_view/Generalised_filters).

wikitech is not part of the projects to account for in PageviewDefinition code (https://github.com/wikimedia/analytics-refinery-source/blob/master/refinery-core/src/main/java/org/wikimedia/analytics/refinery/core/PageviewDefinition.java#L77). Easy to change if needed.

Some more info on the issue as I understand it: Given the data size to work and the fact that hive.auto.convert.join is set to true by default, Hive converts the join to happen into a Map side join, and even more, into a LOCAL map side join. This means that the join happens locally on the client instead of happening onto a cluster container. When using the hive client, local memory can be configured using HADOOP_HEAPSIZE en variable. However when using hue or beeline, local actually means in HiveServer2.
The solution provided above (SET hive.auto.convert.join=false;) will work in any case, but it could interesting to try to raise HiveServer2 available memory and see if it solves the issue.

I have investigated on this topic and have some answers.

I agree with @Nuria on option 3 being a prefered solution for the AQS use-case.
The solution I'd like would be a single RESTBase module whose job would be to redirect any call to https://wikimedia.org/api/rest_v1/metrics/{PARAMS} to another external endpoint base to which PARAMS would be appended, without manipulating the parameters. This would let AQS act as the main server for requests.
I think however the use case for AQS might be simplistic in comparison to others, so the simplistic solution might not be the best.
Happy to talk about it further.

> @nettrom_WMF what can we do to get your queries moved to Hive instead of MySQL? You can use the hive table event.mediawiki_page_create.

Jobs have been successful for the past months. However rerunning the jobs manually made the data-points appear. This is very bizarre.
Let's keep this open and monitor next month.

Hi @Neil_P._Quinn_WMF ,
The error in the first query comes from map memory being too small for the query you run. This can be solved by setting the memory limit for maps higher: SET mapreduce.map.memory.mb=4096;
Also, hue is not good in giving you proper error messages. I got the informative error message by running the query in a CLI from stat1004 (or 5).

The implemented solution is not a real one: it's an oozie check preventing running indexations on production datasources when user is not hdfs.
I think this task is about a real way to prevent indexations.

See https://wikitech.wikimedia.org/wiki/Analytics/Systems/Presto

Tested with variations of this query:

time curl -L -H'Content-Type: application/json' -XPOST --data-binary '
{
  "queryType": "groupBy",
  "dataSource": {
    "type": "query",
    "query": {
      "queryType": "groupBy",
      "dataSource": "mediawiki_history_reduced_2018_08",
      "granularity": "day",
      "dimensions": [
        "user_text",
         {
            "type": "extraction",
            "dimension": "__time",
            "outputName": "time",
            "extractionFn": {
              "type": "timeFormat",
              "format": "yyyy-MM-ddZZ",
              "timeZone": "UTC",
              "locale": "en-US"
            }
          }
      ],
      "filter": {
        "type": "and",
        "fields": [
          { "type": "selector", "dimension": "event_entity", "value": "revision" },
          { "type": "regex", "dimension": "project", "pattern": ".*\\.wikipedia" }
        ]
      },
      "aggregations": [
        { "type": "longSum", "name": "events", "fieldName": "events" }
      ],
      "postAggregations": [],
      "intervals": [ "2018-01-01T00:00:00/2018-03-01T00:00:00" ]
    }
  },
  "filter": {
    "type": "and",
    "fields": [
      { "type": "bound", "dimension": "events", "lower": "1", "upper": "4" , "ordering": "numeric" }
    ]
  },
  "granularity": "all",
  "dimensions": [ "time" ],
  "limitSpec": { "type": "default", "limit": 1000 },
  "aggregations": [
    { "type": "count", "name": "users" }
  ],
  "postAggregations": [],
  "intervals": [ "2018-08-01T00:00:00/2018-09-01T00:00:00" ],
  "context": {
    "groupByStrategy": "v2"
  }
}
' http://druid1004.eqiad.wmnet:8082/druid/v2/

The closest to total number of bytes we have is the metric you have checked. As explained previously, the negative aspect of it is due to historical changes, and should be negligible after 2005. For before, there is no easy solution as of now.

My understanding of the question is: How come is it possible that the global-sum of net-bytes since the beginning of de.wikipedia.org can be negative?

In T203826#4569399, @Nuria wrote:

The data is not public as there are no public stats of edits per country. Your edit history does not include geographical locations where edits were made

Thank you for the quick response ! We definitely don't want to enable the tools "silently". As for "perception" of privacy breach, this is exactly what we want to avoid. So let's make sure we don't hurt any feelings and don't enable the tool for now.

In T172532#4554063, @elukey wrote:

The good thing is that all the crons become systemd units, that have their own journald configuration (so stdout is saved, and stderr should be as well but not separately) and they can trigger failure/alarms if the return code of the script is non zero. We can also ensure that only one occurrence of the script/cron/unit is executed at the same time (for example, if a long job is still running systemd will not create another occurrence of the timer/script until the other completes).

See https://github.com/wikimedia/restbase/pull/1056

Quick note on that idea: As per the discussion with @fdans and @Nuria yestderday, it would be great to implement the notion of function to be applied to a dataset in a generic way, since other functions are already in mind (moving average for instance).

A comment on something that struck me today: for unique-devices computation using last-access-cookie, we store tables per country and we assume additivity of the metric accross countries, i.e. we do as if unique-devices don't change country. If we do the same asumption for mobile-uniques-devices, it means we can go with 2 jobs only instead of 4: we'd keep the old global values for historical purposes, and store only per-country daily and monthly moving forward. This would mean less jobs, less tables to maintain, less redundancy etc.

Hi @Jonas - A quick comment as per a quick chat with @Addshore on IRC. If you want to implement recommandation based on collaborative filtering for instance, I suggest you go for Spark MLLib (Spark Machine Learning LIBrary). It has all the classical ML algorithms, including collaborative filtering (https://spark.apache.org/docs/latest/mllib-collaborative-filtering.html), and has currently a lot more traction than Mahout (the 'old' way). Let's talk when you want :)

@diego I agree we could use NiFi to copy the files, but it seems too much work in term of system setup for this single use-case.

I think the files dependencies are too complex for oozie here. Many projects !

What I've done manually until now is referenced here: https://gerrit.wikimedia.org/r/c/analytics/refinery/+/409960
I have started a python script to do pretty much the same thing with more flexibility and incremental growth.
Comments/ideas welcome :)