Compare · 022

Batch and copyfrom: ClickHouse and DuckDB

Every file of the ClickHouse and DuckDB versions side by side, with the lines that differ marked. 4 parameter or column types differ in Go.

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
DuckDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: tides
4 - name: tides
5 dialect: clickhouse
5 dialect: duckdb
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store
11 driver: clickhouse

Migrations

db/migrations/0001_readings.up.sqldiffers
ClickHouse
DuckDB
1CREATE SEQUENCE reading_ids;
2
1CREATE TABLE readings (
3CREATE TABLE readings (
2 station String,
4 id BIGINT PRIMARY KEY DEFAULT nextval('reading_ids'),
3 minute UInt16,
5 station VARCHAR NOT NULL,
4 height_cm UInt16,
6 minute INTEGER NOT NULL,
5 note Nullable(String)
7 height_cm INTEGER NOT NULL,
6) ENGINE = MergeTree() ORDER BY (station, minute);
8 note VARCHAR
9);
db/migrations/0001_readings.down.sqldiffers
ClickHouse
DuckDB
1DROP TABLE readings;
1DROP TABLE readings;
2DROP SEQUENCE reading_ids;

Queries

RecordReadingsdiffers
ClickHouse · db/queries/readings.sql
DuckDB · db/queries/readings.sql
1-- sgl.query(RecordReadings, batch)
1-- sgl.query(RecordReadings, batch)
2INSERT INTO readings (station, minute, height_cm, note)
2INSERT INTO readings (station, minute, height_cm, note) VALUES ($1, $2, $3, $4);
3VALUES ({station:String}, {minute:UInt16}, {height_cm:UInt16}, {note:Nullable(String)});
SummariseReadingsdiffers
ClickHouse · db/queries/readings.sql
DuckDB · db/queries/readings.sql
1-- sgl.query(SummariseReadings, many)
1-- sgl.query(SummariseReadings, many)
2SELECT station, count() AS readings, count(note) AS noted
2SELECT station, COUNT(*) AS readings, COUNT(note) AS noted
3FROM readings
3FROM readings
4GROUP BY station
4GROUP BY station
5ORDER BY station;
5ORDER BY station;
CountPartsonly ClickHouse
ClickHouse · db/queries/readings.sql
DuckDB · not in this version
1-- sgl.query(CountParts, one)
2SELECT uniqExact(_part) AS parts FROM readings;

What sgl generates

RecordReadingsdiffers
ClickHouse
DuckDB
1func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error
1func (queries *Queries) RecordReadings(ctx context.Context, params []RecordReadingsParams) error
2
2
3type RecordReadingsParams struct {
3type RecordReadingsParams struct {
4 Station string
4 Station string
5 Minute uint16
5 Minute int32
6 HeightCm uint16
6 HeightCm int32
7 Note *string
7 Note *string
8}
8}
SummariseReadingsdiffers
ClickHouse
DuckDB
1func (queries *Queries) SummariseReadings(ctx context.Context) ([]SummariseReadingsRow, error)
1func (queries *Queries) SummariseReadings(ctx context.Context) ([]SummariseReadingsRow, error)
2
2
3type SummariseReadingsRow struct {
3type SummariseReadingsRow struct {
4 Station string `json:"station"`
4 Station string `json:"station"`
5 Readings uint64 `json:"readings"`
5 Readings int64 `json:"readings"`
6 Noted uint64 `json:"noted"`
6 Noted int64 `json:"noted"`
7}
7}
CountPartsonly ClickHouse
ClickHouse
DuckDB · not in this version
1func (queries *Queries) CountParts(ctx context.Context) (CountPartsRow, error)
2
3type CountPartsRow struct {
4 Parts uint64 `json:"parts"`
5}

The program

