Connecting to a database in a ClickHouse cluster

curl --cacert /usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt \
     -H "X-ClickHouse-User: <DB user name>" \
     -H "X-ClickHouse-Key: <DB user password>" \
     'https://<FQDN of any ClickHouse host>:8443/?database=<DB name>&query=SELECT%20version()'

sudo apt update && sudo apt install -y python3 python3-pip && \
pip3 install requests

import requests

url = 'https://{host}:8443/?database={db}&query={query}'.format(
        host='<FQDN of any ClickHouse host>',
        db='<DB name>',
        query='SELECT version()')

auth = {
        'X-ClickHouse-User': '<DB username>',
        'X-ClickHouse-Key': '<DB user password>',
    }

cacert = '/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt'

rs = requests.get(url, headers=auth, verify=cacert)
rs.raise_for_status()

print(rs.text)

python3 connect.py

<?php
  $host = '<FQDN of any ClickHouse host>';
  $db = '<DB name>';
  $query = 'SELECT version()';

  $auth = [
      'X-ClickHouse-User: <DB username>',
      'X-ClickHouse-Key: <DB user password>',
  ];

  $ssl = [
      'cafile' => '/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt',
      'verify_peer' => true,
  ];

  $context = stream_context_create([
      'http' => [
          'method' => 'GET',
          'protocol_version' => 1.1,
          'header' => $auth
      ],
      'ssl' => $ssl
  ]);

  $url = sprintf('https://%s:8443/?database=%s&query=%s', $host, $db, urlencode($query));

  $rs = file_get_contents($url, false, $context);
  print_r($rs);
?>

php connect.php

package com.example;

import java.sql.*;

public class App {
  public static void main(String[] args) {
    String DB_HOST    = "<FQDN of any ClickHouse host>";
    String DB_NAME    = "<DB name>";
    String DB_USER    = "<DB username>";
    String DB_PASS    = "<DB user password>";

    String CACERT     = "/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt";

    String DB_URL = String.format("jdbc:clickhouse://%s:8443/%s?ssl=1&sslmode=strict&sslrootcert=%s", DB_HOST, DB_NAME, CACERT);

    try {
      Class.forName("ru.yandex.clickhouse.ClickHouseDriver");

      Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
      ResultSet rs = conn.createStatement().executeQuery("SELECT version()");
      if(rs.next()) {System.out.println(rs.getString(1));}

      conn.close();
    }
    catch(Exception ex) {ex.printStackTrace();}
  }
}  

mvn clean package && \
java -jar target/app-0.1.0-jar-with-dependencies.jar

sudo apt update && sudo apt install -y nodejs npm && \
npm install querystring

"use strict"
const https = require('https');
const querystring = require('querystring');
const fs = require('fs');

const DB_HOST = "<FQDN of any ClickHouse host>";
const DB_NAME = "<DB name>";
const DB_USER = "<DB username>";
const DB_PASS = "<DB user password>";

const CACERT = "/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt";

const options = {
    'method': 'GET',
    'ca': fs.readFileSync(CACERT),
    'path': '/?' + querystring.stringify({
        'database': DB_NAME,
        'query': 'SELECT version()',
    }),
    'port': 8443,
    '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();

node app.js

sudo apt update && sudo apt install -y golang git

package main

import (
    "fmt"
    "net/http"
    "io/ioutil"
    "crypto/x509"
    "crypto/tls"
)

func main() {

    const DB_HOST = "<FQDN of any ClickHouse host>"
    const DB_NAME = "<DB name>"
    const DB_USER = "<DB username>"
    const DB_PASS = "<DB user password>"

    const CACERT = "/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.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:8443/", 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))
}

go run connect.go

sudo apt update && sudo apt install -y ruby

require "net/http"
require "openssl"
require "uri"

DB_HOST = "<FQDN of any ClickHouse host>"
DB_NAME = "<DB name>"
DB_USER = "<DB username>"
DB_PASS = "<DB user password>"

QUERYSTRING = { :database => DB_NAME, :query => "SELECT version()" }

uri = URI("https://" + DB_HOST + "/")
uri.port = 8443
uri.query = URI.encode_www_form(QUERYSTRING)

req = Net::HTTP::Get.new(uri)
req.add_field("X-ClickHouse-User", DB_USER)
req.add_field("X-ClickHouse-Key", DB_PASS)

conn = Net::HTTP.new(uri.host, uri.port)
conn.ca_file = "/usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt"
conn.use_ssl = true
conn.verify_mode = OpenSSL::SSL::VERIFY_PEER

rs = conn.request(req)
puts rs.body

ruby connect.rb

[ClickHouse]
Driver = ClickHouse ODBC Driver (Unicode)
Server = <FQDN of any ClickHouse host>
Database = <DB name>
UID = <DB username>
PWD = <DB user password>
Port = 8443
Proto = https
SSLMode = allow
CertificateFile = /usr/local/share/ca-certificates/Yandex/YandexInternalRootCA.crt

isql -v ClickHouse