> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.
> Provides real-time access to the tables of a database on a remote ClickHouse server, forwarding `SELECT` and `INSERT` queries to it.
# Remote
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`](/docs/reference/engines/database-engines/mysql) and [`PostgreSQL`](/docs/reference/engines/database-engines/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`](/docs/reference/engines/table-engines/special/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.
```sql theme={null}
CREATE DATABASE remote_db
ENGINE = Remote('addresses_expr', 'database'[, 'user'[, 'password']]);
```
`RemoteSecure` connects over a secure TLS connection using the secure TCP port (`tcp_port_secure`, `9440` by default) when the port is omitted.
```sql theme={null}
CREATE DATABASE remote_db
ENGINE = RemoteSecure('addresses_expr', 'database'[, 'user'[, 'password']]);
```
**Engine Parameters**
* `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`](/docs/reference/functions/table-functions/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`](/docs/reference/engines/table-engines/special/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`](/docs/reference/functions/table-functions/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`](/docs/reference/settings/session-settings/insert#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`](/docs/reference/settings/session-settings/optimize-skip#optimize_skip_unused_shards) and [`force_optimize_skip_unused_shards`](/docs/reference/settings/session-settings/force-optimize#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:
```sql theme={null}
CREATE DATABASE remote_db
ENGINE = Remote(my_named_collection, database = 'default');
```
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:
```sql theme={null}
CREATE DATABASE remote_system
ENGINE = Remote('127.0.0.1:9000', 'system', 'default', '');
```
```sql theme={null}
SHOW TABLES FROM remote_system LIKE 'one';
```
```text theme={null}
┌─name─┐
│ one │
└──────┘
```
```sql theme={null}
SELECT * FROM remote_system.one;
```
```text theme={null}
┌─dummy─┐
│ 0 │
└───────┘
```