Snowflake System Function Error: Argument 0 to Function SYSTEM$PIPE_STATUS Needs to Be Constant

select system$pipe_status(pipe_catalog || '.' || pipe_schema || '.' || pipe_name)
from snowflake.account_usage.pipes
where pipe_catalog = 'REPLICATION_TARGET';

001015 (22023): SQL compilation error:
argument 0 to function SYSTEM$PIPE_STATUS needs to be constant, found 'PIPES.PIPE_CATALOG || '.' || PIPES.PIPE_SCHEMA || '.' || PIPES.PIPE_NAME'

Oops. Well, it may be that the function isn’t getting a proper value due to the concatenation function. I’ll try a different approach:

with data as (
    select pipe_id,
        pipe_name,
        created,
        last_altered,
        pipe_catalog || '.' || pipe_schema || '.' || pipe_name as fqn
    from snowflake.account_usage.pipes
)
select pipe_id,
    pipe_name,
    created,
    last_altered,
    system$pipe_status(fqn)
from data
order by 1,2,3;

001015 (22023): SQL compilation error:
argument 0 to function SYSTEM$PIPE_STATUS needs to be constant, found 'DATA.FQN'

No luck, and this is where the headache starts. After researching documentation and the Snowflake community I found very little about how to use this system function, or any other, the way I want. It appears that the only way is to programmatically generate static SQL to call the system function, and this is valid not only for system$pipe_status but surely for the rest of system functions as well.

Enter stored procedures

Being a long term Oracle DBA, I’m used to this kind of situation. A simple SQL*Plus script that dynamically generates the SQL I want to execute, then executes it is a piece of cake for me. Of course the Snowflake CLI equivalent to SQL*Plus, SnowSQL, allows for this approach but I wanted to go one step further. Now, Snowflake has this very interesting option of using JavaScript (JS) inside stored procedures (SP) and user defined functions (UDT), so I decided to try this path.

I don’t consider myself a developer but I’ve tried different languages here and there so I chose to go for a stored procedure with JS. The idea being to use SQL to generate the static call to the function and obtain the status for each Snowpipe in a given database. So, exactly the same premise I started with, only this time I have a limitation on what tools I can use to execute it.

After much fiddling about with the documentation and the JavaScript examples found in it, I came up with the following code:

create or replace procedure all_pipes_statuses(IN_DATABASE string)
returns array
language javascript
strict
as
-- The "$$" delimiter marks the beginning and end of the JavaScript.
$$
try {
    var array_of_statuses = [];    
    var resultRow =  {};
    var pipeFQN_stmt = snowflake.createStatement(
        {
            sqlText: "select pipe_catalog || '.' || pipe_schema || '.' || pipe_name as fqn from snowflake.account_usage.pipes where pipe_catalog = ?;",
            binds:[IN_DATABASE]
        }
    );    

    var pipeFQNs = pipeFQN_stmt.execute();

    while (pipeFQNs.next()) {

        // Obtain the FQN of the current pipe
        var my_PipeFQN = pipeFQNs.getColumnValue('FQN');        

        var pipeStatus_stmt = snowflake.createStatement(
                {
                    sqlText: "select system$pipe_status(?) as status;",
                    binds:[my_PipeFQN]
                }
        );

        var pipeStatus = pipeStatus_stmt.execute();
        pipeStatus.next();

        var my_pipeStatus = pipeStatus.getColumnValue('STATUS');
        
        resultRow =  {};
        resultRow['pipeName'] = my_PipeFQN
        resultRow['pipeStatus']=my_pipeStatus;
        array_of_statuses.push(resultRow);
    }
    
    return array_of_statuses; 
}
catch (err) {
    return "Failed: " + err; 
}
$$;

Remember, I’m not a developer. I’m sure there are better ways to achieve the above in a more efficient and clean way. I’m always happy to receive advice on how to improve my efforts.

Moving on, with the stored procedure in place I can call it to obtain the information I’m looking for:

call all_pipes_statuses('REPLICATION_TARGET');
+-----------------------------------------------------------------------------+
| ALL_PIPES_STATUSES                                                          |
|-----------------------------------------------------------------------------|
| [                                                                           |
|   {                                                                         |
|     "pipeName": "REPLICATION_TARGET.PUBLIC.TEST_PIPE",                      |
|     "pipeStatus": "{\"executionState\":\"RUNNING\",\"pendingFileCount\":0}" |
|   },                                                                        |
|   {                                                                         |
|     "pipeName": "REPLICATION_TARGET.PUBLIC.TEST_PIPE2",                     |
|     "pipeStatus": "{\"executionState\":\"RUNNING\",\"pendingFileCount\":0}" |
|   }                                                                         |
| ]                                                                           |
+-----------------------------------------------------------------------------+
1 Row(s) produced. Time Elapsed: 2.557s

While I got the information, it isn’t particularly easy to read, especially with the nested JSON, so I’m using the RESULT_SCAN function to get myself a better-looking result:

with results as (
    select value as nested_json
    from table(result_scan(last_query_id())) as res,
        lateral flatten(input=>res.$1)
),
data as (
    select value:pipeName name,
        parse_json(value:pipeStatus) fullstatus
    from results_full as res,
        lateral flatten(input=>res.all_pipes_statuses)
)
select name,
    fullstatus:executionState executionState,
    fullstatus:pendingFileCount pendingFileCount
from data;

+----------------------------------------+----------------+------------------+
| NAME                                   | EXECUTIONSTATE | PENDINGFILECOUNT |
|----------------------------------------+----------------+------------------|
| "REPLICATION_TARGET.PUBLIC.TEST_PIPE2" | "RUNNING"      | 0                |
| "REPLICATION_TARGET.PUBLIC.TEST_PIPE"  | "RUNNING"      | 0                |
+----------------------------------------+----------------+------------------+
2 Row(s) produced. Time Elapsed: 0.225s

Please note the use of last_query_id() in the call to result_scan(). This means that result_scan() will be working with the results of the previously executed query in the current session, so use it only after you call the stored procedure. Should you need to query the results again without calling the procedure, you need to use the Snowflake query id of the actual call to the stored procedure instead.

Conclusion

This whole issue started due to what I believe is a missing feature in Snowflake. There may be a good reason why a system function requires a constant as a parameter, but I can’t think of one. Maybe somebody reading this can shed some light on this question.

In any case, this gave me the opportunity to go deeper into the possibilities of JavaScript inside stored procedures. More importantly, I now have a foundation to use other system functions to help me audit Snowflake systems, so it ended up being a win-win situation.

I hope this helps someone else in the future. If it does, please leave a comment; I’d love to hear about it.