Apache Druid

not on the same middle manager for sure. why do you want to do this?

Figured this: The control is with the imply wrapper: bin/svlogd-wrapper where STDOUT is piped to files. Removing that lines, removes it

with the prometheus emitter, not all metrics are emitted out of the box, in order to avoid too much load on prometheus

you can add them by editing your metric mapping:

<https://druid.apache.org/docs/latest/development/extensions-contrib/prometheus.html#metric-mapping>

the default file is <https://github.com/apache/druid/blob/master/extensions-contrib/prometheus-emitter/src/main/resources/defaultMetrics.json>

Hi, thanks for the advise, I'll test it but honestly, I think this (kind of undocumented) behavior is counter intuitive.

Don't you think that the documentation should include a full-metrics mapping resource for reference and more importantly, to ease its setup. That would also potentially prevent from having this kind of <https://github.com/apache/druid/issues/6646|issues>.

Definitely agree the docs could be improved! The intent is that the default set of emitted metrics works for most people, and limits load on prometheus, but it should be easier to see how to edit it if you need to.

If you're interested in helping, a good place to start could be including the default mapping into the docs, or describing where it can be found and edited in the distribution tarball.

Otherwise hopefully someone else would be able to make those changes

Gian I'd love to contribute but I'm lacking enough of knowledge on the metrics topic to be able to do so. I just started trying out this feature  and there a several things that are unknown to me. After testing your suggestion I still facing some issues:

1. I defined the `segment/count` on the custom mapping: 
```"segment/count" : { "dimensions" : ["dataSource"], "type" : "gauge", "help": "Number of used segments belonging to a data source. Emitted only for data sources to which at least one used segment belongs."},```
This worked and the metric is reported by both coordinator and historical with  the single dimension (`dataSource` ). However as documentation states, we can define different dimensions(labels) on metrics that are being reported by multiple services.

Interestingly enough the default mapping has already a definition of the `segment/count` for the coordinator and the historical to have different set of dimensions  but these are never populated on Prometheus (or emitted by Druid):
```"coordinator_segment/count" : { "dimensions" : ["dataSource"], "type" : "gauge", "help": "Number of served segments."},
"historical_segment/count" : { "dimensions" : ["dataSource", "tier", "priority"], "type" : "gauge", "help": "Number of served segments.```
Following advice from the previously referenced issue I prefixed using the service name as it appears on the service configuration/properties  but didn't work either:
```"druid/coordinator_segment/count" : { "dimensions" : ["dataSource"], "type" : "gauge", "help": "Number of served segments from Coordinator."},
"druid/historical_segment/count" : { "dimensions" : ["dataSource", "tier", "priority"], "type" : "gauge", "help": "Number of served segments from Historicals."},```

2. The following block of metrics is also defined by default but none of them are emitted by Druid:
```"ingest/events/thrownAway" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of events rejected because they are outside the windowPeriod."},
"ingest/events/unparseable" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of events rejected because the events are unparseable." },
"ingest/events/duplicate" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of events rejected because the events are duplicated."},
"ingest/events/processed" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of events successfully processed per emission period." },
"ingest/events/messageGap" : { "dimensions" : ["dataSource"], "type" : "gauge", "help": "Time gap in milliseconds between the latest ingested event timestamp and the current system timestamp of metrics emission."},
"ingest/rows/output" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of Druid rows persisted."},
"ingest/persists/count" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of times persist occurred." },
"ingest/persists/time" : { "dimensions" : ["dataSource"], "type" : "timer", "conversionFactor": 1000.0, "help": "Seconds spent doing intermediate persist."},
"ingest/persists/cpu" : { "dimensions" : ["dataSource"], "type" : "timer", "conversionFactor": 1000000000.0, "help": "Cpu time in Seconds spent on doing intermediate persist." },
"ingest/persists/backPressure" : { "dimensions" : ["dataSource"], "type" : "gauge", "help": "Seconds spent creating persist tasks and blocking waiting for them to finish." },
"ingest/persists/failed" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of persists that failed." },
"ingest/handoff/failed" : { "dimensions" : ["dataSource"], "type" : "count", "help": "Number of handoffs that failed." },
"ingest/merge/time" : { "dimensions" : ["dataSource"], "type" : "timer", "conversionFactor": 1000.0, "help": "Seconds spent merging intermediate segments" },
"ingest/merge/cpu" : { "dimensions" : ["dataSource"], "type" : "timer", "conversionFactor": 1000000000.0, "help": "Cpu time in Seconds spent on merging intermediate segments."},```
I have plenty of batch ingestion jobs (index_parallel) running periodically along with Kafka ingestion events

Thanks for you help

hmm. I'm not familiar with the implementation of this extension or how the prefixing is supposed to work— perhaps there is some issue with that?

Regarding the segment/count by druid service:  this guy confirmed that it works on Druid 0.25.0 <https://github.com/apache/druid/issues/13202#issuecomment-1454652543>

Anyways, I'll keep digging and report any issue/potential bug on Github

CleanShot 2023-04-28 at 11.53.20@2x.png

```      {
        "type": "not",
        "field": {
          "type": "selector",
          "dimension": "country_iso",
          "value": "br",
          "extractionFn": null
        }```

the `select` clause is actually well constructed, as
```(notnull(\"d0\") &amp;&amp; (\"d0\" != 'br'))```
but it seems whatever is building the `where` clause is missing the `not null` part somehow

Hi <@U041X7X3VND>, one quick thought that comes to my mind - is `country_iso` a multi-value string?

hmm. I think its because any equality comparison with `null` is supposed to return false.

hi <@U032CA4JJV6> nope, just regular string

if country_iso is null, the null &lt;&gt; 'br' will return null and `false`

so planning is correct but druid is not evaluating it correctly

<@U031A9D19NU> it's actually one of the other dimensions i added to the dataset, in case you want to see the outcome

not sure what you mean. but there is a fix for it. you need to set `druid.expressions.useStrictBooleans=true` but this option is not available before 0.23

<https://druid.apache.org/docs/latest/misc/math-expr.html#logical-operator-modes>

I mean you can see the outcome of `country_iso &lt;&gt; 'br'` right there in the screenshot, it's column EXPR$4 (returning true)

I'm planning on upgrading to 25.0.0+ anyway, good to know!

yeah. that's wrong. you should use sql compatible mode and also set the flag useStrictBooleans

FWIW, we are going to turn these on by default anytime now.

ah, thanks for the report! fyi, more info about our plans is at: <https://github.com/apache/druid/issues/14154>

I don't have specific experience with redis, although the backend implementation shouldn't really matter (the caching code is structured to be independent of backend implementation)

Maybe you are seeing a general issue with result level caching not being configured? (It is off by default)

Or maybe hitting some maximum payload or timeout limit in the redis extension?

Here is the test i have done . I am firing 5 queries which the result is less than 1 kb. I turned off  broker cache , enabled historical cache which is caffeine . Configured redis cache  and enabled result lvel cache(populate `druid.broker.cache.useResultLevelCache` and  `druid.broker.cache.populateResultLevelCache`  ) in Broker . I am using  v2 group by queries for my testing .  I could not find the result level cache is being hit .
I also read the following doc on cache .<https://druid.apache.org/docs/latest/querying/caching.html#scenarios-where-caching-does-not-increase-query-performance>
```Whole-query caching doesn't work for the following:

queries that involve an inline datasource or a lookup datasource.
GroupBy v2 queries.
queries with joins.
queries with a union datasource.```

