-
Notifications
You must be signed in to change notification settings - Fork 8
/
proc-return-resultset.sql
183 lines (130 loc) · 5.58 KB
/
proc-return-resultset.sql
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
/*
References:
Stored Proc to return a resultset
https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#returning-a-result-set
Snowpipe data creation script:
https://github.com/allen-wong-tech/snowflake/blob/master/unload-and-snowpipe-demo.sql
Open-Sourced:
https://github.com/allen-wong-tech/snowflake/blob/master/proc-return-resultset.sql
Purpose:
Create two procedures for our API to call.
We want to expose our table data to a proc and returned as JSON:
procGetLastTen: get last ten records
procGetRecord: pass in ID variable N, and get that record returned
Benefits:
Procs provide advanced business logic, encapsulation, and reusability
API calls allow a limited, governed surface to expose Snowflake data
*/
--set context
use role sysadmin; use warehouse play_wh; use schema playdb.public;
alter warehouse play_wh set warehouse_size = 'xsmall';
--our data which came in via SnowPipe
select top 10 *
from nums_target_pipe order by 1 desc;
--get last ten records
CALL procGetLastTen(
'nums_target_pipe', -- Table name.
array_append(
ARRAY_APPEND(TO_ARRAY('N'), 'R'),'INSERT_TS') -- Array of column names.
);
--pass in ID and get record back
CALL procGetRecord(
'nums_target_pipe',
array_append(
ARRAY_APPEND(TO_ARRAY('N'), 'R'),'INSERT_TS'
),
55 --ID that we want the record for
);
--procGetLastTen: get the last ten records
CREATE OR REPLACE PROCEDURE procGetLastTen(TABLE_NAME VARCHAR, COL_NAMES ARRAY)
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
// This variable will hold a JSON data structure that holds ONE row.
var row_as_json = {};
// This array will contain all the rows.
var array_of_rows = [];
// This variable will hold a JSON data structure that we can return as
// a VARIANT.
// This will contain ALL the rows in a single "value".
var table_as_json = {};
// Run SQL statement(s) and get a resultSet.
var command = "SELECT TOP 10 * FROM " + TABLE_NAME + " ORDER BY N DESC";
var cmd1_dict = {sqlText: command};
var stmt = snowflake.createStatement(cmd1_dict);
var rs = stmt.execute();
// Read each row and add it to the array we will return.
var row_num = 1;
while (rs.next()) {
// Put each row in a variable of type JSON.
row_as_json = {};
// For each column in the row...
for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
var col_name = COL_NAMES[col_num];
row_as_json[col_name] = rs.getColumnValue(col_num + 1);
}
// Add the row to the array of rows.
array_of_rows.push(row_as_json);
++row_num;
}
// Put the array in a JSON variable (so it looks like a VARIANT to
// Snowflake). The key is "key1", and the value is the array that has
// the rows we want.
// table_as_json = { "key1" : array_of_rows };
// Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
return array_of_rows;
$$
;
--procGetRecord: pass in PARAM parameter and get that record returned as JSON
CREATE OR REPLACE PROCEDURE procGetRecord(TABLE_NAME VARCHAR, COL_NAMES ARRAY, PARAM VARCHAR)
RETURNS VARIANT NOT NULL
LANGUAGE JAVASCRIPT
AS
$$
// This variable will hold a JSON data structure that holds ONE row.
var row_as_json = {};
// This array will contain all the rows.
var array_of_rows = [];
// This variable will hold a JSON data structure that we can return as
// a VARIANT.
// This will contain ALL the rows in a single "value".
var table_as_json = {};
// Run SQL statement(s) and get a resultSet.
var command = "SELECT TOP 5 * FROM " + TABLE_NAME + " WHERE N = " + PARAM;
var cmd1_dict = {sqlText: command};
var stmt = snowflake.createStatement(cmd1_dict);
var rs = stmt.execute();
// Read each row and add it to the array we will return.
var row_num = 1;
while (rs.next()) {
// Put each row in a variable of type JSON.
row_as_json = {};
// For each column in the row...
for (var col_num = 0; col_num < COL_NAMES.length; col_num = col_num + 1) {
var col_name = COL_NAMES[col_num];
row_as_json[col_name] = rs.getColumnValue(col_num + 1);
}
// Add the row to the array of rows.
array_of_rows.push(row_as_json);
++row_num;
}
// Put the array in a JSON variable (so it looks like a VARIANT to
// Snowflake). The key is "key1", and the value is the array that has
// the rows we want.
// table_as_json = { "key1" : array_of_rows };
// Return the rows to Snowflake, which expects a JSON-compatible VARIANT.
return array_of_rows;
$$
;
/*
RECAP
Purpose:
Create two procedures for our API to call.
We want to expose our table data to a proc and returned as JSON:
procGetLastTen: get last ten records
procGetRecord: pass in ID variable N, and get that record returned
Benefits:
Procs provide advanced business logic, encapsulation, and reusability
API calls allow a limited, governed surface to expose Snowflake data
*/