This message was deleted.
# generals
Slackbot
07/28/2023, 10:29 AMThis message was deleted.
b
Bibek Sahoo
07/28/2023, 10:42 AMI believe, You should have added the select clause at the end.
the regular expression is:
Copy code
REPLACE INTO <target table>
OVERWRITE WHERE __time >= TIMESTAMP '<lower bound>' AND __time < TIMESTAMP '<upper bound>'
< SELECT query >
PARTITIONED BY <time granularity>
[ CLUSTERED BY <column list> ]Bibek Sahoo
07/28/2023, 10:47 AMa
anoop kumar
07/28/2023, 10:49 AMMy actual problem is not to replace but to remove
anoop kumar
07/28/2023, 10:50 AMREPLACE INTO "WaterMeterTest"
OVERWRITE WHERE "__time">'2022-01-01T000000' AND __time< '2022-01-02T000000' AND "content.FLOW_TEMPERATURE" = 18.1
Select * from "WaterMeterTest"
i have written above query now but getting below error :
Error: Plan validation failed
Expressions must be of the form __time <operator> TIMESTAMP
org.apache.calcite.tools.ValidationException
b
Bibek Sahoo
07/28/2023, 10:52 AMFor REPLACE clause SELECT is mandatory, For this error you should add TIMESTAMP before your timestamp value.
Copy code
TIMESTAMP '<lower bound>'a
anoop kumar
07/28/2023, 10:53 AMDruid doesnot provide much to delete data
b
Bibek Sahoo
07/28/2023, 10:54 AMTo delete specific records, we should use reindex along with filter and filter out specific records during reingestion.
a
anoop kumar
07/28/2023, 11:12 AMcan you tell how to do that?
anoop kumar
07/28/2023, 11:13 AMi'm struggling to delete some records
b
Bibek Sahoo
07/28/2023, 11:14 AMExample is given here: https://druid.apache.org/docs/latest/data-management/delete.html#specific-records
a
anoop kumar
07/28/2023, 11:21 AMi'm following this only for sql,
they have used replace to remove records, i'm trying that only
b
Bibek Sahoo
07/28/2023, 11:33 AMThis is what i ran to replace 3 records(page=Salo Toraut) from my datasource
Copy code
REPLACE INTO "wikipedia-uri"
OVERWRITE ALL
select * from "wikipedia-uri" where page <>'Salo Toraut'
PARTITIONED BY DAYBibek Sahoo
07/28/2023, 11:38 AMFor specific time interval:
Copy code
REPLACE INTO "wikipedia-uri"
OVERWRITE WHERE ("__time" >= TIMESTAMP '2016-01-01' AND "__time" < TIMESTAMP '2017-01-01')
select * from "wikipedia-uri" where page <>'Salo Toraut'
PARTITIONED BY DAYa
anoop kumar
07/28/2023, 12:46 PMHi, it is working, thanks a lot.
👍 1
s
Sergio Ferragut
07/28/2023, 5:38 PMone additional suggestion is to just select from the appropriate time interval in order to avoid unnecessary processing or rows that will not be in the target time interval:
Copy code
REPLACE INTO "wikipedia-uri"
OVERWRITE WHERE ("__time" >= TIMESTAMP '2016-01-01' AND "__time" < TIMESTAMP '2017-01-01')
select * from "wikipedia-uri"
where page <>'Salo Toraut'
AND "__time" >= TIMESTAMP '2016-01-01' AND "__time" < TIMESTAMP '2017-01-01'
PARTITIONED BY DAY👍 1