[07:09:04] joal: o/ [07:09:35] so you if you have patience this morning I'd ask you some info about reindexing https://gerrit.wikimedia.org/r/#/c/433597/ :) [07:09:48] if the code looks good, what I'd do is: [07:10:04] 1) disable the coordinators that are currently running [07:10:26] 2) copy the oozie dir in hdfs to /user/elukey and then change the files [07:10:33] 3) run my coordinator on all the data [07:11:09] is it vaguely right? [07:13:55] (03PS4) 10Elukey: Index some fields from isp_data and geocoded_data in Druid's webrequest [analytics/refinery] - 10https://gerrit.wikimedia.org/r/433597 (https://phabricator.wikimedia.org/T194055) [07:25:20] mmmm I was rewiewing Druid's metrics and I added to our grafana dashboard the under replicated segments [07:25:23] https://grafana.wikimedia.org/dashboard/db/druid?refresh=1m&orgId=1&var-cluster=druid_analytics&var-druid_datasource=All&from=now-7d&to=now&var-datasource=eqiad%20prometheus%2Fanalytics&panelId=50&fullscreen [07:38:51] I am not really understanding from the coordinator UI what is the replication factor.. do we use 2? [07:43:20] ah ok http://localhost:8081/druid/coordinator/v1/loadstatus?full confirms that for webrequest 204 segments are left to load [07:43:27] (?full includes replication) [07:48:11] but in the coordinator's log I don't see any trace of issue [07:49:10] load queues are empty for all the nodes [07:58:13] ah wait I can see from http://localhost:8081/druid/coordinator/v1/rules that replication is 1 for webrequest [07:58:44] so if availability is 100%, why a metric is emitted for under replicated segments? [08:01:19] hi elukey [08:01:40] elukey: how do we start ? Druid replication factor or reindexation? :) [08:01:51] joal: o/ [08:01:54] I'd say replication [08:02:13] ok [08:02:19] I am checking http://localhost:8081/#/datasources/webrequest (druid1003) [08:02:34] and something is odd.. loadByPeriod [08:02:34] P7D (7 days) [08:02:35] 1 in _default_tier [08:02:40] loadForever (default rule) [08:02:40] 2 in _default_tier [08:04:02] elukey: second one is drop [08:05:02] sure, but I can see three from the UI [08:05:17] loadByPeriod, Drop, loadForever (default) [08:05:24] first one is replication 1, the last 2 [08:05:41] elukey: I can only see 2 rules for webrequest [08:06:18] joal: I am looking at the box next to the one showing "2 Rules etc.." [08:06:33] the one above the green graph [08:06:36] showing the segments [08:07:10] (might be nothing but looks weird) [08:07:13] elukey: Ahh - load-forever is the default rule - applied first for any datasource [08:08:53] joal: yeah I imagined something similar, I am wondering if Druid gets confused if the default rule has replication w and the more specific one 1 [08:08:58] err s/w/2 [08:09:27] I don't think so --> Load everything-rep2 - Drop everything (no rep) - Load 7d-rep1 [08:09:34] ---> rep 1 foir the 7 days [08:10:59] ah wait the rules are applied all together, and the result is the final one? [08:11:10] (logically I mean) [08:11:34] elukey: rule are applied right to left (when looking at the block), or bottom to top (when looking at the list) [08:12:00] very intuitive [08:12:27] elukey: looks like we have an issue with webrequest load :( [08:13:04] it seems more an inconsistency with what Druid thinks about the webrequest datasource [08:13:26] if segments loaded are 100% and replication is 1, how can we have under replicated segments? [08:13:30] it doesn't make sense [08:13:47] and afaics it is the only datasource with replication 1 [08:14:29] and it coincides precisely with the 0.11 rollout [08:14:42] elukey: hm [08:15:09] joal: I bet that if we set replication 2 the metric will drop to zero [08:15:19] elukey: easy to try [08:15:44] it seems also a good thing given that I'd need to reimage those hosts one by one :P [08:16:00] how can I increase the replication? Via the UI? [08:16:00] ok [08:16:08] yes - Modify the rule [08:16:21] !log rerun webrequest-load-wf-misc-2018-5-24-6 [08:16:22] Logged the message at https://www.mediawiki.org/wiki/Analytics/Server_Admin_Log [08:17:46] !log increase webrequest replication to 2 in druid analytics (via coordinator's UI) [08:17:46] Logged the message at https://www.mediawiki.org/wiki/Analytics/Server_Admin_Log [08:18:25] * elukey tails the coordinator's log [08:24:25] elukey: The change is visible in coord UI [08:26:42] joal: it seems that it is working [08:26:52] great [08:26:55] http://localhost:8081/druid/coordinator/v1/loadstatus?full shows only 68 segments under replicated [08:27:05] elukey: maybe druid doesn't like rep-factor < 2 [08:27:11] it seems so yes [08:27:53] after reading how replication works (especially if a node goes down) I think it makes sense to use at least 2 [08:27:56] for all the datasources [08:28:16] works for me elukey [08:28:20] super :) [08:28:28] metric recovered, all good [08:28:32] \o/ [08:29:02] I am actually able to talk with you about druid segments! Achievement unlocked for this quarter [08:29:05] :D [08:29:36] do you have a bit more patience to level me up about indexation? [08:30:06] let's go elukey - nothing to do with patience :) [08:30:42] elukey: I don't know if the achievement is on your side or mine ;) [08:31:25] joal: well it is also yours since you always listen to me and correct me when needed :) [08:34:04] So about indexation [08:34:45] elukey: webrequest indexation job is small (it works on 1/128, with bucketized data, therefore read 1/64th of data) --> You can easily reindex the last week [08:36:33] ah yes right it is only a week that we use [08:36:39] correct [08:37:10] but I guess I'd need to stop at least the hourly indexation right? [08:37:14] to avoid inconsistencies [08:37:39] elukey: correct - We want to stop the current job, and start a new one [08:37:47] And the new one starts 1 week ago [08:37:59] exactly yes [08:39:08] on the practical side, I'd need to cp the oozie files under /user/elukey, change files and then start a oozie coordinator by myself? [08:39:12] elukey: Thing I'm not sure of -- Your job has been tested on a fake datasource? [08:39:30] nope didn't test it, this is what I was asking yesterday [08:39:36] I am not sure how to do it [08:39:41] Ah, ok :0 [08:39:43] ) [08:40:13] I think best way is to test the thing on a test datasource that we'll delete after [08:40:37] something like webrequest-elukey [08:41:11] You run oozie on let's say 2 or 3 hours, from your code, on a test datasource (I'd say test_elukey_webrequest - I like test in those names :) [08:41:28] Like that, we know it works, we deploy, and then we restart etc from prod coed [08:42:20] elukey: This feels so true https://twitter.com/SarahCAndersen/status/999302709239922689 [08:44:03] ahahaha [08:49:41] 10Analytics-Kanban, 10MW-1.31-release-notes (WMF-deploy-2018-02-27 (1.31.0-wmf.23)), 10Patch-For-Review: Record and aggregate page previews - https://phabricator.wikimedia.org/T186728#4227800 (10mforns) >> Please understand, though, that we Analytics are trying our best on our side as well. And that in the v... [09:15:01] joal: is it ok if I just launch a workflow rather than the coordinator? [09:15:14] elukey: ok for me [09:15:46] also I changed only the druid datasource to "test_elukey_webrequest", the tmp hive tables looks fine [09:55:39] joal: druid_loader.py says Error Message variable [target_datasource] cannot be resolved [09:55:50] do I need to do anything to use test_elukey_webrequest? [11:24:51] made it work!! (I had to pass another -D value) [11:26:40] the only thing that is still not working is the as_number [11:26:47] that seems not be present as dimension [11:26:49] mmmm [11:28:31] ahhhh snap I put the wrong map field [11:28:33] * elukey cries [11:31:23] (03PS5) 10Elukey: Index some fields from isp_data and geocoded_data in Druid's webrequest [analytics/refinery] - 10https://gerrit.wikimedia.org/r/433597 (https://phabricator.wikimedia.org/T194055) [11:31:31] there you go, s/as_number/autonomous_system_number [11:36:44] restarted the oozie coordinator (I went for it since it was easier) [11:42:04] elukey: I'm interested in the `Error Message variable [target_datasource]` error and the -D solution you used [11:44:34] joal: ah it was a stupid thing - there was a ${target_datasource} variable to fill :) [11:45:30] elukey: normally this field is filled by default - was it required by your coord? [11:46:14] I have probably not launched it correctly (or didn't do what was expected), I'll forward the cli command that I used later on :) [11:46:22] need to go now for late lunch + errand! [11:46:29] Bye elukey - later ! [12:29:19]  [12:39:28] 10Analytics-Legal, 10WMF-Legal, 10Wikidata: Solve legal uncertainty of Wikidata - https://phabricator.wikimedia.org/T193728#4228561 (10Micru) >>! In T193728#4223211, @Cirdan wrote: > CC licences are built within the framework of current copyright law. That seems to be the main issue, we are thinking about c... [13:33:52] 10Analytics, 10Analytics-Kanban: Archive old geowiki data (editors per country) and make it easily available at WMF - https://phabricator.wikimedia.org/T190856#4228622 (10fdans) Sooo a few decisions to make here in terms of naming/placement. They're these: - Location in HDFS (I suggest /wmf/data/archive/geowi... [14:02:03] so the as_number dimension doesn't pop up in test_elukey_webrequest [14:03:28] wondering why [14:04:43] * elukey hears Joseph saying "Did you check that ALL your files in HDFS are ok?" [14:05:43] and I have to say "probably not!" [14:11:16] so /mnt/hdfs on stat1004 is really slow [14:11:46] same thing on stat1005 [14:13:51] ah yeah they are overloaded [14:19:58] it seems fuse_dfs the issue, weird [14:38:48] hey fdans / nuria_ / mforns: for d3 annotation libraries, it looks like there's one big one: http://d3-annotation.susielu.com/. I will use that unless someone has some strong opinions against it. Examples are here: http://d3-annotation.susielu.com/#examples [14:43:17] PROBLEM - Check if the Hadoop HDFS Fuse mountpoint is readable on stat1005 is CRITICAL: Return code of 255 is out of bounds [14:45:15] the hdfs mountpoint is really slow, tried to umount/remount it but not much luck [14:51:40] (03PS2) 10Milimetric: Ignore search preferences [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/433584 [14:51:42] (03PS2) 10Milimetric: Expand setState to be more explicit [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/433585 [14:51:44] (03PS3) 10Milimetric: Move detail state into store [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/433586 [14:51:46] (03PS5) 10Milimetric: Reflect detail state in the URL and back [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/434500 (https://phabricator.wikimedia.org/T179444) [14:54:52] (03CR) 10jerkins-bot: [V: 04-1] Expand setState to be more explicit [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/433585 (owner: 10Milimetric) [14:56:46] that's a very useful error page there, jenkins, nice job... :/ [14:59:00] milimetric: can I ask you a favor if you have time? I'd need to check the dimensions of a druid datasource (test_elukey_webrequest), because 'as_number' doesn't pop up in turnilo and I can't figure out why [14:59:27] there's a metadata query, elukey, that you can post to the coordinator, one sec [14:59:46] elukey: http://druid.io/docs/latest/querying/segmentmetadataquery.html [15:00:40] thanks! [15:01:09] elukey: do you know how to post queries like that? [15:01:19] like with curl and all that [15:02:14] I think that I saw some examples in the docs yes [15:05:55] 10Analytics, 10Analytics-Kanban, 10Analytics-Wikistats: Missing stats for Atikamekw Wikipedia on stats.wikimedia.org - https://phabricator.wikimedia.org/T193625#4228873 (10Milimetric) a:03Milimetric [15:06:17] 10Analytics, 10Analytics-Kanban, 10Analytics-Wikistats: Render annotations on all Wikistats charts - https://phabricator.wikimedia.org/T194705#4228875 (10Milimetric) a:03Milimetric [15:13:27] milimetric: in the end it was super simple! [15:13:27] elukey@druid1002:~$ curl druid1002.eqiad.wmnet:8082/druid/v2/datasources/test_elukey_webrequest [15:13:28] RECOVERY - Check if the Hadoop HDFS Fuse mountpoint is readable on stat1005 is OK: OK [15:13:30] {"dimensions":["continent","referer","ip","isp","as_number","response_size","country_code","hostname","http_method","uri_host","uri_path","uri_query","x_cache","content_type","time_firstbyte","webrequest_source","http_status","user_agent"],"metrics":["hits"]} [15:13:55] so as_number is there, but apparently turnilo doesn't get it [15:14:59] fdans: asked me a simliar question an hour ago ^^^ [15:15:07] fdans: maybe druid v2 urls are different? [15:16:33] ottomata: what do you mena? [15:16:35] mean? [15:19:40] I think I might need to add the dimensions in turnilo's config explicitly [15:20:34] IIRC we had to do it in the past for a similar reason [15:20:57] * elukey remembers joseph having a similar problem with Pivot [15:25:56] milimetric, d3-annotation looks great visually [15:27:03] cool, it's a good API, so that's all we need [15:30:45] so if I specify the dimension explicitly, it tells me "cannot resolve $as_number", so I guess that the indexation didn't go well [15:42:26] 10Analytics, 10Operations, 10Research, 10Traffic, and 6 others: Referrer policy for browsers which only support the old spec - https://phabricator.wikimedia.org/T180921#4229008 (10TheDJ) Shall we consider this closed then ? [15:47:31] elukey: it was for fdans, he asked me a q I didn't know about getting druid datasources [15:48:17] ahhh okok sorry [15:48:34] elukey: dumb question but what's up with that $ in $as_number? doesn't sound like something druid says [15:49:24] milimetric: I am probably the one making dumb qs in here :D [15:49:40] elukey: post the query you're using, I'm curious [15:50:02] it was from turnilo, I am trying to come up with a druid query to check the as-dimension [15:50:10] of course I am super quick with Druid :P [15:50:29] oh ok, I can help, lemme see [15:50:43] elukey: interestingly enough I have ISP, continent, coutry_code on your new datasource with superset, but not as_number [15:50:52] hm [15:52:25] joal: I added the dimension to the turnilo's config and it returns an error when used, so I guess that the indexation did not go well [15:52:34] even if the datasource shows the as_number as dimension [15:52:45] elukey: something is weird indeed here [15:54:19] joal: I mean, I am testing OOZIE and DRUID at the same time [15:54:28] what can go wrong [15:54:29] :D [15:55:03] but it is really nice to read dev docs about druid [15:55:15] I am feeling soooo ignorant [15:56:10] 10Analytics, 10Operations, 10Research, 10Traffic, and 6 others: Referrer policy for browsers which only support the old spec - https://phabricator.wikimedia.org/T180921#4229032 (10Tgr) Would be nice if someone could test it on IE or Edge, now that Safari is fixed those are the only two browsers that need a... [15:56:32] elukey: what intervals do you have loaded in that test ingestion? [15:56:41] (what time range did you load from/to?) [15:57:06] elukey: I feel you :) https://usercontent.irccloud-cdn.com/file/WdefQDuo/Screen%20Shot%202018-05-24%20at%205.56.31%20PM.png [15:57:57] elukey: I tried to query directly and it doesn't answer - No response [15:58:24] joal: what time range are you using? [15:58:47] milimetric: 1hour [15:58:53] joal: no, but for what dates [15:59:03] "intervals": [ "2018-05-23T12:00:00.000/2018-05-23T13:00:00.000" ] [15:59:18] how do you know that's ingested? [15:59:28] milimetric: May 23rd 12->16 [15:59:37] this is what I (think) I have ingested [15:59:47] Arf I think I have forgotten one thing [15:59:56] ah, ok, confirmed there's data there [16:00:17] https://www.irccloud.com/pastebin/vcNwVGgG/ [16:00:27] ok, now to filter by that dimension [16:00:31] ok elukey - dimension doesn't show because it's null all along I think [16:02:10] ping fdans [16:02:17] sorryyy [16:02:18] 10Analytics, 10Product-Analytics: Partially purge MobileWikiAppiOSUserHistory eventlogging schema - https://phabricator.wikimedia.org/T195269#4229044 (10mforns) @chelsyx > @mforns can you help with this? Thank you! Sure I'll try to help :] > we'd like to purge the IP address and user agent fields, but keep... [16:02:42] 10Analytics, 10Operations, 10Research, 10Traffic, and 6 others: Referrer policy for browsers which only support the old spec - https://phabricator.wikimedia.org/T180921#4229047 (10TheDJ) P.S. I do think that fallback is indeed broken on Safari. Note how the error message says it is reverting the policy to... [16:04:23] 10Analytics, 10Analytics-Kanban, 10Product-Analytics: Partially purge MobileWikiAppiOSUserHistory eventlogging schema - https://phabricator.wikimedia.org/T195269#4229055 (10mforns) a:03mforns [16:04:28] 10Analytics, 10Analytics-Kanban, 10Product-Analytics: Partially purge MobileWikiAppiOSUserHistory eventlogging schema - https://phabricator.wikimedia.org/T195269#4221538 (10mforns) p:05Triage>03Normal [16:09:46] luca, if you post to /druid/v2/sql you can post this: {"query" : "select count(*) from test_elukey_webrequest where isp is not null"} [16:09:51] elukey: ^ [16:10:05] and that gives me results, whereas as_number is not null gives an error [16:10:35] which is weird, and shouldn't happen if it's just null... [16:13:07] wow thanks! [16:14:00] milimetric: I'm not used to sql yet, but it's super great :) [16:18:18] 10Analytics, 10Analytics-Kanban, 10Analytics-Wikistats, 10Patch-For-Review: Hover infobox should adjust date formatting to granularity displayed - https://phabricator.wikimedia.org/T194430#4229104 (10Milimetric) [16:38:34] 10Analytics, 10Scoring-platform-team, 10draftquality-modeling, 10artificial-intelligence: Productionize monthly article quality prediction datasets - https://phabricator.wikimedia.org/T194741#4207171 (10fdans) p:05Triage>03High [16:38:44] 10Analytics, 10Scoring-platform-team, 10draftquality-modeling, 10artificial-intelligence: Productionize monthly article quality prediction datasets - https://phabricator.wikimedia.org/T194741#4207171 (10fdans) p:05High>03Normal [16:38:57] elukey: I confirm hive does the correct job [16:39:12] elukey: problems seems to be coming from indexation [16:39:34] joal: I've restarted with autonomous_system_number [16:39:38] k [16:39:40] let's see how the indexation goes [16:39:53] elukey: If it does the trick, I'm gonna cry badly [16:41:38] elukey: another dumb question - Have you reindexed changing the datasource in he template? [16:42:26] Cause as of now, it looks we have as_number in the webrequest datasource for some segments [16:42:38] noooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo [16:42:58] I forgot to do it the second time when I uploaded the new files [16:43:06] there you go, the dumb mistake [16:43:32] elukey: we're gonna need to rerun an indexation for 2018-05-23 (one daily is enough) [16:43:39] elukey: shall I start now:? [16:43:48] yes please, sorry :( [16:43:59] I am going to kill my coordinator [16:44:28] done [16:44:38] the normal webrequest one is paused atm [16:45:21] !log Rerun webrequest-druid-daily-wf-2018-5-23 to correct corrupted data [16:45:22] Logged the message at https://www.mediawiki.org/wiki/Analytics/Server_Admin_Log [16:45:50] elukey: From what I see every webrequest job is started [16:45:55] nothing paused [16:46:06] joal: all right then I didn't remember correctly, good [16:46:24] 10Analytics, 10Analytics-Kanban: Archive old geowiki data (editors per country) and make it easily available at WMF - https://phabricator.wikimedia.org/T190856#4229220 (10fdans) In hive: `geowiki_archive_monthly_country` `geowiki_archive_country` `geowiki_archive_city` [16:47:12] joal: in this case, what did you do to correct this? Just fire a new oozie coordinator for that hour? [16:47:48] elukey: just reran the previsouly successfull workflow in prod coordinator [16:48:56] ahhh okok nice [16:48:59] a re-run and that's it [16:54:00] Hi, I think you guys run archiva.wikmedia.org right? I'd like to download the latest version of an artifact. The archiva docs suggest I should be able to do it with something like "wget https://archiva.wikimedia.org/restServices/archivaServices/searchService/artifact?r=snapshots&g=org.wikidata.query.rdf&a=service&v=LATEST" but I get a 403. Am I going about it the right way? Perhaps I'm caught by some CSRF thing? [16:58:12] Hi A-team! We are almost ready to release EL implementation for MobileWikiAppiOSReadingLists and several other new tables on iOS. However, some of us sent data to the production beacon before we changed type of a field (from number to integer)... To avoid disaster due to type conflicts when we release the app and start collecting data from real users, I guess the easiest solution is to delete those tables from Hadoop/mysql before the app [16:58:12] roll-out to production, right? Can you help with that? Thanks! [17:08:43] tarrow: Hi! did you try via https://archiva.wikimedia.org/#artifact/org.wikidata.query.rdf/service ? [17:09:10] and like https://archiva.wikimedia.org/#artifact-details-download-content/org.wikidata.query.rdf/service/0.3.1-SNAPSHOT [17:09:19] (a bit ignorant about it but might help) [17:11:33] chelsyx: hi! IIUC in theory if you guys changed the schema after sending the old data you should have created a new table in the Eventlogging db (it gets created when the first event arrives). So should be good from the hadoop/sql point of view, but I am not 100% sure about it [17:13:03] elukey: that's for mysql db [17:13:11] which chelsyx has blacklisted her schema's from [17:13:52] chelsyx: which tables/fields? [17:13:59] depending on how you sent the data, it might be ok [17:14:08] the Hive tables don't actually know anything about the JSON Schema [17:14:13] they are infered from the data [17:14:41] e.g. [17:14:58] ottomata: the `measure` field in MobileWikiAppiOSSettingAction, MobileWikiAppiOSReadingLists, MobileWikiAppiOSLoginAction and MobileWikiAppiOSSessions [17:15:00] if the data you sent looked like an integer (i.e. no decimal point), they will be created in Hive as a bigint [17:15:12] MobileWikiAppiOSReadingLists.event.measure: bigint [17:15:15] so that's good, ya? [17:16:04] ah [17:16:07] ottomata: yeah, we send `double` to that field during testing, then we change our mind, so we change the type to `integer` [17:16:18] there is no measure field in MobileWikiAppiOSSettingAction [17:16:20] but [17:16:22] the other two are double [17:16:28] so ya, probably best to delete the table and data [17:16:33] shoudl I just do that for all 4? [17:16:35] to be safe? [17:16:53] ottomata: That would be great! Thanks! [17:16:56] k [17:17:25] ottomata: can you delete MobileWikiAppiOSUserHistory too? just to be safe [17:17:42] ok [17:18:29] ottomata: Thanks! [17:20:02] chelsyx: done [17:20:07] !log dropped and deleted raw and refined eventlogging tables and data for MobileWikiAppiOSUserHistory, MobileWikiAppiOSLoginAction, MobileWikiAppiOSSettingAction, MobileWikiAppiOSReadingLists, MobileWikiAppiOSSessions [17:20:08] Logged the message at https://www.mediawiki.org/wiki/Analytics/Server_Admin_Log [17:20:46] Thanks! [17:20:48] ottomata: just to understand, you dropped the tables from hive ? [17:29:47] joal: interesting reading https://medium.com/@leventov/the-problems-with-druid-at-large-scale-and-high-load-part-1-714d475e84c9 [17:29:57] chelsyx: yup [17:29:57] I am wondering if the sub-query problem is still there [17:29:59] and removed the data from hdfs [17:30:11] so new incoming messages should cause tables to be recreated [17:30:37] got it [17:31:11] so basically under raw and refined [17:31:21] (the deletion I mean) [17:33:16] yup [17:34:27] thanks :) [17:34:32] today is learning day :D [17:38:45] all right people I am logging off! [17:38:49] thanks for the patience joal :) [17:38:54] talk with you on Monday! [18:05:06] (03PS1) 10Sahil505: Upgraded footer UI/design [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/434971 (https://phabricator.wikimedia.org/T191672) [18:11:18] 10Analytics, 10Analytics-Kanban, 10Analytics-Wikistats: Improve scoping of CSS - https://phabricator.wikimedia.org/T190915#4229571 (10sahil505) [18:43:25] (03CR) 10Joal: [V: 031 C: 031] "I've seen it working :)" [analytics/refinery] - 10https://gerrit.wikimedia.org/r/433597 (https://phabricator.wikimedia.org/T194055) (owner: 10Elukey) [18:52:22] (03PS1) 10Joal: Update mediawiki-history stats to not compressed [analytics/refinery/source] - 10https://gerrit.wikimedia.org/r/434987 (https://phabricator.wikimedia.org/T192481) [19:02:23] (03CR) 10Ottomata: [C: 031] Update mediawiki-history stats to not compressed [analytics/refinery/source] - 10https://gerrit.wikimedia.org/r/434987 (https://phabricator.wikimedia.org/T192481) (owner: 10Joal) [19:14:38] milimetric: Heya - would you have a minute? [19:36:58] joal: still around? [19:37:33] yup [19:37:47] ottomata: java talk? [19:38:13] ya [19:38:46] joal in bc [20:32:41] (03CR) 10Mforns: [V: 032] "Design looks great, and so does mobile responsiveness! Kudos :D" (033 comments) [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/434971 (https://phabricator.wikimedia.org/T191672) (owner: 10Sahil505) [20:34:00] o/ [20:34:09] anyone around to help me with a speedy hive query? [20:34:18] vaugly related to the outage wikidata just saw... [20:35:25] (03CR) 10Mforns: [V: 032] "By semantic I mean: https://semantic-ui.com/" [analytics/wikistats2] - 10https://gerrit.wikimedia.org/r/434971 (https://phabricator.wikimedia.org/T191672) (owner: 10Sahil505) [20:37:57] addshore: not great with fancy sql..buuut whatcha need? [20:38:50] ottomata: a break down of calls to "uri_host":"www.wikidata.org","uri_path":"/wiki/Special:ItemDisambiguation" per hour from webrequest, broken down from now to 48 hours ago [20:38:54] ir something like that [20:39:09] hehe, fancy sql [20:39:44] broken down how? [20:39:47] like, counts per hour? [20:39:53] yeh, counter per hour would be great [20:45:58] ottomata: think it was some sort of attack, utilizing that page, just want to check the general hit rate vs what we saw over the past hours [20:46:15] aye trying..:) [20:46:36] thanks! :) [20:50:59] addshore: something like: [20:51:00] select [20:51:00] CONCAT(year, "-", month, "-", day, "T", hour, ":00") as date_hour, count(*) from wmf.webrequest [20:51:00] where [20:51:00] webrequest_source='text' and year=2018 and month=5 and day in (22,23,24) and [20:51:00] dt > "2018-05-22T20:00:00" [20:51:01] and uri_host = 'www.wikidata.org' and uri_path = '/wiki/Special:ItemDisambiguation' [20:51:01] group by CONCAT(year, "-", month, "-", day, "T", hour, ":00") [20:51:02] order by date_hour [20:51:02] limit 50; [20:51:33] there are probably better date functions to use to do some of that [20:51:36] buti think that will work [20:52:56] actually [20:52:57] addshore: [20:53:01] pivot/turnilo are GREAT for this [20:53:06] you have access i think ya? [20:53:09] turnilo.wikimedia.org [20:53:21] https://turnilo.wikimedia.org/#webrequest/3/N4IgbglgzgrghgGwgLzgFwgewHYgFwhLYCmAtAMYAWcATmiADQgYC2xyOx+IAomuQHoAqgBUAwoxAAzCAjTEaUfAG1QaAJ4AHLgVZcmNYlO4B9E3sl6ASnGwBzYkryqQUNLXoEATAAYAjAAcpD4ArKReXiJ+AJx4Pj5xPgB08T4AWpLE2AAm3L6BwWFeACxRsfGJKfEZAL4AujUMalo6rmg0EPaShsYEMB0mlJhuknDkGDjcnZJgiDCOKiAA7itJSxAA1hDZ6HBJmDR2IHVM2JieUohQxI3N2txuHV0GRtz9ECaa6JSj41i4BGmTFmCHmTmUIAE6y2AgAytpyBBEHgAJLyFgAEWgcBYACMIHZ4BNcCcQGcLlcbqSoJokGhwX [20:53:21] dWhYmNkIGxsFB/qZzGz9CA7DRbDAELQIBpuAAFKIACUkUAOnlAPW5zJArMMf0mBDgUHIWVZXUahDZYvw2GFCHqTDYOv6CzwoGgAFlhRh8JcENcmJz7AhWpQxUpra6IItyJgYNh6EwA/TjkbNJ0SNksezOThwaTE9hk7CFdxY0HCMQHDkzRaYwTKEg7JRPOaEJagA [20:53:54] i think? [20:54:19] results don't seem the same though... [20:56:06] I should do! [20:56:09] *looks* [20:56:22] that link is very long though :P [20:57:36] oooh [20:57:56] results there dont seem the same as running it in hive? [21:00:17] ottomata: whats the different between webrequest_source and raw? [21:01:38] yeah, in hive i get results in the 30K per hour [21:01:52] maybe i'm doing it wrong though [21:02:06] :D [21:02:08] addshore: webrequest_source and raw? [21:02:23] webrequest_source is the name of the cache cluster where the request came from [21:02:33] its a partition, so selecting it lets you operate on less data [21:02:41] aaaah [21:02:43] and, since requests to that uri_path should all come from text [21:02:44] caches [21:02:55] you'll query much less data (you don't need e.g. image requests, js requests, etc.) [21:03:08] can you paste bin me the results from hive as well? [21:03:38] OH maybe webrequset is sampled... [21:03:41] addshore: id din't run the full query [21:03:56] was gonna let you, in case you need to modify it [21:03:57] :) [21:04:07] :D [21:04:12] okay, I am running it! [21:07:57] interesting, doesnt look that bad [21:08:23] everything says between 20-40k requests per hour [21:08:55] but wait, that only goes up to 2018-5-24T9:00 [21:09:01] which is UTC right? [21:09:26] oh not, the sorting is just funky! [21:09:26] 2018-5-24T18:00 27673 [21:10:01] looks like I need the data from 1900 -> 21:00 I guess ill have to wait a few hours? [21:26:45] yeah