Categories:
Table Functions

RESULT_SCAN

Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table. This is particularly useful if you want to process the output from any of the following:

The command/query can be from the current session or any of your other sessions, including past sessions, as long as the 24 hour period has not elapsed. This period is not adjustable. For more details, see Using Persisted Query Results.

See also:
DESCRIBE RESULT (Account & Session DDL)

Syntax

RESULT_SCAN ( { '<query_id>'  | LAST_QUERY_ID() } )

Arguments

query_id or LAST_QUERY_ID()
Either the ID for a query you executed (within the last 24 hours in any session) or the LAST_QUERY_ID function, which returns the ID for a query within your current session.

Usage Notes

  • Snowflake stores all query results for 24 hours. This function only returns results for queries executed within this time period.

  • Result sets do not have any metadata associated with them, so processing large results may be slower than if you were querying an actual table.

  • To retrieve the ID for a specific query, use any of the following methods:

    Web interface:

    In either of the following locations, click the provided link to display/copy the ID:

    • In Worksheets Worksheet tab, after executing a query, the results includes a link for the ID.
    • In History History tab, each query includes the ID as a link.
    SQL:

    Execute either of the following functions:

Examples

Retrieve all values greater than 1 from the result of your most recent query in the current session:

SELECT $1 AS value FROM VALUES (1), (2), (3);

+-------+
| VALUE |
|-------|
|     1 |
|     2 |
|     3 |
+-------+

SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) WHERE value > 1;

+-------+
| VALUE |
|-------|
|     2 |
|     3 |
+-------+

Retrieve all values from your second most recent query in the current session:

SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID(-2)));

Retrieve all values from your first query in the current session:

SELECT * FROM table(RESULT_SCAN(LAST_QUERY_ID(1)));

Retrieve the values from the c2 column in the result of the specified query:

SELECT c2 FROM TABLE(RESULT_SCAN('ce6687a4-331b-4a57-a061-02b2b0f0c17c'));

Process the result of a DESCRIBE USER command to retrieve particular fields of interest, for example the user’s default role. Note that because the output column names from the DESC USER command were printed in lowercase, we use delimited identifier notation (double quotes) around the column names in the query to make sure that the column names in the query match the column names in the output that we are scanning.

DESC USER jessicajones;
SELECT "property", "value" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
  WHERE "property" = 'DEFAULT_ROLE'
  ;

Process the result of a SHOW TABLES command to extract empty tables that are older than 21 days:

SHOW TABLES;
-- Show the tables that are more than 21 days old and that are empty
-- (i.e. tables that I might have forgotten about).
SELECT "database_name", "schema_name", "name" as "table_name", "rows", "created_on"
    FROM table(RESULT_SCAN(LAST_QUERY_ID()))
    WHERE "rows" = 0 AND "created_on" < DATEADD(day, -21, CURRENT_TIMESTAMP())
    ORDER BY "created_on";

Process the result of a SHOW TABLES command to extract the tables in descending order of size. This example also illustrates using a UDF to show table size in a slightly more human-readable format:

-- Show byte counts with suffixes such as "KB", "MB", and "GB".
CREATE OR REPLACE FUNCTION NiceBytes(NUMBER_OF_BYTES FLOAT)
RETURNS VARCHAR
AS
$$
CASE
    WHEN NUMBER_OF_BYTES < 1024
        THEN NUMBER_OF_BYTES::VARCHAR
    WHEN NUMBER_OF_BYTES >= 1024 AND NUMBER_OF_BYTES < 1048576
        THEN (NUMBER_OF_BYTES / 1024)::VARCHAR || 'KB'
   WHEN NUMBER_OF_BYTES >= 1048576 AND NUMBER_OF_BYTES < (POW(2, 30))
       THEN (NUMBER_OF_BYTES / 1048576)::VARCHAR || 'MB'
    ELSE
        (NUMBER_OF_BYTES / POW(2, 30))::VARCHAR || 'GB'
END
$$
;
SHOW TABLES;
-- Show all of my tables in descending order of size.
SELECT "database_name", "schema_name", "name" as "table_name", NiceBytes("rows") AS "size"
    FROM table(RESULT_SCAN(LAST_QUERY_ID()))
    ORDER BY "size" DESC;