Go, also known as Golang, is a statically-typed, concurrent programming language created by Google. ClickHouse is a high-performance, column-oriented database management system that can be used for real-time data analysis.

This tutorial will provide a deep dive into how to connect to ClickHouse from a Go program, including how to perform common database operations such as SELECT and INSERT statements.

Before proceeding, it is assumed that you already have Go and ClickHouse installed on your machine. If not, you can download the latest version of Go from the official website and install it by following the instructions provided. You can also download and install ClickHouse from the official website.

To get started, we need to import the Go library for connecting to databases. The library we will be using in this tutorial is "github.com/kshvakov/clickhouse." To install it, run the following command in your terminal or command prompt:

go get github.com/kshvakov/clickhouse

import (
  "github.com/kshvakov/clickhouse"
)

clickhouse.Open
db, err := clickhouse.Open("tcp://127.0.0.1:9000?database=test_db&username=default&password=")
if err != nil {
  panic(err)
}
defer db.Close()

Once we have established a connection, we can start executing SQL statements against the database.

Let's start by creating a table in the database:

_, err = db.Exec(`
  CREATE TABLE users (
    id UInt64,
    name String,
    age UInt8
  ) ENGINE = Memory
`)
if err != nil {
  panic(err)
}

In this example, we are creating a table named "users" with three columns: "id", "name", and "age". The table is stored in memory for the purpose of this tutorial, but you can change the engine to any of the available engines supported by ClickHouse, such as MergeTree, ReplacingMergeTree, or CollapsingMergeTree.

Next, let's insert some data into the "users" table:

_, err = db.Exec(`
  INSERT INTO users (id, name, age)
  VALUES (1, 'John Doe', 35), (2, 'Jane Doe', 32)
`)
if err != nil {
  panic(err)
}

Now that we have data in the "users" table, let's retrieve it by executing a SELECT statement:

rows, err := db.Query(`
  SELECT id, name, age FROM users
`)
if err != nil {
  panic(err)
}
defer rows.Close

Next, we will iterate over the rows returned by the SELECT statement and print the results:

for rows.Next() {
  var id uint64
  var name string
  var age uint8
  if err := rows.Scan(&id, &name, &age); err != nil {
    panic(err)
  }
  fmt.Printf("%d, %s, %d\n", id, name, age)
}

rows.Scan

The above code should produce the following output:

1, John Doe, 35
2, Jane Doe, 32

The complete code may look like:

package main

import (
	"bufio"
	"database/sql"
	"fmt"
	"github.com/kshvakov/clickhouse"
	"os"
)

func main() {
	// Connect to the ClickHouse server
	dsn := "tcp://127.0.0.1:9000?debug=true"
	db, err := sql.Open("clickhouse", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// Create the "users" table
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS users (
			id UInt64,
			name String,
			age UInt8
		) ENGINE = Memory
	`)
	if err != nil {
		panic(err)
	}

	// Insert data into the table using a bulk insert
	w := bufio.NewWriter(clickhouse.NewWriteTo(
		db,
		"INSERT INTO users (id, name, age) VALUES",
		1000,
	))
	defer w.Flush()

	fmt.Fprintln(w, "1, 'John Doe', 35")
	fmt.Fprintln(w, "2, 'Jane Doe', 32")

	// Retrieve the data from the table using a SELECT statement and stream the results
	rows, err := db.Query("SELECT * FROM users")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	// Iterate over the rows returned by the SELECT statement and print the results
	for rows.Next() {
		var id uint64
		var name string
		var age uint8
		if err := rows.Scan(&id, &name, &age); err != nil {
			panic(err)
		}
		fmt.Printf("%d, %s, %d\n", id, name, age)
	}
}

If using prepared statements, it would look like:

package main

import (
	"database/sql"
	"fmt"
	"github.com/kshvakov/clickhouse"
)

func main() {
	// Connect to the ClickHouse server
	dsn := "tcp://127.0.0.1:9000?debug=true"
	db, err := sql.Open("clickhouse", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// Create the "users" table
	_, err = db.Exec(`
		CREATE TABLE IF NOT EXISTS users (
			id UInt64,
			name String,
			age UInt8
		) ENGINE = Memory
	`)
	if err != nil {
		panic(err)
	}

	// Insert data into the table using a prepared statement
	stmt, err := db.Prepare(`INSERT INTO users (id, name, age) VALUES (?, ?, ?)`)
	if err != nil {
		panic(err)
	}
	defer stmt.Close()

	_, err = stmt.Exec(1, "John Doe", 35)
	if err != nil {
		panic(err)
	}
	_, err = stmt.Exec(2, "Jane Doe", 32)
	if err != nil {
		panic(err)
	}

	// Retrieve the data from the table using a prepared SELECT statement and retrieve the results
	stmt, err = db.Prepare("SELECT * FROM users")
	if err != nil {
		panic(err)
	}
	defer stmt.Close()

	rows, err := stmt.Query()
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	// Iterate over the rows returned by the SELECT statement and print the results
	for rows.Next() {
		var id uint64
		var name string
		var age uint8
		if err := rows.Scan(&id, &name, &age); err != nil {
			panic(err)
		}
		fmt.Printf("%d, %s, %d\n", id, name, age)
	}
}

This is just a simple example of how to connect to ClickHouse and perform basic database operations using Go. You can also use this library to perform more complex operations such as transactions, bulk inserts, and more.

github.com/kshvakov/clickhouse
db.Readdb.Write
github.com/kshvakov/clickhouse