MQL Query to Convert Row to Series

[Chavya Khanna]

Hello Everyone,

Was hoping if someone could assist with a query where 

Metric Data is displayed in below format:

  ID         store    state
xyz12     123      fetch
xyz12     123       sent

Expected Output is as below:
ID         store  state1 state2
xyz12   123    fetch    sent

Is this something achievable via MQL ? I've been looking for a solution but haven't come across a mechanism to implement such.

Regards,

[Joy Wang]

+Dima Korzhyk 

--
© 2021 Google Inc. 1600 Amphitheatre Parkway, Mountain View, CA 94043
 
Email preferences: You received this email because you signed up for the Google Stackdriver Discussion Google Group (google-stackdr...@googlegroups.com) to participate in discussions with other members of the GoogleStackdriver community.
---
You received this message because you are subscribed to the Google Groups "Google Stackdriver Discussion Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-stackdriver-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-stackdriver-discussion/e29613b1-f567-46ef-8beb-f1fec713ca13n%40googlegroups.com.

[Chavya Khanna]

Sorry just checking if someone had a chance to look at this?

[j...@google.com]

Hi,

Thank you for your question! 

If the number of possible states is unlimited then there is no way to do it.

But if it's a limited number of states, you could have one boolean column per state

like is_fetch, is_sent, etc. 

Hope this answers your questions.

Cheers,

Joy

[Chavya Khanna]

Hi Joy,

Thanks for your response apologies, i should've mentioned i've tried this approach already and yes there are only 2 states, its either/or - Now the problem with this approach is:

Approach 1:  Didn't work

We get output of :

ID         store        state1               state2
xyz12   123      true/fetched    false/-

xyz12   123       false/-             true/sent

None of the filters would work in this scenario - knowing one of the states will be false or blank at every given point of time.

Approach 2: Join - Didn't work

In above scenario a Join would help - however, if we apply join then let's we have a situation where there's only "fetch" in the system and no "sent" in that scenario  we will not get any data.

Approach 3: Outer Join - Didn't work

With outer join - It will only return cases where state was not "sent" It wouldn't return everything like example below:

Where Expected Output is as below:

ID         store  state1 state2
xyz12   123    fetch    sent

xyz13   123    fetch    null/-/Nothing

xyz14   123    fetch    sent

xyz15   102    fetch    sent

xyz16   102    fetch    null/-/Nothing

Let me know your thoughts.

[Joy Wang]

Hi Chavya,

You need to aggregate the state1 and state2 columns using the any_true aggregator (https://cloud.google.com/monitoring/mql/reference#any_true-func)

So something like ... | value [state1: if(...), state2: if(...)] | group_by add [] [state1: any_true(state1), state2: any_true(state2)] | ...

Cheers,

Joy

You received this message because you are subscribed to a topic in the Google Groups "Google Stackdriver Discussion Forum" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-stackdriver-discussion/C_ff-eYx0XI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-stackdriver-d...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-stackdriver-discussion/e325b6a8-081b-43c2-b47c-06d05d90f582n%40googlegroups.com.

[Chavya Khanna]

Hi Joy,

Apologies I am not an expert in Google MQL , I did try the that aggregator and was getting syntax error:

fetch cloud_function

| metric 'logging.googleapis.com/user/MetricName'

| value[fetched: if(metric.message_type =  "fetched",true(), false()), sent: if(metric.message_type = "sent", true(), false())]

| group_by add [fetched: any_true(fetched), sent: any_true(sent)]

Line 4: Reducer function 'any_true' given for a table operation argument that does not operate on sets of rows.

- Aside from this would you be able to suggest a course best suited for Google MQL?

Regards,

[Chavya Khanna]

Hi,

Sorry this old ghost is chasing me again - would anyone be able to assist with this?

[Mary Koes]

Hi Chavya,

Unfortunately, I'm not a MQL expert either so can't directly help. But I wonder if PromQL might be easier for this use case? @Lee Yanco @Joy Wang 

Best,

Mary

To view this discussion on the web visit https://groups.google.com/d/msgid/google-stackdriver-discussion/d8fa9ad0-9c2f-4328-849e-33c5e33bff1dn%40googlegroups.com.

[Joy Wang]

Thanks Mary! I have connected with Chavya with MQL experts while removed the bigger audience earlier today!