I need to provide a full spec so @mpopov can write it.

It's become clear that there's interest among the Product Analytics team at least, and it doesn't seem worth looking for other users at this point while we're still ironing out the kinks.

I sat down with @Tbayer and it looks like it's now working for him!

Don't we have historic data already? Or is this specific to a particular metric, like pageviews, which only seems to go back to July 2015?

In T172410#4803991, @elukey wrote:

To follow up what I wrote (after a chat with the data persistence team):

• the proposal in T210478#4794536 would move sX sections (so the database groupings listed in s1.dblist, s2.dblist etc..) to their own mysql instance on an assigned dbstore node. For example, all wikis in S5 will be available (i.e. replicated) to a mysql instance on dbstore1003 (with an assigned port that we don't know yet). So joins between schemas belonging to different sX sections will not be possible anymore (we already knew this).
• the staging database will likely be assigned to a separate mysql instance, so people will be able to keep using its data. It will still be possible to create tables etc.., but importing data from various wiki databases will need some extra work (dump the data, import it, etc..).
  
  Would the above points be ok for everybody? Any special need or use case that is not taken into consideration?

This data is now flowing into Hive:

select
    to_date(dt) as date,
    count(*) as events
from editattemptstep
where year = 2018 and month=12
group by to_date(dt)

In P5608#46554, @Volans wrote:

In P5608#46552, @Neil_P._Quinn_WMF wrote:

@Volans should this really be world-editable? 🤔

Of course not, I've fixed it. Thanks for spotting this, I thought I've made it just readable by anyone.

@Volans should this really be world-editable? 🤔

In T207803#4789807, @Catrope wrote:

However, you're right that event_init_timing is always null, and it wasn't like that in the old schema.

Oh, I see, it's because his username was in the task description.

Damn it!

Sorry @phuedx, I don't know why you keep getting resubscribed.

In T202594#4776550, @Tbayer wrote:

Testing again after Neil's update:

It now detects the outdated matplotlib and appears to try resolve it using "kiwisolver", but unsuccessfully, resulting in the same error message for the import:

@kzimmerman also pulling into Next Up as a blocker for November's movement metrics.

@kzimmerman I've pulled this into Next Up since we need to do this before calculating November's movement metrics (T210040).

@JCuriel, this is done. The results are in the table below; let me know if you notice any issues.

In T206883#4757850, @JAllemandou wrote:

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).

@kzimmerman this is the task that I used as an example in our meeting today. As discussed, it's quite small and has clear value, so I'm auto-accepting it :)

@nettrom_WMF, should we plan to pair on this?

The last time I did these calculations, I wrapped them up in a notebook, so they'll be very easy to rerun.

I fixed this in this commit!

@nettrom_WMF mentioned two current known issues with the data:

• client side mobile events were not submitted until last week's train (the one ending on 15 November)
• there's a continuing issue with mobile init events not being submitted (fix planned for next week).

In T203498#4745341, @elukey wrote:

Now I am wondering how much stability we'd sacrifice if we upgrade to Hive 2.11 like described above. It would be extremely useful to some people, but I fear that it might cause major headaches to all of us in the long term. Writing this note just to say that we didn't forget about this task, but only that it might not as easy as we thought.

In T209536#4753050, @JAllemandou wrote:

In T209536#4752726, @Tbayer wrote:

Is this related to T206279 ?

I don't think the root-cause is the same (one problem happens on HiveServer2, the other on Hive CLI). The solution however is the same, as the issues are happening on the same portion of process:
Issues happen when a MapRedLocalTask is executed. Those tasks are an optimization over joins when data size is small enough. When hive.auto.convert.join=true hive automatically converts joins to map-join when it thinks it's good.
By setting hive.auto.convert.join=false by default, we'll lose some small optimization (because data is small).

In T206883#4753059, @JAllemandou wrote:

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.

Thanks for working on this, @JAllemandou! I've just started suffering this on a lot of queries, including ones that previously worked fine. The error message is "OperationalError: Error while processing statement: FAILED: Execution Error, return code 134 from org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask".

Sorry to weigh in so late, but why can't we simply copy the event_user_text_historical to event_user_text for IP editors at the end of the reconstruction process? It doesn't seem like it would be hard to implement, and it's annoying and hard to learn that the way to get the canonical name for any user is not event_user_text like you'd expect, but rather coalesce(event_user_text, event_user_text_historical).

