Creating stage
/ Database to manage stage objects, fileformats etc.
CREATE OR REPLACE DATABASE MANAGE_DB;
CREATE OR REPLACE SCHEMA external_stages;
// Creating external stage
CREATE OR REPLACE STAGE MANAGE_DB.external_stages.aws_stage
url='s3://bucketsnowflakes3'
credentials=(aws_key_id='ABCD_DUMMY_ID' aws_secret_key='1234abcd_key');
// Description of external stage
DESC STAGE MANAGE_DB.external_stages.aws_stage;
// Alter external stage
ALTER STAGE aws_stage
SET credentials=(aws_key_id='XYZ_DUMMY_ID' aws_secret_key='987xyz');
// Publicly accessible staging area
CREATE OR REPLACE STAGE MANAGE_DB.external_stages.aws_stage
url='s3://bucketsnowflakes3';
// List files in stage
LIST @aws_stage;
//Load data using copy command
COPY INTO OUR_FIRST_DB.[Link]
FROM @aws_stage
file_format= (type = csv field_delimiter=',' skip_header=1)
pattern='.*Order.*';
Steps
1 CREATE OR REPLACE DATABASE MANAGE_DB;
Execute this first create Database manage_db
2 CREATE OR REPLACE SCHEMA external_stages;
Next Create a schema in database called External_stages
3 Create AWS stage in external stage with URL,credentials,id and secret key
CREATE OR REPLACE STAGE MANAGE_DB.external_stages.aws_stage
url='s3://bucketsnowflakes3'
credentials=(AWS_key_id='ABCD_DUMMY_ID' AWS_secret_key='1234abcd_key');
4 Description of external stage
DESC STAGE MANAGE_DB.external_stages.aws_stage;
5 Alter stage and again describe external stage
ALTER STAGE aws_stage
SET credentials=(aws_key_id='XYZ_DUMMY_ID' aws_secret_key='987xyz');
6 LIst files in aws stage
LIST @aws_stage;
COPY command
// Creating ORDERS table
CREATE OR REPLACE TABLE OUR_FIRST_DB.[Link] (
ORDER_ID VARCHAR(30),
AMOUNT INT,
PROFIT INT,
QUANTITY INT,
CATEGORY VARCHAR(30),
SUBCATEGORY VARCHAR(30));
SELECT * FROM OUR_FIRST_DB.[Link];
// First copy command
COPY INTO OUR_FIRST_DB.[Link]
FROM @aws_stage
file_format = (type = csv field_delimiter=',' skip_header=1);
// Copy command with fully qualified stage object
COPY INTO OUR_FIRST_DB.[Link]
FROM @MANAGE_DB.external_stages.aws_stage
file_format= (type = csv field_delimiter=',' skip_header=1);
// List files contained in stage
LIST @MANAGE_DB.external_stages.aws_stage;
// Copy command with specified file(s)
COPY INTO OUR_FIRST_DB.[Link]
FROM @MANAGE_DB.external_stages.aws_stage
file_format= (type = csv field_delimiter=',' skip_header=1)
files = ('[Link]');
// Copy command with pattern for file names
COPY INTO OUR_FIRST_DB.[Link]
FROM @MANAGE_DB.external_stages.aws_stage
file_format= (type = csv field_delimiter=',' skip_header=1)
pattern='.*Order.*';
1- Create a table using create table and add all rows
2- Check where table is created in Our_first_db -> Public ->Tables ->Loan repayment
3- Select the table which is empty with
SELECT * FROM OUR_FIRST_DB.[Link];
You can see the empty table with no values
4 Now we should use copy command
5 It may show error because it mismatches column where there is 3 files
6 We should specify the file name or pattern
Ex; files = ('[Link]');
pattern='.*Order.*';
Transformation of data
// Transforming using the SELECT statement
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
FROM (select s.$1, s.$2 from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('[Link]');
// Example 1 - Table
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT
);
SELECT * FROM OUR_FIRST_DB.PUBLIC.ORDERS_EX;
// Example 2 - Table
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT,
PROFIT INT,
PROFITABLE_FLAG VARCHAR(30)
);
// Example 2 - Copy Command using a SQL function (subset of functions available)
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
FROM (select
s.$1,
s.$2,
s.$3,
CASE WHEN CAST(s.$3 as int) < 0 THEN 'not profitable' ELSE 'profitable' END
from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('[Link]');
SELECT * FROM OUR_FIRST_DB.PUBLIC.ORDERS_EX;
// Example 3 - Table
CREATE OR REPLACE TABLE OUR_FIRST_DB.PUBLIC.ORDERS_EX (
ORDER_ID VARCHAR(30),
AMOUNT INT,
PROFIT INT,
CATEGORY_SUBSTRING VARCHAR(5)
);
// Example 3 - Copy Command using a SQL function (subset of functions available)
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
FROM (select
s.$1,
s.$2,
s.$3,
substring(s.$5,1,5)
from @MANAGE_DB.external_stages.aws_stage s)
file_format= (type = csv field_delimiter=',' skip_header=1)
files=('[Link]');
SELECT * FROM OUR_FIRST_DB.PUBLIC.ORDERS_EX;