Connect to a ClickHouse® cluster

You can connect to a ClickHouse® cluster:

• via console clients, for example clickhouse-client;
• program code.

SSL connection is used for all methods.

When connecting, specify the port and address.

Connection ports

Use the following ports to connect to ClickHouse®:

• 9440 — port for connecting via native interface (TCP);
• 8443 — port for connecting via HTTP interface.

Connection addresses

You can select an address for connection depending on one of the scenarios:

• connecting to a cluster from a private subnet;
• connecting to a cluster from the Internet.

You can view the connection address in the Dashboard.

Connecting to a cluster from a private subnet

If you are connecting to a cluster from a private subnet, use the private IP address.

To connect from another private subnet, first connect both private subnets to a cloud router.

Connecting to a cluster from the Internet

If you are connecting to the cluster from the Internet, use a public IP address. The private subnet must meet the requirements. If the subnet does not meet the requirements, prepare it for a public IP address connection.

View connection address

1. In the Dashboard, from the top menu, click Products and select Managed Databases.
2. Open the Active tab.
3. Open the database cluster page → Connection tab.
4. In the Connection addresses block, open the tab for the node group whose addresses you want to view.

Connect to the cluster

clickhouse-client

1. Create a config.xml configuration file:

   if [ "$USER" = "root" ]; then
       CONFIG_DIR="/root/.clickhouse-client"
   else
       CONFIG_DIR="/home/${USER}/.clickhouse-client"
   fi
   
   mkdir -p ${CONFIG_DIR}
   
   cat > ${CONFIG_DIR}/config.xml << EOF
   <config>
       <openSSL>
           <client>
               <caConfig>${CONFIG_DIR}/root.crt</caConfig>
               <verificationMode>strict</verificationMode>
               <invalidCertificateHandler>
                   <name>RejectCertificateHandler</name>
               </invalidCertificateHandler>
           </client>
       </openSSL>
   </config>
   EOF

2. Download the root certificate and place it in the ~/.clickhouse-client/ folder:

   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.clickhouse-client/root.crt
   chmod 0600 ~/.clickhouse-client/root.crt

3. Connect to the node:

   clickhouse-client --host <host> \
                     --secure \
                     --user admin \
                     --database <database_name> \
                     --port <port> \
                     --password <password>

   Specify:

   • <host> — node IP address; ;
   • <database_name> — database name. When connecting to the cluster for the first time, specify the default database — default. After the first connection, you can create new databases;
   • <port> — connection port;
   • <password> — administrator password. You set the password when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed.

Python

1. Download the root certificate and place it in the ~/.clickhouse/ folder:

   mkdir -p ~/.clickhouse/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.clickhouse/root.crt
   chmod 0600 ~/.clickhouse/root.crt

2. Install the clickhouse-driver library:

   pip3 install clickhouse-driver

3. Connect to the node:

   from clickhouse_driver import Client
   
   client = Client(host='<host>',
                   user='admin',
                   password='<password>',
                   port=<port>,
                   secure=True,
                   verify=True,
                   ca_certs='/root/.clickhouse/root.crt')
   
   print(client.execute('SELECT version()'))

   Specify:

   • <host> — node IP address; ;
   • <password> — administrator password. You set the password when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed;
   • <port> — connection port.

Go

1. Download the root certificate and place it in the ~/.clickhouse/ folder:

   mkdir -p ~/.clickhouse/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.clickhouse/root.crt
   chmod 0600 ~/.clickhouse/root.crt

2. Connect to the node:

   package main
   
   import (
       "fmt"
       "net/http"
       "io/ioutil"
       "crypto/x509"
       "crypto/tls"
   )
   
   func main() {
   
       const DB_HOST = "<host>"
       const DB_NAME = "<database_name>"
       const DB_USER = "admin"
       const DB_PASS = "<password>"
   
       const CACERT = "/root/.clickhouse/root.crt";
   
       caCert, err := ioutil.ReadFile(CACERT)
       if err != nil {
           panic(err)
       }
       caCertPool := x509.NewCertPool()
       caCertPool.AppendCertsFromPEM(caCert)
       conn := &http.Client{
           Transport: &http.Transport{
           TLSClientConfig: &tls.Config{
               RootCAs: caCertPool,
           },
       },
       }
   
       req, _ := http.NewRequest("GET", fmt.Sprintf("https://%s:<port>/", DB_HOST), nil)
       query := req.URL.Query()
       query.Add("database", DB_NAME)
       query.Add("query", "SELECT version()")
   
       req.URL.RawQuery = query.Encode()
   
       req.Header.Add("X-ClickHouse-User", DB_USER)
       req.Header.Add("X-ClickHouse-Key", DB_PASS)
   
       resp, err := conn.Do(req)
       if err != nil {
           panic(err)
       }
   
       defer resp.Body.Close()
   
       data, _ := ioutil.ReadAll(resp.Body)
       fmt.Println(string(data))
   }

   Specify:

   • <host> — node IP address; ;
   • <database_name> — database name. When connecting to the cluster for the first time, specify the default database — default. After the first connection, you can create new databases;
   • <password> — administrator password. You set the password when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed;
   • <port> — connection port.

Node.js

