Connecting via DBeaver

[k0rsakov]

Good afternoon.

Thank you for such a good and much needed tool.

Everything works fast and natively. I'm loving it.

But I have a question that will help a lot in my work.

How can I connect to my DuckDB via DBeaver?

I used your tool and launched DuckDB on the server on 10.11.12.13. The connection works via curl, but I want to write the code natively locally in DBeaver, and run it on my server on 10.11.12.13.

How can I do this?

[lmangani]

Hey @k0rsakov thanks for the kind words! The http_server extension "loosely" emulates the ClickHouse HTTP API so you might be able to use a DBeaver connector for ClickHouse using the HTTP API as long as you use one of the supported formats. This is also how the play UI interacts with the DuckDB http_server extension. If you try and end up in a dead corner let us know and we'll come up with some ideas.

[k0rsakov]

Thanks for your reply.

I use this code on my VM:

INSTALL httpserver FROM community;
LOAD httpserver;
SELECT httpserve_start('0.0.0.0', 9999, 'duck:duck');

And then I get this Unauthorized response.

In my browser, when I go to http://10.11.12.13:9999 / there are no problems. I can write queries and they run fast as needed. But there is a connection error in DBeaver.

[lmangani]

I guess the DBeaver client expects something else. If you see any errors logs or the connection details from the http_server access logs perhaps we can find out.

[k0rsakov]

@lmangani

Hi. I took the time to research and found a problem that the http client from ClickHouse does not work with DuckDB httpserver.

I can create an httpserver like this:

INSTALL httpserver FROM community;
LOAD httpserver;
SELECT httpserve_start('0.0.0.0', 9999, '');

Or so:

INSTALL httpserver FROM community;
LOAD httpserver;
SELECT httpserve_start('0.0.0.0', 9999, 'duck:duck');

But every time I get a connection error.

My attempt to connect via clickhouse_connect:

import clickhouse_connect

#auth
client = clickhouse_connect.get_client(host='10.11.12.13', username='duck', password='duck', port=9999)
#wauth
client = clickhouse_connect.get_client(host='10.250.203.45', port=9999)

If I connect via clickhouse_connect (Python package), with or without authorization:

DatabaseError: HTTPDriver for http://10.11.12.13:9999/ returned response code 500)
 Binder Error: No function matches the given name and argument types 'timezone()'. You might need to add explicit type casts.
	Candidate functions:
	timezone(DATE) -> BIGINT
	timezone(INTERVAL) -> BIGINT
	timezone(INTERVAL, TIME WITH TIME ZONE) -> TIME WITH TIME ZONE
	timezone(TIMESTAMP) -> BIGINT
	timezone(TIMESTAMP WITH TIME ZONE) -> BIGINT
	timezone(VARCHAR, TIMESTAMP) -> TIMESTAMP WITH TIME ZONE
	timezone(VARCHAR, TIMESTAMP WITH TIME ZONE) -> TIMESTAMP
	timezone(VARCHAR, TIME WITH TIME ZONE) -> TIME WITH TIME ZONE


LINE 1: SELECT version(), timezone()

If I connect via DBeaver with authorization:

Unauthorized

Unauthorized

If I connect via DBeaver without authorization:

Parser Error: syntax error at or near "user"

LINE 1: select currentUser() user, timezone() timezone, version() version, toUInt8(ifnull...
                             ^

Parser Error: syntax error at or near "user"

LINE 1: select currentUser() user, timezone() timezone, version() version, toUInt8(ifnull...
                             ^

I don't have any more logs.

[lmangani]

@k0rsakov note we only support some of the connection methods, not ClickHouse SQL. For that there's the chsql extension which adds some aliases but not currentUser() as there are no users in DuckDB - but nevertheless your query example is missing a comma before user