nyuuzyou/wikis huggingface dataset:
Data Platform/Systems/Hive/Queries/Wikidata

== 2018 - Wikidata editors that have also edited a Wikipedia ==
WITH
wikipedias AS (
 SELECT
 DISTINCT dbname
 FROM wmf_raw.mediawiki_project_namespace_map
 WHERE snapshot = '2018-02'
 AND hostname LIKE '%wikipedia.org'
),
wikidata_editors AS (
 SELECT
 DISTINCT event_user_text
 FROM wmf.mediawiki_history
 WHERE snapshot = '2018-02'
 AND event_entity = 'revision'
 AND event_type = 'create'
 AND wiki_db = 'wikidatawiki'
 AND NOT event_user_is_anonymous
 AND NOT ARRAY_CONTAINS(event_user_groups, 'bot')
)
SELECT
 mwh.wiki_db,
 COUNT(DISTINCT mwh.event_user_text) as wikidata_coeditors
FROM wmf.mediawiki_history mwh
 JOIN wikipedias w ON (mwh.wiki_db = w.dbname)
 JOIN wikidata_editors wde ON (mwh.event_user_text = wde.event_user_text)
WHERE snapshot = '2018-02'
 AND event_entity = 'revision'
 AND event_type = 'create'
 AND NOT mwh.event_user_is_anonymous
 AND NOT ARRAY_CONTAINS(mwh.event_user_groups, 'bot')
GROUP BY
 mwh.wiki_db
ORDER BY wikidata_coeditors DESC
LIMIT 1000
;
== 2015 - Special:EntityData calls per user agent in a given hour ==
SELECT
 count(*) as count, user_agent
FROM
 webrequest
WHERE
 year = 2015
 AND month = 10
 AND day = 12
 AND hour = 1
 AND uri_host = "www.wikidata.org"
 AND http_status = 200
 AND http_method = "GET"
 AND uri_path LIKE "/wiki/Special:EntityData%"
GROUP BY user_agent
ORDER BY count
LIMIT 999999;
Category:Query examples
Category:Hive
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Iceberg/Migration Dependencies

== referer_daily ==
=== DPE dependencies ===
* Archived CSV file generation - in the airflow job where the data is created
=== Superset Datasets Dependencies ===
* Accessible through this superset search (multiple data sources shown)
=== Other teams Dependencies ===
* ?
== unique_devices (4 tables) ==
=== DPE dependencies ===
* Archived CSV files generation - in the airflow jobs where the data is created (4 jobs)
* Druid loading - dedicated airflow jobs
* Cassandra loading - dedicated airflow jobs
* GDI equity landscape - Manully launched airflow job
=== Superset Datasets Dependencies ===
* Accessible through this superset search (multiple data sources shown)
=== Other teams Dependencies ===
* ?
== aqs_hourly ==
=== DPE dependencies ===
* None. No downstream dependencies (?)
=== Superset Datasets Dependencies ===
* None found in this superset search
=== Other teams Dependencies ===
* ?
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Jupyter/Administration

= System Overview =
=== conda-analytics ===
conda-analytics is a custom Debian package of Miniconda that includes additional packages useful for analytics at WMF. conda-analytics is installed on all analytics client (AKA stat boxes) and worker nodes. It installs to /opt/conda-analytics.
conda-analytics includes the Python and other packages we need to run JupyterHub. The configuration and setup of JupyterHub is done by Puppet. Users can ssh tunnel to an analytics client node and access JupyterHub over HTTP. JupyterHub is configured to authenticate users via LDAP (and also restricts them to a few POSIX groups). It is also configured to work with conda-analytics cloned conda environments via a custom CondaEnvProfilesSpawner which can create and activate new user Conda environments. After authentication, the user is prompted with a list of Conda environments to use. Their Jupyter Notebook Server process is then launched using a SystemdSpawner running the jupyterhub-singleuser command out of the user's Conda environment, e.g. in /home/otto/.conda/envs/2020-12-13T19.40.09_otto/bin/jupyterhub-singleuser.
Note that JupyterHub runs out of /opt/conda-analytics, and the user's Jupyter Notebook Server (to which JupyterHub proxies) runs out of the user's selected Conda environment. This means that the user's conda environment is ephemeral and can be discarded at will by the user, or if really needed, by an administrator. We can upgrade conda-analytics, and users can install whatever packages they might need into their Conda environments.
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Maintenance Schedule

