Delivering meta, data, totals and statistics in a JSON result using go-clickhouse library in Go for API

[calebeaires]

By using the clickhouse-client command line interface and specifying the format as JSON, we can retrieve both the query result and accompanying statistics. In a query SELECT using some aggregation and grouped, when using the verb with totals the result comes with the totals aggregated

When using clickhouse-client and setting the attribute --format=JSON we get a json with others important values, the same is present in the oficial clickhouse-go Javascript library. Here is the result in the terminal after running the following query

Query

clickhouse-client --password=test --user=default --format=JSON --query="select 1 + 1"

{
    "meta":
    [
        {
            "name": "plus(1, 1)",
            "type": "UInt16"
        }
    ],

    "data":
    [
        {
            "plus(1, 1)": 2
        }
    ],

    "rows": 1,

    "statistics":
    {
        "elapsed": 0.001043427,
        "rows_read": 1,
        "bytes_read": 1
    }
}

Result props in JSON

1. Meta
2. Data
3. Rows
4. Statistics
5. Totals: (when using with totals verb)

Could you please assist me in achieving the same JSON result through an API with the go-clickhouse library in Go, similar to what can be accomplished with the official clickhouse-go library in JavaScript? Your guidance would be greatly appreciated.

[DoubleDi]

Hi!

Did you try passing a query like this to the library?

SELECT 1+1 FORMAT JSON

[calebeaires]

I've tried, here is a simple example I have implemented. It works, but withou FORMAT=JSON

package main

import (
	"context"
	"database/sql"
	"encoding/json"
	"github.com/gofiber/fiber/v2"
	"github.com/mailru/go-clickhouse/v2"
	"log"
)

func main() {

	app := fiber.New()

	app.Get("/", func(c *fiber.Ctx) error {

		connect, err := sql.Open("chhttp", "http://default:[email protected]:8123/default")

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

		ctx := context.Background()
		rows, err := connect.QueryContext(context.WithValue(ctx, clickhouse.QueryID, "dummy-query-id"), `
			SELECT 1+1 
		`)
		if err != nil {
			log.Fatal(err)
		}
		defer rows.Close()

		var result []map[string]interface{}

		columns, err := rows.Columns()
		if err != nil {
			log.Fatal(err)
		}

		for rows.Next() {
			rowData := make([]interface{}, len(columns))
			rowPointers := make([]interface{}, len(columns))
			for i := range rowData {
				rowPointers[i] = &rowData[i]
			}

			if err := rows.Scan(rowPointers...); err != nil {
				log.Fatal(err)
			}

			entry := make(map[string]interface{})
			for i, column := range columns {
				v := rowData[i]
				entry[column] = v
			}
			result = append(result, entry)
		}
		if err := rows.Err(); err != nil {
			log.Fatal(err)
		}

		response, err := json.Marshal(result)
		if err != nil {
			log.Fatal(err)
		}

		return c.Send(response)
	})

	app.Listen(":3007")
}

[calebeaires]

@DoubleDi
Do you have some approach on how to get it work?

[DoubleDi]

what about executing this query ?

		rows, err := connect.QueryContext(context.WithValue(ctx, clickhouse.QueryID, "dummy-query-id"), `
			SELECT 1+1 FORMAT JSON
		`)

[calebeaires]

In the example above, I've go this error:

newTextRows: failed to parse a description of the type '': failed to parse type description: failed to parse type name: wrong token type ''