Apache Pinot vs. Druid: Which Real-Time Analytics Database Should You Choose?

If you need fast analytics on live data (like dashboards or real-time reports), two open-source databases stand out: Apache Pinot and Apache Druid. Both are built for low-latency queries at scale, but they have different strengths.

 

1. What Are Pinot and Druid?

Both are real-time OLAP databases, meaning:

 

·      They handle streaming data (e.g., clicks, transactions) + batch data (historical logs).

·      Optimized for fast aggregations (e.g., "How many users visited today?").

·      Support high concurrency (100s–1000s of queries per second).

 

2. Performance: Which Is Faster?

Pinot:

·      Excels at high-concurrency queries (e.g., 100,000+ queries/sec).

·      Used by companies like Uber Eats and Stripe for real-time dashboards.

·      Requires manual tuning for best performance.

 

Druid:

·      Handles mixed workloads better (e.g., dashboards + ad-hoc queries).

·      Used by Netflix and Salesforce for analytics.

·      May slow down under extreme concurrency.

 

Verdict:

·      Need ultra-fast, predictable queries? Pinot might win.

·      Need flexibility + ease of use? Druid could be better.

 

3. Indexing (How Data Is Organized)

Pinot:

·      You choose indexes manually (like picking tools for a toolbox).

·      More control but harder to set up.

 

Druid:

·      Automatic indexing (it picks the best method for you).

·      Simpler but less customizable.

 

Beginners might prefer Druid (less manual work), and experts might prefer Pinot (more tuning options).

 

 

4. Data Ingestion (Loading Data)

Druid:

·      Supports SQL-based ingestion (transform data while loading).

·      Example: You can JOIN tables during ingestion.

 

Pinot:

·      Needs pre-processed data (e.g., via Spark or Flink).

·      Less flexible for complex transformations.

 

 

5. Which Should You Choose?

Pick Druid if you:

·      Want auto-indexing (less manual work).

·      Need SQL-based data transformations.

·      Have mixed workloads (dashboards + ad-hoc queries).

 

Pick Pinot if you:

·      Need extreme speed (100K+ queries/sec).

·      Can manually optimize indexes.

·      Don’t need complex transformations during ingestion.

 

Previous                                                    Next                                                    Home

Querying Apache Druid via HTTP APIs

Apache Druid supports both SQL and native query APIs. Understanding both is crucial for flexible and optimized querying depending on your use case. In this post, we'll walk through how to run both SQL and native queries using curl, leveraging a sample sales dataset.

For example, I onboarded following sales_data to Druid.

 

sales_data.csv

timestamp,product,city,total_sales
2025-04-01T10:00:00Z,Laptop,Delhi,300
2025-04-01T10:00:00Z,Laptop,Delhi,200
2025-04-01T11:00:00Z,Tablet,Mumbai,150
2025-04-01T11:00:00Z,Tablet,Mumbai,50
2025-04-01T12:00:00Z,Mobile,Bengaluru,200
2025-04-01T13:00:00Z,Laptop,Hyderabad,250
2025-04-01T14:00:00Z,Tablet,Chennai,180
2025-04-01T15:00:00Z,Mobile,Pune,220
2025-04-01T15:00:00Z,Mobile,Pune,80

 

 

1. SQL Query via /druid/v2/sql

Druid’s SQL endpoint allows for familiar querying syntax:

 

Example SQL Query

Get total sales per product

SELECT product, SUM(total_sales) AS total_sales
FROM sales_data
GROUP BY product
ORDER BY total_sales DESC

 

 

Execute Using curl

Save the following to a file sql_query.json.

 

sql_query.json 

{
  "query": "SELECT product, SUM(total_sales) AS total_sales FROM sales_data GROUP BY product ORDER BY total_sales DESC"
}

 

Run the query

curl -X POST http://localhost:8888/druid/v2/sql -H 'Content-Type:application/json' -d @sql_query.json

$curl -X POST http://localhost:8888/druid/v2/sql -H 'Content-Type:application/json' -d @sql_query.json
[{"product":"Laptop","total_sales":750.0},{"product":"Mobile","total_sales":500.0},{"product":"Tablet","total_sales":380.0}]

You can pass this curl output to jq command to pretty print.

$curl -X POST http://localhost:8888/druid/v2/sql -H 'Content-Type:application/json' -d @sql_query.json | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   246  100   125  100   121   9939   9621 --:--:-- --:--:-- --:--:-- 20500
[
  {
    "product": "Laptop",
    "total_sales": 750.0
  },
  {
    "product": "Mobile",
    "total_sales": 500.0
  },
  {
    "product": "Tablet",
    "total_sales": 380.0
  }
]

 

You can even pass the payload directly to druid/v2/sql API.

curl -X POST http://localhost:8888/druid/v2/sql -H 'Content-Type:application/json' -d '{"query":"SELECT product, SUM(total_sales) AS total_sales FROM sales_data GROUP BY product ORDER BY total_sales DESC"}'

