Apache Druid

image.png

Hi
<https://github.com/apache/druid/pull/13287> helps with this duty and is supposed to be in Druid 25.0.0

Hi <@U0319HD8HEC>
This patch really did great help. Thanks for the amazing work. But it didn’t solve all out problems, the snapshot building is still a little slow. So we used a thread pool with size 20 instead of a single thread do build it. Now the time of building is about 100 seconds. :smile:

Hi <@U041J6ZBG1L>, that's great to know.
It may be possible to optimize the snapshot creation as you say, but there may be another route you could explore..
How many rows do your segments have on average? 1.5 million segments is a lot, and there may be scope for reduction using rollup / compaction with range-partitioning etc if you haven't already enabled those.

Thanks for pointing it out <@U0319HD8HEC> I’ll discuss this with my team. Thanks a lot.:grinning:

Hi! Druid does not have the functionality to automagically make projections of one Druid dataset onto another just yet. In general it is common to think of your data in Druid as a projection of your raw data which lives in Kafka or S3 or something. In Druid this is referred to as Rollup (<https://druid.apache.org/docs/latest/tutorials/tutorial-rollup.html>). You can ingest your data only taking the dimensions you need and pre-aggregating the appropriate metrics for your analytic use cases. When doing it you will a single row in Druid representing multiple rows in your source data allowing for huge speedup/space saving (a rollup ratio of 30x is not uncommon). You could also ingest data multiple times (from the same source data), picking different sets of columns and getting different rollups for each dataset. It would then be up to you to route your queries accordingly to the different datasets you create.

Check it out! The link to this :slack: Slack is now front and center on the Druid homepage

`Ran [1] specs, [0] succeeded, [1] failed`

but it would be useful for not freaking out when checking the home page status

it's a game changer in certain deployments!

Screenshot 2022-11-29 at 12.57.29 PM.png

I tried with refresh interval none that also doesn’t work

I believe this happens when you are trying to display too many tasks in the UI. You can control the time frame for which task logs are stored and therefore reduce the number of tasks displayed.
Take a look at <https://druid.apache.org/docs/latest/configuration/index.html#task-logging|Task Log configuration> particularly regarding <https://druid.apache.org/docs/latest/configuration/index.html#log-retention-policy|retention> settings.
By setting `druid.indexer.logs.kill.enabled=True` and an appropriate duration to retain with `druid.indexer.logs.kill.durationToRetain` specified in milliseconds.

if you are getting 502/504 that timeout is likely happening in some network service (like ELB) that sits between the console (ui) and Druid. It is important to note that in Druid 24.0 there were a number of improvements made to the speed of that API.

Also in addition to the properties Sergio has mentioned you should look at setting `druid.indexer.storage.recentlyFinishedThreshold` lower. It is documented on the page linked above

Thanks <@U030W50CNAC>, I knew I was missing one!

BTW this: <https://github.com/apache/druid/pull/12404> is the change I was referring to that made the task fetching much much better/faster. Honestly, I believe it is worth upgrading for that change alone. Even if you generally do not encounter an issue with having too many tasks (and slow task APIs as a result) you might find yourself in that situation due to some issue completely outside of Druid. Maybe one day you wake up and there is a network connectivity issue between Druid &lt;&gt; Kafka and the supervisor is continuously restarting tasks that fail quickly. The console would load slowly when you need it most. The PR linked above is a game changer.

Thanks <@U030K4UM3H7> and<@U030W50CNAC>  for the quick responses

Indeed it looks like we have ngnix in front of druid-router.. will try and increase the timeout there
Thanks for inof regarding 24.0.0 that is another reasin to plan our upgrade

<https://github.com/apache/druid/pull/12404> was 24.0

what version are you on? I know there were vectorization improvements in the 24.0 release <https://github.com/apache/druid/releases#24.0.0-query-engine-updates-query-performance-improvements>

the only time i’m aware of off the top of my head that non-vectorized can be sometimes a decent amount faster than vectorized is with certain expression virtual columns involving a “single input” column expression, such as some transform on __time, and where the value cardinality of that column within the segment is very low. this is because the non-vectorized expression processing stuff has a cache that it uses for single input long and string expressions that has not been added to the vectorized processing yet

if you have an example of a query that is slower with vectorization enabled, i'd love to see it

Hello, here is a trimmed down example where vectorization is much slower.

In this example, vectorization turned on hits a timeout at 300s. w/ it turned off, it finishes &lt; 8s or so.

Druid version: 0.23
datasource stats:
• total segments: 39,593
• avg segment size: ~350mb and ~5.9million rows
• segment granularity: week
• total segments queried: 282
 Some observations:
• querying 1 dimensions works regardless of whether vectorization is on or off.
• w/ vectorization on, it fails as soon as a second dimension is added.
• if i’m looking at things correctly, the ttfb and query cpu usage is much larger when vectorization is on.
`col1` has a cardinality of 6 and `col2` has a cardinality of 352 w/ the query returning 1176 total rows.

```{
  "queryType": "groupBy",
  "dataSource": {
    "type": "union",
    "dataSources": [
      "my-data-source"
    ]
  },
  "granularity": "all",
  "intervals": [
    "2022-11-27T00:00:00+00:00/2023-01-01T00:00:00+00:00"
  ],
  "dimensions": [
    "col1",
    "col2"
  ],
  "limitSpec": {
    "type": "default",
    "limit": 30000.0,
    "columns": [
      "col1",
      "col2"
    ]
  },
  "aggregations": [
    {
      "type": "longSum",
      "name": "longSum(count)_0x0b",
      "fieldName": "count"
    }
  ],
  "filter": null,
  "having": null,
  "context": {
    "queryId": "my-test-query",
    "useCache": true,
    "populateCache": true,
    "useResultLevelCache": true,
    "populateResultLevelCache": true,
    "maxOnDiskStorage": 0,
    "priority": 20,
    "vectorize": true,
    "debug": true
  }
}```


we just upgraded to v24 and this issue seems to no longer exists. at least for this query. we noticed it started to work once the historical nodes were upgraded.

TIL that named window functions are a thing in SQL. I wonder if I will ever know SQL fully or if it is like a fractal of complexity where the more you know the more you know you don't know.

Vadim, you are not that far off from knowing everything SQL ... :wink: ... I think it's mostly a matter of which database "camp" you grew up in.   As an Oracle and SQL Server veteran I was exposed to a lot of these fancy SQL features (and had to work around them before that).   Here are a handful of the newer, more interesting features I think are worth knowing about (and maybe consider implementing in Druid someday):

• GROUPING SETS ... looks like Druid already has this
• STRING_SPLIT(), STRING_AGG() (SQL Server)
• CONNECT BY (Oracle, hierarchal tree walking)
• INTERSECT, MINUS (to complement UNION)
And one other that I don't know if anyone has implemented explicitly ...
• The ability to generate a sequence list as an inline table or CTE
Thanks.  John

Hello world! Thought I’d send out a big invitation and *thank you* to everyone speaking at Druid Summit next week! It’s gonna be coooool. <@U0344FW86DD> <@U0304FW2HPH> <@U030W50CNAC> <@U030K4UM3H7> <@U030L4P6UDA> <@U030MNRGBNG> <@U030GL518NR> <@U03BJAMQ4H5> <@U030AQ8AR8E> <@U03DW1FMU4X> <@U031Y2P142E> <@U032JM0K2H0> <@U03EFRAJNMC> <@U0421NQ3DQX> <@U030GMKBX1B> <@U030KB0NXM0> <@U035FNDU6HH> and a tonne of others in our club… (have I missed anyone?!) you can see the agenda and register here: <https://druidsummit.org/>

druid will take care of the data stored in deep-storage as part of managing the data source.

i.e if you have a 6month retention period on your datasource, once it triggers and deletes that data from druid, it will delete the deep storage data as part of the kill task.

Thanks <@U03HF9GLL1X>, this helps. Isn't there a way, however, to retain the data in deep storage for longer than what the data retention has been configured for inside the cluster?

All this comes down to how you have configured coordinator kill <https://druid.apache.org/docs/latest/configuration/index.html#coordinator-operation>
If `druid.coordinator.kill.on` = false, the segments will not be removed from deep storage

no. your best course of action is to export your data from postgresql as TSV (or similar) into S3  (or whatever) and then load it from there

Here are the existing options - note we have already enabled the datasketches extension

We have a field that represents the key for the sketch and another integer field that represents the summary value (in our input file)

That does not seem to be an aggregator that the UI is currently aware off but do not let you slow you down... The UI is built to be extensible and to have plenty of "fallbacks". Let me show you:

first of all you can type literally anything you want into that field:

of course the UI will not know what are the other fields that are associated with that type but then you can just go to `Edit spec`

and edit that specific agg as you see fit

this is done in such a way because Druid is very extendable and you might add your own `coolAgg` and then still want to use it from the UI

and when I do back to `Configure schema` I see:

which is great! it is clearly an error coming form the `arrayOfDoublesSketch` since `comment` is not a base64 thing

Thank you very much <@U030W50CNAC> - this was very helpful and I’ll try it out!

Thank you, in the mean time I will make sure the UI becomes aware of ArrayOfDoubles

Added to the console! <https://github.com/apache/druid/pull/13486> Happy Friday!

That was quick, thank you for this Vadim!

i don't know of one but it sounds like a cool idea!

This is for an automotive application to track data about cars that will go into predictive model training, ideally in ways that are easy to slice and dice so that the models can be trained on edge cases.

Scale of data set is around 1 TB. Right now we're looking at putting it in a postgres instance until we find something better

it's possible to query with radius if you use native query
but it's possible to use basic lat/lng filters with rectangular (using lat&gt;X and lat &lt; y and long&gt;...) over the SQL engine

<https://druid.apache.org/docs/latest/development/geo.html>

you can also use geohashing (druid has no builtin support for this but the approach works without DB support)

i would look into those two things and see if one meets your needs as far as geo constraints

for time constraints, druid has a sense of primary timestamp, so probably you'll put the start of the range as primary timestamp (always called `__time`), and the end as secondary (let's call it `end_time`).

filters on the primary timestamp are super fast (there is a special index for this) so you can leverage this if you have a sense of what the max duration is. something like this for a point filter on `target_time`:

```WHERE [target time] &gt;= __time AND [target_time] &lt; end_time AND [target_time - max_duration] &lt;= __time```
the `[target_time - max_duration] &lt;= __time` clause enables more aggressive use of the primary timestamp index

you can do `druid.segmentCache.lazyLoadOnStart = true`, which moves the work to query time instead of server startup time

ref: <https://druid.apache.org/docs/latest/configuration/index.html>

&gt; Whether or not to load segment columns metadata lazily during historical startup. When set to true, Historical startup time will be dramatically improved by deferring segment loading until the first time that segment takes part in a query, which will incur this cost instead.

We're giving the option a try now. Thanks.

To be clear; we have log lines like this:
```2022-12-03T00:38:57,281 INFO [main] org.apache.druid.server.coordination.SegmentLoadDropHandler - Loading segment cache file [51944/76371] ...```


do you have a new enough version such that this option is available? i'm surprised it has no effect

The parameter was definitely detected by Druid
```2022-12-03T00:34:26,200 INFO [main] org.apache.druid.cli.CliHistorical - * druid.segmentCache.lazyLoadOnStart: true```


I notice that it's loading everything from the infoDir

