Snowflake Example SQL

/**** ACCOUNT    ***************************************/
/* Show user SQL history with time and credits used */
use role accountadmin;
use database snowflake;
use schema account_usage;
use warehouse interactive_wh;
alter session set timestamp_output_format = 'YYYY-MM-DD HH:MI:SS';
select start_time, query_type, user_name, warehouse_name, to_number(total_elapsed_time/1000,10,2) as seconds, warehouse_size, (CREDITS_USED_CLOUD_SERVICES*300) as cost, query_text
from snowflake.account_usage.query_history
where warehouse_size is not NULL
--and query_type = 'SELECT'
--and warehouse_name = 'XXXXX'
--and role_name = 'XXXXX'
--and user_name = 'XXXXX'
order by 4 desc;

use role accountadmin;
use database snowflake;
use schema account_usage;
use warehouse interactive_wh;
select start_time, query_type, Role_name, user_name, warehouse_name, to_number(total_elapsed_time/1000,10,2) as seconds, bytes_scanned, rows_produced, rows_inserted, (CREDITS_USED_CLOUD_SERVICES*300) as cost, query_text
from snowflake.account_usage.query_history
where query_type <> 'GET_FILES'
and query_type <> 'PUT_FILES'
and query_type <> 'SHOW'
and to_date(start_time) between '2020-07-09' and '2020-08-25';

/* show space taken by tables */
select table_name, table_schema, to_varchar(active_bytes/1024/1024, 'F999,999,999,999.9') as Mbytes from snowflake.account_usage.table_storage_metrics
order by 3 desc;

/* Show Credits used by Warehouse */
use role accountadmin;
use database snowflake;
use schema account_usage;
select 
  warehouse_name,
  sum(credits_used_cloud_services) credits_used_cloud_services,
  sum(credits_used_compute) credits_used_compute,
  sum(credits_used) credits_used
from snowflake.account_usage.warehouse_metering_history
where 
  start_time >= '2020-07-01 00:00:01'
group by 1;

/**** WAREHOUSE ***************************************/
/* Warehouse settings, auto suspend to 1 minute */
ALTER WAREHOUSE "MY_WAREHOUSE" SET WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 1 SCALING_POLICY = 'STANDARD' COMMENT = '';

/* warehouse change name */
ALTER WAREHOUSE  IF EXISTS  "PBI_USER" RENAME TO "PBI_WH";

SHOW tasks;
create or replace temporary table _tasks as
  SELECT 
    *,
    concat("database_name",'.',"schema_name",'.',"name") AS fully_qualified_name //We use this as an identifier
  FROM table(result_scan(last_query_id())) cc;
  
select * from _tasks;
  
alter warehouse test_wh set warehouse_size = medium;

/**** DATABASE ***************************************/
Alter database MY_DB rename to MY_PROD_DB;

CREATE DATABASE MY_DB COMMENT = 'Test DB';

CREATE SCHEMA "My_DB"."My_SCHEMA";

/**** SCHEMAS ***************************************/
use role accountadmin;
use database DW_PROD_DB;
select catalog_name as database,
       schema_name,
       schema_owner,
       created,
       last_altered
from information_schema.schemata
order by schema_name;

select catalog_name || ', ' || schema_name || ', ' || schema_owner as "Database-schema-owner"
from information_schema.schemata
order by schema_name;
/**** USER ***************************************/
show grants to role 'MY-ROLE';

alter user MY-USER SET DEFAUt_ROLE = 'PUBLIC';

alter user MY-USER set default_warehouse = 'MY-WAREHOUSE' default_role = 'MY-ROLE';

use role accountadmin;
use database dw_prod_db;
create role if not exists analyst_reader comment='Read-only access';

use role accountadmin;
use database my-db;
grant select on all tables in database my-db to role MY-ROLE;
grant select on future tables in database my-db to role MY-ROLE;
grant select on future tables in database my-db to role MY-ROLE;
grant select on all tables in database my-db to role MY-ROLE;
grant usage on all schemas in database my-db to role MY-ROLE;
grant select on future tables in database my-db to role MY-ROLE;
grant usage on future schemas in database my-db to role MY-ROLE;