1. Download the root certificate and place it in the ~/.clickhouse/ folder:

   mkdir -p ~/.clickhouse/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.clickhouse/root.crt
   chmod 0600 ~/.clickhouse/root.crt

2. Install dependencies:

   npm install querystring

3. Connect to the node:

   "use strict"
   const https = require('https');
   const querystring = require('querystring');
   const fs = require('fs');
   
   const DB_HOST = "<host>";
   const DB_NAME = "<database_name>";
   const DB_USER = "admin";
   const DB_PASS = "<password>";
   
   const CACERT = "/root/.clickhouse/root.crt";
   
   const options = {
       'method': 'GET',
       'ca': fs.readFileSync(CACERT),
       'path': '/?' + querystring.stringify({
           'database': DB_NAME,
           'query': 'SELECT version()',
       }),
       'port': <port>,
       'hostname': DB_HOST,
       'headers': {
           'X-ClickHouse-User': DB_USER,
           'X-ClickHouse-Key': DB_PASS,
       },
   };
   
   const rs = https.request(options, (res) => {
       res.setEncoding('utf8');
       res.on('data', (chunk) => {
           console.log(chunk);
       });
   });
   
   rs.end();

   Specify:

   • <host> — node IP address; ;
   • <database_name> — database name. When connecting to the cluster for the first time, specify the default database — default. After the first connection, you can create new databases;
   • <password> — administrator password. You set the password when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed;
   • <port> — connection port.

Java

1. Download the root certificate and place it in the ~/.clickhouse/ folder:

   mkdir -p ~/.clickhouse/
   wget https://storage.dbaas.selcloud.ru/CA.pem -O ~/.clickhouse/root.crt
   chmod 0600 ~/.clickhouse/root.crt

2. Install dependencies:

   sudo apt update && sudo apt install --yes default-jdk maven

3. Create a configuration file for Maven:

   pom.xml

   <?xml version="1.0" encoding="UTF-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
           xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   
       <modelVersion>4.0.0</modelVersion>
   
       <groupId>com.example</groupId>
       <artifactId>app</artifactId>
       <version>0.1.0</version>
       <packaging>jar</packaging>
   
       <properties>
           <maven.compiler.source>1.8</maven.compiler.source>
           <maven.compiler.target>1.8</maven.compiler.target>
           <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       </properties>
   
       <dependencies>
           <dependency>
               <groupId>com.clickhouse</groupId>
               <artifactId>clickhouse-jdbc</artifactId>
               <version>0.9.8</version>
           </dependency>
   
           <dependency>
               <groupId>org.slf4j</groupId>
               <artifactId>slf4j-simple</artifactId>
               <version>1.7.36</version>
           </dependency>
       </dependencies>
   
       <build>
           <sourceDirectory>src</sourceDirectory>
           <finalName>${project.artifactId}-${project.version}</finalName>
   
           <plugins>
               <plugin>
                   <groupId>org.apache.maven.plugins</groupId>
                   <artifactId>maven-compiler-plugin</artifactId>
                   <version>3.11.0</version>
                   <configuration>
                       <source>1.8</source>
                       <target>1.8</target>
                   </configuration>
               </plugin>
   
               <plugin>
                   <groupId>org.apache.maven.plugins</groupId>
                   <artifactId>maven-assembly-plugin</artifactId>
                   <version>3.7.1</version>
   
                   <configuration>
                       <descriptorRefs>
                           <descriptorRef>jar-with-dependencies</descriptorRef>
                       </descriptorRefs>
   
                       <archive>
                           <manifest>
                               <mainClass>com.example.App</mainClass>
                           </manifest>
                       </archive>
                   </configuration>
   
                   <executions>
                       <execution>
                           <id>make-assembly</id>
                           <phase>package</phase>
                           <goals>
                               <goal>single</goal>
                           </goals>
                       </execution>
                   </executions>
               </plugin>
           </plugins>
       </build>
   
   </project>

4. Connect to the node:

   package com.example;
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   
   public class App {
       public static void main(String[] args) {
           String DB_HOST = "<host>";
           String DB_NAME = "<database_name>";
           String DB_USER = "admin";
           String DB_PASS = "<password>";
   
           String CACERT = "/root/.clickhouse/root.crt";
   
           String DB_URL = String.format(
               "jdbc:clickhouse://%s:<port>/%s?ssl=true&sslrootcert=%s",
               DB_HOST,
               DB_NAME,
               CACERT
           );
   
           try {
               Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
   
               Connection conn = DriverManager.getConnection(
                   DB_URL,
                   DB_USER,
                   DB_PASS
               );
   
               ResultSet rs = conn.createStatement().executeQuery(
                   "SELECT version()"
               );
   
               if (rs.next()) {
                   System.out.println("ClickHouse version: " + rs.getString(1));
               }
   
               rs.close();
               conn.close();
           } catch (Exception ex) {
               ex.printStackTrace();
           }
       }
   }

   Specify:

   • <host> — node IP address; ;
   • <database_name> — database name. When connecting to the cluster for the first time, specify the default database — default. After the first connection, you can create new databases;
   • <password> — administrator password. You set the password when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed;
   • <port> — connection port.

ClickHouse® is a registered trademark of ClickHouse, Inc. https://clickhouse.com.