query remote clickhouse instance

[alanpaulkwan]

Suppose I would want to read foreign Clickhouse data.

If I typed the following:

select * from remote('database:port','database.table','user','password')

If I understand correctly, it just returns "Ok". and doesn't process the query

Did I do something wrong? It seems like potentially desirable behavior to be able to read directly from Clickhouse instances.

[lmangani]

Hello @alanpaulkwan I can perform remote queries to clickhouse servers just fine using the latest release:

# python3 -m chdb "select * from remote('XXXXXXXX:9000','system.disks','default','XXXXXXXXXXX')"

"backups","/backups/",3505403396096,3910140764160,3505403396096,0,"local",0,0,0,0,0,""
"default","/var/lib/clickhouse/",3505403396096,3910140764160,3505403396096,0,"local",0,0,0,0,0,""
"s3","/var/lib/clickhouse/disks/s3_disk/",18446744073709551615,18446744073709551615,18446744073709551615,0,"s3",0,0,0,1,0,""

Same goes for the libchdb bindings, for example in go:

# ./chdbgo -query "select * from remote('XXXXXXXX:9000','system.disks','default','XXXXXXXXXXX')"

"backups","/backups/",3505439924224,3910140764160,3505439924224,0,"local",0,0,0,0,0,""
"default","/var/lib/clickhouse/",3505439924224,3910140764160,3505439924224,0,"local",0,0,0,0,0,""
"s3","/var/lib/clickhouse/disks/s3_disk/",18446744073709551615,18446744073709551615,18446744073709551615,0,"s3",0,0,0,1,0,""

It even works in the public demo 😄

Could you confirm your running environment and installed version?

[alanpaulkwan]

I was using the demo version on Github. My fault, can try in Python later.

[lmangani]

I was using the demo version on Github. My fault, can try in Python later.

Thanks @alanpaulkwan which demo version are you referring to? This should work from everywhere, including the public demo (which I used above, too) as long as credentials are fine. Perhaps this was a secure connection? if so remoteSecure should be used instead of remote

[alanpaulkwan]

ch.fly.dev

I entered your query which obviously shouldn't run, since I don't know the IP or real pw

[lmangani]

Here's a working query you can try using the public clickhouse play server:

select count(*) from remoteSecure('play.clickhouse.com:9440','default.covid','play','play')

[alanpaulkwan]

Let's close this - I got it working on my latest pip install!

[lmangani]

Thanks @alanpaulkwan I understand the report now: the web demo does NOT return full error messages (the library does) and perhaps this was the confusing bit all along. Glad you got this working, and welcome to our community!