Skip to content

Apache Druid for time series data hands-on

Preamble

Apache Druid was one time series database I wanted to give a try following my testing with QuestDB (http) and the picture of ranking from DB-Engines::

questdb01
questdb01

Apache Druid is an open source TSDB (Time Series DataBase). From the Apache foundation there is also Apache IoTDB and, so far, it’s hard to point out the differences between the two. In a previous version of IoTDB they were saying that Apache Druid focuses on data analysis with OLAP workload (https://iotdb.apache.org/UserGuide/V0.13.x/Reference/TSDB-Comparison.html), but this chapter has gone in more recent release of Apache IoTDB… One cool feature I have seen in Apache IoTDB is its direct integration with Apache Spark to do things like:

import org.apache.iotdb.spark.db._

val df = spark.read.format("org.apache.iotdb.spark.db")
  .option("user", "root")
  .option("password", "root")
  .option("url", "jdbc:iotdb://127.0.0.1:6667/")
  .option("sql", "select ** from root") // query SQL
  .option("lowerBound", "0") // lower timestamp bound
  .option("upperBound", "100000000") // upper timestamp bound
  .option("numPartition", "5") // number of partitions
  .load

df.printSchema()

df.show()

The deployment I will do, to keep things simple, is single server deployment but you can build cluster architecture for high availability and even with containers.

My test server is running Red Hat Enterprise Linux release 8.10 (Ootpa), has 12 cores and 64GB of memory. I have used Java 17.0.14 and Apache Druid release 32.0.1.

Apache Druid installation

As suggested Java 11 or Java 17 is required, so:

[root@server01 ~]# dnf install -y java-17-openjdk.x86_64
[root@server01 ~]# java -version
openjdk version "1.8.0_432"
OpenJDK Runtime Environment (build 1.8.0_432-b06)
OpenJDK 64-Bit Server VM (build 25.432-b06, mixed mode)

On RedHat you need to change the default Java for the server with:

[root@server01 ~]# alternatives --config java

There are 2 programs which provide 'java'.

  Selection    Command
-----------------------------------------------
*+ 1           java-1.8.0-openjdk.x86_64 (/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.432.b06-2.el8.x86_64/jre/bin/java)
   2           java-17-openjdk.x86_64 (/usr/lib/jvm/java-17-openjdk-17.0.14.0.7-3.el8.x86_64/bin/java)

Enter to keep the current selection[+], or type selection number: 2
[root@server01 ~]# java -version
openjdk version "17.0.14" 2025-01-21 LTS
OpenJDK Runtime Environment (Red_Hat-17.0.14.0.7-1) (build 17.0.14+7-LTS)
OpenJDK 64-Bit Server VM (Red_Hat-17.0.14.0.7-1) (build 17.0.14+7-LTS, mixed mode, sharing)

Download the tar.gz release and I have chosen to unzip it to a dedicated VxFS directory. I have also created a dedicated druid account in Linux dba group:

[druid@server01 ~]$ cd /druid/
[druid@server01 druid]$ ll
total 665600
-rw-r----- 1 druid dba  681573751 Apr 11 17:26 apache-druid-32.0.1-bin.tar.gz
drwx------ 2 root  root        96 Apr 11 17:22 lost+found
[druid@server01 druid]$ gtar xvf apache-druid-32.0.1-bin.tar.gz

I have also added below environment variables to my druid:dba account:

User specific environment and startup programs
export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-17.0.14.0.7-3.el8.x86_64
export PATH=$PATH:/druid/apache-druid-32.0.1/bin

Then start with:

[druid@server01 ~]$ start-druid &

You can confirm it works by accessing to the graphical interface at server:8888:

apache_druid01
apache_druid01

Apache Druid data ingestion

Same as for the other time series database flavor I plan to load the New York City yellow cab trips information for full year 2023. The nice starting point with Apache Druid is that I can directly read the original Parquet files. I have generated, first, this SQL script with the graphical interface:

REPLACE INTO "trips"
OVERWRITE ALL
WITH "ext" AS (
  SELECT *
  FROM TABLE(
    EXTERN(
      '{"type":"local","baseDir":"/u01/nyc","filter":"*.parquet"}',
      '{"type":"parquet"}'
    )
  ) EXTEND ("DOLocationID" BIGINT, "RatecodeID" BIGINT, "fare_amount" DOUBLE, "tpep_dropoff_datetime" BIGINT, "congestion_surcharge" DOUBLE, "VendorID" BIGINT, "passenger_count" BIGINT, "tolls_amount" DOUBLE, 
  "improvement_surcharge" BIGINT, "trip_distance" DOUBLE, "store_and_fwd_flag" VARCHAR, "payment_type" BIGINT, "total_amount" DOUBLE, "extra" DOUBLE, "tip_amount" DOUBLE, "mta_tax" DOUBLE, "airport_fee" DOUBLE,
   "tpep_pickup_datetime" BIGINT, "PULocationID" BIGINT)
)
SELECT
  MILLIS_TO_TIMESTAMP("tpep_pickup_datetime") AS "__time",
  "DOLocationID",
  "RatecodeID",
  "fare_amount",
  MILLIS_TO_TIMESTAMP("tpep_dropoff_datetime") AS "tpep_dropoff_datetime",
  "congestion_surcharge",
  "VendorID",
  "passenger_count",
  "tolls_amount",
  "improvement_surcharge",
  "trip_distance",
  "store_and_fwd_flag",
  "payment_type",
  "total_amount",
  "extra",
  "tip_amount",
  "mta_tax",
  "airport_fee",
  "PULocationID"
FROM "ext"
PARTITIONED BY MONTH

Remark:
The __time column is mandatory for Apache Druid:

Druid parses the primary timestamp based on the timestampSpec configuration at ingestion time. Regardless of the source field for the primary timestamp, Druid always stores the timestamp in the __time column in your Druid datasource.

That I customized in below to match what I have done with other database flavors:

replace into trips
overwrite all
with "parquets" as (
  select *
  from table(
    extern(
      '{"type":"local","baseDir":"/u01/nyc","filter":"*.parquet"}',
      '{"type":"parquet"}'
    )
  ) extend ("DOLocationID" bigint, "RatecodeID" bigint, "fare_amount" double, "tpep_dropoff_datetime" bigint, "congestion_surcharge" double, "VendorID" bigint, "passenger_count" bigint, "tolls_amount" double, 
  "improvement_surcharge" bigint, "trip_distance" double, "store_and_fwd_flag" VARCHAR, "payment_type" bigint, "total_amount" double, "extra" double, "tip_amount" double, "mta_tax" double, "airport_fee" double,
   "tpep_pickup_datetime" bigint, "PULocationID" bigint)
)
select
  millis_to_timestamp("tpep_pickup_datetime") as "__time",
  millis_to_timestamp("tpep_dropoff_datetime") as "ended_at",
  "passenger_count",
  "trip_distance" as "distance",
  "tip_amount",
  "total_amount"
from "parquets"
partitioned by month;

I have tried to execute it with dsql but got. Dsql log is in /druid/apache-druid-32.0.1/log/broker.log. Not like the wb console, error messages do not appear afer each failed execution:

2025-04-14T13:42:54,567 WARN [sql[aa2401a7-378a-4874-9ae7-9df8848d3269]] org.apache.druid.sql.http.SqlResource - Exception while processing sqlQueryId[aa2401a7-378a-4874-9ae7-9df8848d3269] (org.apache.druid.error.DruidException: REPLACE operations are not supported by the requested SQL engine [native].  Consider using MSQ.)

Reading the official documentation I found:

You can execute SQL statements using the MSQ task engine through the Query view in the web console or through the /druid/v2/sql/task API.

I have tried with the web console and it works, for command line option let’s try the HTTP API. I first create an ingestion_trips.sql file containing my REPLACE command reading Parquet files::

[druid@server01 ~]$ cat ingestion_trips.sql
replace into trips
overwrite all
with "parquets" as (
  select *
  from table(
    extern(
      '{"type":"local","baseDir":"/u01/nyc","filter":"*.parquet"}',
      '{"type":"parquet"}'
    )
  ) extend ("DOLocationID" bigint, "RatecodeID" bigint, "fare_amount" double, "tpep_dropoff_datetime" bigint, "congestion_surcharge" double, "VendorID" bigint, "passenger_count" bigint, "tolls_amount" double,
  "improvement_surcharge" bigint, "trip_distance" double, "store_and_fwd_flag" VARCHAR, "payment_type" bigint, "total_amount" double, "extra" double, "tip_amount" double, "mta_tax" double, "airport_fee" double,
   "tpep_pickup_datetime" bigint, "PULocationID" bigint)
)
select
  millis_to_timestamp("tpep_pickup_datetime") as "__time",
  millis_to_timestamp("tpep_dropoff_datetime") as "ended_at",
  "passenger_count",
  "trip_distance" as "distance",
  "tip_amount",
  "total_amount"
from "parquets"
partitioned by month

Then submit this SQL file using jq command (see delete paragraph on how to install it). Do not be too generous with the maximum number of tasks. I have tried setting it equal to number of cores and it created some glitches (bug ?), not to say it has delayed the execution, so keeping half number of cores:

[druid@server01 ~]$ curl --location --request POST 'http://server01.domain.com:8888/druid/v2/sql/task' \
> --header 'Content-Type: application/json' \
> --data-raw "$(jq -n --arg query "$(cat ingestion_trips.sql)" '{query: $query, context: {maxNumTasks: 6}}')"
{"taskId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","state":"RUNNING"}

Get the status with (use the query id from above command):

[druid@server01 ~]$ curl --location --request GET 'http://server01.domain.com:8888/druid/indexer/v1/task/query-4ac9ff37-88da-425e-a5a2-216bee04136b/status'
{"task":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","status":{"id":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","groupId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","type":"query_controller","createdTime":"2025-04-14T15:29:59.752Z","queueInsertionTime":"1970-01-01T00:00:00.000Z","statusCode":"RUNNING","status":"RUNNING","runnerStatusCode":"RUNNING","duration":-1,"location":{"host":"localhost","port":8100,"tlsPort":-1},"dataSource":"trips","errorMsg":null}}

