Skip to main content

Snowflake

Loading JSON Data into Snowflake From Local Directory

Screenshot 2023 05 22 181123

In today’s world, there are various data formats such as traditional, unstructured, raw format and semi structured data and load into target system. Semi structured data is often used in web applications, IOT and API sensors. Customers use JSON format mostly to get the data because it is easy to understand.
Snowflake
Snowflake is a cloud data warehouse that helps to load different data types and format of data. It can automatically scale up/down its computed resources to load integrate data. Snowflakes do not utilize any physical or hardware. Snowflake runs public cloud infrastructure.
JSON
JSON is a text-based format that uses key value pair, and it is easily accessible. The syntax is designed to be easy for humans to read and write.

Why do we use JSON?
1.Simplicity and Readability:
JSON syntax is quite simple. It is easy to read as it represents key value
2.Lightweight:
It is a text-based format, larger than other formats like XML which can lead to faster data transfer and storage.
3.Configuration Files:
Mostly JSON use in config also and it is easily readable format
4. Data Exchange:
JSON supports data exchange between different software and systems.
5. JSON Data Type:
Snowflake use variant datatype to load JSON data as it is semi structured data

In this blog, we will use two methods to load JSON data (sales.json) into Snowflake from local directory
And JSON would like to be:

{"location":{"state_city":"MA-Lexington","zip":"40503"},"sale_date":"2017-3-5","price":"275836"}<br> {"location":{"state_city":"MA-Belmont","zip":"02478"},"sale_date":"2017-3-17","price":"392567"}<br> {"location":{"state_city":"MA-Winchester","zip":"01890"},"sale_date":"2017-3-21","price":"389921"}<br>
{"location": {"state_city": "MA-Lexington","zip": "40503"},"sale_date": "2017-3-5","price": "275836"}
{"location": {"state_city": "MA-Belmont","zip": "02478"},"sale_date": "2017-3-17","price": "392567"}
{"location": {"state_city": "MA-Winchester","zip": "01890"},"sale_date": "2017-3-21","price": "389921"}

  1. Direct load JSON data in Snowflake (No Stage involved):
    In Snowflake, we need to create data warehouse, schema and database  to compute the data  as below:
    Picjson1

And  Snowflake provides facility to load the file directly into table as  it is available in local, follow below steps:
Picturejson2
Click on Upload local files
Picturejson3
Browse the file which you want to load into the table
Picturejson4

Picturejson5

Click on next
Picturejson6

Then load the data into table

Picturejson8
Step 2:
Load JSON data through Internal Stage:

Internal Stage:
Internal stage in Snowflake helps to load the data internally. It does not require any cloud integration outside.

Below are the steps to load the JSON file in internal stage:

  1. Create file format as below:
    Fileformat

  2. Create Stage as below
    Stage
    3.Put file into Stage
    To execute the PUT command in Snow SQL
    Please go through link to install Snow SQL (SnowSQL)
    Add file into stage via PUT command as below:
    PUT syntax as below
    PUT file://path of file @Stage_Name
    PUT file://C:\temp\load\sales.Json @  JSON_DATA_STAGE;
    Once execute the PUT command in Snowflake:
    Put
    To check file load into stage through below query:
    list @LOAD_JSON_DATABASE.LOAD_JSON_SCHEMA.JSON_DATA_STAGE;
    Logs
    Copy data into the target table:

     Directly load into table:

Table definition as below:

create or replace TABLE LOAD_JSON_DATABASE.LOAD_JSON_SCHEMA.JSON_DATA (VARIANT_COL                VARIANT);
Loadtable

Create table with column names in Snow sight:
create or replace TABLE LOAD_JSON_DATABASE.LOAD_JSON_SCHEMA.JSON_DATA_SALES (
city varchar, state varchar,zip varchar, sale_date date,price int);
Copy data into table:
Tabled
Note:

$1 use to select single column where the JSON stored

How to retrieve Json data through SELECT query:
1. Dot and colon Notation:
As it is JSON in JSON, so it shows result as below:
Json
If want to retrieve data as a single column, use below query
Singlec
In the above image, get values as in string format but if we want retrieve in actual data type. Use explicitly casting as below query as:
Actuald
2.FLATTEN IN JSON:
  Flattening is the process to divide the array or object into the columnar format

Please go through link to know about FLATTEN(FLATTEN)
Flatten
Lateral
3.GET METHOD:
We can get values of Json from GET method also and use below query as:
Get

Conclusion:
Snowflake supports different format of data to load into tables and JSON is the basic form of data as today many customers use due to easy to understand and key pair value. To retrieve Json data, Snowflake provides different approaches and if any array or object in JSON, we use flatten and get method. And we can load the data through explicitly casting of values.

 

 

Thoughts on “Loading JSON Data into Snowflake From Local Directory”

  1. Great article! The explanation about handling JSON data in Snowflake is really helpful — especially the part about using FLATTEN and GET methods for arrays and objects. I’ve been working with structured data a lot recently, and understanding different formats is crucial. If anyone’s interested in tech-related content, I recently published a post on different types of cables and their uses — might be helpful for those dealing with hardware setups too.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Geeta Lakhwani

Geeta Lakhwani is a Senior Technical Consultant at Perficient in Nagpur. She is always eager to learn new technologies and is excited to share her knowledge in Talend and DataStage Tools.

More from this Author

Follow Us