Snowflake: Select Show to JSON

Published in

BigDataDave

6 min readFeb 16, 2020

UPDATED 17Feb2020: Turns out there is a more efficient way to Select Show <Object> to JSON. My friend Alan from the Last Data Bender: https://blog.databender.net/ enlightened me to the OBJECT_CONSTRUCT() function in Snowflake. All of the code here is still valid and works, it might not be the most efficient method possible as described in the Update: https://bigdatadave.com/2020/02/17/snowflake-update-select-show-object-to-json/. I encourage you to read this first to get the full context of the solution.

The Issue with the Show
The ‘SHOW’ system object command works well in Snowflake, but sometimes you need to have more than Like ‘string%’ syntax to accomplish administrative tasks. We need where predicates and object persistence for looking into advanced situations for locked users, users that have never logged into the system, or other administrative processes. You are going to need to be an ACCOUNTADMIN in Snowflake to code along.

Show Objects
The show objects are documented here: https://docs.snowflake.net/manuals/sql-reference/sql/show.html. They must be called ‘SHOW USERS;’ to return a list of users. You can use a Like ‘string%’ operator to pull back a specific user or pattern matching users.

--Show Commands
SHOW USERS;
SHOW USERS LIKE 'Curt%';
SHOW DATABASES;
SHOW ROLES;

What about CTEs
I first tried to use CTE to wrap the SHOW commands…but only SELECTs are allowed in CTEs. These commands just result in a SQL compilation error: syntax error line 1 at position 31 unexpected ‘USERS’.

--Common Table Expressions with Show
SELECT * FROM TABLE(SHOW USERS);
WITH CTE (login_name) AS (SHOW USERS)
SELECT * FROM CTE;

Google Time
Then after some google-foo most helpers point to this general format, but it is brittle, nonpersistent and not scalable.

-- This one works, turn into Dynamic UDF
SHOW USERS;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));-- Where Clause Too
SHOW USERS;
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "disabled" = true;

Code Our Way Out
Then I got to thinking and tinkering. I was gonna flex some JavaScript. By “flex some JavaScript” I mean spending a couple hours on W3Schools and StackOverflow because I am not a JavaScript programmer by default. I prefer SQL or Visual Basic, but, it was time to buckle up.

I was able to create a JavaScript Stored Procedure to return a JSON payload for any SHOW Object. This made me happy after 5 hours in front of the keyboard and a dozen failed attempts reminding me of my JavaScript dice roll. The snowflake Stored Procedure API is very well documented which helped get me out of the rabbit hole faster: https://docs.snowflake.net/manuals/sql-reference/stored-procedures-api.html

--Show Object to JSON Array
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;
    $$;--Test GET_SHOW()
CALL GET_SHOW('USERS');
CALL GET_SHOW('ROLES');
CALL GET_SHOW('WAREHOUSES');

With 3,015 users on my system this still runs in under 3 seconds and returns an 81,407 line valid JSON document! Yes, there was a small celebration dance when this first worked the way I wanted it to.

Why stop at 3 objects, let’s test them all.

--Let's Get a JSON Payload for Every Object Listed on: https://docs.snowflake.net/manuals/sql-reference/sql/show.html
--Account Operations:
CALL GET_SHOW('PARAMETERS');
CALL GET_SHOW('GLOBAL ACCOUNTS');
CALL GET_SHOW('REGIONS');
CALL GET_SHOW('REPLICATION DATABASES');--Account Object Types:
CALL GET_SHOW('PARAMETERS');
CALL GET_SHOW('FUNCTIONS');
CALL GET_SHOW('NETWORK POLICIES');
CALL GET_SHOW('SHARES');
CALL GET_SHOW('ROLES');
CALL GET_SHOW('GRANTS');
CALL GET_SHOW('USERS');
CALL GET_SHOW('WAREHOUSES');
CALL GET_SHOW('DATABASES');
CALL GET_SHOW('INTEGRATIONS');--Database Object Types:
CALL GET_SHOW('SCHEMAS');
CALL GET_SHOW('OBJECTS');
CALL GET_SHOW('TABLES');
CALL GET_SHOW('EXTERNAL TABLES');
CALL GET_SHOW('VIEWS');
CALL GET_SHOW('COLUMNS');
CALL GET_SHOW('FILE FORMATS');
CALL GET_SHOW('SEQUENCES');
CALL GET_SHOW('STAGES');
CALL GET_SHOW('PIPES');
CALL GET_SHOW('USER FUNCTIONS');
CALL GET_SHOW('PROCEDURES');

Turns Out More Code is the Answer
Now we need to store these representative JSON documents in a table. Storing the data as JSON natively in Snowflake is a good idea for a couple of reasons. Flexibility in query, transportability to audit systems and the destination tables will not break when new fields are inevitably added to the SHOW object.

