You can connect to ByteHouse Cloud Data Warehouse Edition through the open-source ClickHouse Go driver, and then perform operations such as data writing and querying. This article introduces the main operation procedure and key operation points for connecting the ByteHouse Cloud Data Warehouse Edition using the ClickHouse Go driver.

As the Go driver for ByteHouse is currently undergoing maintenance and upgrading, you can use the open-source Go driver for ClickHouse to connect to ByteHouse. The API and protocols supported by open-source ClickHouse are as follows.

It is recommended to use the following versions:

• Currently, ByteHouse JSONB and Bitmap64 data types are not supported.
• To resolve other unknown restrictions encountered during usage, please contact the ByteHouse team for assistance.

Download drivers #

Click this link to download the ClickHouse Go basic driver.

Install drivers #

You can install via go get.

go get github.com/ClickHouse/clickhouse-go/v2@v2.30.0

ByteHouse supports connecting to the ClickHouse Go Driver using IAM users or database users. The differences between IAM users and database users are described as follows; you can choose according to your needs.

• IAM user is a user created in BytePlus Access Control (IAM), whose permissions are determined by IAM permission policies and the resource and data permissions for ByteHouse that you grant. IAM users can access the ByteHouse Console, and can also access ByteHouse through CLI, connection drivers, ecosystem tools, API, and many other methods.
• Database user refers to a user created at the database level in ByteHouse who can be granted environment, resource, and data permissions. Database users cannot access the ByteHouse Console, but can access ByteHouse through the CLI, connection drivers, ecosystem tools, API, and so on.

For more information about IAM users and database users, please refer to the following documents:

• Overview
• User Management
• Manage Database Accounts

Use IAM users to connect #

Please refer to步骤三：获取 ByteHouse 连接串信息Learn how to connect to ByteHouse using the IAM user method.
General parameter descriptions are as follows:

Use a database user connection #

Please refer to步骤三：获取 ByteHouse 连接串信息, understand how to connect to ByteHouse using a database user.
General parameter descriptions are as follows:

Connect ByteHouse #

Caution: You can refer to the code below to connect to ByteHouse. When using the code, replace the placeholders {Host}, {Password}, {User}, {Database}, {VIRTUAL_WAREHOUSE_ID}, and other connection information fields in the connection statement. For details on how to obtain these fields, see the documentation.获取 ByteHouse 连接信息。

• Timeout configuration
  • When using the TCP protocol: dial_timeout defaults to 30s, and read_timeout defaults to 300s.
  • When using the HTTP protocol: the default value of dial_timeout is 30s, and the default value of read_timeout is 300s.
• Default support for keepAlive enables connection reuse and prevents short-lived connections.

Use the TCP/Native protocol #