This page is meant to collect the upcoming maintenance windows scheduled together with some useful info like: point of contact, impact, related task, etc. As general guideline, for any issue you can reach out to the Analytics team via Slack/Libera Chat() or email (analytics-internal@).
Status 	Time window 	Affected Systems 	Point of contact (IRC nick)	Task ID	Description
Planned 	2021-07-01, 15:00-17:00 UTC	Hadoop and clients (jupyter, oozie, spark)	Razzi Abuissa (razzi) 	T278423	Finish namenode upgrades with an-master1001
Done 	2021-06-15, 15:00-18:00 UTC	Hadoop and clients (jupyter, oozie, spark)	Razzi Abuissa (razzi) 	T278423	2nd attempt snapshot and os upgrade: upgrade an-master1002 only
Attempted, failed	2021-05-25, 15:00UTC 	Hadoop and clients (jupyter, oozie, spark)	Razzi Abuissa (razzi) 	T278423	Reimage to upgrade OS
Done 	2021-02-17, 9 CET 	stat1004,stat1006,stat1007 	Luca Toscano (elukey) 	 	Reboot for new kernel
Done 	2021-02-22, 9 CET 	stat1005,stat1008 	Luca Toscano (elukey) 	 	Reboot for new kernel
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Manual maintenance

== Monthly ==
* Mediawiki history Druid data source switch
* Check for newly created wikis
* Add _REFINED flags for events that contribute to the wmf.wikidata_item_page_link dataset. (this is not even documented anywhere outside of email)
* We run the false positive checker for webrequest loss probably once or more a month. This could be partially automated, if the script finds that all instances of loss are false positives, the job could be automatically rerun. If we do this automatically, we could update the webrequest_sequence_stats table with the results, allowing for trend tracking on top of that table. Currently if you try to analyze data loss over time you find lots of noise with high % loss due to host restarts, etc.
* We re-run sanitization. It's painful to update the command because you often have to change a property file nested in another property file nested in the command. See the docs for Backfilling sanitization, and we should build a rerun command that just takes a list of schemas, since, and until parameters.
== Annual ==
* Update country protection list
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Mediawiki history reduced algorithm

This page describes the algorithm we devised to generate the mediawiki_history_reduced dataset, a dataset optimized for Druid to serve Data Platform/AQS requests.
=== Reduction and filtering optimization ===
The denormalized mediawiki_history table contains a lot of fields, among which only a small number are useful for Wikistats 2 API. We therefore drop a lot of those fields, and also reorganise them to take advantage of columnar-indexing of druid (basically, better to have an array of tags than plenty of booleans):
- user_type can be one of anonymous, group_bot, name_bot (users having a name that looks like a bot) or user.
- page_type can be one oif content or non_content depending on the page namespace.
- other_tags can contain any of deleted, reverted or revert for revisions; self_created or user_first_24_hours for users (the later is whether a user has made an edit within 24 hours after registration)
=== Augmentation with pre-computed new events ===
From the metrics needed for Wikistas 2, one type in particular was causing problems for druid: getting the number of pages or users filtered by their edit activity (e.g. their number of edits). For instance an active editor is calssically a user having made 5 edits or more one a given month. This type of metric is problematic natively for druid because it involves grouping edits by user or page and counting their number of events, then filtering the users or pages not having an appropriate edit-count, and finally count how many users or pages are left. While this seems reasonably simple -- and it is for most query systems -- the dimension cardinality we are dealing with in terms of distinct number of pages or users per month, in addition o the relatively small size of the cluster, makes it a difficult task for druid to handle correctly. So we cheated :)
We created a new event type called digest. We generate digests daily and monthly that store precomputed number of edits per user or page (the field is named revisions), denormalized over the dimensions of interest (namely user_type and page_type).
Those represent a big number of new events, but since we're not interested in keeping the actual user_id or page_id of the user having made the edits, those events are well suited for druid to pre-aggregate them. Finaly, when looking at digest events, we know that individual events having the revisions count field matching 5 or more is our number of active editors.
Notice: Since we need to denormalize over user_type and page_type, we need to explicitly set user_type = 'all' or page_type = 'all' in case no filter along those dimensions is specified. This denormalization is the price to pay for precomputing non-additive metrics.
Category:Data pipelines
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Presto/Administration

