Hi @nettrom_WMF ,
Indeed the mediawiki_history table doesn't contain historical blocks (or actually group, but it's not relevant).
The approach taken when rebuilding user-history through logging table was to concentrate on registered-users, as IPs are by nature multi-user, changing etc.
The use-case you describe here makes a lot of sense, and while we have curretnly too many things on the plate, I'll talk with the team to prioritize adding blocks for IPs.

OMG ! Done ... Sorry for having skipped that one :S

Thanks for pointing this out @Tgr. This is a known issue due indeed to the comment-storage change.
Comments are unavialable for snapshots 2018-10 and 2018-11 and we are currently devising a solution so that 2018-12 should have them.

Something to notice is that the problem with the rows listed above occurs all along the hour but only shows-up at the end-border of the calendar-hour since this is the time we finalize the batch. The following queries count how many rows have
big discrepancies in sequence-numbers (next sequence being more than 100 bigger instead of 1) when grouped per minute batches (instead of hour-batches), for the same hour as above:

spark.sql("""
SELECT COUNT(1) FROM (
SELECT
  sequence,
  LAG(sequence) OVER minute_hostname_window AS prev_seq
FROM wmf_raw.webrequest
WHERE webrequest_source = 'upload'
  AND year = 2018
  AND month = 12
  AND day = 1
  AND hour = 14
  AND dt != '-'
-- Catch only requests having a big diff in seq
HAVING abs(sequence - prev_seq) > 100
WINDOW minute_hostname_window AS (PARTITION BY substr(dt, 15, 2), hostname ORDER by hour, sequence)
) t
""").show(1000)

Problematic requests for end-of-hour upload 2018-12-01T14:

spark.sql("""
SELECT
  hour,
  hostname,
  sequence,
  LAG(sequence) OVER hour_hostname_window AS prev_seq,
  (sequence - LAG(sequence) OVER hour_hostname_window) AS diff_seq,
  dt,
  LAG(dt) OVER hour_hostname_window AS prev_dt
FROM wmf_raw.webrequest
WHERE webrequest_source = 'upload'
  AND year = 2018
  AND month = 12
  AND day = 1
  AND hour = 14
  AND dt != '-'
HAVING (sequence - prev_seq > 10)
WINDOW hour_hostname_window AS (PARTITION BY hour, hostname ORDER by hour, sequence)
""").show(1000)

Operational problem solved: the refinement job has been restarted with higher error-acceptance rates - Blocked jobs have caught up.
Some more analysis to come to try root-causing this.

TL;DR: 83 requests in the failed hour are responsible for the failure. Their sequence-number is fairly bigger than the ones in the current hour, while their timestamp is still assigned to the hour (this is not the dt = '-' case).

Info backfilled since beggining of time: https://grafana.wikimedia.org/dashboard/db/wikidata-co-editors?orgId=1&from=now-8y&to=now
Will keep an eye on next month run.

