Posts

Snowflake Data Unloading into Json Format

Image
Pre-Requisites: Snowflake account details username password account name Step-1 : Create database and schema. Syntax: create or replace database DATA_UNLOADING; USE DATABASE DATA_UNLOADING; USE SCHEMA PUBLIC Step-2 : creating table and insert the records into it as shown below: Syntax: create or replace table EMPLOYEE(Empid number,empname string,region string,contact string); insert into employee values (1,'sai','EU','sai@123'),(2,'hiteesh','EU','hiteesh@123'),(3,'rishi','EU','rishi@123'),(4,'jaypal','EU','jaypal@123'),(5,'karthik','EU','karthik@123'), (6,'easwar','EU','easwar@123'),(7,'yasin','EU','yasin@123'),(8,'bhanu','EU','bhanu@123'),(9,'praneeth','EU','praneeth@123'),(10,'sagar','EU','sagar@123');   Step-3 : create a file format, which file f...
Post a Comment
Read more

Time Travel in Snowflake

Image
  Snowflake time travel : snowflake time travel enables accessing historical data (i.e. Data that has been changed or deleted) at any point within a defined period. ·         It serves as a powerful tool for performing the following table . ·         Restoring data- related objects (tables, schemas, and database)that might have been accidentally or intentionally deleted. ·        duplicate and backing up data from key points in the past. ·        Analysing data usage/manipulation over specified period of time. ·        Time Travel Retention period is (1 to 90 days) TIME TRAVEL ALLOWED : SELECT..AT|BEFORE….                                        ...
Post a Comment
Read more

Loading JSON data from Amazon S3 into Snowflake Using Copy Command

Image
    Pre-Requisites: AWS account snowflake account details aws_key_id and aws_secret_key firstly we need to connect snowflake account : syntax: snowsql -u accountname account :snowflake account password:which we created in snowflake Step 1:Firstly we need to create a database : create or replace database my_first_db; Step 2:Here we can create a target table : create or replace table jsontable( json_data varient);                                      NOTE:varient is a datatype Step 3:Here we can create a warehouse: create or replace  warehouse wh01 with warehouse_size='X-SMALL' auto_suspend =10 auto_resume = true initially_suspended=true; Step 4:Here we can create a file format type: create or replace file format jsonformat type='JSON' strip_outer_array='true' Here strip=true when we  can set true to instruct the JSON parser to remove the root brackets[],so that we...
Post a Comment
Read more

Tables and Views are available in Snowflake.

Image
  Snowflake : Snowflake is built on top of the Amazon Web Services, Microsoft Azure, and Google cloud infrastructure. There’s no hardware or software to select, install, configure, or manage, so it’s ideal for organizations that don’t want to dedicate resources for setup, maintenance, and support of in-house servers . But what sets Snowflake apart is its architecture and data sharing capabilities. The Snowflake architecture allows storage and compute to scale independently, so customers can use and pay for storage and computation separately. And the sharing functionality makes it easy for organizations to quickly share governed and secure data in real time There are 4 Types of Tables in Snowflake : 1.Permanent Table 2.Transient Table 3.Temporary Table 4.External Tables 1. Permanent Table : ·        Permanent table its stores the data permanently ·        Its Availability is until explicitly dropped ·  ...
Post a Comment
Read more