Get a report with:

[druid@server01 ~]$ curl --location --request GET 'http://server01.domain.com:8888/druid/indexer/v1/task/query-4ac9ff37-88da-425e-a5a2-216bee04136b/reports'
{"multiStageQuery":{"type":"multiStageQuery","taskId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","payload":{"status":{"status":"RUNNING","startTime":"2025-04-14T15:30:04.387Z","durationMs":22370,"workers":{"0":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker0_0","state":"RUNNING","durationMs":-1,"pendingMs":93}],"1":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker1_0","state":"RUNNING","durationMs":-1,"pendingMs":202}],"2":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker2_0","state":"RUNNING","durationMs":-1,"pendingMs":187}],"3":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker3_0","state":"RUNNING","durationMs":-1,"pendingMs":200}],"4":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker4_0","state":"RUNNING","durationMs":-1,"pendingMs":186}]},"pendingTasks":0,"runningTasks":6},"stages":[{"stageNumber":0,"definition":{"id":"query-4ac9ff37-88da-425e-a5a2-216bee04136b_0","input":[{"type":"external","inputSource":{"type":"local","baseDir":"/u01/nyc","filter":"*.parquet"},"inputFormat":{"type":"parquet"},"signature":[{"name":"DOLocationID","type":"LONG"},{"name":"RatecodeID","type":"LONG"},{"name":"fare_amount","type":"DOUBLE"},{"name":"tpep_dropoff_datetime","type":"LONG"},{"name":"congestion_surcharge","type":"DOUBLE"},{"name":"VendorID","type":"LONG"},{"name":"passenger_count","type":"LONG"},{"name":"tolls_amount","type":"DOUBLE"},{"name":"improvement_surcharge","type":"LONG"},{"name":"trip_distance","type":"DOUBLE"},{"name":"store_and_fwd_flag","type":"STRING"},{"name":"payment_type","type":"LONG"},{"name":"total_amount","type":"DOUBLE"},{"name":"extra","type":"DOUBLE"},{"name":"tip_amount","type":"DOUBLE"},{"name":"mta_tax","type":"DOUBLE"},{"name":"airport_fee","type":"DOUBLE"},{"name":"tpep_pickup_datetime","type":"LONG"},{"name":"PULocationID","type":"LONG"}]}],"processor":{"type":"scan","query":{"queryType":"scan","dataSource":{"type":"external","inputSource":{"type":"local","baseDir":"/u01/nyc","filter":"*.parquet"},"inputFormat":{"type":"parquet"},"signature":[{"name":"DOLocationID","type":"LONG"},{"name":"RatecodeID","type":"LONG"},{"name":"fare_amount","type":"DOUBLE"},{"name":"tpep_dropoff_datetime","type":"LONG"},{"name":"congestion_surcharge","type":"DOUBLE"},{"name":"VendorID","type":"LONG"},{"name":"passenger_count","type":"LONG"},{"name":"tolls_amount","type":"DOUBLE"},{"name":"improvement_surcharge","type":"LONG"},{"name":"trip_distance","type":"DOUBLE"},{"name":"store_and_fwd_flag","type":"STRING"},{"name":"payment_type","type":"LONG"},{"name":"total_amount","type":"DOUBLE"},{"name":"extra","type":"DOUBLE"},{"name":"tip_amount","type":"DOUBLE"},{"name":"mta_tax","type":"DOUBLE"},{"name":"airport_fee","type":"DOUBLE"},{"name":"tpep_pickup_datetime","type":"LONG"},{"name":"PULocationID","type":"LONG"}]},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"resultFormat":"compactedList","columns":["tpep_pickup_datetime","tpep_dropoff_datetime","passenger_count","trip_distance","tip_amount","total_amount"],"context":{"__timeColumn":"tpep_pickup_datetime","__user":"allowAll","finalize":true,"maxNumTasks":6,"maxParseExceptions":0,"queryId":"4ac9ff37-88da-425e-a5a2-216bee04136b","scanSignature":"[{\"name\":\"tpep_pickup_datetime\",\"type\":\"LONG\"},{\"name\":\"tpep_dropoff_datetime\",\"type\":\"LONG\"},{\"name\":\"passenger_count\",\"type\":\"LONG\"},{\"name\":\"trip_distance\",\"type\":\"DOUBLE\"},{\"name\":\"tip_amount\",\"type\":\"DOUBLE\"},{\"name\":\"total_amount\",\"type\":\"DOUBLE\"}]","sqlInsertSegmentGranularity":"\"MONTH\"","sqlQueryId":"4ac9ff37-88da-425e-a5a2-216bee04136b","sqlReplaceTimeChunks":"all","windowFunctionOperatorTransformation":true},"columnTypes":["LONG","LONG","LONG","DOUBLE","DOUBLE","DOUBLE"],"granularity":{"type":"all"},"legacy":false}},"signature":[{"name":"__bucket","type":"LONG"},{"name":"__boost","type":"LONG"},{"name":"tpep_pickup_datetime","type":"LONG"},{"name":"tpep_dropoff_datetime","type":"LONG"},{"name":"passenger_count","type":"LONG"},{"name":"trip_distance","type":"DOUBLE"},{"name":"tip_amount","type":"DOUBLE"},{"name":"total_amount","type":"DOUBLE"}],"shuffleSpec":{"type":"targetSize","clusterBy":{"columns":[{"columnName":"__bucket","order":"ASCENDING"},{"columnName":"__boost","order":"ASCENDING"}],"bucketByCount":1},"targetSize":3000000},"maxWorkerCount":5,"shuffleCheckHasMultipleValues":true},"phase":"READING_INPUT","workerCount":5,"shuffle":"globalSort","output":"localStorage","startTime":"2025-04-14T15:30:04.975Z","duration":21786,"sort":true},{"stageNumber":1,"definition":{"id":"query-4ac9ff37-88da-425e-a5a2-216bee04136b_1","input":[{"type":"stage","stage":0}],"processor":{"type":"segmentGenerator","dataSchema":{"dataSource":"trips","timestampSpec":{"column":"__time","format":"millis","missingValue":null},"dimensionsSpec":{"dimensions":[{"type":"long","name":"ended_at","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"long","name":"passenger_count","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"double","name":"distance","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"double","name":"tip_amount","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"double","name":"total_amount","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false}],"dimensionExclusions":["__time"],"includeAllDimensions":false,"useSchemaDiscovery":false},"metricsSpec":[],"granularitySpec":{"type":"arbitrary","queryGranularity":{"type":"none"},"rollup":false,"intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"transformSpec":{"filter":null,"transforms":[]}},"columnMappings":[{"queryColumn":"tpep_pickup_datetime","outputColumn":"__time"},{"queryColumn":"tpep_dropoff_datetime","outputColumn":"ended_at"},{"queryColumn":"passenger_count","outputColumn":"passenger_count"},{"queryColumn":"trip_distance","outputColumn":"distance"},{"queryColumn":"tip_amount","outputColumn":"tip_amount"},{"queryColumn":"total_amount","outputColumn":"total_amount"}],"tuningConfig":{"maxNumWorkers":5,"maxRowsInMemory":100000,"rowsPerSegment":3000000}},"signature":[],"maxWorkerCount":5}}],"counters":{"0":{"0":{"input0":{"type":"channel","rows":[300088],"totalFiles":[2]},"output":{"type":"channel","rows":[297540],"bytes":[34516612],"frames":[58]},"sortProgress":{"type":"sortProgress","totalMergingLevels":-1,"levelToTotalBatches":{},"levelToMergedBatches":{},"totalMergersForUltimateLevel":-1}},"1":{"input0":{"type":"channel","rows":[292159],"totalFiles":[3]},"output":{"type":"channel","rows":[287280],"bytes":[33326384],"frames":[56]},"sortProgress":{"type":"sortProgress","totalMergingLevels":-1,"levelToTotalBatches":{},"levelToMergedBatches":{},"totalMergersForUltimateLevel":-1}},"2":{"input0":{"type":"channel","rows":[279727],"totalFiles":[2]},"output":{"type":"channel","rows":[277020],"bytes":[32136156],"frames":[54]},"sortProgress":{"type":"sortProgress","totalMergingLevels":-1,"levelToTotalBatches":{},"levelToMergedBatches":{},"totalMergersForUltimateLevel":-1}},"3":{"input0":{"type":"channel","rows":[247100],"totalFiles":[3]},"output":{"type":"channel","rows":[246240],"bytes":[28565472],"frames":[48]},"sortProgress":{"type":"sortProgress","totalMergingLevels":-1,"levelToTotalBatches":{},"levelToMergedBatches":{},"totalMergersForUltimateLevel":-1}},"4":{"input0":{"type":"channel","rows":[277714],"totalFiles":[2]},"output":{"type":"channel","rows":[277020],"bytes":[32136156],"frames":[54]},"sortProgress":{"type":"sortProgress","totalMergingLevels":-1,"levelToTotalBatches":{},"levelToMergedBatches":{},"totalMergersForUltimateLevel":-1}}}}}}}

