1. Home
  2. Knowledge Base
  3. ClickHouse
  4. Access Remote Server in ClickHouse
  1. Home
  2. Knowledge Base
  3. ClickHouse DBA
  4. Access Remote Server in ClickHouse

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>])


  • 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.


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 is reached via the “default” user.

SELECT count()
FROM remote('', recoDB, opensky, 'default', 'your_password')
│ 11348846 │

Insert Data Into a Remote Table

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

-- Remote Server IP :
-- 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

│       0 │

Insert data to this table from the local ClickHouse server.

-- Run the following command in local server

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

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

│ 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      :
-- Destination Node :
-- Run the command in destination node
INSERT INTO default.opensky SELECT *
FROM remote('', recoDB.opensky, 'default', 'hedelek123')

Command in remote node:

-- Source Node : 
-- Destination Node :

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


Was this article helpful?

Related Articles


In the spirit of freedom, independence and innovation. ChistaDATA Corporation is not affiliated with ClickHouse Corporation 

Need Support?

Can't find the answer you're looking for?
Contact Support

ChistaDATA Inc. Knowledge base is licensed under the Apache License, Version 2.0 (the “License”)

Copyright 2022 ChistaDATA Inc

Licensed under the Apache License, Version 2.0 (the “License”); you may not use this file except in compliance with the License. You may obtain a copy of the License at


Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an “AS IS” BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.