Loading JSON data from Amazon S3 into Snowflake Using Copy Command

   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  can use strip 

                                    

Step 5:Here we can create a stage:

create or replace stage json_stage

file_format=jsonformat

url='s3://snowflakdoc';

here:url..we uplod the JSON file into S3bucket file name

step 6:Alter  the warehouse because when we create the warehouse auto_suspended=10 ,we need to resume the warehouse ,so that we can use alter command

alter warehouse wh01 resume

Step 7:Here copy the JSON file into target table:

copy into myjsontable

from s3://snowflakdoc/sample2.json

credentials=(aws_key_id='AKIAYMFP4KTSRP4UD7UE' aws_secret_key='elnkoDR6QRb+pXnSnzJrbyyf/yhYgU1hlnAgJ4rE')   file_format = (type = JSON);

Step 8:Here is the output for JSON data into snowflake target table as shown below.

select * from jsontable;

Conclusion: By Performing above Steps/Queries we can load the Json data from S3 to Snowflake.