Access Remote Server in ClickHouse

In ClickHouse, it is possible to access a remote database without creating a distributed table. For that reason, “remote” or “remoteSecure” functions can be used. “remoteSecure” is the same as “remote” but with a secure connection. With the help of these functions, it is possible to migrate tables to another server, insert data into a remote database, and so on.

Syntax of this command is as follows:(“remoteSecure“‘s syntax same as “remote“)

remote('remote_server_address', <DB>, <TABLE>[, '<USERNAME>'[, '<PASSWORD>'], <SHARDING KEY>])
remote('remote_server_address', <DB>.<TABLE>[, '<USERNAME>'[, '<PASSWORD>'], <SHARDING KEY>])

Here:

• remote_server_address: IP(Hostname) or IP(Hostname):Port of the remote server.
• DB : Database@Remote Server.
• TABLE: Table@Remote Server.
• USERNAME: Username@Remote Server, if not specified “default” user is used.
• PASSWORD : Password for the specified user. If not specified empty password is used.
• SHARDING KEY: Sharding key for distributing data across nodes.

Examples

You can find the usage examples as follows.

Selecting Data From Remote Server

You can directly select data from the remote node. In the example below, the “opensky” table in the “recoDB” database at server 192.168.4.2 is reached via the “default” user.

SELECT count()
FROM remote('192.168.64.2', recoDB, opensky, 'default', 'your_password')
┌──count()─┐
│ 11348846 │
└──────────┘

Insert Data Into a Remote Table

Let’s assume there is a “remote_test” table in remote server.

-- Remote Server IP :192.168.64.2
-- Table in remote server:
CREATE TABLE default.remote_test
(
    `name` String,
    `id` UInt32
)
ENGINE = MergeTree
ORDER BY tuple()

-- Table is empty right now

SELECT count()
FROM default.remote_test

┌─count()─┐
│       0 │
└─────────┘

Insert data to this table from the local ClickHouse server.

-- Run the following command in local server

INSERT INTO FUNCTION remote('192.168.64.2',default.remote_test,'default','your_password') values ('ChistaDATA',1);

-- Select remote table if the value is inserted or not
SELECT *
FROM remote('192.168.64.2', default, remote_test, 'default', 'your_password')

┌─name───────┬─id─┐
│ ChistaDATA │  1 │
└────────────┴────┘

Migrate Table to New Server

With the help of remote command, it is also possible to migrate full table to another server. Let’s assume there is a table called “recoDB.opensky” in remote server and we would like to migrate this table to the local ClickHouse server.

Here is the steps:

• Create  table in local server. You can find CREATE TABLE statement with “show create table <DBNAME>.<TABLENAME> in remote node.
• Run the insert command with “remote” function. You can run insert command either from local node(destination node) or remote node(source node).

Command in local node:

-- Source Node      : 192.168.64.2
-- Destination Node : 192.168.64.7
-- Run the command in destination node
INSERT INTO default.opensky SELECT *
FROM remote('192.168.64.2', recoDB.opensky, 'default', 'hedelek123')

Command in remote node:

-- Source Node : 192.168.64.2 
-- Destination Node : 192.168.64.7

-- Run the command in source node
INSERT INTO FUNCTION remote('192.168.64.7', default.opensky, 'default', 'your_password') SELECT * FROM recoDB.opensky

References

• https://clickhouse.com/docs/en/sql-reference/table-functions/remote/