RESULT_SCAN in Snowflake

Spread the love

1. Introduction

In Snowflake, reusing the results of previously executed queries is often needed for tasks like debugging, validating outputs, or analyzing metadata commands such as SHOW and DESCRIBE. Accessing these results efficiently can be challenging.

To address this challenge, Snowflake provides the RESULT_SCAN function, which allows users to retrieve and work with the results of past queries efficiently.

2. RESULT_SCAN in Snowflake

RESULT_SCAN is a table function in Snowflake that retrieves the results of a previously executed query and presents them as a virtual table allowing users to run subsequent queries on the returned tabular data.

RESULT_SCAN is particularly useful in the following scenarios:

  • Capturing the result of DDL/DML statements in Snowflake Stored Procedures
  • Quickly inspect the results of previous queries during debugging or validation tasks.
  • Executing SHOW or DESCRIBE command.

3. Syntax of RESULT_SCAN

The following is the syntax of the RESULT_SCAN table function in Snowflake.

RESULT_SCAN ( '<Query ID>'  | LAST_QUERY_ID() )

A Query ID is a unique alphanumeric identifier assigned to each query executed in Snowflake. It typically follows a similar below format.

01b4c8df-0702-4852-0000-1c3d0f47ea28.

There are several ways to extract the Query ID in Snowflake.

3.1. Extracting Query ID from Snowflake UI

The Snowflake web interface displays Query IDs for all executed queries 

  • Under the Query Details section of the worksheet 
  • Under Monitoring > Query History details page.

3.2. Extracting Query ID using System Functions

The QUERY_HISTORY table function can be used to extract the Query ID of up to the last 100 queries run in the current session of the user.

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
ORDER BY START_TIME DESC;

3.3. Extracting Query ID using LAST_QUERY_ID()

The LAST_QUERY_ID() function returns the ID of the last executed query in the current session. A numeric argument can be passed to the function to retrieve the ID of a specific query based on its position in the query history.

Positive numbers start with the first query executed in the session. 

  • LAST_QUERY_ID(1) returns the ID of the first query.

Negative numbers start with the most recently-executed query in the session. 

  • LAST_QUERY_ID(-1) or LAST_QUERY_ID() returns the ID of the most recently-executed query.

4. Demonstration of RESULT_SCAN

The following example demonstrates how RESULT_SCAN table function can be used in Snowflake.

4.1. Extracting Query Results using RESULT_SCAN with Query ID

The following example extracts the results of a query using the ID of the executed query.

1. Execute a Query.

SELECT * FROM EMP;

-- Output:
+--------+----------+--------+---------+
| EMP_ID | EMP_NAME | SALARY | DEPT_ID |
+--------+----------+--------+---------+
|    101 | TONY     |  10000 |      10 |
|    102 | CHRIS    |  25000 |      20 |
+--------+----------+--------+---------+

2. Retrieve the Query ID of the Last Executed Query.

The Query ID can be retrieved using any of the methods described in the previous section of this article.

SELECT LAST_QUERY_ID();

-- Output:
+------------------------------------------+
| LAST_QUERY_ID()                          |
+------------------------------------------+
| 01b9c9dd-3201-623a-0000-000b99624171     |
+------------------------------------------+

3. Use RESULT_SCAN to get query results using the Query ID.

SELECT * FROM TABLE(RESULT_SCAN('01b9c9dd-3201-623a-0000-000b99624171'));

-- Output:
+--------+----------+--------+---------+
| EMP_ID | EMP_NAME | SALARY | DEPT_ID |
+--------+----------+--------+---------+
|    101 | TONY     |  10000 |      10 |
|    102 | CHRIS    |  25000 |      20 |
+--------+----------+--------+---------+

4.2. Applying Filters on the Query Results using RESULT_SCAN

The RESULT_SCAN function allows you to apply filters to the results of a previously executed query. This can be particularly useful refining data from commands like SHOW and DESCRIBE.

The following example filters the results of the SHOW TABLES statement using RESULT_SCAN.

-- Execute statement
SHOW TABLES;

-- Filtering results to extract tables of type Transient
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) 
WHERE "kind" = 'TRANSIENT';

The following example selects only required fields from the results of the SHOW TABLES statement using RESULT_SCAN.

-- Select only required fields using RESULT_SCAN
SELECT "database_name", "schema_name", "name" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

4.3. Using RESULT_SCAN in a Stored Procedure

When executing DDL or DML statements dynamically within Snowflake stored procedures, RESULT_SCAN can be a highly effective tool for logging or analyzing the result of these operations.

The following is an example of a Stored Procedure that captures the output of DML results using RESULT_SCAN.

CREATE OR REPLACE PROCEDURE sp_merge_table()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
  rows_inserted NUMBER;
  rows_updated NUMBER;
BEGIN
  --Merge data between two tables
  MERGE INTO EMP a USING EMP_RAW b ON a.EMP_ID = b.EMP_ID
  WHEN NOT MATCHED 
    THEN INSERT (EMP_ID, EMP_NAME, SALARY, DEPT_ID) 
    VALUES (b.EMP_ID, b.EMP_NAME, b.SALARY, b.DEPT_ID)
  WHEN MATCHED 
    THEN UPDATE SET a.EMP_NAME = b. EMP_NAME, a.SALARY = b.SALARY, a.DEPT_ID = b.DEPT_ID;
    
  -- Get number of rows inserted and updated
  SELECT $1, $2 INTO :rows_inserted, :rows_updated FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    
  -- Return Output
  RETURN 'Number of Rows Inserted:'||rows_inserted || '\nNumber of Rows Updated:' || rows_updated;
END;
$$
;

--Execute Stored Procedure
CALL sp_merge_table();

Output:

+--------------------------------+
| SP_MERGE_TABLE                 |
+--------------------------------+
| Number of Rows Inserted:0      |
| Number of Rows Updated:2       |
+------------------------+-------+

5. Summary

In this article, we have explored the RESULT_SCAN function in Snowflake, covering its syntax, use cases, and how it allows users to retrieve and work with the results of previously executed queries.

  • It covered how to retrieve query results using Query ID and LAST_QUERY_ID().
  • Demonstrated how to apply filters on query results using RESULT_SCAN.
  • Highlighted the use of RESULT_SCAN in stored procedures for capturing DDL/DML results.

Subscribe to our Newsletter !!

Related Articles:

Leave a Comment

Related Posts