Function , cursor , or procudure in dremio

1

can we create any function , cursor, or procedure in dremio . I have a requirement to create a procedure or function , cursor based on some conditions I want to add values to those columns .

2

There are a couple options available today

  1. Being open-source, Dremio allows the capabilities for users to create their own UDF (in Java). This will appear as a SQL function in the UI.
  2. While stored procedure’s aren’t directly supported today, a similar workaround is by creating VDSs and chaining them together. In other words, you would create a VDS with a logic you are trying to reuse, save it, secure it, and give users the ability to query and build upon it.
3

Can you provide a simple example? For example, I have SPs that use variables then DML language – are you suggesting we need to write a UDF with that embedded then rather than ‘call’ the SP, invoke it as a function?

REPLACE PROCEDURE xxx

BEGIN
BEGIN TRANSACTION;
CALL vds (parameters?);
END TRANSACTION;

more vsp dml statements?
    insert into xxx;
    update xxx;

END;

create vds?

REPLACE PROCEDURE vds (x,y,z)
PROCESS_USER:BEGIN
DECLARE USER_CNT INTEGER;
DECLARE CURR_TS TIMESTAMP;

SEL Current_Timestamp INTO CURR_TS;
	
SELECT Count(*) INTO USER_CNT
FROM ...;

IF USER_CNT = 0
	THEN LEAVE PROCESS_USER;
END IF;	 		

FOR  CUR_PTR AS MYPTR CURSOR FOR  	 
	SELECT xxx

DO

UPDATE x
INSERT INTO x	
END FOR;

END PROCESS_USER;