Question 219575 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Error: Unknown user-defined function L2."xx_proc_getDqtReport" in Snowflake UDF

Hi I am using the below code to create a function (UDF) in Snowflake:

create or replace function l2."xx_proc_getDqtReport"(schemaname text,tablename text)
returns table(tablename text,colname text,fillrate float,distvalues variant,dqtupdatetime timestamp)
language javascript
as
$$
    var sql1 = `create or replace table app.dqt_report_${tablename} (tablename text,colname text,fillrate float,distvalues variant,dqtupdatetime timestamp)`;
    var st1 = snowflake.createStatement({sqlText:sql1});
    var rs1 = st1.execute();

    var colquery = `select distinct column_name
                    from information_schema.columns
                    where table_schema ilike '${schemaname}' -- put your schema name here
                        and table_name ilike '${tablename}'  -- put your table name here
                    order by column_name`;

    var colst = snowflake.createStatement({sqlText:colquery});
    var colrs = colst.execute();

    var useschemasql = `use schema ${schemaname}`;
    var useschemast =  snowflake.createStatement({sqlText:useschemasql});
    var useschemars = useschemast.execute();

    while(colrs.next()){
        var colname = colrs.getColumnValue(1);
        var sql2 = `insert into app.dqt_report_${tablename}
                with t1 as (
                    select count(*) from ${tablename}
                ),
                t2 as (
                    select count(*) as fillcount from ${tablename} where nullif(${colname},'') is not null
                )
                select ${tablename}, ${colname}, (fillcount/rowcount)*100 as fillrate,listagg(distinct ${colname},',') as distvalues,current_timestamp() as dqtupdatetime
                from ${tablename},t1,t2`;

        var st2 = snowflake.createStatement({sqlText:sql2});
        var rs2 = st2.execute();
    }

    var fetchreportsql = `select * from app.dqt_report_${tablename}`;
    var fetchreportst =  snowflake.createStatement({sqlText:useschemasql});
    var fetchreportrs = useschemast.execute();

    return table(fetchreportrs);
$$;

When I try to use the function using the below query:
select l2."xx_proc_getDqtReport"('l1','dtidr_elg_raw');,
it shows an error like this: Unknown user-defined function L2."xx_proc_getDqtReport"

  1. I used double quotes to avoid issues with the case
  2. Output of the function code:
    Function xx_proc_getDqtReport successfully created.
  3. But the function name is not available when checked with show user functions;
Tags:

2

Answers


  1. 0

    Snowflake UDTF should be part of from clause

    e.g.

    select * from table(l2."xx_proc_getDqtReport"('l1','dtidr_elg_raw'));
    Login or Signup to reply.
  2. 0

    You have to CREATE FUNCTION syntax of a Javascript User Defined Tabular Function, that is a per row opperator. But have the wrong body syntax for this, for those you must define three explicit functions that get called before rows, for each row, and after the last row of a partition batch.

    The body of you code is a JavaScript Stored procedure (that also has a number of errors in it)

    so a simple UDTF:

    create or replace function simple_udtf(parameter1 text) returns table(coulmn_1 text)
language javascript
as
$$
   var simple_sql = `select ${parameter1} || '_extra_text' as column_1`;
    var simple_sql_statement =  snowflake.createStatement({sqlText:simple_sql});
    var simple_sql_result = simple_sql_statement.execute();

    return table(simple_sql_result);
$$;

    lets call it like a UDF:

    select simple_udtf('simple');

    Unknown function SIMPLE_UDTF

    now call it like a UDTF and it explodes because it is the wrong syntax:

    select * from table(simple_udtf('simple'));

    enter image description here

    If we remake this as a JavaScript Procedure, you cannot return TABLEs from those, but there are examples of return results sets are VARIANT data (json).

    enter image description here

    so in one way, what you are currently trying to do, cannot be done the way you are trying to do it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search