# Snowflake Context Functions General Context (8) current_client() current_date() current_ip_address() current_region() current_time() current_timestamp() current_version() sysdate()

Session Context (10) all_user_names() current_account() current_role() current_available_role() current_session() current_statement() current_transaction() current_user() last_query_id last_transaction

Session Object Context (8) current_database() current_schema() current_schemas() current_warehouse() invoker_role() invoker_share() is_granted_to_invoker_role() is_role_in_session()

show functions like 'CURRENT%'; use role sysadmin; use database my_db; use schema my_schema; use warehouse sap_load_wh;

How to use Set Variables

set my_wh = current_warehouse();
select $my_wh;
set (my_db, my_schema, my_wh) = (current_database(), current_schema(), current_warehouse());
select $my_db, $my_schema, $my_wh;
set my_search_path = current_schemas();
select $my_search_path;
select * from table(strtok_split_to_table($my_search_path,',')) f;

Snowflake Role Hierarchy & Context functions

USE ROLE securityadmin;

CREATE ROLE admin01; -- who can create all objects CREATE ROLE analyst01; -- analyst who can query objects CREATE ROLE developer01; -- developer role

-- make sure these roles are accessible upward GRANT ROLE developer01 TO ROLE admin01; GRANT ROLE analyst01 TO ROLE admin01; GRANT ROLE admin01 TO ROLE sysadmin;

--make sure role is mapped to user GRANT ROLE developer01 TO USER ADMIN; GRANT ROLE analyst01 TO USER ADMIN; GRANT ROLE admin01 TO USER ADMIN;

--give grants USE ROLE SYSADMIN; GRANT CREATE DATABASE ON ACCOUNT TO ROLE admin01; GRANT CREATE WAREHOUSE ON ACCOUNT TO ROLE admin01; -- GRANT CREATE DATABASE ON ACCOUNT TO ROLE dev_db_admin WITH GRANT OPTION;

-- now use new role USE ROLE admin01;

-- create a db and schema CREATE database db01; CREATE SCHEMA db01.schema01; CREATE WAREHOUSE wh01 WITH WAREHOUSE_SIZE = 'XLARGE' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 300 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1 SCALING_POLICY = 'STANDARD' COMMENT = 'this is load wh';

-- grant roles to other roles. GRANT USAGE ON WAREHOUSE wh01 TO ROLE analyst01; GRANT USAGE ON WAREHOUSE wh01 TO ROLE developer01; GRANT USAGE ON database db01 TO ROLE analyst01; GRANT USAGE ON database db01 TO ROLE developer01; GRANT USAGE ON SCHEMA db01.schema01 TO ROLE analyst01; GRANT USAGE ON SCHEMA db01.schema01 TO ROLE developer01;

GRANT create table ON SCHEMA db01.schema01 TO ROLE developer01;

GRANT SELECT ON ALL TABLES IN SCHEMA db01.schema01 TO ROLE analyst01; GRANT SELECT ON FUTURE TABLES IN SCHEMA db01.schema01 TO ROLE analyst01;

use role developer01; use schema db01.schema01 create OR replace table t01 (c1 string); insert into t01 (c1) values ('1');

use role analyst01; use schema db01.schema01 select * from t01;

show roles;

show roles like '%01%';

Context Function & Task

use role sysadmin; use schema my_db.my_schema; select current_schema(), current_database(), current_role();

create or replace table my_logging_01( log_id integer AUTOINCREMENT not null, user_name string, role_name string, db_name string, schema_name string, wh_name string );

-- lets check what happens when we insert insert into my_logging_01 (user_name,role_name,db_name,schema_name,wh_name) values ( current_user(), current_role(), current_database(), current_schema(), current_warehouse());

select * from my_logging_01;

-- create a task create or replace task my_log_task_001 warehouse = 'compute_wh' schedule = '1 minute' as insert into my_logging_01(user_name,role_name,db_name,schema_name,wh_name) values (current_user(),current_role(),current_database(),current_schema(),current_warehouse());

-- start the task alter task my_log_task_001 resume;

-- check the information schema for select * from table(information_schema.task_history()) where name ='MY_LOG_TASK_001' order by scheduled_time; select * from my_logging_01;

Date Time Context Functions

select current_date(), current_time(), current_timestamp(), sysdate() ;

Recursive CTE's

# List @main_db.external_stage.aws_stage; List files contained in stage

DESC STAGE MANAGE_DB.external_stages.aws_stage; File Formats // Creating schema to keep things organized CREATE OR REPLACE SCHEMA MANAGE_DB.file_formats;

// Creating file format object CREATE OR REPLACE file format MANAGE_DB.file_formats.my_file_format TYPE = CSV;

// See properties of file format object DESC file format MANAGE_DB.file_formats.my_file_format;

CREATE OR REPLACE STAGE MANAGE_DB.external_stages.aws_stage URL= 's3://bucketsnowflakes3' FILE_FORMAT = (FORMAT_NAME=MANAGE_DB.file_formats.my_file_format);

// Reset table CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS ( ORDER_ID VARCHAR(30), AMOUNT INT, PROFIT INT, QUANTITY INT, CATEGORY VARCHAR(30), SUBCATEGORY VARCHAR(30));

// Specifying file_format in Copy command COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS FROM @MANAGE_DB.external_stages.aws_stage files = ('OrderDetails.csv')

// Altering file format object ALTER file format MANAGE_DB.file_formats.my_file_format SET SKIP_HEADER = 1;

// Defining properties on creation of file format object
CREATE OR REPLACE file format MANAGE_DB.file_formats.my_file_format TYPE=JSON, TIME_FORMAT=AUTO;

// See properties of file format object
DESC file format MANAGE_DB.file_formats.my_file_format;

Account Usage