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

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

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

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.

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

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?

Any ideas ?

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