How to write a efficient ES query for the below SQL query?

Elastic Stack Elasticsearch
1 
select tag1, tag2, sum(sum_val)
    select tag-uid, sum(value) as sum_val
    from data_table
    group by tag-uid
join tag_table using(tag-uid)
group by tag1, tag2
2

If you have your documents ingested appropriately like mentioned here, you could use the translate endpoint of Elasticsearch SQL and convert the SQL query to equivalent Query DSL.

--
Aravind

3

That seems to be a join which Elasticsearch does not support. You are therefore likely to restructure your data when indexing. If you describe the use case and what you are trying to achieve at a higher level someone may be able to help you.

4

we use es as tsdb, and data we store is look like:

{
	"metric": "metric_name",
	"@timestamp": "2020-01-01T01:01:01",
	"t": {
		"key1": "val1",
		"key2": "val2"
	},
	"f": {
		"name1": 1.2,
		"name2": 2.3
	}
}

simple aggregation is ok (but some aggs are still very slow) like:

select t.key2, sum(f.name1) from tsdata where t.key1='val1' group by t.key2

but i have no idea that if we can handle a more complex aggregation like "join" does in mysql

5

oh, thanks for your replying!!

we use es as tsdb described in:

my other answer

data look like:

{
	"metric": "metric_name",
	"@timestamp": "2020-01-01T01:01:01",
	"t": {
		"key1": "val1",
		"key2": "val2"
	},
	"f": {
		"name1": 1.2,
		"name2": 2.3
	}
}

so, if we want to have more tsdb-use funcs like:

1、"topk( metric_name.f.name1 ) by t.key1 in_every '10m'"
2、"inc-rate( metric_name.f.name1 ) in_every '5m'"

that es aggs can not directly support

my question is:

can i enrich the function of es like udf do in hive ?
or, it's to say that how to create a user defined function in es?

6

Any ideas ?