yeah, it would do that even w/ the lazy load setting

the lazy load setting just speeds up the process by loading not as much stuff

like, it won't load the metadata for each column, for example

Oh, well that's what takes hours to startup. The metadata I've never had any issue with that taking any time whatsoever.

the parade of "Loading segment cache file" (from 0 to 76371) is what takes hours?

I'm planning to set `druid.segmentCache.numBootstrapThreads` to `num_cores`

I measured it as being able to read around 17/second.

Something you may find interesting though: if we interrupt this process (say, to apply a new config property) then it seems to catch up VERY quickly to where it left off last time.

In all, some server restarts go by in seconds and others in hours.

FYI - org.eclipse.jetty.server.Server - *Started* @6913615ms

are you setting `druid.segmentCache.numThreadsToLoadSegmentsIntoPageCacheOnDownload` or `druid.segmentCache.numThreadsToLoadSegmentsIntoPageCacheOnBootstrap`?

these are configs to prepopulate the page cache with segment data on startup — it's going to force a scan off disk of the entire segment during startup

but if set they would extend startup time quite a bit

(the purpose is to make query performance more regular at the cost of startup time — they're like the opposite direction of lazy loading)

Before today we just set locations but now we set these three:
```$ egrep segmentCache historical/runtime.properties
druid.segmentCache.locations=[{"path":"/data/tripstack/druid/segment-cache","maxSize":"1000g"}]
druid.segmentCache.lazyLoadOnStart = true
druid.segmentCache.numBootstrapThreads = 16```


Just to be clear it's not the `Loading segment[x/y]` that are taking time?

With 16 threads allocated we're now getting just under 90/second

It seems pretty odd that the `Loading segment cache file` stuff would take so much time, since it isn't doing much. Just loading the descriptor json files from the info dir

the `Loading segment[x/y]` parade is doing the actual segment loading

Example log line:
```2022-12-03T01:29:46,685 INFO [main] org.apache.druid.server.coordination.SegmentLoadDropHandler - Loading segment cache file [55566/78304][/data/tripstack/druid/segment-cache/info_dir/best-booking-emails_2022-07-10T23:00:00.000Z_2022-07-11T00:00:00.000Z_2022-09-14T15:07:20.565Z_86].```


`Loading segment[x/y]` also takes some time but seems to go by MUCH faster

does anything get logged between the `Loading segment cache file` lines, or just a parade of them with nothing in between?

Nope. Just a parade of these with nothing in between.

how big are the individual files in your `info_dir`?

I just noticed these are on the `main` thread. However it does seem to be going faster now that we've increased the `druid.segmentCache.numBootstrapThreads`

not much is being done except reading them all in sequence

seems quite odd it would take appreciable amounts of time

unless they're super jumbo for some reason? usually they're a few KB each so even 100,000 of them would be just a few hundred MB

Biggest ones are 6K but the majority are around 4.7K

if you can capture a flame graph i'll take a look <https://support.imply.io/hc/en-us/articles/360033747953-Profiling-Druid-queries-using-flame-graphs>

also out of curiosity i'm wondering how fast i/o is on the volume

try `find [path to info_dir] -type f -exec cat {} \; &gt; /dev/null`

to see how long it takes to simply read through all the files

Okay with 16 threads assigned the `Loading segment[x/y]` took about 4 minutes so nearly all the work is done in `Loading segment cache file [x/y]`

```$ date; time find info_dir -type f -exec cat {} \; &gt; /dev/null; date
Fri 02 Dec 2022 08:42:14 PM EST

real	1m56.704s
user	1m7.776s
sys	0m49.148s
Fri 02 Dec 2022 08:44:11 PM EST```

ok, now i'm interested in a flame graph :slightly_smiling_face:

if you can capture one post it here &amp; i'll have a look

Unfortunately this is a production system and we are just coming out of maintenance now so I won't be able to get a flame graph today.

well, remember it next time if you can; we'll be here :slightly_smiling_face:

Looking back in logs it's notable that just before the parade of `Loading segment cache file` logs we had an error from ZK
```2022-12-03T01:43:37,309 ERROR [main-EventThread] org.apache.curator.framework.imps.EnsembleTracker - Invalid config event received: {server.1=bidruid-zk01:2888:3888:participant, version=0, server.3=bidruid-zk03:2888:3888:participant, server.2=bidruid-zk02:2888:3888:participant}```


is it possible to post the contents of a cache file that is known to take time?

efoe-search-started-events_2022-08-16T00:00:00.000Z_2022-08-17T00:00:00.000Z_2022-08-17T06:56:26.428Z_11

<@U031A9D19NU> There's no particular file that takes noticeably longer than others. It's just that Druid slowly processes them at a rate of ~ 400 KB/sec so it takes _forever_ to get through &gt; 70K of them.  Here's a typical example:

fwiw, I just tried deserializing that (on the off chance it hit some perf issue) but that's not it. the deserialize rate is good (10s of thousands of JSONs like that per second)

Hi, I'm interrested about this behavior.... so finaly how did you solved that ? just by the  `druid.segmentCache.numBootstrapThreads`  and lazy loading ?

&gt;  As we have recently started with druid, we would really appreciate if you can suggest us some good training courses (free and paid) on druid. (edited)
Hi Prabir, do check out <https://learn.imply.io/>

&gt;  We have recently taken over a project which uses druid as a data source which ingests data from Kafka every 15 mins. There are quite a lot of kafka topics(few are really fat with lot of columns and few are small) that land up as druid data sources. They all contain millions of records though.
Seems your Kafka data is pretty skewed. Any chance you can fix that ?

&gt; Of late we have seen many issues with the historicals getting down quite often. The current deployment is in kubernetes. We understand that we have to get a strong hold on Apache Druid to define the configurations that is apt for the use cases. Currently they are all running with the default configurations.
a) What sort of issues are you facing? Logs from the historicals will be useful
b) Did you install Druid using Helm charts or Operators for <https://druid.apache.org/docs/latest/operations/kubernetes.html|K8s>?
c) What version of Druid are you on?