Thanks for raising the issue. This is very bizarre.
The job for october was showing successful in our side. I reran it, and data showed up :(
I have the feeling this is not the first time this happens, something must be wrong somewhere.
I'am also going to run backfilling info.

This should be solved by https://gerrit.wikimedia.org/r/c/operations/puppet/cdh/+/474907

Another comment about folders that I hadn't thought before having read your update in the description: I actually think that the chosen is not the most efficient.
In term of data-retrieval, using /wmf/data/ores/score/wiki=enwiki/model=editquality/model_version=2.1/ or /wmf/data/ores/score/model=editquality/model_version=2.1/wiki=enwiki/ is very similar.
However it is different at deletion step: the latter is a lot easier, as it involves only a single delete for the whole model.

For reference, here is the request sent to druid for realtime ingestion:

curl -L -X POST -H 'Content-Type: application/json' -d '{
  "type": "kafka",
  "dataSchema": {
    "dataSource": "test_kafka_event_centralnoticeimpression",
    "parser": {
      "type": "string",
      "parseSpec": {
        "format": "json",
        "flattenSpec": {
          "useFieldDiscovery": false,
          "fields": [
            "dt",
            { "type": "path", "name": "event_anonymous", "expr": "$.event.anonymous" },
            { "type": "path", "name": "event_banner", "expr": "$.event.banner" },
            { "type": "path", "name": "event_bannerCategory", "expr": "$.event.bannerCategory" },
            { "type": "path", "name": "event_bucket", "expr": "$.event.bucket" },
            { "type": "path", "name": "event_campaign", "expr": "$.event.campaign" },
            { "type": "path", "name": "event_campaignCategory", "expr": "$.event.campaignCategory" },
            { "type": "path", "name": "event_campaignCategoryUsesLegacy", "expr": "$.event.campaignCategoryUsesLegacy" },
            { "type": "path", "name": "event_country", "expr": "$.event.country" },
            { "type": "path", "name": "event_db", "expr": "$.event.db" },
            { "type": "path", "name": "event_device", "expr": "$.event.device" },
            { "type": "path", "name": "event_impressionEventSampleRate", "expr": "$.event.impressionEventSampleRate" },
            { "type": "path", "name": "event_project", "expr": "$.event.project" },
            { "type": "path", "name": "event_recordImpressionSampleRate", "expr": "$.event.recordImpressionSampleRate" },
            { "type": "path", "name": "event_region", "expr": "$.event.region" },
            { "type": "path", "name": "event_result", "expr": "$.event.result" },
            { "type": "path", "name": "event_status", "expr": "$.event.status" },
            { "type": "path", "name": "event_statusCode", "expr": "$.event.statusCode" },
            { "type": "path", "name": "event_uselang", "expr": "$.event.uselang" },
            "recvFrom",
            { "type": "path", "name": "ua_browser_family", "expr": "$.userAgent.browser_family" },
            { "type": "path", "name": "ua_browser_major", "expr": "$.userAgent.browser_major" },
            { "type": "path", "name": "ua_device_family", "expr": "$.userAgent.device_family" },
            { "type": "path", "name": "ua_is_bot", "expr": "$.userAgent.is_bot" },
            { "type": "path", "name": "ua_is_mediawiki", "expr": "$.userAgent.is_mediawiki" },
            { "type": "path", "name": "ua_os_family", "expr": "$.userAgent.os_family" },
            { "type": "path", "name": "ua_os_major", "expr": "$.userAgent.os_major" },
            { "type": "path", "name": "ua_wmf_app_version", "expr": "$.userAgent.wmf_app_version" },
            "webHost",
            "wiki"
          ]
        },
        "timestampSpec": {
          "column": "dt",
          "format": "auto"
        },
        "dimensionsSpec": {
          "dimensions": [
            "event_anonymous",
            "event_banner",
            "event_bannerCategory",
            "event_bucket",
            "event_campaign",
            "event_campaignCategory",
            "event_campaignCategoryUsesLegacy",
            "event_country",
            "event_db",
            "event_device",
            "event_impressionEventSampleRate",
            "event_project",
            "event_recordImpressionSampleRate",
            "event_region",
            "event_result",
            "event_status",
            "event_statusCode",
            "event_uselang",
            "recvFrom",
            "ua_browser_family",
            "ua_browser_major",
            "ua_device_family",
            "ua_is_bot",
            "ua_is_mediawiki",
            "ua_os_family",
            "ua_os_major",
            "ua_wmf_app_version",
            "webHost",
            "wiki"
          ]
        }
      }
    },
    "transformSpec": {
      "transforms": [
        {
          "type": "expression",
          "name": "event_inverseRecordImpressionSampleRate",
          "expression": "1 / event_recordImpressionSampleRate" }
      ]
    },
    "metricsSpec": [
      {
        "name": "event_count",
        "type": "count"
      },
      {
        "name": "event_normalized_count",
        "type": "doubleSum",
        "fieldName": "event_inverseRecordImpressionSampleRate"
      }
    ],
    "granularitySpec": {
      "type": "uniform",
      "segmentGranularity": "HOUR",
      "queryGranularity": "SECOND"
    }
  },
  "tuningConfig": {
    "type": "kafka",
    "maxRowsPerSegment": 5000000
  },
  "ioConfig": {
    "topic": "eventlogging_CentralNoticeImpression",
    "consumerProperties": {
      "bootstrap.servers": "kafka-jumbo1001.eqiad.wmnet:9092"
    },
    "taskCount": 1,
    "replicas": 3,
    "taskDuration": "PT10M"
  }
}' http://druid1001.eqiad.wmnet:8090/druid/indexer/v1/supervisor

I have launched a realtime job indexing values flowing in kafka. Data can be seen here (please notice the event normalized count metric :) :

Will the order of partitions make a difference? For example, if consumers are more likely to get multiple models of scores for a single wiki, vs. multiple wikis for a single model, would we put the wiki partition before model partition in the HDFS path, or would this have no effect?