I am particularly found of this line of SQL: `INSERT INTO ` + OBJECT.replace(“ “,”_”) + `_TABLE (SELECT JSON.VALUE FROM LATERAL FLATTEN (SELECT * FROM TABLE(RESULT_SCAN(‘` + stmt.getQueryId() + `’))) AS JSON);` It would be hard to do anything remotely like this in another SQL Engine. With Snowflake we can flatten the JSON payload and insert it into a dynamic table from the SHOW command all in 1 line of Beautiful SQL.

--Buckle Up and Code
CREATE OR REPLACE PROCEDURE STORE_GET_SHOW (OBJECT STRING)
    RETURNS STRING
    LANGUAGE JAVASCRIPT
    EXECUTE AS CALLER
    AS
    $$
    //First call the GET_SHOW(OBJECT) Stored Procedure from above with the input Object
    var sqlcmd = `CALL GET_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 JSON.VALUE FROM LATERAL FLATTEN (SELECT * FROM TABLE(RESULT_SCAN('` + stmt.getQueryId() + `'))) AS JSON);`;
    var stmt2 = snowflake.createStatement( { sqlText: sqlcmd } );
    stmt2.execute();
    $$;--Testing
CALL STORE_GET_SHOW('TABLES');
SELECT * FROM TABLES_TABLE;CALL STORE_GET_SHOW('USERS');
SELECT * FROM USERS_TABLE;CALL STORE_GET_SHOW('USER FUNCTIONS');
SELECT * FROM USER_FUNCTIONS_TABLE;

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

Why stop at 3 objects, let’s test them all.

--Let's Store a JSON Payload for Every Object Listed on: https://docs.snowflake.net/manuals/sql-reference/sql/show.html--Account Operations:
CALL STORE_GET_SHOW('PARAMETERS');
SELECT * FROM PARAMETERS_TABLE;CALL STORE_GET_SHOW('GLOBAL ACCOUNTS');
SELECT * FROM GLOBAL_ACCOUNTS_TABLE;CALL STORE_GET_SHOW('REGIONS');
SELECT * FROM REGIONS_TABLE;
SELECT V:cloud::String FROM REGIONS_TABLE;CALL STORE_GET_SHOW('REPLICATION DATABASES');
SELECT * FROM REPLICATION_DATABASES_TABLE;--Account Object Types:
CALL STORE_GET_SHOW('PARAMETERS');
SELECT * FROM PARAMETERS_TABLE;CALL STORE_GET_SHOW('FUNCTIONS');
SELECT * FROM FUNCTIONS_TABLE;CALL STORE_GET_SHOW('NETWORK POLICIES');
SELECT * FROM NETWORK_POLICIES_TABLE;CALL STORE_GET_SHOW('SHARES');
SELECT * FROM SHARES_TABLE;CALL STORE_GET_SHOW('ROLES');
SELECT * FROM ROLES_TABLE;CALL STORE_GET_SHOW('GRANTS');
SELECT * FROM GRANTS_TABLE;CALL STORE_GET_SHOW('USERS');
SELECT * FROM USERS_TABLE;CALL STORE_GET_SHOW('WAREHOUSES');
SELECT * FROM WAREHOUSES_TABLE;CALL STORE_GET_SHOW('DATABASES');
SELECT * FROM DATABASES_TABLE;CALL STORE_GET_SHOW('INTEGRATIONS');
SELECT * FROM INTEGRATIONS_TABLE;--Database Object Types:
CALL STORE_GET_SHOW('SCHEMAS');
SELECT * FROM SCHEMAS_TABLE;CALL STORE_GET_SHOW('OBJECTS');
SELECT * FROM OBJECTS_TABLE;CALL STORE_GET_SHOW('TABLES');
SELECT * FROM TABLES_TABLE;CALL STORE_GET_SHOW('EXTERNAL TABLES');
SELECT * FROM EXTERNAL_TABLES_TABLE;CALL STORE_GET_SHOW('VIEWS');
SELECT * FROM VIEWS_TABLE;CALL STORE_GET_SHOW('COLUMNS');
SELECT * FROM COLUMNS_TABLE;CALL STORE_GET_SHOW('FILE FORMATS');
SELECT * FROM FILE_FORMATS_TABLE;CALL STORE_GET_SHOW('SEQUENCES');
SELECT * FROM SEQUENCES_TABLE;CALL STORE_GET_SHOW('STAGES');
SELECT * FROM STAGES_TABLE;CALL STORE_GET_SHOW('PIPES');
SELECT * FROM PIPES_TABLE;CALL STORE_GET_SHOW('USER FUNCTIONS');
SELECT * FROM USER_FUNCTIONS_TABLE;CALL STORE_GET_SHOW('PROCEDURES');
SELECT * FROM PROCEDURES_TABLE;

Conclusion
Now any Snowflake Object can be retrieved as JSON, 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 Object querying!

--Query Time
SELECT V:login_name::STRING AS NAME FROM USERS_TABLE WHERE V:disabled = 'false';

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

Snowflake: Select Show to JSON

Written by David A Spezia