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

To connect to ClickHouse, use the following ports:

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

Connection addresses

The connection address depends on where you are connecting from. You can choose an address based on one of the following scenarios:

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

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.

You can view the connection address in the Control Panel.

Connecting to a cluster from the Internet

If you are connecting to a 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.

You can view the connection address in the Control Panel.

View the connection address

1. In the Control Panel, click Products in the top menu 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 a 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> — the 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> — the administrator user password. The password is set 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> — the administrator user password. The password is set 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> — the 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> — the administrator user password. The password is set when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed;
   • <port> — the connection port.

Node.js

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

   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> — the node IP address;
   • <database_name> — the 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> — the administrator user password. The password is set when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed;
   • <port> — the connection port.

Java

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

   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> — the node IP address;
   • <database_name> — the 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> — the administrator user password. The password is set when creating the cluster. After creation, the password cannot be viewed in the Control Panel, but it can be changed;
   • <port> — the connection port.