Perform a left join

[ayoub mrini]

Hello,

I know there is a related question here: https://groups.google.com/d/topic/prometheus-users/mSkcyW5ZvPI/discussion, but no response was provided.

I need to perform a left outer join, suppose we have two series of metric1: {label1=v1, label2=v2} and {label1=v3, label2=v4}

and one series of metric2: {label1=v1, label3=v5}

how can we join the two metrics without loosing {label1=v3, label2=v4}, in other words, how can we get  {label1=v1, label2=v2, label3=v5} and  {label1=v3, label2=v4, label3=None(or sth else)}

If we do sth like:    metric1 * on(label1) group_left(label3) metric2  we will only get  {label1=v1, label2=v2, label3=v5} 

Thanks

[Tim Butler]

Take your original query that returns only the results with matching key(s) augmented with the extra label("label3")

and "or" it with a version of metric1 with an added label3 populated with a default/None/Null label value.

 

  metric1 + on(label1) group_left(label3) metric2*0
  or
  label_replace(metric1, "label3", "None", "label1", ".*")

 

 

See https://prometheus.io/docs/prometheus/latest/querying/functions/#label_replace

 

label_replace(v instant-vector, dst_label string, replacement string, src_label string, regex string)

 

Note that you cannot use an empty string as a default label value,

because that apparently results in the label being removed completely from the element.

Any label present in all of the metric1 elements could be used instead of "label1" as the src_label.

We just need a src_label,regex combination that will match all elements.

I chose "label1" because that is what we want to join on anyway.

 

Note that first part of the "or" expression loses its metric name because of the arithmetic + operation.

The label_replace expression after the "or" retains the metric name.

I don't know if that would ever be a problem,

but the metric name can be eliminated in the second expression with an identity arithmetic operation.

 

  metric1 + on(label1) group_left(label3) metric2*0
  or
  label_replace(metric1, "label3", "None", "label1", ".*")+0

 

Now I'm just making things up,

but I kinda wish Prometheus supported an outer join for labels with something like

 

  metric1 + on(label1) join_left(label3="None") metric2*0

 

Or supported default values in expressions such as

really_complicated_expression > on(threshold_key) group_left() default(42) custom_thresholds_timeseries

that did not require repeating really_complicated_expression.

 

The solution described at https://www.robustperception.io/using-time-series-as-alert-thresholds

seems verbose for what I would consider a common use case.

 

-tim

[Harald Koch]

On Fri, Oct 18, 2019, at 16:22, Tim Butler wrote:

that did not require repeating really_complicated_expression.

 

The solution described at https://www.robustperception.io/using-time-series-as-alert-thresholds

seems verbose for what I would consider a common use case.

Put "really_complicated_expression" in a recording rule? Works for me...

--

Harald

[Tim Butler]

 

Put "really_complicated_expression" in a recording rule? Works for me...

There are times that I don't necessarily want to bake a persistent recording rule into Prometheus such as:

* developing queries

* creating ad-hoc queries

* creating queries outside of Prometheus, such as in Grafana, as a user that should not necessarily have access to the Prometheus configuration

* changing an expression that I would like to take effect retroactively

-tim

[Tim Butler]

Correction: I left out an on(label1) for the or:
 

  metric1 + on(label1) group_left(label3) metric2*0

  or on(label1)
  label_replace(metric1, "label3", "None", "label1", ".*")

-tim

[ayoub mrini]

Thanks Tim for your response, actually the provided example doesn't reflect the real situation which is:

metric1 has two series {label1=v1, label2=v2} and {label2=v4} (without label1)

and the metric2 has {label1=v1, label3=v5}

the thing I don't get is that when I run label_replace(metric1, "label3", "None", "label1", ".*"), I get {label1=v1, label2=v2, label3=None} and  {label2=v4, label3=None} even if {label2=v4} doesn't have src_label=label1

(How did we match the regular expression regex against the label src_label ?)and {label1=v1, label2=v2} doesn't have dst_label=label3 (How did we replace label dst_label  by the expansion of replacement seen that label3 doesn't exist).

Am I wrong ?