Compare · 008

Arrays and lists as typed slices: DuckDB and ClickHouse

Every file of the DuckDB and ClickHouse versions side by side, with the lines that differ marked.

Compare DuckDB withPostgreSQL CockroachDB TimescaleDB ClickHouse

Configuration

sgl.yamldiffers

DuckDB
ClickHouse
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: voyages
4  - name: quay
5    dialect: duckdb
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

Migrations

db/migrations/0001_voyages.up.sqldiffers

DuckDB
ClickHouse · db/migrations/0001_hauls.up.sql
1CREATE SEQUENCE voyage_ids;
1CREATE TABLE hauls (
2
2    id         UInt64,
3CREATE TABLE voyages (
3    boat       String,
4    id        BIGINT    PRIMARY KEY DEFAULT nextval('voyage_ids'),
4    species    Array(String),
5    boat      VARCHAR   NOT NULL,
5    weights_kg Array(UInt32)
6    ports     VARCHAR[] NOT NULL,
6) ENGINE = MergeTree() ORDER BY id;
7    catch_kg  INTEGER[] NOT NULL,
8    sightings VARCHAR[]
9);

db/migrations/0001_voyages.down.sqldiffers

DuckDB
ClickHouse · db/migrations/0001_hauls.down.sql
1DROP TABLE voyages;
1DROP TABLE hauls;
2DROP SEQUENCE voyage_ids;

Queries

LogVoyageonly DuckDB

DuckDB · db/queries/voyages.sql
ClickHouse · not in this version
1-- sgl.query(LogVoyage, exec)
2INSERT INTO voyages (boat, ports, catch_kg, sightings) VALUES ($1, $2, $3, $4);

ListVoyagesonly DuckDB

DuckDB · db/queries/voyages.sql
ClickHouse · not in this version
1-- sgl.query(ListVoyages, many)
2SELECT id, boat, ports, catch_kg, sightings FROM voyages ORDER BY id;

VoyagesCallingAtonly DuckDB

DuckDB · db/queries/voyages.sql
ClickHouse · not in this version
1-- sgl.query(VoyagesCallingAt, many)
2-- $1 as sgl.param(port, type: varchar)
3SELECT boat, ports[1] AS first_port, array_length(ports) AS stops
4FROM voyages
5WHERE $1 = ANY(ports)
6ORDER BY id;

VoyagesPerBoatonly DuckDB

DuckDB · db/queries/voyages.sql
ClickHouse · not in this version
1-- sgl.query(VoyagesPerBoat, many)
2SELECT boat, array_agg(id ORDER BY id) AS voyages
3FROM voyages
4GROUP BY boat
5ORDER BY boat;

LandHaulonly ClickHouse

DuckDB · not in this version
ClickHouse · db/queries/hauls.sql
1-- sgl.query(LandHaul, exec)
2INSERT INTO hauls (id, boat, species, weights_kg)
3VALUES ({id:UInt64}, {boat:String}, {species:Array(String)}, {weights_kg:Array(UInt32)});

GetHaulonly ClickHouse

DuckDB · not in this version
ClickHouse · db/queries/hauls.sql
1-- sgl.query(GetHaul, one)
2SELECT id, boat, species, weights_kg FROM hauls WHERE id = {id:UInt64};

HaulsWithonly ClickHouse

DuckDB · not in this version
ClickHouse · db/queries/hauls.sql
1-- sgl.query(HaulsWith, many)
2SELECT id, boat, species, weights_kg
3FROM hauls
4WHERE has(species, {fish:String})
5ORDER BY id;

What sgl generates

LogVoyageonly DuckDB

DuckDB
ClickHouse · not in this version
1func (queries *Queries) LogVoyage(ctx context.Context, params LogVoyageParams) error
2
3type LogVoyageParams struct {
4	Boat      string
5	Ports     []string
6	CatchKg   []int32
7	Sightings []string
8}

ListVoyagesonly DuckDB

DuckDB
ClickHouse · not in this version
1func (queries *Queries) ListVoyages(ctx context.Context) ([]ListVoyagesRow, error)
2
3type ListVoyagesRow struct {
4	ID        int64     `json:"id"`
5	Boat      string    `json:"boat"`
6	Ports     []*string `json:"ports"`
7	CatchKg   []*int32  `json:"catch_kg"`
8	Sightings []*string `json:"sightings"`
9}

VoyagesCallingAtonly DuckDB