CREATE USER MY-USER PASSWORD = '***********' LOGIN_NAME = 'MY-USER' DISPLAY_NAME = 'MY-USER' 
FIRST_NAME = 'first' LAST_NAME = 'Last' EMAIL = 'MY-USER@gmail.com' 
DEFAULT_ROLE = "MY-ROLE" DEFAULT_WAREHOUSE = 'MY-WAREHOUSE' MUST_CHANGE_PASSWORD = TRUE;
GRANT ROLE "MY-ROLE" TO USER MY-USER;

Drop schema DEMO_DB.DEMO_DB;

/**** COPY ***************************************/
copy into company_summary
 from @my_s3_stage/my-table-redshift-unload000;
 
 copy into employee_summary
  from @my_s3_stage file_format=(format_name = MY_CSV_FORMAT) 
  ON_ERROR=CONTINUE 
  pattern='my-table-redshift-unload0001_part_*';
  
/* Show S3 Bucket files */
select $1, $2 from @my_s3_stage/ACCOUNTS limit 100;

copy into opportunity
  from s3://ops-backups/dev/opportunity_sf 
  credentials=(aws_key_id='xxxxxxxxxxxxxxxxxxxxxxxx' 
               aws_secret_key='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
  file_format = (type = csv)
  
/* CSV stage with credentials for copy command */
create or replace stage my_s3_stage url='ops-backups/dev/'
 credentials=(aws_key_id='<key_id>' aws_secret_key='<key_secret>’')
 encryption=(master_key = '<encryption_key>')
 file_format = auto; 
 

use database MY_DB;
list @my_s3_stage/ACCOUNTS/;

  
/**** TABLE ***************************************/

create or replace temporary table _tasks as
  SELECT 
    *,
    concat("database_name",'.',"schema_name",'.',"name") AS fully_qualified_name //We use this as an identifier
  FROM table(result_scan(last_query_id())) cc;
  
/*** Create table ***/
  create or replace table company_summary (
      company_id bigint,
      status_desc varchar(32),
      name_size varchar(255),
      name_version varchar(255),
      name_industry varchar(255)
      );
  
select *
from snowflake.information_schema.tables 
  where table_schema <> 'INFORMATION_SCHEMA'
limit 10; 
  

/**** SELECT ***************************************/

/* select */
select * from "DEMO_DB"."PUBLIC"."COMPANY_SUMMARY"
limit 10;


select datediff (day, start_timestamp,end_timestamp) as diff_days, count(*) from employee_summary e
left outer join public.company_summary c on (e.company_id = c.app_company_id)where start_timestamp <> end_timestamp
and (c.is_demo <> 1 or is_demo is null)
group by 1
order by 2 desc
limit 10000;

SELECT e.city, count(*), count(distinct(e.company_id)) companies 
FROM  public.employee_summary e
left outer join public.company_summary c on (e.company_id = c.app_company_id)
where city in ('Provo', 'Orem', 'Lindon', 'Lehi', 'American Fork','Springville', 'Spanish Fork')
and (c.is_demo <> 1 or is_demo is null)
group by 1
order by 2 desc
limit 1000;

select count(*), country, city from EMPLOYEE_SUMMARY
group by 2,3
order by 1 desc
limit 300;
  
  
/**** INSERT ***************************************/

/* insert */
INSERT INTO employee VALUES(100, 'Jack', 'Watts', '1987-11-17', '0', 100, 'M');

select to_date(created_date), sum(amount) as amount
  from dw_prod_db.zuora.invoice
  where created_date > '2020-07-01'
  group by 1
  Order by 1;

select table_name, Column_name, ordinal_position, column_default, is_nullable, data_type, character_maximum_length, numeric_precision, numeric_precision_radix, numeric_scale
  from DW_PROD_DB.INFORMATION_SCHEMA.COLUMNS
  where table_schema = 'SALESFORCE'
  AND table_name = 'ACCOUNT'
  order by ordinal_position;