$curl -X POST http://localhost:8888/druid/v2/sql -H 'Content-Type:application/json' -d '{"query":"SELECT product, SUM(total_sales) AS total_sales FROM sales_data GROUP BY product ORDER BY total_sales DESC"}' | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   243  100   125  100   118   9806   9257 --:--:-- --:--:-- --:--:-- 20250
[
  {
    "product": "Laptop",
    "total_sales": 750.0
  },
  {
    "product": "Mobile",
    "total_sales": 500.0
  },
  {
    "product": "Tablet",
    "total_sales": 380.0
  }
]

2. Native Query via /druid/v2

Native queries are powerful, fine-grained JSON-based constructs used for more complex operations.

 

Example Native Query

Equivalent native groupBy query for total sales per product:

 

Save to native_query.json.

 

Execute Using curl

 

native_query.json

{
  "queryType": "groupBy",
  "dataSource": "sales_data",
  "granularity": "all",
  "dimensions": ["product"],
  "aggregations": [
    {
      "type": "longSum",
      "name": "total_sales",
      "fieldName": "total_sales"
    }
  ],
  "intervals": ["2025-04-01T00:00:00.000Z/2025-04-02T00:00:00.000Z"]
}

curl -X POST http://localhost:8888/druid/v2/?pretty -H 'Content-Type:application/json' -d @native_query.json

$curl -X POST http://localhost:8888/druid/v2/?pretty -H 'Content-Type:application/json' -d @native_query.json
[ {
  "version" : "v1",
  "timestamp" : "2025-04-01T00:00:00.000Z",
  "event" : {
    "product" : "Laptop",
    "total_sales" : 750
  }
}, {
  "version" : "v1",
  "timestamp" : "2025-04-01T00:00:00.000Z",
  "event" : {
    "product" : "Mobile",
    "total_sales" : 500
  }
}, {
  "version" : "v1",
  "timestamp" : "2025-04-01T00:00:00.000Z",
  "event" : {
    "product" : "Tablet",
    "total_sales" : 380
  }
} ]

 

Passing Native Query payload inline

curl -X POST http://localhost:8888/druid/v2/?pretty -H 'Content-Type:application/json' -d '{"queryType":"groupBy","dataSource":"sales_data","granularity":"all","dimensions":["product"],"aggregations":[{"type":"longSum","name":"total_sales","fieldName":"sales"}],"intervals":["2025-04-01T00:00:00.000Z/2025-04-02T00:00:00.000Z"]}'

$ curl -X POST http://localhost:8888/druid/v2/?pretty -H 'Content-Type:application/json' -d '{"queryType":"groupBy","dataSource":"sales_data","granularity":"all","dimensions":["product"],"aggregations":[{"type":"longSum","name":"total_sales","fieldName":"sales"}],"intervals":["2025-04-01T00:00:00.000Z/2025-04-02T00:00:00.000Z"]}'
[ {
  "version" : "v1",
  "timestamp" : "2025-04-01T00:00:00.000Z",
  "event" : {
    "product" : "Laptop",
    "total_sales" : null
  }
}, {
  "version" : "v1",
  "timestamp" : "2025-04-01T00:00:00.000Z",
  "event" : {
    "product" : "Mobile",
    "total_sales" : null
  }
}, {
  "version" : "v1",
  "timestamp" : "2025-04-01T00:00:00.000Z",
  "event" : {
    "product" : "Tablet",
    "total_sales" : null
  }
} ]

 

Previous                                                    Next                                                    Home

Exploring Apache Druid Native Queries

Apache Druid is a real-time analytics database designed for fast slice-and-dice analytics on large datasets. While SQL is a convenient way to query data in Druid, native queries provide greater flexibility, control, and performance tuning.

 

In this blog post, we’ll explore how to use Druid’s scan native query to extract raw event-level data from a sales_data datasource. We'll walk through examples using real-world sales data.

 

Sample Data: sales_data

I ingested the following CSV into Druid with the name sales_data.

timestamp,product,city,total_sales
2025-04-01T10:00:00Z,Laptop,Delhi,300
2025-04-01T10:00:00Z,Laptop,Delhi,200
2025-04-01T11:00:00Z,Tablet,Mumbai,150
2025-04-01T11:00:00Z,Tablet,Mumbai,50
2025-04-01T12:00:00Z,Mobile,Bengaluru,200
2025-04-01T13:00:00Z,Laptop,Hyderabad,250
2025-04-01T14:00:00Z,Tablet,Chennai,180
2025-04-01T15:00:00Z,Mobile,Pune,220
2025-04-01T15:00:00Z,Mobile,Pune,80

Each row represents a product sale at a specific timestamp, city, and quantity.

 

1. Scan Query in Druid

The scan query in Druid is used to fetch raw data from your datasource. Unlike groupBy or timeseries queries that aggregate data, scan queries return unprocessed rows in a customizable format.

 

Here’s a basic scan query that fetches all data from the sales_data datasource between two timestamps.

 

