UPDATED Snowflake: Select Show to JSON

As described previously in https://bigdatadave.com/2020/02/15/108/ the ‘SHOW’ system object command works well in Snowflake, but sometimes you need to have more than Like ‘string%’ syntax to accomplish administrative tasks. For full context read the previous article and come on back.

The Show Command
The most common method to get the result of the SHOW command is using a SELECT * FROM RESULT_SCAN() function.

-- This one works, turn into Dynamic UDF
SHOW USERS;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Object Construct
Turns out getting to the JSON payload is way easier than writing an entire JavaScript Stored Procedure to convert a query resultset into JSON via a multidimensional associative array, you can just use OBJECT_CONSTRUCT() https://docs.snowflake.net/manuals/sql-reference/functions/object_construct.html.

--From the Land Down Under
SHOW USERS;
SELECT OBJECT_CONSTRUCT(*) FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

The GET_SHOW() code is no longer necessary…this one line OBJECT_CONSTRUCT(*) replaces almost the entire GET_SHOW() procedure from the previous article.

--RIP GET_SHOW()
CREATE OR REPLACE PROCEDURE GET_SHOW(OBJECT STRING)
    RETURNS ARRAY
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    //First call the show command for the input object
    var sqlcmd = `SHOW ` + OBJECT + `;`
    var stmt = snowflake.createStatement( { sqlText: sqlcmd } );
    stmt.execute();
     
    //Next get the result set from the show sql ran above
    var sqlcmd = `SELECT * FROM TABLE(RESULT_SCAN('` + stmt.getQueryId() + `'));`;
    var stmt1 = snowflake.createStatement( { sqlText: sqlcmd } );
    var resultSet = stmt1.execute();
     
    //Get the column count for the while loop and create a blank array
    c = stmt1.getColumnCount();
    valueArray = [];
    //Step through each record returned in the result set and right the output obj to an associative array based on column names
    while (resultSet.next()) {
        i = 1;
        let obj = new Object;
        while  (i <= c) {
            obj[resultSet.getColumnName(i).toString()] = resultSet.getColumnValue(i);
            i++;
        }
        //Store the row JSON object to the array
        valueArray.push(obj);
    }
    //Return the JSON payload for the entire table scanned
    return valueArray;
    $$;

Time to Penance Code
Let’s combine GET_SHOW(), OBJECT_CONSTRUCT() and STORE_GET_SHOW() into one function and this time add in error handling via try catch.

CREATE OR REPLACE PROCEDURE STORE_GET_SHOW2 (OBJECT STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    try {
      //First call the show command for the input object
      var sqlcmd = `SHOW ` + OBJECT + `;`
      var stmt = snowflake.createStatement( { sqlText: sqlcmd } );
      stmt.execute();
 
      //Now create a table with the input Object's name, no spaces
      var sqlcmd = `CREATE OR REPLACE TABLE ` + OBJECT.replace(" ","_") + `_TABLE (V VARIANT);`;
      var stmt1 = snowflake.createStatement( { sqlText: sqlcmd } );
      stmt1.execute();
 
      //Beautiful line of SQL that inserts the data as JSON into our input Object table while transforming the JSON payload into one record per record in the Show Command. 
      var sqlcmd = `INSERT INTO ` + OBJECT.replace(" ","_") + `_TABLE (SELECT OBJECT_CONSTRUCT(*) FROM TABLE(RESULT_SCAN('` + stmt.getQueryId() + `')));`;
      var stmt2 = snowflake.createStatement( { sqlText: sqlcmd } );
      stmt2.execute();
    // Return a success/error indicator
    return "Succeeded.";   
    }
     
    catch (err)  {
      // Return a success/error indicator.
      return "Failed: " + err;
    }
    $$;
 
--Testing
CALL STORE_GET_SHOW2('TABLES');
SELECT * FROM TABLES_TABLE;
 
CALL STORE_GET_SHOW2('USERS');
SELECT * FROM USERS_TABLE;
 
CALL STORE_GET_SHOW2('USER FUNCTIONS');
SELECT * FROM USER_FUNCTIONS_TABLE;

Now STORE_GET_SHOW2() can be called with any object and turn it into valid JSON stored in a table.

Conclusion
Now any Snowflake Object can be retrieved as or stored to JSON in a table. The JSON can be queried directly to answer even deeper questions or be pulled into other systems for audit purposes. Happy (more efficient) querying!

--Query Time (CaSe SeNsItIvE CoLuMn NaMeS)
SELECT V:login_name::STRING AS NAME FROM USERS_TABLE WHERE V:disabled = 'false';

Originally published at http://bigdatadave.com on February 17, 2020.