ClickHouse Insert

• API
• Example
• Bulk-insert
• Columnar
• INSERT ... SELECT

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"