Apache Druid

I guess it depends on your particular needs.  Usually you'd do rollup when loading to druid, so you'll have aggregated data, and not all the individual records from the original source.

Yes exactly, the problem is 90% of my queries are count or sum grouped by day, week, month over differents time windows and the other 10% are just select individual records from historical data :/

You can still do that - I'd test for query performances, since you won't have rollup at ingestion time.  You should still be able to get fast answers, especially with good segment sizes, etc, and if approximations are acceptable (for the counts - GROUP BY will give exact results, but can take longer.  Counts are approximate by default, but you can make them exact, but again they'll take longer).

I'm not sure, but maybe something with a CASE statement and FILTER clauses?  The case might not be necessary, maybe just FILTERs for each aggregation and timezone, getting different columns (for each timezone).

Hi Rishi, can you share some pseudo code on your logic ? Thanks !

So I have a table which contains data in UTC, each row has a column based on which it identifies the timezone for that particular row, so what I need is to be able to query each row in its respective timezone for LastNDays

commented in <#C0303FDCZEZ|> - <https://apachedruidworkspace.slack.com/archives/C0303FDCZEZ/p1673380301176109>

Hi <@U037V6NT7FF>,

You are using a CASE statement in the grouping key ... it sounds like Druid SQL doesn't like that.

Since Machine_Date is the only *real* field being evaluated in that  grouping expression it makes sense that it is allowed as the GROUP BY column though, and it looks like it should produce the correct result ... does it not?

If I were writing this query, then if the underlying table 'metadata' isn't too large, what I would probable first try doing for readability is to put the entire case expression in a CTE, for example:

```With tmp as (
 select case when TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata') &lt; TIME_FORMAT(CURRENT_TIMESTAMP,NULL,'Asia/Kolkata')  
              then TIME_FORMAT((TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata')),'yyyy-MM-dd','Asia/Kolkata') 
              else 'null' end) as a1,
        Machine_count
   From metadata
)
 select a1, sum(Machine_count)
   From tmp
  group by a1```
If that does not work, and/or your initial query is not producing the correct result, then please explain a bit more what you are trying to do ... and also provide a count(*) and count(distinct Machine_Date) for the table.

Thanks.  John

I just re-read your problem statement ... you cannot change the phrase "group by 2"?  If so, then the above query could be adjusted as:

```With tmp as (
 select case when TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata') &lt; TIME_FORMAT(CURRENT_TIMESTAMP,NULL,'Asia/Kolkata')  
              then TIME_FORMAT((TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata')),'yyyy-MM-dd','Asia/Kolkata') 
              else 'null' end) as a1,
        Machine_count
   From metadata
)
 select sum(Machine_count), a1
   From tmp
  group by 2```
you could also write the CTE as an inline table:

``` select sum(Machine_count), a1
   From (
         select case when TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata') &lt; TIME_FORMAT(CURRENT_TIMESTAMP,NULL,'Asia/Kolkata')  
                     then TIME_FORMAT((TIME_PARSE("Machine_Date", NULL, 'Asia/Kolkata')),'yyyy-MM-dd','Asia/Kolkata') 
                     else 'null' end) as a1,
                Machine_count
           From metadata
        )
  group by 2```
not sure if any of this will work with whatever SQL syntax restrictions you are working with ...

actually it seems that if you substitute the `2` with the full case expression it works. Not sure what is up. This is a bug as far as I can see (probably in calcite with how it handles the `2` shortcut)

I filed: <https://github.com/apache/druid/issues/13626>

Thank you all for replying
I have one more issue with current time stamp
This is normal date column which is working fine
SELECT SUM("final_price"),
(case when "final_price"&gt;50 then TIME_PARSE("date_purchased",null,'Asia/Kolkata') else 0 end) as a1
FROM ecommerce GROUP BY 2
This is current time column which is not working
is there any issue with CURRENT_TIMESTAMP with case
My query which is not working
SELECT SUM("final_price"),
(case when "final_price"&gt;50 then TIME_FORMAT(CURRENT_TIMESTAMP,'dd-MM-yyyy','Asia/Kolkata') else 0 end) as a1
FROM ecommerce GROUP BY 2
Let me know if i am doing anything wrong.
 Group by 2 is added by some other lib i am using cube so group by formula i can't put it

Druid users in APAC……if you want issues looked at, want to know what is new in druid or just want to talk about use cases and best practices….please come to my druid drop-in next week. <https://www.meetup.com/bangalore-druid-meetup-group/events/290191715/>

May your 2023 be filled with peace, joy and health. Happy 2023!

Not sure, but maybe <https://docs.imply.io/latest/druid/querying/nested-columns/|nested columns> and <https://docs.imply.io/latest/druid/tutorials/tutorial-unnest-datasource/#unnest-nested_data2|unnest> would help.  There are also new <https://druid.apache.org/docs/latest/querying/sql-json-functions.html|SQL operators for json data>.  But I'm not sure how that all fits together.

if the data is already ingested using arrays, usually you just need to group by it
this series is very good:
<https://blog.hellmar-becker.de/2021/10/03/multivalue-dimensions-in-apache-druid-part-4/>

Be sure to check part 1..3

[sorry, I missed the `json`in the question, I need more coffee]

<@U04GEHS4TRU> did you get any solution for this?
Thinking of exploding array of json in multiple rows using <https://github.com/apache/druid/pull/13268|UNNEST> and then using <https://druid.apache.org/docs/latest/querying/sql-json-functions.html|SQL JSON Functions> But not sure if this would work

I would say it would be another 10 days tops.

It's here! Check the website. Official announcement coming as soon as it can be written

If you need someone to take a look at your druid cluster or are trying out druid and want answers to questions and hands on help, please book time in the POC clinic <https://calendly.com/vijay-narayanan/druid-poc-clinic-by-imply>

The first column __time is picking up '1970-01-01'

which is the timestamp field upon which `__time` is based on?

There is no such column , it got the name automatically

you need one preferably of ISO-8601 format

Is there any way to populate the __time column automatically without referencing any columns?

<https://druid.apache.org/docs/latest/ingestion/ingestion-spec.html#timestampspec>

yeah , using that for now. Which we have to hardcode everytime

What do you want to set the `__time` column to? Usually it maps to a column in your data

current time is ambiguous in a distributed system, so I don't think there's a way to tell it to set to current system time at ingestion.  You might have to inject that into the data somehow just before loading it, if you can.

Hi Druids!

The Apache Druid team wishes you all a very happy new year!
We are very excited to start the year with the release of Apache Druid 25.0.0.
Druid is a high-performance analytics data store for event-driven data.

Apache Druid 25.0.0 contains over 300 new features, bug fixes, performance enhancements, documentation improvements, and additional test coverage from 51 contributors. Some of the major highlights of this release are:
• The new multi-stage query (MSQ) engine is now production ready
• Nested column now supports ingestion from Avro, Parquet, ORC and other formats
• Native ingestion on K8s without using middle managers
• Front-coded string dictionary compression which significantly reduces segment footprint
• New and improved Druid quickstart experience
• Overlord improvements to support high volumes of real-time ingestion
Source and binary distributions can be downloaded from:
<https://druid.apache.org/downloads.html>

Release notes are at:
<https://github.com/apache/druid/releases/tag/druid-25.0.0>

Thanks to all the contributors to this release.

Stay tuned for many more exciting Druid capabilities in 2023!

Thank you to all the contributors. Once again, thank you <@U0318CX1NH4> for shepherding the release.

you could also concat and do the sketch in the ingest. Doing this ingest is useful only if you enable rollup and are seeing rollup. If there is no rollup then I would ingest the concatenated column and do the sketch in the in the query

Do you mean increasing the setting without adding more resources? If so, then this would be expected.
Is the server a single server deployment? or is it just for the Middle Manager?
If this is just the Middle Manager, your worker.capacity should be set to 15 max because each worker will use a core and you want to leave one for management overhead.

If this is a single node deployment, you'll need to consider the cores dedicated to each process. The broker, middle manager and historicals are the main consumers of CPU. Historical thread count (`druid.processing.numThreads`), Broker activity for queries and MM worker capacity (`druid.worker.capacity`) will drive CPU utilization, so you'll need to keep those parameters in balance to not over extend the 16 CPUs that you have and you'll need to leave some processing capacity for the other services (coordinator, overlord, ZK, router).

The index_parallel task has a log that looks good, it ends with:

`2023-01-06T00:57:39,784 INFO [task-runner-0-priority-0] org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexSupervisorTask - Published [4] segments`
2023-01-06T00:57:39,803 INFO [task-runner-0-priority-0] org.apache.druid.indexing.worker.executor.ExecutorLifecycle - Task completed with status: {
  "id" : "index_parallel_longterm_dmaacdcd_2023-01-06T00:27:42.588Z",
  "status" : "SUCCESS",
  "duration" : 1791222,
  "errorMsg" : null,
  "location" : {
    "host" : null,
    "port" : -1,
    "tlsPort" : -1
  }
}
2023-01-06T00:57:39,807 INFO [main] org.apache.druid.java.util.common.lifecycle.Lifecycle - Stopping lifecycle [module] stage [ANNOUNCEMENTS]
2023-01-06T00:57:39,810 INFO [main] org.apache.druid.java.util.common.lifecycle.Lifecycle - Stopping lifecycle [module] stage [SERVER]

(followed by all cleaning and closisng stuff)

Could you please search the coordinator logs for occurrences of this datasource's name?
Also, did you previously change the retention rules for this datasource?

Any results for `select * from sys.segments where datasource = &lt;new datasource&gt;` ?

Here is the last message:
2023-01-06T00:58:51.765774887Z stdout F 2023-01-06T00:58:51,747 INFO [Coordinator-Exec--0] org.apache.druid.server.coordinator.KillStalePendingSegments - Killed [0] pendingSegments created until [2023-01-04T20:58:09.414Z] for dataSource[longterm]

&gt; what am I looking for in the logs ?
Any occurrence with the name of the datasource

yes there's the whole indexation process logged

The coordinator loads segment metadata from the metadata store.
Could you please try these sql queries on your metadata store (derby / mysql etc)?
`select count(*) from druid_pendingSegments where datasource = &lt;new datasource&gt;`
`select count(*) from druid_segments where datasource = &lt;new datasource&gt;`

&gt; select count(*) from druid_pendingSegments where datasource = 'longterm';
 count
-------
     0

&gt; select count(*) from druid_segments where datasource = 'longterm';
 count
-------
     4

any pre-existing rules for the datasource which may be causing it to be not loaded?
```select * from druid_rules where dataSource = 'longterm';```

On the same note - does clicking `Show unused` in the Datasources tab show anything

The default rule was dropforever, however I changed that thsi morning and created a rule for the datasource, it doesn't look like it helped

I suppose that since they were actually all implicitely dropped by the dropforever I need to mark them as used so that they're evaluated again by the load rule correct ?

Thanks a lot for your help and the pointers, it's alright now !

<https://druid.apache.org/docs/latest/operations/rolling-updates.html>

Perfect, you can just upgrade following instructions shared above

yeah. You can tell the scripts what services do you want to launch on that node. In cluster mode, you will run the script with different service arguments on different nodes

fwik, normally a high cardinality field on which you want to do operations like unique/distincts, set operations etc. are made a sketch

something like a bidRequest.id is a good candidate to ingest as a sketch

Is it possible to get theta sketch of id under imp in Druid

I am able to reach till imp but after that it’s giving null

this should give you what you need <https://druid.apache.org/docs/latest/configuration/logging.html>

If you are using the `supervise` configs, the rotation of these logs is controlled with `svlogd` <http://smarden.org/runit/svlogd.8.html#sect6>
You can modify this config by creating a custom `svlogd.conf` file containing configuration parameters like:
```s10000000
n1
N5
t604800```
(see man page for the meanings)
And run the services for startup like
```bin/supervise -c conf/supervise/quickstart.conf --svlogd-conf syslogd.conf```

oops - last line should be `svlogd.conf` not `syslogd.conf`
```bin/supervise -c conf/supervise/quickstart.conf --svlogd-conf svlogd.conf```

Is it possible to wrtie log hourly instead of a day  (by changing in the config  log4j2.xml) ?

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

yeah. this is a known issue - <https://github.com/apache/druid/issues/13626>

<@U04ACMF4JSE>, if you have to get this to work ...

```with tmp as (select CURRENT_TIMESTAMP val)
select sum(added) ,
case when flags = 'MB' then tmp.val else null end grp
from "wikipedia" CROSS JOIN tmp
group by 2```
it's a bit of a hack, but it is valid SQL and it works for me.

Getting no data returned from sampler error when reading data from s3