Clickhouse - Multi-statements are not allowed errors. How to debug?

Issue Summary

Some queries to Clickhouse returns an error like the following:

Error running query: **Code: 62, e.displayText() = DB::Exception: Syntax error (Multi-statements are not allowed): failed at position 774 (end of query) (line 19, col 25): ; FORMAT JSON. (version 19.17.2.4 (official build))**

I’m not sure why is this happening when all the query I tried works fine when using Clickhouse HTTP interface done via curl or using the Clickhouse client.

How can I view the full query that Redash is sending to Clickhouse?

Technical details:

I don’t think it’s a bug. This sounds like expected behavior for Clickhouse.

I searched your stack trace and found this:

It seems Clickhouse can’t support multi-statements without special client configuration.

Look at your query editor. What you see is sent as-is to the database. You can step through it manually using a development environment described in our developer guide.

Perhaps the query runner can be updated to support multi-statements? This isn’t something we can do internally since we don’t have a CH instance to test against.

1 Like

This would be a very useful feature for us. Clickhouse is very powerful but sometimes obstinate database. Especially in distributed setup and when global joins are required. In this case it’s very useful to break one query in several smaller ones and materialize intermediate results in temporary tables.

Clickhouse has session support for HTTP-based query interface (see session_id in documentation) and it seems like it’s technically possible to implement multi statements. Although, script must be split on queries on client-side (redash).

Does it seems like something from other users can benefit. I think I could provide a PR?

1 Like

We would be happy to review this PR :smiley: We can’t really do this ourselves since our team doesn’t run Clickhouse anywhere.

Hi!

You can use Python query runner to concatenate results from several queries.

Seems like a promising idea. Didn’t know about python query runner, because it doesn’t enabled by default on self hosted instance. :slight_smile: Thanks.

May be You may have a problem with the semicolon since ClickHouse does not need to specify at the end of the line “;” Check this and your problem is may gone !

4 Likes

You are right,thank you