Wednesday, August 26, 2020

Oracle to Snowflake ETL

 Move data from Oracle to Snowflake Using Custom Code:

Steps to move data from Oracle to Snowflake can be categorised as follows:

1.     Extract data from Oracle to CSV using SQL*Plus 

2.     Creating Staging files

3.     Finally, copy staged files to the Snowflake table.

Step 1: Extract data from Oracle to CSV file:

Create DWH in Snow sql:

CREATE WAREHOUSE LOAD_WH WAREHOUSE_SIZE = "X-SMALL" AUTO_SUSPEND = 300 AUTO_RESUME = TRUE;

 

Create DB in Snow sql:

CREATE DATABASE DMU_DB;

Create Schema in Snow sql:

Create table structure in snowflake db:

2.     Creating Staging files:

 

create or replace stage my_oracle_stage copy_options= (on_error='skip_file') file_format  = (type = 'CSV' field_delimiter = ' ' skip_header = 1);

 

 

put file://D:\Sravani\emp.csv @my_oracle_stage auto_compress=true;

 

 

 

 

3.     Finally, copy staged files to the Snowflake table.

 

copy into emp from  @my_oracle_stage/emp.csv  file_format = (type = csv  skip_header = 1 ) pattern = '.*.csv.gz' on_error = 'skip_file';

 

 

No comments:

Post a Comment