Compare · 022

Batch and copyfrom: SQLite and ClickHouse

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

Compare SQLite withPostgreSQL CockroachDB TimescaleDB MySQL MariaDB DuckDB ClickHouse

Configuration

sgl.yamldiffers

SQLite
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: tides
4  - name: tides
5    dialect: sqlite
5    dialect: clickhouse
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

SQLite
ClickHouse
1CREATE TABLE readings (
1CREATE TABLE readings (
2    id        INTEGER PRIMARY KEY,
2    station   String,
3    station   TEXT    NOT NULL,
3    minute    UInt16,
4    minute    INTEGER NOT NULL,
4    height_cm UInt16,
5    height_cm INTEGER NOT NULL,
5    note      Nullable(String)
6    note      TEXT
6) ENGINE = MergeTree() ORDER BY (station, minute);
7);

db/migrations/0001_readings.down.sqlidentical

SQLite
ClickHouse
1DROP TABLE readings;
1DROP TABLE readings;

Queries

RecordReadingsdiffers

SQLite · db/queries/readings.sql
ClickHouse · db/queries/readings.sql
1-- sgl.query(RecordReadings, batch)
1-- sgl.query(RecordReadings, batch)
2INSERT INTO readings (station, minute, height_cm, note) VALUES (?, ?, ?, ?);
2INSERT INTO readings (station, minute, height_cm, note)
3VALUES ({station:String}, {minute:UInt16}, {height_cm:UInt16}, {note:Nullable(String)});

SummariseReadingsdiffers

SQLite · db/queries/readings.sql
ClickHouse · 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

SQLite · not in this version
ClickHouse · db/queries/readings.sql
1-- sgl.query(CountParts, one)
2SELECT uniqExact(_part) AS parts FROM readings;

What sgl generates

RecordReadingsdiffers

SQLite
ClickHouse
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   int64
5	Minute   uint16
6	HeightCm int64
6	HeightCm uint16
7	Note     *string
7	Note     *string
8}
8}

SummariseReadingsdiffers

SQLite
ClickHouse
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 int64  `json:"readings"`
5	Readings uint64 `json:"readings"`
6	Noted    int64  `json:"noted"`
6	Noted    uint64 `json:"noted"`
7}
7}

CountPartsonly ClickHouse

SQLite · not in this version
ClickHouse
1func (queries *Queries) CountParts(ctx context.Context) (CountPartsRow, error)
2
3type CountPartsRow struct {
4	Parts uint64 `json:"parts"`
5}

The program

example.godiffers

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

Go type differences

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

Query	Field	SQLite	ClickHouse
RecordReadings	parameter `minute`	`int64`	`uint16`
RecordReadings	parameter `height_cm`	`int64`	`uint16`
SummariseReadings	column `readings`	`int64`	`uint64`
SummariseReadings	column `noted`	`int64`	`uint64`