Hi Ruan Hui, may I know the version of your Hadoop cluster?

Thanks for the quick reply. sir.

The hadoop cluster is our internal version based on hadoop 2.8.5, It is fully compatible with the community 2.8.5 version.

<https://lists.apache.org/thread/q6l63glvnjntplh71568zg26s23hmym0> may be relevant

I think this is the problem. Really thanks for the reply. I wonder Is there anyway I can do to use index_hadoop task now ?

Any chance you can use <https://druid.apache.org/docs/latest/multi-stage-query/index.html|SQL-based ingestion> ?

&gt; I think this is the problem. Really thanks for the reply. I wonder Is there anyway I can do to use index_hadoop task now ?
Would it be possible for you to use the patch: <https://github.com/apache/druid/pull/13138>, if you need to run hadoop ingestion before 24.0.2 is available?

Thanks for your suggestion, <@U04CGQ1P68Z> But I’m afraid we won’t be able to use it. Anyway, thanks a lot.

Let me to try to backport it to the 24.0.1. Thanks for your suggestion.:grinning:

You can also remove the MSQ extension from your config and that would fix this error as well. MSQ extension is included by default in 24.0.

Hi <@U031A9D19NU>
I followed what you said, but it looks like that the error is still existing, do you have any other solutions to this problem ?
Thanks.

