The Remote and RemoteSecure database engines provide real-time access to the tables of a database on a remote ClickHouse server over the native TCP protocol. They are the ClickHouse-to-ClickHouse counterparts of the MySQL and PostgreSQL database engines.
The list of tables and their structure are fetched from the remote server on demand (using SHOW TABLES and DESCRIBE TABLE under the hood), so the database always reflects the current state of the remote server. Each table is exposed as a Distributed storage over an ad-hoc cluster built from the supplied addresses, which forwards SELECT and INSERT queries to the remote server.
This is handy for federating several ClickHouse clusters or for plugging a larger ClickHouse cluster into clickhouse-local or a smaller cluster.
Creating a database
Remote connects over the plain TCP port (tcp_port, 9000 by default) when the port is omitted.RemoteSecure connects over a secure TLS connection using the secure TCP port (tcp_port_secure, 9440 by default) when the port is omitted.
addresses_expr — A remote server address or an expression that generates several addresses, in the form host or host:port. The address expression supports the same globbing patterns as the remote table function (for example {a,b,c}, {N..M} and {a|b} to expand into multiple shards and replicas). When the port is omitted, Remote uses the plain TCP port (tcp_port, 9000 by default) and RemoteSecure uses the secure TCP port (tcp_port_secure, 9440 by default).database — The name of the database on the remote server.user — The remote user name. Optional, default: default.password — The remote user password. Optional, default: empty.
The addresses and credentials are stored in the database definition, so the password is hidden in SHOW CREATE DATABASE. As with the remote table function, an address that points to the current server is treated as a local shard: SELECT and INSERT are executed directly under the current user — who therefore needs the corresponding privileges on the underlying database and its tables — and the stored credentials are used only for genuinely remote servers. If the local replica of a shard does not have the database or a table, the lookup falls back to the remote replicas of the shard, like a Distributed table does. In that case SHOW CREATE TABLE prints the effective fallback addresses (the local replicas stripped from their shards) instead of the configured ones, so the emitted Remote(...) table definition reconstructs the object that actually serves the queries.
When the address expression describes several shards, each proxy table reads from all of them, but the metadata — the list of the tables and their structure — is taken from an arbitrary shard (a local one is preferred), just like the remote table function does, so that a listing costs a single query instead of one per shard. The shards of a cluster are therefore expected to serve the same set of tables; a table that only some of them have is served by a proxy whose queries then fail on the shards that do not have it. An INSERT into a table of a multi-shard database sends each row to a random shard (the proxy Distributed tables carry an implicit rand() sharding key); to pin the shard for a query, set insert_shard_id. The implicit key only distributes the inserted rows: for reading, the table behaves like a Distributed table without a sharding key (in particular, optimize_skip_unused_shards and force_optimize_skip_unused_shards do not treat it as a shard-pruning key). SHOW CREATE TABLE includes the key in the emitted Remote(...) table definition, so a table recreated from it accepts multi-shard INSERT queries as well.
Named collections are supported as well:
Notes
- The engine is a read-through view of the remote server:
CREATE TABLE, DROP TABLE, ALTER and similar DDL statements against the Remote database are not supported. Manage the schema on the remote server directly.
- Access rights are enforced on the remote server for the configured remote user, and locally by the usual privileges on the database and its tables.
- A table of a local shard that the user is not allowed to see is reported as missing rather than as forbidden, so a
Remote database cannot be used to probe the table names of a local database the user has no privileges on. This applies to listing (SHOW TABLES, EXISTS TABLE) as well as to resolution (DESCRIBE TABLE, SHOW CREATE TABLE, SELECT), and such a table is not served through the remote replicas of its shard either: the fallback described above engages only when the local replica genuinely does not have the table.
- Listing the tables of a database that exists on the local replica of a shard also includes the tables that only the remote replicas of that shard have, so that
SHOW TABLES and system.tables agree with EXISTS TABLE, DESCRIBE TABLE and SELECT, which fall back to those replicas. When none of the remote replicas answers, the list of the local replica is returned as it is, because it is already the answer of an available replica.
- If the remote server is unavailable, listing its tables (
SHOW TABLES, system.tables) reports the connection error instead of an empty list of tables, as EXISTS TABLE and SELECT on the same database do. Note that a SELECT from system.tables covering all databases fails as well while such a database is unreachable.
- A
Remote database may point to another Remote database on the same server. Listing and describing the tables of such a chain needs no privileges on the intermediate database — it holds neither data nor metadata of its own, and every hop already checks the caller’s rights on the objects that it proxies in turn. Reading and writing the data, in contrast, needs SELECT / INSERT on every hop of the chain, because the query is really executed against the table of the intermediate database, exactly like for a Distributed table over another Distributed table. The visibility rule described above survives the chain: a table that the intermediate database hides from the caller is not served through the remote replicas of the outer database either. If the intermediate database on the local replica cannot reach its own target, the local replica of the outer shard cannot answer at all — exactly as if the replica itself were down — and the outer database falls back to the remote replicas of the shard.
Example
Create a Remote database that points to the system database of a remote server and read from it:
Last modified on August 12, 2026
