How initialize the result of groupArray function of the ClickHouse to the array

Question

In my Go application, I make a request to the ClickHouse database via clickhouse-go package. This query which I make return always only one record and it's an array of integers. Is there any way to initialize that result to the array in Go?

var ids []int

ids, err := database.ClickHouse.Exec("SELECT groupArray(ID) FROM layers;")
if err != nil {
    fmt.Println(err)
}

I tried such code but it raises an error: cannot assign Result to ids (type []int) in multiple assignment.

score 1 · Answer 1 · answered Dec 13 '19 at 05:20

1

The Exec method doesn't return the rows, it returns driver.Result and error.

func (stmt *stmt) Exec(args []driver.Value) (driver.Result, error)
                                             ^^^^^^^^^^^^^

And, the driver.Result type has the following definition (comments removed):

type Result interface {
    LastInsertId() (int64, error)
    RowsAffected() (int64, error)
}

What you're looking for is Query method that returns driver.Rows:

func (stmt *stmt) Query(args []driver.Value) (driver.Rows, error)
                                              ^^^^^^^^^^^

You can then iterate over the rows to generate the array you want.

An example has been listed in the README.md (copied here):

rows, err := connect.Query("SELECT country_code, os_id, browser_id, categories, action_day, action_time FROM example")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var (
        country               string
        os, browser           uint8
        categories            []int16
        actionDay, actionTime time.Time
    )
    if err := rows.Scan(&country, &os, &browser, &categories, &actionDay, &actionTime); err != nil {
        log.Fatal(err)
    }
    log.Printf("country: %s, os: %d, browser: %d, categories: %v, action_day: %s, action_time: %s", country, os, browser, categories, actionDay, actionTime)
}

Hope that helps!

answered Dec 13 '19 at 05:20

Azeem

11,148
4
27
40

Hello! I don't need to return rows. I know that `Query` method return rows but in my case, I need to return only one row. So I thought this method is inappropriate here. As I said before `groupArray` function of ClickHouse return array of integers as one row. As I understand in this package there are only 2 methods: `Exec` and `Query`, right? – Nurzhan Nogerbek Dec 13 '19 at 05:45
@NurzhanNogerbek: But, the Exec method does not return row so you need to use the Query method to extract the values you want. It also has a QueryContext method but it also returns rows. You can write your own wrapper method on top of Query and use that. – Azeem Dec 13 '19 at 06:29
Can you show me an example of the `QueryContext` method, please? – Nurzhan Nogerbek Dec 13 '19 at 07:09
@NurzhanNogerbek: Check this: https://github.com/ClickHouse/clickhouse-go/blob/a1ffb9f99e76344fbadc95df5a9d27cea48af59d/stmt.go#L74. – Azeem Dec 13 '19 at 07:20
I think [QueryRowContext](https://golang.org/pkg/database/sql/#Conn.QueryRowContext) is that what I was looking for but I don't understand how correctly use it. What do think about this method? – Nurzhan Nogerbek Dec 13 '19 at 09:02
@NurzhanNogerbek: Yes. It could be used. But, why don't you use `Query` directly? It's a wrapper on `queryContext` method and already maintaining a `Context`. You could simply check the rows and extract your values. Mixing the APIs of these two libraries would be inconsistent. I'd suggest to go with `Query`. – Azeem Dec 13 '19 at 09:19
On second thoughts, you can drop the `ClickHouse` lib and go all the way with `sql` package if you're on the early stages of your codebase. – Azeem Dec 13 '19 at 09:20

score 0 · Answer 2 · answered May 04 '22 at 11:28

0

var ids []int64

err := database.ClickHouse.QueryRow("SELECT groupArray(ID) FROM layers").Scan(&ids)
if err != nil {
    fmt.Println(err)
}
fmt.Println(ids)

answered May 04 '22 at 11:28

SG Computers

1

How initialize the result of groupArray function of the ClickHouse to the array

2 Answers2