Apache Druid

Druid Sql Api might be helpful for you.

References:
<https://druid.apache.org/docs/latest/querying/sql-api.html#submit-a-query>

I hadn't realised this API supported parameterised queries like that. This would definitely be the most sustainable solution. Thank you :slightly_smiling_face:

seconding the recommendation; from C# I think your best bet is use the HTTP API directly. it's pretty straightforward

can you try below?
```SELECT 
  user_id,
  SUM(visits) as sum_visits,
  COUNT(DISTINCT user_id) filter (where visits&gt;0) AS total_rows
FROM "visits_tbl"
GROUP BY user_id
ORDER BY sum_visits DESC```
row number does not exist as of today

If you want to compute a RANK() without Window functions, the only way I know how to do it is to do self-cross-join on the user visit counts aggregate ... then you can create user and overall totals as CTEs, and finally compute the percent_rank:

```with user_total as (
 select user_id, 
        count(*) as user_visits
   from visits_tbl
  group by user_id
),
overall_total as (
 select sum(user_visits) overall_visits,
        count(distinct user_id) as overall_users
   from user_total
),
user_rank as (
 select a.user_id, a.user_visits,
        sum(case when b.user_visits &gt; a.user_visits then 1 else 0 end) + 1 as user_rank
   from user_total a 
  cross join user_total b
  group by a.user_id, a.user_visits
 )
select a.user_id, a.user_visits, c.overall_visits, a.user_rank,
       (1.0 - a.user_rank) / (c.overall_users - 1) pct_rank
  from user_rank a 
 cross join overall_total c
 group by a.user_id, a.user_visits, a.user_rank, c.overall_visits, c.overall_users```
You could probably tighten this up a bit ... I left it verbose to show some of the work.

If that doesn't do it, let us know what is still missing.

Thanks.  John

Awesome <@U04C4593VDY> that works perfectly thank you! It would be a critical feature for us so have saved me a bunch of time, appreciate it :pray:

I'll also note that our next release is going to have window functions available at least as an experimental feature (if not better-than-experimental)

Including `PERCENT_RANK` :slightly_smiling_face:

sounds like you are setting deep storage to local disk. This own't work with a clutser. You need a network storage (s3,hdfs,nfs etc)

I *highly* recommend you to try to use minio instead, even if you just run with just one node of minio

<https://min.io/> don't be afraid bc it's says "Kubernetes-Native", it's pretty simple to run locally/single node

if i understand correctly— you'd want to do the replace with a gran of `day`. you can mix granularities in the same datasource, so this will work, but IMO it's easier to manage if you stick to a single granularity

<https://druid.apache.org/docs/latest/multi-stage-query/reference.html#replace> is the thing for it

and `OVERWRITE WHERE __time &gt;= TIMESTAMP '2021-12-01 00:00:00' AND __time &lt; TIMESTAMP '2021-12-10 00:00:00'`

Hello Gian and thank you for your answer!
this is for queries?
we want to delete the old data of 10 days of the segment and replace it with the new data.
we should change gran to day for this month, so to have 31 segments than 1, do the changes and then compact again to monthly?

If you use the new MSQ REPLACE/OVERWRITE/SELECT statement as Gian referenced, you should be able to overlay the new DAY segments on top of the existing MONTH segment ...

... the DAY segments will "overshadow" the MONTH segment for query purposes so you will get consistent query results across that month ...

... then later on when you run compaction, all of the DAY segments should merge into the MONTH segment retaining the latest ingested data for each day.

I had no idea there was an OVERWRITE where clause. This is a feature you don't even get at all in native batch isn't it?

you can do it in native batch with `appendToExisting: false`

i find the SQL version easier to understand though :slightly_smiling_face:

I knew you could append. I didn't realize you could filter out certain records from eg. a month segment, presumably replacing the month minus some days, then also insert some days and then use them over top of each other, then switch over cleanly from the old month.

You can do this, but you have to replace the entire time chunk with a select statement that selects all the data from that time chunk, applying the mods you want in the where condition and the select list.

If you want to post your SQL here we can help you to adjust it to do what you want.

You can also do this via Native, it's just not as pretty ... :wink:

Hello again,
we have upload segments with following json file.
if now try to upload segment in the same datasource with "segmentGranularity":  "day" we getting error because new data overlapping timestamps between the segments.
We should contain and something else in json?

{
  "type": "index_parallel",
  "spec": {
    "ioConfig": {
      "type": "index_parallel",
      "inputSource": {
        "type": "hdfs",
        "paths": "folder_path"
      },
      "inputFormat": {
        "type": "parquet"
      },
      "appendToExisting": true
    },
    "tuningConfig": {
      "type": "index_parallel",
      "partitionsSpec": {
        "type": "dynamic"
      },
      "logParseExceptions": true
    },
    "dataSchema": {
      "timestampSpec": {
        "column": "__time",
        "format": "auto"
      },
      "dimensionsSpec": {
        "dimensions": [
		...
		...
		...
	]
      },
      "granularitySpec": {
        "queryGranularity": "none",
        "rollup": false,
        "segmentGranularity": "month"
      },
      "dataSource": "datasource_name"
    }
  }
}

Hi Giwrgros,

... if you are replacing data here then per Gian's note you should set appendToExisting to false ... this should allow mixed segment granularity (e.g. overlay DAY on top of MONTH)

... if you are inserting new data, but it is mid-month and you have already compacted the current month's data to MONTH granularity, then you may have to ingest using the same granularity as the existing segment, i.e. MONTH ...

Mixed segment granularity means that the datasource overall can have mixed granularity in it over time ... I don't know that it allows mixed for inserting (appending) new data through ingestion ... hopefully someone else can confirm.

Thanks.  John

&gt; Mixed segment granularity means that the datasource overall can have mixed granularity in it over time ... I don't know that it allows mixed for inserting (appending) new data through ingestion ... hopefully someone else can confirm.
appending does require using a segment granularity that is aligned with the existing segment granularities (whatever they may be)

replacing doesn't require that; you can use any granularity you want for the new data. It will overshadow the older segments, perhaps partially (for example: if you have a MONTH segment and you insert a single DAY that partially covers it, then Druid will use the MONTH segment for all other days, but ignore that one day; instead, it'll use the new DAY segment you inserted.)

i do feel it's generally simpler to keep a consistent segment granularity for a given datasource, so i do encourage that. however if you keep the above :point_up:  in mind you can definitely mix them if you like

Hello, thank both.
Gian i don't understand how to insert a single DAY that partially covers MONTH segment. If i try to insert in same datasource with MONTH segments, a DAY segment, i take the error because new data overlapping timestamps between the segments.
any ideas?

CleanShot 2023-03-09 at 16.44.43@2x.png

Using MSQE, `REPLACE INTO "trips_month" OVERWRITE WHERE "__time" &gt;= TIMESTAMP '2013-08-02 00:00:00' and "__time"&lt; TIMESTAMP '2013-08-03 00:00:00'`, I am able to insert 1 DAY partitioned segment into a DS with `MONTH` segment granularity:

