Insert ClickHouse
API
For the full list of supported methods, see InsertQuery.
db.NewInsert().
Model(&strct).
Model(&slice).
Model(&map). // only map[string]interface{}
Column("col1", "col2"). // list of columns to insert
ExcludeColumn("col1"). // all columns except col1
ExcludeColumn("*"). // exclude all columns
Table("table1", "table2"). // quotes table names
TableExpr("table1 AS t1"). // arbitrary unsafe expression
TableExpr("(?) AS subq", subquery).
ModelTableExpr("table1 AS t1"). // overrides model table name
Where("id = ?", 123).
Where("name LIKE ?", "my%").
Where("? = 123", ch.Ident("id")).
Where("id IN (?)", ch.In([]int64{1, 2, 3})).
Where("id IN (?)", subquery).
Where("FALSE").WhereOr("TRUE").
WhereGroup(" AND ", func(q *ch.SelectQuery) *ch.SelectQuery {
return q.WhereOr("id = 1").
WhereOr("id = 2")
}).
Setting("max_bytes_to_read = ?", 256<<20).
Setting("read_overflow_mode = 'break'")
Exec(ctx)
Example
To insert data into ClickHouse, define a model and use InsertQuery:
type Span struct {
ID uint64
Name string
}
span := &Span{
ID: 123,
Name: "hello",
}
res, err := db.NewInsert().Model(span).Exec(ctx)
Bulk-insert
To bulk-insert models, use a slice:
books := []Book{book1, book2}
res, err := db.NewInsert().Model(&books).Exec(ctx)
Columnar
If you already have columns of data to insert, you can use the ,columnar
option to insert data in column-oriented way:
type Model struct {
ch.CHModel `ch:",columnar"`
Col1 []string
Col2 []uint64
Col3 []time.Time
}
model := &Model{
Col1: []string{"value1", "value2"},
Col2: []uint64{1, 2},
Col3: []time.Time{time.Now(), time.Now()},
}
res, err := db.NewInsert().Model(model).Exec(ctx)
INSERT ... SELECT
To copy rows between tables:
_, err := db.NewInsert().
Table("books_backup").
Table("books").
Exec(ctx)
INSERT INTO "books_backup" SELECT * FROM "books"
You can also specify columns to copy:
_, err := db.NewInsert().
ColumnExpr("id, name").
Table("dest").
Table("src").
Exec(ctx)
INSERT INTO "dest" (id, name) SELECT id, name FROM "src"