Monday, March 27, 2023
HomeBig DataThe best way to Deal with Database Joins in Apache Druid vs...

The best way to Deal with Database Joins in Apache Druid vs Rockset

Apache Druid is a real-time analytics database, offering enterprise intelligence to drive clickstream analytics, analyze threat, monitor community efficiency, and extra.

When Druid was launched in 2011, it didn’t initially assist joins, however a be a part of function was added in 2020. That is vital as a result of it’s usually useful to incorporate fields from a number of Druid information — or a number of tables in a normalized knowledge set — in a single question, offering the equal of an SQL take part a relational database.

This text focuses on implementing database joins in Apache Druid, seems at some limitations builders face, and explores potential options.


We’ll begin by acknowledging that the Druid documentation says query-time joins aren’t beneficial and that, if potential, you must be a part of your knowledge earlier than loading it into Druid. Should you’ve labored with relational databases, chances are you’ll acknowledge this pre-joining idea by one other title: denormalization.

We don’t have house to dive into denormalization in depth, nevertheless it boils all the way down to figuring out forward of time which fields you’d like to incorporate throughout a number of tables, making a single desk that accommodates all of these fields, after which populating that desk with knowledge. This removes the necessity to do a runtime be a part of as a result of the entire knowledge you want is on the market in a single desk.

Denormalization is nice when you realize upfront what knowledge you need to question. This doesn’t at all times match real-world wants, nonetheless. If you have to do a wide range of ad-hoc queries on knowledge that spans many tables, denormalization could also be a poor match. It’s additionally less-than-ideal once you want true real-time querying as a result of the time wanted to denormalize knowledge earlier than making it out there to Druid might introduce unacceptable latency.

If we do have to carry out a query-time take part Druid, what are our choices?

Forms of Database Joins in Druid

There are two approaches to Druid database joins: be a part of operators and query-time lookups.

Be a part of Operators

Be a part of operators join two or extra datasources equivalent to knowledge information and Druid tables. Primarily, datasources in Apache Druid are issues that you may question. You may be a part of datasources in a approach just like joins in a relational database, and you may even use an SQL question to take action. You may stack joins on prime of one another to hitch many datasources, enabling faster execution and permitting for higher question efficiency.

Druid helps two forms of queries: native queries, and SQL queries — and you are able to do joins with each of them. Native queries are specified utilizing JSON, and SQL queries are similar to the sorts of SQL queries out there on a relational database.

Joins in SQL Queries

Internally, Druid interprets SQL queries into native queries utilizing an information dealer, and any Druid SQL JOIN operators that the native layer can deal with are then translated into be a part of datasources from which Druid extracts knowledge. A Druid SQL be a part of takes the shape:

 <fields from tables>
FROM <base desk>
[INNER | OUTER] JOIN <different desk> ON <be a part of situation>

The primary vital factor to notice is that as a result of broadcast hash-join algorithm Druid makes use of, the bottom desk should slot in reminiscence. If the bottom desk you need to be a part of in opposition to is simply too massive to slot in reminiscence, see if denormalization is an possibility. If not, you’ll have so as to add extra reminiscence to the machine Druid is working on, or look to a special datastore.

The be a part of situation in an SQL be a part of question have to be an equality that tells Druid which columns in every of the 2 tables include an identical knowledge so Druid can decide which rows to mix knowledge from. A easy be a part of situation would possibly appear to be = pet.parent_id. You too can use features within the be a part of situation equality, for instance LOWER(t1.x) = t2.x.

Word that Druid SQL is extra permissive than native Druid queries. In some circumstances, Druid can’t translate a SQL be a part of right into a single native question – so a SQL be a part of might lead to a number of native subqueries to return the specified outcomes. As an illustration, foo OUTER JOIN customers ON = UPPER(customers.def) is an SQL be a part of that can’t be straight translated to a be a part of datasource as a result of there may be an expression on the proper aspect as an alternative of straightforward column entry.

Subqueries carry a considerable efficiency penalty, so use warning when specifying advanced be a part of situations. Normally, Druid buffers the outcomes from subqueries in reminiscence within the knowledge dealer, and a few extra processing happens within the dealer. Subqueries with massive end result units could cause bottlenecks or run into reminiscence limits within the dealer — which is another excuse to keep away from subqueries if in any respect potential.

Bear in mind that Druid SQL doesn’t assist the next SQL be a part of options:

  • Be a part of between two native knowledge sources, together with tables and lookups
  • Be a part of situations that aren’t equal between expressions from each side
  • Be a part of situations with a relentless variable contained in the situation

We’ll end up with an entire instance of a Druid be a part of question:

The next is an instance of an SQL be a part of.

   shop_to_product.v AS product,
   SUM(purchases.income) AS product_revenue
   INNER JOIN lookup.shop_to_product ON purchases.retailer = shop_to_product.okay

Be a part of Datasources in Native Queries

Subsequent, we’ll study tips on how to create be a part of datasources in native queries. We’re assuming you’re already conversant in common native JSON queries in Druid.

The next properties characterize be a part of knowledge sources in native queries:

