Creating new index by inner join of two index in opendistro

Plugins SQL
1

Hi All,
I have below 2 indexes with with one common fields.I have to create a new index after inner join with common field.
I am able to get query results with below query but wanted to know how to create new index with this inner join results.

index1> common field,name,city,Model_fraud
index2>common field,flag

Query:

POST _opendistro/_sql
{
“query”: “SELECT p.Name, p.Model_Flag,d.Flag,p.a FROM index1 p JOIN index2 d ON p.a = d.a”
}

Thanks
Sarvendra

3

Hi @sarvendras, there is something in SQL standards to create table from query result set. but we don’t support any CREATE TABLE for now. A workaround is to run your query by curl with format=raw and then pipe it with some other command to convert it to Elasticsearch bulk request format and use it to create a new index finally.

4

@daichen Thanks for reply…
Can you please give an example how to do this with syntax…that will be really helpfull.

Thanks
Sarvendra

5

I am getting data like below after inner join:

POST _opendistro/_sql?format=raw
{
“query”: “SELECT p.a,p.Name,p.modalflag,d.Flag FROM merchantindex p JOIN flagindex d ON p.a = d.a”
}

1|Sarvendra|fraud|nonfraud
2|Shashank|nonfraud|nonfraud
3|Anand|fraud|nonfraud