I am trying to run a stored procedure from lookup inside adf.
CREATE OR REPLACE PROCEDURE merge_proc(src_table TEXT, tgt_table TEXT, unique_columns TEXT)
RETURNS float
not null
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
var row_count = 0;
var src_table_arr = src_table.split(".")
var schemasql = "select ordinal_position as position,column_name,data_type from information_schema.columns where table_schema ilike 'src_table_arr[0]' and table_name ilike 'src_table_arr[1]' order by ordinal_position"
var schemadetails = getschema(schemasql)
var unique_columns_arr = unique_columns.split(",")
var difference = schemadetails.filter(x => !unique_columns_arr.includes(x));
var column_values = (schemadetails.map(i => 'src.' + i)).join();
sql_command = "merge into DEMO_DB."+ tgt_table + " as target using (select "+ schemadetails.join() " from DEMO_DB." + src_table + " ) src on " + getJoinCondition(unique_columns) + " when matched then update set " + getColumnsToBeInserted(difference) + " when not matched then insert (" +
schemadetails.join() + ") values (" + column_values +")"
var stmt = snowflake.createStatement(
{
sqlText: sql_command
}
);
var res = stmt.execute();
res.next();
row_count = res.getColumnValue(1);
return row_count;
function getColumnsToBeInserted(difference) {
difference_column = "";
for (i = 0; i < difference.length; i++) {
difference_column = difference_column + 'target.' + difference[i] + " = " + 'src.' + difference[i] + ","
}
return difference_column.slice(0, -1)
}
function getJoinCondition(unique_columns) {
var a = unique_columns.split(","),
i,join_string;
for (i = 0; i < a.length; i++) {
join_string = join_string + 'target.' + a[i] + " = " + 'src.' + a[i] + "AND"
}
return join_string.slice(0, -3)
}
function getschema(schemasql) {
var schema_arr = [];
cmd1 = {sqlText: schemasql};
stmt = snowflake.createStatement(cmd1);
var rs = stmt.execute();;
while (result_set1.next()) {
schema_arr.push(result_set1.getColumnValue(2));
}
return schema_arr;
}
$$
;
call merge_proc('abc','xyz','cfd');
Getting a parse error: {"error":{"code":"BadRequest","message":"ErrorCode=InvalidTemplate, ErrorMessage=Unable to parse expression "}"}