snowflake UDTF update array at specific index

Question

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]

Simeon Pilgrim · Answer 1 · 2022-03-07T03:21:39.363

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:

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; -- tweaked to show it working
      this.ar[this.index] -=1; 
      rowWriter.writeRow( {NUM: this.index, NUM2: this.ar.slice(0)});
      this.index=(this.index+1)%4; 
    },
    finalize: function (rowWriter, context) {
     rowWriter.writeRow({NUM: 0, NUM2: [0,0,0,0]});
    },
    initialize: function(argumentInfo, context) {
     this.ar=[0,0,0,0]; 
     this.index = 0;
    }}';

SELECT * 
FROM (VALUES (1),(2),(3),(4),(5),(6),(7) ) as v(c1),
    TABLE(SUMMER(v.c1::FLOAT))
ORDER BY 1;

gives:

C1	NUM	NUM2
1	0	[ -1, 0, 0, 0 ]
2	1	[ -1, -1, 0, 0 ]
3	2	[ -1, -1, -1, 0 ]
4	3	[ -1, -1, -1, -1 ]
5	0	[ -2, -1, -1, -1 ]
6	1	[ -2, -2, -1, -1 ]
7	2	[ -2, -2, -2, -1 ]
null	0	[ 0, 0, 0, 0 ]

so if you want to have the last row first, push the NULLS FIRST

SELECT * 
FROM (VALUES (1),(2),(3),(4),(5),(6),(7) ) as v(c1),
    TABLE(SUMMER(v.c1::FLOAT))
ORDER BY 1 NULLS FIRST;

gives:

C1	NUM	NUM2
null	0	[ 0, 0, 0, 0 ]
1	0	[ -1, 0, 0, 0 ]
2	1	[ -1, -1, 0, 0 ]
3	2	[ -1, -1, -1, 0 ]
4	3	[ -1, -1, -1, -1 ]
5	0	[ -2, -1, -1, -1 ]
6	1	[ -2, -2, -1, -1 ]
7	2	[ -2, -2, -2, -1 ]

Another option:

So if you really are trying to build an array of zeros and negative ones, you can just do it in SQL:

SELECT c1
    ,ARRAY_CONSTRUCT(0,0,0,0) as zeros
    ,ARRAY_CONSTRUCT(-1,-1,-1,-1) as negs
    ,LEAST(c1-1, 4) as nf
    ,ARRAY_SLICE(negs, 0, nf) as np
    ,ARRAY_SLICE(zeros, nf, 4) as zp
    ,ARRAY_CAT(np, zp) as answer
FROM VALUES (1),(2),(3),(4),(5),(6),(7) as v(c1)
ORDER BY 1;

gives:

C1	ZEROS	NEGS	NF	NP	ZP	ANSWER
1	[ 0, 0, 0, 0 ]	[ -1, -1, -1, -1 ]	0	[]	[ 0, 0, 0, 0 ]	[ 0, 0, 0, 0 ]
2	[ 0, 0, 0, 0 ]	[ -1, -1, -1, -1 ]	1	[ -1 ]	[ 0, 0, 0 ]	[ -1, 0, 0, 0 ]
3	[ 0, 0, 0, 0 ]	[ -1, -1, -1, -1 ]	2	[ -1, -1 ]	[ 0, 0 ]	[ -1, -1, 0, 0 ]
4	[ 0, 0, 0, 0 ]	[ -1, -1, -1, -1 ]	3	[ -1, -1, -1 ]	[ 0 ]	[ -1, -1, -1, 0 ]
5	[ 0, 0, 0, 0 ]	[ -1, -1, -1, -1 ]	4	[ -1, -1, -1, -1 ]	[]	[ -1, -1, -1, -1 ]
6	[ 0, 0, 0, 0 ]	[ -1, -1, -1, -1 ]	4	[ -1, -1, -1, -1 ]	[]	[ -1, -1, -1, -1 ]
7	[ 0, 0, 0, 0 ]	[ -1, -1, -1, -1 ]	4	[ -1, -1, -1, -1 ]	[]	[ -1, -1, -1, -1 ]

and that can be done in a one line wonder:

SELECT c1
    ,ARRAY_CAT(
        ARRAY_SLICE(ARRAY_CONSTRUCT(-1,-1,-1,-1), 0, LEAST(c1-1, 4)), 
        ARRAY_SLICE(ARRAY_CONSTRUCT(0,0,0,0), LEAST(c1-1, 4), 4) 
    ) as answer
FROM VALUES (1),(2),(3),(4),(5),(6),(7) as v(c1)
ORDER BY 1;

gives:

C1	ANSWER
1	[ 0, 0, 0, 0 ]
2	[ -1, 0, 0, 0 ]
3	[ -1, -1, 0, 0 ]
4	[ -1, -1, -1, 0 ]
5	[ -1, -1, -1, -1 ]
6	[ -1, -1, -1, -1 ]
7	[ -1, -1, -1, -1 ]

thanks alot Simeon - indeed the trick was to use slice. As you pointed out i did several mistakes in my original question but it basically boils down to how to update arrays at given index. The code that works for me is this create or replace function summer(inf float) returns table(N1 float, N2 array) language javascript as $$ { initialize: function (ArgumentInfo,context ) { this.ar=[0,0,0,0] this.index=0 }, processRow: function (row,rowWriter,context){ this.ar[this.index]=-1; rowWriter.writeRow( { N1: this.index, N2:this.ar.slice(0)} ); this.index=(this.index+1)%4; }} $$; — pawelek69420, Mar 12 '22 at 17:35

snowflake UDTF update array at specific index

1 Answers1

Another option: