Gorm:使用数组列批量插入到ClickHouse中

huangapple go评论79阅读模式
英文:

Gorm: Batch insert into ClickHouse with Array columns

问题

我想批量将数据插入到我们的ClickHouse数据库中。使用gorm,我可以轻松地使用以下代码:

type audit struct{
    field1 string `json:"field1"`,
    field2 string `json:"field2"`, 
}

chDB.Table(tableName).CreateInBatches(audits, 5)

然而,如果audit包含如下的数组字段,其他字段的数据仍然会保存在数据库中,只有数组字段(如下的field1)不会保存。

type audit struct{
    field1 []string `json:"field1"`,
    field2 string   `json:"field2"`, 
}

chDB.Table(tableName).CreateInBatches(audits, 5)

错误信息:[error] unsupported data type: &[]

只有当我准备一个语句并保存数据时,它才能正常工作。然而,在这种情况下,它不再是批量插入了。

sqlDB, err := db.DB()
tx, err := sqlDB.Begin()
stmt, err := tx.PrepareContext(ctx, `insert into audit_table (field1, field2) values (?, ?)`)
_, err = stmt.ExecContext(ctx, clickhouse.Array(audit.field1), audit.field2)
err = tx.Commit()

有人可以帮我解决这个问题吗?如何将数组数据批量插入到ClickHouse列中?

英文:

I would like to batch-insert data into our ClickHouse database. Using gorm, I can easily use

type audit struct{
 field1 string `json:"field1"`,
 field2 string `json:"field2"`, 
}

chDB.Table(tableName).CreateInBatches(audits, 5)

However, if the audit contains an array field as below, the data of other fields will still be saved in the database. Only the field with array (field1 as below) will not be saved.

type audit struct{
 field1 []string `json:"field1"`,
 field2 string   `json:"field2"`, 
}

chDB.Table(tableName).CreateInBatches(audits, 5)

Error:[error] unsupported data type: &[]

Only if I prepare a statement and save data then it works. However, in that case, it's not batch-insert anymore

sqlDB, err := db.DB()
tx, err := sqlDB.Begin()
stmt, err := tx.PrepareContext(ctx, `insert into audit_table (field1, field2) values (?, ?)`)
_, err = stmt.ExecContext(ctx, clickhouse.Array(audit.field1), audit.field2)
err = tx.Commit()

Can someone help me with this? How to batch-insert array-data into ClickHouse columns?

答案1

得分: 1

你可能需要使用clickhouse.Array类型来插入clickhouse.Array列。

type audit struct{
 field1 clickhouse.Array `json:"field1"`,
 field2 string           `json:"field2"`, 
}

audits := audit{
  field1: clickhouse.Array([]string{"one", "three"}),
  field2: "two"
}

chDB.Table(tableName).CreateInBatches(audits, 5)
英文:

You may need clickhouse.Array Type to insert in clickhouse.Array column

type audit struct{
 field1 clickhouse.Array `json:"field1"`,
 field2 string           `json:"field2"`, 
}

audits := audit{
  field1: clickhouse.Array([]string{"one", "three"}),
  field2: "two"
}

chDB.Table(tableName).CreateInBatches(audits, 5)

答案2

得分: 0

这对我没有起作用,我需要使用正确的字段标签更新我的结构体,像这样:

type User struct {
	Timestamp    time.Time `gorm:"precision:6"`
	Name         string    `gorm:"type:LowCardinality(String)"`
	Age          int8
	DefaultValue string    `gorm:"default:hello world"`
	Elapsed      time.Duration
	NullableInt  *int8     `gorm:"type:Nullable(Int8)"`
	Array        []string  `gorm:"type:Array(String)"`
}

然后可以轻松地进行批量插入,像这样:

// 批量插入
user1 := User{Timestamp: time.Now(), Age: 12, Name: "Bruce Lee", Array: []string{"hello", "ne"}}
user2 := User{Timestamp: time.Now(), Age: 13, Name: "Feynman", Array: []string{"no", "me"}}
user3 := User{Timestamp: time.Now(), Age: 14, Name: "Angeliz"}
var users = []User{user1, user2, user3}
db.Create(&users)
英文:

That didn't work for me, I needed to update my struct with the correct field tags, like so

type User struct {
	Timestamp    time.Time `gorm:"precision:6"`
	Name         string    `gorm:"type:LowCardinality(String)"`
	Age          int8
	DefaultValue string `gorm:"default:hello world"`
	Elapsed      time.Duration
	NullableInt  *int8    `gorm:"type:Nullable(Int8)"`
	Array        []string `gorm:"type:Array(String)"`
}

and could easily do batch inserts like so

	// Batch Insert
	user1 := User{Timestamp: time.Now(), Age: 12, Name: "Bruce Lee", Arraa: []string{"hello", "ne"}}
	user2 := User{Timestamp: time.Now(), Age: 13, Name: "Feynman", Arraa: []string{"no", "me"}}
	user3 := User{Timestamp: time.Now(), Age: 14, Name: "Angeliz"}
	var users = []User{user1, user2, user3}
	db.Create(&users)

huangapple
  • 本文由 发表于 2022年1月21日 01:58:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/70791170.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定