Snowflake - Unloading Data - Working Session

Veröffentlicht am: 24 Dezember 2022
auf dem Kanal: Janardhan Reddy Bandi

12,929

128

You can get all snowflake Videos, PPTs, Queries, Interview questions and Practice files in my Udemy course for very less price.. I will be updating this content and will be uploading all new videos in this course.

My Snowflake Udemy Course:
https://www.udemy.com/course/snowflak...

I can be reachable on jana.snowflake2@gmail.com.

==================================================
Unloading data to external cloud storage locations
===================================================
// Create required Database and Schemas
CREATE DATABASE IF NOT EXISTS MYDB;
CREATE SCHEMA IF NOT EXISTS MYDB.EXT_STAGES;
CREATE SCHEMA IF NOT EXISTS MYDB.FILE_FORMATS;

----------------------------
// Add new aws s3 location to our storage int object to store output files

CREATE OR REPLACE STORAGE INTEGRATION S3_INT
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::555064756008:role/snowflake_access_role2'
STORAGE_ALLOWED_LOCATIONS = ('s3://awss3bucketjana/csv/', 's3://awss3bucketjana/json/','s3://awss3bucketjana/pipes/csv/', 's3://awss3bucketjana/output/')
COMMENT = 'Integration with aws s3 buckets' ;

OR

ALTER STORAGE INTEGRATION S3_INT
SET STORAGE_ALLOWED_LOCATIONS = ('s3://awss3bucketjana/csv/', 's3://awss3bucketjana/json/','s3://awss3bucketjana/pipes/csv/', 's3://awss3bucketjana/output/');

// Create file format object
CREATE OR REPLACE FILE FORMAT MYDB.FILE_FORMATS.CSV_FILEFORMAT
type = csv
field_delimiter = '|'
skip_header = 1
empty_field_as_null = TRUE;

// Create stage object with integration object & file format object
// Using the Storeage Integration object that was already created

CREATE OR REPLACE STAGE MYDB.EXT_STAGES.MYS3_OUTPUT
URL = 's3://awss3bucketjana/output/'
STORAGE_INTEGRATION = s3_int
FILE_FORMAT = MYDB.FILE_FORMATS.CSV_FILEFORMAT ;

// Generate files and store them in the stage location
COPY INTO @MYDB.EXT_STAGES.MYS3_OUTPUT
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

//Listing files under my s3 bucket
LIST @MYDB.EXT_STAGES.MYS3_OUTPUT;

===================
Unloading Options
===================
OVERWRITE = TRUE | FALSE - Specifies to Overwrite existing files
SINGLE = TRUE | FALSE - Specifies whether to generate a single file or multiple files
MAX_FILE_SIZE = NUMBER - Maximum file size
INCLUDE_QUERY_ID = TRUE | FALSE - Specifies whether to uniquely identify unloaded files by including a universally unique identifier
DETAILED_OUTPUT = TRUE | FALSE - Shows the path and name for each file, its size, and the number of rows that were unloaded to the file.;

// We can mentione file name like 'customer', maximum files size

// Specifiy the filename in the copy command
COPY INTO @MYDB.EXT_STAGES.MYS3_OUTPUT/customer
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;

// MAX_FILE_SIZE
COPY INTO @MYDB.EXT_STAGES.MYS3_OUTPUT/customer
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
MAX_FILE_SIZE=2000000;

// Use OVERWRTIE=TRUE
// If we want to overwrite existing file we can set that to TRUE
COPY INTO @MYDB.EXT_STAGES.MYS3_OUTPUT/customer
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
MAX_FILE_SIZE=2000000
OVERWRITE = TRUE;

//Listing files under my s3 bucket
LIST @MYDB.EXT_STAGES.MYS3_OUTPUT;

//generate single file
COPY INTO @MYDB.EXT_STAGES.MYS3_OUTPUT/CUST
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
SINGLE = TRUE;

//detailed output
COPY INTO @MYDB.EXT_STAGES.MYS3_OUTPUT/cust_data
FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
DETAILED_OUTPUT = TRUE;

Auf dieser Seite können Sie das Online-Video Snowflake - Unloading Data - Working Session mit der Dauer stunde minuten sekunde in guter Qualität ansehen, das der Benutzer Janardhan Reddy Bandi 24 Dezember 2022 hochgeladen hat, den Link mit Freunden und Bekannten teilen, dieses Video wurde auf Youtube bereits 12,929 Mal angesehen und es wurde von 128 den Zuschauern gefallen. Viel Spaß beim Betrachtenden Zuschauern gefallen!

149