How to update the column in a elasticsearch database using sql query

Elastic Stack Elasticsearch
1

Hi All,
Can we update a column in a elasticsearch database using sql query.
I have used this query
'UPDATE from duration_17 SET delay_sec = '12.20' WHERE dest_ip=192.168.1.181;'

but got this error

Bad request [line 1:1: mismatched input 'UPDATE' expecting {'(', 'DEBUG', 'DESC', 'DESCRIBE', 'EXPLAIN', 'SELECT', 'SHOW', 'SYS', 'WITH'}]>

Here i need to update the column directly in the database itself. Is there any possibility to do within Database.

2

Currently ES SQL only allows SELECT queries.
It's not possible to INSERT/UPDATE/DELETE documents through SQL.

If you want to update existing documents based on some match criteria please refer to the:
update by query section in the docs.

3

Hi @matriv , What if i want to update a particular column , can i use the update by query. I have gone through it , There i have seen this ,

POST twitter/_update_by_query
{
"script": {
"source": "ctx._source.likes++",
"lang": "painless"
},
"query": {
"term": {
"user": "kimchy"
}
}
}

By using this , can i update the column delay_sec

sql> select delay_sec,event_id,client_ip,dest_ip,message_id from duration_17 LIMIT 70;
delay_sec | event_id | client_ip | dest_ip | message_id
------------------+---------------+---------------+---------------+----------------------------------
0.9620000123977661|SEND |IP ADDRESS |IP ADDRESS |!&!AA@openaccessjournals.org
0.781000018119812 |SEND |IP ADDRESS |IP ADDRESS |!&!AA@openaccessjournals.org
2.743000030517578 |SEND |IP ADDRESS |IP ADDRESS |SRSR!&!AA@openaccessjournals.org
1.0399999618530273|SEND |IP ADDRESS |IP ADDRESS |SRSR!&!AA@openaccessjournals.org
0.0 |SEND |IP ADDRESS |IP ADDRESS |SSS!&!AA@openaccessjournals.org
1.2589999437332153|SEND |IP ADDRESS |IP ADDRESS |SSS!&!AA@openaccessjournals.org
0.7419999837875366|SEND |IP ADDRESS |IP ADDRESS |SSS!&!AA@openaccessjournals.org

This is my table looks like , i need to update the delay_sec value.
By using update by query , Is it possible ?

4

You should just adjust the filter:
"query": { "term": { "user": "kimchy" }
to match your dest_ip
and the "source": "ctx._source.likes++", to just set the value you want to delay_sec.

5

I don't think this will fill my requirements, That will be a live data coming into the elastic search , i need a code that runs everytime whenever the data comes into ES.

6

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.