After a while the task is completed:

[druid@server01 ~]$ curl --location --request GET 'http://server01.domain.com:8888/druid/indexer/v1/task/query-4ac9ff37-88da-425e-a5a2-216bee04136b/status'
{"task":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","status":{"id":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","groupId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","type":"query_controller","createdTime":"2025-04-14T15:29:59.752Z","queueInsertionTime":"1970-01-01T00:00:00.000Z","statusCode":"SUCCESS","status":"SUCCESS","runnerStatusCode":"WAITING","duration":242637,"location":{"host":"localhost","port":8100,"tlsPort":-1},"dataSource":"trips","errorMsg":null}}

Remark:
Duration of 242637 is in miliseconds so 4 minutes and 2 seconds.

[druid@server01 ~]$ curl --location --request GET 'http://server01.domain.com:8888/druid/indexer/v1/task/query-4ac9ff37-88da-425e-a5a2-216bee04136b/reports'
{"multiStageQuery":{"type":"multiStageQuery","taskId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","payload":{"status":{"status":"SUCCESS","startTime":"2025-04-14T15:30:04.387Z","durationMs":237343,"workers":{"0":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker0_0","state":"SUCCESS","durationMs":233763,"pendingMs":93}],"1":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker1_0","state":"SUCCESS","durationMs":233727,"pendingMs":202}],"2":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker2_0","state":"SUCCESS","durationMs":233700,"pendingMs":187}],"3":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker3_0","state":"SUCCESS","durationMs":233648,"pendingMs":200}],"4":[{"workerId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b-worker4_0","state":"SUCCESS","durationMs":233693,"pendingMs":186}]},"pendingTasks":0,"runningTasks":1,"segmentReport":{"shardSpec":"NumberedShardSpec","details":"CLUSTERED BY clause is empty."}},"stages":[{"stageNumber":0,"definition":{"id":"query-4ac9ff37-88da-425e-a5a2-216bee04136b_0","input":[{"type":"external","inputSource":{"type":"local","baseDir":"/u01/nyc","filter":"*.parquet"},"inputFormat":{"type":"parquet"},"signature":[{"name":"DOLocationID","type":"LONG"},{"name":"RatecodeID","type":"LONG"},{"name":"fare_amount","type":"DOUBLE"},{"name":"tpep_dropoff_datetime","type":"LONG"},{"name":"congestion_surcharge","type":"DOUBLE"},{"name":"VendorID","type":"LONG"},{"name":"passenger_count","type":"LONG"},{"name":"tolls_amount","type":"DOUBLE"},{"name":"improvement_surcharge","type":"LONG"},{"name":"trip_distance","type":"DOUBLE"},{"name":"store_and_fwd_flag","type":"STRING"},{"name":"payment_type","type":"LONG"},{"name":"total_amount","type":"DOUBLE"},{"name":"extra","type":"DOUBLE"},{"name":"tip_amount","type":"DOUBLE"},{"name":"mta_tax","type":"DOUBLE"},{"name":"airport_fee","type":"DOUBLE"},{"name":"tpep_pickup_datetime","type":"LONG"},{"name":"PULocationID","type":"LONG"}]}],"processor":{"type":"scan","query":{"queryType":"scan","dataSource":{"type":"external","inputSource":{"type":"local","baseDir":"/u01/nyc","filter":"*.parquet"},"inputFormat":{"type":"parquet"},"signature":[{"name":"DOLocationID","type":"LONG"},{"name":"RatecodeID","type":"LONG"},{"name":"fare_amount","type":"DOUBLE"},{"name":"tpep_dropoff_datetime","type":"LONG"},{"name":"congestion_surcharge","type":"DOUBLE"},{"name":"VendorID","type":"LONG"},{"name":"passenger_count","type":"LONG"},{"name":"tolls_amount","type":"DOUBLE"},{"name":"improvement_surcharge","type":"LONG"},{"name":"trip_distance","type":"DOUBLE"},{"name":"store_and_fwd_flag","type":"STRING"},{"name":"payment_type","type":"LONG"},{"name":"total_amount","type":"DOUBLE"},{"name":"extra","type":"DOUBLE"},{"name":"tip_amount","type":"DOUBLE"},{"name":"mta_tax","type":"DOUBLE"},{"name":"airport_fee","type":"DOUBLE"},{"name":"tpep_pickup_datetime","type":"LONG"},{"name":"PULocationID","type":"LONG"}]},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"resultFormat":"compactedList","columns":["tpep_pickup_datetime","tpep_dropoff_datetime","passenger_count","trip_distance","tip_amount","total_amount"],"context":{"__timeColumn":"tpep_pickup_datetime","__user":"allowAll","finalize":true,"maxNumTasks":6,"maxParseExceptions":0,"queryId":"4ac9ff37-88da-425e-a5a2-216bee04136b","scanSignature":"[{\"name\":\"tpep_pickup_datetime\",\"type\":\"LONG\"},{\"name\":\"tpep_dropoff_datetime\",\"type\":\"LONG\"},{\"name\":\"passenger_count\",\"type\":\"LONG\"},{\"name\":\"trip_distance\",\"type\":\"DOUBLE\"},{\"name\":\"tip_amount\",\"type\":\"DOUBLE\"},{\"name\":\"total_amount\",\"type\":\"DOUBLE\"}]","sqlInsertSegmentGranularity":"\"MONTH\"","sqlQueryId":"4ac9ff37-88da-425e-a5a2-216bee04136b","sqlReplaceTimeChunks":"all","windowFunctionOperatorTransformation":true},"columnTypes":["LONG","LONG","LONG","DOUBLE","DOUBLE","DOUBLE"],"granularity":{"type":"all"},"legacy":false}},"signature":[{"name":"__bucket","type":"LONG"},{"name":"__boost","type":"LONG"},{"name":"tpep_pickup_datetime","type":"LONG"},{"name":"tpep_dropoff_datetime","type":"LONG"},{"name":"passenger_count","type":"LONG"},{"name":"trip_distance","type":"DOUBLE"},{"name":"tip_amount","type":"DOUBLE"},{"name":"total_amount","type":"DOUBLE"}],"shuffleSpec":{"type":"targetSize","clusterBy":{"columns":[{"columnName":"__bucket","order":"ASCENDING"},{"columnName":"__boost","order":"ASCENDING"}],"bucketByCount":1},"targetSize":3000000},"maxWorkerCount":5,"shuffleCheckHasMultipleValues":true},"phase":"FINISHED","workerCount":5,"partitionCount":29,"shuffle":"globalSort","output":"localStorage","startTime":"2025-04-14T15:30:04.975Z","duration":143669,"sort":true},{"stageNumber":1,"definition":{"id":"query-4ac9ff37-88da-425e-a5a2-216bee04136b_1","input":[{"type":"stage","stage":0}],"processor":{"type":"segmentGenerator","dataSchema":{"dataSource":"trips","timestampSpec":{"column":"__time","format":"millis","missingValue":null},"dimensionsSpec":{"dimensions":[{"type":"long","name":"ended_at","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"long","name":"passenger_count","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"double","name":"distance","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"double","name":"tip_amount","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false},{"type":"double","name":"total_amount","multiValueHandling":"SORTED_ARRAY","createBitmapIndex":false}],"dimensionExclusions":["__time"],"includeAllDimensions":false,"useSchemaDiscovery":false},"metricsSpec":[],"granularitySpec":{"type":"arbitrary","queryGranularity":{"type":"none"},"rollup":false,"intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"transformSpec":{"filter":null,"transforms":[]}},"columnMappings":[{"queryColumn":"tpep_pickup_datetime","outputColumn":"__time"},{"queryColumn":"tpep_dropoff_datetime","outputColumn":"ended_at"},{"queryColumn":"passenger_count","outputColumn":"passenger_count"},{"queryColumn":"trip_distance","outputColumn":"distance"},{"queryColumn":"tip_amount","outputColumn":"tip_amount"},{"queryColumn":"total_amount","outputColumn":"total_amount"}],"tuningConfig":{"maxNumWorkers":5,"maxRowsInMemory":100000,"rowsPerSegment":3000000}},"signature":[],"maxWorkerCount":5},"phase":"FINISHED","workerCount":5,"partitionCount":29,"output":"localStorage","startTime":"2025-04-14T15:32:28.510Z","duration":88971}],"counters":{"0":{"0":{"input0":{"type":"channel","rows":[6374000],"bytes":[102672835],"files":[2],"totalFiles":[2]},"output":{"type":"channel","rows":[6374000],"bytes":[739426262],"frames":[1243]},"shuffle":{"type":"channel","rows":[0,3,1,4,0,0,11,25,1533432,1533286,10,0,0,0,0,19,0,1375252,1931941,16],"bytes":[0,370,146,482,0,0,1266,2834,171754754,171738402,1154,0,0,0,0,2162,0,154037506,216390448,1826],"frames":[0,1,1,1,0,0,1,1,305,305,1,0,0,0,0,1,0,273,384,1]},"sortProgress":{"type":"sortProgress","totalMergingLevels":3,"levelToTotalBatches":{"0":4,"1":1,"2":29},"levelToMergedBatches":{"0":4,"1":1,"2":29},"totalMergersForUltimateLevel":29,"progressDigest":1.0}},"1":{"input0":{"type":"channel","rows":[9160778],"bytes":[152204493],"files":[3],"totalFiles":[3]},"output":{"type":"channel","rows":[9160778],"bytes":[1062711040],"frames":[1788]},"shuffle":{"type":"channel","rows":[0,1,0,5,4,0,0,0,8,0,2913900,0,42,0,0,0,0,41,0,2907049,6,6,27,2,1669816,1669868,0,3],"bytes":[0,146,0,594,482,0,0,0,930,0,326376486,0,4738,0,0,0,0,4626,0,325609106,706,706,3058,258,187030680,187036504,0,370],"frames":[0,1,0,1,1,0,0,0,1,0,579,0,1,0,0,0,0,1,0,577,1,1,1,1,332,332,0,1]},"sortProgress":{"type":"sortProgress","totalMergingLevels":3,"levelToTotalBatches":{"0":5,"1":1,"2":29},"levelToMergedBatches":{"0":5,"1":1,"2":29},"totalMergersForUltimateLevel":29,"progressDigest":1.0}},"2":{"input0":{"type":"channel","rows":[6250488],"bytes":[104023277],"files":[2],"totalFiles":[2]},"output":{"type":"channel","rows":[6250488],"bytes":[725098054],"frames":[1219]},"shuffle":{"type":"channel","rows":[3,2,3,4,4,1,0,0,0,0,93,1702587,1700990,0,85,0,0,0,0,0,18,2846690,0,8],"bytes":[370,258,370,482,482,146,0,0,0,0,10450,190701236,190522372,0,9554,0,0,0,0,0,2050,318848490,0,930],"frames":[1,1,1,1,1,1,0,0,0,0,1,338,338,0,1,0,0,0,0,0,1,565,0,1]},"sortProgress":{"type":"sortProgress","totalMergingLevels":3,"levelToTotalBatches":{"0":4,"1":1,"2":29},"levelToMergedBatches":{"0":4,"1":1,"2":29},"totalMergersForUltimateLevel":29,"progressDigest":1.0}},"3":{"input0":{"type":"channel","rows":[9489026],"bytes":[159179327],"files":[3],"totalFiles":[3]},"output":{"type":"channel","rows":[9489026],"bytes":[1100789950],"frames":[1851]},"shuffle":{"type":"channel","rows":[3,4,1,5,5,0,0,0,0,0,0,41,0,1663816,1624339,0,46,0,0,19,2824175,8,0,0,34,0,1688299,1688225,6],"bytes":[370,482,146,594,594,0,0,0,0,0,0,4626,0,186358646,181936950,0,5186,0,0,2162,316326674,930,0,0,3842,0,189100878,189092590,706],"frames":[1,1,1,1,1,0,0,0,0,0,0,1,0,331,323,0,1,0,0,1,561,1,0,0,1,0,335,335,1]},"sortProgress":{"type":"sortProgress","totalMergingLevels":3,"levelToTotalBatches":{"0":6,"1":1,"2":29},"levelToMergedBatches":{"0":6,"1":1,"2":29},"totalMergersForUltimateLevel":29,"progressDigest":1.0}},"4":{"input0":{"type":"channel","rows":[7035934],"bytes":[117663686],"files":[2],"totalFiles":[2]},"output":{"type":"channel","rows":[7035934],"bytes":[816214992],"frames":[1372]},"shuffle":{"type":"channel","rows":[0,1,1,5,2,0,0,0,0,0,0,0,0,8,0,2011788,1501811,0,25,9,2,37,1761140,1761092,6,7],"bytes":[0,146,146,594,258,0,0,0,0,0,0,0,0,930,0,225333856,168212964,0,2834,1042,258,4178,197259580,197254204,706,818],"frames":[0,1,1,1,1,0,0,0,0,0,0,0,0,1,0,400,298,0,1,1,1,1,350,350,1,1]},"sortProgress":{"type":"sortProgress","totalMergingLevels":3,"levelToTotalBatches":{"0":4,"1":1,"2":29},"levelToMergedBatches":{"0":4,"1":1,"2":29},"totalMergersForUltimateLevel":29,"progressDigest":1.0}}},"1":{"0":{"input0":{"type":"channel","rows":[6,0,0,0,0,1,0,0,0,0,2914003,0,0,0,0,2011807,0,0,0,0,2824201,0,0,0,0,1669875],"bytes":[740,0,0,0,0,146,0,0,0,0,326388192,0,0,0,0,225336018,0,0,0,0,316329654,0,0,0,0,187037288],"frames":[2,0,0,0,0,1,0,0,0,0,584,0,0,0,0,401,0,0,0,0,563,0,0,0,0,332]},"segmentGenerationProgress":{"type":"segmentGenerationProgress","rowsProcessed":9419893,"rowsPersisted":9419893,"rowsMerged":9419893,"rowsPushed":9419893}},"1":{"input0":{"type":"channel","rows":[0,11,0,0,0,0,11,0,0,0,0,1702628,0,0,0,0,1501857,0,0,0,0,2846741,0,0,0,0,1688299],"bytes":[0,1300,0,0,0,0,1266,0,0,0,0,190705862,0,0,0,0,168218218,0,0,0,0,318854236,0,0,0,0,189100878],"frames":[0,2,0,0,0,0,1,0,0,0,0,339,0,0,0,0,301,0,0,0,0,566,0,0,0,0,335]},"segmentGenerationProgress":{"type":"segmentGenerationProgress","rowsProcessed":7739547,"rowsPersisted":7739547,"rowsMerged":7739547,"rowsPushed":7739547}},"2":{"input0":{"type":"channel","rows":[0,0,6,0,0,0,0,25,0,0,0,0,1701032,0,0,0,0,1375293,0,0,0,0,1761167,0,0,0,0,1688228],"bytes":[0,0,740,0,0,0,0,2834,0,0,0,0,190527076,0,0,0,0,154042098,0,0,0,0,197262638,0,0,0,0,189092960],"frames":[0,0,2,0,0,0,0,1,0,0,0,0,338,0,0,0,0,273,0,0,0,0,351,0,0,0,0,336]},"segmentGenerationProgress":{"type":"segmentGenerationProgress","rowsProcessed":6525751,"rowsPersisted":6525751,"rowsMerged":6525751,"rowsPushed":6525751}},"3":{"input0":{"type":"channel","rows":[0,0,0,23,0,0,0,0,1533440,0,0,0,0,1663824,0,0,0,0,1931966,0,0,0,0,1761102,0,0,0,0,6],"bytes":[0,0,0,2644,0,0,0,0,171755684,0,0,0,0,186359542,0,0,0,0,216393384,0,0,0,0,197255324,0,0,0,0,706],"frames":[0,0,0,2,0,0,0,0,306,0,0,0,0,331,0,0,0,0,388,0,0,0,0,350,0,0,0,0,1]},"segmentGenerationProgress":{"type":"segmentGenerationProgress","rowsProcessed":6890361,"rowsPersisted":6890361,"rowsMerged":6890361,"rowsPushed":6890361}},"4":{"input0":{"type":"channel","rows":[0,0,0,0,15,0,0,0,0,1533286,0,0,0,0,1624424,0,0,0,0,2907093,0,0,0,0,1669856],"bytes":[0,0,0,0,1748,0,0,0,0,171738402,0,0,0,0,181946572,0,0,0,0,325614102,0,0,0,0,187035228],"frames":[0,0,0,0,2,0,0,0,0,305,0,0,0,0,326,0,0,0,0,579,0,0,0,0,334]},"segmentGenerationProgress":{"type":"segmentGenerationProgress","rowsProcessed":7734674,"rowsPersisted":7734674,"rowsMerged":7734674,"rowsPushed":7734674}}}}}},"taskContext":{"type":"taskContext","taskId":"query-4ac9ff37-88da-425e-a5a2-216bee04136b","payload":{"lookupLoadingMode":"NONE","useLineageBasedSegmentAllocation":true,"forceTimeChunkLock":true}}}

Remark:
Better to paste this JSON in an online JSON validator to have a more human readable display or use the graphical interface:

apache_druid02
apache_druid02

We can control the table content with dsql:

dsql> select count(*) from trips;
┌──────────┐
│ EXPR$0   │
├──────────┤
│ 38310226 │
└──────────┘
Retrieved 1 row in 0.05s.

dsql> select * from trips limit 10;
┌──────────────────────────┬───────────────┬─────────────────┬──────────┬────────────┬──────────────┐
│ __time                   │ ended_at      │ passenger_count │ distance │ tip_amount │ total_amount │
├──────────────────────────┼───────────────┼─────────────────┼──────────┼────────────┼──────────────┤
│ 2001-01-01T00:06:49.000Z │  978363731000 │               2 │     7.41 │        7.0 │         46.9 │
│ 2001-01-01T00:07:36.000Z │  978308191000 │               1 │     1.68 │        0.0 │         14.7 │
│ 2001-01-01T00:08:31.000Z │  978364887000 │               1 │    18.63 │      16.46 │        98.76 │
│ 2001-01-01T00:08:42.000Z │  978358431000 │               1 │    17.68 │        0.0 │         81.8 │
│ 2001-01-01T00:28:40.000Z │  978311469000 │               1 │    10.23 │        0.0 │         52.5 │
│ 2001-01-01T15:18:51.000Z │  978386920000 │               2 │     9.13 │        0.0 │         43.0 │
│ 2002-12-31T22:16:54.000Z │ 1041433328000 │               1 │     3.25 │       3.63 │        21.78 │
│ 2002-12-31T22:27:05.000Z │ 1041374797000 │               1 │    11.77 │        0.0 │         51.8 │
│ 2002-12-31T23:03:18.000Z │ 1041455384000 │               1 │    28.19 │      16.54 │        99.23 │
│ 2002-12-31T23:03:19.000Z │ 1041421949000 │               1 │    18.13 │      16.46 │        98.76 │
└──────────────────────────┴───────────────┴─────────────────┴──────────┴────────────┴──────────────┘
Retrieved 10 rows in 0.03s.

Apache Druid query

The same query I have done on other database flavors is :

select
  floor(__time to month) as bucket,
  avg(total_amount) as avg_total_amount,
  max(total_amount) as max_total_amount,
  min(total_amount) as min_total_amount
from trips
where __time >= '2023-01-01' and __time <= '2023-12-31'
and total_amount > 0
group by floor(__time to month)
order by floor(__time to month);

That I execute with dsql:

dsql> select
more>   floor(__time to month) as bucket,
more>   avg(total_amount) as avg_total_amount,
more>   max(total_amount) as max_total_amount,
more>   min(total_amount) as min_total_amount
more> from trips
more> where __time >= '2023-01-01' and __time <= '2023-12-31'
more> and total_amount > 0
more> group by floor(__time to month)
more> order by floor(__time to month);
┌──────────────────────────┬────────────────────┬──────────────────┬──────────────────┐
│ bucket                   │ avg_total_amount   │ max_total_amount │ min_total_amount │
├──────────────────────────┼────────────────────┼──────────────────┼──────────────────┤
│ 2023-01-01T00:00:00.000Z │ 27.448150646754048 │           1169.4 │             0.01 │
│ 2023-02-01T00:00:00.000Z │ 27.340832782304098 │           2208.1 │             0.01 │
│ 2023-03-01T00:00:00.000Z │  28.26997047105207 │           2100.0 │              1.0 │
│ 2023-04-01T00:00:00.000Z │  28.76314423433438 │           2451.0 │             0.01 │
│ 2023-05-01T00:00:00.000Z │ 29.463553602568716 │           6304.9 │             0.01 │
│ 2023-06-01T00:00:00.000Z │ 29.596327359323684 │        386987.63 │             0.01 │
│ 2023-07-01T00:00:00.000Z │  29.16617527523992 │          1079.45 │             0.01 │
│ 2023-08-01T00:00:00.000Z │  29.25346302454243 │          1435.19 │             0.01 │
│ 2023-09-01T00:00:00.000Z │  30.38171181443254 │         187513.9 │             0.01 │
│ 2023-10-01T00:00:00.000Z │ 29.767678816319247 │           6339.0 │             0.01 │
│ 2023-11-01T00:00:00.000Z │   29.3062339519644 │          1814.27 │             0.01 │
│ 2023-12-01T00:00:00.000Z │  29.22982835232216 │          4269.16 │             0.01 │
└──────────────────────────┴────────────────────┴──────────────────┴──────────────────┘
Retrieved 12 rows in 0.20s.

dsql> select
more>   floor(__time to month) as bucket,
more>   avg(total_amount) as avg_total_amount,
more>   max(total_amount) as max_total_amount,
more>   min(total_amount) as min_total_amount
more> from trips
more> where __time >= '2023-01-01' and __time <= '2023-12-31'
more> and total_amount > 0
more> group by floor(__time to month)
more> order by floor(__time to month);
┌──────────────────────────┬────────────────────┬──────────────────┬──────────────────┐
│ bucket                   │ avg_total_amount   │ max_total_amount │ min_total_amount │
├──────────────────────────┼────────────────────┼──────────────────┼──────────────────┤
│ 2023-01-01T00:00:00.000Z │ 27.448150646754048 │           1169.4 │             0.01 │
│ 2023-02-01T00:00:00.000Z │ 27.340832782304098 │           2208.1 │             0.01 │
│ 2023-03-01T00:00:00.000Z │  28.26997047105207 │           2100.0 │              1.0 │
│ 2023-04-01T00:00:00.000Z │  28.76314423433438 │           2451.0 │             0.01 │
│ 2023-05-01T00:00:00.000Z │ 29.463553602568716 │           6304.9 │             0.01 │
│ 2023-06-01T00:00:00.000Z │ 29.596327359323684 │        386987.63 │             0.01 │
│ 2023-07-01T00:00:00.000Z │  29.16617527523992 │          1079.45 │             0.01 │
│ 2023-08-01T00:00:00.000Z │  29.25346302454243 │          1435.19 │             0.01 │
│ 2023-09-01T00:00:00.000Z │  30.38171181443254 │         187513.9 │             0.01 │
│ 2023-10-01T00:00:00.000Z │ 29.767678816319247 │           6339.0 │             0.01 │
│ 2023-11-01T00:00:00.000Z │   29.3062339519644 │          1814.27 │             0.01 │
│ 2023-12-01T00:00:00.000Z │  29.22982835232216 │          4269.16 │             0.01 │
└──────────────────────────┴────────────────────┴──────────────────┴──────────────────┘
Retrieved 12 rows in 0.05s.

So after the first run of 200ms it takes around 50 ms to run the query !

To compare with my other testing, we almost do not see the bar for Apache Druid:

apache_druid03
apache_druid03

Apache Druid data deletion

Deletion with Apache Druid is not as simple as a DROP TABLE… You must mark the data segments as unused and then launch a kill task that will delete the disabled segments from deep storage.

Remark:
Storage with Apache Druid is in this folder:

[druid@server01 ~]$ ll /druid/apache-druid-32.0.1/var/druid/segments/trips
total 0
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2001-01-01T00:00:00.000Z_2001-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2002-12-01T00:00:00.000Z_2003-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2003-01-01T00:00:00.000Z_2003-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2008-12-01T00:00:00.000Z_2009-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2009-01-01T00:00:00.000Z_2009-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2014-11-01T00:00:00.000Z_2014-12-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2022-10-01T00:00:00.000Z_2022-11-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2022-12-01T00:00:00.000Z_2023-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-01-01T00:00:00.000Z_2023-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-02-01T00:00:00.000Z_2023-03-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-03-01T00:00:00.000Z_2023-04-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-04-01T00:00:00.000Z_2023-05-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-05-01T00:00:00.000Z_2023-06-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-06-01T00:00:00.000Z_2023-07-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-07-01T00:00:00.000Z_2023-08-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-08-01T00:00:00.000Z_2023-09-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-09-01T00:00:00.000Z_2023-10-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-10-01T00:00:00.000Z_2023-11-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-11-01T00:00:00.000Z_2023-12-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-12-01T00:00:00.000Z_2024-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2024-01-01T00:00:00.000Z_2024-02-01T00:00:00.000Z

I plan to disable segments by segment ids, as suggested install jq with:

[root@server01 ~]# dnf install -y jq.x86_64

Jq that, at the end, I will not use because I want to delete evrything:

[druid@server01 ~]$ unusedSegmentIds=$(curl -X 'GET' -H 'Content-Type:application/json' http://localhost:8081/druid/coordinator/v1/datasources/trips/segments)
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  2424    0  2424    0     0   591k      0 --:--:-- --:--:-- --:--:--  591k
[druid@server01 ~]$ echo $unusedSegmentIds
["trips_2001-01-01T00:00:00.000Z_2001-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2002-12-01T00:00:00.000Z_2003-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2003-01-01T00:00:00.000Z_2003-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2008-12-01T00:00:00.000Z_2009-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2009-01-01T00:00:00.000Z_2009-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2014-11-01T00:00:00.000Z_2014-12-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2022-10-01T00:00:00.000Z_2022-11-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2022-12-01T00:00:00.000Z_2023-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-01-01T00:00:00.000Z_2023-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-01-01T00:00:00.000Z_2023-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2023-02-01T00:00:00.000Z_2023-03-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-03-01T00:00:00.000Z_2023-04-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-03-01T00:00:00.000Z_2023-04-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2023-04-01T00:00:00.000Z_2023-05-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-04-01T00:00:00.000Z_2023-05-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2023-05-01T00:00:00.000Z_2023-06-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-05-01T00:00:00.000Z_2023-06-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2023-06-01T00:00:00.000Z_2023-07-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-06-01T00:00:00.000Z_2023-07-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2023-07-01T00:00:00.000Z_2023-08-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-08-01T00:00:00.000Z_2023-09-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-09-01T00:00:00.000Z_2023-10-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-10-01T00:00:00.000Z_2023-11-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-10-01T00:00:00.000Z_2023-11-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2023-11-01T00:00:00.000Z_2023-12-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-11-01T00:00:00.000Z_2023-12-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2023-12-01T00:00:00.000Z_2024-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z","trips_2023-12-01T00:00:00.000Z_2024-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1","trips_2024-01-01T00:00:00.000Z_2024-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z"]

That you can also confirm with dsql:

dsql> select segment_id
more> from sys.segments
more> where datasource='trips';
┌────────────────────────────────────────────────────────────────────────────────────┐
│ segment_id                                                                         │
├────────────────────────────────────────────────────────────────────────────────────┤
│ trips_2001-01-01T00:00:00.000Z_2001-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2002-12-01T00:00:00.000Z_2003-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2003-01-01T00:00:00.000Z_2003-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2008-12-01T00:00:00.000Z_2009-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2009-01-01T00:00:00.000Z_2009-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2014-11-01T00:00:00.000Z_2014-12-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2022-10-01T00:00:00.000Z_2022-11-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2022-12-01T00:00:00.000Z_2023-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-01-01T00:00:00.000Z_2023-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-01-01T00:00:00.000Z_2023-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2023-02-01T00:00:00.000Z_2023-03-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-03-01T00:00:00.000Z_2023-04-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-03-01T00:00:00.000Z_2023-04-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2023-04-01T00:00:00.000Z_2023-05-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-04-01T00:00:00.000Z_2023-05-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2023-05-01T00:00:00.000Z_2023-06-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-05-01T00:00:00.000Z_2023-06-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2023-06-01T00:00:00.000Z_2023-07-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-06-01T00:00:00.000Z_2023-07-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2023-07-01T00:00:00.000Z_2023-08-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-08-01T00:00:00.000Z_2023-09-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-09-01T00:00:00.000Z_2023-10-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-10-01T00:00:00.000Z_2023-11-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-10-01T00:00:00.000Z_2023-11-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2023-11-01T00:00:00.000Z_2023-12-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-11-01T00:00:00.000Z_2023-12-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2023-12-01T00:00:00.000Z_2024-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
│ trips_2023-12-01T00:00:00.000Z_2024-01-01T00:00:00.000Z_2025-04-14T10:01:44.051Z_1 │
│ trips_2024-01-01T00:00:00.000Z_2024-02-01T00:00:00.000Z_2025-04-14T10:01:44.051Z   │
└────────────────────────────────────────────────────────────────────────────────────┘
Retrieved 29 rows in 0.04s.

Finally mark then unused with:

[druid@server01 ~]$ curl -X 'POST' -H 'Content-Type:application/json' -d "{\"segmentIds\": $unusedSegmentIds}" http://localhost:8081/druid/coordinator/v1/datasources/trips/markUnused
{"numChangedSegments":29,"segmentStateChanged":true}

Segments have gone from system dictionary, but you need to issue a kill task to delete them from deep storage:

[druid@server01 ~]$ ll /druid/apache-druid-32.0.1/var/druid/segments/trips
total 0
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2001-01-01T00:00:00.000Z_2001-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2002-12-01T00:00:00.000Z_2003-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2003-01-01T00:00:00.000Z_2003-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2008-12-01T00:00:00.000Z_2009-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2009-01-01T00:00:00.000Z_2009-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2014-11-01T00:00:00.000Z_2014-12-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2022-10-01T00:00:00.000Z_2022-11-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:03 2022-12-01T00:00:00.000Z_2023-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-01-01T00:00:00.000Z_2023-02-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-02-01T00:00:00.000Z_2023-03-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-03-01T00:00:00.000Z_2023-04-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-04-01T00:00:00.000Z_2023-05-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-05-01T00:00:00.000Z_2023-06-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-06-01T00:00:00.000Z_2023-07-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-07-01T00:00:00.000Z_2023-08-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-08-01T00:00:00.000Z_2023-09-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-09-01T00:00:00.000Z_2023-10-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-10-01T00:00:00.000Z_2023-11-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-11-01T00:00:00.000Z_2023-12-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2023-12-01T00:00:00.000Z_2024-01-01T00:00:00.000Z
drwxr-x--- 3 druid dba 96 Apr 14 12:04 2024-01-01T00:00:00.000Z_2024-02-01T00:00:00.000Z

Copy the kill tutorial to customize it. The upper bound is strict that’s why I extended a bit the period:

[druid@server01 ~]$ cp /druid/apache-druid-32.0.1/quickstart/tutorial/deletion-kill.json deletion-kill.json
[druid@server01 ~]$ vi deletion-kill.json
[druid@server01 ~]$ cat deletion-kill.json
{
  "type": "kill",
  "dataSource": "trips",
  "interval" : "2001-01-01/2024-02-01"
}
[druid@server01 ~]$ curl -X 'POST' -H 'Content-Type:application/json' -d @deletion-kill.json http://localhost:8081/druid/indexer/v1/task
{"task":"kill_trips_milkdfea_2001-01-01T00:00:00.000Z_2024-02-01T00:00:00.000Z_2025-04-14T13:09:34.601Z"}

References

About Post Author

Share the knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *