Snowflake - Unloading Data - Working Session скачать в хорошем качестве

Snowflake - Unloading Data - Working Session

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;