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() } )
Copy

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

  • If the original query is executed manually, only the user who runs the original query can use the RESULT_SCAN function to process the output of the query. Even a user with the ACCOUNTADMIN privilege cannot access the results of another user’s query by calling RESULT_SCAN.

  • If the original query is executed via a task, the role that owns the task, instead of a specific user, triggers and runs the query. If a user or a task is operating with the same role, they can use RESULT_SCAN to access the query results.

  • 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 might be slower than if you were querying an actual table.

  • The query containing the RESULT_SCAN can include clauses, such as filters and ORDER BY clauses, that were not in the original query. This allows you to narrow down or modify the result set.

  • A RESULT_SCAN is not guaranteed to return rows in the same order as the original query returned the rows. You can include an ORDER BY clause with the RESULT_SCAN query to specify a specific order.

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

    Classic Console:

    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:

  • If RESULT_SCAN processes query output that contained duplicate column names (for example, a query that JOINed two tables that have overlapping column names), then RESULT_SCAN references the duplicate columns with modified names, appending “_1”, “_2”, etc. to the original name. For an example, see the Examples section below.

  • Timestamps in Parquet files queried using the vectorized scanner sometimes display the time in a different time zone. Use the CONVERT_TIMEZONE function to convert to a standard time zone for all timestamp data.

Collation Details

When RESULT_SCAN returns the results of the previous statement, RESULT_SCAN preserves the collation specification(s) of the values that it returns.

Examples

Simple 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 |
+-------+
Copy

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

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

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

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

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'));
Copy

Examples using DESCRIBE and SHOW Commands

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 generated in lowercase, the commands use delimited identifier notation (double quotes) around the column names in the query to ensure that the column names in the query match the column names in the output that was scanned.

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

Process the result of a SHOW TABLES command to extract empty tables that are older than 21 days. The SHOW command generates lowercase column names, so the command quotes the names and use matching case:

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";
Copy

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 INTEGER)
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("bytes") AS "size"
    FROM table(RESULT_SCAN(LAST_QUERY_ID()))
    ORDER BY "bytes" DESC;
Copy

Examples Using a Stored Procedure

Stored procedure calls return a value. However, this value cannot be processed directly because you cannot embed a stored procedure call in another statement. To work around this limitation, you can use RESULT_SCAN to process the value returned by a stored procedure. A simplified example is below:

First, create a procedure that returns a “complicated” value (in this case, a string that contains JSON-compatible data) that can be processed after it has been returned from the CALL.

CREATE OR REPLACE PROCEDURE return_JSON()
    RETURNS VARCHAR
    LANGUAGE JavaScript
    AS
    $$
        return '{"keyA": "ValueA", "keyB": "ValueB"}';
    $$
    ;
Copy

Second, call the procedure:

CALL return_JSON();
+--------------------------------------+
| RETURN_JSON                          |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+
Copy

The next three steps extract the data from the result set.

Get the first (and only) column:

SELECT $1 AS output_col FROM table(RESULT_SCAN(LAST_QUERY_ID()));
+--------------------------------------+
| OUTPUT_COL                           |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+
Copy

Convert the output from a VARCHAR to a VARIANT:

SELECT PARSE_JSON(output_col) AS JSON_COL FROM table(RESULT_SCAN(LAST_QUERY_ID()));
+---------------------+
| JSON_COL            |
|---------------------|
| {                   |
|   "keyA": "ValueA", |
|   "keyB": "ValueB"  |
| }                   |
+---------------------+
Copy

Extract the value that corresponds to the key “keyB”:

SELECT JSON_COL:keyB FROM table(RESULT_SCAN(LAST_QUERY_ID()));
+---------------+
| JSON_COL:KEYB |
|---------------|
| "ValueB"      |
+---------------+
Copy

Here is a more compact way to extract the same data that was extracted in the previous example. This example has fewer statements, but is harder to read:

CALL return_JSON();
+--------------------------------------+
| RETURN_JSON                          |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+
SELECT JSON_COL:keyB 
   FROM (
        SELECT PARSE_JSON($1::VARIANT) AS JSON_COL 
            FROM table(RESULT_SCAN(LAST_QUERY_ID()))
        );
+---------------+
| JSON_COL:KEYB |
|---------------|
| "ValueB"      |
+---------------+
Copy

The output from the CALL uses the function name as the column name. For example:

+--------------------------------------+
|              RETURN_JSON             |
+--------------------------------------+
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+
Copy

We can use that column name in the query. Here’s one additional compact version, in which the column is referenceed by name rather than the column number:

CALL return_JSON();
+--------------------------------------+
| RETURN_JSON                          |
|--------------------------------------|
| {"keyA": "ValueA", "keyB": "ValueB"} |
+--------------------------------------+
SELECT JSON_COL:keyB
        FROM (
             SELECT PARSE_JSON(RETURN_JSON::VARIANT) AS JSON_COL 
                 FROM table(RESULT_SCAN(LAST_QUERY_ID()))
             );
+---------------+
| JSON_COL:KEYB |
|---------------|
| "ValueB"      |
+---------------+
Copy

Example with Duplicate Column Names

The following example shows that RESULT_SCAN effectively references alternate column names when there are duplicate column names in the original query:

Create two tables that have at least one column with the same name:

CREATE TABLE employees (id INT);
Copy
CREATE TABLE dependents (id INT, employee_id INT);
Copy

Load data into the two tables:

INSERT INTO employees (id) VALUES (11);
Copy
INSERT INTO dependents (id, employee_id) VALUES (101, 11);
Copy

Now execute a query for which the output will contain two columns with the same name:

SELECT * 
    FROM employees INNER JOIN dependents
        ON dependents.employee_ID = employees.id
    ORDER BY employees.id, dependents.id
    ;
+----+-----+-------------+
| ID |  ID | EMPLOYEE_ID |
|----+-----+-------------|
| 11 | 101 |          11 |
+----+-----+-------------+
Copy

Note that the output in Snowsight is different than the output shown above, since Snowsight handles duplicate column names automatically.

Now call RESULT_SCAN to process the results of that query. Note that if different columns that have the same name in the results, RESULT_SCAN uses the original name for the first column and assigns the second column a modified name that is unique. To make the name unique, RESULT_SCAN appends the suffix “_<n>” to the name, where “<n>” is the next number available that produces a name that is different from the names of the previous columns.

SELECT id, id_1, employee_id
    FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
    WHERE id_1 = 101;
+----+------+-------------+
| ID | ID_1 | EMPLOYEE_ID |
|----+------+-------------|
| 11 |  101 |          11 |
+----+------+-------------+
Copy