== View the Presto UI ==
The Presto coordinator hosts an admin interface that can be accessed via browser.
Read the upstream documentation here.
To access the presto UI for the production cluster, first open an SSH tunnel:
ssh -NL 8280:an-coord1003.eqiad.wmnet:8280 an-coord1003.eqiad.wmnet
Then open http://localhost:8280/ in your browser.
For the test cluster, use an-test-coord1001.eqiad.wmnet instead.
⚠️ Both clusters say the environment is "TEST"; ideally this would be "PRODUCTION" for the production cluster instead.
== Roll restart the Presto cluster ==
There is a Spicerack cookbook for it!
== Check metrics about Presto ==
The first thing to check is the Grafana dashboard: https://grafana.wikimedia.org/d/pMd25ruZz/presto
Presto is currently deployed in the following hosts:
* an-coord1001- Presto Server (acting as coordinator)
* an-presto100[1-5] - Presto Server (acting as worker)
All the queries are managed by the Presto Coordinator, that uses the workers to fetch data from HDFS when needed.
There is a test cluster deployed to the following hosts:
* an-test-coord1001 - Presto Server (acting as coordinator)
* an-test-presto1001 - Presto Server (acting as worker)
At the moment there is no working failover mechanism for the presto-coordinator, so an-coord1001 is a SPOF. However work to improve this is being done under T280905
== Who/What uses Presto? ==
Presto is a new service that the Analytics team is experimenting with to provide a faster and better read-access to HDFS data compared to other systems like Hive. It is used mostly by Analysts/Researchers via CLI or via Superset.
== Presto server down ==
There is a nagios check to control that the Presto server daemon is up and running on every Presto node. As described above, if one of the daemons is reported down please check metrics and contact the Data Engineering team.
Category:Presto
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Presto/Query Logger

Currently work in progress. Will be as specified in https://phabricator.wikimedia.org/T269832.
Repository is at https://github.com/razzius/wmf-presto-query-logger
We want to get the information out of the `tasks` table:
presto:runtime> describe tasks;
 Column | Type | Extra | Comment
-------------------------+-----------+-------+---------
 node_id | varchar | |
 task_id | varchar | |
 stage_execution_id | varchar | |
 stage_id | varchar | |
 query_id | varchar | |
 state | varchar | |
 splits | bigint | |
 queued_splits | bigint | |
 running_splits | bigint | |
 completed_splits | bigint | |
 split_scheduled_time_ms | bigint | |
 split_cpu_time_ms | bigint | |
 split_blocked_time_ms | bigint | |
 raw_input_bytes | bigint | |
 raw_input_rows | bigint | |
 processed_input_bytes | bigint | |
 processed_input_rows | bigint | |
 output_bytes | bigint | |
 output_rows | bigint | |
 physical_written_bytes | bigint | |
 created | timestamp | |
 start | timestamp | |
 last_heartbeat | timestamp | |
 end | timestamp | |
(24 rows)
Category:Presto
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Revision augmentation and denormalization

This process is the third step of the Data Lake pipeline.
After this step, a fully denormalized version of users, pages and revisions history will be available for queries in a single folder / hive table on Hadoop. It also means it'll be available for loading into the serving layer(s), like druid. The data sources used to build this denormalized table are: the revision and archive tables from the MediaWiki databases loaded at Data Loading, plus the page and user histories reconstructed in previous step. The resulting dataset documentation can be found in Analytics/Data Lake/Schemas/Mediawiki history.
=== Main functionalities of this step ===
* Compute values that need complex joins, like revert information, byte-diff and delete time.
* Historify fields that change in time, for example: page title, page namespace, user name, user groups, etc. A field is considered "historical" when, i.e. in the case of the page title, it holds the title the page had at the time of the given event. The opposite of historical fields are "latest" fields, that hold the value that is valid as of today.
* Union and format all data (coming from 3 different schemas) in the same denormalized schema.
=== Performance challenges ===
To historify and populate all required fields, sorting of big portions of the huge dataset (like all revisions of all projects - ~3 billion items) needs to happen. This prevented the initial algorithm from running the process over large wikis.
=== Solution ===
The solution that worked around the performance problems was to use the "secondary sorting" trick, a classical distributed system pattern for sorting big datasets. The algorithm is written in Scala-Spark and uses Resilient Distributed Datasets (RDDs) to store and process big data in a distributed architechture. We tried to decouple as much as possible code for solving scalability problems from code solving data-oriented problems.
Category:Data pipelines
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Siege

== Siege ==
Siege is a unix tool to do load testing of http endpoints. 
== Docs ==
https://www.joedog.org/siege-home/
== Getting started ==
Install siege and on your home directory and edit the file where is the tool logging to:
* Create siege config (will create .siegerc)
 > siege.config
* See Siege config
 > siege -C
* Edit where log can be found on .siegerc
=== Example ===
To test: 25 simulated users [ -c25 ] each hit the server with 10 repetitions [ -r10 ], a total of 250 transactions.
 > siege -c 25 -r 10 --file=small-test.txt --log
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Superset/Date functions

The date function documentation is licensed by Apache Software Foundation (ASF) under the Apache License, Version 2.0. Use these functions when configuring advanced time ranges in Superset charts.
==DATETIME==
Return to specific datetime.
===Syntax===
 datetime([string])
===Example===
 datetime("2020-03-01 12:00:00")
 datetime("now")
 datetime("last year")
==DATEADD==
Moves the given set of dates by a specified interval.
===Syntax===
 dateadd([datetime], [integer], [dateunit])
 dateunit = (year | quarter | month | week | day | hour | minute | second)
===Example===
 dateadd(datetime("today"), -13, day)
 dateadd(datetime("2020-03-01"), 2, day)
==DATETRUNC==
Truncates the specified date to the accuracy specified by the date unit.
===Syntax===
 datetrunc([datetime], [dateunit])
 dateunit = (year | month | week)
===Example===
 datetrunc(datetime("2020-03-01"), week)
 datetrunc(datetime("2020-03-01"), month)
==LASTDAY==
Get the last date by the date unit.
===Syntax===
 lastday([datetime], [dateunit])
 dateunit = (year | month | week)
===Example===
 lastday(datetime("today"), month)
==HOLIDAY==
Get the specify date for the holiday
===Syntax===
 holiday([string])
 holiday([holiday string], [datetime])
 holiday([holiday string], [datetime], [country name])
===Example===
 holiday("new year")
 holiday("christmas", datetime("2019"))
 holiday("christmas", dateadd(datetime("2019"), 1, year))
 holiday("christmas", datetime("2 years ago"))
 holiday("Easter Monday", datetime("2019"), "UK")
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Wikistats

== Legacy Version, Wikistats 1 ==
Wikistats 1, available at stats.wikimedia.org, consists of two almost independent clusters of data and scripts.
*Data about edits, editors and content (harvested from xml dumps)
*Data about pageviews, per wiki, per month, globally or per country (nowadays harvested with hadoop/hive) (see also this page)
== New Version, Wikistats 2 ==
Analytics/Wikistats_2
Dec 2017: WMF Analytics Team is happy to announce the first release of Wikistats 2. Wikistats has been redesigned for architectural simplicity, faster data processing, and a more dynamic and interactive user experience. The data used in the reports will also be made available for external processing. 
First goal is to match the numbers of the current system, and to provide the most important reports, as decided by the Wikistats community (see survey). Over time, we will continue to migrate reports and add new ones that you find useful. We can also analyze the data in new and interesting ways, and look forward to hearing your feedback and suggestions. 
=== Metrics FAQ ===
Analytics/Systems/Wikistats/Metrics/FAQ
Category:Wikistats
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Wikistats/Deprecation of Wikistats 1

This page describes the planned steps to effectively replace Wikistats 1 with the new version released by the Analytics team as it reaches its beta status.
=== URLs (T237752) ===
The stats.wikimedia.org website will be the new home of Wikistats 2, currently hosted in stats.wikimedia.org/v2/. We will try to make sure that the integrity of the old site is kept, as the URL structure of both sites do not conflict, but Wikistats 1's homepage will be discontinued.
Example: https://stats.wikimedia.org/EN/TablesPageViewsMonthlyCombined.htm should still work after the change
Example: https://stats.wikimedia.org/v2/#/mediawiki would be moved to https://stats.wikimedia.org/#/mediawiki, but the previous version should also work and redirect to the new one.
=== Notice (T237999) ===
Until the change happens, we'll place a noticeable banner at the top of the Wikistats 1 homepage announcing this change and the date it'll come into effect.
=== Docs ===
A lot of documentation currently in metawiki, wikitech, and mediawiki use the name Wikistats to refer to the old site. A lot of links and names will have to be replaced.
Category:Wikistats
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Wikistats/Traffic

Wikistats 1 consists of two almost independent clusters of data and scripts.
*Data about edits, contributors and content (harvested from xml dumps)
*Data about pageviews, per wiki, per month, globally or per country (nowadays harvested with hadoop/hive)
This page is about traffic.
== Process flow for Wikistats 1, traffic reports ==
Category:Wikistats
Category:Data pipelines
Category:Data platform
Category:Data platform systems
Data Platform/Systems/Wikistats 2/Smoke Testing

If you're adding a new feature or performing a big change on the UI's, you should run some smoke tests manually. We generally don't automate tests directly on the rendering of the UI, except for very basic integration tests, so here's a list of actions to ensure that the site is working correctly. Not all of them have to be run, only the ones that are pertinent to your change.
# Switch areas by using the top navigation bar. Come back to the dashboard by clicking on the Wikimedia Statistics header.
# Toggle between "All wikis" and a specific wiki. Check that the wiki-specific metrics (like Unique Devices or Edited Pages) lock/unlock.
# Search for a question in the Topic Selector and access that metric.
# Download the data using the download button
# Check all metric types:
## Timeseries - Bar
### Activate a breakdown, you should see more bars with different colors and a legend.
### Check that the Y and X axes make sense
### Use the time range selector, check that granularities change
### Check that the numbers at the bottom of the chart make sense
## Timeseries - Line
### Hover over line to see values
### Make sure there's a line following your cursor at the graph
### Activate a breakdown and check that there are several lines with different colors and the legend reports several values.
### Check that the Y and X axes make sense
### Use the time range selector, check that granularities change
### Check that the numbers at the bottom of the chart make sense
## Top - Table
### Make sure you can load more rows
## Top - Map
### Hover over countries. A tooltip with the value for that country should appear
### Zoom in and out. There should be limits to the amount of zoom in both directions
### The legend's colors and numbers should reflect the values on the tooltips
# Switch between the graphs and their tables. Activated breakdowns should show in the table.
# Load more rows in the table chart using the button at the bottom
# Change the wiki on the wikiselector on the left
# Switch metrics on the buttons of the sidebar
Category:Wikistats
Category:Data platform
Category:Data platform systems
Data Platform/Systems/analytics.wikimedia.org

analytics.wikimedia.org is a static site that will serves all our dashboards and data downloads. For information on how to publish files into analytics.wikimedia.org/published, see Analytics/Web publication.
== Service ==
* URL: https://analytics.wikimedia.org/
* Maintainer: Analytics team
* Issue tracker: ??
* Source code: https://gerrit.wikimedia.org/g/analytics/analytics.wikimedia.org
* Creation task: T132407
== Operation ==
Currently hosted on thorium in Eqiad.
=== Deployment ===
The domain includes static pages and also two dashiki dashboard instances, it is unfortunate we have to deploy from source but this is the state of affairs at this time.
* Get the code from Gerrit: 
Dashiki instances are in the dashboards/ sub directory: https://gerrit.wikimedia.org/g/analytics/analytics.wikimedia.org/+/master/dashboards/
* To rebuild dashiki from source, follow these commands: https://gerrit.wikimedia.org/g/analytics/analytics.wikimedia.org#readme
* Commit the result to git, and merge in Gerrit. Puppet will automatically deploy it via git::clone ensure=>latest.
Category:Services
Category:Data platform
Category:Data platform systems