Apache Druid

looks like zookeeper is not reachable or zookeeper is not healthy

Tried initiating the zookeeper ec2 instances again but still nothing worked

its a clearly a zookeeper issue. if zk is running ok, you may want to look at the firewall rules

how to check if zookeeper instances are okay?

I can't think of an exhaustive list but the internet should have some pointers. you can start with the logs of zk services.

can't find anything for these commands:
netstat -anp | grep 2181
ps -ef|grep -i zoo

We have a new release for the druid kubernetes operator with support for init containers.
With init containers user's can add jar dependencies into druid pods. ( No more hacking docker images and init scripts to pull jars :slightly_smiling_face: )

<https://github.com/datainfrahq/druid-operator/releases/tag/v1.1.1>
Slack Conversation

What do you mean by hot swap mode? And how do you intend to use the tiering?

We have done this a lot on Kubernetes, I may be able to share to you what we’ve done.

I mean to change to `druid.worker.category` of one MM during its execution

Ah, unfortunately you have to redeploy when changing that.
But it is better to create a StatefulSet per category to be more precise.

Why not use mm-less Druid?  If you are in k8s, no need for the middle manager process.

I would love to haha, just need to find the time to R&amp;D it in our lab.

yeah, this is currently a deficiency with the nested type (and all complex types)

im working on fixing this in the future, but don’t have an exact timeline, likely druid 27 release

the sad part is it actually does have an index internally on which values are null, just the higher level parts of the query engine are not currently able to take advantage of it yet

on the upside, that means once we do add support, null filtering should be pretty fast even for segments that have already been created

Thanks for explaining.
&gt; null filtering should be pretty fast even for segments that have already been created
That is good to know :+1:

In the meantime, is there a better workaround filter to use? Or what I mentioned seem ok? I’m hoping druid internally stores the json string and `TO_JSON_STRING` is just looking that up instead if serializing it during execution

unfortunately it is serializing it, we store the raw json as smile encoded and then compressed

both of those solutions should work though neither are probably particularly fast unfortunately

If there is a feature request / issue for this please do share. Will be easier for us to follow and know when this gets released.

Hi Cactus -- here's one way ...

```insert into new_table
WITH seq10 AS (
  SELECT * FROM TABLE(EXTERN(
    '{"type":"inline","data":"0\n1\n2\n3\n4\n5\n6\n7\n8\n9\n"}',
    '{"type":"csv","findColumnsFromHeader":false,"columns":["val"]}',
    '[{"name":"val","type":"long"}]'
  ))
)
SELECT TIMESTAMPADD(DAY, b.val*10 + c.val, CURRENT_TIMESTAMP) __time, '-' flag from seq10 b CROSS JOIN seq10 c
PARTITIONED BY ALL```
Let me know if you have any questions.

Thanks.  John

in the Imply release, there is a DATE_EXPAND function which (I believe) does that

could you do something like
```SELECT
  FLOOR(__time TO HOUR),
  AVG(dim)
FROM "table"
GROUP BY 1```
?

Thank you for the answer! I have tried the query, I need to perform some test to be sure that the aggregation is correct but after a first rapid look it seems to work. Is there any loss of efficency in writing the query in sql rather than using druid native language? Does Druid still perform all "its magic" also if the query is in sql?

performance between SQL and native JSON queries is equivalent other than the (generally slight) time it takes to parse/plan SQL

since the SQL planner plans into native JSON queries and then executes those

I understand. Thank you for the clarification!

Hello,

Any body help me on this...

Currently we have core apache Druid installed in single server (and all the druid processes are running in same single server).
We using apache druid 0.22.0 version. Launched using /bin/start-single-server-small config.  My server has 8 core CPU and 64 GB Ram.

Now my question I want to upgrade the hardware resources like cpu, ram. But I don't want to upgrade technically from single-server-small to single-server-medium.

Having single-server-small, whether I can still upgrade my resources like CPU and RAM and change in small configuration runtime properties to have more cpu and ram, will it work ?
or it will have limitation since it configured &amp; installed &amp; launched as single-server-small ?

[ OR ] Whether I can technically upgrade the druid server from single-server-small  to  single-server-medium (having require cpu / ram resources are added in the server) ?

I would recommend updating to Druid 25 and using the `bin/start-druid` script

it sets these properties automatically based on available memory and CPU on your server

more info— <https://druid.apache.org/docs/latest/operations/single-server.html>

Gian, actually so far my understanding is since I launched (installed) using  single-server-small   i.e.  ./bin/start-single-server-small  whether it had hard limit / restriction to the described resource sizing i.e. 8 cpu and 64 gb, eventhough I would add resource in server (more cpu / ram / etc...) and do changes in druid  single-server/small  config runtime properties ? !!

the sized configs `small`, `medium`, etc are just starting points for various common server sizes

So I can simply add resources and change the properties and still I can run / launch the druid using single-server-small ?

however, I would suggest that the newer `bin/start-druid` script is even simpler

Am currently facing some Query performance issue

oh...  once after stop the druid service (of small)  and while start again i can use bin/start-druid  ?

inspite the server config and druid config properties?

Hi <@U043F8HA5L6> you can find more information on Single Server deployment using bin/start-druid here: <https://druid.apache.org/docs/latest/operations/single-server.html>

This is only available from Druid version 25 onwards

It is quite useful to automatically configure your single server deployment

You can also do this in your current version but you will have to manually update the configurations yourself.

bin/start-druid makes it a lot easier and automatic

having druid 25,  a doubt, If stop the current druid service which launched using single-server-small and start with using bin/start-druid  still the log files, data-set path are considered same

There are 2 different ways that you can approach it. You can update your existing druid version to 25. Once you upgrade, you will have bin/start-druid available. The other way is to create a separate cluster in parallel running Druid 25, migrate everything in this new cluster, test everything and then move everything over to the new cluster.

If you are using the default bin/start script, then most of the information should be in var directory. You can just copy it across

Is there a way to use the bin/start-druid script also for docker deployment? I have followed the official guide for druid deployment using docker and I had to manually set lots of conf on the environment file

<@U0315MQKEHK> should be able to answer this better. I am not sure to be honest. An easy way to achieve this if I had to do it will be to use the bin/start-druid script to generate all the relevant config files and then update your docker config using those.

<@U043F8HA5L6> Scaling druid clusters vertically on the basis of cpu, mem and storage is supported in the <https://github.com/datainfrahq/druid-operator|druid operator>.

Ok I will check Adheip thank you and thank you all

Hi Marco -- if you are using the druid/v2/sql endpoint you can request different format types ... here I used CSV in my tests:

```curl --location --request POST '<http://localhost:8888/druid/v2/sql/>' \
     --header 'Content-Type: application/json' \
     --data-raw '{ "query" : "SELECT regionName, COUNT(*) FROM wikipedia where regionName is not null  GROUP BY 1 ORDER BY 2 DESC ", "resultFormat":"csv"}'```
JSON format I thought requires each fieldname to accompany each field value so I don't think you can get the "single header line" format that CSV provides automatically.

Thank you very much! This is exactly what I was looking for.

Are you using rollups on the datasource?  If so I believe rows will be consolidated at times as part of the rollup process ... but the metric values should remain consistent.

compaction can reduce the number of rows.  Where and how do you see it?

image.png

what is `generate_series` ? is this preudo code or an attempted Druid query?

Could you find downtime by looking for a store where COUNT(*) or heartbeats is &lt; 48 in a 24 hour period? or something like that

so like (pseudo code): `SELECT storeNumber, TIME_FLOOR(__time, 'P1D') as "t", count(*) AS "heartbeats" FROM XXX WHERE eventName = 'HEARTBEAT' GROUP BT 1, 2 HAVING "heartbeats" &lt; 48`

It's likely that more information about this error is in the next few lines of the log

The piece you posted here is just the summary, full details and stack trace would follow

Pasted the complete log trace in <#C0309C9L90D|>, pls have a look over there

<https://apachedruidworkspace.slack.com/archives/C0309C9L90D/p1684774446202239>

I can’t say I have for that, I think the most applicable use case for fargate would be for scaling ingestion, and in that case I think you would want the ease of k8s as the interaction layer to spawn new ingest compute (where fargate is the provider of compute)

I suppose you could do this same thing on ecs

were you thinking of running all the services in druid on fargate on ecs?

Hi Yuliya, I have some experience of running Druid on EKS/Fargate. Let me know if I can provide any help on this.

dataset.csv

druid native query.txt

Hi Pavithra,

I haven't taken a look at your data or native query text, but just looking at the SQL ...

... one thing odd that I noticed is that your final "where l.itemName = r.itemName" I would phrase as the second part of the join condition using an AND.    Unfortunately I don't know native query syntax well enough to know if this would make a difference.

Also, your __time value, I assume it is truncated to a high enough level that you are looking for data with the latest incomingEvenhubTimeStamp within each time "slice"?

<@U0521H53QMB> I'd suggest running `EXPLAIN PLAN FOR` on the SQL query, seeing how it got translated to native, and comparing that against your native query

maybe there is some difference that explains it

also you could try running the sub-`query` separately from your native query, and see if each one returns results that look like you expect

No. Druid will only use up to 300GB. You should make sure maxSize to be as close as possible to the actual disk size.

What's the path for Deep Storage in your config? Is it also under the same path as `druid.segmentCache.locations`?

no, actually, i'd like to correct myself. the deep storage is azure and the segment size is indeed set correctly to 800 GB. there was a duplicate config on the cluster which got me very confused. thanks for the responses. :pray:

I think i found a way:
```    "parser": {
      "type": "string",
      "parseSpec": {
        "format": "tsv",
        "timestampSpec": {
          "column": "timestamp",
          "format": "auto"
        },
        "transformSpec": {
            "transforms": [
                {
                    "type": "expression",
                    "name": "a",
                    "expression": "substring(params, index_of(params, '=') + 1, index_of(params, '&amp;'))"
                },
                {
                    "type": "extraction",
                    "name": "c",
                    "expr": "substring(params, index_of(params, '=', index_of(params, '&amp;')) + 1, index_of(params, '&amp;', index_of(params, '&amp;') + 1))"
                }
            ]
        }
        "dimensionsSpec": {
          "dimensions": [
            "type",
            "name",
            "status",
            "a",
            "c"
          ]
        },
        "columns": [
          "timestamp",
          "type",
          "name",
          "status",
        ]
      }
    },```

What a strange format to use both `\t` and `&amp;` as separators

I don't know how consistent the columns are but you could also use a regex

you could also try using tsv with delimiter set to `\t` and setting the listDelimiter to `&amp;` this would ingest `a=b` `c=d` `e=f` as values of a single multi-value field

which is not what you said you wanted but might work well depending on the usecase (I have seen real usecases where that was the ingestion method)

it would look like this on the dataset you provided (which has one more column than you indicated)

Oh.. nice… I will have to give it a shot. Thank you very much for the suggestion. Yeah their messages are weird. Not sure who thought it was a good idea :disappointed:

not at the moment but something like that is in the works building on top of the Multi Stage Query (MSQ) framework

Hi I found following example from <https://www.metatron.app/2020/08/20/exporting-data-using-druid-sql/> but found no documentation. Will it work?

`insert overwrite into directory '/Users/i1befree/workspace/test' as 'EXCEL' with ('maxRowsPerSheet' =&gt; '5000')` 
`SELECT * FROM "druid"."covid_worldwide_test_data"`

Shuffle join for msq is really nice feature Thanks

thanks Clint for release-managing this one!!

I have been eagerly waiting for this release. Thank you for the notification <@U030C8H59T8>

Hi everyone, I just wanted to pop in and share that the <https://open.spotify.com/show/6KAKYLJvCVegsFfKvbfDnt?si=5c6403cc45234163|Tales at Scale podcast> has a couple great new episodes talking about Druid and Kubernetes with <@U057ANG59K4> and Druid 26 with <@U030W50CNAC>.

On deck for next week, <@U0322ET4ZGE> gives me the full scoop on Druid Operator.

I’ve linked to the show on Spotify but it’s also on Apple Podcasts, Google, Overcast, etc.

How are you planning on getting the rabbitmq data into Druid? What is "rabbitmq super streams based model"? By "plugin is being developed right now by my colleague" are you saying you are writing a rabbitmq supervisor?

Yes. It  rabbitmq recently released a preview feature called "superstreams" which has much of the same functionality as kafka or kinesis. In particular,  a rabbitmq stream is an append-only log, so the same ingestion model used by kafka and kinesis works here.

<https://blog.rabbitmq.com/posts/2021/07/rabbitmq-streams-overview/#what-are-rabbitmq-streams>

So, we're writing a plugin that will ingest data from them. We based much of it off the kinesis one, for what it's worth. We're planning on open-sourcing it.  I know my colleague has discussed it, though I'm not sure if it was with the rabbit community or druid.