I support the idea of using model name and version as partitions. Wiki_db would possibly be another good fit if requests will most often be on singular projects. Finally, if we partition on model and name, time is not needed I assume.

Hi @GoranSMilovanovic, the code we use to generate monthly data is here: https://github.com/wikimedia/analytics-refinery-source/blob/master/refinery-job/src/main/scala/org/wikimedia/analytics/refinery/job/ClickstreamBuilder.scala
As per the clickstream database in Hive, it's not used anymore, it's a left-over from Ellerys time.

@Nuria : Do we know why those events don't get inserted into MySQL?

We could add an event property with the value calculated per event--that is, the 1/sample rate. I imagine that float value could be put directly in Druid, which would then just have to sum all the values in the results of each query. Can Druid/Turnilo do that? That way, perhaps we could still have the metric with the new tool?

The solution that you proposed though might work - if 1/sample-rate was available in the event itself we could ingest it to druid as measure (and hence set correctly as float/double) and possibly plot it to Turnilo without too many problems. @mforns what do you think?

Indeed in hadoop there is no such thing as 'in place'. The way to go could be to use model version as a partition-key. You'd backfill a new version, keeping the old ones (and therefore not breaking queries on old ones). Then the decision is on how many versions to keep, and whether to keep them update or not.

In T209731#4754979, @Nuria wrote:

It is worth looking at already existing event data, if we want to reuse the logic that reads events and persists those to hive partitions cannot be schema dependent

When data gets stored in Hadoop, it is easy to supply pageviews-like dumps files.
About how to compute the scores, hitting the ORES API is probably the shortest solution in term of schedule, next steps might be to try to reproduce ORES scoring in PySpark. I already started some POC of that, but didn't move to the core of the thing: feature-extraction from mediawiki-text instead of mediawiki-api.

In T209536#4754461, @Neil_P._Quinn_WMF wrote:

Hmm, the big uptick in errors I described above happened using HiveServer2. Do you mean that this happens on both, whereas T206279 happens on HiveServer2 only?

I hear your point and it makes a lot of sense. I think our views differ in the notion of current name. In my world a current name is associated to events only when we're sure those events have been made by the same person/account. In my world the event_user_text field references single users. I however understand that taken from a purely name-changing perspective, an IP has the same value before and now. My concern lay in misrepresentations of IP changes in time: If you use an IP as current-name for an edit, you might be tempted to consider other edits made by that IPs as belonging to the same user - Which is false (but true in the case of non-anonymous edits).

Hi @Neil_P._Quinn_WMF ,
While I understand the usage frustration, keeping the IPs in event_user_text_historical is for me a matter of data correctness.
Doing it represents nothing in term of code change, but I'd rather not do it to keep the semantics of event_user_text and event_user_text_historical valid and similar for anonymous and non-anonymous edits.
Happy to continue the discussion and to get other opnions weighting :)

In T209536#4752726, @Tbayer wrote:

Is this related to T206279 ?

Not sure when we'll be able to do that. However there is more recent dump available: /user/joal/wmf/data/wmf/mediawiki/wikidata_parquet/20181001 :)

Did some analysis in term of data size and storage:

• Our Cassandra instances (2 per host) each have 2.9Tb usable space.
• We currently use ~720Gb per instance (this accounts for all keyspaces, replication included).
• 98% of those 720Gb is used for pageview-per-article daily data

Ping @elukey, @Ottomata : I think we should apply hive.auto.convert.join = false in hive-site.xml so that map-side joins are never done automatically.

Thanks @chasemp for raising the point. We are aware of the 2 steps for sanitization.

Thanks again a lot @Anomie, @daniel, @Bstorm for having chimed in, your questions and suggestions have helped us move forward.

• Short term solution: We have tested exporting data from the logging_compat view and it's fast enough to get us by for now. A lot faster than getting the comment view on its own, or getting the joint logging and comment views. But we still can't export the revision table using the revision_compat view, it's too slow.
• Long term solution : Having invested more time into understanding how the sanitization of data happens for labs, we think that it would be a better solution to use the analytics-replica to get the data from MariaDB, and then sanitize it in hadoop. This will be a long project, and I will reuse the parent task to coordinate. Ideally, we'll just use your maintain-views.yaml logic directly, so we stay in sync with any changes made on currently existing views sanitization.

@daniel : We were using sanitized comment view (named comment), not the compat one (named revision_compat).
We tried two things:

• export the comment view (select comment_id, comment_text from comment) - but this is very slow due to the way the comment view is built.
• export a manually built equivalent of the logging_compat view - we were doing the join in the query, and it was also very slow as it took more than a day for the logging table.

In T209031#4741603, @Krenair wrote:

In T209031#4740771, @JAllemandou wrote:

• Access to underlying tables - We could query the underlying tables, and that would bypass any performance problems we have with the views. We would duplicate the sanitizing logic from the views, and maintain it to be always the same as it is in cloud db. This would require special permissions to the cloud db.

At that point you wouldn't be using the provided views so there'd be no point connecting out to the cloud DB replicas. And I don't think you'd get special permissions.

Ping @Nuria - Can we close this parent task?

ping @Bstorm for the above comment, as she's the one having setup views.
Thanks

@Ottomata , @ArielGlenn - I'm ok with copying the dumps from a stat machine.
Let's see what @elukey thinks of it

Thanks @Anomie. We (analytics team) also had thought of a third potential solution. I list the 3 solutions below by increasing level of complexity (IMO),

In T202489#4740221, @ArielGlenn wrote:

Excuse me for butting in at this late date but these files are already available from labstore1006,7 to labs instances and on stats100? (I forget which one now). Do you need them to be available somewhere else?

After a chat in #wikimedia-cloud chan on IRC, here is what we found with @Krenair :
The comment view is defined through linking to all parent tables (the tables from which comments come), enforcing existence and non-masking of the comment (see https://gerrit.wikimedia.org/r/plugins/gitiles/operations/puppet/+/refs/heads/production/modules/profile/templates/labs/db/views/maintain-views.yaml#291).
This means that getting min and max ids of that table require a full table scan with 9 joins for each rows. This explains the time (20sec for a very small wiki, less than 170k revisions).

To keep archive happy: Failed refinement hours were 13->19 (included) on webrequest_source = 'upload', 2018-11-05.
I refined them manually 2018-11-06 and marked them as done manually as well.

The only reason I can think of for this issue to happen would be that you and someone else have a hive query writing parquet-logs at the same time.
I'd love to be able to provide a better naming for the log-files (embedding username at least), but java-logging configuration doesn't allow that easily by default.
Maybe there are ways to do differently?

TL;DR: I think 2/3Tb per host is just enough for a start, but might quickly become too small.
Details:
In term of storage, Hadoop has a default replication factor of 3, giving you (actual space / 3) usage space; so roughly 5Tb.
As @Ottomata pointed, the dataset we want to provide is less than 1Tb (currently 770Gb, growing). We will probably keep some of them (let's assume 3/4, taking into account the copying time period), and we will need enough space on the machine used as an entry-point to the dataset to have it full (copy locally from dumps, then to HDFS - Might be doable streaming).
Lastly, we also need to consider space for logs. Hadoop generates a big bunch of logs, and they have proven very usefull when debugging. I don't enough yet about presto, but we should keep some space for this.

@Nuria: This is related to the fix we deployed to try to prevent the logging lines (https://gerrit.wikimedia.org/r/c/operations/puppet/cdh/+/469499)
Can you precise which machine you were using when you got those logs?

Here are my findings:

• The warning message happens for 2 schemas of AQS: pageviews.per.article.flat and top.pageviews. The difference between schema definitions are on the version field. The one store in database is 2 (verified in cassandra), the one coming from AQS definition is 3 (verified in code).
• In this commit we have changed the version number of 3 schemas: pageviews.per.article.flat, top.pageviews and pageviews.per.project.v2. The 2 schemas raising warning messages are present, and there is the pageviews.per.project.v2 that doesn't raise warnings. Tis due to the database containing 3 for that schema version, which matches the AQS defined version number.
• Looking at the restbase-mod-table-cassandra git repo, I found that schema migration are driven by changes in code-defined-configuration (as expected, a new schema line is stored in the cassandra meta table for the targetted keyspace), and that this new row is added only if a real schema change happens (see this function). Since we updated the version number without having actually changed the schema, no DB updates were made, hence throwing warnings (for more than a year :( Sadness).

If we want an email to be sent once data is available on the cluster, no need to create a new oozie job. Adding a step sending the email in the currently existing job is easier I think.

It works in that it is queryable, but for small data only (times out otherwise). The thing I have not tested is to build charts out of data.

In T206279#4695030, @Ottomata wrote:

Reopening because we have an idea:

We will set SET hive.auto.convert.join=false; in hive-server2 only, (not for hive CLI clients). This should make the default for clients that use hive-server (like notebooks) to have this turned off.