The user part of this task is in testing with the datasource located at hdfs:///user/joal/wmf/data/wmf/mediawiki/history/snaphsot=2019-03 and hdfs:///user/joal/wmf/data/wmf/mediawiki/user_history/snaphsot=2019-03 (along with a bunch of other changes).

Checking improvements in new datsource.

// Current datasource - normally the problem is present in here
spark.read.parquet("/wmf/data/wmf/mediawiki/history/snapshot=2019-03").createOrReplaceTempView("mwh_old")

spark.sql("select event_user_is_bot_by, count(1) as c from mwh group by event_user_is_bot_by").show(20, false)
+--------------------+----------+

+--------------------+----------+

+--------------------+----------+

spark.sql("select caused_by_user_text, count(1) as c from mwph group by caused_by_user_text order by c desc limit 20").show(20, false)
+--------------------------+--------+

+--------------------------+--------+

+--------------------------+--------+

Data is available in new test-datasource located at /user/joal/wmf/data/wmf/mediawiki/user_history:

Confirmation of problem resolution in new test-datasource located at /user/joal/wmf/data/wmf/mediawiki/user_history:

Hi @Neil_P._Quinn_WMF, sorry for the big comment above - Do you mind having a look and confirming this looks ok for you? Many thanks :)

Confirmation of problem resolution in new test-datasource located at /user/joal/wmf/data/wmf/mediawiki/user_history:

// Current datasource - normally the problem is present in here
val odf = spark.read.parquet("/wmf/data/wmf/mediawiki/history/snapshot=2019-03")
val oudf = spark.read.parquet("/wmf/data/wmf/mediawiki/user_history/snapshot=2019-03")

Hi @Neil_P._Quinn_WMF - Test data is available :)
Here is an example of accessing it in scala-spark2:

val history = spark.read.parquet("/user/joal/wmf/data/wmf/mediawiki/history/snapshot=2019-03")
history.where("event_entity = 'revision' and wiki_db = 'enwiki' and revision_tags is not null and size(revision_tags) > 0").select("event_timestamp", "revision_id", "revision_tags").show(100, false)

