i'm very new to JS especially in snowflake and i am trying to achieve a very simple thing - i would like to update an array according to an index for each row.
this is my code:
CREATE TABLE T1(C1 INT); -- test table
INSERT INTO T1(C1) VALUES (1),(2),(3),(4),(5),(6),(7);
-- MY UDTF:
CREATE OR REPLACE FUNCTION "SUMMER"(INF FLOAT)
RETURNS TABLE (NUM float, NUM2 array)
LANGUAGE JAVASCRIPT
AS '{
processRow: function (row, rowWriter, context) {
this.ar[this.index]=-1;
rowWriter.writeRow( {NUM: this.index, NUM2: this.ar} );
this.index=(this.index+1)%3;
},
finalize: function (rowWriter, context) {
rowWriter.writeRow({NUM: 0,NUM2: [0,0,0,0]});
},
initialize: function(argumentInfo, context) {
this.ar=[0,0,0,0]; --array i want to update at specific index
this.index = 0; --index changing every row
}}';
SELECT * FROM T1,TABLE(SUMMER(C1::FLOAT));
Result I receive is:
1 0 [ -1, -1, -1, 0 ]
2 1 [ -1, -1, -1, 0 ]
3 2 [ -1, -1, -1, 0 ]
4 0 [ -1, -1, -1, 0 ]
5 1 [ -1, -1, -1, 0 ]
6 2 [ -1, -1, -1, 0 ]
7 0 [ -1, -1, -1, 0 ]
0 [ 0, 0, 0, 0 ]
Whereas i was hoping to be able to update the array according to an index, hence receive following arrays:
[0,0,0,0]
[-1,0,0,0]
[-1,-1,0,0]
[-1,-1,-1,0]
[-1,-1,-1,-1]
[-1,-1,-1,-1]
[-1,-1,-1,-1]
there are 3 problems, you are outputing N + 1 rows of output, but you say you want N (7) for the seven lines. Which implies you should drop the finalizer, as it's giving you the extra row. OR you do want the n+1 so you can have all the before and the final after, where as you code code is writing after, and a fake before (but afterwards). If you want the before, write the before in the initializer.
second problem, your array is 4 wide, and you want all four values changed, but you are modulating by 3. That needs to go to 4 it would seem.
And lastly, all your output values appear to be the make value, because you are assigning the "reference" to the array as the result. So you need to clone the array to make clean new data. This question implies slice is a good way to go thus:
so sticking with the N+1 way I would use:
I have made a tiny change to your set to
-1
to sub 1, so we can see the wrap-round is working:gives:
so if you want to have the last row first, push the NULLS FIRST
gives:
Another option:
So if you really are trying to build an array of zeros and negative ones, you can just do it in SQL:
gives:
and that can be done in a one line wonder:
gives: