CREATE DATABASE SOURCE_DB;
USE DATABASE SOURCE_DB;
CREATE SCHEMA SOURCE_SCM;
USE SCHEMA SOURCE_SCM;
CREATE OR REPLACE STORAGE INTEGRATION MY_S3_INTEGRATION
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'ARN:AWS:IAM::851725461099:ROLE/ASWSNOWPOLICY'
STORAGE_ALLOWED_LOCATIONS = ('S3://EXPORTSALES1920/CSV/');
DESC INTEGRATION MY_S3_INTEGRATION;
// CREATE FILE FORMAT OBJECT
CREATE OR REPLACE FILE FORMAT CSV_FILEFORMAT
TYPE = CSV
FIELD_DELIMITER = '|'
SKIP_HEADER = 1
EMPTY_FIELD_AS_NULL = TRUE;
CREATE OR REPLACE STAGE CUSTOMER_STAGE
URL = 'S3://EXPORTSALES1920/CSV/'
STORAGE_INTEGRATION = MY_S3_INTEGRATION
FILE_FORMAT = CSV_FILEFORMAT;
//LISTING FILES UNDER YOUR S3 BUCKETS
LIST @CUSTOMER_STAGE;
SELECT CURRENT_DATABASE();
SELECT CURRENT_SCHEMA();
CREATE OR REPLACE TABLE CUSTOMER_DATA (
CUSTOMERID NUMBER,
CUSTNAME STRING,
EMAIL STRING,
CITY STRING,
STATE STRING,
DOB DATE
);
// USE COPY COMMAND TO LOAD THE FILES
COPY INTO CUSTOMER_DATA
FROM @CUSTOMER_STAGE
PATTERN = '.*CUSTOMER.*';
SELECT * FROM CUSTOMER_DATA;
DELETE FROM CUSTOMER_DATA WHERE CUSTNAME LIKE 'C%';
SELECT * FROM CUSTOMER_DATA;
DELETE FROM CUSTOMER_DATA WHERE CUSTNAME LIKE 'C%';
SELECT * FROM CUSTOMER_DATA_NEW AT (OFFSET => -60);
CREATE OR REPLACE TABLE CUSTOMER_DATA_NEW AS (
SELECT * FROM CUSTOMER_DATA AT (OFFSET =>-60*15));
CREATE DATABASE TGT;
USE DATABASE TGT;
SELECT CURRENT_SCHEMA();
CREATE SCHEMA TGT_SCM;
CREATE TABLE CUSTOMER_DATA_TGT AS (
SELECT CUSTOMER_DATA.*,(DATEDIFF(YEAR,CUSTOMER_DATA.DOB,CURRENT_DATE())) AGE,
(DATEDIFF(YEAR,CUSTOMER_DATA.DOB,CURRENT_DATE()))*1000 SALARY FROM
SOURCE_DB.SOURCE_SCM.CUSTOMER_DATA);
SELECT * FROM CUSTOMER_DATA_TGT;
arn:aws:iam::014498633229:user/x2pp0000-s
ARN:AWS:IAM::851725461099:ROLE/ASWSNOWPOLICY
https://yz66934.ap-southeast-1.snowflakecomputing.com