Hi @nettrom_WMF - I have a test dataset for you that include this data (example in scala-spark2:

val user_history = spark.read.parquet("/user/joal/wmf/data/wmf/mediawiki/user_history/snapshot=2019-03")
user_history..where("caused_by_event_type = 'alterblocks' and wiki_db = 'itwiki' and start_timestamp like '2019-01%' and source_log_params['sitewide'] = 'false' and source_log_params['7::restrictions'] is not null").select("source_log_params").show(100, false)

Actually it'll not finish - We just killed it as we need to restart the cluster (planned maintenance - see https://lists.wikimedia.org/pipermail/engineering/2019-April/000695.html). Sorry for that :( Hopefully you'll still have enough logs.

Hi @bmansurov,
I've been monitoring the current run of the recommender (https://yarn.wikimedia.org/proxy/application_1553764233554_69057/), and I think the approach in term of cluster usage is not sustainable. Particularly, generating the pageviews datasets for the top 50 languages should be done in a single pass, and written in partitioned folders (using partitionBy). Currently, there are 50 runs each reading the whole pageview needed data multiple times - This represents reading 50 *2 *1Tb of data. Doing it in one pass will save a lot of IOs as well as a lot of time :)

@Neil_P._Quinn_WMF : Indeed the validation job failed for expected reasons (a higher than usual group-bot-removal, a dimension against which our validation is not very stable). We restarted the job manually with higher threshold: https://hue.wikimedia.org/oozie/list_oozie_coordinator/0171576-181112144035577-oozie-oozi-C
In this job, the email was configured to be sent to multiple emails, but it seems it failed (I didn't received it either): https://hue.wikimedia.org/oozie/list_oozie_workflow_action/0171577-181112144035577-oozie-oozi-W%40send_success_email/?coordinator_job_id=0171576-181112144035577-oozie-oozi-C.
Let's double check again next month if the email gets sent.

Here is the definition we agred on with @Milimetric: removal of user_is_bot_by_name boolean field and addition of 2 new fields: user_is_bot_by: Array[String] and user_is_bot_by_historical: Array[String]. They can contain 2 different values as of now: group (when the user is in bot group) and name_regex (if the username contains bot). Having an array also allows us for possible new methods (machine learning?).

I did a quick analysis using Spark on user data after @nettrom_WMF comment:

import com.databricks.spark.avro._
// user table data
val u = spark.read.avro("/wmf/data/raw/mediawiki/tables/user/snapshot=2019-03").
        select("wiki_db", "user_id", "user_registration")
// logging table data
val l = spark.read.avro("/wmf/data/raw/mediawiki/tables/logging/snapshot=2019-03").
        where("log_type = 'newusers' and log_user is not null and log_user > 0").
        select("wiki_db", "log_user", "log_timestamp")
// joined data on wiki_db and user_id
val j = u.join(l, u("wiki_db") === l("wiki_db") && u("user_id") === l("log_user")).cache()

Thanks for your comment @nettrom_WMF - I should have explained the plan more thoroughtly.
In the next changes for mediawiki-history, we will add fields for pages and users, ending up in having pageCreationTimestamp and pageFirstEditTimestamp coherent by page_id for each page-event, and similarly for users.
the precise definition of how those values is as follow:

• pageCreationTimestamp - Timestamp of the page-create event in logging table if it exists, null otherwise.
• pageFirstEditTimestamp - Timestamp of the oldest revision associated to the page (by page_id), whether in revision or archive table.
• userCreationTimestamp - oldest from user_registration (in user-table) and user-create event in logging table (if both exist, otherwise the existing one if only one exist, otherwise null).
• userFirstEditTimestamp - Timestamp of the oldest revision associated to the user (by user_id), whether in revision or archive table.

Ping @Neil_P._Quinn_WMF and @nettrom_WMF - I'll move forward with the suggested implementation this end of week to have it tested next week :)

Some queries are computed using hadoop for wikidata (see https://github.com/wikimedia/analytics-refinery/tree/master/oozie/wikidata). If SQL over recent-changes works for, that's great :)

I did a quick analysis over request-patterns: 94% of edits-per-page requests made on April 4th were on a timespan of more than 1 year with mostly daily granularity. I have made the following patch to restrict timespan of per-page requests to 1 year: https://gerrit.wikimedia.org/r/c/analytics/aqs/+/502198

Reading about this - Would delayed data be interesting? This information is accessible in hadoop :)

In T219910#5083908, @Nuria wrote:

@JAllemandou the title of this graph looks like it needs changing?

https://grafana.wikimedia.org/d/000000538/druid?refresh=1m&panelId=43&fullscreen&orgId=1&from=now-1d%2Fd&to=now-1d%2Fd

Shall I send a PR updating rate-limiting in restbase for edits-per-page requests to 10?
https://github.com/wikimedia/restbase/blob/master/v1/metrics.yaml#L1551

I think the problem experienced yesterday could have been prevented by T189623.
Data backup:

• Actual number of queries - https://grafana.wikimedia.org/d/000000538/druid?refresh=1m&panelId=42&fullscreen&orgId=1&from=now-1d%2Fd&to=now-1d%2Fd
• Per-segment number of queries - https://grafana.wikimedia.org/d/000000538/druid?refresh=1m&panelId=43&fullscreen&orgId=1&from=now-1d%2Fd&to=now-1d%2Fd

We prefer the ISO-8601 strings for serialization everywhere.

Most of the complicated things already exist for this to work (equicalent of rsync for HDFS, spark job converting wikidata json dumps to parquet).
I wanted for T216160 to be settled before moving into productionization (having the same date for the various dumps we handle simplifies quite a bit), and it takes time.

Looks like we have a winner:

• page_is_deleted
• revision_is_deleted_by_page_deletion
• revision_deleted_parts
• revision_deleted_parts_are_suppressed

@Neil_P._Quinn_WMF and @Milimetric - Confirmation?

the bot_by_name and bot_by_group terminology is the one we decided to use a while ago - Let's see if others have opinion :)

@Nuria: we set user_is_bot_by_name using a regex: https://github.com/wikimedia/analytics-refinery-source/blob/master/refinery-job/src/main/scala/org/wikimedia/analytics/refinery/job/mediawikihistory/user/UserEventBuilder.scala#L27
This represents users having a name that looks like a bot.
user_is_bot_by_group would be the equivalent of WHERE array_contains(user_groups, 'bot'), meaning the user is flagged as bot in mediawiki groups system.

And after saying it, and saying it again, I'm actually ok to go with revision_is_page_deleted, even I think it is confusing (sorry for the hard-no above...).
It looks like every solution we've encountered is confusing to some extent, so one confusion or another...
My increasing confusion order is:

1. page_is_deleted, revision_is_deleted, revision_hidden_parts and revision_hidden_parts_suppressed
2. page_is_deleted, revision_is_archived, revision_deleted_parts and revision_deleted_parts_suppressed
3. page_is_deleted, revision_deleted_by_page_deletion, revision_deleted_parts and revision_deleted_parts_suppressed
4. page_is_deleted, revision_is_page_deleted, revision_deleted_parts and revision_deleted_parts_suppressed

The validation checks above are done without the patch for page-history refactor (https://gerrit.wikimedia.org/r/c/analytics/refinery/source/+/493390) because it still generates erroneous data.
Not tested: page events are joined by id, explicit page-create events are used.

Data check details:

Only thing remaining here that has not been worked is a field about being a bot or not instead of relying on the groups.
We have user_is_anonymous and user_is_bot_by_name - We could add user_is_bot_by_group.
@Milimetric, good for you?

Yes, there is a patch to fix this in the base data: https://gerrit.wikimedia.org/r/c/analytics/refinery/source/+/491494
Not sure when this will get deployed, and some more checks are needed on wikistats to ensure the change is visible.

Thanks @Neil_P._Quinn_WMF and @matmarex :)
This error is indeed related to wrong filtering of weird block-expirations.
A patch fixing this and more should come in the relatively near future, as there are other issues with user-alterblocks events.

I disagree! I think that the confusing similarity between revision deletion and page deletion is part of reality, so we should not try to hide it in this dataset or introduce our own unique terminology which confuses things further. revision_is_deleted naturally seems like the equivalent of rev_deleted in the source data set, referring to revision deletion. How about revision_is_page-deleted? That clarifies that we're talking about the feature called "page deletion", not about the page having the status "deleted".

So we have a history of 4 days, more or less, at any given time. Not sure if this also translates in what logs are retained and for how long (edit: seems to be 3h for yarn.nodemanager.log.retain-seconds's default).

Thanks Neil for having raised this.
I have found 3 issues:

• One is that there was an inconsistency between user create events start_timestampand the user_registration_timestamp, leading to users not being correctly linked to other events (this is the case for S7w4j9 and SheriffsIsInTown in the given examples above). See this patch: https://gerrit.wikimedia.org/r/#/c/497604/
• The second is that by approximating user registration with its first-edit when registration is undefined, we miss the opportunity to link the user to its real create event happening a lot before the actual first edit (example of Rovack above). We should make an explicit distinction between registration (either defined in user-page or through create-event), and user first-edit timestamp (such a distinction is already coming to pages, it makes sense to add it for users as well).
• Finally the user first-edit date was computed using revisions and not archive, leading to some archive rows not correctly attached to the user if before the first revision ( corrected but not not yet deployed: https://gerrit.wikimedia.org/r/c/analytics/refinery/source/+/491494)

In T144100#5036380, @Nuria wrote:

I think the suggestion of escaping all white-space characters (end-of-lines, spaces, tabs etc) actually makes sense.
I also think we should update the page-title extraction function to better handle the special <script> case shown here.

+1, these changes are to happen on pageviewdefinition java classes rather than HIVE.

We should confirm that white-space chars are not accepted in regular page titles before adding this to the main function. If they are accepted, keeping them in parquet format is probably a good idea, and the filtering should happen in hive. If not accepted, ok to remove them at page-title extraction :)

Reading HIVE-5672, the root bug has been fixed

thanks for working on this @awight :)

Absolutely right @Ottomata - We wanted to use Timestamps type to facilitate applying functions, but it was not feasible because of Parquet, so we went for the string format that allowed those functions.

Hey @GoranSMilovanovic - I don't have a good understanding of what you're after, but having read pairs and contingency table above, maybe this Spark function could be helpful: https://spark.apache.org/docs/2.3.0/api/java/index.html?org/apache/spark/sql/DataFrameStatFunctions.html

Hi Folks, I'll tr to provide more infoon Hive Timestamps and related formats.
There are (at least) two considerations when dealing with Hive datasets: the metastore (schema handler) and the file format (how data is actually stored/retrieved). In classical datastores, there is no such distinction, and therefore Timestamp type being available means usable. In hive, the Timestamp type is available in the metastore and in default-supported file formats, but with the version of Hive we have there is a conversion problem with Parquet (https://issues.apache.org/jira/browse/HIVE-9482). This is the reason why we use String and not Timestamp in Mediawiki-history.

In T216160#5020236, @ArielGlenn wrote:

By Friday I'll have done that; by next Wednesday let's make a decision, barring any huge obstacles.

Nice! I don't see flaws in this approach @Ottomata, but before testing I don't want to say it'll work for all cases (timestamps work for us in hive, but not in parquet for instance).
Now how we move forward depending on the upgrade time is another thing.

@Ottomata : I don't think Hive timestamp-functions will parse ISO by default. All functions doc gives examples without the 'T'.
Also, the above test with hive is also true in Spark.
When we upgrade to newer hive, we will be able to use hive timestamps, meaning we won't have strings anymore, and therefore format will not be that important because transformation will be done once at refine stage. For now, using format without 'T' facilitates usage a lot IMO.

I have an opinion and some information here.
We use string-encoded timestamps in mediawiki-history because our version of hive doesn't support timestamps in parquet (see https://issues.apache.org/jira/browse/HIVE-6384).
The reason we chose the SQL format instead of the ISO one is because Hive UDF work as-is for the former, not the latter:

select dt from event.navigationtiming where year = 2019 and month = 3 and day = 11 and hour = 0 limit 1;
2019-03-11T00:10:45Z

Following up on this: another viable solution to get monthly-coherence between dumps is to force a dump on the 1st of the month ... I'm not sure the idea is better.
@ArielGlenn - How do we proceed to try moving forward (in either direction) ?

IMO this issue is not data-quality as in a problem in the dataset generation, but rather a problem of data-semantics and how we interpret our data. By this I mean the effort on data quality we made this quarter is not related to this issue.

Hi folks - Thanks again for quick answers - My superbad I looked at the wrong. I confirm data is available.
A first toward having it available in mediawiki-history is already in CR (https://gerrit.wikimedia.org/r/c/analytics/refinery/source/+/493012). This will (should) make the logParams available in the page-history table, with all its information.
I'll keep this ticket open to further improve how we handle detailed data.

Hi @nettrom_WMF,
ipblocks_restrictions table is sqooped since this month on the cluster.
However I think that logging table doesn't contain detailed historical information on partial blocks. This will prevent us to rebuild historical (and therefore more interesting) information on partial-blocks.
Can you talk to your team see if more detailed logging could be developped?
Cheers
Joseph

Thanks for the comments and the announcement @Bstorm . For the record, we have experienced problems with hewiki and eswiki, so I think the changes have applied for those two.

Exact analysis ran on 2018-12-06:

val df = spark.read.parquet("/user/joal/wmf/data/wmf/mediawiki/wikidata_parquet/20181001")
val base_rdd = df.select("labels", "descriptions", "aliases").rdd
val strings = base_rdd.flatMap(r => {
  r.getMap[String,String](0).values ++
  r.getMap[String,String](1).values ++
  r.getMap[String,Seq[String]](2).values.flatMap(l => l)
})

Hi @Bstorm , given the merged patches above, I'm assuming comment-data is fully moved toward the comment table, and that we can (must) remove references to the comment-related fields in the data we gather from labsdb. Can you confirm? Many thanks :)