DuckDB
ClickHouse · not in this version
1func (queries *Queries) VoyagesCallingAt(ctx context.Context, port string) ([]VoyagesCallingAtRow, error)
2
3type VoyagesCallingAtRow struct {
4	Boat      string  `json:"boat"`
5	FirstPort *string `json:"first_port"`
6	Stops     int64   `json:"stops"`
7}

VoyagesPerBoatonly DuckDB

DuckDB
ClickHouse · not in this version
1func (queries *Queries) VoyagesPerBoat(ctx context.Context) ([]VoyagesPerBoatRow, error)
2
3type VoyagesPerBoatRow struct {
4	Boat    string  `json:"boat"`
5	Voyages []int64 `json:"voyages"`
6}

LandHaulonly ClickHouse

DuckDB · not in this version
ClickHouse
1func (queries *Queries) LandHaul(ctx context.Context, params LandHaulParams) error
2
3type LandHaulParams struct {
4	ID        uint64
5	Boat      string
6	Species   []string
7	WeightsKg []uint32
8}

GetHaulonly ClickHouse

DuckDB · not in this version
ClickHouse
1func (queries *Queries) GetHaul(ctx context.Context, id uint64) (GetHaulRow, error)
2
3type GetHaulRow struct {
4	ID        uint64   `json:"id"`
5	Boat      string   `json:"boat"`
6	Species   []string `json:"species"`
7	WeightsKg []uint32 `json:"weights_kg"`
8}

HaulsWithonly ClickHouse

DuckDB · not in this version
ClickHouse
1func (queries *Queries) HaulsWith(ctx context.Context, fish string) ([]HaulsWithRow, error)
2
3type HaulsWithRow struct {
4	ID        uint64   `json:"id"`
5	Boat      string   `json:"boat"`
6	Species   []string `json:"species"`
7	WeightsKg []uint32 `json:"weights_kg"`
8}

The program

example.godiffers

