This message was deleted.

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

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 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 ... 😉

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

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 ☝️ 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?

Using MSQE,

REPLACE INTO "trips_month" OVERWRITE WHERE "__time" >= TIMESTAMP '2013-08-02 00:00:00' and "__time"< TIMESTAMP '2013-08-03 00:00:00'

, I am able to insert 1 DAY partitioned segment into a DS with

MONTH

segment granularity:

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

Can you try 25?

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.

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

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.

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).