CODEWITHSUNDEEP
snowflake-cloud-data-platform
Senthil
Senthil

Reputation: 11

Query `list stage` in Snowflake UDF

I am trying to write a Snowflake UDF that accepts a stage name and specific folder name as input parameters and returns the latest file id ( striping from full file name) as the output. Could anyone help me with a simple code to achieve this?

Upvotes: 1

Views: 1045

Answers (1)

Josh
Josh

Reputation: 2845

I'm not sure if you want a UDF or stored procedure. The syntax to create would be similar so I think this can help. Here is a stored procedure which will fetch latest staged file from a given stage and path. Just be aware of the limit 1 in query, multiple staged files may share the same last modified date while this procedure returns a scalar (single) value.

Stored Procedure Definition

create or replace procedure "MYDB"."MYSCHEMA"."LATEST_STAGED_FILE"(stage_name text, folder text) 
returns string not null 
language javascript  
execute as caller 

as 
$$ 
var sql_text = "list @" + STAGE_NAME + "/" + FOLDER ; 
var sql_command0 = snowflake.createStatement({ sqlText: sql_text}); 
var sql_command1 = snowflake.createStatement({ sqlText:`SELECT "name" FROM table(result_scan(last_query_id())) WHERE "last_modified" = (select MAX("last_modified") from table(result_scan(last_query_id()))) LIMIT 1;`});

try { 
    sql_command0.execute();
    var resultSet = sql_command1.execute(); 
    while(resultSet.next()) 
    {  
        var resultFile = resultSet.getColumnValue('name').split("/")
        return resultFile[resultFile.length - 1]
    }
}
catch (err) { 
    return "Failed: " + err;
}
$$;

You can then call the stored procedure like

call "MYDB"."MYSCHEMA"."LATEST_STAGED_FILE"('MYDB.MYSCHEMA.MYSTAGE', 'mypath/myotherpath');

References

select from list @

list stage via SP

Upvotes: 2

Related Questions