英文:
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)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论