forked from datalayers-io/examples
-
Notifications
You must be signed in to change notification settings - Fork 0
/
main.go
189 lines (174 loc) · 5.82 KB
/
main.go
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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
package main
import (
"fmt"
"os"
)
func main() {
// Read the TLS certificate path from the environment variable.
var tlsCert *string = nil
path := os.Getenv("TLS_CERT")
if path != "" {
tlsCert = &path
}
// Creates a client for executing SQLs on the Datalayers server.
config := &ClientConfig{
Host: "127.0.0.1",
Port: 8360,
Username: "admin",
Password: "public",
TlsCert: tlsCert,
}
client, err := MakeClient(config)
if err != nil {
fmt.Println("Failed to create client: ", err)
return
}
// Creates a database `go`.
sql := "CREATE DATABASE go;"
result, err := client.Execute(sql)
if err != nil {
fmt.Println("Failed to create database: ", err)
return
}
// The result should be:
// Affected rows: 0
PrintAffectedRows(result)
// Optional: sets the database header for each outgoing request to `go`.
// The Datalayers server uses this header to identify the associated table of a request.
// This setting is optional since the following SQLs contain the database context
// and the server could parse the database context from SQLs.
client.UseDatabase("go")
// Creates a table `demo` within the database `go`.
sql = `
CREATE TABLE go.demo (
ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
sid INT32,
value REAL,
flag INT8,
timestamp key(ts)
)
PARTITION BY HASH(sid) PARTITIONS 8
ENGINE=TimeSeries;`
result, err = client.Execute(sql)
if err != nil {
fmt.Println("Failed to create table: ", err)
return
}
// The result should be:
// Affected rows: 0
PrintAffectedRows(result)
// Inserts some data into the `demo` table.
sql = `
INSERT INTO go.demo (ts, sid, value, flag) VALUES
('2024-09-01T10:00:00+08:00', 1, 12.5, 0),
('2024-09-01T10:05:00+08:00', 2, 15.3, 1),
('2024-09-01T10:10:00+08:00', 3, 9.8, 0),
('2024-09-01T10:15:00+08:00', 4, 22.1, 1),
('2024-09-01T10:20:00+08:00', 5, 30.0, 0);`
result, err = client.Execute(sql)
if err != nil {
fmt.Println("Failed to insert data: ", err)
return
}
// The result should be:
// Affected rows: 5
PrintAffectedRows(result)
// Queries the inserted data.
sql = "SELECT * FROM go.demo"
result, err = client.Execute(sql)
if err != nil {
fmt.Println("Failed to scan data: ", err)
return
}
// The result should be:
// ts sid value flag
// 2024-09-01 10:15:00 +0800 CST 4 22.10 1
// 2024-09-01 10:00:00 +0800 CST 1 12.50 0
// 2024-09-01 10:05:00 +0800 CST 2 15.30 1
// 2024-09-01 10:10:00 +0800 CST 3 9.80 0
// 2024-09-01 10:20:00 +0800 CST 5 30.00 0
PrintRecords(result)
// Inserts some data into the `demo` table with prepared statement.
sql = "INSERT INTO go.demo (ts, sid, value, flag) VALUES (?, ?, ?, ?);"
preparedStmt, err := client.Prepare(sql)
if err != nil {
fmt.Println("Failed to create a insert prepared statement: ", err)
return
}
binding := MakeInsertBinding()
result, err = client.ExecutePrepared(preparedStmt, binding)
if err != nil {
fmt.Println("Failed to execute a insert prepared statement: ", err)
return
}
// The result should be:
// Affected rows: 5
PrintAffectedRows(result)
// Queries the inserted data with prepared statement.
sql = "SELECT * FROM go.demo WHERE sid = ?"
preparedStmt, err = client.Prepare(sql)
if err != nil {
fmt.Println("Failed to create a select prepared statement: ", err)
return
}
// Retrieves all rows with `sid` = 1.
binding = MakeQueryBinding(1)
result, err = client.ExecutePrepared(preparedStmt, binding)
if err != nil {
fmt.Println("Failed to execute a select prepared statement: ", err)
return
}
// The result should be:
// ts sid value flag
// 2024-09-01 10:00:00 +0800 CST 1 12.50 0
// 2024-09-02 10:00:00 +0800 CST 1 12.50 0
PrintRecords(result)
// Retrieves all rows with `sid` = 1.
binding = MakeQueryBinding(2)
result, err = client.ExecutePrepared(preparedStmt, binding)
if err != nil {
fmt.Println("Failed to execute a select prepared statement: ", err)
return
}
// The result should be:
// ts sid value flag
// 2024-09-01 10:05:00 +0800 CST 2 15.30 1
// 2024-09-02 10:05:00 +0800 CST 2 15.30 1
PrintRecords(result)
// Closes the prepared statement to notify releasing resources on server side.
if err = client.ClosePrepared(preparedStmt); err != nil {
fmt.Println("Failed to close a prepared statement: ", err)
return
}
// There provides a dedicated interface `execute_update` for executing DMLs, including Insert, Delete.
// This interface directly returns the affected rows which might be convenient for some use cases.
//
// Note, Datalayers does not support Update and the development for Delete is in progress.
sql = `
INSERT INTO go.demo (ts, sid, value, flag) VALUES
('2024-09-03T10:00:00+08:00', 1, 4.5, 0),
('2024-09-03T10:05:00+08:00', 2, 11.6, 1);`
_, err = client.ExecuteUpdate(sql)
if err != nil {
fmt.Println("Failed to insert data: ", err)
return
}
// It's expected that the affected rows is 2.
// However, the Go implementation of Arrow Flight SQL client might have some flaws and the affected rows is always 0.
//
// The output should be:
// Affected rows: 2
// fmt.Println("Affected rows: ", affectedRows)
// Checks that the data are inserted successfully.
sql = "SELECT * FROM go.demo where ts >= '2024-09-03T10:00:00+08:00'"
result, err = client.Execute(sql)
if err != nil {
fmt.Println("Failed to scan data: ", err)
return
}
// The result should be:
// ts sid value flag
// 2024-09-03 10:00:00 +0800 CST 1 4.50 0
// 2024-09-03 10:05:00 +0800 CST 2 11.60 1
PrintRecords(result)
}