• Use ClickHouse Driver's OpenDB to create a connection

  import (
      "context"
      //"crypto/tls"
      "database/sql"
      "fmt"
      "github.com/ClickHouse/clickhouse-go/v2"
      "github.com/google/uuid"
      "time"
  )
  
  func main() {
      host := "{Host}"
      port := 19000
      password := "{Password}"
      user := "{User}"
      database := "{Database}"
      virtual_warehouse_id := "{VIRTUAL_WAREHOUSE_ID}"
  
      db := clickhouse.OpenDB(&clickhouse.Options{
       Addr: []string{fmt.Sprintf("%s:%d", host, port)},
       Auth: clickhouse.Auth{
          Database: database,
          Username: user,
          Password: password,
       },
       Protocol: clickhouse.Native,
       TLS: &tls.Config{
          InsecureSkipVerify: true,
       },
       Settings: clickhouse.Settings{
          "virtual_warehouse": virtual_warehouse_id,
       },
      })
  

• Create a connection using the Go Database DSN (Data Source Name, data source name)

  import (
      "context"
      //"crypto/tls"
      "database/sql"
      "fmt"
      "github.com/ClickHouse/clickhouse-go/v2"
      "github.com/google/uuid"
      "time"
  )
  
  func main() {
      host := "{Host}"
      port := 19000
      password := "{Password}"
      user := "{User}"
      database := "{Database}"
      virtual_warehouse_id := "{VIRTUAL_WAREHOUSE_ID}"
  
      db, err := sql.Open(
         "clickhouse",
         fmt.Sprintf("clickhouse://%v:%v@%v:%v/%v?secure=true&virtual_warehouse=%v", user, password, host, port, database, virtual_warehouse_id),
      )
     if err != nil {
        log.Fatal(err)
     }
  

Use the HTTP protocol #

When using the HTTP protocol and making global connections, you must add the describe_query_with_data_type_flags=0 parameter.

• Use ClickHouse Driver's OpenDB to create a connection

  import (
      "context"
      //"crypto/tls"
      "database/sql"
      "fmt"
      "github.com/ClickHouse/clickhouse-go/v2"
      "github.com/google/uuid"
      "time"
  )
  
  func main() {
      host := "{Host}"
      port := 8123
      password := "{Password}"
      user := "{User}"
      database := "{Database}"
      virtual_warehouse_id := "{VIRTUAL_WAREHOUSE_ID}"
  
      db := clickhouse.OpenDB(&clickhouse.Options{
       Addr: []string{fmt.Sprintf("%s:%d", host, port)},
       Auth: clickhouse.Auth{
          Database: database,
          Username: user,
          Password: password,
       },
       Protocol: clickhouse.HTTP,
       TLS: &tls.Config{
          InsecureSkipVerify: true,
       },
       Settings: clickhouse.Settings{
          "virtual_warehouse":                   virtual_warehouse_id,
          "describe_query_with_data_type_flags": "0",
       },
      })
  

• Create a connection using Go Database's DSN (Data Source Name, 数据源名称)

  import (
      "context"
      //"crypto/tls"
      "database/sql"
      "fmt"
      "github.com/ClickHouse/clickhouse-go/v2"
      "github.com/google/uuid"
      "time"
  )
  
  func main() {
      host := "{Host}"
      port := 8123
      password := "{Password}"
      user := "{User}"
      database := "{Database}"
      virtual_warehouse_id := "{VIRTUAL_WAREHOUSE_ID}"
  
      db, err := sql.Open(
         "clickhouse",
         fmt.Sprintf("https://%v:%v@%v:%v/%v?secure=true&describe_query_with_data_type_flags=0&virtual_warehouse=%v", user, password, host, port, database, virtual_warehouse_id),
      )
      if err != nil {
         log.Fatal(err)
      }
  

Custom query ID #

You can use Context to set the query ID.

func UseContextSendQueryId(db *sql.DB) {
    queryId, _ := uuid.NewUUID()
    ctx := clickhouse.Context(context.Background(), clickhouse.WithQueryID(fmt.Sprintf("customer_%v", queryId.String())))
    _, err := db.ExecContext(ctx, "DROP DATABASE IF EXISTS bhgotest")
    if err != nil {
       log.Fatal(err)
    }
}

Custom query settings #

func UseContextSendQuerySetting(db *sql.DB) {
    // we can use context to pass settings to a specific API call
    ctx := clickhouse.Context(context.Background(), clickhouse.WithSettings(clickhouse.Settings{
       "max_execution_time": 450,
    }))

    var settingValue uint16
    if err := db.QueryRowContext(ctx, "SELECT getSetting('max_execution_time')").Scan(&settingValue); err != nil {
       log.Fatal(err)
    }
    fmt.Printf("settingValue: %v\n", settingValue)
    if settingValue != 450 {
       panic(fmt.Sprintf("expected setting value to be 450, got %d", settingValue))
    }
}

Complete connection and query sample #

You can use the following code to connect to ByteHouse and manage data.

import (
    "context"
    //"crypto/tls"
    "database/sql"
    "fmt"
    "github.com/ClickHouse/clickhouse-go/v2"
    "github.com/google/uuid"
    "time"
)

func main() {
    host := "{Host}"
    port := 8123 // According to the protocol, select 8123 (TCP) or 19000 (TCP)
    password := "{Password}"
    user := "{User}"
    database := "{Database}"
    virtual_warehouse_id := "{VIRTUAL_WAREHOUSE_ID}"

    //db := clickhouse.OpenDB(&clickhouse.Options{
    // Addr: []string{fmt.Sprintf("%s:%d", host, port)},
    // Auth: clickhouse.Auth{
    //    Database: database,
    //    Username: user,
    //    Password: password,
    // },
    // Protocol: clickhouse.Native,
    // TLS: &tls.Config{
    //    InsecureSkipVerify: true,
    // },
    // Settings: clickhouse.Settings{
    //    "virtual_warehouse": virtual_warehouse_id,
    // },
    //})
    db, err := sql.Open(
       "clickhouse",
       fmt.Sprintf("clickhouse://%v:%v@%v:%v/%v?secure=true&virtual_warehouse=%v", user, password, host, port, database, virtual_warehouse_id),
    )
    if err != nil {
       log.Fatal(err)
    }

    defer db.Close()

    if err := db.Ping(); err != nil {
       log.Fatal(err)
    }

    UseContextSendQueryId(db)
    UseContextSendQuerySetting(db)

    _, err = db.Exec("DROP DATABASE IF EXISTS bhgotest")
    if err != nil {
       log.Fatal(err)
    }

    _, err = db.Exec("CREATE DATABASE IF NOT EXISTS bhgotest")
    if err != nil {
       log.Fatal(err)
    }

    _, err = db.Exec(`
CREATE TABLE IF NOT EXISTS bhgotest.example (
        Col1 UInt8
    , Col2 String
    , Col3 FixedString(3)
    , Col4 UUID
    , Col5 Map(String, UInt8)
    , Col6 Array(String)
    , Col7 Tuple(String, UInt8, Array(Map(String, String))) KV
    , Col8 DateTime
) Engine = CnchMergeTree() ORDER BY tuple()
`)
    if err != nil {
       log.Fatal(err)
    }

    batchInsert, err := db.Begin()
    if err != nil {
       log.Fatal(err)
    }
    batchInsertPrepare, err := batchInsert.Prepare("INSERT INTO bhgotest.example")
    if err != nil {
       log.Fatal(err)
    }
    for i := 0; i < 1000; i++ {
       _, err := batchInsertPrepare.Exec(
          uint8(42),
          "ClickHouse", "Inc",
          uuid.New(),
          map[string]uint8{"key": 1},             // Map(String, UInt8)
          []string{"Q", "W", "E", "R", "T", "Y"}, // Array(String)
          []any{ // Tuple(String, UInt8, Array(Map(String, String)))
             "String Value", uint8(5), []map[string]string{
                {"key": "value"},
                {"key": "value"},
                {"key": "value"},
             },
          },
          time.Now(),
       )
       if err != nil {
          log.Fatal(err)
       }
    }
    batchInsert.Commit()

    row := db.QueryRow("SELECT * FROM bhgotest.example  limit 1")
    var (
       col1             uint8
       col2, col3, col4 string
       col5             map[string]uint8
       col6             []string
       col7             interface{}
       col8             time.Time
    )
    if err := row.Scan(&col1, &col2, &col3, &col4, &col5, &col6, &col7, &col8); err != nil {
       log.Fatal(err)
    }
    print(col1, col2, col3, col4, col5, col6, col7)

    db.Exec("DROP TABLE IF EXISTS bhgotest.example")

}

func UseContextSendQueryId(db *sql.DB) {
    queryId, _ := uuid.NewUUID()
    ctx := clickhouse.Context(context.Background(), clickhouse.WithQueryID(fmt.Sprintf("customer_%v", queryId.String())))
    _, err := db.ExecContext(ctx, "DROP DATABASE IF EXISTS bhgotest")
    if err != nil {
       log.Fatal(err)
    }
}

func UseContextSendQuerySetting(db *sql.DB) {
    // we can use context to pass settings to a specific API call
    ctx := clickhouse.Context(context.Background(), clickhouse.WithSettings(clickhouse.Settings{
       "max_execution_time": 450,
    }))

    var settingValue uint16
    if err := db.QueryRowContext(ctx, "SELECT getSetting('max_execution_time')").Scan(&settingValue); err != nil {
       panic(err)
    }
    fmt.Printf("settingValue: %v\n", settingValue)
    if settingValue != 450 {
       panic(fmt.Sprintf("expected setting value to be 450, got %d", settingValue))
    }
}