Left — The left-hand aspect of the be a part of have to be a desk, be a part of, lookup, question, or inline datasource. Alternatively, the left-hand knowledge supply will be one other be a part of, connecting a number of knowledge sources.

Proper — The precise-hand knowledge supply have to be a lookup, question, or inline datasource.

Proper Prefix — It is a string prefix positioned on columns from the right-hand knowledge supply to keep away from a battle with columns from the left-hand aspect. The string have to be non-empty.

Situation — The situation have to be an equality that compares the information supply from the left-hand aspect to these from the right-hand aspect.

Be a part of kind — INNER or LEFT.

The next is an instance of a Druid native be a part of:

  "QueryType": "GroupBy",
    "dataSource": {
      "kind": "be a part of",
      "left": "purchases",
      "proper": {
      "kind": "lookup",
      "lookup": "shop_to_product"
      "rightPrefix": "r.",
      "situation": "store == "r.okay"",
      "joinType": "INNER"
    "intervals": ["0000/3000"],
    "granularity": "all",
    "dimensions": [
      { "type": "default", "outputName": "product", "dimension": "r.v" }
    "aggregations": [
      { "type": "longSum", "name": "product_revenue", "fieldName": "revenue" }

This can return a end result set exhibiting cumulative income for every product in a store.

Question-Time Lookups

Question-time lookups are pre-defined key-value associations that reside in-memory on all servers in a Druid cluster. With query-time lookups, Druid replaces knowledge with new knowledge throughout runtime. They’re a particular case of Druid’s normal lookup performance, and though we don’t have house to cowl lookups in minute element, let’s stroll by them briefly.

Question-time lookups assist one-to-one matching of distinctive values, equivalent to person privilege ID and person privilege title. For instance, P1-> Delete, P2-> Edit, P3-> View. In addition they assist use circumstances the place the operation should match a number of values to a single worth. Right here’s a case the place person privilege IDs map to a single person account: P1-> Admin, P2-> Admin, P3-> Admin.

One benefit of query-time lookups is that they don’t have historical past. As a substitute, they use present knowledge as they replace. Which means if a specific person privilege ID is mapped to a person administrator (for instance, P1-> David_admin), and a brand new administrator is available in, a lookup question of the privilege ID returns the title of the brand new administrator.

One disadvantage of query-time lookups is that they don’t assist time-range-sensitive knowledge lookups.

Some Disadvantages of Druid Be a part of Operators

Though Druid does assist database joins, they’re comparatively new and have some drawbacks.

Knowledge sources on the left-hand aspect of joins should slot in reminiscence. Druid shops subquery leads to reminiscence to allow speedy retrieval. Additionally, you employ a broadcast hash-join algorithm to implement Druid joins. So subqueries with massive end result units occupy (and will exhaust) the reminiscence.

Not all datasources assist joins. Druid be a part of operators don’t assist all joins. One instance of that is non-broadcast hash joins. Neither do be a part of situations assist columns of a number of dimensional values.

A single be a part of question might generate a number of (presumably sluggish) subqueries. You can’t implement some SQL queries with Druid’s native language. This implies you have to first add them to a subquery to make them executable. This generally generates a number of subqueries that eat a number of reminiscence, inflicting a efficiency bottleneck.

For these causes, Druid’s documentation recommends in opposition to working joins at question time.

Rockset In comparison with Apache Druid

Though Druid has many helpful options for real-time analytics, it presents a few challenges, equivalent to an absence of assist for all database joins and vital efficiency overhead when doing joins. Rockset addresses these challenges with one in all its core options: high-performance SQL joins.

In supporting full-featured SQL, Rockset was designed with be a part of efficiency in thoughts. Rockset partitions the joins, and these partitions run in parallel on distributed Aggregators that may be scaled out if wanted. It additionally has a number of methods of performing joins:

  • Hash Be a part of
  • Nested loop Be a part of
  • Broadcast Be a part of
  • Lookup Be a part of

The power to hitch knowledge in Rockset is especially helpful when analyzing knowledge throughout completely different database techniques and dwell knowledge streams. Rockset can be utilized, for instance, to hitch a Kafka stream with dimension tables from MySQL. In lots of conditions, pre-joining the information just isn’t an possibility as a result of knowledge freshness is vital or the flexibility to carry out advert hoc queries is required.

You may consider Rockset as a substitute for Apache Druid, with improved flexibility and manageability. Rockset allows you to carry out schemaless ingestion and question that knowledge instantly, with out having to denormalize your knowledge or keep away from runtime joins.

In case you are trying to reduce knowledge and efficiency engineering wanted for real-time analytics, Rockset could also be a more sensible choice.


Subsequent Steps

Apache Druid processes excessive volumes of real-time knowledge in on-line analytical processing functions. The platform presents a variety of real-time analytics options, equivalent to low-latency knowledge ingestion. Nevertheless, it additionally has its shortcomings, like not supporting all types of database joins.

Rockset helps overcome Druid’s restricted be a part of assist. As a cloud-native, real-time indexing database, Rockset presents each velocity and scale and helps a variety of options, together with joins. Begin a free trial as we speak to expertise essentially the most versatile real-time analytics within the cloud.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments