Frequently used queries in Snowflake | Snowflake query samples

{tocify} $title={Table of Contents}


Introduction

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 (
	ID STRING,
    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

CREATE OR REPLACE PROCEDURE "SPROC_CITY_DATAUPSERT"()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
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

CREATE OR REPLACE PROCEDURE SPROC_CITY_INSERT(name varchar,zip varchar)  
RETURNS VARCHAR  
LANGUAGE JAVASCRIPT  
AS  
$$  
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
	warehouse=WH_DEV
	schedule='USING CRON 5 * * * * UTC'
	when SYSTEM$STREAM_HAS_DATA('CITY_STREAM')
	as CALL SPROC_CITY_DATAUPSERT();


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