batch.AppendStruct() converting int64 to DateTime is unsupported

[Meppo]

• conn.Exec("insert into ...) support converting int64 to DateTime, but batch.AppendStruct() not support ...
• import data with format JSONEachRow also support converting int to DateTime

environment

# go version
go version go1.19.2 linux/amd64

# cat go.mod
module xxx

go 1.19

require (
	github.com/ClickHouse/clickhouse-go/v2 v2.3.0
        ...
)

example code

package main

import (
	"context"
	"log"
	"time"

	"github.com/ClickHouse/clickhouse-go/v2"
)

const ddl = `
CREATE TABLE benchmark (
	  Col1 UInt64
	, Col2 String
	, Col3 DateTime
) Engine Null
`

type row struct {
	Col1 uint64
	Col2 string
	Col3 int64  // unix timestamp
}

// conn.Exec convert int64 -> datetime success
func insert(conn clickhouse.Conn, r *row) error {

	err := conn.Exec(context.Background(),"INSERT INTO benchmark VALUES (?,?,?)",
		r.Col1,
		r.Col2,
		r.Col3)
	if err != nil {
		return err
	}

	return nil
}

// batch.AppendStruct convert int64 -> datetime not support
func appendstruct(conn clickhouse.Conn, r *row) error {
	batch, err := conn.PrepareBatch(context.Background(), "INSERT INTO benchmark")
	if err != nil {
		return err
	}

	err = batch.AppendStruct(r)
	if err != nil {
		return err
	}
	return batch.Send()
}

func main() {
	var (
		ctx       = context.Background()
		conn, err = clickhouse.Open(&clickhouse.Options{
			Addr: []string{"localhost:9000"},
			Auth: clickhouse.Auth{
				Database: "default",
				Username: "default",
				Password: "",
			},
		})
	)
	defer conn.Close()
	if err != nil {
		log.Fatal(err)
	}
	if err := conn.Exec(ctx, "DROP TABLE IF EXISTS benchmark"); err != nil {
		log.Fatal(err)
	}
	if err := conn.Exec(ctx, ddl); err != nil {
		log.Fatal(err)
	}

	r := &row{
		Col1: uint64(1),
		Col2: "Golang SQL database driver",
		Col3: 1667179638,
	}

	if err := insert(conn, r); err != nil {
		log.Fatal(err)
	}

	if err := appendstruct(conn, r); err != nil {
		log.Fatal(err)
	}
}

• run result

# go run main.go
2022/10/31 09:35:44 clickhouse [AppendRow]: Col3 clickhouse [AppendRow]: converting int64 to DateTime is unsupported
exit status 1

[gingerwizard]

This should work. I'll patch if it doesn't. Thanks for reporting @Meppo

[gingerwizard]

Note "INSERT INTO benchmark VALUES" is not equivalent to the AppendStruct interface. The latter uses a batch and sends blocks for high performance - the former simply sends a query (and shouldn't be used for anything other than occasional small inserts). The data is untyped in the latter case - its just a query.

Append is the equivalent to AppendStruct. We generally don't support inserting int64 into Date - we do support int64 to DateTime64. Its a reasonable thing to support but note the range bounds of DateTime ([1970-01-01 00:00:00, 2106-02-07 06:28:15]) - if we assume you are specifying a UNIX timestamp in seconds, you can easily go out of bounds.

[gingerwizard]

Will be closed by #807

[Meppo]

Note "INSERT INTO benchmark VALUES" is not equivalent to the AppendStruct interface. The latter uses a batch and sends blocks for high performance - the former simply sends a query (and shouldn't be used for anything other than occasional small inserts). The data is untyped in the latter case - its just a query.

Append is the equivalent to AppendStruct. We generally don't support inserting int64 into Date - we do support int64 to DateTime64. Its a reasonable thing to support but note the range bounds of DateTime ([1970-01-01 00:00:00, 2106-02-07 06:28:15]) - if we assume you are specifying a UNIX timestamp in seconds, you can easily go out of bounds.

Thanks the reply . it's working when use the DateTime64 , but unit is not seconds。 That still doesn't help for my scene:

1. data source(json)

# Col3 : unix timestamp seconds
{
    "Col1": 1,
    "Col2": "Hello world",
    "Col3": 1667343639
}

2. receive json and unmarkshal

type row struct {
	Col1 uint64
	Col2 string
	Col3 int64  // unix timestamp seconds
}

3. write data to clickhouse

        // append success, but time result is not expected
	err = batch.AppendStruct(row)

example

package main

import (
	"context"
	"encoding/json"
	"log"

	"github.com/ClickHouse/clickhouse-go/v2"
)

const ddl = `
CREATE TABLE benchmark (
	  Col1 UInt64
	, Col2 String
	, Col3 DateTime64
) Engine Memory
`

//Col3 : unix timestamp seconds
const data = `{
    "Col1": 1,
    "Col2": "Hello world",
    "Col3": 1667343639
}`

type row struct {
	Col1 uint64
	Col2 string
	Col3 int64  // unix timestamp seconds
}

// batch.AppendStruct convert int64 -> datetime64 success, but Col3 result  is not expected
func appendstruct(conn clickhouse.Conn, r *row) error {
	batch, err := conn.PrepareBatch(context.Background(), "INSERT INTO benchmark")
	if err != nil {
		return err
	}

	err = batch.AppendStruct(r)
	if err != nil {
		return err
	}
	return batch.Send()
}

func main() {
	var (
		ctx       = context.Background()
		conn, err = clickhouse.Open(&clickhouse.Options{
			Addr: []string{"localhost:9000"},
			Auth: clickhouse.Auth{
				Database: "default",
				Username: "default",
				Password: "",
			},
		})
	)
	defer conn.Close()
	if err != nil {
		log.Fatal(err)
	}
	if err := conn.Exec(ctx, "DROP TABLE IF EXISTS benchmark"); err != nil {
		log.Fatal(err)
	}
	if err := conn.Exec(ctx, ddl); err != nil {
		log.Fatal(err)
	}

	var r row
	if err := json.Unmarshal([]byte(data), &r); err != nil {
		log.Fatal(err)
	}

	if err := appendstruct(conn, &r); err != nil {
		log.Fatal(err)
	}
}

result

:) select * from benchmark;

SELECT *
FROM benchmark

Query id: acfb0c2c-90c4-4039-95da-148578972e97

┌─Col1─┬─Col2────────┬────────────────────Col3─┐
│    1 │ Hello world │ 1970-01-20 15:09:03.639 │
└──────┴─────────────┴─────────────────────────┘

solution

func appendstruct(conn clickhouse.Conn, r *row) error {
...
        // change sec => ms
	r.Col3 = r.Col3*1000
	err = batch.AppendStruct(r)
...
}

it's a inconvenien to change Col3 s => ms before do appendstruct()

[gingerwizard]

I added a pr for support for int64 for datetime.it assumes secs.