Apache Druid for time series data hands-on
Table of contents
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::

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 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:

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 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"}





