Apache Druid

hi, I was doing a bench mark test using Jmeter and test was done as follows:

2.) Call druid via java microservice and API gateway

I see that the second option is faster compared to the first(direct druid query)

how many brokers and routers did you have? How were you load balancing the requests when you were connecting directly to druid API?

Hi <@U031A9D19NU> please find below the cluster details

not really sure why there will be a difference if it's just one broker. could be some client-side issue.

ok. Just one difference that i can think of is, on the java microservice side , we are enabling response compression

where as with druid, we are not enabling compression

not sure whether that can be a difference. The output is less than 1MB though

<https://druid.apache.org/docs/latest/querying/sql-jdbc.html> - it is already available.

I mean for spring boot. Native via avatica I am aware about

what is special about spring boot? Avatica driver is the standard JDBC implementation for Java. Any 3rd party library that speaks JDBC language and uses the Driver interface, should be able to connect to druid.

Well, spring boot comes with ORM, that pre constructs the query template. Also, the select query can be made, using java method names.
<https://www.javatpoint.com/spring-with-orm-frameworks|https://www.javatpoint.com/spring-with-orm-frameworks>

So you don't need a druid JDBC connector for java. The responsibility of that connector is to execute JDBC queries from a java application. That is something that Avatica driver already does and what you will use in your application as well.

The custom implementation that is needed here is what jdbc queries get generated as developers use a higher-level abstraction and leave it to the framework to build the actual sql queries. This is where I believe the `Dialect` interface comes in. I wonder what happens if you just use Postgres dialect along with Avatica JDBC driver. You could also write an implementation of Dialect interface that works well with Druid. In some ways, it very similar to writing a dialect for tableau or a BI tool that auto-generates SQL queries as users work with a UI.

P.S - This short analysis is based on cursory research about how spring boot ORM works.

Thank you so much Abhishek. I’ll definitely explore this. Will need about a week.

The order of aggregation is not guaranteed to be deterministic, see note<https://druid.apache.org/docs/latest/querying/sql-aggregations.html| here>. My understanding is that HLL sketches will have minor variations from run to run for this reason. The results should still fall within the accuracy level expected.

Is it only because of underlying HLL lib behavior? I ran Druid 25 with old HLL lib (1.1.0) and results are consistent.

I'm not sure. Perhaps something changed in the order of aggregation in between 0.19 and 25.0. What difference do you see?

In general, just inconsistent result for the same request. I haven't found big difference in HLL accuracy.
0.18.1 groupby result is always deterministic for HLL.
It is a big shift for customers as even though they know that value is approximate people used to rely on this because of stability.

I'll ask around for more clarity on why this change occurred. I think that as long as the accuracy is consistent, it is working as expected.

thanks! let me know if you find anything.

the approach would be to create an ingestion spec and submit to druid api (if you are using 25.0 then you should use MSQ)

<@U050Y7XRHAB> I second the idea of using <https://druid.apache.org/docs/latest/multi-stage-query/reference.html#sql-reference|SQL Based Ingestion >for this.

Hello Druid users,
Just wanted to tip you of this nifty little feature in Kafka ingestion. You can configure Kafka Supervisor to stay idle when data on stream is sparse and is inactive for a while and no index tasks are spun during this time. When data arrives on stream again, Supervisor will create index tasks as usual. This saves computing cost without effecting performance when paired up with autoScalerConfig. You can find more information on the configuration here <https://druid.apache.org/docs/latest/development/extensions-core/kafka-supervisor-reference.html#kafkasupervisorioconfig>

If you configure your segments to have 2 or more replicas (which is recommended), you would need at least as many historicals as replicas.
A single historical setup wouldn't be resilient to historical failure and could lead to data unavailability.

<https://druid.apache.org/docs/latest/operations/basic-cluster-tuning.html#number-of-historicals> recommends having a smaller number of large servers provided that you have sufficient fault tolerance

the merge across segments in the historical is single threaded for a query (unless the aggregation spills to disk in which case it can be multi threaded). So having more than one historical gets more merge threads for a query

For faster startup you can try setting `druid.segmentCache.numBootstrapThreads` to the number of CPUs on the machine. Depending on how you are splitting the smaller nodes they might be using more threads as they might see more processors (Kubernetes for example normally reports the total node CPUs).

I believe that in general Druid never abruptly (between adjutant versions) changes default behavior so as to allow for a version rollback. So unless you enabled some cool config (not sure what, off the top of my head in the versions you mentioned) you should be good to downgrade

kerberos is used for security is clustered setup. Can you paste the kafka config options you entered in Druid?. This error is coming from kafka. Also try using the kafka console consumer and connect to the same topic using the same config as you entered in Druid. If the console consumer cannot connect then Druid also cannot

thanks for your help , it was because of user and permissions , after re applying it issue got fixed. :slightly_smiling_face:

Hi Kanthraj,

You may use timestamp_parse to convert to any timestamp.

```timestamp_parse (string expr, [pattern, [timezone]]) parses a string into a timestamp using a given Joda DateTimeFormat pattern. If the pattern is not provided, this parses time strings in either ISO8601 or SQL format. The time zone, if provided, should be a time zone name like "America/Los_Angeles" or offset like "-08:00", and will be used as the time zone for strings that do not include a time zone offset. Pattern and time zone must be literals. Strings that cannot be parsed as timestamps will be returned as nulls.```

Hi,
i tried to use that, but its not working for me.

SELECT timestamp_parse('2023-04-04 10:00:00', 'yyyy-MM-dd HH:mm:ss', 'UTC')

always give me error:
druid error: Unknown exception (org.apache.calcite.tools.ValidationException): org.apache.calcite.runtime.CalciteContextException: From line 1, column 8 to line 1, column 75: No match found for function signature timestamp_parse(&lt;CHARACTER&gt;, &lt;CHARACTER&gt;, &lt;CHARACTER&gt;)

I tried from one of the druid datasource as below and it is working for `TIME_FORMAT.`
```SELECT TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss', 'UTC') FROM rle```
<https://druid.apache.org/docs/latest/misc/math-expr.html#time-functions>

image.png

Hi am still facing error here: when record gets insert into the druid the time comes in EST time and since it wont have any zone info Druid is treating it as UTC and getting inserted

SELECT TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss', 'UTC') FROM  "XXX" WHERE eventName = 'ACTIVE' ORDER BY __time DESC LIMIT 1

__time stored in druid is in UTC(BUT actual time value is in EST as it wont have the zone info druid assuming it as UTC) when i execute the above query it gives the EST time only not utc..


below query gives the

  SELECT Current_timestamp as currentTime, TIME_FORMAT(__time,'yyyy-MM-dd HH:mm:ss', 'UTC') as lastruntime from
  "lockers-stage-lockers-mco" WHERE eventName = 'HEARTBEAT' ORDER BY __time DESC LIMIT 1


screenshot

now_time is in

Druid will store the timestamp in UTC only, So while ingesting the data try to convert from EST to UTC and then insert into druid table.

Would automatic schema detection solve your use case? There is some work on this with <https://druid.apache.org/docs/latest/querying/nested-columns.html|nested columns> and upcoming <https://github.com/apache/druid/pull/13653|automatic column detection with typing expected in Druid 26.0>(still experimental).

Thanks for the response, unfortunately no since what i need is a more development-side solution for how to maintain schema across multiple clusters along with versioning

pre-segment caching in the historicals will help. This can significantly improve query performance if you have same query running on additional data

i have enable the following cache configs.
also segments are cached in datanodes.
are anything else that i can do?

druid.query.cache.type=local
druid.query.cache.populateCache=true
druid.query.cache.useCache=true
druid.query.cache.enable=true

druid.historical.cache.useCache=true
druid.historical.cache.populateCache=true
druid.historical.cache.enabled=true

druid.broker.cache.useCache=true
druid.broker.cache.populateCache=true
druid.broker.cache.enabled=true

increase the size of the cache druid.cache.sizeInBytes

Could you share your spec and the steps you followed to create it?

Added the Bootsrap server and then the topic and clicked start stream and next: start stream. I recevied th feedback 'No data returned from sampler'

The following is the Screen shot of the feedback

Are you sampling from the start of the stream? If not, it looks for data appended after you begin sampling and may not return data if no new data is being consumed

Yes its from the start of the stream, its my first go at it

Could you please check the response and error using your browser's developer console? I wonder if the consumer properties related to auth are missing

How do I go about that, accessing the browsers developer console?

Are you perhaps trying to edit a very old sepc (2021.07 or earlier i think) <https://docs.imply.io/latest/druid/ingestion/ingestion-spec/#legacy-dataschema-spec>

In that case copy over   `dataSchema.parser.parseSpec.timestampSpec` to `.spec.dataSchema.timestampSpec`

it is giving you an error about missing timestampSpec because your timestamp spec is missing, you have not gone though all the steps of the data loading wizard so it did not finish making a spec for you. You can write a spec by hand and submit it, you do not have to use the spec building wizard. But if the sampler can not fetch any data likely something is wrong. Is your Druid able to reach your kafka bootstrap server at 178.128.113.56:9092 ?

How do I modify the timespec in druid to pick up historical data from kafka?

here are the docs for the timestamp spec: <https://druid.apache.org/docs/latest/ingestion/ingestion-spec.html#timestampspec>

if you manually write an ingestion spec <https://druid.apache.org/docs/latest/development/extensions-core/kafka-ingestion.html#json-input-format-supervisor-spec-example|like this example>, you can submit it via the API or via the console

No, there isn't a way to do it in bulk, you would have to spell out the name of every key but doing that defeats the purpose of the COMPLEX&lt;json&gt; type

i suppose we could add customization options to lower or uppercase the keys someday if that’s a common need, or a transform function for it :shrug:

or maybe an option store them case insensitive or something

Hi, It would be nice to have `transform` functions in druid’s the transform spec such as
• <https://spark.apache.org/docs/latest/api/sql/index.html#transform>
• <https://spark.apache.org/docs/latest/api/sql/index.html#transform_keys>
• <https://spark.apache.org/docs/latest/api/sql/index.html#transform_values>


Here are examples.

```&gt; SELECT transform(array(1, 2, 3), x -&gt; x + 1);
 [2,3,4]
&gt; SELECT transform(array(1, 2, 3), (x, i) -&gt; x + i);
 [1,3,5]```
```&gt; SELECT transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -&gt; k + 1);
 {2:1,3:2,4:3}
&gt; SELECT transform_keys(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -&gt; k + v);
 {2:1,4:2,6:3}```
```&gt; SELECT transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -&gt; v + 1);
 {1:2,2:3,3:4}
&gt; SELECT transform_values(map_from_arrays(array(1, 2, 3), array(1, 2, 3)), (k, v) -&gt; k + v);
 {1:2,2:4,3:6}```

Those transform functions also accept  a map parameter.
```df.selectExpr("transform_values(col1, (k,v) -&gt; coalesce(v.a, v.b, v.c, v.d)) as col1").show()```


Hi Druid experts

is there a way to configure retention rule based on regex with datasource name
for example
```datasource starting with wiki-*  will be reatained for 3 months
datasource starting with test-* will be retained for last 2 months```


It is a matter of minutes. Perhaps the email went to your spam folder?

Hi <@U043WQK8XDJ>, are you interested in Imply Cloud (our hybrid offering) or Polaris the fully managed SaaS offering. The former needs engagement by Imply to be setup bc it connects to the data plane hosted in your env. For the latter you can sign up here: <https://signup.imply.io/get-started>

Hi Gabriel, i need hybrid offering. I have already created Cloudformation template and as per above link, i was expecting email with credentials after imply cloud account is setup, but haven't received it yet, so want to check on the same

ok sounds good. let me follow up internally and get back to you

Cool, here's ticket ref - <https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsupport.imply.io%2Fhc%2Frequests%2F12578&amp;data=05%7C01%7Cglo51706%40adobe.com%7Cea1ba8e6c9a3472be8a908db369e217e%7Cfa7b1b5a7b34438794aed2c178decee1%7C0%7C0%7C638163825015682857%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=UBtX1l0fVSK65rHm4IawnsYj6uDqf7v0xv6lnFQsSg4%3D&amp;reserved=0|https://nam04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fsupport.imply.io%2Fhc%2Frequests%2F12578&amp;data=05%7C01%7Cglo51706%40adobe.com%7Cea1ba8e6c9a3472be8a908db369e217e%7Cfa7b1b5a7b34438794aed2c178decee1%7C0%7C0%7C638163825015682857%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&amp;sdata=UBtX1l0fVSK65rHm4IawnsYj6uDqf7v0xv6lnFQsSg4%3D&amp;reserved=0>

<@U043WQK8XDJ> let me pull in the team to help you out here. <@U032URN5AKB> from your region will be able to help

It does sounds like a bug. Could you create an bug report at <https://github.com/apache/druid/issues>
You should be able to run a manual compaction job on the old segments where you override with a new spec instead of relying on the stored ingestion spec.

Sounds like an internal communication failure. Is the overlord running? Or did it fail when this was occurring? Could it be network failure?

Hello Everybody,

I am using druid single-server-small configuration and data are loaded and using in production,  is it possible to upgrade from small -&gt; single server medium or large  from the existing small one without data loss ?

I believe the SQL Window functions currently being worked on should handle this, e.g.:

```select *,
  avg(Price) OVER(ORDER BY Date
     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )
     as moving_average
from stock_price;```

Thanks <@U04C4593VDY>. If I understand it right, by `currently being worked on`, you mean it is in the making but not yet released/available. Could you confirm that?

I don't see this available on our Druid instance (and we are on 0.23.0)

Hi <@U032X7PGEN4> , yes they are under development.  Though I don’t know which Druid release they will be in. 


You can test them out if you build from the master branch. <@U032CA4JJV6> wrote a great blog on this: <https://blog.hellmar-becker.de/2023/03/26/druid-26-sneak-peek-window-functions/>

Thanks <@U04C4593VDY> and thanks <@U030K4UM3H7> for the blog. This helps our planning a lot!

This should help : <https://druid.apache.org/docs/latest/configuration/index.html#enabling-metrics>

Saket, can you post the complete error message as well?

Hi Pavithra,

Is this what you are looking for?  <https://docs.imply.io/polaris/time-series-functions/>

This is currently released only in Imply Polaris (SaaS Druid) since last week but may make its way into OSS Druid over time.

Hi <@U04C4593VDY>.thanks for quick reply.the above links you provided will help me.i have one more question?  i have requrement on aggregate will first interpolate to find values at the beginning and end of each interval, sum all raw values in said interval (including the interpolated values), then divide the sum by the length of the <http://interval.is|interval.is> there any way in druid we can achive this?

It sounds like you want to compute an integral under the curve ...

... the upcoming LAG() window function should handle that ...

... until then there is a DATE_EXPAND() function which is used in conjunction with UNNEST(), which can generate evenly spaced time values.  This has just come out in Imply, not sure if it's readily available in OSS right now ... and there isn't much doc on it right now, maybe take a look at: <https://imply.io/blog/whats-new-in-imply-polaris-our-real-time-analytics-dbaas/>

There is doc on `unnest` and the `date_expand` docs are in the work. <https://github.com/apache/druid/blob/master/docs/querying/sql.md#unnest> . These docs will go out with Druid 26.

Thank you.i will go through these docs and check

Hello all, I have druid installed on k8s cluster and enabled pack4j extension to secure access to the druid console. My question is that, is it possible to unsecure de query Api endpoint of the router in order to execute SQL queries whithout authentication ? because another internal service is using  it to execute queries.

There are updates coming to the docs : <https://github.com/apache/druid/pull/14035/files>
I think the relevant portion is:
```druid.msq.intermediate.storage.enable=true
druid.msq.intermediate.storage.type=s3
druid.msq.intermediate.storage.bucket=YOUR_BUCKET
druid.msq.intermediate.storage.prefix=YOUR_PREFIX
druid.msq.intermediate.storage.tempDir=/path/to/your/temp/dir```

