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 />
- 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:
And Snowflake provides facility to load the file directly into table as it is available in local, follow below steps:
Click on Upload local files
Browse the file which you want to load into the table
Click on next
Then load the data into table
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:
- Create file format as below:
-
Create Stage as below
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:temploadsales.Json @ JSON_DATA_STAGE;
Once execute the PUT command in Snowflake:
To check file load into stage through below query:
list @LOAD_JSON_DATABASE.LOAD_JSON_SCHEMA.JSON_DATA_STAGE;
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);
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:
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:
If want to retrieve data as a single column, use below query
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:
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)
3.GET METHOD:
We can get values of Json from GET method also and use below query as:
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.
Source: Read MoreÂ