Support for Variant/Dynamic/JSON types

[arnaudbriche]

Hi,

I'm just wondering if you are planning on supporting the new set of semi-structured data types.

[SpencerTorres]

This is how they are supported in clickhouse-go:

• Add Variant Type clickhouse-go#1453
• Add Dynamic Type clickhouse-go#1454
• Add JSON Type clickhouse-go#1455

I would like to add some support for them, but we would need to make sure it fits the performance goals of ch-go. There's some overhead for working with these types, and we need to find a user-friendly way to read/write this type of data.

[tanner-bruce]

Is it possible to write to a JSON type column via ch-go? We do not need to to query it through ch-go.

[SpencerTorres]

You may be able to use String, I think the server can automatically cast it. I haven't checked in a while though.

Adding Variant and Dynamic would need some discussion given their complexity, but JSON has support for string encoding. Perhaps we can add a JSONString type pretty easily since it's just String with a u64 prefix. You would need to handle marshal/unmarshal yourself though

[SpencerTorres]

Just in case String doesn't directly insert into JSON (and for easily reading JSON directly), I have added ColJSONStr in #1034. Check the PR description for some notes on usage.

[freak82]

Hi there,

Is there some workaround for inserting into a column with variant type while using the ch-go library?
Can the ColAuto type be used somehow to achieve it?

[SpencerTorres]

Is there some workaround for inserting into a column with variant type while using the ch-go library?

No, unfortunately we don't have this implemented yet. I haven't looked into it recently but I think Variant may be easier to implement than Dynamic since the column types within a Variant are likely known ahead of time by the developer. For Dynamic we would need a way to initialize column types. Perhaps it will use ColAuto.

I would recommend solving this on the server side with DDL. If you know what you're inserting (maybe an Int64, or a String) you could make an ephemeral column on the table and then reference that value for the Variant column. Basically use a different column to receive the data, but persist it as Variant.

Can the ColAuto type be used somehow to achieve it?

Not at the moment, it looks like the implementation is limited to these column types:

ch-go/proto/col_auto.go

Lines 38 to 166 in dbcdccf

	switch t {
	case ColumnTypeNothing:
	c.Data = new(ColNothing)
	case ColumnTypeString:
	c.Data = new(ColStr)
	case ColumnTypeBool:
	c.Data = new(ColBool)
	case ColumnTypeDateTime:
	c.Data = new(ColDateTime)
	case ColumnTypeDate:
	c.Data = new(ColDate)
	case "Map(String,String)":
	c.Data = NewMap[string, string](new(ColStr), new(ColStr))
	case ColumnTypeUUID:
	c.Data = new(ColUUID)
	default:
	switch t.Base() {
	case ColumnTypeArray:
	inner := new(ColAuto)
	if err := inner.Infer(t.Elem()); err != nil {
	return errors.Wrap(err, "array")
	}
	innerValue := reflect.ValueOf(inner.Data)
	arrayMethod := innerValue.MethodByName("Array")
	if arrayMethod.IsValid() && arrayMethod.Type().NumOut() == 1 {
	if col, ok := arrayMethod.Call(nil)[0].Interface().(Column); ok {
	c.Data = col
	c.DataType = t
	return nil
	}
	}
	case ColumnTypeNullable:
	inner := new(ColAuto)
	if err := inner.Infer(t.Elem()); err != nil {
	return errors.Wrap(err, "nullable")
	}
	innerValue := reflect.ValueOf(inner.Data)
	nullableMethod := innerValue.MethodByName("Nullable")
	if nullableMethod.IsValid() && nullableMethod.Type().NumOut() == 1 {
	if col, ok := nullableMethod.Call(nil)[0].Interface().(Column); ok {
	c.Data = col
	c.DataType = t
	return nil
	}
	}
	case ColumnTypeLowCardinality:
	inner := new(ColAuto)
	if err := inner.Infer(t.Elem()); err != nil {
	return errors.Wrap(err, "low cardinality")
	}
	innerValue := reflect.ValueOf(inner.Data)
	lowCardinalityMethod := innerValue.MethodByName("LowCardinality")
	if lowCardinalityMethod.IsValid() && lowCardinalityMethod.Type().NumOut() == 1 {
	if col, ok := lowCardinalityMethod.Call(nil)[0].Interface().(Column); ok {
	c.Data = col
	c.DataType = t
	return nil
	}
	}
	case ColumnTypeDateTime:
	v := new(ColDateTime)
	if err := v.Infer(t); err != nil {
	return errors.Wrap(err, "datetime")
	}
	c.Data = v
	c.DataType = t
	return nil
	case ColumnTypeDecimal:
	var prec int
	precStr, _, _ := strings.Cut(string(t.Elem()), ",")
	if precStr != "" {
	var err error
	precStr = strings.TrimSpace(precStr)
	prec, err = strconv.Atoi(precStr)
	if err != nil {
	return errors.Wrap(err, "decimal")
	}
	} else {
	prec = 10
	}
	switch {
	case prec >= 1 && prec < 10:
	c.Data = new(ColDecimal32)
	case prec >= 10 && prec < 19:
	c.Data = new(ColDecimal64)
	case prec >= 19 && prec < 39:
	c.Data = new(ColDecimal128)
	case prec >= 39 && prec < 77:
	c.Data = new(ColDecimal256)
	default:
	return errors.Errorf("decimal precision %d out of range", prec)
	}
	c.DataType = t
	return nil
	case ColumnTypeDecimal32:
	c.Data = new(ColDecimal32)
	c.DataType = t
	return nil
	case ColumnTypeDecimal64:
	c.Data = new(ColDecimal64)
	c.DataType = t
	return nil
	case ColumnTypeDecimal128:
	c.Data = new(ColDecimal128)
	c.DataType = t
	return nil
	case ColumnTypeDecimal256:
	c.Data = new(ColDecimal256)
	c.DataType = t
	return nil
	case ColumnTypeEnum8, ColumnTypeEnum16:
	v := new(ColEnum)
	if err := v.Infer(t); err != nil {
	return errors.Wrap(err, "enum")
	}
	c.Data = v
	c.DataType = t
	return nil
	case ColumnTypeDateTime64:
	v := new(ColDateTime64)
	if err := v.Infer(t); err != nil {
	return errors.Wrap(err, "datetime64")
	}
	c.Data = v
	c.DataType = t
	return nil
	}
	return errors.Errorf("automatic column inference not supported for %q", t)
	}

[freak82]

Thank you for the response.
I'm not allowed to change the server DDL.

Will it work if I make ColVariant type for our specific use case so that it implements ColInput interface? What type it should return from the Type() function though?
Does it need to implement some other interfaces in order to work only for insert scenarios?