You are browsing a read-only backup copy of Wikitech. The live site can be found at wikitech.wikimedia.org

Analytics/Data Lake: Difference between revisions

From Wikitech-static
Jump to navigation Jump to search
imported>Neil P. Quinn-WMF
(Copyedit and expand documentation)
imported>Neil P. Quinn-WMF
(Add more info on syntax differences (integer division, date conversion))
(12 intermediate revisions by 5 users not shown)
Line 1: Line 1:
The '''Analytics Data Lake''' (ADL), or the '''Data Lake''' for short, is a large, analytics-oriented repository of data about Wikimedia projects (in industry terms, a [[w:data lake|data lake]]). All of the data it contains can be joined together.
The '''Analytics Data Lake''' (ADL), or the '''Data Lake''' for short, is a large, analytics-oriented repository of data about Wikimedia projects (in industry terms, a [[w:data lake|data lake]]).


== Data available ==
== Data available ==
* [[Analytics/Data Lake/Traffic|Traffic data]]  -- webrequest, pageviews, unique devices ...
{{Note|content=Currently, you need [[Analytics/Data access#Production access|production data access]] to use some of this data. A lot of it is available publicly at [https://dumps.wikimedia.org/ dumps.wikimedia.org].|type=notice}}
* [[Analytics/Data Lake/Edits|Edits data]] -- Historical data about revisions, pages, and users [in beta as of 2017-04-07].
Currently, you need [[Analytics/Data access#Production access|production data access]] to use this data, but as of March 2018, work is underway to make the edit history data publicly available as part of the [[Portal:Data Services|Data Services provided to Cloud Services users]] ([[phab:T169572|T169572]]).


As the Data Lake matures, we will add any and all data and try to make it public as much as possible.
;[[Analytics/Data Lake/Traffic|Traffic data]]
:[[Analytics/Data Lake/Traffic/Webrequest|Webrequest]], [[Analytics/Data Lake/Traffic/Pageviews|pageviews]], and [[Analytics/Data Lake/Traffic/Unique Devices|unique devices]]
;[[Analytics/Data Lake/Edits|Edits data]]
:Historical data about revisions, pages, and users (e.g. [[Analytics/Data Lake/Edits/MediaWiki history|MediaWiki History]])
;[[Analytics/Data Lake/Content|Content data]]
:Wikitext ([[Analytics/Data Lake/Content/Mediawiki wikitext current|latest]] & [[Analytics/Data Lake/Content/Mediawiki wikitext history|historical]]) and wikidata-entities
;[[Analytics/Data Lake/Events|Events data]]
:[[Analytics/Systems/EventLogging|EventLogging]], EventBus and event streams data (raw, refined, [[Analytics/Systems/Event Sanitization|sanitized]])
;[[Analytics/Data Lake/ORES|ORES scores]]
:Machine learning predictions (available as events as of 2020-02-27)
 
Some of these datasets (such as webrequests) are only available in Hive, while others (such as pageviews) are ''also'' available as [[mw:Wikimedia Product/Data dictionary|data cubes]] (usually in more aggregated capacity).
 
== Access ==
The main way to access the data in the Data Lake is to run queries using one of the three available SQL engines: [[Analytics/Systems/Presto|Presto]], [[Hive]], and [[Analytics/Systems/Cluster/Spark|Spark]].
 
You can access these engines through several different routes:
 
* [[Analytics/Systems/Superset|'''Superset''']] has a graphical SQL editor where you can run [[Analytics/Systems/Presto|Presto]] queries
* '''[[Analytics/Cluster/Hue|Hue]]''' has a graphical SQL editor where you can run [[Analytics/Systems/Cluster/Hive|Hive]] queries
* '''Custom code''' on one of the [[Analytics/Systems/Clients|analytics clients]] (the easiest way to do this is to use our [[Analytics/Systems/Jupyter|Jupyter service]])
** for '''Python''', use the [https://github.com/wikimedia/wmfdata-python wmfdata-python] package
** for '''R''', use the [https://github.com/wikimedia/wmfdata-r wmfdata-r] package
 
All three engines also have command-line programs which you can use on one of the [[Analytics/Systems/Clients|analytics clients]]. This is probably the least convenient way, but if you want to use it, consult the engine's documentation page.
 
=== Differences between the SQL engines ===
For the most part, Presto, Hive, and Spark work the same way, but they have some differences in SQL syntax and processing power.
 
==== Syntax differences ====
 
* Spark and Hive use <code>STRING</code> as the keyword for string data, while Presto uses <code>VARCHAR</code>.
** One consequence is a different method for transforming integer <code>year</code>/<code>month</code>/<code>day</code> fields to a date string.
** Spark and Hive: <code>CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0'))</code> (casting to <code>STRING</code> is not actually required)
** Presto: <code>CONCAT(CAST(year AS VARCHAR), '-', LPAD(CAST(month AS VARCHAR), 2, '0'), '-', LPAD(CAST(day AS VARCHAR), 2, '0'))</code> (casting to <code>VARCHAR</code> ''is'' required)
 
*In Spark and Hive, you use the <code>SIZE</code> function to get the length of an array, while in Presto you use <code>CARDINALITY</code>.
*In Spark and Hive, double quoted text (like <code>"foo"</code>) is interpreted as a string, while in Presto it is interpreted as a column name. It's easiest to use single quoted text (like <code>'foo'</code>) for strings, since all three engines interpret it the same way.
*Spark and Hive have a <code>CONCAT_WS</code> ("concatenate with separator") function, but Presto does not.
* Spark supports both <code>FLOAT</code> and <code>REAL</code> as keywords for the 32-bit floating-point number data type, while Presto supports only <code>REAL</code>.
* Presto has no FIRST and LAST functions
* If you need to use a keyword like <code>DATE</code> as a column name, you use backticks (<code>`date`</code>) in Spark and Hive, but double quotes (<code>"date"</code>) in Presto.
*To convert an ISO 8601 timestamp string (e.g. <code>"2021-11-01T01:23:02Z"</code>) to an SQL timestamp:
**Spark: <code>TO_TIMESTAMP(dt)</code>
**Presto: <code>FROM_ISO8601_TIMESTAMP(dt)</code>
**Hive: <code>FROM_UNIXTIME(UNIX_TIMESTAMP(dt, "yyyy-MM-dd'T'HH:mm:ss'Z'"))</code>
*If you divide integers, Hive and Spark will return a floating-point number if necessary (e.g. <code>1 / 3</code> returns <code>0.333333</code>). However, Presto will return only an integer (e.g. <code>1 / 3</code> returns <code>0</code>). Use <code>CAST(x AS REAL)</code> to work around this.
*See also: [https://prestodb.io/docs/0.273.3/migration/from-hive.html Presto's guide to migrating from Hive]


== Technical architecture ==
== Technical architecture ==
''Main article: [[Analytics/Systems/Data Lake]]''
Data Lake datasets which are available in Hive are stored in the [[Analytics/Systems/Cluster/Hadoop|Hadoop]] Distributed File System (HDFS), usually in the Parquet file format. The [https://cwiki.apache.org/confluence/display/Hive/AdminManual+Metastore+Administration Hive metastore] is a centralized repository for metadata about these data files, and all three SQL query engines we use (Presto, Spark SQL, and Hive) rely on it.
[[En:Apache Parquet]]
Some Data Lake datasets are available in [[Analytics/Systems/Druid|Druid]], which is separate from Hive and HDFS, and allows quick exploration and dashboarding of those datasets in [[Analytics/Systems/Turnilo|Turnilo]] and [[Analytics/Systems/Superset|Superset]].


The [[Analytics/Systems/Cluster|Analytics Hadoop cluster]]<nowiki/>is the primary backend for the Data Lake.
The [[Analytics/Systems/Cluster|Analytics cluster]], which consists of Hadoop servers and related components, provides the infrastructure for the Data Lake.

Revision as of 00:37, 16 September 2022

The Analytics Data Lake (ADL), or the Data Lake for short, is a large, analytics-oriented repository of data about Wikimedia projects (in industry terms, a data lake).

Data available

Traffic data
Webrequest, pageviews, and unique devices
Edits data
Historical data about revisions, pages, and users (e.g. MediaWiki History)
Content data
Wikitext (latest & historical) and wikidata-entities
Events data
EventLogging, EventBus and event streams data (raw, refined, sanitized)
ORES scores
Machine learning predictions (available as events as of 2020-02-27)

Some of these datasets (such as webrequests) are only available in Hive, while others (such as pageviews) are also available as data cubes (usually in more aggregated capacity).

Access

The main way to access the data in the Data Lake is to run queries using one of the three available SQL engines: Presto, Hive, and Spark.

You can access these engines through several different routes:

All three engines also have command-line programs which you can use on one of the analytics clients. This is probably the least convenient way, but if you want to use it, consult the engine's documentation page.

Differences between the SQL engines

For the most part, Presto, Hive, and Spark work the same way, but they have some differences in SQL syntax and processing power.

Syntax differences

  • Spark and Hive use STRING as the keyword for string data, while Presto uses VARCHAR.
    • One consequence is a different method for transforming integer year/month/day fields to a date string.
    • Spark and Hive: CONCAT(year, '-', LPAD(month, 2, '0'), '-', LPAD(day, 2, '0')) (casting to STRING is not actually required)
    • Presto: CONCAT(CAST(year AS VARCHAR), '-', LPAD(CAST(month AS VARCHAR), 2, '0'), '-', LPAD(CAST(day AS VARCHAR), 2, '0')) (casting to VARCHAR is required)
  • In Spark and Hive, you use the SIZE function to get the length of an array, while in Presto you use CARDINALITY.
  • In Spark and Hive, double quoted text (like "foo") is interpreted as a string, while in Presto it is interpreted as a column name. It's easiest to use single quoted text (like 'foo') for strings, since all three engines interpret it the same way.
  • Spark and Hive have a CONCAT_WS ("concatenate with separator") function, but Presto does not.
  • Spark supports both FLOAT and REAL as keywords for the 32-bit floating-point number data type, while Presto supports only REAL.
  • Presto has no FIRST and LAST functions
  • If you need to use a keyword like DATE as a column name, you use backticks (`date`) in Spark and Hive, but double quotes ("date") in Presto.
  • To convert an ISO 8601 timestamp string (e.g. "2021-11-01T01:23:02Z") to an SQL timestamp:
    • Spark: TO_TIMESTAMP(dt)
    • Presto: FROM_ISO8601_TIMESTAMP(dt)
    • Hive: FROM_UNIXTIME(UNIX_TIMESTAMP(dt, "yyyy-MM-dd'T'HH:mm:ss'Z'"))
  • If you divide integers, Hive and Spark will return a floating-point number if necessary (e.g. 1 / 3 returns 0.333333). However, Presto will return only an integer (e.g. 1 / 3 returns 0). Use CAST(x AS REAL) to work around this.
  • See also: Presto's guide to migrating from Hive

Technical architecture

Data Lake datasets which are available in Hive are stored in the Hadoop Distributed File System (HDFS), usually in the Parquet file format. The Hive metastore is a centralized repository for metadata about these data files, and all three SQL query engines we use (Presto, Spark SQL, and Hive) rely on it. Some Data Lake datasets are available in Druid, which is separate from Hive and HDFS, and allows quick exploration and dashboarding of those datasets in Turnilo and Superset.

The Analytics cluster, which consists of Hadoop servers and related components, provides the infrastructure for the Data Lake.