{
  "queryType": "scan",
  "dataSource": "sales_data",
  "resultFormat": "compactedList",
  "columns": ["__time", "product", "city", "total_sales"],
  "intervals": ["2025-04-01T10:00:00Z/2025-04-01T16:00:00Z"],
  "batchSize": 35575,
  "limit": 100
}

 

 

Following table summarizes the native query payload.

 

Field	Purpose
queryType	Specifies the query type. In this case, scan
dataSource	The name of the Druid datasource (your table)
resultFormat	Format of the result: compactedList, list, or valueVector
columns	List of columns to include in the response
intervals	Time interval filter, ISO 8601 format
batchSize	Number of rows fetched internally per scan
limit	Maximum number of rows returned in the result

 

Output json looks like below.

 

Output 

[
  {
    "segmentId": "sales_data_2025-04-01T00:00:00.000Z_2025-04-02T00:00:00.000Z_2025-04-18T08:18:17.761Z",
    "columns": [
      "__time",
      "product",
      "city",
      "total_sales"
    ],
    "events": [
      [
        1743501600000,
        "Laptop",
        "Delhi",
        300
      ],
      [
        1743501600000,
        "Laptop",
        "Delhi",
        200
      ],
      [
        1743505200000,
        "Tablet",
        "Mumbai",
        150
      ],
      [
        1743505200000,
        "Tablet",
        "Mumbai",
        50
      ],
      [
        1743508800000,
        "Mobile",
        "Bengaluru",
        200
      ],
      [
        1743512400000,
        "Laptop",
        "Hyderabad",
        250
      ],
      [
        1743516000000,
        "Tablet",
        "Chennai",
        180
      ],
      [
        1743519600000,
        "Mobile",
        "Pune",
        220
      ],
      [
        1743519600000,
        "Mobile",
        "Pune",
        80
      ]
    ],
    "rowSignature": [
      {
        "name": "__time",
        "type": "LONG"
      },
      {
        "name": "product",
        "type": "STRING"
      },
      {
        "name": "city",
        "type": "STRING"
      },
      {
        "name": "total_sales",
        "type": "DOUBLE"
      }
    ]
  }
]

  Example 1:  Retrieve all rows where sales happened in a specific hour

 

{
  "queryType": "scan",
  "dataSource": "sales_data",
  "resultFormat": "compactedList",
  "columns": ["__time", "product", "city", "total_sales"],
  "intervals": ["2025-04-01T11:00:00Z/2025-04-01T12:00:00Z"]
}

 

Example 2: Filter specific columns only (e.g., product, city and total_sales) 

{
  "queryType": "scan",
  "dataSource": "sales_data",
  "resultFormat": "list",
  "columns": ["product", "city", "total_sales"],
  "intervals": ["2025-04-01T11:00:00Z/2025-04-01T12:00:00Z"]
}

 

Example 3: Limit the number of records returned.

{
  "queryType": "scan",
  "dataSource": "sales_data",
  "resultFormat": "compactedList",
  "columns": ["__time", "product", "total_sales"],
  "intervals": ["2025-04-01T10:00:00Z/2025-04-01T16:00:00Z"],
  "limit": 3
}

 

Example 4: Using resultFormat as list.

{
  "queryType": "scan",
  "dataSource": "sales_data",
  "resultFormat": "list",
  "columns": ["__time", "city", "total_sales"],
  "intervals": ["2025-04-01T13:00:00Z/2025-04-01T16:00:00Z"]
}

2. Druid GroupBy Native Queries with Filters

While scan queries in Druid return raw data, groupBy queries are used to aggregate data much like SQL GROUP BY. This is useful when you want to answer questions like:

 

·      What is the total sales per product?

·      What is the sales count by city per hour?

·      How much did each city sell after 2 PM?

 

 

Example 1: Total Sales by Product

{
  "queryType": "groupBy",
  "dataSource": "sales_data",
  "intervals": ["2025-04-01T10:00:00Z/2025-04-01T16:00:00Z"],
  "granularity": "all",
  "dimensions": ["product"],
  "aggregations": [
    { "type": "longSum", "name": "sales_by_product", "fieldName": "total_sales" }
  ]
}

 

Example 2: Sales per City after 1 PM

{
  "queryType": "groupBy",
  "dataSource": "sales_data",
  "intervals": ["2025-04-01T13:00:00Z/2025-04-01T16:00:00Z"],
  "granularity": "all",
  "dimensions": ["city"],
  "aggregations": [
    { "type": "longSum", "name": "sales_per_city", "fieldName": "total_sales" }
  ]
}

Example 3: Add a WHERE Clause (Filter by product = 'Laptop')

{
  "queryType": "groupBy",
  "dataSource": "sales_data",
  "intervals": ["2025-04-01T10:00:00Z/2025-04-01T16:00:00Z"],
  "granularity": "all",
  "filter": {
    "type": "selector",
    "dimension": "product",
    "value": "Laptop"
  },
  "dimensions": ["city"],
  "aggregations": [
    { "type": "longSum", "name": "total_sales", "fieldName": "total_sales" }
  ]
}

 

Previous                                                    Next                                                    Home