Frequently used queries in Snowflake | Snowflake query samples

byMaheshkumar Tiwari •3:14 PM

0

{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

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

Tags: Snowflake