/* Snowflake <> DataRobot Virtual Hands-on Lab */ /* 2 - Set role */ USE ROLE accountadmin; /* 3 - Create Database */ CREATE OR REPLACE DATABASE customer_data; /* 3 - Create Warehouse */ CREATE OR REPLACE WAREHOUSE compute_wh WITH WAREHOUSE_SIZE = 'XSMALL'; /* 3 - Set context */ USE DATABASE customer_data; USE SCHEMA public; USE WAREHOUSE compute_wh; /* 4 - Create table for data loading */ CREATE OR REPLACE TABLE train_data ( "CUST_ID" NUMBER(38,0), "CHURN" BOOLEAN, "STATE" VARCHAR(16777216), "ACCOUNT_LENGTH" NUMBER(38,0), "AREA_CODE" NUMBER(38,0), "INTERNATIONAL" VARCHAR(16777216), "VOICEMAIL_PLAN" VARCHAR(16777216), "NUM_VM_MESSAGES" NUMBER(38,0), "TOTAL_DAY_MINS" FLOAT, "TOTAL_DAY_CALLS" NUMBER(38,0), "TOTAL_DAY_CHARGE" FLOAT, "TOTAL_EVE_MINS" FLOAT, "TOTAL_EVE_CALLS" NUMBER(38,0), "TOTAL_EVE_CHARGE" FLOAT, "TOTAL_NIGHT_MINS" FLOAT, "TOTAL_NIGHT_CALLS" NUMBER(38,0), "TOTAL_NIGHT_CHARGE" FLOAT, "TOTAL_INT_MINS" FLOAT, "TOTAL_INT_CALLS" NUMBER(38,0), "TOTAL_INT_CHARGE" FLOAT, "CUSTOMER_SERVICE_CALLS" NUMBER(38,0), "TARRIF_PLAN_CONDS" VARCHAR(16777216) ); /* 4 - Create table for data loading */ CREATE OR REPLACE TABLE SCORING_DATA ( "CUST_ID" NUMBER(38,0), "CHURN" BOOLEAN, "STATE" VARCHAR(16777216), "ACCOUNT_LENGTH" NUMBER(38,0), "AREA_CODE" NUMBER(38,0), "INTERNATIONAL" VARCHAR(16777216), "VOICEMAIL_PLAN" VARCHAR(16777216), "NUM_VM_MESSAGES" NUMBER(38,0), "TOTAL_DAY_MINS" FLOAT, "TOTAL_DAY_CALLS" NUMBER(38,0), "TOTAL_DAY_CHARGE" FLOAT, "TOTAL_EVE_MINS" FLOAT, "TOTAL_EVE_CALLS" NUMBER(38,0), "TOTAL_EVE_CHARGE" FLOAT, "TOTAL_NIGHT_MINS" FLOAT, "TOTAL_NIGHT_CALLS" NUMBER(38,0), "TOTAL_NIGHT_CHARGE" FLOAT, "TOTAL_INT_MINS" FLOAT, "TOTAL_INT_CALLS" NUMBER(38,0), "TOTAL_INT_CHARGE" FLOAT, "CUSTOMER_SERVICE_CALLS" NUMBER(38,0), "TARRIF_PLAN_CONDS" VARCHAR(16777216), "MOSTUSAGE" VARCHAR(16777216) ); /* 5 - Create Stage Directory */ CREATE OR REPLACE STAGE sf_dr_stage URL = 's3://snowflake-workshop-lab/telecoms'; /* 5 - Show Directory */ list @sf_dr_stage; /* 6 - Create File Format to load csv file */ CREATE OR REPLACE FILE FORMAT churndata_ff 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'); /* 7 - Loading data into snowflake */ ALTER WAREHOUSE compute_wh SET WAREHOUSE_SIZE = "SMALL"; /* 7 - COPY DATA INTO TABLES */ COPY INTO train_data FROM @sf_dr_stage/Churn_Telecomms_training.csv FILE_FORMAT = (FORMAT_NAME = churndata_ff); COPY INTO scoring_data FROM @sf_dr_stage/Churn_Telecomms_scoring.csv FILE_FORMAT = (FORMAT_NAME = churndata_ff); /* 7 - View loaded data */ SELECT * FROM train_data LIMIT 10;