Here  I assume the doc is refering to segment cache not result level cache .  If this is applicable for result cache , may be there could be some reason why its not supported.  But eager to know the reason .

I have done some profiling in Redis and realized that the cahce is not being hit .  I used redis just because it has profiler to be used from the cache  level.

ah, "Whole-query caching" does refer to result level cache

so if you are in one of those situations it wouldn't use the result level cache

Hi Curious to know why caching doesnt work fro GroupBy V2 queries.

it could be just a missing implementation?

Oh .. didnt see ur response . I tested with result level cache, after increasing the `druid.broker.cache.maxEntrySize` topn queries started the hitting the cache.  I know the result level cache is a double edge sword .  For the query patten for my usecase the performance is good . Exploring further why groupby is not  pushing the result to cache

Kindly check the retention rules as well for the data source, the timestamp on the segment which is mentioned is of `2022-03-25`

Thanks <@U031A9D19NU> <@U030LNJJ13M>

The segment is not loaded in any of the historicals.

Retention period is a good point- retention period in this case is 30 days. It might be an issue where a new segment is reported as unavailable if its time period is already outside the retention period.

That is exactly how it works: if a new segment is outside the retention period, it's immediately dropped, so wouldn't show up in `sys.segments` very long

do you see in the task logs the instruction to pause but an instruction to resume never arrives?

and what is `task-runner-priority-0` doing in this thread dump?

i dont see `task-runner-priority` in the thread dump. this is a thread dump of the peon though

&gt; do you see in the task logs the instruction to pause but an instruction to resume never arrives?
nope

reseting the supervisor did solve the problem as the new set of tasks ingest without any issue. But still not sure what happened to the old set of tasks

hmm. what happened to the `task-runner-priority-0` thread? that's the one supposed to do the reading. maybe you can share the logs

I dont see task-runner-priority-0 in the thread dump.

for peon launched by MM, it'll typically be called `task-runner-0-priority-0` (or, in rare cases, `task-runner-0-priority-N` where `N` is an overridden priority)

for Indexer the first number can be variable

if you don't see that thread, it means the task main thread has exited, which typically means the task should be shutting down

there's this code:

```                            try {
                              taskStatus = task.run(toolbox);
                            }
                            catch (Throwable t) {
                              LOGGER.error(t, "Exception caught while running the task.");
                              taskStatus = TaskStatus.failure(
                                  task.getId(),
                                  "Failed with an exception. See indexer logs for more details."
                              );
                            }```

So you should see that `Exception caught while running the task` message if it exited by throwing an exception

The issue happened again where ingestion was paused for about 20mins across all tasks for a particular supervisor. I took a thread dump on one of the task and did see task-runner-priority-0.
```"task-runner-0-priority-0" #116 daemon prio=5 os_prio=0 tid=0x00007f3d12413800 nid=0x7a40 waiting on condition [0x00007f394ac90000]
   java.lang.Thread.State: WAITING (parking)
	at sun.misc.Unsafe.park(Native Method)
	- parking to wait for  &lt;0x00000005df6dd588&gt; (a java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject)
	at java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
	at java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2044)
	at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskRunner.possiblyPause(SeekableStreamIndexTaskRunner.java:1270)
	at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskRunner.runInternal(SeekableStreamIndexTaskRunner.java:570)
	at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTaskRunner.run(SeekableStreamIndexTaskRunner.java:267)
	at org.apache.druid.indexing.seekablestream.SeekableStreamIndexTask.run(SeekableStreamIndexTask.java:145)
	at org.apache.druid.indexing.overlord.SingleTaskBackgroundRunner$SingleTaskBackgroundRunnerCallable.call(SingleTaskBackgroundRunner.java:451)
	at org.apache.druid.indexing.overlord.SingleTaskBackgroundRunner$SingleTaskBackgroundRunnerCallable.call(SingleTaskBackgroundRunner.java:423)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:750)

   Locked ownable synchronizers:
	- &lt;0x00000005c2aa2020&gt; (a java.util.concurrent.ThreadPoolExecutor$Worker)```
Looks like a pause was requested from the overlord for checkpointing?. Why would it be paused for so long (~20mins) though

look for this particular task id in overlord logs and that may offer some clues

to me this looks kind of like <https://github.com/apache/druid/issues/11414> (tasks stuck in pause)

We made a bunch of improvements here in 25: <https://github.com/apache/druid/issues/11414#issuecomment-1311505442>

One of them isn't on by default in 25 (but will be in 26): `chatAsync`. if you aren't using it yet, try setting `chatAsync: true` as described here <https://druid.apache.org/docs/latest/development/extensions-core/kafka-supervisor-reference.html>

taking a closer look, looks like the lag spike up every `taskDuration` period

these changes were done to support a cluster that had ~1000 ingestion tasks in a single datasource

this is another nice one in 25.0 <https://github.com/apache/druid/pull/13369>

