COPY INTO command (Table) in Snowflake

Опубликовано: 11 Октябрь 2024
на канале: Snowflake Developer

5,468

#Snowflake #Stages #COPYINTO #ExternalStage

Below are the steps which we performed:
*******************************************

--Step #1 : Checking previous Stages
SHOW STAGES LIKE '%STAGE%' IN Database.Schema;

--Step #2 : Copying the External Stage name
LIST @EXTERNAL_STAGE_EMPLOYEE/load/FD_GROUP.txt;

--Step #3 : Checking data on FD Group File
SELECT $1 FROM @TRAINING.BI.EXTERNAL_STAGE_EMPLOYEE/load/FD_GROUP.txt;

--Flat File CREATE Script:
CREATE FILE FORMAT "TRAINING"."BI".CSV_FORMAT TYPE = 'CSV' COMPRESSION = 'AUTO' FIELD_DELIMITER = '^' RECORD_DELIMITER = '\n' SKIP_HEADER = 0 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');

--Step #4 : Creating Table
CREATE TABLE FD_GROUP (PRODUCT_ID VARCHAR(6),PRODUCT_NAMES VARCHAR(100));

--Step #5 : Checking table is created or not
SELECT * FROM FD_GROUP;

--Step #6 : Creating Copy COMMAND
COPY INTO FD_GROUP
FROM @TEXTERNAL_STAGE_EMPLOYEE/load/FD_GROUP.txt
FILE_FORMAT = (FORMAT_NAME='CSV_FORMAT');

--Step #7 : Checking data on table
SELECT * FROM FD_GROUP;

--Step #8 : Creting new table for Transformation data
CREATE TABLE FD_GROUP_TRANS (PRODUCT_ID VARCHAR(4),PRODUCT_NAMES VARCHAR(100));

--Step #9 : Checking table is created or not
SELECT * FROM FD_GROUP_TRANS;

--Step #10 : COPY COMMAND with Transformation
COPY INTO FD_GROUP_TRANS (PRODUCT_ID,PRODUCT_NAMES)
FROM (SELECT REPLACE($1,'~',''),REPLACE($2,'~','') FROM @EXTERNAL_STAGE_EMPLOYEE/load/FD_GROUP.txt)
FILE_FORMAT = (FORMAT_NAME='CSV_FORMAT')
FORCE=TRUE;

--Step #11 : Checking data on table with transformation
SELECT * FROM FD_GROUP;

How to create External Stages in Snowflake: • Create External Stage in Snowflake

For more Information regarding COPY INTO COMMAND: https://docs.snowflake.com/en/sql-ref...