Question: Is possible to check for specific variable value in during query building.

[ceckoslab]

I read the plugin docs and played a lot with the plugin but I couldn't find a way to make this working.

Here is my case:

I am building dashboards for displaying webperformance metrics. Depending on a metric sometimes the SQL is slightly different.

This is the query for a metric called Cumulative Layout Shift:

SELECT
    intDiv(cumulative_layout_shift, 0.01) * 0.01 as cumulative_layout_shift,
    sum(toUInt32(1)) as count

FROM $table

WHERE
    $timeFilter
    AND event_type = 'visit_page'
    AND cumulative_layout_shift IS NOT NULL
    $conditionalTest(AND hostname in($hostname), $hostname)

GROUP BY cumulative_layout_shift
ORDER BY cumulative_layout_shift

Here we have another for Time to First Byte:

SELECT
    intDiv(first_byte_duration, $bucket_size) * $bucket_size as first_byte_duration_value,
    sum(toUInt64(1)) as count

FROM $table

WHERE
    $timeFilter
    AND $perf_timer > 100
    AND $perf_timer < 10000
    AND event_type = 'visit_page'
    $conditionalTest(AND hostname in($hostname), $hostname)

GROUP BY first_byte_duration_value
ORDER BY first_byte_duration_value

The example queries have some differences:

• The Cumulative Layout Shift query has a check for NOT NULL.

• The Cumulative Layout Shift also has different bucketing: intDiv(cumulative_layout_shift, 0.01) * 0.01 as cumulative_layout_shift

  Is it possible to have something similar in the query temple:

$variableMatch('cumulative_layout_shift', $perf_timer, 
SELECT
    intDiv(cumulative_layout_shift, 0.01) * 0.01 as cumulative_layout_shift,
    sum(toUInt32(1)) as count

FROM $table

WHERE
    $timeFilter
    AND event_type = 'visit_page'
    AND cumulative_layout_shift IS NOT NULL
    $conditionalTest(AND hostname in($hostname), $hostname)

GROUP BY cumulative_layout_shift
ORDER BY cumulative_layout_shift
)

$variableMatch('first_byte_duration', $perf_timer, 
SELECT
    intDiv(first_byte_duration, $bucket_size) * $bucket_size as first_byte_duration_value,
    sum(toUInt64(1)) as count

FROM $table

WHERE
    $timeFilter
    AND $perf_timer > 100
    AND $perf_timer < 10000
    AND event_type = 'visit_page'
    $conditionalTest(AND hostname in($hostname), $hostname)

GROUP BY first_byte_duration_value
ORDER BY first_byte_duration_value
)

[Slach]

sorry, i don't understand

what exactly SQL shall generated after apply

$variableMatch('cumulative_layout_shift', $perf_timer,

?

Could you please provide raw SQL which you want to generate?

[ceckoslab]

Sorry, I had to be more specific. I will try again :)

It's something like a SWITCH operator.

If $perf_timer value is cumulative_layout_shift I would like to get the query wrapped only in this block:

$variableMatch('cumulative_layout_shift', $perf_timer, 
SELECT
    intDiv(cumulative_layout_shift, 0.01) * 0.01 as cumulative_layout_shift,
    sum(toUInt32(1)) as count

FROM $table

WHERE
    $timeFilter
    AND event_type = 'visit_page'
    AND cumulative_layout_shift IS NOT NULL
    $conditionalTest(AND hostname in($hostname), $hostname)

GROUP BY cumulative_layout_shift
ORDER BY cumulative_layout_shift
)

So if we have this save in the Gafana query editor:

$variableMatch('cumulative_layout_shift', $perf_timer, 
SELECT
    intDiv(cumulative_layout_shift, 0.01) * 0.01 as cumulative_layout_shift,
    sum(toUInt32(1)) as count

FROM $table

WHERE
    $timeFilter
    AND event_type = 'visit_page'
    AND cumulative_layout_shift IS NOT NULL
    $conditionalTest(AND hostname in($hostname), $hostname)

GROUP BY cumulative_layout_shift
ORDER BY cumulative_layout_shift
)

$variableMatch('first_byte_duration', $perf_timer, 
SELECT
    intDiv(first_byte_duration, $bucket_size) * $bucket_size as first_byte_duration_value,
    sum(toUInt64(1)) as count

FROM $table

WHERE
    $timeFilter
    AND $perf_timer > 100
    AND $perf_timer < 10000
    AND event_type = 'visit_page'
    $conditionalTest(AND hostname in($hostname), $hostname)

GROUP BY first_byte_duration_value
ORDER BY first_byte_duration_value
)

I would like to get as a RAW SQL:

SELECT
    intDiv(cumulative_layout_shift, 0.01) * 0.01 as cumulative_layout_shift,
    sum(toUInt32(1)) as count

FROM performance_data_table

WHERE
    event_date >= toDate(1661176376) AND event_date <= toDate(1668955976) AND created_at >= toDateTime(1661176376) AND created_at <= toDateTime(1668955976)
    AND event_type = 'visit_page'
    AND cumulative_layout_shift IS NOT NULL
    AND hostname IN('www.myhost.com')

GROUP BY cumulative_layout_shift
ORDER BY cumulative_layout_shift

If $perf_timer value is first_byte_duration I would like to get this RAW SQL:

SELECT
    intDiv(first_byte_duration, 100) * 100 as first_byte_duration_value,
    sum(toUInt64(1)) as count

FROM performance_data_table

WHERE
    event_date >= toDate(1661176376) AND event_date <= toDate(1668955976) AND created_at >= toDateTime(1661176376) AND created_at <= toDateTime(1668955976)
    AND first_byte_duration > 100
    AND first_byte_duration < 10000
    AND event_type = 'visit_page'
    AND hostname IN('www.myhost.com')

GROUP BY first_byte_duration_value
ORDER BY first_byte_duration_value

[Slach]

Ok. I would like to propose create TWO separate queries

and add AND $perf_timer = 'cumulative_layout_shift' to first query

AND $perf_timer = 'first_byte_duration' to second query

[ceckoslab]

Is the proposal to always run the TWO queries in parallel for the same panel?

The queries will be more different in the WHERE clause but also the SELECT part varies.

For cumulative_layout_shift we have bucketing by 0.01 where for first_byte_duration we have bucketing by 100.

For cumulative_layout_shift:

SELECT
    intDiv(cumulative_layout_shift, 0.01) * 0.01 as cumulative_layout_shift,
    sum(toUInt32(1)) as count

For first_byte_duration:

SELECT
    intDiv(first_byte_duration, 100) * 100 as first_byte_duration_value,
    sum(toUInt64(1)) as count

By the way, I've never written a single line for a Grafana plugin but I would like to contribute if such a thing makes sense or if it already doesn't exist out of the box.

[Slach]

any PR is welcome ;)

[Slach]

query where AND false will fast

[ceckoslab]

One more clarification. My intention is to show display the result in single Grafana panel.

cumulative_layout_shift

first_byte_duration

The bucketing and filtering for these metrics is different and that's why all the complexity. I already have the metrics rendered in separate Grafana panels but for some dashboards I would like to have metrics to be rendered in a single Grafana panel. The querying and rendered data in the panel will triggered by selecting an option from a filter.

E.g.

I think that we can close this issue.

[ceckoslab]

@Slach I just opened this PR: https://github.com/Altinity/clickhouse-grafana/pull/466

My intention is to socialize the idea/intention.

[Slach]

close after long inactivity