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"
)
Next, we need to create a new database connection by calling the clickhouse.Open function and passing in the necessary parameters, such as the host and port of the ClickHouse server, the database name, and the username and password.
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)
}
Note that we are using the rows.Scan method to retrieve the values from each row and store them in the appropriate variables.
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.
In addition to the basic operations covered in this tutorial, the github.com/kshvakov/clickhouse library also provides other advanced features such as:
- Prepared statements: Prepared statements allow you to reuse the same statement multiple times, reducing the overhead of parsing and optimizing the query each time. To use prepared statements, you can create a statement object using the db.Prepare method and then execute it using the stmt.Exec or stmt.Query methods.
- Transactions: Transactions allow you to execute multiple statements as a single, atomic unit of work. To use transactions in ClickHouse, you can start a transaction using the db.Begin method and then use the tx.Commit or tx.Rollback methods to either commit or roll back the transaction.
- Stream result processing: ClickHouse supports streaming result processing, which allows you to retrieve the results of a query incrementally, without waiting for the entire result set to be returned. To use this feature, you can use the
db.Readmethod to retrieve the results of a query as a stream. - Inserting data in bulk: ClickHouse supports bulk inserts, which allow you to insert multiple rows of data in a single query. To use this feature, you can use the
db.Writemethod to insert data into a table in bulk.
In conclusion, the github.com/kshvakov/clickhouse library provides a simple and convenient way to connect to ClickHouse from a Go program, and the library's documentation provides more information on how to use the various features it provides.
