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.
Related Articles,
- Snowsql Command Line Options and Examples
- Execute SQL Script File using Snowflake Snowsql Variable Substitution
- Snowflake Snowsql Exit Codes for Unix/Linux Systems
Hope this helps 🙂