View source for Discovery/Analytics

== Data Sources, Databases, and Datasets ==
Be sure to check out [[Discovery/Data access guidelines |Discovery data access guidelines]] and [[data retention guidelines]].

=== Data Sources ===

==== Web requests ====
* Web requests are generated when a browser (or API client) navigates to a Wikimedia site.

* The initial request may just be for a page, e.g. https://en.wikipedia.org/wiki/San_Francisco

* There may then be subsequent requests for additional content, e.g. images, other data from the page

* Some subset of those web requests are considered "page views" based on the profile of the requests

* "Page views" is a metric that attempts to capture how many times a page has been viewed by a real human being

* Page views tools: <nowiki>https://tools.wmflabs.org/pageviews/</nowiki>

* Some additional reading: [[En:Wikipedia:Pageview statistics|Wikipedia:Pageview_statistics]]

* e.g. a web request for https://en.wikipedia.org/w/api.php?action=help should not be counted as a page view, but https://en.wikipedia.org/wiki/San_Francisco would be

==== EventLogging ====
* [[Mw:Extension:EventLogging|EventLogging]] (EL) & [[Mw:Extension:EventLogging/Guide|EL Guide]]

* Almost all, but not *all*, EventLogging requires Javascript be enabled in the client

* It's often used to track user interactions with features to determine how well the feature serves their need

* As you perform different actions, those actions fire events from the Javascript engine in your browser which are sent to our EventLogging servers

=== Databases ===

==== MySQL ====
Once SSH'd to stat1002 ("stat2") or stat1003 ("stat3"), connect to the MySQL server with the following command: <code>mysql -h analytics-store.eqiad.wmnet</code>

===== log =====
Contains [[Wikitech:Analytics/EventLogging|event logging]] tables, defined by [[Research:Schemas|schemas]]. The following tables are of particular interest:
* '''Search_*''' ([[Schema:Search]]) captures the events from the autocomplete text field in the top right corner of wikis on desktop.
* '''MobileWikiAppSearch_*''' ([[Schema:MobileWikiAppSearch]]) captures events from people searching on the Wikipedia app on Android and iOS.
* '''MobileWebSearch_*''' ([[Schema:MobileWebSearch]]) captures events from people searching on the *.m.* domains.
* '''TestSearchSatisfaction2_*''' ([[Schema:TestSearchSatisfaction2]]) which allows us to track user sessions and derive certain metrics.
* '''WikipediaPortal_*''' ([[Schema:WikipediaPortal]]) captures events from people going to the Wikipedia Portal (wikipedia.org)
* '''PrefUpdate_*''' ([[Schema:PrefUpdate]]) contains users' preference change events (e.g. opting in to / out of a beta feature).

==== Hadoop Cluster ====
The cluster contains many databases, some of which we'll make a note of and describe in this section. It has A LOT of data. So much, in fact, that we can only retain about a month of it at any point.

