Analytics at Wikipedia with Druid

B45193666b6922c140244eb153cc5f3c?s=47 Druid
December 11, 2017

Analytics at Wikipedia with Druid

Presented at Strata NYC 2017

B45193666b6922c140244eb153cc5f3c?s=128

Druid

December 11, 2017
Tweet

Transcript

  1. 2.
  2. 3.

    “Imagine a world in which every single human being can

    freely share in the sum of all knowledge.”
  3. 4.

    Senior Systems Engineer I have been with Wikimedia since 2012,

    mostly on infrastructure for analytics. Previously Lead SysAdmin at CouchSurfing.org http://www.mediawiki.org/wiki/User:Ottomata Andrew Otto
  4. 7.
  5. 13.

    Wikipedia is ranked the 6th largest website when it comes

    to pageviews [Alexa] Scale is an obvious issue ...
  6. 14.

    Complexity is less obvious MediaWiki is not designed for analytics.

    It is hard to know the pages that were created by users with more than 5 edits in German wikipedia in 2016.
  7. 15.

    Privacy We do not send data to third parties. Commercial

    solutions to gather user data are not an option.
  8. 18.

    Captures rich events of JSON schema-ed data. Events are validated

    and sent off to various data stores. EventLogging Event Logs
  9. 23.

    Must host all data in our datacenters Event Logs Store

    as little private data as possible: - No IPs - Only sanitized user agents - Historical data (older than 90 days) must be fully cleansed of sensitive information - Should not be able to reconstruct browsing sessions
  10. 26.

    Future work: Teach folks to design event schemas that fit

    an OLAP data model Event Logs i.e. dimensions and metrics.
  11. 27.

    Future work: Teach folks to design event schemas that fit

    an OLAP data model Event Logs i.e. dimensions and metrics. Auto ingest these into Druid to allow for use with nice GUIs (Pivot / Superset)
  12. 30.

    MediaWiki databases MediaWiki metadata is not huge, but analytics of

    edit history since the beginning of wiki time is dimensionally complex.
  13. 32.

    We want to learn about users and edit history. -

    How many new users over time? - How many edits over time? - How many edits do new users make? - How do editors stay active? - How many edits are made by bots? - Etc. etc. etc. MediaWiki databases
  14. 34.

    MediaWiki databases How many new editors across all wikis? 2

    joins, 3 subqueries 3 tables on 800+ wikis 5 days to run
  15. 36.

    MySQL (OLTP) works fine as a application storage but not

    so great for historical analytics. MediaWiki databases
  16. 37.

    MySQL (OLTP) works fine as a application storage but not

    so great for historical analytics. Can only query current state. MediaWiki databases
  17. 38.

    However... Historical events are saved in a ‘log’ table. Log

    has juicy information about: MediaWiki databases
  18. 39.

    page (AKA article) events (move, delete and restore) user events

    (rename, block, rights) ...which both go back to the almost beginning of wiki-time MediaWiki databases However... Historical events are saved in a ‘log’ table. Log has juicy information about:
  19. 42.

    MediaWiki databases However...HOWEVER! MediaWiki is an old(ish) popular open source

    project. Super cool! But this also leads us to the land of database design insanity!
  20. 45.

    MediaWiki databases However...HOWEVER! Primary ids are missing for most events!

    Instead they have page title or user name. Pages and users are renamed all the time!
  21. 46.

    MediaWiki databases However...HOWEVER! Primary ids are missing for most events!

    Instead they have page title or user name. Pages and users are renamed all the time! Data is stored in different formats depending on MediaWiki version that created the log record
  22. 47.

    MediaWiki databases However...HOWEVER! Primary ids are missing for most events!

    Instead they have page title or user name. Pages and users are renamed all the time! Data is stored in different formats depending on MediaWiki version that created the log record sometimes in PHP encoded blobs!
  23. 48.

    MediaWiki databases However...HOWEVER! Primary ids are missing for most events!

    Instead they have page title or user name. Pages and users are renamed all the time! Data is stored in different formats depending on MediaWiki version that created the log record page and user properties might be in different fields depending on age of event! sometimes in PHP encoded blobs!
  24. 49.

    MediaWiki databases However...HOWEVER! Primary ids are missing for most events!

    Instead they have page title or user name. Pages and users are renamed all the time! Data is stored in different formats depending on MediaWiki version that created the log record Durations of user blocks is stored in 7 different date formats! page and user properties might be in different fields depending on age of event! sometimes in PHP encoded blobs!
  25. 50.

    MediaWiki databases However...HOWEVER! Primary ids are missing for most events!

    Instead they have page title or user name. Pages and users are renamed all the time! Data is stored in different formats depending on MediaWiki version that created the log record Durations of user blocks is stored in 7 different date formats! page and user properties might be in different fields depending on age of event! Some revisions are historically orphaned (no existent page IDs!) sometimes in PHP encoded blobs!
  26. 51.

    MediaWiki databases However...HOWEVER! Primary ids are missing for most events!

    Instead they have page title or user name. Pages and users are renamed all the time! Data is stored in different formats depending on MediaWiki version that created the log record Durations of user blocks is stored in 7 different date formats! page and user properties might be in different fields depending on age of event! Some revisions are historically orphaned (no existent page IDs!) Some page IDs were reused for different pages! (I guess IDs were a scarce commodity in 2006?) sometimes in PHP encoded blobs!
  27. 53.

    MediaWiki history Solution: history reconstruction Complex series of Spark jobs

    that iterate over imported snapshot of all MediaWiki databases. Final output: large denormalized history Hive table of all MediaWiki events across all Wikimedia sites
  28. 54.

    MediaWiki history How many new editors across all wikis? 0

    joins, 0 subqueries 1 table 5 minutes to run
  29. 58.

    Webrequests We want to know - How many HTTP requests?

    - What browsers do people use? - How many of those requests actual article page views? - How many of those are unique devices?
  30. 63.

    Can max at > 200,000 requests per second. 2014 World

    Cup Final Webrequests We lost data because: soccer! (this is the infamous Brazil vs Germany game)
  31. 64.

    Varnish Webrequests handled by Varnish in multiple datacenters. Shared memory

    log varnishlog apps can access in Varnish’s logs in memory. Varnishncsa Varnishlog -> stdout formatter Wikimedia patched this to send logs over UDP. Webrequests
  32. 66.

    udp2log Listens for UDP traffic stream. Delimits messages by newlines.

    Tees out and samples traffic to custom filters. multicast relay socat relay sends varnishncsa traffic to a multicast group, allowing for multiple udp2log consumers. Webrequests
  33. 68.

    doesn’t scale - every udp2log instance must see every network

    packet. Works for simple use cases and lower traffic scenarios. Webrequests - udp2log
  34. 71.
  35. 75.
  36. 78.

    Why? Druid Can’t use proprietary software (No Tableu) Druid works

    with some really nice GUIs (Pivot, Superset, etc.), which provide a lot of value for us.
  37. 79.

    Why? Druid Can’t use proprietary software (No Tableu) Druid works

    with some really nice GUIs (Pivot, Superset, etc.), which provides a lot of value for us. and we get a fast scalable OLAP database, with realtime support
  38. 81.

    Ingestion Druid Batch Data is partially pre-aggregated in Hadoop into

    useful Hive tables (e.g. Mediawiki History, Pageview counts, etc.) This is then regularly loaded into Druid using custom tooling and Oozie
  39. 82.

    Ingestion Druid Streaming Spark Streaming job consumes from Kafka and

    crunches dimensions and metrics. Spark-Tranquility used to beam directly to Druid for indexing (WIP)
  40. 85.

    Druid Suddenly outrageous higher pageviews for main pages Lots of

    slicing and dicing in Hive, but we didn’t see a trend.
  41. 86.

    Druid Suddenly outrageous higher pageviews for main pages Suspicion about

    browsers. Used Druid/Pivot to slice Lots of slicing and dicing in Hive, but we didn’t see a trend.
  42. 89.

    Answer: Chrome 41 on Windows 7 Chrome 51 users, upgrading

    to 52, cool And there’s our problem, definitely Chrome 41
  43. 91.

    OLAP is about splitting apart a large dataset into smaller

    subsets and analyzing the subsets Aggregations are often involved (counts, sums, quantiles, etc) The OLAP Problem
  44. 93.

    High dimensionality: 10s, 100s, or even 1000s of columns High

    cardinality: each column may have millions of unique values Multi-tenancy: many concurrent users Freshness: load from streams, not batch files OLAP at scale is hard
  45. 94.

    How Druid tackles OLAP Column storage format Compression & encoding

    Intelligent search filters Approximate algorithms Distributed architecture
  46. 95.

    Storing data timestamp domain gender clicked 2011-01-01T00:01:35Z bieber.com Female 1

    2011-01-01T00:03:03Z bieber.com Female 0 2011-01-01T00:04:51Z ultra.com Male 1 2011-01-01T00:05:33Z ultra.com Male 1 2011-01-01T00:05:53Z ultra.com Female 0 2011-01-01T00:06:17Z ultra.com Female 1 2011-01-01T00:23:15Z bieber.com Female 0 2011-01-01T00:38:51Z ultra.com Male 1 2011-01-01T00:49:33Z bieber.com Female 1 2011-01-01T00:49:53Z ultra.com Female 0
  47. 96.

    timestamp domain gender clicked 2011-01-01T00:00:00Z bieber.com Female 1 2011-01-01T00:00:00Z ultra.com

    Female 2 2011-01-01T00:00:00Z ultra.com Male 3 timestamp domain gender clicked 2011-01-01T00:01:35Z bieber.com Female 1 2011-01-01T00:03:03Z bieber.com Female 0 2011-01-01T00:04:51Z ultra.com Male 1 2011-01-01T00:05:33Z ultra.com Male 1 2011-01-01T00:05:53Z ultra.com Female 0 2011-01-01T00:06:17Z ultra.com Female 1 2011-01-01T00:23:15Z bieber.com Female 0 2011-01-01T00:38:51Z ultra.com Male 1 2011-01-01T00:49:33Z bieber.com Female 1 2011-01-01T00:49:53Z ultra.com Female 0 Rollup
  48. 99.

    Create IDs - Justin Bieber -> 0, Ke$ha -> 1

    Store - page → [0 0 0 1 1 1] - language → [0 0 0 0 0 0] Compression
  49. 100.

    Justin Bieber → [0, 1, 2] → [111000] Ke$ha →

    [3, 4, 5] → [000111] Justin Bieber OR Ke$ha → [111111] Compression! Fast filters
  50. 101.

    timestamp domain gender clicked 2011-01-01T00:00:00Z bieber.com Female 1 2011-01-01T00:00:00Z ultra.com

    Female 2 2011-01-01T00:00:00Z ultra.com Male 3 timestamp domain gender clicked 2011-01-01T00:01:35Z bieber.com Female 1 2011-01-01T00:03:03Z bieber.com Female 0 2011-01-01T00:04:51Z ultra.com Male 1 2011-01-01T00:05:33Z ultra.com Male 1 2011-01-01T00:05:53Z ultra.com Female 0 2011-01-01T00:06:17Z ultra.com Female 1 2011-01-01T00:23:15Z bieber.com Female 0 2011-01-01T00:38:51Z ultra.com Male 1 2011-01-01T00:49:33Z bieber.com Female 1 2011-01-01T00:49:53Z ultra.com Female 0 Approximation algorithms
  51. 102.

    Approximation algorithms timestamp domain user gender clicked 2011-01-01T00:01:35Z bieber.com 4312345532

    Female 1 2011-01-01T00:03:03Z bieber.com 3484920241 Female 0 2011-01-01T00:04:51Z ultra.com 9530174728 Male 1 2011-01-01T00:05:33Z ultra.com 4098310573 Male 1 2011-01-01T00:05:53Z ultra.com 5832058870 Female 0 2011-01-01T00:06:17Z ultra.com 5789283478 Female 1 2011-01-01T00:23:15Z bieber.com 4730093842 Female 0 2011-01-01T00:38:51Z ultra.com 9530174728 Male 1 2011-01-01T00:49:33Z bieber.com 4930097162 Female 1 2011-01-01T00:49:53Z ultra.com 3081837193 Female 0
  52. 103.

    timestamp domain gender clicked unique_users 2011-01-01T00:00:00Z bieber.com Female 1 [approximate_sketch]

    2011-01-01T00:00:00Z ultra.com Female 2 [approximate_sketch] 2011-01-01T00:00:00Z ultra.com Male 3 [approximate_sketch] timestamp domain gender clicked 2011-01-01T00:01:35Z bieber.com Female 1 2011-01-01T00:03:03Z bieber.com Female 0 2011-01-01T00:04:51Z ultra.com Male 1 2011-01-01T00:05:33Z ultra.com Male 1 2011-01-01T00:05:53Z ultra.com Female 0 2011-01-01T00:06:17Z ultra.com Female 1 2011-01-01T00:23:15Z bieber.com Female 0 2011-01-01T00:38:51Z ultra.com Male 1 2011-01-01T00:49:33Z bieber.com Female 1 2011-01-01T00:49:53Z ultra.com Female 0 Approximation algorithms
  53. 106.

    Scale (largest cluster I know about – not a hard

    limit) >3M events/sec ingested (peak) 10 – 100k events/sec/cpu >500 TB of segments (>50 trillion raw events, >50 PB raw data) mean 500ms query time 90%ile < 1s, 95%ile < 5s, 99%ile < 10s
  54. 108.

    Works well with: - Kafka - Hadoop - S3 -

    Spark Streaming - Storm - Samza - I bet some other things that also start with S Connect