Skip to main content
Version: 0.2.0

Client Connection

Document Type: Tutorial

Document structure: tutorial purpose, pre-preparation, step-by-step explanation of principles & examples, and related document recommendations;

Summary:

  1. Which clients or drivers does ByConity provide to interact with other systems
  2. How to download and start these clients after the cluster is deployed

Command line client (CLI)​

Docker Client​

If you have installed Docker, you can directly establish a ByConity connection with the following command:

docker run -it yandex/clickhouse-client --host 127.0.0.1 --port 9000 --user default --password xxxx

All of the following are Optional, subject to availability:

  • --host xxx host IP or domain name
  • --port 9000 tcp port，default as 9000
  • --user default user name
  • --password xxxx password

Clickhouse Client​

ByConity is compatible with clients provided by the ClickHouse open source community.

curl -O 'https://builds.clickhouse.com/master/macos/clickhouse' \
&& chmod a+x ./clickhouse

sudo mv ./clickhouse /usr/local/bin/

  • Build connection：
clickhouse client --host 127.0.0.1 --password xxxx

  • clickhouse client --help You can view the optional parameters, the parameters here and above.

docker run -it yandex/clickhouse-client ... The following parameter names are consistent.

Driver​

The open source drivers in various languages officially provided by Clickhouse can be directly used for ByConity connections.

Python​

clickhouse-connect​

  • clickhouse-connect only support python3
  • installation：pip3 install clickhouse-connect
  • demo code：
import clickhouse_connect

# connect
client = clickhouse_connect.get_client(host='localhost', username='default', password='xxxx')

# query
query_result = client.query('SHOW DATABASES;')
print (query_result.result_set)

clickhouse-driver​

  • Python3 installation：pip3 install clickhouse-driver
  • Python2 installation：pip install clickhouse-driver
  • 示例代码：
from clickhouse_driver import Client
from clickhouse_driver import connect

# connect
client = Client(host='localhost',port =9000, user='default', password='xxxx')

# query
query_result = client.execute('SHOW DATABASES;')
print (query_result)

Go​

use clickhouse-go to connect​

  • Compared with the later ch-go, lickhouse-go is more friendly to Go's type support, and the bottom layer is implemented by ch-go, which is recommended.
  • Execute go get ``github.com/ClickHouse/clickhouse-go/v2 in the project to install
  • demo code:
package main

import (
        "context"
        "log"
        "net"
        "time"

        "github.com/ClickHouse/clickhouse-go/v2"
)

func main() {
        ctx := context.Background()

        // Configure connection parameters
        conn, err := clickhouse.Open(&clickhouse.Options{
                Addr: string[]{"127.0.0.1"},
                Auth: clickhouse.Auth{
                        Database: "my_db",
                        Username: "default",
                        Password: "xxxx",
                },
                DialContext: func(ctx context.Context, addr string) (net.Conn, error) {
                        var d net.Dialer
                        return d.DialContext(ctx, "tcp", addr)
                },
                Settings: clickhouse.Settings{
                        "max_execution_time": 60,
                },
                DialTimeout:      time.Duration(10) * time.Second,
                MaxOpenConns:     5,
                MaxIdleConns:     5,
                ConnMaxLifetime:  time.Duration(10) * time.Minute,
                ConnOpenStrategy: clickhouse.ConnOpenInOrder,
        })
        if err != nil {
                log.Panic(err)
        }

        if err := conn.Ping(ctx); err != nil {
                log.Panic(err)
        }

        // run SQL
        if err := conn.Exec(ctx, "SHOW DATABASES;"); err != nil {
                log.Panic(err)
        }
}

use ch-go to connect​

  • ch-go has better support for Clickhouse data types, and its performance is slightly higher than clickhouse-go.
  • installation：go get ``github.com/ClickHouse/ch-go
  • demo code：
package main

import (
  "context"
  "fmt"

  "github.com/ClickHouse/ch-go"
  "github.com/ClickHouse/ch-go/proto"
)

func main() {
  ctx := context.Background()
  c, err := ch.Dial(ctx, ch.Options{Address: "localhost:9000"})
  if err != nil {
    panic(err)
  }
  var (
    numbers int
    data    proto.ColUInt64
  )
  if err := c.Do(ctx, ch.Query{
    Body: "SELECT number FROM system.numbers LIMIT 500000000",
    Result: proto.Results{
      {Name: "number", Data: &data},
    },
    // OnResult will be called on next received data block.
    OnResult: func(ctx context.Context, b proto.Block) error {
      numbers += len(data)
      return nil
    },
  }); err != nil {
    panic(err)
  }
  fmt.Println("numbers:", numbers)
}

Java​

use clickhouse-jdbc to connect

  • Installation and configuration via maven ：
<dependency>
    <!-- please stop using ru.yandex.clickhouse as it's been deprecated -->
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.2-patch11</version>
    <!-- use uber jar with all dependencies included, change classifier to http for smaller jar -->
    <classifier>all</classifier>
    <exclusions>
        <exclusion>
            <groupId>*</groupId>
            <artifactId>*</artifactId>
        </exclusion>
    </exclusions>
</dependency>

  • demo code：
String url = "jdbc:ch://localhost:8123";
Properties properties = new Properties();
properties.setProperty("max_execution_time", "120");
ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
try (Connection conn = dataSource.getConnection("default", "password");
    Statement stmt = conn.createStatement()) {
    ResultSet rs = stmt.executeQuery("SHOW DATABASES;");
    while(rs.next()) {
        // ...
    }
}

HTTP​

ByConity also provides an HTTP connection method, which can execute SQL with curl or postman. curl example:

curl --location --request POST '127.0.0.1:8123/?max_execution_time=60&default_format=Native' \
--header 'Authorization: Basic xxx' \
--header 'Content-Type: text/plain' \
--data-raw 'SHOW DATABASES;'

  • Encrypt with Basic Auth (username + password), the encrypted result is --header 'Authorization: Basic xxx' above
  • Fill in the SQL to be executed directly in the Body, corresponding to --data-raw 'SHOW DATABASES;' above
  • The default port number of the HTTP interface is 8123
  • Example parameters:
  • max_execution_time=60 specifies the maximum waiting time for the query (seconds)
  • By default, the returned data is in TabSeparated format, and default_format can specify a default format other than TabSeparated

Reference Documentation​