In T209503#4748908, @mforns wrote:

@Tbayer

Just to double-check: The information in the documentation that "Sanitization happens right after events are generated (with a couple hours lag)" is still current, right? In that case I don't think this will be a concern (although we will need to update some queries - CCing @Groceryheist regarding ReadingDepth).

Yes, it still happens this way. We are considering to shift if back, but will let you know if we do.

I've now uploaded the survey lists to Qualtrics, so we can call this done.

The primary desire here was a gated home for defining things like Global North/Global South, and that exists now that I've created the wikimedia-reasearch/canonical-data repo.

Now that the data is easily available in Hive, I think this is done!

In T206898#4738499, @Tbayer wrote:

What approach do you recommend for joining a CSV in Hive? If it involves creating a temporary Hive table, wouldn't it make sense to instead upload the CSV as a non-temporary table already? (similar to e.g. wmf.domain_abbrev_map or tbayer.country_name_vs_code)

Actually, I'll go ahead and upload the CSV file into the Data Lake so we can join it to the other tables. That actually may be just as good as writing a UDF—you don't have to add a jar and create a function at the start of every query, at least.

The most challenging part of this is coming up with human-readable project names, and I've actually already done that as part of the wiki segmentation work. I just started work wrapping that up in a slightly more general form so it can go in the canonical-data repo, although it's not high priority so I don't know when I'll finish.

@Tbayer, I've created a CSV file with country names, ISO codes, Global North/South classification, and MaxMind continents, tracked in a new wikimedia-research/canonical-data repo. It contains all the countries which appear in projectview_hourly, and I've carefully checked it to make sure the Global North/South classifications match the ones at meta:List of countries by regional classification.

In T206898#4735113, @Neil_P._Quinn_WMF wrote:

@Tbayer,

The list of Global North countries I've been using is:

(
    "AD", "AL", "AT", "AX", "BA", "BE", "BG", "CH", "CY", "CZ",
    "DE", "DK", "EE", "ES", "FI", "FO", "FR", "FX", "GB", "GG",
    "GI", "GL", "GR", "HR", "HU", "IE", "IL", "IM", "IS", "IT",
    "JE", "LI", "LU", "LV", "MC", "MD", "ME", "MK", "MT", "NL",
    "NO", "PL", "PT", "RO", "RS", "RU", "SE", "SI", "SJ", "SK",
    "SM", "TR", "VA", "AU", "CA", "HK", "MO", "NZ", "JP", "SG",
    "KR", "TW", "US"
)

To check whether this was accurate, I pulled all the countries present in a month of pageviews data that didn't match this list or "--". That's in this spreadsheet and it looks good to me.

The list of Global North countries I've been using is:

(
    "AD", "AL", "AT", "AX", "BA", "BE", "BG", "CH", "CY", "CZ",
    "DE", "DK", "EE", "ES", "FI", "FO", "FR", "FX", "GB", "GG",
    "GI", "GL", "GR", "HR", "HU", "IE", "IL", "IM", "IS", "IT",
    "JE", "LI", "LU", "LV", "MC", "MD", "ME", "MK", "MT", "NL",
    "NO", "PL", "PT", "RO", "RS", "RU", "SE", "SI", "SJ", "SK",
    "SM", "TR", "VA", "AU", "CA", "HK", "MO", "NZ", "JP", "SG",
    "KR", "TW", "US"
)

In T206898#4718381, @Tbayer wrote:

+1. And to clarify, we haven't yet decided whether we should report all three of these regions separately in the board deck and in https://www.mediawiki.org/wiki/Wikimedia_Audiences (it would amount to adding two new sections, as only GS is listed currently). But in any case we resolved to remove the "unknown" country part from the existing GS metrics.

@Tbayer and I discussed this yesterday and came to the following conclusions:

• Unknown countries account for roughly:
  • 0.3% of pageviews
  • 0.9% of editors (technically, of (wiki, country, editor) entities)
  • 27% of edits (my estimate in the description was too high)
• We should investigate the small group of editors that are producing all these unknown-country edits
• Starting with October board metrics (T206895, etc.), we will treat unknown countries as a third region alongside the Global North and the Global South. I will produce some shared code to help with this.