Apache Druid

Hi Frank,  both the Imply docs and the Druid docs <https://druid.apache.org/docs/latest/operations/rolling-updates.html|HERE> recommend updating the Query Node processes last ... was there a specific reason why you thought to do the Master Nodes last?

master last is OK (that's #6 — "merged Coordinator+Overlord")

Thanks for confirming. Appreciate it. <@U0344FW86DD>

that's just one trigger, there are also others (time-based, bytes-based)

it's possible you are hitting another trigger earlier

log files should have something at INFO level saying which trigger you hit

thank you for your answer. I have an additional question. Is the newly created persistent     file compressed? If so, is there a way to view the size before it was compressed?

the same log lines (at INFO level) would have an estimate of the in-memory size, although it is just an estimate and can be off

I looked for the logs based on your answer, but couldn't find them. I was trying to find the logs for the middle manager. Could you please let me know where I should look for the logs? Thank you.

these would be task logs, which in a typical setup you can get in the web console, ingestion tab, by clicking "log" next to each task

It took me some time to find the log because there was a problem with the settings. However, i found the desired data. Thank you

Have you had a look at <https://druid.apache.org/docs/latest/tutorials/cluster.html> and <https://druid.apache.org/docs/latest/operations/basic-cluster-tuning.html> ?

<@U030MBK46BD> can you help with sizing of the servers? does master server requires atleast 8vCPU as mentioned in the doc and same for other servers

No, for a small cluster 2vCPU / 8GB RAM on the master is a good starting point

I want a scalable solution in-line with best practices

I don't think we can come up with a general rule for that. Although, if putting them in separate tables requires a large join to query them, then single table will definitely perform better. I would think of the workloads that I want each table to serve in terms of data availability and granularity. Rolled up data will perform faster queries, less dimensions will likely increase rollup...<https://imply.io/blog/learn-how-to-achieve-sub-second-responses-with-apache-druid/|here's a blog >that goes through a bunch of considerations when modeling that might help in making those decisions.

there's some additional details here— <https://druid.apache.org/docs/latest/querying/multitenancy.html>

the doc page discusses when to use one big datasource and when to use a bunch of small datasources

What is the source here (batch/Streaming Ingestion), we should look at the ingestion log to see what the job is doing.

```"statusCode":"SUCCESS","status":"SUCCESS","runnerStatusCode":"WAITING"```


```org.apache.hadoop.io.serializer.SerializationFactory: Serialization class not found: 
java.lang.ClassNotFoundException: Class  org.apache.hadoop.io.serializer.avro.AvroSpecificSerialization not found```

```2023-05-30T17:37:15,506 INFO [forking-task-runner-7] org.apache.druid.indexing.overlord.ForkingTaskRunner - Logging task index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17:37:15.483Z output to: /data1/tmp/druid-task/index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17:37:15.483Z/log
2023-05-30T22:39:42,082 INFO [forking-task-runner-7-[index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17:37:15.483Z]] org.apache.druid.indexing.overlord.ForkingTaskRunner - Process exited with status[0] for task: index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17:37:15.483Z
2023-05-30T22:39:42,082 INFO [forking-task-runner-7] org.apache.druid.storage.hdfs.tasklog.HdfsTaskLogs - Writing task log to: /dod_prod/indexing-logs/index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17_37_15.483Z
2023-05-30T22:39:42,100 INFO [forking-task-runner-7] org.apache.druid.storage.hdfs.tasklog.HdfsTaskLogs - Wrote task log to: /dod_prod/indexing-logs/index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17_37_15.483Z
2023-05-30T22:39:42,100 INFO [forking-task-runner-7] org.apache.druid.storage.hdfs.tasklog.HdfsTaskLogs - Writing task reports to: /dod_prod/indexing-logs/index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17_37_15.483Z.reports.json
2023-05-30T22:39:42,112 INFO [forking-task-runner-7] org.apache.druid.storage.hdfs.tasklog.HdfsTaskLogs - Wrote task reports to: /dod_prod/indexing-logs/index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17_37_15.483Z.reports.json
2023-05-30T22:39:42,113 INFO [forking-task-runner-7] org.apache.druid.indexing.overlord.ForkingTaskRunner - Removing task directory: /data1/tmp/druid-task/index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17:37:15.483Z
2023-05-30T22:39:42,116 INFO [WorkerTaskManager-NoticeHandler] org.apache.druid.indexing.worker.WorkerTaskManager - Task [index_hadoop_dsp_media_and_bids_ninhfnmn_2023-05-30T17:37:15.483Z] completed with status [SUCCESS].```

Please share the complete log, As per log the task is successful, might be it is waiting for segment handoff.

Complete task log and check for the tasks status on the overlord, it should show you if the task is in segment handoff period.

same latitude? What does your datasource look like? what's the query?
Also 0.17?! :smile: 26.0 is pretty cool :wink:

Hello, my data source comes from Hadoop. When I query by hour, it returns multiple records in the same hour. At this time, I sort by date, but when I sort by hour, some of them are normal. I don’t understand how the underlying query is parsed and executed?

druid查询.png

uTools_1685593180403.png

Yes, due to some reasons, we are unable to directly upgrade Druid to the new version. At the same time, I am not sure if the new version can avoid this issue. Attached is a result of my query.

That does seem like a bug... can you share the EXPLAIN for the query?

```{
    "queryType": "groupBy",
    "dataSource": {
        "type": "table",
        "name": "table_name"
    },
    "intervals": {
        "type": "intervals",
        "intervals": [
            "2023-06-01T00:00:00.000Z/2023-06-01T00:00:00.001Z"
        ]
    },
    "virtualColumns": [],
    "filter": {
        "type": "and",
        "fields": [
            {
                "type": "selector",
                "dimension": "action_day",
                "value": "2023-06-01",
                "extractionFn": null
            },
            {
                "type": "selector",
                "dimension": "adx_name",
                "value": "xxx",
                "extractionFn": null
            },
            {
                "type": "or",
                "fields": [
                    {
                        "type": "bound",
                        "dimension": "impression",
                        "lower": "0",
                        "upper": null,
                        "lowerStrict": true,
                        "upperStrict": false,
                        "extractionFn": null,
                        "ordering": {
                            "type": "numeric"
                        }
                    },
                    {
                        "type": "bound",
                        "dimension": "bid",
                        "lower": "0",
                        "upper": null,
                        "lowerStrict": true,
                        "upperStrict": false,
                        "extractionFn": null,
                        "ordering": {
                            "type": "numeric"
                        }
                    }
                ]
            }
        ]
    },
    "granularity": {
        "type": "all"
    },
    "dimensions": [
        {
            "type": "default",
            "dimension": "action_hour",
            "outputName": "d0",
            "outputType": "LONG"
        },
        {
            "type": "default",
            "dimension": "action_day",
            "outputName": "d1",
            "outputType": "STRING"
        }
    ],
    "aggregations": [
        {
            "type": "longSum",
            "name": "a0",
            "fieldName": "impression",
            "expression": null
        },
        {
            "type": "longSum",
            "name": "a1",
            "fieldName": "bid",
            "expression": null
        }
    ],
    "postAggregations": [
        {
            "type": "expression",
            "name": "p0",
            "expression": "(\"a0\" * 1)",
            "ordering": null
        },
        {
            "type": "expression",
            "name": "p1",
            "expression": "(\"a1\" * 1)",
            "ordering": null
        }
    ],
    "having": null,
    "limitSpec": {
        "type": "default",
        "columns": [
            {
                "dimension": "d1",
                "direction": "ascending",
                "dimensionOrder": {
                    "type": "lexicographic"
                }
            }
        ],
        "limit": 100
    },
    "context": {
        "sqlOuterLimit": 100,
        "sqlQueryId": "c48b6789-4ce7-4157-a2f2-f79e0a2bdfde"
    },
    "descending": false
}```

I think it may be because the underlying segments were not merged?
My current settings are as follows,
`QueryGranularity ‘is set to’ hour ’,
`SegmentGranularity ‘is’ day’`

It shouldn't matter that they were not merged, the groupBy query should aggregate them into a single result per group by dimension values... I'll look for bug fixes that may already resolve this between 0.17 and 26.0 and let you know what I find. In the meantime, is this something you can consistently reproduce?

Sorry for not responding to the message in a timely manner.
This error can be replicated, and it will occur when I query yesterday’s data, but it was normal when I query the day before yesterday

image.png

It’s June 7th now, and when I query the data for June 6th, I will get many duplicate entries, like this

This bug says it affects 0.18 but it may also affect 0.17. Does it fit your query pattern? <https://github.com/apache/druid/issues/9866|https://github.com/apache/druid/issues/9866>

Thank you for your help
It seems different from mine. I don’t have any joins or sub queries, just like the query plan I replied to earlier. It looks like a regular ‘group by’ query,
In Druid version 0.17, join does not seem to be supported
<https://druid.apache.org/docs/0.17.0/querying/joins.html>

I’ve been searching for other issue reports that might fit… haven’t found anything specific. In the interest of figuring this out…can you try a few thing separately on the date where you see the problem:
• remove the CAST for action hour
• Remove the limit
• Remove the order by
also can you share the segments view for the corresponding segments for the day that fails and for the day that doesn’t?



I have tried these methods separately, and removing the case is feasible, while the other methods remain unchanged, including
`Remove the limit`
`Remove the order by`
The segment distribution is like this
Regarding segment, I have noticed a phenomenon where all the other days were the same segment. Yesterday’s data is always five, and only yesterday’s data can cause this problem. Does it seem like a coincidence?
Meanwhile, how can I set it to merge to verify that merging can avoid this problem?

I thought this happens at the end of the ingestion task lifecycle, when the Supervisor tells the ingestion tasks to pause so it can reset "endpoints" to cause the tasks to finish their work immediately, build their last segments, and exit.

That's correct, and it also happens in the additional case of intermediate publishing (the task may continue to work after the publish)

Either way it's connected with the checkpoint/publish process

Typically, the pause is very short, like a few seconds. If it's longer then you may have some issue with your Overlord or metadata store

I don't think either implementation cares, it is all down to your configuration.

Hi David, I have installed druid operator and did the required configuration based on <https://druid.apache.org/docs/latest/development/extensions-contrib/k8s-jobs.html>
```# within common runtime properties
druid.indexer.runner.namespace=druid-operator
druid.indexer.queue.maxSize=10
druid.processing.intermediaryData.storage.type=deepstore 
## within the overlord config
druid.indexer.runner.type=k8s
druid.indexer.task.encapsulatedTask=true```
However, I always see the error

When I see the overlord log, it says
```        at java.lang.Thread.run(Thread.java:829) ~[?:?]
Caused by: io.fabric8.kubernetes.client.KubernetesClientException: Failure executing: GET at: <https://10.255.0.1:443/apis/batch/v1/namespaces/druid-operator/jobs?labelSelector=druid.k8s.peons>. Message: Forbidden!Configured service account doesn't have access. Service account may have been revoked. jobs.batch is forbidden: User "system:serviceaccount:druid-operator:default" cannot list resource "jobs" in API group "batch" in the namespace "druid-operator".
        at io.fabric8.kubernetes.client.dsl.internal.OperationSupport.requestFailure(OperationSupport.java:728) ~[?:?]
        at io.fabric8.kubernetes.client.dsl.internal.OperationSupport.requestFailure(OperationSupport.java:708) ~[?:?]
        at io.fabric8.kubernetes.client.dsl.internal.OperationSupport.assertResponseCode(OperationSupport.java:657) ~[?:?]
        at io.fabric8.kubernetes.client.dsl.internal.OperationSupport.lambda$handleResponse$0(OperationSupport.java:587) ~[?:?]
        at java.util.concurrent.CompletableFuture$UniApply.tryFire(CompletableFuture.java:642) ~[?:?]
        at java.util.concurrent.CompletableFuture.postComplete(CompletableFuture.java:506) ~[?:?]
        at java.util.concurrent.CompletableFuture.complete(CompletableFuture.java:2073) ~[?:?]
        at io.fabric8.kubernetes.client.dsl.internal.OperationSupport.lambda$retryWithExponentialBackoff$2(OperationSupport.java:629) ~[?:?]```
It seems the service account doesn't have privilege.
I have created the role and rolebinding as below
```apiVersion: <http://rbac.authorization.k8s.io/v1|rbac.authorization.k8s.io/v1>
kind: Role
metadata:
  name: druid-cluster
rules:
- apiGroups:
  - ""
  resources:
  - pods
  - configmaps
  - jobs
  verbs:
  - '*'
---
kind: RoleBinding
apiVersion: <http://rbac.authorization.k8s.io/v1|rbac.authorization.k8s.io/v1>
metadata:
  name: druid-cluster
subjects:
- kind: ServiceAccount
  name: default
  namespace: druid-operator
roleRef:
  kind: Role
  name: druid-cluster
  apiGroup: <http://rbac.authorization.k8s.io|rbac.authorization.k8s.io>```
Also if I test the permission, it shows that I have the privilege
```root@bh-gsn-57-xtz-dev-01:~/druid-op/druid-cluster# kubectl auth can-i create jobs --as=system:serviceaccount:druid-operator:default -n druid-operator
yes```
Not sure what I am doing wrong!

link to same thread with response: <https://apachedruidworkspace.slack.com/archives/C030K0Z7S1Z/p1685557121420909>

<@U05AEKV6Y81>, for data that’s already been published/ingested, I think you need to run a separate batch ingestion task with either deep storage or the druid datasource as your input source. Streaming supervisors can’t do much after the task is complete in order to overwrite if that makes sense. Two ways to overwrite data:
• Run `REPLACE` queries in MSQ: <https://druid.apache.org/docs/latest/multi-stage-query/concepts.html#overwrite-data-with-replace>
• Or if you’re on an older system that doesn’t have MSQ, as you pointed out, native batch ingestion should work too: <https://druid.apache.org/docs/latest/ingestion/native-batch.html#replacing-or-appending-data>

hmm, i think its more of a disclaimer about how complete the functionality is

and there are also some issues with using them on realtime tasks i think, though i believe there is an active PR trying to fix that

the broadcast join functionality is a bit rough/unfinished, but is eventually planned to be a replacement for lookups i think

<https://github.com/apache/druid/pull/10224> has some details of what is essentially the unfinished reference implementation we have for integration tests

heh.. from 3 years ago, you can see we’re a bit slow on finishing this stuff up :sweat_smile:

ah this is the fix for realtime tasks <https://github.com/apache/druid/pull/14239>

So...  Should I be using a `loadForever` rule instead?

well, if you’re planning on doing joins with this to other datasources its probably worth investigating if the broadcast stuff works well enough for your use case, since it should be a fair bit more performant

i don’t think druid can automatically do anything fancy just by having the segments loaded everywhere other than just have a larger pool of nodes to process those tables, but it will still need to do subqueries to compute the join

the broadcast join stuff besides loading the segment everywhere also lets the join be pushed down to the data nodes so that the join can be computed without a subquery

it has some limitations though, such as the datasource can only be a single segment and has to be ingested with that extra stuff in the indexSpec to specify the key columns (which should be unique iirc)

the broadcast rule itself should work as well as a loadForever rule with many replicas though afaik, so that part is less experimental than the docs specify i think, just not a ton of advantage other than having more data nodes to compute for that table

<https://github.com/apache/druid/pull/10020> has some more details about how the broadcast join stuff works

IMO right now it's best, from an operational perspective, to use either:

• a regular table with `loadForever`
• a lookup, rather than a regular table <https://druid.apache.org/docs/latest/querying/lookups.html>

the perf implications of a regular table w/ `loadForever` are the broker will need to gather and broadcast the table for each query, which if the table is small, is probably not big deal

lookup will perform better, as they're prebroadcast; at the cost of needing to be managed in a separate way from your regular tables

the broadcast stuff is a not-yet-complete story that aims to enable prebroadcast of regular tables. when it's complete we'd recommend it for this case

however, right now i wouldn't want you to have to wrestle with unless you really have to, due to incompleteness :slightly_smiling_face:

yeah that is fair, i was more trying to mention that there isn’t really a benefit to have it on loadForever with num replicas = num data servers since its still going to have to subquery

Oh, I didn't know you had to do something special to create a lookup table.

My team uses the UI. They copy and pasted the data from a simple CSV file. Is there functionality there to turn that table into a lookup table instead?

Btw, it's all in a single segment. When the team needs to update the data, they kill the old segment file (just in case).

lookup tables are a sort of special construct which we eventually plan to replace with the broadcast stuff once we finish it, but <https://druid.apache.org/docs/latest/querying/lookups.html> has the details

they are limited to a single key to value lookup, so if the table has multiple columns it probably wont work

they aren’t real segments, though we did retrofit them so you could query the key and value column as a table via sql when we added some of the broadcast stuff

there are a couple of extensions which provide the lookups, i think <https://druid.apache.org/docs/latest/development/extensions-core/lookups-cached-global.html> is the more commonly used one

the web console does have stuff to manage the lookups, if the limitation of only having a single key to value replacement works for your use case it will be the most performant at least when using the `LOOKUP` function, especially if the lookup is 1:1 replacement since Druid can do some optimizations to only do the replacement when it really needs to

can you run with approximation turned off in console? That might be causing this behaviour

Yes, it's showing now same result, after turned off the use count(distinct) approximation. thank you.

what is that approximation does ?

it's a faster way of doing distinct count, at the cost of accuracy.

while ingest the data, for the date-time value ? whats the data type can set ?  is string or long ?

I mean apart from __time  column  another datetime column value.

Hi Ashok, regarding your second question, this doc page <https://druid.apache.org/docs/latest/ingestion/schema-design.html#secondary-timestamps|HERE> recommends to ingest secondary timestamp fields as LONG in milliseconds format.

ok, John, I tried but it becomes Null value, and If I change to String the datetime show correctly in Load data - data schema step - druid console

oh i think it should be in millisecond format ...  i just put it as date-time format and tried as Long during ingestion.

I've used `timestamp_parse` for this purpose.

This is often good enough `timestamp_parse("field")`

<https://druid.apache.org/docs/latest/misc/math-expr.html#time-functions>

Here is the link with detailed information: <https://druid.apache.org/docs/latest/operations/clean-metadata-store.html#segment-records-and-segments-in-deep-storage-kill-task>

Screenshot 2023-06-01 at 4.43.29 PM.png

Screenshot 2023-06-01 at 4.43.00 PM.png

Manually also you can delete the data from deep storage using below two steps:

Yes you can reindex a time window smaller than segment granularity but complete data for that hour will be replaced with the 1 minute data that you were reindexing.

For example if you have a day granularity data and reindexing a specific hour then your day segment will gets replaced with one hour of data(segment granularity day)

if you have hour granularity data for 24 hours (24 segments) and you are reindexing specific hour then only that segment for the specific hour will get replaced.

May be we can reindex the complete data first into minute interval and then reindex the specific minute and then reindex back to hour.

OK, so for the time being you would advise to reindex the complete hour and add the appropriate minute I want to drop in the filter with __time=x I guess ?

Yes, you could try that to drop specific records

You can also ingest a new segment with 1 MINUTE granularity in OVERWRITE mode (appendToExisting=false) ... that one minute segment will overshadow your existing HOUR segment, and the queries will see the correct "latest" version of all data across that hour.

You do not need to match segment granularities when you are using overwrite mode.

The caveat is that you cannot create a new segment with no data in it (unless you create a tombstone segment, but I don't know if that can be created on its own.) ... so you may have to ensure that you have at least one record of data to insert into that new segment.    I believe based on your original question (you are deleting data related to only one dimension) that there will be other data in that minute interval ... so you should be okay.

how come there are `\` before the `"` around test? in what you posted? Are yo submitting the string from something that does its own escaping?

you are correct that the docs should call out that str2 is a regexp

Do you have an extra layer of escaping on top of Druid? Can you double check your query in the console as a baseline?

Ok docs have been updated to indicate the regex

To answer your question, it's part of the ingestion spec (transform).

Ah... well you see what is happening here is that you are not using SQL (as I first assumed you were) the transform spec uses a lower lever expression language called math-expr (<https://druid.apache.org/docs/latest/misc/math-expr.html>). In math-expr you have to escape `\` in literals (unlike in SQL). So to encode the regex `\|` you have to have `'\\|'` as the second argument to `string_to_array`. But that whole thing is contained within a JSON string representing the ingestion spec... so you need to express `string_to_array("test", '\\|')` in the json you actually have to write `"string_to_array(\"test\", '\\\\|')"` . I hope this answers your question of why the delimiter is going through multiple stages of interpolation. It is intended.

1) if you are using batch ingestion you can switch to <https://druid.apache.org/docs/latest/multi-stage-query/index.html|SQL based ingestion> which has less escaping due to the way SQL escaping is done as you saw above

Interesting. Are all string literals interpolated in math-expr?

2) you can avoid using the `\` character and use the fact that math-expr allows you to enter unicode code points . os write `\u005C` which will be `"...\\u005C..."` when written inside of a JSON string

that is in fact what the Druid SQL planner does when it generates the internal JSON based query

Although I don't know if that is more readable

I am not sure what you mean by "string literals interpolated"

Well, `'\\|'` is a string literal and it's clearly being interpolated. I didn't know you could use Unicode code points, for instance. That must be why it is interpolated.

btw I am no expert on math-expr. In fact I detest writing it... whenever I need to write a math-expr (like in the process of answering this inquiry) I always write a SQL query and then do explain plan in the console and copy out the expression I need from the plan.

hm... in my vocabulary interpolation in the context of strings means inlining expressions into string literals (instead of concatenating many fragments together). Like in JavaSscript you can do
```console.log(`Hello ${firstName}, the time right now is ${new Date()}`);```
to my knowledge you can not do :point_up: in math-expr.

Good point. I'm unsure what to call it when a string goes through a transformation that requires an escape sequence to overcome other than string interpolation. But Druid string literals only supports specific escape sequences and not "full interpolation".

For appending data the segment granularity of the must match.

But within a datasource different time chunks can be set up with different segment granularity.

So, for example, if you leave your ingestion at DAY level and never ingest old (i.e. yesterday's) data, then on the first of each month you can compact/reindex all of last month's data from DAY to MONTH.

If you want to change your current ingestion from DAY to MONTH granularity, then you first have to compact/reindex the current month of data from DAY to MONTH so it will match the MONTH granularity of the ingestion task.

I will not do old data....  I am actually doing ingestion using Kafka stream in real time. Assume I do modify the data-source spec for tomorrow 02-June onwards. then in this case will it work ?

I mean old data in day wise segmented and from tomorrow onwards in month wise segment  !?  or this is not the case we can do !

I mean druid require entire data-source to reindexed to month wise ?!

You just need to make sure there are no DAY segments in the month of June :slightly_smiling_face:   if there is anything existing, then reindex it to MONTH, even though it may be a small segment, doesn't matter, the granularity just has to match what's in your supervisor spec.

Btw the downsize of this is that you will not easily be able to compact the current month time chunk so it will become very fragmented as the ingestion tasks cycle and create new segments every (hour?).  This could affect performance if the fragmentation is extreme.

Or can I achive the same, without editing the Spec of data-source and just do by Compaction (dynamic) ?

What are your retention rules?
In general, the process goes something like this:
• coordinator wakes up every minute (*`druid.coordinator.period`*) and checks for published/used segments and all the current retention rules
• for each segment it evaluates which retention rule to apply
• it prioritizes loading segments which have no presence within the target tier and then replicas
• it generates load/drop commands for historicals
The historicals read the load/drop commands from their load queue which has a max depth of *<https://druid.apache.org/docs/latest/configuration/index.html#dynamic-configuration|maxSegmentsInNodeLoadingQueue>*. Each historical works independently to execute its load/drop commands using one or more threads based on each historical's config *<https://druid.apache.org/docs/latest/configuration/index.html#storing-segments|druid.segmentCache.numLoadingThreads>*

To answer your question directly, AFAIK, the coordinator does not prioritize by tier but you can control the resources used by the historicals in each tier for segment loading.

Okay that answers my question, I was under the incorrect assumption that historicals coordinate their data loading to balance available segments. Since they all work independently I can just configure their settings individually by tier. 

My retention rules are setup in a way where there's no data overlap between both tiers as well as no replicas of any segment. 

Thanks for the help Sergio

I use a loadByPeriod (P3M) for my hot tier followed by a loadForever to my cold tier.

Hi Anoop! Are you trying to do the timeseries query over some time granularity which is producing multiple row results? Or do you have a single row result from the timeseries query?

Are you looking for something like this:
```SELECT "__time"
FROM "kttm-nested-v2-2019-08-25"
WHERE "session_length" = (SELECT MAX("session_length") FROM "kttm-nested-v2-2019-08-25") 
LIMIT 1```
an explain of that returns the native query (with the nested timeseries query to get the max):
```{
  "queryType": "scan",
  "dataSource": {
    "type": "join",
    "left": {
      "type": "table",
      "name": "kttm-nested-v2-2019-08-25"
    },
    "right": {
      "type": "query",
      "query": {
        "queryType": "timeseries",
        "dataSource": {
          "type": "table",
          "name": "kttm-nested-v2-2019-08-25"
        },
        "intervals": {
          "type": "intervals",
          "intervals": [
            "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
          ]
        },
        "granularity": {
          "type": "all"
        },
        "aggregations": [
          {
            "type": "longMax",
            "name": "a0",
            "fieldName": "session_length"
          }
        ],
        "context": {
          "queryId": "eb825977-2bd6-4f5f-ab80-bb2af837b76a",
          "sqlOuterLimit": 1001,
          "sqlQueryId": "eb825977-2bd6-4f5f-ab80-bb2af837b76a",
          "useNativeQueryExplain": true
        }
      }
    },
    "rightPrefix": "j0.",
    "condition": "(\"session_length\" == \"j0.a0\")",
    "joinType": "INNER"
  },
  "intervals": {
    "type": "intervals",
    "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
    ]
  },
  "resultFormat": "compactedList",
  "limit": 1,
  "columns": [
    "__time"
  ],
  "legacy": false,
  "context": {
    "queryId": "eb825977-2bd6-4f5f-ab80-bb2af837b76a",
    "sqlOuterLimit": 1001,
    "sqlQueryId": "eb825977-2bd6-4f5f-ab80-bb2af837b76a",
    "useNativeQueryExplain": true
  },
  "granularity": {
    "type": "all"
  }
}```

value

i want to fetch timestamp for this value field in aggregations

looks like the same question: <https://apachedruidworkspace.slack.com/archives/C0303FDCZEZ/p1686135562377219?thread_ts=1686032990.676209&amp;cid=C0303FDCZEZ>

if this isn't helpful please be a little more specific about what you're looking for. examples would be helpful

No difference, then explain plan uses the same filtered aggregator in both cases:
```{
      "type": "filtered",
      "aggregator": {
        "type": "longSum",
        "name": "a0:sum",
        "fieldName": "session_length"
      },
      "filter": {
        "type": "selector",
        "dimension": "language",
        "value": "en"
      },
      "name": "a0:sum"
    },
    {
      "type": "filtered",
      "aggregator": {
        "type": "count",
        "name": "a0:count"
      },
      "filter": {
        "type": "selector",
        "dimension": "language",
        "value": "en"
      },
      "name": "a0:count"
    }```


Interesting....
In a simple test on kttm-nested sample data, the explain for the first one is two nested `groupby` queries while the second one is a `timeseries` query. I'm not knowledgable enough about the inner working of each query type to know what conditions would cause one to be faster than the other.  One thought: Perhaps groupBy in this scenario only needs to read the dictionary for the column since you are selecting a constant value and timeseries scans the rows?

You mentioned "sometimes", have you figured out when one is better than the other? column cardinality? total rows? # of segments?

I think the sweet spot is when cardinality is under 5 million.

I just tested on another datasource with 650 million rows and cardinality of 20 million and the APPROX_COUNT_DISTINCT took 7.88 seconds while the GROUP BY took 65.89 seconds.

I am wondering why APPROX_COUNT_DISTINCT doesn't just read the dictionary for the column.

Oh, for the above test, I should add that the actual test included a CONCAT, so it was:
```SELECT COUNT(*)
FROM (
  SELECT 1
  FROM "datasource"
  WHERE __time &gt;= CURRENT_TIMESTAMP - INTERVAL '1' DAY
  GROUP BY "Field1", "Field2", "Field3"
)```
&gt; Took 65.89 seconds
```SELECT APPROX_COUNT_DISTINCT(CONCAT("Field1", "Field2", "Field3")
FROM "datasource"
WHERE __time &gt;= CURRENT_TIMESTAMP - INTERVAL '1' DAY```
&gt; Took 7.88 seconds


note that `APPROX_COUNT_DISTINCT_DS_HLL` is generally faster &amp; more accurate than `APPROX_COUNT_DISTINCT`

we're looking into changing the default, although it's tricky to do a migration path that maintains compatibility for various cases, so we haven't done it yet

i suspect with that one you'll find it is more competitive with a plain `GROUP BY` at lower cardinality

intuitively though what is going on is that for low cardinalities, the operations we need to do to update a hash table (`GROUP BY`) and the ops we need to update a sketch are similar in complexity, and both fit comfortably in a small amount of memory, so sketches don't have an inherent advantage. (in general, their inherent advantage is they use less memory)

so the faster one will be down to which one is better optimized for that case where the number of unique things being tracked is relatively small

`GROUP BY` is pretty good at that, and `APPROX_COUNT_DISTINCT_DS_HLL` is generally better than `APPROX_COUNT_DISTINCT`

although i'm not sure which one between `GROUP BY` and `APPROX_COUNT_DISTINCT_DS_HLL` will be better or where a breakpoint, if any, is!

so i'm curious if you try that out, how it goes :slightly_smiling_face:

Not sure what you mean. How is Druid deployed?
single server or cluster?
kubernetes? docker? bare metal/vms?

Hey guys , how do we retain the deleted segments data

If you only dropped the segments, they are still available in deep storage and you can bring them back. If you ran a kill task, the data is gone.

is by "retain" you mean undelete then you can do it via the console (if you simply marked the segments as unused) - you have not run a kill task yet

click "Show unused" in the datasources view:

Hey guys, we ran the task and data sources was completely deleted. we can't retain I hope. What could be the reason for deleted the datasource.

I have tested "Mark as unused segment by Interval" and opted the time period "`2021-11-04T00:00:00.00/2021-11-05T00:00:00.00`  but datasource was deleted . Any idea on this.

if you had the segments as unused and you ran a kill task then they are gone

do you mean, for each hour, you want to find the timestamp of the row that had the highest value of `VALUE` in that hour?

most natural way to write that in SQL would be something like—

```SELECT
  FLOOR(__time TO HOUR),
  MILLIS_TO_TIMESTAMP(ANY_VALUE(TIMESTAMP_TO_MILLIS(__time)))
FROM "DATASOURCE"
WHERE (FLOOR(__time TO HOUR), "VALUE") IN (
  SELECT
    FLOOR(__time TO HOUR) AS t,
    MAX("VALUE") as maxValue
  FROM "DATASOURCE"
  GROUP BY 1
)
GROUP BY 1```
you can do `EXPLAIN PLAN FOR` on that to see how it runs

the log looks sort of like a task restored itself after MM restart, and was unable to contact the master after restore

Could it be that the master services (Coordinator/Overlord) were down?

If not— could it be a network or OS problem?

1. If you have both create and delete records in the same table and the timestamps in separate columns, then how about a simple group by with having clause:
```select emp_id, emp_name, ..., emp_othercols, max(term_date) term_date
from employees
group by emp_id, emp_name, ..., emp_othercols
having term_date is null```
2. Druid segments are immutable once created ... however you can replace a time chunk with data pulled from the same time chunk and modified ... and you can also overwrite a time chunk with a smaller time chunk that contains the data you want to update, so you don't have to replace the entire time chunk.  If you want more info about this let me know and I can send you some more info on this.

Hello John, Thank You. Will try it out the point 1 on delete case.  Yes I want more info on the point 2 on Edit case.

Fast Updates (and Deletes) with Druid.pdf

Hi Ashok, here's a short writeup I put together recently.  Let me know if you have any questions.

you could have a lookup `employee_id =&gt; "true"` that only contains deleted employees then you could do  `SELECT * FROM employee WHERE LOOKUP(id, 'deleted_employees') &lt;&gt; 'true'`

since the lookup is materialized and broadcast this would be much faster than a sub query

Thanks Vadim, I will check it with lookup

Thank You John, will check your doc, thanks.

you can do this, it may increase perf if you haven't fully saturated any resource (CPU, memory, disk)

OTOH if you have fully saturated some resource, then increasing worker capacity will likely have no effect on perf or even make it worse

Worker capacity and memory usage is linked

the simplest thing is use the data loader in the web console to get started—

"Batch - SQL [multi stage query]" then select HDFS

make sure to load the `druid-hdfs-storage` extension

Thanks Gian, I don't find such option in druid data loader.

Actually my requirement : Need to create datasource on top of hdfs deep storage.
Recently one datasource was dropped but hdfs deep storage data existed.  So I just want recover the dropped datasource.

oh i see, i thought you wanted to load some source data

best way to restore a datasource is to use a backup of your metadata store

the `druid_segments` table would have pointers to all the relevant files on hdfs

or, it's also possible you don't even need a backup, if the records are there with `used = false` you can set `used = true` and they will be reloaded

`select * from INFORMATION_SCHEMA.TABLES`

The above shows the required table i.e dropped datasource.

With `select * from INFORMATION_SCHEMA.TABLES` query we are able to see the deleted data source however when we execute `SELECT * FROM sys.segments WHERE datasource = 'deleted_datasouruce_name'` query it is not returning any records for the deleted data source. In this situation could you please suggest the appropriate way to restore the deleted data source using the files available in deep storage?

Did you have a backup of the metadata store? If so, you could restore it to another DB instance and then copy the records in druid_segments for that datasource back into the metadata DB.

We don't have the backup of the metadata store.
But we have druid deep storage data i.e. segments file data which was in zip format.
When I was unzipped w e found the following files.
1&gt; Will it be helpful to  restore the dropped datasource using the following files.
i.e. `meta.smoosh, factory.json, 00000.smoosh, version.bin`
2&gt; Whenever we opted editspec option it's showing the following exception . Any idea on this.

#1 - in theory, you could build the `druid_segments` rows manually that correspond to each segment file you have in S3. Rebuilding <https://druid.apache.org/docs/latest/dependencies/metadata-storage.html#segments-table|the payload> is likely the hard part.
#2 - is that error from the Druid Console? Are you trying to edit an existing supervisor? Is the metadata for it still in `druid_supervisors` ?

What use case do you have in mind? A benchmark may not be your best proxy... like you mention, they are usually not representative of real production use cases.

Yes. I just want to see how druid's behavior in a more realistic workload (no preference but real). So I am wondering if you knows any production traces that is suitable for druid or if imply has any open-sourced traces.

Maybe look for some time series sample data. Stock market prices would be a good example data set. 