===== wmf.WebRequest =====
The [[Wikitech:Analytics/Data/Webrequest|WebRequest]] table in the wmf database contains ''refined'' request data stored in the Parquet column-based format (as opposed to the raw JSON imported directly from [[Wikitech:Analytics/Cluster/Kafka|Kafka]] to wmf_raw.WebRequest). ''Refined'' means some fields (columns) are computed from the raw data using user-defined functions (UDFs) in [[Wikitech:Analytics/Cluster/Refinery|Analytics' Refinery]]. For example, '''client_ip''' is computed from '''ip''' and '''x_forwarded_for''' to reveal the true IP of the request, and '''geocoded_data''' (country code, etc.) is computed from '''client_ip''' so we can easily write queries that fetch requests from a specific country.

Due to the volume of the data, the requests are written out to specific partitions indexed by '''webrequest_source''' (e.g. "text" or "misc"), '''year''', '''month''', '''day''', and '''hour'''. It is important to include a <code>WHERE webrequest_source = "__" AND year = YYYY AND etc.</code> clause in your Hive query to avoid querying all partitions, which will take a very, very, VERY long time.

As of Summer 2017,<ref>{{Cite web|url=https://phabricator.wikimedia.org/T164021|title=⚓ T164021 Create tagging udf|website=phabricator.wikimedia.org|accessdate=2017-06-16}}</ref> certain special pageviews have tags (for example, visits to www.wikipedia.org/ are tagged as "portal").

===== wmf_raw.CirrusSearchRequestSet =====
The [[Wikitech:Analytics/Data/Cirrus|CirrusSearchRequestSet]] table in the wmf_raw database contains raw JSON of Cirrus searches.

===== User-defined Functions =====
[[Wikitech:Analytics/Cluster/Hive/QueryUsingUDF|User-defined functions (UDFs)]] are custom functions written in Java that can be called in a Hive query to perform complex tasks that can't be done with the [https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF built-in Hive QL functions]. To see examples of UDFs, look in [https://phabricator.wikimedia.org/diffusion/ANRS/browse/master/refinery-hive/src/main/java/org/wikimedia/analytics/refinery/hive Analytics' Refinery source]  (e.g. [https://phabricator.wikimedia.org/diffusion/ANRS/browse/master/refinery-hive/src/main/java/org/wikimedia/analytics/refinery/hive/IsPageviewUDF.java IsPageview UDF]).

To get started with writing your own UDFs, <code>git clone [[gerrit:analytics/refinery/source|https://gerrit.wikimedia.org/r/analytics/refinery/source]]</code>, install '''maven''' (e.g. <code>brew install maven</code> on Mac OS X if you have [http://brew.sh/ Homebrew] installed), and then package the codebase into a Java .jar file (via <code>mvn package</code>) that you can import into Hive:<syntaxhighlight lang="sql">
ADD JAR /home/bearloga/Code/analytics-refinery-jars/refinery-hive.jar;
CREATE TEMPORARY FUNCTION my_udf AS 'org.wikimedia.analytics.refinery.hive.MyUDF';

USE [Database];
SELECT my_udf([Field]) AS [UDF-processed Field] FROM [Table]
  WHERE year = YYYY AND month = MM AND day = DD;
</syntaxhighlight>I have a clone of the analytics refinery source repository in '''/home/bearloga/Code''' and have an update script scheduled to run (<code>12 20 * * * cd /home/bearloga/Code && bash update-refineries.sh</code>) that checks if origin/master is ahead of the clone and then pulls the updates & <code>mvn package</code>-es it up and copies the latest JARs to '''/home/bearloga/Code/analytics-refinery-jars''', so our data collection scripts can use those (as of [[phab:T130083|T130083]]) and always have the latest and greatest. The script is:<syntaxhighlight lang="bash">
cd analytics-refinery-source

MERGES=$(git log HEAD..origin/master --oneline)
if [ ! -z "$MERGES" ]; then
  git pull origin master
  mvn package
  for refinery in {'core','tools','hive','camus','job','cassandra'}
  do
    jar=$(ls -t "/home/bearloga/Code/analytics-refinery-source/refinery-${refinery}/target" | grep 'SNAPSHOT.jar' | head -1)
    cp "/home/bearloga/Code/analytics-refinery-source/refinery-${refinery}/target/${jar}" "/home/bearloga/Code/analytics-refinery-jars/refinery-${refinery}.jar"
  done
fi
</syntaxhighlight>

=== Public Datasets ===
Our [https://phabricator.wikimedia.org/diffusion/WDGO/ golden] (data) retriever codebase fetches data from the above-described MySQL and Hadoop databases, does additional munging & tidying-up, and then writes the data out to '''/a/published-datasets/discovery/'''['''search'''|'''portal'''|'''maps'''|'''wdqs'''|'''external_traffic'''] directory on '''stat1002''', which rsyncs to https://analytics.wikimedia.org/datasets/ (specifically [https://analytics.wikimedia.org/datasets/discovery/ discovery directory]). It is a collection of R scripts and SQL/HiveQL queries. All the scripts are executed daily via [[Wikitech:Analytics/Systems/Reportupdater|Reportupdater]], which is scheduled as a crontab job (in Mikhail's '''bearloga''' account):<syntaxhighlight lang="bash">
0 5 * * * cd /a/discovery/golden/ && sh main.sh >> /home/bearloga/discovery-golden.log 2>&1
</syntaxhighlight>We still need to get it Puppet-ized so it's not dependent on any staff account. Note that this means that any time '''golden''' is updated, it needs to be <code>git pull</code>'d in /a/discovery/