:point_up_2:I partitioned 60M rows of NYC taxi trip data (CSV) [ available in Druid 25.0's MSQE datasets] by MONTH first and then did a `REPLACE` by `DAY`

hmm i have apache-druid-0.23.0. MSQE is available for this version?

when i add druid-multi-stage-query to configuration, then node crashing with log org.hibernate.validator.internal.util.Version - HV000001: Hibernate Validator 5.2.5.Final.

We have all data in cluster with version 0.23.0.
Before try to update druid version, is there other way to achieve this?
I think it's simple what I want (replace specific days that are in a monthly segment) but I can't find a solution.

image.png

So i have upload and DAY Segment with appendToExisting to false.
Now i want the day in MONTH segment to be repalced from DAY segment.
Any ideas? With compact?

If you are not finding a good way to do this, given that it is old data, what you can do is recompact that month into daily, then replace a few days. The auto compactor will eventually recompact it to month.

If you Native ingest with appendtoExisting=false it should allow you to ingest with DAY segment granularity over an existing MONTH segment.   Does that specifically not work?  If not, what type of error are you seeing?

Hello John, this way working and i have the image that i attached above.
Now i want the DAY segment to replace the specific day from the MONTH Segment and after that to have again a MONTH Segment with updated data!

Oh, from your last screenshot it looks like you already have it.

Yes, compaction should merge the two together.

If you notice the timestamp (Version) of your DAY segment is newer than the MONTH segment ... so if you were to query across this month you would see all of the data across the month correctly, the data for that particular day would come from the DAY segment, and the rest of the data for the month would come from the MONTH segment.

This is essentially "committed read" read consistency behavior from perspective of the ANSI SQL relational world.

When you compact the time chunk, compaction will generate resulting segments with the *largest* segment granularity in the group that is being compacted, so in this case it will product a single MONTH segment that contains the equivalent of having read (with read consistency) all of the data for that month.

How does it know not to take data from the days in the month segment that were "overshadowed"? Segments only have a start and end date and I would assume it would take data in that day range from both segments.

The version (timestamp) of each segment tells you the pecking order ... newer timestamp overshadows older segment

Once it sees overshadowing, then it looks at the start and end points.   The segment time range is inclusive of the lower bound and exclusive of the upper bound.

So in your first example:
• 12/01 &lt;= t &lt; 12/10 data is read from the DAY segments you've created
• 12/10 &lt;= t &lt; 01/01 data is read from the portion of the MONTH segment that is not overshadowed.
... and in the example in your screenshot:
• 07/01 &lt;= t &lt; 07/10 data is read from the MONTH segment (dated 3/6)
• 07/10 &lt;= t &lt; 07/11 data is read from the DAY segment (dated 3/9)
• 07/11 &lt;= t &lt; 08/01 data is read from the MONTH segment (dated 3/6)

I didn't realize the version handling was so fine grained, that it would take from the newest version for ranges while keeping in mind which sub ranges were overshadowed by a newer version.

Then I guess the only real difference appendToExisting does is whether it uses the old version or a new one for the segment it is writing.

Gian and John, we did it!
Thank you for your help and for the discussion.
If I need anything, I will come back.
Thanks again.

Overshadowing -- my understanding is that you can have many layers on top of each other, and theoretically with uneven overlap too (e.g. weekly segments)

===

"appendToExisting = true" SHOULD NOT WORK with mixed segments, i.e. you cannot append with DAY granularity into a timechunk that has MONTH segments in it ... you should get an error.

So for append ingestion purposes you have to ingest using a consistent segment granularity.   This means if you are changing granularity for older time chunks during compaction, make sure that your new ingestion won't try to insert into those time chunks (e.g. as in late arrival data).

where are you reading your data from, what format is it in, &amp; are you using batch or stream ingest?

Can you post a screenshot of where you are setting the unreadable data?

The ingestion is of type index parallel and here's copy paste of the ioConfig. It can "read" the file, but its definitely not in a human-readable format. (The file itself is fine for sure). I also tried changing the suffix of the file to just json and hoping that druid would recognize the compression. Same thing happens with zst compressed orc file (tried suffixing the file with zst and orc).
"type": "index_parallel",
      "inputSource": {
        "type": "s3",
        "objects": [
          {
            "bucket": "bucket",
            "path": "path/to/file.json.snappy"
          }
        ]
      },
      "inputFormat": {
        "type": "json",
        "compression": "snappy"
      }

for batch ingest, Druid uses the suffix of the file to determine how to decompress

for snappy framing format (<https://github.com/google/snappy/blob/main/framing_format.txt>) Druid expects `.sz` so try naming it `<http://file.json.sz|file.json.sz>`

note that there are multiple snappy compression formats out there. Druid supports just this one :point_up:  so if it doesn't work for you, it's possible you are using one of the other formats. The most common are unframed (raw) compression, and the snappy frame format defined by Hadoop, neither of which Druid supports reading directly

Thanks for the info. I'll try tomorrow and hopefully get it to work.

For file.orc.zst I get
Error: Failed to sample data: java.io.IOException: Decompression error: Unknown frame descriptor
And for <http://file.json.sz|file.json.sz> I get
Error: Failed to sample data: java.io.IOException: Not a framed Snappy stream
So I guess the snappy compression is one of the not supported group. However, with the zstd I'd like the guess the same, but this error is different. Any ideas?

The file is for sure zst compressed, but there are maybe also some subsets of zst that druid supports?

Same compression is used for CSV and we can successfully read those. I've checked and I did include orc-extension for Druid

hmm i'm not super familiar with zst so i am not sure if there are multiple formats

i wonder if it's a zst problem or orc problem

<https://github.com/luben/zstd-jni/issues/103> seems to indicate it's a zst problem

<https://github.com/luben/zstd-jni/> is what we're using btw

Hi Jamie, this sounds cool! I will try poking around in your branch to see if I can spot something. The error message makes it sound like the module is not loaded correctly

You might also get better help posting this in <#C030CMF6B70|>

Thanks, I will try that. The have solved that error, it was caused by the capitalisation on rabbitStream. I've got a new error to work through:
```2023-03-08T16:21:37,693 WARN [qtp107279519-181] org.eclipse.jetty.server.HttpChannel - /druid/indexer/v1/task
java.lang.NoClassDefFoundError: com/rabbitmq/stream/MessageHandler
        at java.lang.ClassLoader.defineClass1(Native Method) ~[?:?]
        at java.lang.ClassLoader.defineClass(ClassLoader.java:1017) ~[?:?]```

when you copy the dependencies over you might want to use the pull-deps tool to do that - <https://druid.apache.org/docs/latest/operations/pull-deps.html>

The error makes me think that the extension needs some rabbitmq dependency that wasn't copied over to the class path

Alternatively, you can update the dist profile in your branch so that when you build druid, it bundles the extension for you.

I was having trouble getting the druid tag to build, hence the alternate strategy of moving the jars over. I'm only copying over the one jar, I assumed at some point it would complain about missing rabbitmq among others

Add the extension to this list here <https://github.com/Agilicus/druid/blob/feature-add-rabbit-stream-indexing-service/distribution/pom.xml#L262>
```&lt;argument&gt;-c&lt;/argument&gt;              &lt;argument&gt;org.apache.druid.extensions.contrib:druid-rabbit-indexing-service&lt;/argument&gt;```
Then when you build druid with
```mvn -T1.0C -DskipTests -Dforbiddenapis.skip=true -Dcheckstyle.skip=true -Dpmd.skip=true -Dmaven.javadoc.skip=true -Danimal.sniffer.skip=true -Denforcer.skip=true -Dspotbugs.skip=true clean install -Pdist```
It should correctly add the extension to the bundle that is created at `$CODEBASE/distribution/target/apache-druid-26.0.0-SNAPSHOT-bin.tar.gz`

I made one small change to the extension's pom here to reference the druid version 26.0.0-SNAPSHOT instead of 25.0.0 <https://github.com/Agilicus/druid/blob/feature-add-rabbit-stream-indexing-service/extensions-contrib/rabbit-stream-indexing-service/pom.xml#L32>

hopefully this unblocks your testing of the extension!

if it helps, here's all the dependencies it put in the rabbitmq extension directory
```apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/netty-common-4.1.86.Final.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/netty-transport-4.1.86.Final.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/druid-rabbit-indexing-service-26.0.0-SNAPSHOT.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/slf4j-api-1.7.36.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/snappy-java-1.1.8.4.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/lz4-java-1.8.0.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/netty-codec-4.1.86.Final.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/zstd-jni-1.5.2-3.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/stream-client-0.9.0.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/netty-buffer-4.1.86.Final.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/netty-transport-native-unix-common-4.1.86.Final.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/netty-handler-4.1.86.Final.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/netty-resolver-4.1.86.Final.jar
apache-druid-26.0.0-SNAPSHOT/extensions/druid-rabbit-indexing-service/proton-j-0.34.0.jar```


Okay thanks, that helps! We'll see what it does next

so k1024 is the column that has the sketch?

if the column is already a quantiles sketch then you can use it in the ds_histogram directly without having to sketch again

Screen Shot 2023-03-08 at 16.18.08.png

Yes, when running
```SELECT "ORDINAL_POSITION", "COLUMN_NAME", "IS_NULLABLE", "DATA_TYPE", "JDBC_TYPE"
FROM INFORMATION_SCHEMA.COLUMNS
where "COLUMN_NAME" = 'k1024'```
I got the attached result

But running the `ds_histogram` on the column like use suggesting:
```SELECT 
  DS_HISTOGRAM("k1024",10,20,30) AS median_s
FROM "wikipedia_new"```
Gives me the following error:
```Error: SQL query is unsupported

Query not supported. Please check Broker logs for additional details. SQL was: SELECT DS_HISTOGRAM("k1024",10,20,30) AS median_s FROM "wikipedia_new"

org.apache.calcite.plan.RelOptPlanner$CannotPlanException```

looks like I was not correct I tried the following
```select DS_HISTOGRAM(DS_QUANTILES_SKETCH("sum_added"),20,30,40) from "wikipedia_quantilesketch"```
and got
```[14386.0,1152.0,1276.0,7619.0]```
sum_added is a quantilesdoublesketch created in the druid ingestion

Yes, fix me if I am mistaken:
Using `DS_QUANTILES_SKETCH` causes druid to recalculate the sketch and not use the pre-calculated sketch(by the plan I attached)

actually...I did a rollup at ingestion and hence the sum_added has only the sketch not the actual numerical value. So druid cannot recalculate the sktech. What does happen is a sktech merge

Cool, thank you for the answer.
Is it possible to see how the query runs under the hood and see that it does sketch merge?

because from the point of view of the query plan I use it seems like the plan for using `sum_added` and `addaed`  gives the same plan

quick note here, if you want to compute a median (as the `AS` in on of your queries implied) you want to use `APPROX_QUANTILE_DS(expr, probability, [k])` with the second argument as 0.5, the first argument could be a sketch column or a numeric column (see <https://druid.apache.org/docs/latest/querying/sql-aggregations.html>)

&gt; Cool, thank you for the answer.
&gt; Is it possible to see how the query runs under the hood and see that it does sketch merge?
:point_up:  for this, if you see a sketch type in `aggregations` with `fieldName` set to the name of a sketch column, then it is doing a sketch merge

<@U030W50CNAC>, you right i forgot to delete the `AS`

<@U0344FW86DD> thank you for the answer, but I do not understand where I can see if it doing `sketch merge`  because from the plan I cant see that

it's doing one if `k1024` is a sketch field (the JSON won't tell you that; but if it is, then you would get a sketch merge)

Thank you very much for the answer <@U0344FW86DD>

It could be nice to see the actual plan of what Druid is doing under the hood.
It could help with understanding it better and for optimization.

i agree!! hope to deliver something like that in the future :slightly_smiling_face:

Hi Everyone, We are looking for good folks who has been Apache Druid Contributor to join our Apache Druid Team in Confluent. Interested folks can DM me, if they are interested.

You make a good case for it. I would worry that such a change would break existing ingestions that rely on defaults in the same subtle way. I suggest that you add the request to <https://github.com/apache/druid/issues|Apache Druid github issues> and the developer/user discussion can occur there as well.

<@U030C8H59T8> what do you think about this? What does the JSON indexer do?

today the JSON column doesn't support multi value strings at all, it currently breaks arrays out into separate elements

this is changing very soon though with JSON indexer supporting true array columns

which will also be a top level replacement for using multi value strings

<https://github.com/apache/druid/pull/13803|https://github.com/apache/druid/pull/13803>

these will just hold arrays directly as they are

I want to move towards a world where people only use multi value dimensions when they truly never care about the array, and use arrays otherwise since they are so much more sane to reason about

that makes sense, especially if we couple it with data like `["a", "b", "c"]` being defaulted to array (like in the data loader wizard) and people can explicitly ask for it to be a MV dimension

the nested column arrays will have indexes for both array_contains (and probably overlap) style operations, as well as equality when searching for specific arrays

all this said, I do agree the current defaults for multi value handling should probably be array, though we probably need to be careful thinking about if there are any implications for changing the default

which mode was used is also not persisted in the segment, so reingest doesn't necessarily use the same handling

which is another argument in favor of making plain array be the default

i would be good with making plain arrays the default. i agree it makes more sense as a default

for SQL-based ingestion it'd be also nice to have functions that sort and dedupe arrays

like `ARRAY_SORT(foo)` if you want `foo` ingested as a sorted array

yeah, i agree about the sort and set operations so that it is possible to do most of the things mvds can do with arrays

id love to get the native `map`,`fold`,`filter` etc array expressions wired up in sql somehow too so that then really could do anything with them

<@U04SK99SJ6S> are you interested in raising a PR? if so, link it here and we can continue the discussion there

<@U0344FW86DD> I'll look into it, but I'm not familiar with the codebase and contribution guidelines yet, so it might take me a while. If someone steals the spotlight in the meantime i wouldnt mind

You can see/update the retention rules in the Druid Console in the DataSource view in the Retention column:

The APIs call will result in changes in that view. The coordinator runs every minute by default and applies the current retention rules for each datasource, instructing the historicals to load or unload segments. Some coordinator level dynamic configs also control aspects of how this operates and are <https://druid.apache.org/docs/latest/configuration/index.html#dynamic-configuration|documented here.>

Hey Sergiu! Thanks for your reply! so as far as I got from you, when we apply these retention rules, it has nothing to do with coordinator configs or change nothing there but instead it is gonna effect on data sources rather than setting them like coordinator config, right?

So in coordinator config, we can set how it should operate as for frequency and so on for retention policies we have set?

We do not want to set the retention rules for each data sources manually in UI but rather create a Python file which makes API requests to set those retention for each data sources and run the Python file via Jenkins pipeline. Do you think does this make sense? It is just because we want to have 1 common source where we can just change retention policies in case we need to change in the future.

And an Airflow DAG runs periodically to apply the kill tasks. Are these common ways to achieve in our use cases?

<@U04Q25Z05U0> Are you planning on changing retention rules frequently?

Also, you don't need "manually" trigger kill tasks. This can also be configured to just run periodically within Druid, the basic coordinator config parameters that control this are:
```druid.coordinator.kill.on = true #turn on periodic kill tasks
druid.coordinator.kill.period    # how often to run kill tasks defaults to P1D```

You can definitely use the Retention Rules API to set the rules from Python. I don't think this is common since retention rules are rarely changed but the APIs are there to be used so, if that works for you, I don't see any reason not to do it that way.

Hey <@U030K4UM3H7>! thank you so much really for the replies once again :slightly_smiling_face: Well actually we dont plan changes to be made frequently but just in case if we want to do it , we wanted to have a automated way having a common file for all data sources. I dont know if this is really usual way to do it

Maybe I misunderstood, but if it is meant to be a common retention rule for all datasources, keep in mind that the `_default` rule applies to any data sources that do not have rules of their own. So this might be an easier approach.

Hi <@U030K4UM3H7> sorry for my late reply. Thank you for your help! :slightly_smiling_face:

what clustering (secondary partitioning) are you using for that data source?

i.e. if you look at one of the segments in the console segments view what do you see under `Shard type` ?

oh boy, well get ready to be lifted up on peoples arms and be treated as a hero. Forget paying for your own beer for I have good news. You can likely get a ton of performance out of your datasource for "free" by switching to <https://imply.io/blog/multi-dimensional-range-partitioning/|range partitioning>

you will likely want to range partition on `type, site` or maybe `site, type` ?

is one of your dimensions frequently being filtered on? Like looking at sensors for a specific type or site?

how you get there depends on what Druid version you are on, and how you are doing ingestion (I am guessing streaming?)

The dimension that is always used for filtering in all queries is the tag field (we have about 60000 tags (sensors).

you can find that info on the home page (click the logo in the corner)

well good news that is the first version that has range partitioning

you can do it as part of the compaction config

but wait... before you jump into it... you asked a very good question! "Could you tell me how I can evaluate if the performance has improved"

yes, that is the big question :slightly_smiling_face:

In Druid 24 an amazing feature called <https://druid.apache.org/docs/latest/multi-stage-query/index.html|SQL based ingestion> was added (it was GA in Druid 25). With SQL based ingestion it would be trivial to copy a section (like a day or a month) of your main datasource into a new datasource to play with different partitioning types. You could try different partitioning types and evaluate which one works best. This is one of the main values of SQL based ingestion: it greatly lowers the cost of experimentation with data arrangements. I would say this is THE main value if you are using streaming ingest.

It is so easy that I can even write the query for you (I have not tested this query, it might have typos):

So I would recommend you upgrade to Druid 25!

but if you don't want to do it... you can still do effectively the same thing by using the native batch system

and then you can play the the secondary partitioning in the `Partition` step

after you figure out what partitioning you want you would add it to your compaction spec

ReIndex Task is running but i don't watch the test table in druid schema, maybe at the end of task?

yeah task has to finish and the segments have to load

another cool reason to upgrade and use SQL based ingestion is that it comes with really detailed progress reporting! (look at them progress bars!)

That there is the Multi Stage Query system at work :slightly_smiling_face:

<@U030W50CNAC> hi!! I have installed version 25 of druid but I don't see the sql-msq-task engine in the query console...

Did you load the extension? <https://druid.apache.org/docs/latest/multi-stage-query/index.html#load-the-extension>

Did you restart all the nodes after adding the extension?

INSERT INTO "tabla_msq"
SELECT * FROM "eparksignals-sanroque"
where __time &gt;='2023-04-11' and __time &lt; '2023-04-12'
PARTITIONED BY HOUR
CLUSTERED BY tag

As you can see I have added the "tag" column as a secondary partition (CLUSTERED BY), but if I go to see the segments it seems to have been included right?

You need to use REPLACE not INSERT, see first bullet point in <https://druid.apache.org/docs/latest/multi-stage-query/concepts.html#clustering>

My sentence only goes with CLUSTER BY tag column

yeah the d1 is a boost and is used to make sure things are distributed better, future versions of the web console will not show it at all as people find it confusing

Polaris is Imply’s SaaS with Druid. I’m just trying to understand the context of your question. Polaris has free trials, so I was wondering if that was your case. If you are an Imply customer, you will get faster assistance with its products through Imply technical support. 

Hi Sergio, thank you for your reply. Our Druid is Imply hosted and want to make some API requests to set some configs in Coordinator settings. In the request URL, I need to authenticate to make POST request. So I was thinking to use service account for username:password instead of my login credentials.

Makes sense, please work with Imply support to resolve this. 

Hi <@U030K4UM3H7> thank you so much! :slightly_smiling_face: I will create a ticket there in Support Portal then

Hello, I'm currently using druid on a kubernetes cluster and accessing the unified-console using the node port. Now I want to access to the console using an ingress for example /druid to route the traffic to the druid-router service. is there a way to change the default context path for unified-console on router to match /druid defined in the ingress ?

hi Antoine, this section of the docs might be what you're looking for - <https://druid.apache.org/docs/latest/ingestion/native-batch.html#fully-replacing-existing-segments-using-tombstones>

Thanks a lot, that looks perfect ! :grin:

Join us today at 10AM PT for a special edition of Apache Druid Office Hours. We're going to be discussing the details of how real-time ingestion tasks work and how they use resources with <@U0344FW86DD>. <https://www.meetup.com/druidio/events/290102436?utm_medium=referral&amp;utm_campaign=share-btn_savedevents_share_modal&amp;utm_source=link|Sign up here>.

There is an API for the new MSQ SQL-based ingestion operations: <https://druid.apache.org/docs/latest/multi-stage-query/api.html> ...

<@U04C4593VDY> We are not using SQL based ingestion. My use case is submitting ingestion task to druid on new file arrival. I am generating ingestionSpec and making an API call to `/druid/indexer/v1/task`

To generate ingestionSpec i was looking for if there is any client library druid has to use these classes

Ok sorry I don't know the answer to that one ...

<@U04TDJDVBHQ> - I am not sure if you have seen this but, the API documentation for the Overlord is here: <https://druid.apache.org/docs/latest/operations/api-reference.html#overlord>

Also, it is possible to use Apache Airflow to complete the same operation of taking a new file and creating a Druid ingestion task.

Airflow's filesensor:
<https://airflow.apache.org/docs/apache-airflow/stable/howto/operator/file.html>

<https://stackoverflow.com/questions/54791596/any-example-of-airflow-filesensor>

Airflow has a Druid operator that can be used to generate the task for the ingestion <https://airflow.apache.org/docs/apache-airflow-providers-apache-druid/stable/operators.html>

<@U03S0JXTM4N> Thanks sharing the information on the API. The info i was looking around client libraries in order to send ingestion request to Druid from Java application. For example to send ingestion task we need to send JSON spec can be generated from Java POJOs

If there is any client library that has all these supported classes to use from Java client applicaiton

<@U04TDJDVBHQ> - Most libraries and other 3rd party apps for Druid are here: <https://druid.apache.org/libraries>, but most focus more on query than ingestion.

Still, there maybe parts of the source code that is useable for your use case.

Got it. Thanks <@U03S0JXTM4N> I will look into it

I put together a quick write-up using Kafka, Flink and Druid in a pipeline. This has a worked out example with code for Flink. Hope this will help folks trying to join streams coming into Druid. <https://meditationsonbigdata.blog/2023/03/09/analysing-transactional-data-using-a-pipeline-with-apache-flink-apache-kafka-and-apache-druid/>

You can set the worker selection strategy using custom javascript and assign the worker nodes based on datasource.
<https://druid.apache.org/docs/latest/configuration/index.html#javascript-1>

I think you don't need to enable javascript (a security risk) if you use <https://druid.apache.org/docs/latest/configuration/index.html#affinity|affinity> (for fixed IPs), or, if you don't have fixed IPs, take a look at <https://support.imply.io/hc/en-us/articles/4406982671251-Assigning-ingestion-jobs-to-run-in-a-specific-tier-of-middleManager-nodes-based-on-worker-category-in-Cloud-|this Imply KB>.

It’s for data source type I want do by name

in below ds3 and ds11 are data sources. you need to add this in overlord dynamic config
```{
  "type": "equalDistribution",
  "affinityConfig": {
    "affinity": {
      "ds3": [
        "host1:8091",
        "host2:8091"
      ],
      "ds11": [
        "host3:8091"
      ]
    },
    "strong": true
  }
}```

nice - maybe some variation the same approach would work with the other method as well, idk

I want to restrict ingestion of particular geo data to that geo cluster

Excited to be doing a joint meetup with Apache Dolphin. We will have sessions on some of the key highlights of both Dolphin and Druid and also look at a demo of orchetsrtaing batch ingestion to Druid using Dolphin <https://www.meetup.com/bangalore-druid-meetup-group/events/292084826?utm_medium=referral&amp;utm_campaign=share-btn_savedevents_share_modal&amp;utm_source=link>

what are you loading from, and what's the goal of specifying an offset?  I imagine you dno't want to duplicate records with something like useEarliestOffset?

Thanks <@U0411DE2SS0> My data source is Kafka ingestion. I would like to load new add field data. Don't want to duplicate record on the existing streaming task.

Also, which one of the config prop is applicable to the topN queries?
since topN would have to use N as the limit, does providing the config have any effect?

all of those are groupBy specific properties— no effect on topN

topN basically always behaves like `forceLimitPushDown: true` and `applyLimitPushDownToSegment: true` is set

this seems to be a timestamp in microseconds. Divide this by 1000 to get milisecond timestamp. If you are ingesting this field as __time than that should be enough. If you want to covert this to a string data the you can use timestamp_format

<@U03BJAMQ4H5>
Yes. I am trying to ingest this as _time.  when I select the date column for _time, it is showing another value not the actual date value.

does your column actually have *1539129600000000?*

<@U03BJAMQ4H5>
No, I actually gave an example.
The actual BQ table has date values like "2022-01-01" and I exported table data to the GCS bucket using the BQ extract command: "bq extract --destination_format PARQUET ------".
While ingesting this Parquet file, My date column data is showing as Int64, and due to that, the _time column is not getting with the proper date as shown in the previous screenshot.
Hope you understood what my problem is.

if the value is like 1539129600000000 then you need to specify the format in ingestion as micro seconds.

<@U03BJAMQ4H5>
I am pretty new to Druid.
Could you please help me with where I need to specify.?
Can we implement this formatting in the "*Parse Time*" section or Can I do it in Edit Spec Step?

you can do it in parse time.Just just micro from the format drop down

note that in Druid there is no builtin timestamp type for columns other than `__time`

you should store the timestamp as a `long` type, and then at query time, you can use functions like `MILLIS_TO_TIMESTAMP` to get a timestamp type and use time functions

there is no performance overhead to `MILLIS_TO_TIMESTAMP` so don't worry about that :slightly_smiling_face:

it's best to store the number as milliseconds: so if it comes from parquet as millis, store it as-is; if it comes as microseconds, divide by 1000 (you can do this during ingest or query)

Thank you for your help <@U0344FW86DD> and <@U03BJAMQ4H5>

I am excited to announce that we are doing a in person meetup in Bangalore on 18th March (sat). This is hosted by Imply and Walmart at the Walmart office in Bangalore. We will have some interesting sessions around the Lates in Druid (MSQ, nested columns), using Druid in a ML pipeline and tips and tricks to  optimising your Druid deployment. Please register here <https://go.imply.io/Bangalore-Druid-meetup-Registration.html>

The easiest way is through <https://druid.apache.org/docs/latest/multi-stage-query/index.html|SQL based ingestion>. Take a look at some examples: <https://druid.apache.org/docs/latest/multi-stage-query/examples.html>
In the FROM clause you specify a table function called <https://druid.apache.org/docs/latest/multi-stage-query/reference.html#extern|EXTERN> to access external data in the form of an InputSource. One of the <https://druid.apache.org/docs/latest/ingestion/native-batch-input-sources.html|InputSources supported is S3>.

i would also encourage you to use the web console if you're just getting started

it's on port `8888`, you can access by browser

Check out this blog post: <https://blog.hellmar-becker.de/2023/03/07/selective-bulk-upserts-in-apache-druid/>

it did not resolve my concern
NOTE: I am ingesting data using kafka and want to update some records a time partition without overwriting the entire partition

Druid does not support individual row updates directly. It handles data in immutable files within time intervals, Helmar's article shows a mechanism through which you could update a subset of records within a time interval. In the streaming ingestion case, with updates to the "same record", you can use LATEST functions at query time to see the most recent value of a column within a time period and group on the dimensions you would use as the key to your update record.

<@U04TWAH3VQU>, read through <https://apachedruidworkspace.slack.com/archives/C0303FDCZEZ/p1678200919655739|THIS THREAD> to see how you can effect updates with reasonable efficiency using mixed segment overshadowing with very small slices of data ...

In general, Imply engineering work on Druid is contributed to the Apache Druid project. Imply's distribution has a more frequent cadence of release, so you may see some features arrive there early. When that occurs, the next release of Apache Druid usually catches up. There may be some exceptions where the integration into Apache Druid takes more than one release cycle.

Screen Shot 2023-03-14 at 9.17.42 AM.png

Hi Udit,

There is an experimental feature in Druid called "Cold Tier", still currently under development ... if you set up your datasource retention to store older data with zero "0" replicas it will display a Cold Tier percentage for the data source (see screenshots).   The segments in Cold Tier won't be loaded onto Historials ... and the regular query engine will not see them or be able to query them ... but the MSQ query engine *will* see them and be able to query them directly from deep storage.

The MSQ query is still using task slots, but not reserving them in the way the other Druid servers are.

There is an API you can use to submit MSQ tasks ... what I don't know is if the current version of the API can be used to issue queries, or only ingestion tasks (with little return data).