Compare · 008

Arrays and lists as typed slices: ClickHouse and DuckDB

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

Compare ClickHouse withPostgreSQL CockroachDB TimescaleDB DuckDB

Configuration

sgl.yamldiffers

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

Migrations

db/migrations/0001_hauls.up.sqldiffers

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

db/migrations/0001_hauls.down.sqldiffers

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

Queries

LandHaulonly ClickHouse

ClickHouse · db/queries/hauls.sql
DuckDB · not in this version
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

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

HaulsWithonly ClickHouse

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

LogVoyageonly DuckDB

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

ListVoyagesonly DuckDB

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

VoyagesCallingAtonly DuckDB

ClickHouse · not in this version
DuckDB · db/queries/voyages.sql
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

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

What sgl generates

LandHaulonly ClickHouse

ClickHouse
DuckDB · not in this version
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

ClickHouse
DuckDB · not in this version
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

ClickHouse
DuckDB · not in this version
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}

LogVoyageonly DuckDB

ClickHouse · not in this version
DuckDB
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

ClickHouse · not in this version
DuckDB
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

ClickHouse · not in this version
DuckDB
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

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

The program

example.godiffers

ClickHouse
DuckDB
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/clickhouse/008_arrays/internal/store"
9	"seagull.sh/seagull/examples/duckdb/008_arrays/internal/store"
10)
10)
11
11
12// run lands four hauls of fish and reads them back. An Array(T) column arrives as a Go
12// run logs three fishing voyages and reads them back. A DuckDB list column becomes a
13// slice of T's type, so species, an Array(String), is []string and weights_kg, an
13// typed Go slice: VARCHAR[] gives []*string and INTEGER[] gives []*int32, and array_agg
14// Array(UInt32), is []uint32, in result rows and in parameters alike: LandHaul takes the
14// over the NOT NULL id column gives []int64: the list can hold no NULL, so its elements
15// slices and binds each as one {species:Array(String)} value.
15// are plain values.
16//
16//
17// HaulsWith filters with has(species, {fish:String}), which looks inside the array on the
17// The elements are pointers because DuckDB can hold NULL in any list, whatever the
18// server. An Array column cannot be Nullable in ClickHouse, so an empty haul is an empty
18// column's type says, so a NULL element reads as nil, which elements prints as NULL.
19// array, never 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.
20//
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//
33// VoyagesCallingAt searches inside a list with $1 = ANY(ports), whose parameter is pinned
34// with sgl.param(port, type: varchar) as inference leaves it as any, and reads one
35// element with ports[1], which is *string because a missing element is NULL. DuckDB lists
36// count from 1, like PostgreSQL arrays.
37//
21// Takes database (*sql.DB) which is a migrated database.
38// Takes database (*sql.DB) which is a migrated database.
22// Takes output (io.Writer) which receives the hauls.
39// Takes output (io.Writer) which receives the voyages and the searches.
23//
40//
24// Returns error when a query fails.
41// Returns error when a query fails.
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
42func run(ctx context.Context, database *sql.DB, output io.Writer) error {
26	queries := store.New(database)
43	queries := store.New(database)
27
44
28	hauls := []store.LandHaulParams{
45	voyages := []store.LogVoyageParams{
29		{ID: 1, Boat: "Kittiwake", Species: []string{"mackerel", "pollock"}, WeightsKg: []uint32{120, 45}},
46		{Boat: "Kittiwake", Ports: []string{"Newlyn", "St Ives", "Mousehole"}, CatchKg: []int32{120, 85}, Sightings: []string{"dolphin", "basking shark"}},
30		{ID: 2, Boat: "Salty Chip", Species: []string{"crab", "lobster"}, WeightsKg: []uint32{30, 12}},
47		{Boat: "Salty Chip", Ports: []string{"Penzance", "Mousehole"}, CatchKg: []int32{40}, Sightings: nil},
31		{ID: 3, Boat: "Gull Wing", Species: []string{"mackerel"}, WeightsKg: []uint32{80}},
48		{Boat: "Kittiwake", Ports: []string{"Newlyn", "Lamorna"}, CatchKg: []int32{60, 30, 15}, Sightings: []string{}},
32		{ID: 4, Boat: "Driftwood", Species: []string{}, WeightsKg: []uint32{}},
33	}
49	}
34	for _, haul := range hauls {
50	for _, voyage := range voyages {
35		if err := queries.LandHaul(ctx, haul); err != nil {
51		if err := queries.LogVoyage(ctx, voyage); err != nil {
36			return fmt.Errorf("landing haul %d: %w", haul.ID, err)
52			return fmt.Errorf("logging %s's voyage: %w", voyage.Boat, err)
37		}
53		}
38	}
54	}
39
55
40	first, err := queries.GetHaul(ctx, 1)
56	logged, err := queries.ListVoyages(ctx)
41	if err != nil {
57	if err != nil {
42		return fmt.Errorf("getting haul 1: %w", err)
58		return fmt.Errorf("listing voyages: %w", err)
43	}
59	}
44	fmt.Fprintf(output, "haul 1: species %T %v, weights_kg %T %v\n", first.Species, first.Species, first.WeightsKg, first.WeightsKg)
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	}
45
67
46	mackerel, err := queries.HaulsWith(ctx, "mackerel")
68	calling, err := queries.VoyagesCallingAt(ctx, "Mousehole")
47	if err != nil {
69	if err != nil {
48		return fmt.Errorf("finding mackerel: %w", err)
70		return fmt.Errorf("finding voyages calling at Mousehole: %w", err)
49	}
71	}
50	fmt.Fprintln(output, "hauls with mackerel:")
72	fmt.Fprintln(output, "\ncalling at Mousehole:")
51	for _, haul := range mackerel {
73	for _, voyage := range calling {
52		fmt.Fprintf(output, "  #%d %-10s %v %v kg\n", haul.ID, haul.Boat, haul.Species, haul.WeightsKg)
74		fmt.Fprintf(output, "  %-10s  from %s, %d stops\n", voyage.Boat, *voyage.FirstPort, voyage.Stops)
53	}
75	}
54
76
55	empty, err := queries.GetHaul(ctx, 4)
77	perBoat, err := queries.VoyagesPerBoat(ctx)
56	if err != nil {
78	if err != nil {
57		return fmt.Errorf("getting haul 4: %w", err)
79		return fmt.Errorf("grouping voyages by boat: %w", err)
58	}
80	}
59	fmt.Fprintf(output, "haul 4 landed %d species, an empty array: %v\n", len(empty.Species), empty.Species)
81	fmt.Fprintln(output, "\nvoyages per boat:")
82	for _, boat := range perBoat {
83		fmt.Fprintf(output, "  %-10s  %v\n", boat.Boat, boat.Voyages)
84	}
60	return nil
85	return nil
61}
86}
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}