hmm. can you confirm from the logs that MSQ extension is not getting loaded?

Sorry for not reply in time, got stuck by the work.

Yes, I didn’t find MSQ in the logs. I guess it’s not getting loaded ?

i think you'll need to remove `druid-multi-stage-query` from the `druid.extensions.loadList` in your `common.runtime.properties` file

Hi <@U0344FW86DD> Thanks for the kind reply.
I did remove the MSQ from the list in common.runtime.properties, and I checked the log with multi-stage-query as the keyword to ensure that I removed, but it looks like this doesn’t solve the problem. So we downgraded to the version 0.23.0.:smile:
Thanks.

ok, good to hear you fixed it but sorry you had to downgrade! we'll fix this for future releases

It’s fine, never mind. Thanks all for the amazing work.:+1:

what is it that you are trying to do? Only one of the task replicas will commit to deep storage. so adding more replicas will double the reads but won’t double the ingestion throughput.

adding task replicas would increase Kinesis reads proportionally to the number of replicas you add

it only doubles if you double the replicas

<@U03BJAMQ4H5> I have 17 tasks for native kinesis ingestion, but am thinking of adding a replica for each task.

what is the reason for adding the replica?

if you are looking to boost ingestion throughput then the approach would be to increase number of tasks (and reshard kinesis if you already have 17 shards). Adding replicas will add fault tolerance but will not increase ingestion throughput

fault tolerance + query throughput for the live segments

For query throughput increasing the processing.numThread also helps . Default is 2 , you could try increasing to 4 . 


May I know what is the query pattern and concurrency.

Thank you all for your help. Follow question here if anyone knows.. would tranquility replication also double the reads of the kinesis stream?

I know it would double the number of druid tasks, but not sure if that means tranquility would read the kinesis stream twice or the druid tasks are getting data from tranquility that has only read the kinesis stream once.

