Data with long days time range is not able loaded in grafana v8.0+ dashboard when using Clickhouse plugins with v1.1.2.

[sujuanliu]

What happened:
Grafana: v9.0.0,v8.5.4, v8.3.3
ClickHouse Plugin: v1.1.2

When using ClickHouse plugin with version 1.1.2, 7 days' data in dashboard is not able loaded as expected. But data with short time range is loaded correctly in dashboard. e.g. 24 hours, 1-6hours

What you expected to happen:
7 days's data should be shown correctly in dashboard.

How to reproduce it (as minimally and precisely as possible):

1. Install ClickHouse with latest version 1.1.2.
2. Create a dashboard with ClickHouse as datasource.
3. Select time ranage as 30 mins,1 or 6 hours. You will find the data is shown well.
4. Select time range as 2 days or 7 days, you will find the msg "driver: bad connection: Could not process SQL results" is shown in dashboard. Actually, the SQL executed in ClickHouse takes only 10 seconds or less in the ClickHouse server.

Anything else we need to know?:
The issue only occurs when installing ClickHouse plugins with lastest version 1.1.2. If you downgrade to version 1.0.1, the data is shown correctly in dashboard. There are 2000 million data for 7 days in ClickHouse, and it returns 1900+ rows when executing the SQL.

Environment:

• Grafana version: 9.0.0, 8.5.4, 8.3.3
• Data source type & version: ClickHouse Plugin version v1.1.2
• OS Grafana is installed on: CentOS 7
• User OS & Browser: MacOS, Chrome
• Grafana plugins: ClickHouse Plugin version v1.1.2
• Others:

[ivanahuckova]

Hello @sujuanliu! This seems to be an issue directly with ClickHouse plugin. I am going to transfer this issue to ClickHouse plugin repository.

[gingerwizard]

Default timeout on the connection is 10s @sujuanliu can you ensure the data source is configured with a higher value?

[gingerwizard]

e.g.

using play

SELECT
        toStartOfMonth(created_at) AS time,
                lower(repo_name) AS repo,
        count() AS c
    FROM github_events
    WHERE (event_type = 'WatchEvent') AND (toYear(created_at) >= 2015) AND (repo IN
    (
        SELECT lower(repo_name) AS repo
        FROM github_events
        WHERE (event_type = 'WatchEvent') AND (toYear(created_at) >= 2015)
        GROUP BY repo
        ORDER BY count() DESC
        LIMIT 10
    ))
    GROUP BY
        repo,
        time
    ORDER BY time ASC    

[sujuanliu]

@gingerwizard i tried to configure the datasource with a higher value 3600 s, but got the same timeout error when showing 7 days' data. It doesn't looks like the issue is related to datasource timeout config because the data is still able to show when it takes more than 10s or even serveral minutes in plugin with version 1.0.1.

i use ClickHouse to store Nginx access logs and monitor the domain requests statistic via grafana. Below is the SQL i used in grafana.

SELECT $__timeInterval(create_time) as time, create_time, fields_log_source, count(1) as "domain_name:" FROM huoli.nginx_log_all WHERE $__timeFilter(create_time) AND (fields_log_source in($domain_name) ) GROUP BY create_time, fields_log_source, time ORDER BY time ASC

It is OK for showing data in 24 hours：

Data is not showing in 7 days with error "driver: bad connection: Could not process SQL results"：

[sujuanliu]

Here is the graph for 7 days when using clickhouse plugin with version 1.0.1. Everything looks well here.

[gingerwizard]

Are you able to try against main? We updated the driver to v2 yesterday which is a significant change.

[sujuanliu]

How can i install the main build with the driver v2 commit? What i need to do is to download the plugin zip file in main release, and run "yarn install && yarn dev"? i got a "Unsigned" ClickHouse plugin with version 1.1.2, and it prompts the plugin is disabled even i have configured appmode as development. Sorry i am not very familiar with the plugin build process.

[gingerwizard]

@sujuanliu we will try to release this week so you can test. v2 of the driver is a significant improvement and this looks like a driver issue in v1.

[sujuanliu]

I haven't seen the release of v2 yet. I will wait for the realse and test it. Thanks!

[washcroft]

@sujuanliu
Here's a ZIP containing a built latest version (as of now):
https://github.com/4thu/clickhouse-datasource/raw/main/grafana-clickhouse-datasource.zip

To build your own, follow the Grafana instructions:

Clone/download repo

cd clickhouse-datasource
yarn install
yarn build
go get -u github.com/grafana/grafana-plugin-sdk-go
go mod tidy
mage -v

Now, rename the new dist folder grafana-clickhouse-datasource, and ZIP it up (so that the grafana-clickhouse-datasource folder is in the ZIP).

Finally, follow the Grafana instructions for installing a plugin from ZIP, remember to add grafana-clickhouse-datasource to the allowed list of unsigned plugins.

[gingerwizard]

@sujuanliu release 2.0-beta out. Please try.

[gingerwizard]

@sujuanliu 2.0 out. please test.

[bossinc]

Closing this issue. Feel free to reopen if this hasn't been resolved.