Grafana with ClickHouse (Bad Request when trying create visualization)

Grafana
1

Hello,
ClickHouse connected into Grafana as a source. I have an issue when trying to create visualization (table). I create next query:

SELECT
    toDateTime(t / 1000) + 3600*3  as deviceReceiptTime,
    deviceAddress,
    deviceVendor,
    deviceProduct,
    deviceVersion,
    eventId,
    deviceEventCategory,
    severity,
    name,
    sourceTime,
    deviceHostName,
    deviceProcessName,
    sourceHostName,
    sourceAddress,
    sourcePort,
    sourceUserName,
    destinationAddress,
    destinationHostName,
    destinationUserName,
    destinationPort,
    reason,
    requestUrl,
    applicationProtocol,
    deviceCustomString1,
    deviceCustomString2
FROM
(
    SELECT
        $timeSeries as t,
        deviceAddress,
        deviceVendor,
        deviceProduct,
        deviceVersion,
        eventId,
        deviceEventCategory,
        severity,
        name,
        sourceTime,
        deviceHostName,
        deviceProcessName,
        sourceHostName,
        sourceAddress,
        sourcePort,
        sourceUserName,
        destinationAddress,
        destinationHostName,
        destinationUserName,
        destinationPort,
        reason,
        requestUrl,
        applicationProtocol,
        deviceCustomString1,
        deviceCustomString2
    FROM seo.DATA_mwg_F
    
    WHERE $timeFilter
    
    UNION ALL
  
    SELECT
        $timeSeries as t,
        deviceAddress,
        deviceVendor,
        deviceProduct,
        deviceVersion,
        eventId,
        deviceEventCategory,
        severity,
        name,
        sourceTime,
        deviceHostName,
        deviceProcessName,
        sourceHostName,
        sourceAddress,
        sourcePort,
        sourceUserName,
        destinationAddress,
        destinationHostName,
        destinationUserName,
        destinationPort,
        reason,
        requestUrl,
        applicationProtocol,
        deviceCustomString1,
        deviceCustomString2
    FROM seo.DATA_asa_F
    
    WHERE $timeFilter
)

WHERE deviceVendor in ($deviceVendor) and deviceAddress in ($deviceAddress) and severity in ($severity) and sourceAddress in ($sourceAddress) and sourceUserName in ($sourceUserName)

ORDER BY deviceReceiptTime DESC

And it’s work correct. But when i’m trying to add another one variable in condition WHERE, my query become broken. I have an error Bad Request.

image

Seems, like Grafana can’t take into input too long query, because in variables quite a lot of values. Also this query correctly work directly in clickhouse and i haven’t got any problems.
Any suggestions? Thx!

2

Hi,

Did you try to break your query into multiple queries and do a join on them.

Good Luck

3

Hi, dude,
Thx, but i have same error, and i don’t understand what is it. Directly in clickhouse-client the same query runs quickly and correct.

4

Hi,

Can you see the request you get on clickhouse, might be some syntax error after replacing the variables values.

Good Luck

5

This topic was automatically closed after 365 days. New replies are no longer allowed.