Snowflake Documentation:
Roles in Snowflake:
1.AccountAdmin
2.OrgAdmin
3.Public
4.Securityadmin
5.Sysadmin
6.Useradmin
Snowflake Worksheet Page:
Creating Warehouse:
CREATE WAREHOUSE INTL_WH
WITH WAREHOUSE_SIZE = 'XSMALL'
WAREHOUSE_TYPE = 'STANDARD'
AUTO_SUSPEND = 600
AUTO_RESUME = TRUE;
Creating Database:
USE ROLE SYSADMIN;
CREATE DATABASE INTL_DB;
Or
Creating Tables:
USE SCHEMA INTL_DB.PUBLIC;
CREATE TABLE INTL_DB.PUBLIC.CURRENCIES
(
CURRENCY_ID INTEGER,
CURRENCY_CHAR_CODE varchar(3),
CURRENCY_SYMBOL varchar(4),
CURRENCY_DIGITAL_CODE varchar(3),
CURRENCY_DIGITAL_NAME varchar(30)
)
COMMENT = 'Information about currencies including character codes, symbols, digital
codes, etc.';
CREATE TABLE INTL_DB.PUBLIC.COUNTRY_CODE_TO_CURRENCY_CODE
(
COUNTRY_CHAR_CODE Varchar(3),
COUNTRY_NUMERIC_CODE INTEGER,
COUNTRY_NAME Varchar(100),
CURRENCY_NAME Varchar(100),
CURRENCY_CHAR_CODE Varchar(3),
CURRENCY_NUMERIC_CODE INTEGER
)
COMMENT = 'Many to many code lookup table';
File Format to Load the Data into Snowflake table:
CREATE FILE FORMAT INTL_DB.PUBLIC.CSV_COMMA_LF_HEADER
TYPE = 'CSV'
COMPRESSION = 'AUTO'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = 'NONE'
TRIM_SPACE = FALSE
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
ESCAPE = 'NONE'
ESCAPE_UNENCLOSED_FIELD = '\134'
DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
NULL_IF = ('\\N');
Loading Snowflake Tables:
Using Wizard:
Step 1: Go the Table, Right click the three dots at the corner of the table and choose load
Data
Step 2: Browse the file
Step 3: Choose the file Format:
Also you can load data through table using Copy into Statement:
COPY INTO "INTL_DB"."PUBLIC"."COUNTRY_CODE_TO_CURRENCY_CODE"
FROM
'@"INTL_DB"."PUBLIC"."%COUNTRY_CODE_TO_CURRENCY_CODE"/__snowflake_temp_imp
ort_files__/'
FILES = ('country_code_to_currency_code.xls')
FILE_FORMAT = (
TYPE=CSV,
SKIP_HEADER=1,
FIELD_DELIMITER=',',
TRIM_SPACE=FALSE,
FIELD_OPTIONALLY_ENCLOSED_BY='"',
REPLACE_INVALID_CHARACTERS=TRUE,
DATE_FORMAT=AUTO,
TIME_FORMAT=AUTO,
TIMESTAMP_FORMAT=AUTO
)
ON_ERROR=ABORT_STATEMENT
PURGE=TRUE
Creating a View :
CREATE VIEW INTL_DB.PUBLIC.NATIONS_SAMPLE_PLUS_ISO
( iso_country_name
,country_name_official
,alpha_code_2digit
,region) AS
SELECT
iso_country_name
, country_name_official,alpha_code_2digit
,r_name as region
FROM INTL_DB.PUBLIC.INT_STDS_ORG_3661 i
LEFT JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION n
ON UPPER(i.iso_country_name)=n.n_name
LEFT JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION r
ON n_regionkey = r_regionkey;
Creating Objects:
--Caden set up a new database (and you will, too)
create database ACME;
use database ACME;
--get rid of the public schema - too generic
drop schema ACME.PUBLIC;
--When creating shares it is best to have multiple schemas
create schema ACME.SALES;
create schema ACME.STOCK;
create schema ACME.ADU; --this is the schema they'll use to share to ADU, Max's company
Creating a Table :
create or replace table ACME.STOCK.LOTSTOCK
(
VIN VARCHAR(17)
,EXTERIOR VARCHAR(50)
,INTERIOR VARCHAR(50)
,DATE_SOLD DATE
,CUSTOMER_ID NUMBER(20)
);
Creating a Secure View:
To Create a secure View you need to add Secure keyword to it
create or replace secure view ACME.ADU.LOTSTOCK
AS (
SELECT VIN
, LEFT(VIN,3) as WMI
, SUBSTR(VIN,4,5) as VDS
, SUBSTR(VIN,10,1) as MODYEARCODE
, SUBSTR(VIN,11,1) as PLANTCODE
, EXTERIOR
, INTERIOR
FROM ACME.STOCK.LOTSTOCK
WHERE DATE_SOLD is NULL
);
--You need a file format if you want to load the table
create file format ACME.STOCK.COMMA_SEP_HEADERROW
TYPE = 'CSV'
COMPRESSION = 'AUTO'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '\042'
TRIM_SPACE = TRUE
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
ESCAPE = 'NONE'
ESCAPE_UNENCLOSED_FIELD = '\134'
DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
NULL_IF = ('\\N');
Loading data from Stage Using Copy Command:
COPY INTO acme.stock.lotstock
from @util_db.public.like_a_window_into_an_s3_bucket
files = ('smew/Lotties_LotStock_Data.csv')
file_format =(format_name=ACME.STOCK.COMMA_SEP_HEADERROW);
Creating Stage:
create stage util_db.public.like_a_window_into_an_s3_bucket
url = 's3://uni-lab-files';
Creating few more objects to load data :
USE ROLE SYSADMIN;
--Max created a database to store Vehicle Identification Numbers
CREATE DATABASE max_vin;
DROP SCHEMA max_vin.public;
CREATE SCHEMA max_vin.decode;
CREATE TABLE MAX_VIN.DECODE.WMITOMANUF
(
WMI VARCHAR(6)
,MANUF_ID NUMBER(6)
,MANUF_NAME VARCHAR(50)
,COUNTRY VARCHAR(50)
,VEHICLETYPE VARCHAR(50)
);
CREATE TABLE MAX_VIN.DECODE.MANUFTOMAKE
(
MANUF_ID NUMBER(6)
,MAKE_NAME VARCHAR(50)
,MAKE_ID NUMBER(5)
);
CREATE TABLE MAX_VIN.DECODE.MODELYEAR
(
MODYEARCODE VARCHAR(1)
,MODYEARNAME NUMBER(4)
);
CREATE TABLE MAX_VIN.DECODE.MANUFPLANTS
(
MAKE_ID NUMBER(5)
,PLANTCODE VARCHAR(1)
,PLANTNAME VARCHAR(75)
);
CREATE TABLE MAX_VIN.DECODE.MMVDS
(
MAKE_ID NUMBER(3)
,MODEL_ID NUMBER(6)
,MODEL_NAME VARCHAR(50)
,VDS VARCHAR(5)
,DESC1 VARCHAR(25)
,DESC2 VARCHAR(25)
,DESC3 VARCHAR(50)
,DESC4 VARCHAR(25)
,DESC5 VARCHAR(25)
,BODYSTYLE VARCHAR(25)
,ENGINE VARCHAR(100)
,DRIVETYPE VARCHAR(50)
,TRANS VARCHAR(50)
,MPG VARCHAR(25)
);
CREATE FILE FORMAT MAX_VIN.DECODE.COMMA_SEP_HEADERROW
TYPE = 'CSV'
COMPRESSION = 'AUTO'
FIELD_DELIMITER = ','
RECORD_DELIMITER = '\n'
SKIP_HEADER = 1
FIELD_OPTIONALLY_ENCLOSED_BY = '\042'
TRIM_SPACE = TRUE
ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE
ESCAPE = 'NONE'
ESCAPE_UNENCLOSED_FIELD = '\134'
DATE_FORMAT = 'AUTO'
TIMESTAMP_FORMAT = 'AUTO'
NULL_IF = ('\\N');
COPY INTO MAX_VIN.DECODE.WMITOMANUF
from @util_db.public.like_a_window_into_an_s3_bucket
files = ('smew/Maxs_WMIToManuf_data.csv')
file_format =(format_name=MAX_VIN.DECODE.COMMA_SEP_HEADERROW);
COPY INTO MAX_VIN.DECODE.MANUFTOMAKE
from @util_db.public.like_a_window_into_an_s3_bucket
files = ('smew/Maxs_ManufToMake_Data.csv')
file_format =(format_name=MAX_VIN.DECODE.COMMA_SEP_HEADERROW);
COPY INTO MAX_VIN.DECODE.MODELYEAR
from @util_db.public.like_a_window_into_an_s3_bucket
files = ('smew/Maxs_ModelYear_Data.csv')
file_format =(format_name=MAX_VIN.DECODE.COMMA_SEP_HEADERROW);
COPY INTO MAX_VIN.DECODE.MANUFPLANTS
from @util_db.public.like_a_window_into_an_s3_bucket
files = ('smew/Maxs_ManufPlants_Data.csv')
file_format =(format_name=MAX_VIN.DECODE.COMMA_SEP_HEADERROW);
COPY INTO MAX_VIN.DECODE.MMVDS
from @util_db.public.like_a_window_into_an_s3_bucket
files = ('smew/Maxs_MMVDS_Data.csv')
file_format =(format_name=MAX_VIN.DECODE.COMMA_SEP_HEADERROW);
Sample Queries using above tables:
--Max has Lottie's VINventory table. Now he'll join his decode tables to the data
-- He'll create a select statement that ties each table into Lottie's VINS
-- Every time he adds a new table, he'll make sure he still has 298 rows
SELECT *
FROM ACME_DETROIT.ADU.LOTSTOCK l-- he uses Lottie's data from the INBOUND SHARE
JOIN MAX_VIN.DECODE.MODELYEAR y -- and confirms he can join it with his own decode
data
ON l.modyearcode=y.modyearcode;
SELECT *
FROM ACME_DETROIT.ADU.LOTSTOCK l -- he uses Lottie's data from the INBOUND SHARE
JOIN MAX_VIN.DECODE.WMITOMANUF w -- and confirms he can join it with his own
decode data
ON l.WMI=w.WMI;
--Add the next table (still 298?)
SELECT *
FROM ACME_DETROIT.ADU.LOTSTOCK l -- he uses Lottie's data from the INBOUND SHARE
JOIN MAX_VIN.DECODE.WMITOMANUF w -- and confirms he can join it with his own decode
data
ON l.WMI=w.WMI
JOIN MAX_VIN.DECODE.MANUFTOMAKE m
ON w.manuf_id=m.manuf_id;
--Until finally he has all 5 lookup tables added
--He can then remove the asterisk and start narrowing down the
--fields to include in the final output
SELECT
l.VIN
,y.MODYEARNAME
,m.MAKE_NAME
,v.DESC1
,v.DESC2
,v.DESC3
,BODYSTYLE
,ENGINE
,DRIVETYPE
,TRANS
,MPG
,MANUF_NAME
,COUNTRY
,VEHICLETYPE
,PLANTNAME
FROM ACME_DETROIT.ADU.LOTSTOCK l -- he joins Lottie's data from the INBOUND SHARE
JOIN MAX_VIN.DECODE.WMITOMANUF w -- with all his data (he just tested)
ON l.WMI=w.WMI
JOIN MAX_VIN.DECODE.MANUFTOMAKE m
ON w.manuf_id=m.manuf_id
JOIN MAX_VIN.DECODE.MANUFPLANTS p
ON l.plantcode=p.plantcode
AND m.make_id=p.make_id
JOIN MAX_VIN.DECODE.MMVDS v
ON v.vds=l.vds
and v.make_id = m.make_id
JOIN MAX_VIN.DECODE.MODELYEAR y
ON l.modyearcode=y.modyearcode;
USE ROLE SYSADMIN;
CREATE DATABASE MAX_OUTGOING; --this new database will be used for his OUTBOUND
SHARE
CREATE SCHEMA MAX_OUTGOING.FOR_ACME; --this schema he creates especially for
ACME
-- This is a live view of the data Lottie and Caden Need!
CREATE OR REPLACE SECURE VIEW MAX_OUTGOING.FOR_ACME.LOTSTOCKENHANCED as
(
SELECT
l.VIN
,y.MODYEARNAME
,m.MAKE_NAME
,v.DESC1
,v.DESC2
,v.DESC3
,BODYSTYLE
,ENGINE
,DRIVETYPE
,TRANS
,MPG
,EXTERIOR
,INTERIOR
,MANUF_NAME
,COUNTRY
,VEHICLETYPE
,PLANTNAME
FROM ACME_DETROIT.ADU.LOTSTOCK l
JOIN MAX_VIN.DECODE.WMITOMANUF w
ON l.WMI=w.WMI
JOIN MAX_VIN.DECODE.MANUFTOMAKE m
ON w.manuf_id=m.manuf_id
JOIN MAX_VIN.DECODE.MANUFPLANTS p
ON l.plantcode=p.plantcode
AND m.make_id=p.make_id
JOIN MAX_VIN.DECODE.MMVDS v
ON v.vds=l.vds and v.make_id = m.make_id
JOIN MAX_VIN.DECODE.MODELYEAR y
ON l.modyearcode=y.modyearcode
);
CREATE OR REPLACE TABLE MAX_OUTGOING.FOR_ACME.LOTSTOCKRETURN
(
VIN VARCHAR(17)
,MODYEARNAME NUMBER(4)
,MAKE_NAME VARCHAR(50)
,DESC1 VARCHAR(50)
,DESC2 VARCHAR(50)
,DESC3 VARCHAR(50)
,BODYSTYLE VARCHAR(25)
,ENGINE VARCHAR(100)
,DRIVETYPE VARCHAR(50)
,TRANS VARCHAR(50)
,MPG VARCHAR(25)
,EXTERIOR VARCHAR(50)
,INTERIOR VARCHAR(50)
,MANUF_NAME VARCHAR(50)
,COUNTRY VARCHAR(50)
,VEHICLETYPE VARCHAR(50)
,PLANTNAME VARCHAR(75)
);
USE ROLE SYSADMIN;
create or replace procedure lotstockupdate_sp()
returns string not null
language javascript
as
$$
var my_sql_command1 = "truncate table max_outgoing.for_acme.lotstockreturn;";
var statement1 = snowflake.createStatement( {sqlText: my_sql_command1} );
var result_set1 = statement1.execute();
var my_sql_command2 ="insert into max_outgoing.for_acme.lotstockreturn ";
my_sql_command2 += "select VIN, MODYEARNAME, MAKE_NAME, DESC1, DESC2, DESC3,
BODYSTYLE";
my_sql_command2 += ",ENGINE, DRIVETYPE, TRANS, MPG, EXTERIOR, INTERIOR,
MANUF_NAME, COUNTRY, VEHICLETYPE, PLANTNAME";
my_sql_command2 += " from max_outgoing.for_acme.lotstockenhanced;";
var statement2 = snowflake.createStatement( {sqlText: my_sql_command2} );
var result_set2 = statement2.execute();
return my_sql_command2;
$$;
--View your Stored Procedure
show procedures in account;
desc procedure lotstockupdate_sp();
-- Create a task that calls the stored procedure every hour
-- so that Lottie sees updates at least every hour
USE ROLE ACCOUNTADMIN;
grant execute task on account to role sysadmin;
USE ROLE SYSADMIN;
create or replace task acme_return_update
warehouse = COMPUTE_WH
schedule = '1 minute'
as
call lotstockupdate_sp();
--if you need to see who owns the task
show grants on task acme_return_update;
--Look at the task you just created to make sure it turned out okay
show tasks;
desc task acme_return_update;
--if you task has a state of "suspended" run this to get it going
alter task acme_return_update resume;
--Check back 5 mins later to make sure your task has been running
--You will not be able to see your task on the Query History Tab
select *
from table(information_schema.task_history())
order by scheduled_time;
show tasks in account;
desc task acme_return_update;
alter task acme_return_update suspend;
--Check back 5 mins later to make sure your task is NOT running
desc task acme_return_update;
select *
from MAX_OUTGOING.FOR_ACME.LOTSTOCKRETURN
select * from max_outgoing.for_acme.lotstockenhanced
Inbound Shares:
GRANT IMPORTED PRIVILEGES
ON DATABASE SNOWFLAKE_SAMPLE_DATA
TO ROLE SYSADMIN;
--Check the range of values in the Market Segment Column
SELECT DISTINCT c_mktsegment
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
--Find out which Market Segments have the most customers
SELECT c_mktsegment, COUNT(*)
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
GROUP BY c_mktsegment
ORDER BY COUNT(*);
-- Nations Table
SELECT N_NATIONKEY, N_NAME, N_REGIONKEY
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION;
-- Regions Table
SELECT R_REGIONKEY, R_NAME
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION;
-- Join the Tables and Sort
SELECT R_NAME as Region, N_NAME as Nation
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION
JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION
ON N_REGIONKEY = R_REGIONKEY
ORDER BY R_NAME, N_NAME ASC;
--Group and Count Rows Per Region
SELECT R_NAME as Region, count(N_NAME) as NUM_COUNTRIES
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.NATION
JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.REGION
ON N_REGIONKEY = R_REGIONKEY
GROUP BY R_NAME;
