Snowflake Code Repository is a cloud-based repository provided by Snowflake, which allows users to manage and store SQL scripts and other code artifacts used for data integration, transformation, and analytics. It provides a centralized location for managing and sharing SQL scripts, views, and user-defined functions across different Snowflake accounts, regions, and organizations. With the Snowflake Code Repository, users can easily collaborate on code development, version control, and code deployment workflows. It also enables users to manage dependencies between different code artifacts, track changes and revisions, and automate code testing and deployment. The Snowflake Code Repository integrates seamlessly with Snowflake's cloud data platform and other Snowflake services, including Snowflake Data Marketplace, Snowflake Data Exchange, and Snowflake Partner Connect. It also provides access to various third-party tools and platforms, such as GitHub, Bitbucket, and Jenkins, to enhance the code management and deployment capabilities.
Getting Started >> Getting to Know the Interface
#### Create Database: 1. Create a database from the share. create database snowflake_sample_data from share sfc_samples.sample_data; 2. Grant the PUBLIC role access to the database. 3. Optionally change the role name to restrict access to a subset of users. grant imported privileges on database snowflake_sample_data to role public;
Loading Data In Snowflake:
//Rename data base & creating the table + meta data
ALTER DATABASE FIRST_DB RENAME TO OUR_FIRST_DB;
CREATE TABLE "OUR_FIRST_DB"."PUBLIC"."LOAN_PAYMENT" ( "Loan_ID" STRING, "loan_status" STRING, "Principal" STRING, "terms" STRING, "effective_date" STRING, "due_date" STRING, "paid_off_time" STRING, "past_due_days" STRING, "age" STRING, "education" STRING, "Gender" STRING);
//Check that table is empy
USE DATABASE OUR_FIRST_DB;
SELECT * FROM LOAN_PAYMENT;
//Loading the data from S3 bucket
COPY INTO LOAN_PAYMENT
FROM s3://bucketsnowflakes3/Loan_payments_data.csv
file_format = (type = csv field_delimiter = ',' skip_header=1);
//Validate
SELECT * FROM LOAN_PAYMENT;Loading Data >> Create 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.PUBLIC.ORDERS
FROM @aws_stage
file_format= (type = csv field_delimiter=',' skip_header=1)
pattern='.Order.';Transforming using the SELECT statement
// 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=('OrderDetails.csv');Error handling using the ON_ERROR option
// Error handling using the ON_ERROR option
COPY INTO OUR_FIRST_DB.PUBLIC.ORDERS_EX
FROM @MANAGE_DB.external_stages.aws_stage_errorex
file_format= (type = csv field_delimiter=',' skip_header=1)
files = ('OrderDetails_error.csv')
ON_ERROR = 'CONTINUE';