Frequently used queries in Snowflake | Snowflake query samples

{tocify} $title={Table of Contents}


While working with snowflake, there are many queries which we make use of very frequently or to say in another words if you are using Snowflake then few scenarios will always be part of it.

Here, the intention of post is to have a quick reference point to such piece of codes.

Before we start, make note of following 

You don't have any client like SQL Server Management studio(SSMS) to interact with Snowflake, so whatever is to be done is through Snowflake UI.

Also unlike other database warehouse systems, only few objects are visible when you visit the Snowflake UI i.e. Databases, Tables and Views

visible database objects in snowflake

Other objects like stored procedures , tasks , streams etc are abstracted and can be dealt with the help of queries.

Create queries

Below are the sample queries for creating database objects in snowflake

Create Table 

create or replace TABLE CITY (
    CreateUser STRING,
    CreateDate TIMESTAMP_NTZ(9),
    LastUser STRING,
    LastUpdate TIMESTAMP_NTZ(9),
    Status STRING,
    Name STRING,
    ST_Code STRING,
    CT_Code STRING,
    Zip STRING,
    Latitude FLOAT,
    Longitude FLOAT,
    POBoxOnly STRING

Create View 

create view city_pune as
select *
from city
where ct_code = 'Pune';

Create Stored Procedure

AS ' 
var sql_command_begin = ''begin;'';  
var sql_command_merge = ''merge into ........'' ; 
var sql_command_commit = ''commit;''; 

try { 
	snowflake.execute ({sqlText: sql_command_begin}); 
	snowflake.execute ({sqlText: sql_command_merge});  
	snowflake.execute ({sqlText: sql_command_commit});  
	return ''Succeeded.'';   // Return a success/error indicator.
catch (err) {   
 return ''Failed: '' + err;   // Return a success/error indicator.

Create Stored Procedure with parameters

var command = "INSERT INTO CITY (Name,Zip) VALUES ('"+NAME+"','"+ZIP+"')";  
var cmd = {sqlText: command};  
var stmt = snowflake.createStatement(cmd);  
var rs = stmt.execute();  
return 'success';  

While dealing with stored procedure with parameter, make sure parameter name should be in small case , but while using it inside the stored procedure it should be in capital

Create Stream

create stream CITY_STREAM on table CITY

If any changes happen in the CITY table, that will be captured in CITY_STREAM.

Note : You can create multiple stream on a Table 

Create Task

create or replace task CITY_TASK
	schedule='USING CRON 5 * * * * UTC'

Alter Queries

Once you have created an object, and later if there arises a need to make change in the definition or status of an object, Alter commands can be used.

Say to change data type of column staus in table City, following query can be used

Alter Table 

Alter table CITY alter STATUS set data type varchar(50)

Alter Task 

By default when Task is created it is not enabled, to enable it we use resume command in association with Alter

Alter task CITY_TASK resume
To disable the tasks, we can
Alter task CITY_TASK suspend

Update Table 

To update a row in a Snowflake table, just use the UPDATE statement with a WHERE clause
update City
  set ST_CODE = 'MH'
  where CT_CODE='Pune'

To update all rows in a Snowflake table, just use the UPDATE statement without a WHERE clause
update City
  set ST_CODE = 'MH'
  where CT_CODE='Pune'

Get definition queries

When you need to know the how the particular object is created in snowflake or if you are planning to recreate the particular object you need - for that we can make use of get_ddl command

select get_ddl()

To get the definition of a particular Table

select get_ddl('table','CITY')

To get the definition of a particular stored procedure

select get_ddl('procedure','SPROC_CITY_DATAUPSERT()')

To get the definition of a particular Task

select get_ddl('task','CITY_TASK')

To get the definition of a particular Stream

select get_ddl('stream','CITY_STREAM')

List of object queries 

There are many objects which are not visible in Snowflake but you can search for it and get details about it,  for that we can make use of show command

To get the list of stored procedures

show procedures - It displays all the stored procedures in the current Database belonging to the selected schema.

show procedures in schemaname

To get the list of tasks

show tasks

show tasks in schema schemaname

To get the list of streams

show streams

Learn More about Snowflake

Post a Comment

If you have any suggestions or questions or want to share something then please drop a comment

Previous Post Next Post