clickhouse-go/examples/std/dynamic.go at main · ClickHouse/clickhouse-go · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
package std

import (
	"context"
	"fmt"
	"github.com/ClickHouse/clickhouse-go/v2"
)

func DynamicExample() error {
	ctx := context.Background()

	conn, err := GetStdOpenDBConnection(clickhouse.Native, nil, nil, nil)
	if err != nil {
		return err
	}

	if !CheckMinServerVersion(conn, 25, 6, 0) {
		fmt.Print("unsupported clickhouse version for Dynamic type")
		return nil
	}

	_, err = conn.ExecContext(ctx, "SET allow_experimental_dynamic_type = 1")
	if err != nil {
		return err
	}

	_, err = conn.ExecContext(ctx, "SET output_format_native_use_flattened_dynamic_and_json_serialization = 1")
	if err != nil {
		return err
	}

	defer func() {
		conn.Exec("DROP TABLE go_dynamic_example")
	}()

	_, err = conn.ExecContext(ctx, "DROP TABLE IF EXISTS go_dynamic_example")
	if err != nil {
		return err
	}

	_, err = conn.ExecContext(ctx, `
		CREATE TABLE go_dynamic_example (
		    c Dynamic
		) ENGINE = Memory
	`)
	if err != nil {
		return err
	}

	tx, err := conn.BeginTx(ctx, nil)
	if err != nil {
		return err
	}

	batch, err := tx.PrepareContext(ctx, "INSERT INTO go_dynamic_example (c)")
	if err != nil {
		return err
	}

	if _, err = batch.ExecContext(ctx, true); err != nil {
		return err
	}

	if _, err = batch.ExecContext(ctx, int64(42)); err != nil {
		return err
	}

	if _, err = batch.ExecContext(ctx, "example"); err != nil {
		return err
	}

	if _, err = batch.ExecContext(ctx, clickhouse.NewDynamic("example dynamic")); err != nil {
		return err
	}

	if _, err = batch.ExecContext(ctx, clickhouse.NewDynamicWithType("example dynamic with specific type", "String")); err != nil {
		return err
	}

	if _, err = batch.ExecContext(ctx, nil); err != nil {
		return err
	}

	if err = tx.Commit(); err != nil {
		return err
	}

	// Switch on Go Type

	rows, err := conn.QueryContext(ctx, "SELECT c FROM go_dynamic_example")
	if err != nil {
		return err
	}

	for i := 0; rows.Next(); i++ {
		var row clickhouse.Dynamic
		err := rows.Scan(&row)
		if err != nil {
			return fmt.Errorf("failed to scan row index %d: %w", i, err)
		}

		switch row.Any().(type) {
		case bool:
			fmt.Printf("row at index %d is Bool: %v\n", i, row.Any())
		case int64:
			fmt.Printf("row at index %d is Int64: %v\n", i, row.Any())
		case string:
			fmt.Printf("row at index %d is String: %v\n", i, row.Any())
		case nil:
			fmt.Printf("row at index %d is NULL\n", i)
		}
	}

	// Switch on ClickHouse Type

	rows, err = conn.QueryContext(ctx, "SELECT c FROM go_dynamic_example")
	if err != nil {
		return err
	}

	for i := 0; rows.Next(); i++ {
		var row clickhouse.Dynamic
		err := rows.Scan(&row)
		if err != nil {
			return fmt.Errorf("failed to scan row index %d: %w", i, err)
		}

		switch row.Type() {
		case "Bool":
			fmt.Printf("row at index %d is bool: %v\n", i, row.Any())
		case "Int64":
			fmt.Printf("row at index %d is int64: %v\n", i, row.Any())
		case "String":
			fmt.Printf("row at index %d is string: %v\n", i, row.Any())
		case "":
			fmt.Printf("row at index %d is nil\n", i)
		}
	}

	return nil
}