snowflake_cheatsheet

-- Deep copy
CREATE OR REPLACE TABLE table1_deep_copy AS SELECT * FROM table1;
-- Shallow copy
CREATE OR REPLACE TABLE table1_shallow_copy LIKE table1;
-- Temporary - for current session. Cannot be cloned.
CREATE TEMPORARY TABLE cust_temp AS SELECT * FROM customers;
-- Transient - multisession, does not use fail-safe storage.
CREATE TRANSIENT TABLE cust_temp AS SELECT * FROM customers;

-- Clone
create database mytestdb_clone clone mytestdb;
create schema source_clone clone source;
create table orders_clone_restore clone orders;
create table orders_clone_restore clone orders at (timestamp => to_timestamp_tz('04/05/2013 01:02:03', 'mm/dd/yyyy hh24:mi:ss'));
create table orders_clone_restore clone orders before (statement => '8e5d0ca9-005e-44e6-b858-a8f5b37c5726');

-- Generate 365 days
SLECT 
(to_date('2020-01-01') + SEQ4()) cal_dt, 
DATE_PART(day, cal_dt) AS cal_dom, -- day of month
DATE_PART(month, cal_dt) AS cal_month, -- month of year
DATE_PART(year, cal_dt) AS cal_year, -- month of year
DATE_TRUNC('month', cal_dt) AS cal_first_dom,
DATEADD('day', -1, DATEADD('month', 1, DATE_TRUNC('month', cal_dt))) AS cal_last_dom,
DECODE(cal_month,
'1', 'January') as cal_month_name,
DATEADD('day', -1, DATEADD('month', 3, DATE_TRUNC('quarter', cal_dt))) as cal_qtr_end_dt
FROM TABLE(GENERATOR(ROWCOUNT => 365));

ALTER SESSIOn USE_CACHED_RESULT = FALSE/TRUE;

-- HyperLogLog algorythm for approximate counts
APPROX_COUNT_DISTINCT(column) vs COUNT(DISTINCT column);

/*
=============== SP EXAMPLE ===============
*/
CREATE OR REPLACE PROCEDURE sp_some_name(parm1 FLOAT) 
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$

var variable = "";
var sql_command = "SELECT 1 FROM some_table";

snowflake.execute({sqlText: "BEGIN WORK;"});
//start transaction

try {
  snowflake.execute({sqlText: sql_command});
  snowflake.execute({sqlText: "COMMIT WORK;"});
  } catch(err) {
   snowflake.execute({sqlText: "ROLLBACK WORK;"});
   return "Failed: " + err;
  }
  return "Success"
$$;

ALTER SESSION SET AUTOCOMMIT = FALSE;
CALL sp_some_name(1000);

/*
=================== Order Analytics over Window Frames ============
*/

AVG(column) OVER (PARTITION BY column_id ORDER BY order_id)

/*
=================== Sequence Generation =======================
*/
CREATE SEQUENCE SQ1 START WITH = 777 INTERVAL = 111;
SELECT SQ1.NEXTVAL;

/****Useful Function*****/
SELECT RANDSTR(10, RANDOM())

/* 
================================UDFs ===================    
*/
--Scalar UDF using SQL. 
-- Arithmetic,  call other UDF, aggregate table data.
CREATE FUNCTION square(val float)
RETURNS float
AS
$$
 val * val
$$
;
SELECT square(5);

--Table UDF using SQL
CREATE FUNCTION LocationTimeZone()
RETURNS TABLE(LocationName String, TimeZoneName String)
AS
$$
  SELECT 'Sydney', 'GMT+11'
  UNION
  SELECT 'Auckland', 'GMT+13';
$$
;
SELECT TimeZoneName FROM TABLE(LocationTimeZone()) WHERE LocationName = 'Sydney';

--Scalar UDF using JavaScript. 
-- Arithmetic,  call other UDF, aggregate table data.
CREATE FUNCTION square(val float)
RETURNS float
LANGUAGE JAVASCRIPT
AS
$$
 return VAL * VAL;
$$
;
SELECT square(5);

--Table UDF using JavaScript
CREATE FUNCTION CountryISO()
RETURNS TABLE(CountryCode String, CountryName String)
LANGUAGE JAVASCRIPT
AS
$${
processRow: function f (row, rowWriter, context) {
  rowWriter.writeRow({COUNTRYCODE: "AU", COUNTRYNAME: "Australia"});
  rowWriter.writeRow({COUNTRYCODE: "NZ", COUNTRYNAME: "New Zealand"});
}
}
$$
;
SELECT * FROM TABLE(CountryISO()) WHERE CountryCode = 'NZ';

CREATE FUNCTION StringSize(input STRING)
RETURNS TABLE(Size FLOAT)
LANGUAGE JAVASCRIPT
AS
$${
processRow: function f (row, rowWriter, context) {
  rowWriter.writeRow({SIZE: row.INPUT.length});
}
}
$$
;
SELECT * FROM TABLE(StringSize('TEST'));
SELECT * FROM some_real_table,
TABLE(StringSize(some_real_table.column1));