Snowflake rowcount and QueryID of last Executed Query

The best part about Snowflake cloud data warehouse is that it provides almost all feature that are available in other relational databases. The MPP databases such as Teradata provides the number of rows affected by the last SQL statement within its BTEQ files. Similar feature is also available within SnowSQL which is a Snowflake command line interface. In this article, we will check how get rowcount and the queryID of the last executed Snowflake query.

Snowflake rowcount and QueryID of last Executed Query

SnowSQL includes a set of built-in variables that return metadata about statements executed in the current user session. The metadata such as the row count and query ID of previous executed query is stored in those variables.

These variables are useful when you are working with multiple SQL statements and want to store affected rows of each query for later use.

SnowSQL built-in Metadata Variables

SnowSQL supports two built in variables that return metadata information about SQL query.

__rowcount
__sfqid

Note that, each of these variable names begins with two underscore characters (“__”).

Snowflake __rowcount Variable

The __rowcount variable returns the number of rows affected by the most recent DML statement executed by the user on Snowflake.

Note that, you should enable the variable substitution on SnowSQL in order to use built-in variables.

Following command enables variable substitution on SnowSQL.

snuser#COMPUTE_WH@DEMO_DB.PUBLIC>!set variable_substitution=True

Now, let us check how to use built-in variable with an example.

Consider following set of SQL queries.

>select * from TEST1;
+------+
| COL1 |
|------|
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 Row(s) produced. Time Elapsed: 1.588s

>select &__rowcount;
+---+
| 5 |
|---|
| 5 |
+---+
1 Row(s) produced. Time Elapsed: 1.540s

As you can see, second query returns affected row count.

Snowflake __sfqid Variable

The __sfqid returns the query ID for the most recent query executed by the user.

Note that, you should enable the variable substitution on SnowSQL in order to use built-in variables.

Following command enables variable substitution on SnowSQL.

snuser#COMPUTE_WH@DEMO_DB.PUBLIC>!set variable_substitution=True

Now, let us check how to use built-in variable with an example.

Consider following example.

>select * from TEST1;
+------+
| COL1 |
|------|
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 Row(s) produced. Time Elapsed: 1.508s

>select '&__sfqid';
+----------------------------------------+
| '0192D053-0254-FB02-0011-B70300024D36' |
|----------------------------------------|
| 0192d053-0254-fb02-0011-b70300024d36   |
+----------------------------------------+
1 Row(s) produced. Time Elapsed: 1.594s

As you can see, second query returns query id.