DuckDB
ClickHouse
1package main
1package main
2
2
3import (
3import (
4	"context"
4	"context"
5	"database/sql"
5	"database/sql"
6	"fmt"
6	"fmt"
7	"io"
7	"io"
8
8
9	"seagull.sh/seagull/examples/duckdb/008_arrays/internal/store"
9	"seagull.sh/seagull/examples/clickhouse/008_arrays/internal/store"
10)
10)
11
11
12// run logs three fishing voyages and reads them back. A DuckDB list column becomes a
12// run lands four hauls of fish and reads them back. An Array(T) column arrives as a Go
13// typed Go slice: VARCHAR[] gives []*string and INTEGER[] gives []*int32, and array_agg
13// slice of T's type, so species, an Array(String), is []string and weights_kg, an
14// over the NOT NULL id column gives []int64: the list can hold no NULL, so its elements
14// Array(UInt32), is []uint32, in result rows and in parameters alike: LandHaul takes the
15// are plain values.
15// slices and binds each as one {species:Array(String)} value.
16//
17// The elements are pointers because DuckDB can hold NULL in any list, whatever the
18// column's type says, so a NULL element reads as nil, which elements prints as NULL.
19// Parameters keep plain elements, so LogVoyage takes []string and []int32. When the data
20// never holds a NULL element, non_null_elements: true opts out, in an sgl.column line of
21// a query or migration or as go.types.non_null_elements in sgl.yaml: the elements then
22// read as plain values, and a NULL one fails the read.
23//
24// A slice parameter is bound as a DuckDB list. duckdb-go would bind a nil slice as an
25// empty list, so the generated code converts the nullable sightings parameter and binds a
26// nil slice as NULL, as other drivers do: Salty Chip's Sightings: nil is stored as NULL,
27// and the second Kittiwake voyage's empty slice as an empty list.
28//
29// The generated SQL selects each list column as it is, and the generated code reads the
30// list duckdb-go returns into the slice; open internal/store to see it. A NULL list
31// therefore scans to a nil slice, and an empty list to an empty one.
32//
16//
33// VoyagesCallingAt searches inside a list with $1 = ANY(ports), whose parameter is pinned
17// HaulsWith filters with has(species, {fish:String}), which looks inside the array on the
34// with sgl.param(port, type: varchar) as inference leaves it as any, and reads one
18// server. An Array column cannot be Nullable in ClickHouse, so an empty haul is an empty
35// element with ports[1], which is *string because a missing element is NULL. DuckDB lists
19// array, never NULL.
36// count from 1, like PostgreSQL arrays.
37//
20//
38// Takes database (*sql.DB) which is a migrated database.
21// Takes database (*sql.DB) which is a migrated database.
39// Takes output (io.Writer) which receives the voyages and the searches.
22// Takes output (io.Writer) which receives the hauls.
40//
23//
41// Returns error when a query fails.
24// Returns error when a query fails.
42func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
43	queries := store.New(database)
26	queries := store.New(database)
44
27
45	voyages := []store.LogVoyageParams{
28	hauls := []store.LandHaulParams{
46		{Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: []int32{120, 85}, Sightings: []string{"dolphin", "basking shark"}},
29		{ID: 1, Boat: "Kittiwake", Species: []string{"mackerel", "pollock"}, WeightsKg: []uint32{120, 45}},
47		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: []int32{40}, Sightings: nil},
30		{ID: 2, Boat: "Salty Chip", Species: []string{"crab", "lobster"}, WeightsKg: []uint32{30, 12}},
48		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: []int32{60, 30, 15}, Sightings: []string{}},
31		{ID: 3, Boat: "Gull Wing", Species: []string{"mackerel"}, WeightsKg: []uint32{80}},
32		{ID: 4, Boat: "Driftwood", Species: []string{}, WeightsKg: []uint32{}},
49	}
33	}
50	for _, voyage := range voyages {
34	for _, haul := range hauls {
51		if err := queries.LogVoyage(ctx, voyage); err != nil {
35		if err := queries.LandHaul(ctx, haul); err != nil {
52			return fmt.Errorf("logging %s's voyage: %w", voyage.Boat, err)
36			return fmt.Errorf("landing haul %d: %w", haul.ID, err)
53		}
37		}
54	}
38	}
55
39
56	logged, err := queries.ListVoyages(ctx)
40	first, err := queries.GetHaul(ctx, 1)
57	if err != nil {
41	if err != nil {
58		return fmt.Errorf("listing voyages: %w", err)
42		return fmt.Errorf("getting haul 1: %w", err)
59	}
60	for _, voyage := range logged {
61		sightings := elements(voyage.Sightings)
62		if voyage.Sightings == nil {
63			sightings = "NULL"
64		}
65		fmt.Fprintf(output, "voyage %d  %-10s  ports %s, catch %s kg, sightings %s\n", voyage.ID, voyage.Boat, elements(voyage.Ports), elements(voyage.CatchKg), sightings)
66	}
43	}
44	fmt.Fprintf(output, "haul 1: species %T %v, weights_kg %T %v\n", first.Species, first.Species, first.WeightsKg, first.WeightsKg)
67
45
68	calling, err := queries.VoyagesCallingAt(ctx, "Mousehole")
46	mackerel, err := queries.HaulsWith(ctx, "mackerel")
69	if err != nil {
47	if err != nil {
70		return fmt.Errorf("finding voyages calling at Mousehole: %w", err)
48		return fmt.Errorf("finding mackerel: %w", err)
71	}
49	}
72	fmt.Fprintln(output, "\ncalling at Mousehole:")
50	fmt.Fprintln(output, "hauls with mackerel:")
73	for _, voyage := range calling {
51	for _, haul := range mackerel {
74		fmt.Fprintf(output, "  %-10s  from %s, %d stops\n", voyage.Boat, *voyage.FirstPort, voyage.Stops)
52		fmt.Fprintf(output, "  #%d %-10s %v %v kg\n", haul.ID, haul.Boat, haul.Species, haul.WeightsKg)
75	}
53	}
76
54
77	perBoat, err := queries.VoyagesPerBoat(ctx)
55	empty, err := queries.GetHaul(ctx, 4)
78	if err != nil {
56	if err != nil {
79		return fmt.Errorf("grouping voyages by boat: %w", err)
57		return fmt.Errorf("getting haul 4: %w", err)
80	}
81	fmt.Fprintln(output, "\nvoyages per boat:")
82	for _, boat := range perBoat {
83		fmt.Fprintf(output, "  %-10s  %v\n", boat.Boat, boat.Voyages)
84	}
58	}
59	fmt.Fprintf(output, "haul 4 landed %d species, an empty array: %v\n", len(empty.Species), empty.Species)
85	return nil
60	return nil
86}
61}
87
88// elements returns a list's elements as fmt prints a slice, with NULL for each element
89// the database holds NULL in.
90//
91// Takes list ([]*T) which holds the elements, nil for a NULL one.
92//
93// Returns string which is the elements in brackets, separated by spaces.
94func elements[T any](list []*T) string {
95	texts := make([]string, 0, len(list))
96	for _, element := range list {
97		if element == nil {
98			texts = append(texts, "NULL")
99			continue
100		}
101		texts = append(texts, fmt.Sprint(*element))
102	}
103	return fmt.Sprint(texts)
104}