also not on by default; we will be making this the default in the future (I forget if it's gonna be default in 26 or not, though)

Screenshot 2023-05-01 at 4.38.59 PM.png

From the error it seems like the supervisor service is not available. Could you please provide a bit more context on what you were trying to do before this happened? Can you please provide the supervisor logs?

my team member created a table and it must have lots of data after that we started facing this issue .

and i exec in one of my middle manager pod saw this error 1 druid  12:38 java -server -XX:+ExitOnOutOfMemoryError -XX:+UseG1GC -Duser.timezone=UTC -Dfile.encoding=UTF-8 -<http://Djava.io|Djava.io>.tmpdir=var/tmp -Djava.util.logging.manager=org.apache.logging.log4j.jul.LogM

broker:
 ## If false, broker will not be installed
 ##
 enabled: true
 name: broker
 replicaCount: 5
 port: 8082
 serviceType: ClusterIP

 config:
  DRUID_XMX: 2048m
  DRUID_XMS: 2048m
  DRUID_MAXDIRECTMEMORYSIZE: 700m
  druid_processing_buffer_sizeBytes: '50000000'
  druid_processing_numMergeBuffers: 4
  druid_processing_numThreads: 7
  # druid_monitoring_monitors: '["org.apache.druid.client.cache.CacheMonitor", "org.apache.druid.server.metrics.QueryCountStatsMonitor"]'

 ingress:
  enabled: false
  annotations: {}
   # <http://kubernetes.io/ingress.class|kubernetes.io/ingress.class>: nginx
   # <http://kubernetes.io/tls-acme|kubernetes.io/tls-acme>: "true"
  path: /
  hosts:
   - chart-example.local
  tls: []
  # - secretName: chart-example-tls
  #  hosts:
  #   - chart-example.local

 resources:
  limits:
   cpu: 1000m
   memory: 5000Mi
  requests:
   cpu: 900m
   memory: 4000Mi

 nodeSelector:
  allowed: druid-uat

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed

 affinity: {}

 podAnnotations: {}

coordinator:
 ## If false, coordinator will not be installed
 ##
 enabled: true
 name: coordinator
 replicaCount: 2
 port: 8081
 serviceType: ClusterIP

 config:
  DRUID_XMX: 4086m
  DRUID_XMS: 4086m
  # druid_monitoring_monitors: '["org.apache.druid.server.metrics.TaskCountStatsMonitor"]'

 ingress:
  enabled: false
  annotations: {}
   # <http://kubernetes.io/ingress.class|kubernetes.io/ingress.class>: nginx
   # <http://kubernetes.io/tls-acme|kubernetes.io/tls-acme>: "true"
  path: /
  hosts:
   - chart-example.local
  tls: []
  # - secretName: chart-example-tls
  #  hosts:
  #   - chart-example.local

 resources:
  limits:
   cpu: 3000m
   memory: 5000Mi
  requests:
   cpu: 2500m
   memory: 4000Mi

 nodeSelector:
  allowed: druid-uat

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed

 affinity: {}

 podAnnotations: {}

overlord:
 ## If true, the separate overlord will be installed
 ##
 enabled: false
 name: overlord
 replicaCount: 1
 port: 8081
 serviceType: ClusterIP

 javaOpts: "-Xms1G -Xmx1G"

 ingress:
  enabled: false
  annotations: {}
   # <http://kubernetes.io/ingress.class|kubernetes.io/ingress.class>: nginx
   # <http://kubernetes.io/tls-acme|kubernetes.io/tls-acme>: "true"
  path: /
  hosts:
   - chart-example.local
  tls: []
  # - secretName: chart-example-tls
  #  hosts:
  #   - chart-example.local

 resources:
  limits:
   cpu: 500m
   memory: 1500Mi
  requests:
   cpu: 100m
   memory: 1000Mi

 nodeSelector:
  allowed: druid-uat

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed

 affinity: {}

 podAnnotations: {}

historical:
 ## If false, historical will not be installed
 ##
 enabled: true
 name: historical
 replicaCount: 2
 port: 8083
 serviceType: ClusterIP

 config:
  DRUID_XMX: 4086m
  DRUID_XMS: 4086m
  DRUID_MAXDIRECTMEMORYSIZE: 2000m
  druid_processing_buffer_sizeBytes: '100000000'
  druid_processing_numMergeBuffers: 5
  druid_processing_numThreads: 10
  # druid_monitoring_monitors: '["org.apache.druid.client.cache.CacheMonitor", "org.apache.druid.server.metrics.HistoricalMetricsMonitor", "org.apache.druid.server.metrics.QueryCountStatsMonitor"]'
  #druid_segmentCache_locations: '[{"path":"/var/druid/segment-cache","maxSize":1000000000}]'

 ingress:
  enabled: false
  annotations: {}
   # <http://kubernetes.io/ingress.class|kubernetes.io/ingress.class>: nginx
   # <http://kubernetes.io/tls-acme|kubernetes.io/tls-acme>: "true"
  path: /
  hosts:
   - chart-example.local
  tls: []
  # - secretName: chart-example-tls
  #  hosts:
  #   - chart-example.local

 persistence:
  enabled: true
  accessMode: ReadWriteOnce
  size: "8Gi"
  # storageClass: "ssd"

 antiAffinity: "soft"

 nodeAffinity: {}

 nodeSelector:
  allowed: druid-uat

 securityContext:
  fsGroup: 1000

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed

 resources:
  limits:
   cpu: 5000m
   memory: 15000Mi
  requests:
   cpu: 4000m
   memory: 10000Mi

 ## (dict) If specified, apply these annotations to each master Pod
 podAnnotations: {}

 podDisruptionBudget:
  enabled: false
  # minAvailable: 2
  maxUnavailable: 1

 updateStrategy:
  type: RollingUpdate

middleManager:
 ## If false, middleManager will not be installed
 ##
 enabled: true
 name: middle-manager
 replicaCount: 5
 port: 8091
 serviceType: ClusterIP

 config:
  DRUID_XMX: 5000m
  DRUID_XMS: 5000m
  druid_indexer_runner_javaOptsArray: '["-server", "-Xms4096m", "-Xmx4096m", "-XX:MaxDirectMemorySize=2000m", "-Duser.timezone=UTC", "-Dfile.encoding=UTF-8", "-XX:+ExitOnOutOfMemoryError", "-Djava.util.logging.manager=org.apache.logging.log4j.jul.LogManager"]'
  druid_indexer_fork_property_druid_processing_buffer_sizeBytes: '250000000'
  druid_worker_capacity: 14

 autoscaling:
  enabled: true
  minReplicas: 5
  maxReplicas: 5
  metrics:
  - type: Resource
   resource:
    name: cpu
    target:
     type: Utilization
     averageUtilization: 70
  - type: Resource
   resource:
    name: memory
    target:
     type: Utilization
     averageUtilization: 70

 ingress:
  enabled: false
  annotations: {}
   # <http://kubernetes.io/ingress.class|kubernetes.io/ingress.class>: nginx
   # <http://kubernetes.io/tls-acme|kubernetes.io/tls-acme>: "true"
  path: /
  hosts:
   - chart-example.local
  tls: []
  # - secretName: chart-example-tls
  #  hosts:
  #   - chart-example.local

 persistence:
  enabled: true
  accessMode: ReadWriteOnce
  size: "4Gi"
  # storageClass: "ssd"

 antiAffinity: "soft"

 nodeAffinity: {}

 nodeSelector:
  allowed: druid-uat

 securityContext:
  fsGroup: 1000

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed

 resources:
  limits:
   cpu: 1200m
   memory: 10000Mi
  requests:
   cpu: 1000m
   memory: 9000Mi


 ## (dict) If specified, apply these annotations to each master Pod
 podAnnotations: {}

 podDisruptionBudget:
  enabled: false
  # minAvailable: 2
  maxUnavailable: 1

 updateStrategy:
  type: RollingUpdate

router:
 ## If false, router will not be installed
 ##
 enabled: true
 name: router
 replicaCount: 1
 port: 8888
 serviceType: ClusterIP

 config:
  DRUID_XMX: 1024m
  DRUID_XMS: 1024m
  DRUID_MAXDIRECTMEMORYSIZE: 512m

 ingress:
  enabled: false
  annotations: {}
   # <http://kubernetes.io/ingress.class|kubernetes.io/ingress.class>: nginx
   # <http://kubernetes.io/tls-acme|kubernetes.io/tls-acme>: "true"
  path: /
  hosts:
   - chart-example.local
  tls: []
  # - secretName: chart-example-tls
  #  hosts:
  #   - chart-example.local

 resources:
  limits:
   cpu: 250m
   memory: 1200Mi
  requests:
   cpu: 150m
   memory: 1000Mi

 nodeSelector:
  allowed: druid-uat

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed

 affinity: {}

 podAnnotations: {}

# ------------------------------------------------------------------------------
# Zookeeper:
# ------------------------------------------------------------------------------

# zkHosts: druid-zookeeper-headless:2181

zookeeper:
 enabled: true
 ## Environmental variables to set in Zookeeper
 ##
 env:
  ## The JVM heap size to allocate to Zookeeper
  ZK_HEAP_SIZE: "2048M"
 ## Configure Zookeeper headless
 headless:
  publishNotReadyAddresses: true

 resources:
  limits:
   cpu: 1000m
   memory: 3000Mi
  requests:
   cpu: 700m
   memory: 2000Mi

 nodeSelector:
  allowed: druid-uat

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed

# ------------------------------------------------------------------------------
# MySQL:
# ------------------------------------------------------------------------------
mysql:
 enabled: false
 mysqlRootPassword: druidroot
 mysqlUser: druid
 mysqlPassword: druid
 mysqlDatabase: druid
 configurationFiles:
  mysql_collate.cnf: |-
   [mysqld]
   character-set-server=utf8
   collation-server=utf8_unicode_ci

# ------------------------------------------------------------------------------
# postgres:
# ------------------------------------------------------------------------------
postgresql:
 enabled: true
 postgresqlUsername: druid
 postgresqlPassword: druid
 postgresqlDatabase: druid
 resources:
  limits:
   cpu: 200m
   memory: 600Mi
  requests:
   cpu: 100m
   memory: 500Mi
 service:
  port: 5432

 nodeSelector:
  allowed: druid-uat

 tolerations:
 - effect: NoSchedule
  key: only-druid-uat
  operator: Equal
  value: allowed
  # Secrets

this is my conf. if you can suggest any thing that i can change

my broker logs

2023-05-01T14:52:14,132 ERROR [sql[0a0c7eec-f8c0-4d9e-a2e3-aed5b6bf7e4b]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:52:40,580 ERROR [sql[4f0812e6-cd84-4816-9d0b-fed137999374]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:52:53,213 ERROR [sql[8a0eb8c0-f74a-4887-8489-7e6bec396a37]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:53:14,131 ERROR [sql[86bddc14-6046-4239-933e-8a5b0b788b24]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:53:40,578 ERROR [sql[c8a94423-4d7c-4f14-b4ed-4b3f191c32ec]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:54:14,130 ERROR [sql[e225685c-6a43-40d1-b34c-279d5d2b89cd]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:54:29,350 ERROR [sql[66e7dada-5959-4f41-ac97-187492b71200]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:54:40,584 ERROR [sql[e7dd665d-f8e3-4fca-adf8-51b9b9cde6ba]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:54:59,128 ERROR [sql[3e6a0ad0-7553-4ffc-8a12-ddc26bff3de2]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:55:14,127 ERROR [sql[620032d4-1c87-448d-87fb-0457c5dbe83b]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:55:25,584 ERROR [sql[c9da858a-11b5-4978-880d-4ed708fef930]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:55:46,210 ERROR [sql[c796690f-f9d4-4bdf-b864-9c3561ba5ed8]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:56:24,027 ERROR [sql[82521fa7-5843-49eb-8e7d-e4dfa125f59e]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:56:44,144 ERROR [sql[b26c34ac-0cb8-47fa-a9ca-508d37ca2931]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:57:25,578 ERROR [sql[4a955357-5b96-44e7-b852-7bb0f4d442bd]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]
2023-05-01T14:57:59,123 ERROR [sql[4f5126a3-05ce-4e08-a620-1e208406046d]] org.apache.druid.curator.discovery.ServerDiscoverySelector - No server instance found for [druid/overlord]

Hi <@U04VDGHUKTQ> your overlord config is set to false. So, the overlord service is not getting started

*coordinator*:
  ## If false, coordinator will not be installed
  ##
 *enabled*: true
 *name*: coordinator
 *replicaCount*: 2
 *port*: 8081
 *serviceType*: ClusterIP

 *config*:
  *DRUID_XMX*: 2048m
  *DRUID_XMS*: 2048m
   # druid_monitoring_monitors: '["org.apache.druid.server.metrics.TaskCountStatsMonitor"]'

 *ingress*:
  *enabled*: false
  *annotations*: {}
    # <http://kubernetes.io/ingress.class|kubernetes.io/ingress.class>: nginx
    # <http://kubernetes.io/tls-acme|kubernetes.io/tls-acme>: "true"
  *path*: /
  *hosts*:
   - chart-example.local
  *tls*: []
   # - secretName: chart-example-tls
   #  hosts:
   #   - chart-example.local

 *resources*:
  *limits*:
   *cpu*: 3000m
   *memory*: 5000Mi
  *requests*:
   *cpu*: 2500m
   *memory*: 4000Mi

Hi <@U04VDGHUKTQ> you need both coordinator and overlord. These 2 services do 2 different things.

if i check in Service peon is not getting created . What could be causing that !!
<@U053EQBHN5N>

Have you enabled the overlord service? The overlord service will create supervisors for ingestion tasks and the supervisor will work with the middlemanagers to create the pions

yes i just did but still facing the same issue .
Tough i just checked my Coordinator logs and found

`Caused by: java.lang.NumberFormatException: For input string: "2177280000"`
	at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:1.8.0_252]
	at java.lang.Integer.parseInt(Integer.java:583) ~[?:1.8.0_252]
	at java.lang.Integer.parseInt(Integer.java:615) ~[?:1.8.0_252]

The sketch object is null or the values in the sketch?

The sketch object itself, here is a water down version of my SQL like query:

```select * from data_source
where sketch &lt;&gt; NULL;```

I work on the sketches library side, so this is outside my knowledge area. Is it possible to check that the sketch is not empty? IIRC, an empty quantiles sketch is 8 bytes, so the same size as a null pointer.

Not sure if you can ask if the sketch is empty from the SQL (the sketch object supports it, but that’s not the same thing)

Would it matter if I do the query in native JSON instead of SQL? Would prefer having Druid filter out empty values instead of the application code

Outside my knowledge area. I think we need a Druid person to chime in, not just someone who works on the separate datasketches library

Anyone from Druid here can help out with my question?

Hi <@U055M1Q1XJ9>, Could it be related to this?
<https://druid.apache.org/docs/latest/configuration/index.html#sql-compatible-null-handling>

If you set `druid.generic.useDefaultValueForNulL` to `false`, it should default all data types to NULL when ingested according to this: <https://druid.apache.org/docs/latest/querying/sql-data-types.html#standard-types>
I'm guessing that applies to COMPLEX types too, but I could be wrong.

You can control how many threads the historicals use to download segments with
```druid.segmentCache.numLoadingThreads```

:face_palm: makes sense :slightly_smiling_face:

Also, *`maxSegmentsInNodeLoadingQueue`* controls how many pending operations are given to each historical. Default of 100 should be plenty.

```numLoadingThreads```
 to 5, and *maxSegmentsInNodeLoadingQueue was already 200*

Do you see pending loads on the historicals in the Druid Console?

hmmm what else could throttle the download?

just to confirm, did you restart the historicals after the change?

Is the location hosting the segments able to do ~25Gb/s? Outside of Druid are you able to get that kind of bandwidth a CLI tool?

Yes sir… even getting data via s3cli runs ~10Gb+

That includes writing and flushing to disk? Do you have a NVMe raid setup for Druid? I am assuming disk IO is the bottle neck here but maybe I am missing something.

IIRC, there's some additional config needed to actually parallelize loading beyond `numLoadingThreads` — I believe there's also gates for feeding the threads

for http load queues (`druid.coordinator.loadqueuepeon.type=http`) IIRC you also want to set `druid.coordinator.loadqueuepeon.http.batchSize=N` (where `N &lt;= numLoadingThreads`)

`druid.coordinator.loadqueuepeon.curator.numCallbackThreads` seems relevant from reading the docs, although i don't have personal experience with it

Mostly use HTTP these days :slightly_smiling_face:

This was it my friend :slightly_smiling_face:
```druid.coordinator.loadqueuepeon.type=http
druid.coordinator.loadqueuepeon.http.batchSize=50```


```org.jboss.netty.channel.ChannelException: Channel disconnected
	at org.apache.druid.java.util.http.client.NettyHttpClient$1.channelDisconnected(NettyHttpClient.java:334) ~[druid-processing-2023.03.0-iap.jar:2023.03.0-iap]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:102) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.channelDisconnected(SimpleChannelUpstreamHandler.java:208) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:102) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.channelDisconnected(SimpleChannelUpstreamHandler.java:208) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:102) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline$DefaultChannelHandlerContext.sendUpstream(DefaultChannelPipeline.java:791) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.handler.codec.replay.ReplayingDecoder.cleanup(ReplayingDecoder.java:570) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.handler.codec.frame.FrameDecoder.channelDisconnected(FrameDecoder.java:365) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.SimpleChannelUpstreamHandler.handleUpstream(SimpleChannelUpstreamHandler.java:102) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.handler.codec.http.HttpClientCodec.handleUpstream(HttpClientCodec.java:92) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:564) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.DefaultChannelPipeline.sendUpstream(DefaultChannelPipeline.java:559) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.Channels.fireChannelDisconnected(Channels.java:396) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.socket.nio.AbstractNioWorker.close(AbstractNioWorker.java:360) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.socket.nio.NioWorker.read(NioWorker.java:93) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.socket.nio.AbstractNioWorker.process(AbstractNioWorker.java:108) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.socket.nio.AbstractNioSelector.run(AbstractNioSelector.java:337) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.socket.nio.AbstractNioWorker.run(AbstractNioWorker.java:89) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.channel.socket.nio.NioWorker.run(NioWorker.java:178) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.util.ThreadRenamingRunnable.run(ThreadRenamingRunnable.java:108) ~[netty-3.10.6.Final-iap3.jar:?]
	at org.jboss.netty.util.internal.DeadLockProofWorker$1.run(DeadLockProofWorker.java:42) ~[netty-3.10.6.Final-iap3.jar:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_352]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_352]
	at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_352]```

it's an issue syncing authentication details from the Coordinator to the server that you saw the log on. It may resolve itself in which case you can ignore it. If it doesn't resolve itself then I would check if your Coordinator is healthy

thanks <@U0344FW86DD> i see it is not resolving by it self, let me check the Coordinator service.

You should be able to use JDBC (<https://druid.apache.org/docs/latest/querying/sql-jdbc.html>)

try `timestamp_format` with `"E"` (day of week text)

see <https://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat> for the formatter options

some generic performance advice:

1) consider schema design best practices— <https://druid.apache.org/docs/latest/ingestion/schema-design.html#general-tips-and-best-practices>

2) try adding more data servers and see if that helps. if it does then it means your bottleneck is dat servers. if it doesn't, then your bottleneck is likely at the Broker

3) try to attack the bottleneck by looking at HW metrics: for example, if you are I/O bound then try adding memory or try looking into the performance of your I/O layer

4) also consider grabbing a flame graph to understand the bottleneck further (<https://support.imply.io/hc/en-us/articles/360033747953-Profiling-Druid-queries-using-flame-graphs>)

Thanks <@U0344FW86DD> will go through these docs.

hi <@U03DJ6BJY5A>! also, be mindful of how you are usually accessing the data and how it's physically stored in the segments; I was using a specific dimension for filtering 100% of the time, so using `single_dim` as <https://druid.apache.org/docs/latest/ingestion/native-batch.html#partitionsspec|partitioning> helped a ton

The user you have used to create lookup tables , does it have  required permissions  . Any specific error in logs

We r able to resolve this by cleaning metadata

Was looking how to implement column level access  security

__time is stored as timestamp. there is no time zone information stored with __time column

when you query the __time column, you could use the timestamp_format function that lets you specify the timezone

Got it! so actually I have a json payload.

```{"eventTime": "2023-05-03T06:11:34+05:30"}```
How can I ingest data in druid with timeZone info? like __time in this case should be 2023-05-03T06:11:34 instead of 2023-05-03T00:41:34 and where should I store the timezone?

so you are getting these values with different timezones and you want to be able to show the values in same timezone that you received in?

all values will not be in the same timezone.

you could store two columns - one is __time (timestamp type) and the second is the string representation of this time column.

should the producer be sending both the the string and epoch time in that case. as parsing the string (eventTime) is leading to unexpected time.

`__time` is stored in timezone-independent manner, it's internally milliseconds since UTC epoch (1970-01-01T00:00:00Z)

your `eventTime` are all stored correctly in the sense that they represent the correct instant, although the timezone info is not persisted

if you just need to store the correct instant and retrieve in arbitrary timezone, then you should be good as-is

if you need to retrieve in the original `eventTime` timezone that may vary from row to row, rather than a timezone determined by the query, then yeah you gotta store timezone as a separate column

does storing timezone alone as a separate column work? I thought about it but I am not sure if our timestamp functions support expression as an argument to timezone. or if they support just literals

although it could be extended to support expressions for the tz

however you could store the tz offset as milliseconds in a second column

yeah i think we'd need to extend `TIME_FORMAT`

this isn't a very common ask— most people use a timezone determined by the user at query time, rather than using the original event timezone

however it would still be nice to support it.

For now, I think storing eventTime as a whole including tz in a separate column is a good enough workaround

to your question, <@U055T2J3LCW>, producer doesn't need to send both. You can map same column in input to two columns in your ingestion spec.

got it! let me try storing this in two columns and see how it goes. Thank you for the quick support here. Also would be happy to learn and contribute in case we plan to bring this functionality natively to druid.

that will be nice. you should first look at `TimeFormatOperatorConversion` class in druid.

great! will start from the `TimeFormatOperatorConversion` class.

It is best if data producer always normalized to using UTC time. And if you can’t control the producer, why not create a simple consumer that pre-process the time column into UTC and then dump it to another kafka topic for Druid to consume?

<@U03149ALHN3>

I have control over the producer. So basically it is ideal if the producer sends data like the one below.

```{
    "eventTime": "epochTime",
    "timezone": "+05:30"
}```
Time Zone shall be required to convert to the right time at the time of visualization.

ah, yes. if your front end can handle that, that’s best.

do you mind sharing your `druid.extensions.loadList`

This is likely due to the fact that you may not have "druid-azure-extensions" added to your extensions loadlist <https://druid.apache.org/docs/latest/development/extensions-core/azure.html>

Hello Arun, can you guide me where can i see this load list?

image.png

This is what I can see in the extension folder, and one folder for azure.

the extension load list should be in your common.runtime.properties under your /conf directory in your druid install path

having it in the extensions folder does not mean its loaded FYI, by default druid does not load all extensions in the extensions folder automatically.  You must specify what extensions you want to load via the druid.extensions.loadList parameter

Now after loading the extension also, I am getting quite different error.

It looks like it's still not loaded on all processes; this time on the Coordinator. Try double checking that the processes are all using the same loadList setting and have all been restarted to apply it

I have addded extenstion in common runtime propertise file [which is located @ auto/__common/_], how can I check if same property file is being used.

can you also share the command ur using for the startup

im assuming ur using the supervisor startup scripts

./bin/start-druid I am using this command to start druid service? Can we have a connect on call, That way I can better explain my issue and it will be easier to resolve to I guess

we have enabled druid as a service and use systemctl start druid command to start it.

you possibly need to add this to `druid.indexer.runner.javaOpts` or `druid.indexer.runner.javaOptsArray` as well

Hi <@U0344FW86DD>, I did added the above properties but it still fails with the same error.

Any other thing I am missing.

the main things that use tmp space are indexing (a few GB of temp space per concurrent task is good) and query spill-to-disk (which you may not use at all as the default config is in-memory. but you can enable this using `maxOnDiskStorage`)

if you aren't using query spill-to-disk then i would set aside a few GB per `druid.worker.capacity` for data servers.

It also depends on your JVM config. Do you dump large heap when daemon crashed? If so, you need ample space for that.

These are the tranquilty settings:
```druid.maxdruidclients=1
druid.batch.size=10
druid.indexservice=druid/overlord
druid.discoverypath=/druid/discovery
druid.firehosePattern=druid:firehose:%s
druid.datasource=ourteam
druid.zookeeper.host=<http://server1.com|server1.com>,<http://server2.com|server2.com>,<http://server3.com|server3.com>
druid.segment.partitions=8
druid.segment.replications=2
druid.windowperiod=PT17M
druid.warmingPeriod=PT5M
druid.persistperiod=PT5M
druid.maxrowsinmemory=500000
druid.blockOnFull=true
druid.maxBatchSize=2000
druid.maxPendingBatches=5
druid.lingerMillis=0
druid.dimensions=&lt;about 30 of them here&gt;
druid.connectionTimeoutMs=30000
druid.sessionTimeoutMs=30000
druid.retryBaseSleep=1000
druid.retryMaxRetries=20
druid.retryMaxSleep=10000
druid.maxPendingPersists=0
druid.buildV9Directly=true```

ooo somehow coordinator still keeps track of it under “unused”. How can I clear this?

coordinator gets unused segments from the metadata store.

Thanks for the pointer! I will seek around the PostgreSQL db and find where is that left over record.

I have to note, this only works if you put hostname inside druid.host. Don’t put IP addresses.

<@U03149ALHN3> This is really cool. Thanks for your work. Can I ask why we need to have two clusters to achieve 0 downtime environment? Per the Druid document, it is suggesting to use rolling updates.

<@U04QJCT4N2G> This stems from the need to roll upgrade AWS EKS versions. Druid has never been able to tolerate EKS rolling upgrade (Druid 25 dealt with it much better thanks to the new Apache Curator library), but it wasn’t perfect. For some of our apps, they are P0 mission critical. We must absolutely achieve 0 downtime.

Screenshot 2023-05-04 at 11.47.38 AM.png

One more thing, you need to make your historical tiers looked like the following so that you can replicate between 2 clusters. This cannot be achieved using the UI. You have to submit directly using the API.

Hi All, i am getting below error in druid when executing query. I am using druid version 25.0.0

`Error: Unknown exception`
`Detected duplicate prefix in join clauses: [__j0.]`
`org.apache.druid.java.util.common.IAE`

can you share the output you get if you try to explain the query?

hi <@U031A9D19NU> here is the aggregated result i get if i remove (OR condition)

EXPR$0	        sales_year	    sales_month	sales_quarter
1130297838	   2019	                             1	          1
1243378194	   2019	                           10	  4
1174913950	   2019	                            11	  4
1354069796	   2019	                             12	  4
1108412262	   2019	                               2	   1
1252788091	   2019	                                3	   1
1139362573	   2019	                                4	    2

it will be helpful if you can share some sample data schema on which you can reproduce this issue

Hi <@U031A9D19NU> here is the table and sample data

__time	product_id	monthenddatekey	sales_year	sales_month	sales_quarter	dist_total_revenue	dist_total_units	facility_type	unspsc_1	unspsc_2	unspsc_3	unspsc_4	taxonomy_id	manf_desc	prod_desc	sku	direct_total_revenue	direct_total_units
2017-01-31T00:00:00.000Z	1000003	20170131	2017	1	1	73	1	PHYSICIAN OFFICE	Medical Equipment and Accessories and Supplies	Surgical products	Surgical clamps and forceps and surgical ligators and related instruments	Surgical clamps or clips or forceps	42291802	DERRON SURGICAL INSTRUMENTS	FORCEP TISSUE 5" STRAIGHT JAW STAINLESS STEEL EA	42138	73	1
2017-01-31T00:00:00.000Z	1000006	20170131	2017	1	1	340	1	PHYSICIAN OFFICE	Medical Equipment and Accessories and Supplies	Patient care and treatment products and supplies	Vascular and compression therapy equipment and supplies	Vascular or compression apparel or support	42142802	DEVON MEDICAL	PUMP DVT SYSTEM CIRONA 6100 EA	6100	340	1
2017-01-31T00:00:00.000Z	1000056	20170131	2017	1	1	1128	34	LONG-TERM CARE	Medical Equipment and Accessories and Supplies	Medical cleaning and sterilization products	Cold sterilization and disinfectant solutions	Medical surface disinfectants	42281604	DIVERSEY	DISINFECTANT,EXPOSE II 256, 2X2L, JFILL	5699	1128	34
2017-01-31T00:00:00.000Z	1000062	20170131	2017	1	1	267	12	LONG-TERM CARE	Cleaning Equipment and Supplies	Cleaning and janitorial supplies	Cleaning and disinfecting solutions	Floor cleaners	47131801	DIVERSEY	DBD-MBO-SHAMPOO,CARPET, HIGH FOAM, 4 X 1	5002689	267	12
2017-01-31T00:00:00.000Z	1000064	20170131	2017	1	1	57	12	LONG-TERM CARE	Cleaning Equipment and Supplies	Cleaning and janitorial supplies	Cleaning and disinfecting solutions	Furniture polish or waxes	47131806	DIVERSEY	POLISH FURNITURE PLEDGE 10OZ **DISC BY MFG-RPL NOT SET UP**	723725	57	12
2017-01-31T00:00:00.000Z	1000066	20170131	2017	1	1	402	192	HOSPITAL/HEALTH SYSTEM	Cleaning Equipment and Supplies	Cleaning and janitorial supplies	Cleaning and disinfecting solutions	Toilet cleaners	47131829	DIVERSEY	CLEANER,BOWL,CREW,MILD ACID,1X32OZ	94476081	402	192
2017-01-31T00:00:00.000Z	1000067	20170131	2017	1	1	327	5	HOSPITAL/HEALTH SYSTEM	Cleaning Equipment and Supplies	Cleaning and janitorial supplies	Cleaning and disinfecting solutions	Stain cleaners or removers	47131827	DIVERSEY	STRIPPER,FLOOR,ULTRA,5GAL,BOX	95386176	327	5
2017-01-31T00:00:00.000Z	1000086	20170131	2017	1	1	61	1	TREATMENT CENTER	Medical Equipment and Accessories and Supplies	Orthopedic and prosthetic and sports medicine products	Orthopedic softgoods for lower extremity	Knee therapeutic brace or support	42241703	DJO GLOBAL	BRACE KNEE PATELLA RIGHT XXXLARGE	1102607	61	1
2017-01-31T00:00:00.000Z	1000086	20170131	2017	1	1	154	2	PHYSICIAN OFFICE	Medical Equipment and Accessories and Supplies	Orthopedic and prosthetic and sports medicine products	Orthopedic softgoods for lower extremity	Knee therapeutic brace or support	42241703	DJO GLOBAL	BRACE KNEE PATELLA RIGHT XXXLARGE	1102607	154	2
2017-01-31T00:00:00.000Z	1000086	20170131	2017	1	1	536	9	HOSPITAL/HEALTH SYSTEM	Medical Equipment and Accessories and Supplies	Orthopedic and prosthetic and sports medicine products	Orthopedic softgoods for lower extremity	Knee therapeutic brace or support	42241703	DJO GLOBAL	BRACE KNEE PATELLA RIGHT XXXLARGE	1102607	536	9

__time	email_address	manufacturer_desc	data_frequency	geography_entitlement	unspsc_code	cot
2023-04-18T06:25:42.242Z	<mailto:4s@dr.com|4s@dr.com>	4S H	Quarterly	Zip	42132205	
2023-04-18T06:25:42.242Z	<mailto:a.a@c.com|a.a@c.com>	CLAR	Monthly	Zip	["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]	
2023-04-18T06:25:42.242Z	<mailto:a.p@cl.com|a.p@cl.com>	3B			["14111539","31162506","31201512","41112601","41113035","41113117","41116116","41116132","42131613","42131701","42131713","42131721","42141503","42142105","42142106","42142108","42142111","42142535","42143101","42152453","42172105","42181702","42181707","42181708","42181715","42181716","42181904","42182101","42182103","42182105","42182107","42182109","42182110","42182114","42182207","42203502","42203503","42221507","42221604","42221703","42221708","42221803","42222307","42241502","42241504","42241505","42241506","42241507","42241510","42281607","42281802","42281803","42281804","42281805","42281806","42281807","42281809","42281810","42281904","42281907","42281909","42294211","42294927","42295104","42295143","42295408","42295414","42295415","42295421","42295424","42295480","42311502","42311506","42311510","42311513","42311514","42311515","42311518","42311527","42311531","42311540","42311543","42311545","42311546","42311547","42311552","42311554","42312005","42312010","42312018","42312103","42312105","42312313","42312401","42312601","42312602","42312603","44121605","44121613","46181802","46181804","46182001","46182002","46182005","46182007","47131602","47131704","49241704","52151644","53131607","53131608","53131613","53131616","53131626","60104202"]	
2023-04-18T06:25:42.242Z	<mailto:a2_test@do.com|a2_test@do.com>	MED	Quarterly	National	["11223344","22334455","41151519"]	["ASC","HOME CARE","HOSPITAL/HEALTH SYSTEM","LAB/DIAGNOSTIC","LONG-TERM CARE","OTHER","PHYSICIAN OFFICE","RETAIL/CONSUMER","TREATMENT CENTER"]
2023-04-18T06:25:42.242Z	<mailto:aa@cl.com|aa@cl.com>	CLA	Monthly	Zip	["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]	
2023-04-18T06:25:42.242Z	<mailto:aa@cla.com|aa@cla.com>	CLA	Monthly	Zip	["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]	
2023-04-18T06:25:42.242Z	<mailto:a@cl.com|a@cl.com>	3MI			["14111539","31162506","31201512","41112601","41113035","41113117","41116116","41116132","42131613","42131701","42131713","42131721","42141503","42142105","42142106","42142108","42142111","42142535","42143101","42152453","42172105","42181702","42181707","42181708","42181715","42181716","42181904","42182101","42182103","42182105","42182107","42182109","42182110","42182114","42182207","42203502","42203503","42221507","42221604","42221703","42221708","42221803","42222307","42241502","42241504","42241505","42241506","42241507","42241510","42281607","42281802","42281803","42281804","42281805","42281806","42281807","42281809","42281810","42281904","42281907","42281909","42294211","42294927","42295104","42295143","42295408","42295414","42295415","42295421","42295424","42295480","42311502","42311506","42311510","42311513","42311514","42311515","42311518","42311527","42311531","42311540","42311543","42311545","42311546","42311547","42311552","42311554","42312005","42312010","42312018","42312103","42312105","42312313","42312401","42312601","42312602","42312603","44121605","44121613","46181802","46181804","46182001","46182002","46182005","46182007","47131602","47131704","49241704","52151644","53131607","53131608","53131613","53131616","53131626","60104202"]	
2023-04-18T06:25:42.242Z	aa@clcom	CLAR	Monthly	Zip	["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]	
2023-04-18T06:25:42.242Z	<mailto:aa@cl.com|aa@cl.com>	CLARIVA	Monthly	Zip	["42311502","42311506","42311510","42311513","42311515","42311523","42311527","42311540","42311545","42311546","42311547","42311552","42312005","42312103","42312105","42312313","42312401"]	
2023-04-18T06:25:42.242Z	<mailto:a@cl.com|a@cl.com>	MED	Quarterly	National	["11223344","22334455","41151519"]	["ASC","HOME CARE","HOSPITAL/HEALTH SYSTEM","LAB/DIAGNOSTIC","LONG-TERM CARE","OTHER","PHYSICIAN OFFICE","RETAIL/CONSUMER","TREATMENT CENTER"]

do you mind creating a github issue instead? <https://github.com/apache/druid/issues/new?assignees=&amp;labels=Uncategorized+problem+report&amp;template=problem_report.md&amp;title=>

Doing joins on one side of an OR clause has always been problematic in my experience as it's not a very "relational" thing to do.

Can you change that "or in()" clause to an outer join that you always do against the subquery and then change that where condition to

```where fe.manf_desc = 'BDD' OR fe.manf_desc = outer_table.top5manf_desc```
to see if it gets around the error?

Hi <@U04C4593VDY> The below query works but performance is not that good . Not sure this is what you meant

`with me_revenue as (select SUM(fe.dist_total_revenue) as me_rev,`
    `fe.sales_year,` 
    `fe.sales_month,` 
    `fe.sales_quarter from denorm_fact_extrapolated_unspsc fe` 
    `-- INNER join` 
   `where __time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')` 
                            `AND TIME_PARSE ('2023-01-31T00:00:00.000Z')`
                            `AND fe.manf_desc = 'BD'`
                            `AND fe.taxonomy_id IN(`
                              `SELECT` 
                                `unspsc_code` 
                              `FROM` 
                                `denorm_manf_entitlements` 
                              `WHERE` 
                                `email_address= '<mailto:a@dr.com|a@dr.com>'`
                            `)`
              `group by fe.sales_year,` 
                      `fe.sales_month,` 
                      `fe.sales_quarter`
                      `),`
                      
`manf_total_revenue as (select SUM(fe.dist_total_revenue) as manf_tot_rev,`
    `fe.sales_year,` 
    `fe.sales_month,` 
    `fe.sales_quarter from denorm_fact_extrapolated_unspsc fe` 
    `-- INNER join` 
   `where  fe.manf_desc in (SELECT` 
                       
                      `manf_desc as top5manf_desc` 
                    `FROM` 
                      `denorm_fact_extrapolated_unspsc fes` 
                    `where` 
                      `__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')` 
                      `AND TIME_PARSE ('2023-01-31T00:00:00.000Z')`
                      `AND fes.manf_desc != 'BD'`
                      `and fes.taxonomy_id in (`
                        `SELECT` 
                          `unspsc_code` 
                        `FROM` 
                          `denorm_manf_entitlements` 
                        `where` 
                          `email_address= '<mailto:a@dr.com|a@dr.com>'`
                      `)`
                    `group by` 
                      `manf_desc`
                    `order by` 
                      `SUM(fes.dist_total_revenue) desc` 
                    `limit` 
                      `5`
                      `)`
                      
                      `-- top5manf on manf_desc = top5manf_desc`
                      `-- WHERE` 
                      `AND`
                            `__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')` 
                            `AND TIME_PARSE ('2023-01-31T00:00:00.000Z')`
                            `-- or fe.manf_desc = 'BD'`
                            `AND fe.taxonomy_id IN(`
                              `SELECT` 
                                `unspsc_code` 
                              `FROM` 
                                `denorm_manf_entitlements` 
                              `WHERE` 
                                `email_address= '<mailto:a@dr.com|a@dr.com>'`
                            `)`
              `group by fe.sales_year,` 
                      `fe.sales_month,` 
                      `fe.sales_quarter`
                      
                      
`),`

`temp_table as (SELECT` 
                       
                      `manf_desc as top5manf_desc` 
                    `FROM` 
                      `denorm_fact_extrapolated_unspsc fes` 
                    `where` 
                      `__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')` 
                        `AND TIME_PARSE ('2023-01-31T00:00:00.000Z')`
                             `AND fes.manf_desc != 'BD'`
                      `and fes.taxonomy_id in (`
                        `SELECT` 
                          `unspsc_code` 
                        `FROM` 
                          `denorm_manf_entitlements` 
                        `where` 
                          `email_address= '<mailto:a@dr.com|a@dr.com>'`
                      `)`
                    `group by` 
                      `manf_desc`
                    `order by` 
                      `SUM(fes.dist_total_revenue) desc` 
                    `limit` 
                      `5`
`),`

`manf_revenue AS (`
  `SELECT` 
    `fe.manf_desc AS manf_desc,` 
    `SUM(fe.dist_total_revenue) AS manf_dist_total_revenue,` 
    `sales_year,` 
    `sales_month,` 
    `sales_quarter` 
  `FROM` 
    `denorm_fact_extrapolated_unspsc fe` 
  `WHERE` 
    `__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')` 
    `AND TIME_PARSE ('2023-01-31T00:00:00.000Z')`
    `AND fe.taxonomy_id IN (`
      `SELECT` 
        `unspsc_code` 
      `FROM` 
        `denorm_manf_entitlements` 
      `WHERE` 
        `email_address= '<mailto:a@dr.com|a@dr.com>'`
    `)` 
    `AND (fe.manf_desc ='BD'` 
     `OR fe.manf_desc IN ( select top5manf_desc from temp_table))`
     
  `GROUP BY` 
    `fe.manf_desc,` 
    `sales_year,` 
    `sales_month,` 
    `sales_quarter`
    `),`
    
`total_revenue AS (`
  `SELECT` 
    `SUM(fes.dist_total_revenue) AS total_rev,` 
    `sales_year,` 
    `sales_month,` 
    `sales_quarter` 
  `FROM` 
    `denorm_fact_extrapolated_unspsc fes` 
  `WHERE` 
    `__time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z')` 
    `AND TIME_PARSE ('2023-01-31T00:00:00.000Z')` 
    `AND fes.taxonomy_id IN(`
      `SELECT` 
        `unspsc_code` 
      `FROM` 
        `denorm_manf_entitlements` 
      `WHERE` 
        `email_address= '<mailto:a@dr.com|a@dr.com>'`
    `)` 
  `GROUP BY` 
    `sales_year,` 
    `sales_month,` 
    `sales_quarter`
`)`

`SELECT` 
  `manf_desc,` 
  `mr.sales_year,` 
  `mr.sales_month,` 
  `mr.sales_quarter,`
  `total_rev as total_revenue,`
  `manf_dist_total_revenue,` 
  `(`
    `manf_dist_total_revenue / total_rev`
  `)* 100 AS percentage,` 
  `(total_rev - (me_rev + manf_tot_rev)) as others_revenue,` 
  `((total_rev - (me_rev + manf_tot_rev)) / total_rev)* 100 AS others_percentage` 
`FROM` 
  `manf_revenue mr` 
  `JOIN total_revenue tr ON mr.sales_year = tr.sales_year` 
  `AND mr.sales_month = tr.sales_month` 
  `AND mr.sales_quarter = tr.sales_quarter` 
  `JOIN me_revenue ot ON mr.sales_year = ot.sales_year` 
  `AND mr.sales_month = ot.sales_month` 
  `AND mr.sales_quarter = ot.sales_quarter`
  `JOIN manf_total_revenue mtr ON mr.sales_year = mtr.sales_year` 
  `AND mr.sales_month = mtr.sales_month` 
  `AND mr.sales_quarter = mtr.sales_quarter`
  

Hi <@U031A9D19NU> i still created an issue here at <https://github.com/apache/druid/issues/14206>

hi DK,  you still have the "OR ... IN()" phrasing in there ... try changing that CTE to something like this:

```manf_revenue AS (
 SELECT fe.manf_desc AS manf_desc, 
        SUM(fe.dist_total_revenue) AS manf_dist_total_revenue, 
        sales_year, 
        sales_month, 
        sales_quarter 
   FROM denorm_fact_extrapolated_unspsc fe
   LEFT JOIN temp_table t on fe.manf_desc = t.top5manf_desc
  WHERE __time BETWEEN TIME_PARSE ('2019-01-31T00:00:00.000Z') 
    AND TIME_PARSE ('2023-01-31T00:00:00.000Z')
    AND fe.taxonomy_id IN (
         SELECT unspsc_code 
           FROM denorm_manf_entitlements 
          WHERE email_address= '<mailto:a@dr.com|a@dr.com>'
        ) 
    AND (fe.manf_desc ='BD' OR fe.manf_desc = t.top5manf_desc)
  GROUP BY 
        fe.manf_desc, 
        sales_year, 
        sales_month, 
        sales_quarter
),```

Not able to open Apache kafka dashboard in front