This article explains how to connect to and access the ByteHouse cloud data warehouse in the Go development environment.

• ByteHouse does not support the JSONB and Bitmap64 data types.
• Currently, the ByteHouse MySQL protocol does not support the ComPrepare protocol, so the insert batch logic cannot use the prepare functionality in MySQL Go and can only construct SQL statements by string concatenation.
• Currently, authentication of database accounts is not supported.
• Setting query ID is not supported.
• Setting a virtual warehouse at the connection level (sql.Open) is not supported.
• To address any other unknown restrictions encountered during use, please contact the ByteHouse team for processing.

You can obtain the latest documentation and information about release versions at the program's Github homepage.

go get -u github.com/go-sql-driver/mysql

-- 以下为依赖程序
go get -u github.com/stretchr/testify

After installation is complete, insert the following content in the import section of the Go program code.

import (
        "database/sql"

        _ "github.com/go-sql-driver/mysql"
)

ByteHouse supports connection through IAM users, and the method for obtaining the required connection information is as follows. For more connection operations, please refer to步骤三：获取 ByteHouse 连接串信息。

You can use the following code to connect to ByteHouse and start using standard statements to develop on ByteHouse for querying, writing, and reading data.

• Timeout configuration: The default value for timeout is 0s, the default value for readTimeout is 0s, and the default value for writeTimeout is 0s.
• Default support for keepAlive enables connection reuse and prevents short-lived connections.

Connect to ByteHouse #

You can refer to the code example below to fill in the ByteHouse connection information. Caution: Fill in the values of fields such as {Host}, {Password}, {User}, {Database}, and {VIRTUAL_WAREHOUSE_ID} according to the information obtained. For details on how to obtain this information, see获取 ByteHouse 连接信息。

host := "{Host}"
port := 3306
password := "{Password}"
user := "{User}"
database := "{Database}"
virtual_warehouse_id := "{VIRTUAL_WAREHOUSE_ID}"

db, err := sql.Open(
   "mysql", 
   fmt.Sprintf("%v:%v@(%v:%v)/%v", user, password, host, port, database),
)
if err != nil {
   log.Fatal(err)
}

Set a specific virtual warehouse (connection level) #

After creating each connection, set the virtual warehouse configuration.

_, err = conn.ExecContext(ctx, fmt.Sprintf("set virtual_warehouse = '%v'", virtual_warehouse_id))
    if err != nil {
        log.Fatal(err)
    }

Basic usage samples #

package main

import (
    "context"
    "database/sql"
    "fmt"
    "github.com/go-sql-driver/mysql"
    _ "github.com/go-sql-driver/mysql"
    "github.com/google/uuid"
    "log"
    "strconv"
    "strings"
    "time"
)

func main() {
    host := "{Host}"
    port := 3306
    password := "{Password}"
    user := "{User}"
    database := "{Database}"
    virtual_warehouse_id := "{VIRTUAL_WAREHOUSE_ID}"

    db, err := sql.Open(
       "mysql", 
       fmt.Sprintf("%v:%v@(%v:%v)/%v", user, password, host, port, database),
    )
    if err != nil {
       log.Fatal(err)
    }

    if err := db.Ping(); err != nil {
       log.Fatal(err)
    }
    ctx := context.Background()
    conn, err := db.Conn(context.Background())
    if err != nil {
       log.Fatal(err)
    }
    defer conn.Close()
    defer db.Close()
    
    _, err = conn.ExecContext(ctx, fmt.Sprintf("set virtual_warehouse = '%v'", virtual_warehouse_id))
    if err != nil {
        log.Fatal(err)
    }
    
    _, err = conn.ExecContext(ctx, "DROP DATABASE IF EXISTS bhgotest")
    if err != nil {
       log.Fatal(err)
    }

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

    _, err = conn.ExecContext(ctx, `
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)
    }

    values_list := make([]string, 0)
    for i := 0; i < 1000; i++ {
       value := fmt.Sprintf("(%v,%v,%v,'%v',%v,%v,%v,'%v')", uint8(42),
          "'ClickHouse'", "'Inc'",
          uuid.New(),
          "{'key': 1}",                     // Map(String, UInt8)
          "['Q', 'W', 'E', 'R', 'T', 'Y']", // Array(String)
          "('String Value', 5, [{'key': 'value'},{'key': 'value'},{'key': 'value'}])",
          time.Now().Format("2006-01-02 15:04:05"))
       values_list = append(values_list, value)
    }

    sql := fmt.Sprintf("INSERT INTO bhgotest.example VALUES %s",
       strings.Join(values_list, ", "))
    _, err = conn.ExecContext(ctx, sql)
    if err != nil {
       log.Fatal(err)
    }

    row := conn.QueryRowContext(ctx, "SELECT * FROM bhgotest.example  limit 1")
    var (
       col1             uint8
       col2, col3, col4 string
       col5             string
       col6             string
       col7             string
       col8             string
    )
    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, col8)

    conn.ExecContext(ctx, "DROP TABLE IF EXISTS bhgotest.example")

}