AFAIK, it will double the reads, at last on kafka that's the way it's

but that 'should' not be a problem, unless kinesis is priced based on that (that could be the case, given aws way of doing biz)

however, here Rohit is saying that the read is only done once for tranquility:
```User decide the replication factor while using tranquility via a configuration.Tranquility will read the data once and will send the same data to multiple indexing tasks(equal to number of replication) hence ensuring that each replica gets exactly the same set of <http://data.In|data.In> case of realtime nodes ensuring all replicas get same set of data was not possible.```
<https://www.druidforum.org/t/doubts-regarding-replicaiton-policies-in-realtime-ndoes-and-tranquility/1767/2>

hm, I don't know for sure if this feature was merged back into apache druid

for what I understand, tranquility acts as man(manager)-in-the-middle of the ingestion, so it manager it's own tasks and how they should publish,
but I don't think this feature exists in the apache druid or imply

Hi everyone!

Druid Summit 2022 starts tomorrow and there is still time to register!

View the full agenda and register today, to catch one of the 30+ sessions (which are available, for free)!

Register here: <https://druidsummit.org/>

as far as i know all the sessions will be recorded :+1:

this is the code that validates datasource names <https://github.com/apache/druid/blob/8296123d895db7d06bc4517db5e767afb7862b83/core/src/main/java/org/apache/druid/common/utils/IdUtils.java#L45-L75>

Thanks <@U0344FW86DD>. This helps. Just to make sure, is it just for datasource names or also for the attribute names used in the data source schema? And are there also constraints on the data/values that get ingested in terms of maximum length permitted, characters allowed etc.?

that code is not used to validate column names or the actual values; they can be more flexible

string columns can contain any valid Unicode data

in terms of length — the absolute hard limit is 2GB per value, but that is definitely outside the expected design envelope and you would likely run into various issues if you had lots of values that long

Apologies for the delay and thanks for clarifying this <@U0344FW86DD>

I just made a PR to hopefully improve the auto-compaction status display and make it less confusing where it says "Fully compacted" but there are some segments skipped. If you were ever confused by this in the console please have a look at the change <https://github.com/apache/druid/pull/13523> and let me know what you think.

Yes it is.
<https://druid.apache.org/docs/latest/ingestion/native-batch-input-sources.html#sql-input-source>
Should be helpful.

As discussed on DM's. please create a bug for your usecase.

I would suggest exporting from SQL Server (or any source system) to files (such as Parquet on S3, or something like that) and then loading the resulting files into Druid

it's a nice, clean way to get data in from any source that supports some kind of export

Sure - do you have some specific questions?

My requirement is .. we are pulling data from mongo and store it in oracle. In oracle we are forming dimension, facts and reporting table (A typical datawarehouse). Can I use Druid in place of Oracle? Please help me on this..

Druid works best when data is denormalized and requires minimal joins

Screenshot 2022-12-08 at 15.41.14.png

<https://learn.imply.io/> helped me to understand Druid much better.
There is a 20 min intro to see the basics. Definitely worth it a try.

This is a forum for Apache Druid specific questions, your question would be better directed to your Imply rep.

Hello All!  A great opportunity to hear from <@U0421NQ3DQX> who will be presenting "Building applications with analytics capabilities using AWS services and Apache Druid" at AWS Community Day Pune 2022, 10 Dec 2022. :raised_hands: Registration : <http://www.acdpune.in|www.acdpune.in>

I think the type of retention you want is `dropBeforeByPeriod`

You would need to have a dop rule as well.
<https://druid.apache.org/docs/latest/operations/rule-configuration.html>

If the documentation is unclear do let us know. We are always looking for feedback on it.

Can you clarify on includeFuture flag with an example ? How it is used ?

So, I am working on different cubes with retention rules as loadByPeriod + dropForever. I am seeing segments loading correctly for some and some cubes are not following the rules.

Can it be the case data is loaded in cache and is unable to clear it or there is some other issue ?

So I generally look at the coordinator logs to figure out issues here and the kill task logs

Ohk, Is there anyway to reset cache and reload all segments according to retention rules ?

I donot know what you mean by reset cache. By cache I mean you are talking about the historical cache

