Is it possible to have foreign schema in ClickHouse, to query other database inside your own?

Question

In PostgreSQL there is postgres_fdw extension, which allows to add a foreign schema to your own database, like a view which makes it possible to get data from another Postgres making queries to it right inside you own DB.

Does ClickHouse have some kind of the same functionality?

For example - I've recently migrated several schemas from one ClickHouse instance to another one, but I have a query which inserts some data in a INSERT INTO SELECT way, and the schema with the table which is in SELECT statement wasn't migrated, so I can't directly query it anymore.

I want to find some elegant solution to make it possible to save the same INSERT INTO SELECT logic, or at least that I don't need to use dataframes as intermediate data storage for inserting data from another db. I know there is a remote command which makes it possible to query another DB directly, but as far as I understand, it needs to have credentials hardcoded in the SQL, which isn't the best way to deal with it as my code is stored in GitHub. Maybe it is possible to use this remote but in another way?

I have ClickHouse 23.9.

Answer 1

I know there is a remote command which makes it possible to query another DB directly, but as far as I understand, it needs to have credentials hardcoded in the SQL

use remote and remoteSecure table functions with combination with named collections

look documentation

https://clickhouse.com/docs/en/operations/named-collections#named-collections-for-accessing-a-remote-clickhouse-database

Answer 2

Check this out - it uses remoteSecure to create a local table proxy_uk_prices that reads from a ClickHouse Cloud table named uk_prices_paid:

CREATE TABLE proxy_uk_prices 
   AS 
   remoteSecure(
       'abcd.us-west-2.aws.clickhouse.cloud', 
       default, 
       uk_price_paid,
       'default', 
       'password'
)