0
0
During this post we will discuss about COPY and UNLOAD the data into snowflake via Javascript procedure. As per the requirement, Process should create the Stage in Database based on the input pass to the procedure. Once the stage is created, process should load the data from the stage into Table automatically. Moreover once the Data is loaded into the table, we need to unload his data into JSON format in another bucket. This data would be consumed by another team having access to this Archive bucket. To unload the data it is require a stage to be created which maps to the Archive bucket. So as part of this process, Unload stage should be created as well automatically. Later on, Process would execute the unload command to load the table data into JSON format at Archive bucket.
So in short, Stage creation for File Load and Unload should create automatically based on the Procedure input. Execute the COPY command to load data into table. Finally Unload the data from table to Staging area.
Procedure should present the output in below way:
Stage Created Successfully
Stage to unload the data Created Successfully
Copy command success
Number of Rows uploaded(52)
Note: we can create the FILE FORMAT as well through the Procedure as well. However for this use case we are assuming File format is already available in the Database.
Technical Implementation:
Prerequisite:
create or replace table TEST (
CUST_NUM varchar(8) null,
CUST_STAT varchar(255) null,
CUST_BAL number(20) null,
INV_NO varchar(10) null,
INV_AMT number(10) null,
CRID VARCHAR(4) not null,
SSN VARCHAR(10),
phone number(10),
Email VARCHAR(50)
);
CREATE or REPLACE FILE FORMAT JSON
TYPE = 'JSON'
COMPRESSION = 'AUTO'
STRIP_OUTER_ARRAY = FALSE
STRIP_NULL_VALUES = FALSE;
create or replace file format demo_db.public.csv_format
type = csv
skip_header = 1
null_if = ('NULL', 'null')
empty_field_as_null = true;
Execute the Procedure:
call stage_copy_unload('TEST');
Stage Created at AWS:
Proc output