example.godiffers
ClickHouse
DuckDB
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "fmt"
6 "fmt"
6 "io"
7 "io"
7
8
8 "seagull.sh/seagull/examples/clickhouse/022_bulk_writes_native/internal/store"
9 "seagull.sh/seagull/examples/duckdb/022_bulk_writes/internal/store"
9)
10)
10
11
11const (
12const (
12 // minutesPerDay is how many readings each tide gauge takes in a day.
13 // minutesPerWeek is how many readings each tide gauge takes in a week, one a minute.
13 minutesPerDay = 24 * 60
14 minutesPerWeek = 7 * 24 * 60
14)
15)
15
16
16// run records a day of readings from two tide gauges in one batch call, shows how many
17// statementCounter is a store.DBTX that counts the statements sent through ExecContext.
17// parts that wrote, and summarises what landed in the table.
18// QueryContext and QueryRowContext come from the embedded *sql.DB.
19type statementCounter struct {
20 *sql.DB
21
22 // statements is how many times ExecContext has been called.
23 statements int
24}
25
26// ExecContext counts the statement, then runs it on the database.
18//
27//
19// A batch query takes a slice of Params, one per row. On the native driver the generated
28// Takes query (string) which is the SQL to run.
20// method calls PrepareBatch, appends every row with batch.Append and sends them with
29// Takes arguments (...any) which are its bind values.
21// batch.Send: one INSERT, its rows sent column by column in blocks, with no bind
22// variables and so no chunking. The {name:Type} placeholders in the VALUES list only name
23// and type the columns; the driver takes the values from Append. copyfrom generates the
24// same code.
25//
30//
26// Each INSERT writes a new part, which ClickHouse merges in the background, so writing
31// Returns sql.Result which is the database's result.
27// rows one at a time leaves many small parts to merge. CountParts reads the _part virtual
32// Returns error when the statement fails.
28// column to show that the whole day landed as one part.
33func (counter *statementCounter) ExecContext(ctx context.Context, query string, arguments ...any) (sql.Result, error) {
34 counter.statements++
35 return counter.DB.ExecContext(ctx, query, arguments...)
36}
37
38// run records a week of readings from two tide gauges in one batch call, shows how many
39// INSERT statements that took, and summarises what landed in the table.
29//
40//
30// Takes connection (store.DBTX) which is a native connection to a migrated database.
41// Takes database (*sql.DB) which is a migrated database.
31// Takes output (io.Writer) which receives the part counts and the summary.
42// Takes output (io.Writer) which receives the statement counts and the summary.
32//
43//
33// Returns error when a query fails.
44// Returns error when a query fails.
34func run(ctx context.Context, connection store.DBTX, output io.Writer) error {
45func run(ctx context.Context, database *sql.DB, output io.Writer) error {
35 queries := store.New(connection)
46 counter := &statementCounter{DB: database}
47 queries := store.New(counter)
36
48
37 // An empty batch returns at once, without preparing an INSERT, so it writes no part.
49 // An empty batch is a no-op: nothing is sent to the database.
38 if err := queries.RecordReadings(ctx, nil); err != nil {
50 if err := queries.RecordReadings(ctx, nil); err != nil {
39 return fmt.Errorf("recording no readings: %w", err)
51 return fmt.Errorf("recording no readings: %w", err)
40 }
52 }
41 if err := printParts(ctx, queries, 0, output); err != nil {
53 fmt.Fprintf(output, "0 readings sent in %d statements\n", counter.statements)
42 return err
43 }
44
54
45 readings := tideReadings("harbour", "pier")
55 readings := tideReadings("harbour", "pier")
56
57 // A batch query takes a slice of Params, one per row. On database/sql the generated
58 // method writes them as multi-row INSERTs, chunked so that no statement binds more than
59 // 65535 variables, the limit declared by Seagull's DuckDB dialect: four columns a row
60 // allows 16383 rows a statement, so a week's 20160 rows take two. copyfrom generates the
61 // same code on database/sql.
62 //
63 // Each chunk is its own statement, so run the call inside RunInTx (concept 23) when
64 // every row must land or none.
46 if err := queries.RecordReadings(ctx, readings); err != nil {
65 if err := queries.RecordReadings(ctx, readings); err != nil {
47 return fmt.Errorf("recording readings: %w", err)
66 return fmt.Errorf("recording readings: %w", err)
48 }
67 }
49 if err := printParts(ctx, queries, len(readings), output); err != nil {
68 fmt.Fprintf(output, "%d readings sent in %d statements\n", len(readings), counter.statements)
50 return err
51 }
52
69
53 summary, err := queries.SummariseReadings(ctx)
70 summary, err := queries.SummariseReadings(ctx)
54 if err != nil {
71 if err != nil {
55 return fmt.Errorf("summarising readings: %w", err)
72 return fmt.Errorf("summarising readings: %w", err)
56 }
73 }
57 for _, row := range summary {
74 for _, row := range summary {
58 fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
75 fmt.Fprintf(output, "%-8s %d readings, %d with a note\n", row.Station, row.Readings, row.Noted)
59 }
76 }
60 return nil
77 return nil
61}
78}
62
79
63// printParts prints how many parts hold the table's rows.
80// tideReadings makes one reading a minute for a week at each station. The height rises
64//
81// and falls in a simple saw-tooth, and the reading on the hour carries a note.
65// Takes queries (*store.Queries) which runs the query.
66// Takes sent (int) which is how many readings were sent.
67// Takes output (io.Writer) which receives the line.
68//
69// Returns error when the query fails.
70func printParts(ctx context.Context, queries *store.Queries, sent int, output io.Writer) error {
71 parts, err := queries.CountParts(ctx)
72 if err != nil {
73 return fmt.Errorf("counting parts: %w", err)
74 }
75 fmt.Fprintf(output, "%d readings sent, parts holding rows: %d\n", sent, parts.Parts)
76 return nil
77}
78
79// tideReadings makes one reading a minute for a day at each station. The height rises and
80// falls in a simple saw-tooth, and the reading on the hour carries a note.
81//
82//
82// Takes stations (...string) which name the gauges.
83// Takes stations (...string) which name the gauges.
83//
84//
84// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
85// Returns []store.RecordReadingsParams which holds every reading, ready for the batch.
85func tideReadings(stations ...string) []store.RecordReadingsParams {
86func tideReadings(stations ...string) []store.RecordReadingsParams {
86 readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerDay)
87 readings := make([]store.RecordReadingsParams, 0, len(stations)*minutesPerWeek)
87 for _, station := range stations {
88 for _, station := range stations {
88 for minute := range uint16(minutesPerDay) {
89 for minute := range int32(minutesPerWeek) {
89 var note *string
90 var note *string
90 if minute%60 == 0 {
91 if minute%60 == 0 {
91 note = new(fmt.Sprintf("hour %d check", minute/60))
92 note = new(fmt.Sprintf("hour %d check", minute/60))
92 }
93 }
93 readings = append(readings, store.RecordReadingsParams{
94 readings = append(readings, store.RecordReadingsParams{
94 Station: station,
95 Station: station,
95 Minute: minute,
96 Minute: minute,
96 HeightCm: 200 + minute%360,
97 HeightCm: 200 + minute%360,
97 Note: note,
98 Note: note,
98 })
99 })
99 }
100 }
100 }
101 }
101 return readings
102 return readings
102}
103}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

QueryFieldClickHouseDuckDB
RecordReadingsparameter minuteuint16int32
RecordReadingsparameter height_cmuint16int32
SummariseReadingscolumn readingsuint64int64
SummariseReadingscolumn noteduint64int64