Yes. Compaction will create a new set of segment files that replace existing segments. While it builds the new set, the old set are available for query.

But just to be clear: compaction happens in the background and is totally "invisible" from the query side (other than it usually improves performance). Very notably it does not change the results of any query (if it does it would be a big issue). You need to make sure that your data model makes sense without thinking about compaction

specifically rollup is not "deduplication" of data in general, it is just a group by applied on your data with duplicates and all. It would only "deduplicate" your data if you had (1) everything as a dimension, (2) no metrics and (3) you considered data to be duplicate if a single row is equal in all values to another row.

```you considered data to be duplicate if a single row is equal in all values to another row.```
it is precisely this

to expand on use case a bit, we have different requirements for the first 3 months data, then the next 3, then the next 3

first 3 months will be "raw" (query granularity none), then the next 3 we will aggregate (query granularity day).  so for the first three months it is ok to deduplicate as if there are no aggregations

One question: When the auto-compaction is enabled for a datasource, will the original segments (without compaction) be kept intact forever?
From the above discussion I assume, no. If so, when will the old segments be discarded?

if you mean for the original segment, in the object storage, yes, it's kept until the 'kill task' is issued

and by default, druid do not issue this kill task for the perid that contains used segments

I still need to test if issuing a manual kill will remove or not the used files, it should not remove, but the storage is so cheap that I didn't had time to setup another cluster to test this yet

&gt; it's kept until the 'kill task' is issued
Yes, that seems to be true.  But the dilemma that I've with Kill Task is - how to differentiate between
1. the segments marked as unused after compaction  
2. those segments that are marked as unused(dropped) by a Data Retention Rule ? 
I do not want to delete the segments marked as unused because of Data Retention Rule.

&gt; but the storage is so cheap
Yes :slightly_smiling_face: But.. the data generated these days is also humongous!

yeah, you kinda need to run the kill before the retention rule go in

but I was a little disappointed as well when I discovered that I had all segments on the s3

Renato, could you expand on this? Did the kill job fail to remove the unused segments from S3?

Hello, <@U030K4UM3H7>, I thought that the kill task was only necessary to remove older data after the retention period, as I don't want to remove any data, I never ran any kill task, but I was sure that the compaction jobs would remove the older versions automatically (as well failed tasks that leave unfinished segments in the s3, eg: when a mm fails with OOM)
I think Clinton had the same impression that this cleanup maintenance would be included by default, and not affect unused segments not loaded because of the retention period

i have not ran the kill task yet to test, even inside the retention period because if this delete the used segments the headache will be greater than the cost of having the data duplicated, but I need to address this at some point

I see. So perhaps an improvement would be to separate kill for cleanup vs kill for “dropped” segments data on retention. 

<@U030K4UM3H7>: The assumption that I had when I ran compaction task was that, the older smaller segments will be removed. But Druid keeps both segments.
with Kill Task - how to differentiate between
1. the segments marked as unused after compaction 
2. those segments that are marked as unused(dropped) by a Data Retention Rule ?
Do not want to delete the segments marked as unused because of Data Retention Rule.

Described here with a lot of details -
<https://github.com/apache/druid/issues/9755>

for who was not aware of what "30W" means, according to chatGPT, 30W means:
&gt;  The term "30W" is a notation used in China to represent 300,000 units. The W stands for "wan", which is used in the Chinese language to denote ten thousand. Therefore, "30W" stands for 30 * 10,000, which equals 300,000. So, in your context, there are more than 300,000 segment records in metadata storage.


Thanks <@U0316GKJR0R> for the issue  and <@U038XPQFJHM> for the 30W explanation (:slightly_smiling_face: I was thinking it was an upside M).   There is ongoing work in the Apache Druid project to figure out cold query capability, this is the idea of having used segments that are not cached in historicals and live in deep storage only.
I think this will dovetail nicely to distinguish between cleanup and retention based segments, so that cleanup can occur regularly without removing data that you don't want to lose but also don't want to cache.