Upgrade to Pro — share decks privately, control downloads, hide ads and more …

NoSQL no more: SQL on Druid with Apache Calcite (Strata SJ 2018)

26290e7e829b985a6bcb44da8213029e?s=47 Imply
March 07, 2018

NoSQL no more: SQL on Druid with Apache Calcite (Strata SJ 2018)

Druid is an analytics-focused, distributed, scale-out data store. Existing Druid clusters have scaled to petabytes of data and trillions of events, ingesting millions of events every second. Up until version 0.10, Druid could only be queried in a JSON-based language that many users found unfamiliar.

Enter Apache Calcite. It includes an industry-standard SQL parser, validator, and JDBC driver, as well as a cost-based relational optimizer. Calcite bills itself as “the foundation for your next high-performance database” and is used by Hive, Drill, and a variety of other projects. Druid uses Calcite to power Druid SQL, a standards-based query API that vaults Druid out of the NoSQL world and into the SQL world.

26290e7e829b985a6bcb44da8213029e?s=128

Imply

March 07, 2018
Tweet

Transcript

  1. NoSQL no more SQL on Druid with Apache Calcite Gian

    Merlino gian@imply.io
  2. Who am I? Gian Merlino Committer & PMC member on

    Committer on Apache Calcite Cofounder at 2
  3. Agenda • What is Druid? • What is NoSQL? •

    What is Apache Calcite? • From NoSQL to SQL • Do try this at home! 3
  4. 4 open source, high-performance, column-oriented, distributed data store

  5. What is Druid? • “high performance”: low query latency, high

    ingest rates • “column-oriented”: best possible scan rates • “distributed”: deployed in clusters, typically 10s–100s of nodes • “data store”: the cluster stores a copy of your data 5
  6. Why does Druid exist? 6

  7. The Problem • OLAP slice-and-dice for big data • Interactive

    exploration • Look under the hood of reports and dashboards • And we want our data fresh, too 7
  8. The Problem 8

  9. Challenges • Scale: big data is tough to process quickly

    • Complexity: too much fine grain to precompute • High dimensionality: 10s or 100s of dimensions • Concurrency: many users and tenants • Freshness: load from streams 9
  10. Motivation • Sub-second responses allow dialogue with data • Rapid

    iteration on questions • Remove barriers to understanding 10
  11. Powered by Druid 11 Source: http://druid.io/druid-powered.html

  12. Powered by Druid “The performance is great ... some of

    the tables that we have internally in Druid have billions and billions of events in them, and we’re scanning them in under a second.” 12 Source: https://www.infoworld.com/article/2949168/hadoop/yahoo-struts-its-hadoop-stuff.html From Yahoo:
  13. Druid Key Features • Low latency ingestion from Kafka •

    Bulk load from Hadoop • Can pre-aggregate data during ingestion • “Schema light” • Ad-hoc queries • Exact and approximate algorithms • Can keep a lot of history (years are ok) 13
  14. Druid Druid makes interactive data exploration fast and flexible, and

    powers analytic applications. 14
  15. What is NoSQL? 15

  16. What is NoSQL? “There's no strong definition of the concept

    out there, no trademarks, no standard group, not even a manifesto.” 16 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  17. What is NoSQL? Early examples: Voldemort, Cassandra, Dynomite, HBase, Hypertable,

    CouchDB, MongoDB 17 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  18. What is NoSQL? What are they? • Document stores •

    Key/value stores • Graph databases • Timeseries databases 18
  19. What is NoSQL? • Not using the relational model (nor

    the SQL language) • Open source • Designed to run on large clusters • Based on the needs of 21st century web properties • No schema, allowing fields to be added to any record without controls 19 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  20. Categorizing Druid • Not using the relational model (nor the

    SQL language) • Open source • Designed to run on large clusters • Based on the needs of 21st century web properties • No schema, allowing fields to be added to any record without controls 20 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  21. Categorizing Druid • Not using the relational model (nor the

    SQL language) • Open source • Designed to run on large clusters • Based on the needs of 21st century web properties • No schema, allowing fields to be added to any record without controls 21 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  22. Categorizing Druid • Not using the relational model (nor the

    SQL language) • Open source • Designed to run on large clusters • Based on the needs of 21st century web properties • No schema, allowing fields to be added to any record without controls 22 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  23. Categorizing Druid • Not using the relational model (nor the

    SQL language) • Open source • Designed to run on large clusters • Based on the needs of 21st century web properties • No schema, allowing fields to be added to any record without controls 23 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  24. Categorizing Druid • Not using the relational model (nor the

    SQL language) • Open source • Designed to run on large clusters • Based on the needs of 21st century web properties • No schema, allowing fields to be added to any record without controls 24 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  25. Categorizing Druid • Not using the relational model (nor the

    SQL language) • Open source • Designed to run on large clusters • Based on the needs of 21st century web properties • No schema, allowing fields to be added to any record without controls 25 Source: https://martinfowler.com/bliki/NosqlDefinition.html
  26. Categorizing Druid Is avoiding the SQL language and relational model

    really a good thing? 26
  27. The Relational Model • The relational model is based around

    relations • SQL calls them tables and those tables have columns • SQL queries imply relational operations ◦ Scan ◦ Project ◦ Filter ◦ Aggregate ◦ Union ◦ Join 27
  28. The Relational Model 28 timestamp product_id user_id revenue 2030-01-01 212

    1 180.00 2030-01-01 998 2 24.95 Table: “sales” Table: “products” id name 212 Office chair 998 Coffee mug, 2-pack Table: “users” id country city user_gender user_age 1 US New York F 34 2 FR Paris M 28
  29. Druid and the Relational Model 29 timestamp product country city

    gender age revenue 2030-01-01 Office chair US New York F 34 180.00 2030-01-01 Coffee mug, 2-pack FR Paris M 28 24.95 Datasource: “sales”
  30. Druid and the Relational Model 30 Datasource: “sales” Lookup: “products”

    id name 212 Office chair 998 Coffee mug, 2-pack timestamp product_id country city gender age revenue 2030-01-01 212 US New York F 34 180.00 2030-01-01 998 FR Paris M 28 24.95
  31. Druid and the Relational Model • Datasources are like tables

    ◦ Druid “lookups” apply to a common join use case ◦ Big, flat tables are common in SQL databases anyway, when analytical performance is critical • Benefits of offering SQL ◦ Developers and analysts know it ◦ Integration with 3rd party apps 31
  32. 32 Enter…

  33. Apache Calcite • SQL parser • Query optimizer • Query

    interpreter • JDBC server (Avatica) 33
  34. Apache Calcite • Widely used ◦ Druid ◦ Hive ◦

    Storm ◦ Samza ◦ Drill ◦ Phoenix ◦ Flink 34
  35. Apache Calcite 35 SQL SqlNode Parse tree RelNode Relational operator

    tree RelNode Optimized in target calling convention
  36. SQL query SELECT dim1, COUNT(*) FROM druid.foo WHERE dim1 IN

    ('abc', 'def', 'ghi') GROUP BY dim1 36
  37. SQL parse tree SELECT dim1, COUNT(*) FROM druid.foo WHERE dim1

    IN ('abc', 'def', 'ghi') GROUP BY dim1 37 Identifier “Select” keyword Operator Identifier Literal “Where” keyword “Group by” keyword
  38. Relational operators SELECT dim1, COUNT(*) FROM druid.foo WHERE dim1 IN

    ('abc', 'def', 'ghi') GROUP BY dim1 38 LogicalAggregate(group=[{0}], EXPR$1=[COUNT()]) LogicalProject(dim1=[$2]) LogicalFilter(condition=[OR(=($2, 'abc'), =($2, 'def'), =($2, 'ghi'))]) LogicalTableScan(table=[[druid, foo]])
  39. Query planner • Planner rules ◦ Match certain relational operator

    patterns ◦ Can transform one set of operators into another ◦ New set must have same behavior, but may have a different cost • HepPlanner (heuristic) ◦ Applies all matching rules • VolcanoPlanner (cost based) ◦ Applies rules while searching for low cost plans 39
  40. Using Calcite Calcite can be embedded or it can be

    used directly by end-users through built-in adapters. Druid SQL embeds Calcite. 40
  41. From NoSQL to SQL 41

  42. Native vs SQL { "queryType": "topN", "dataSource": “wikipedia”, "dimension": "countryName",

    "metric": { "type": "numeric", "metric": "added" }, "intervals": "2018-03-01/2018-03-06", "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "channel", "value": "#en.wikipedia", "extractionFn": null }, { "type": "not", "field": { "type": "selector", "dimension": "countryName", "value": "", "extractionFn": null } } ] }, "granularity": "all", "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" } ], "threshold": 5 } SELECT countryName, SUM(added) FROM wikipedia WHERE channel = '#en.wikipedia' AND countryName IS NOT NULL AND __time BETWEEN '2018-03-01' AND '2018-03-06' GROUP BY countryName ORDER BY SUM(added) DESC LIMIT 5 42
  43. Native vs SQL { "queryType": "topN", "dataSource": “wikipedia”, "dimension": "countryName",

    "metric": { "type": "numeric", "metric": "added" }, "intervals": "2018-03-01/2018-03-06", "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "channel", "value": "#en.wikipedia", "extractionFn": null }, { "type": "not", "field": { "type": "selector", "dimension": "countryName", "value": "", "extractionFn": null } } ] }, "granularity": "all", "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" } ], "threshold": 5 } SELECT countryName, SUM(added) FROM wikipedia WHERE channel = '#en.wikipedia' AND countryName IS NOT NULL AND __time BETWEEN '2018-03-01' AND '2018-03-06' GROUP BY countryName ORDER BY SUM(added) DESC LIMIT 5 43
  44. Native vs SQL { "queryType": "topN", "dataSource": “wikipedia”, "dimension": "countryName",

    "metric": { "type": "numeric", "metric": "added" }, "intervals": "2018-03-01/2018-03-06", "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "channel", "value": "#en.wikipedia", "extractionFn": null }, { "type": "not", "field": { "type": "selector", "dimension": "countryName", "value": "", "extractionFn": null } } ] }, "granularity": "all", "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" } ], "threshold": 5 } SELECT countryName, SUM(added) FROM wikipedia WHERE channel = '#en.wikipedia' AND countryName IS NOT NULL AND __time BETWEEN '2018-03-01' AND '2018-03-06' GROUP BY countryName ORDER BY SUM(added) DESC LIMIT 5 44
  45. Native vs SQL { "queryType": "topN", "dataSource": “wikipedia”, "dimension": "countryName",

    "metric": { "type": "numeric", "metric": "added" }, "intervals": "2018-03-01/2018-03-06", "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "channel", "value": "#en.wikipedia", "extractionFn": null }, { "type": "not", "field": { "type": "selector", "dimension": "countryName", "value": "", "extractionFn": null } } ] }, "granularity": "all", "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" } ], "threshold": 5 } SELECT countryName, SUM(added) FROM wikipedia WHERE channel = '#en.wikipedia' AND countryName IS NOT NULL AND __time BETWEEN '2018-03-01' AND '2018-03-06' GROUP BY countryName ORDER BY SUM(added) DESC LIMIT 5 45
  46. Native vs SQL { "queryType": "topN", "dataSource": “wikipedia”, "dimension": "countryName",

    "metric": { "type": "numeric", "metric": "added" }, "intervals": "2018-03-01/2018-03-06", "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "channel", "value": "#en.wikipedia", "extractionFn": null }, { "type": "not", "field": { "type": "selector", "dimension": "countryName", "value": "", "extractionFn": null } } ] }, "granularity": "all", "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" } ], "threshold": 5 } SELECT countryName, SUM(added) FROM wikipedia WHERE channel = '#en.wikipedia' AND countryName IS NOT NULL AND __time BETWEEN '2018-03-01' AND '2018-03-06' GROUP BY countryName ORDER BY SUM(added) DESC LIMIT 5 46
  47. Native vs SQL { "queryType": "topN", "dataSource": “wikipedia”, "dimension": "countryName",

    "metric": { "type": "numeric", "metric": "added" }, "intervals": "2018-03-01/2018-03-06", "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "channel", "value": "#en.wikipedia", "extractionFn": null }, { "type": "not", "field": { "type": "selector", "dimension": "countryName", "value": "", "extractionFn": null } } ] }, "granularity": "all", "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" } ], "threshold": 5 } SELECT countryName, SUM(added) FROM wikipedia WHERE channel = '#en.wikipedia' AND countryName IS NOT NULL AND __time BETWEEN '2018-03-01' AND '2018-03-06' GROUP BY countryName ORDER BY SUM(added) DESC LIMIT 5 47
  48. Native vs SQL { "queryType": "topN", "dataSource": “wikipedia”, "dimension": "countryName",

    "metric": { "type": "numeric", "metric": "added" }, "intervals": "2018-03-01/2018-03-06", "filter": { "type": "and", "fields": [ { "type": "selector", "dimension": "channel", "value": "#en.wikipedia", "extractionFn": null }, { "type": "not", "field": { "type": "selector", "dimension": "countryName", "value": "", "extractionFn": null } } ] }, "granularity": "all", "aggregations": [ { "type": "longSum", "name": "added", "fieldName": "added" } ], "threshold": 5 } SELECT countryName, SUM(added) FROM wikipedia WHERE channel = '#en.wikipedia' AND countryName IS NOT NULL AND __time BETWEEN '2018-03-01' AND '2018-03-06' GROUP BY countryName ORDER BY SUM(added) DESC LIMIT 5 48
  49. SQL to Native translation 49 PartialDruidQuery Scan Filter Project Aggregate

    Filter Project Sort Druid’s query execution pipeline
  50. SQL to Native translation SELECT dim1, COUNT(*) FROM druid.foo WHERE

    dim1 IN ('abc', 'def', 'ghi') GROUP BY dim1 50 LogicalAggregate(group=[{0}], EXPR$1=[COUNT()]) LogicalProject(dim1=[$2]) LogicalFilter(condition=[OR(=($2, 'abc'), =($2, 'def'), =($2, 'ghi'))]) LogicalTableScan(table=[[druid, foo]])
  51. SQL to Native translation 51 PartialDruidQuery Scan(table=[[druid, foo]]) Filter(condition=[OR(=($2, 'abc'),

    =($2, 'def'), =($2, 'ghi'))]) Project(dim1=[$2]) Aggregate(group=[{0}],EXPR$1=[COUNT()]) Filter Project Sort LogicalTableScan(table=[[druid, foo]]) LogicalFilter(condition=[OR(=($2, 'abc'), =($2, 'def'), =($2, 'ghi'))]) LogicalProject(dim1=[$2]) LogicalAggregate(group=[{0}],EXPR$1=[COUNT()])
  52. SQL to Native translation 52 PartialDruidQuery Filter Project Sort {

    "queryType" : "groupBy", "dataSource" : “foo”, "filter" : { "type" : "in", "dimension" : "dim1", "values" : [ "abc", "def", "ghi" ] }, "dimensions" : [ “dim1” ], "aggregations" : [ { "type" : "count", "name" : "a0" } ], } Scan(table=[[druid, foo]]) Filter(condition=[OR(=($2, 'abc'), =($2, 'def'), =($2, 'ghi'))]) Project(dim1=[$2]) Aggregate(group=[{0}],EXPR$1=[COUNT()]) toDruidQuery()
  53. SQL to Native translation • Calcite implements: ◦ SQL parser

    ◦ Basic set of rules for reordering and combining operators ◦ Rule-based optimizer frameworks • Druid implements: ◦ Build Calcite catalog from Druid datasources ◦ Cost functions guide reordering and combining operators ◦ Rules to push operators one-by-one into a PartialDruidQuery ◦ Convert PartialDruidQuery to DruidQuery 53
  54. SQL to Native translation Minimal performance overhead. Can even be

    faster due to transferring less data to the client! 54
  55. Challenges: Schema-lightness • Druid is schema-light (columns and their types

    are flexible) • SQL model has tables and columns with specific types • Druid native queries use type coercions at query time (e.g. user specifies: treat column XYZ as “string”) • Druid SQL populates catalog with latest metadata 55
  56. Challenges: Lookups Think back to lookups. 56 Lookup: “products” id

    name 212 Office chair 998 Coffee mug, 2-pack timestamp product_id country city gender age revenue 2030-01-01 212 US New York F 34 180.00 2030-01-01 998 FR Paris M 28 24.95
  57. Challenges: Lookups SQL experts may think of this as a

    JOIN. SELECT products.name, SUM(sales.revenue) FROM sales JOIN products ON sales.product_id = products.id GROUP BY products.name 57
  58. Challenges: Lookups Druid SQL does not support JOINs, but provides

    a “LOOKUP” function instead. SELECT LOOKUP(id, ‘products’) AS product_name SUM(sales.revenue) FROM sales GROUP BY product_name 58
  59. Challenges: Writing good queries SQL makes it surprisingly easy to

    write inefficient queries. Databases strive to optimize as best as they can. But the “EXPLAIN” tool is still essential. 59
  60. Future work • Druid features not supported in Druid SQL

    (as of 0.12) ◦ Multi-value dimensions ◦ Spatial filters ◦ Theta sketches (approx. set intersection, differences) • JOIN related ◦ Allow users to write lookups as a SQL JOIN ◦ Allow JOINs between two Druid datasources • Others: SQL window functions, SQL UNION, GROUPING SETS 60
  61. Try this at home 61

  62. Download Imply distribution: https://imply.io/get-started Druid community site: http://druid.io/ 62

  63. Contribute 63 http://druid.io/community https://github.com/druid-io/druid

  64. Contribute 64 Druid has recently begun migration to the Apache

    Incubator. Apache Druid is coming soon!