That is spectacular. Let me know how it goes. If you open source it and contribute it to Druid (and make sure that the sampler API works with it - I don't know if that happens automatically or need a bit of extra work) then ping me and I would be more than happy to make a tile for it in the streaming data loader in the console...

as for your original question: you can only have 1 supervisor per datasource so I assume you will have another (new) datasource for the rabbitmq. does it make sense to let them all run for some time and then just point you app at the new datasource (you can also do a batch backfill by copying from old data source to new datasource)

we recently did something like that where I work. We went from an OSS Kafka cluster to a Confluent cloud cluster (shoutout to Confluent cloud!). We did exactly what I said in my comment. In our usecase we only have several weeks of data that people can access in the app so we just let it run until the new datasource had that much data in it. We did not need to do the backfill but we discussed it before natural delays to the schedule from other projects obviated the need.

We definitely plan on contributing it to druid, so that's great to hear. :slightly_smiling_face: I hadn't considered pointing it at a different data source. So, basically, we could identify when data started arriving at the new data source, and copy all the old data in prior to that. Or, keep the old one around, and stitch it together in  the app. Unfortunately for us we have a few years' worth of data (not a huge amount, so copying it around is likely feasible).

So... I'd like to know the answer to this question as well, but I can tell you what I have found...

I had a unit test that failed under `jdk8, sql-compat=true` but passed under `jdk8, sql-compat=false`

Trying to replicate that locally, I found this in `.github/workflows/reusable-unit-tests.yml`:
```if (${{ inputs.sql_compatibility }} == true); then
  echo "DRUID_USE_DEFAULT_VALUE_FOR_NULL=false" &gt;&gt; $GITHUB_ENV
else
  echo "DRUID_USE_DEFAULT_VALUE_FOR_NULL=true" &gt;&gt; $GITHUB_ENV
fi```
However, I still saw the test pass in a jdk8 container with this environment variable set: DRUID_USE_DEFAULT_VALUE_FOR_NULL=false

:shrug:

Also, just found this, which explains the origin of the SQL compatibility flag: <https://druid.apache.org/docs/latest/configuration/index.html#sql-compatible-null-handling>

yeah, you can set the default value for null to true or false. For example, the following command runs tests in the sql module with default value for null as false:
`mvn test -pl sql -Ddruid.generic.useDefaultValueForNull=false`

Someone else can confirm, but i believe this is to confirm availability of the segments for each datasource (i.e. for the Availability column in the web console Datasources page)

And when segment metadata queries failed, it will block broker startup?
```WARN [DruidSchema-Cache-0] org.apache.druid.sql.calcite.schema.DruidSchema - Metadata refresh failed, trying again soon.```

yes, Broker startup does block until metadata is initialized

would be great, although I actually don't know what versions most people are using!

same, we've tested it with 8.x versions as well and there are a few compatibility issues

Ill probably open a PR to add this to the docs

can you attach the broker logs? would like to understand more about where this timeout is coming from?

the broker log:
```2023-05-27T07:03:02,039 WARN [DruidSchema-Cache-0] org.apache.druid.sql.calcite.schema.DruidSchema - Metadata refresh failed, trying again soon
<http://org.apache.druid.java.util.common.RE|org.apache.druid.java.util.common.RE>: Query[bbd91a0d-0949-4a9a-9e40-39be9d99c821] url[<http://rt-druid-historical-0.rt-druid-historical.rt-druid.svc.cluster.local:8083/druid/v2/>] timed out.
	at org.apache.druid.client.DirectDruidClient.run(DirectDruidClient.java:438) ~[druid-server-0.17.1.jar:0.17.1]
	at org.apache.druid.client.CachingClusteredClient$SpecificQueryRunnable.getSimpleServerResults(CachingClusteredClient.java:647) ~[druid-server-0.17.1.jar:0.17.1]
	at org.apache.druid.client.CachingClusteredClient$SpecificQueryRunnable.lambda$addSequencesFromServer$7(CachingClusteredClient.java:611) ~[druid-server-0.17.1.jar:0.17.1]
	at java.util.TreeMap.forEach(TreeMap.java:1005) ~[?:1.8.0_221]
	at org.apache.druid.client.CachingClusteredClient$SpecificQueryRunnable.addSequencesFromServer(CachingClusteredClient.java:593) ~[druid-server-0.17.1.jar:0.17.1]
	at org.apache.druid.client.CachingClusteredClient$SpecificQueryRunnable.lambda$run$0(CachingClusteredClient.java:300) ~[druid-server-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.LazySequence.toYielder(LazySequence.java:46) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.query.RetryQueryRunner$1.toYielder(RetryQueryRunner.java:97) ~[druid-processing-0.17.1.jar:0.17.1]
	at org.apache.druid.common.guava.CombiningSequence.toYielder(CombiningSequence.java:79) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.MappedSequence.toYielder(MappedSequence.java:49) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.MappedSequence.toYielder(MappedSequence.java:49) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.WrappingSequence$2.get(WrappingSequence.java:88) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.WrappingSequence$2.get(WrappingSequence.java:84) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.query.CPUTimeMetricQueryRunner$1.wrap(CPUTimeMetricQueryRunner.java:74) ~[druid-processing-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.WrappingSequence.toYielder(WrappingSequence.java:83) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.WrappingSequence$2.get(WrappingSequence.java:88) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.WrappingSequence$2.get(WrappingSequence.java:84) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.SequenceWrapper.wrap(SequenceWrapper.java:55) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.WrappingSequence.toYielder(WrappingSequence.java:83) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.java.util.common.guava.Yielders.each(Yielders.java:32) ~[druid-core-0.17.1.jar:0.17.1]
	at org.apache.druid.sql.calcite.schema.DruidSchema.refreshSegmentsForDataSource(DruidSchema.java:517) ~[druid-sql-0.17.1.jar:0.17.1]
	at org.apache.druid.sql.calcite.schema.DruidSchema.refreshSegments(DruidSchema.java:473) ~[druid-sql-0.17.1.jar:0.17.1]
	at org.apache.druid.sql.calcite.schema.DruidSchema$2.run(DruidSchema.java:258) [druid-sql-0.17.1.jar:0.17.1]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [?:1.8.0_221]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_221]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180) [?:1.8.0_221]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293) [?:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_221]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_221]
	at java.lang.Thread.run(Thread.java:748) [?:1.8.0_221]Show context```

if this is always timing out, and you don't see a problem on the Historical, is it possible that the Broker cannot contact the Historical?

If you do `curl <http://rt-druid-historical-0.rt-druid-historical.rt-druid.svc.cluster.local:8083/status>` from your Broker server, does that return something OK?

Sorry for now to reply.  Now our broker has already recovered. And I execute this command ``curl <http://rt-druid-historical-0.rt-druid-historical.rt-druid.svc.cluster.local:8083/status>``, it returns ok.
If it returns connection refused or other error, maybe broker connect historical has something error, you know where the error happen?

Hi Mugil,

Druid stores it's segment data in it's own specific columnar format for query performance reasons, that to my knowledge is currently not read directly by any other products on the market.

Druid does provide a segment dump tool that will export the data to CSV, if you need to use the data in it's raw form:  <https://druid.apache.org/docs/latest/operations/dump-segment.html>

But I would recommend using one of the APIs for pulling the data via simple SELECTs .  I've tested pulling millions of records through both the sync and async query APIs, as well as the new MSQ Task API ... they all work fine, you just need to find the one that works best for you.

Hey john, 

Thanks for your inputs.. I'll check these options.

<https://github.com/apache/druid/blob/master/owasp-dependency-check-suppressions.xml>

the offsets are stored in the metadata store. the reader will resume from those offsets. It falls back to earliest offset in case the offset stored in metadata store is no longer available

Check in the ingestion log if it is not able to connect to specific offset and waiting for that.

If for any reason offset is missed deleted from kafka and druid has not completed consuming it, it will wait. In that scenario, we should stop the ingestion, do HARD Reset of supervisor and start reading from first available offset from kafka. BY doing this you might loose the data from the offset druid was waiting and till the offset you have started now.

Before doing this make sure you are able to connect to kafka stream, you can check this by opening the supervisor in `open in dataloader` and by going to the `connect` tab.

is the ingestion log available in the druid ui?

Screenshot 2023-05-29 at 3.37.40 PM.png

i got this when i tried to `open in dataloader`

Check if your zookeeper is running fine and you are able to connect to the specific topic from outside , may be running a consumer command to read from the topic.

Also check if you have a stable overlord Leader. you may check the leader overlord log to see if you can find more info on the error