Just kill the historical, clear the segment cache location manually and then start the historical again

Just a note that there may be segment unavailability while segments are downloaded from  deep storage. Hence, please take the necessary precautions. Maybe try it in a non prod cluster first

<@U030AQ8AR8E> <@U0344FW86DD> - any help will be appreciated as you guys were active in the previous thread

Could you mention the steps to reproduce the issue.

Hi <@U0421NQ3DQX>
I am not able to reproduce the issue in wikipedia datasource. :disappointed:
let me see if i can reproduce the issue

Hi <@U0421NQ3DQX>

after few hours of hustling finnaly got the steps to reproduce the issue

<https://github.com/apache/druid/issues/13540#issuecomment-1344450569>

i missed one of the steps, updated all step in the git comment

thanks for the repro instructions! it was very helpful

I wrote a comment on your issue with two suggested workarounds, until we can do a fix

Druid Operator Channel is now on Official Kubernetes Slack.
Link : <https://kubernetes.slack.com/archives/C04F4M6HT2L>

PR: <https://github.com/kubernetes/community/pull/6989>

for example, given this query to what pages given user ID 1107243346 *might* have visited
```select * from (
  SELECT 
    path, THETA_SKETCH_ESTIMATE(
      THETA_SKETCH_INTERSECT(DS_THETA(ds_mm), DS_THETA(1107243346))
    ) as path_visited
  FROM events
  where __time &gt;= ...
  group by 1 
) sub where sub.path_visited = 1```
can I trust that I can run another (heavy) query to check the information (to test if the user really visited some path) but I can skip any check if no results were found ?

I noticed that THETA_SKETCH is somewhat bigger than just HLL, but I could not figure out how to run this query with HLL

Also, side note: if I change the path_visited into HAVING clause the Calcite cannot make a plan for it

Great question! My guess will be "theta does not provide a guarantee eater way" but I will see what someone more knowledgeable says

<https://chat.openai.com/chat> think it can return false negatives, but at first it get bloom filter wrong, so do not trust it

did some testing, it does :confused:

Druid bloom-filter exists only at query time, it's not possible to use on roll-ups ? Doind on query-time scanning millions of events I think it's better to just run the real query

one approach is to use THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS. This should get you upper and lower bound of the estimate

theta sketch accuracy is here <https://datasketches.apache.org/docs/Theta/ThetaSketchSetOpsAccuracy.html>

if you expect the estimate to be 1 nad the bounds are 0 and 2 for instance then the sketch could get you false negatives

ds_mm was created by 'mm' (user-id)

```{
          "type": "thetaSketch",
          "name": "ds_mm",
          "fieldName": "mm",
          "size": 16384,
          "shouldFinalize": true,
          "isInputThetaSketch": false,
          "errorBoundsStdDev": null
        }```
I tried some values, if I choose users from a small combination, it works, but I choose a userid from a big pop, It fails

I was not able to create a working THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS query

if I try just select THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(ds_mm , 0) it says the ds_mm is not group'ed by
If i add to group by, it fails as COMPLEX grouping type is not enabled by default

if I try APPROX_COUNT_DISTINCT_DS_THETA(THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(ds_mm , 0)) calcite fails to create a plan

for my use case, this is kinda fixed right now (see <https://apachedruidworkspace.slack.com/archives/C0303FDCZEZ/p1670621113409749?thread_ts=1670621045.271699&amp;cid=C0303FDCZEZ>)
I still left this datasource enabled becase it's really fast, I think I will add some dashboards with this data (maybe make it more granullar than DAY)


I had this use-case where I need to check if a use is still active (eg: use the app in the last N days) before sending an external event

I set up a redis where I can keep track of last iteration for a few million users, but when I get a 'cache-miss' I don't know if the user is not active or if the cache was corrupted (I have a checksum bc I use another redis where i keep the most frequent and small values in form of a int, eg: "Samsung" is always encoded to 222, but redis can crash, etc) so I still need to query druid a few times per second, most those requests would never return a row bc the user was inactive for a month

it was taking so much CPU (because it was converting all the strings to INT then making the matching) that I was looking for another ways or even another database for this