Snowflake - Loading Semi Structured Data - JSON

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:

------------------------------------------------------------

Missed Flattening in explanation, but available in below queries

-- Processing semi-structured data (Ex.JSON Data)

--Creating required schemas
CREATE OR REPLACE SCHEMA MYOWN_DB.external_stages;
CREATE OR REPLACE SCHEMA MYOWN_DB.STAGE_TBLS;
CREATE OR REPLACE SCHEMA MYOWN_DB.INTG_TBLS;

--Creating file format object
CREATE OR REPLACE FILE FORMAT MYOWN_DB.file_formats.FILE_FORMAT_JSON
TYPE = JSON;

--Creating stage object
CREATE OR REPLACE STAGE MYOWN_DB.external_stages.STAGE_JSON
STORAGE_INTEGRATION = s3_int
URL = 's3://awss3bucketjana/json/';

--Listing files in the stage
LIST @MYOWN_DB.external_stages.STAGE_JSON;

--Creating Stage Table to store RAW Data
CREATE OR REPLACE TABLE MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW
(raw_file variant);

--Copy the RAW data into a Stage Table
COPY INTO MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW
FROM @MYOWN_DB.external_stages.STAGE_JSON
file_format= MYOWN_DB.file_formats.FILE_FORMAT_JSON

--View RAW table data
SELECT * FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW;

--Extracting single column
SELECT raw_file:Name::string as Name FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW;

--Extracting Array data
SELECT raw_file:Name::string as Name,
raw_file:Pets[0]::string as Pet
FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW;

--Get the size of ARRAY
SELECT raw_file:Name::string as Name, ARRAY_SIZE(RAW_FILE:Pets) as PETS_AR_SIZE
FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW;

SELECT max(ARRAY_SIZE(RAW_FILE:Pets)) as PETS_AR_SIZE
FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW;

--Extracting nested data
SELECT raw_file:Name::string as Name,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
raw_file:Address.State::string as State
FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW;

--Parsing entire file
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[0]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
raw_file:Address.State::string as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW
UNION ALL
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[1]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
raw_file:Address.State::string as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW
UNION ALL
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[2]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
raw_file:Address.State::string as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW
WHERE Pets is not null;

--Creating/Loading parsed data to another table
CREATE TABLE MYOWN_DB.INTG_TBLS.PETS_DATA
AS
ABOVE SELECT STATEMENT

--Viewing final data
SELECT * from MYOWN_DB.INTG_TBLS.PETS_DATA;

--Truncate and Reload by using flatten

TRUNCATE TABLE MYOWN_DB.INTG_TBLS.PETS_DATA;

INSERT INTO MYOWN_DB.INTG_TBLS.PETS_DATA
select
raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
raw_file:Address.State::string as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW,
table(flatten(raw_file:Pets)) f1;

--Viewing final data
SELECT * from MYOWN_DB.INTG_TBLS.PETS_DATA;

[charlesa]

You know what? I have developed a personal likeness for you Janar.... so good and audible!

[VasukiM-syls]

This is the only video explaining Loading JSON from Azure.Kudos to you.Thanks

[tanukuwest]

Your way of explaining each and every detail is amazing Sir.People with no knowledge of databases can also understand your videos easily.Thank you so much for your wonderful content.
Please upload 4-5 project videos using Snowflake.
I wish you could also make tutorials on DATABRICKS.

[swapnagandi]

You are just Amazing!!!! The minute details you share feels speechless. Thanks a Zillion!!!

[mounikakaja]

Can you please upload more videos, the content is very useful and explained in a very clear manner with Examples. Thankyou

[prabhakarreddyk]

Thank you so much for your detailed explanation.

[mrjana]

-- Processing semi-structured data (Ex.JSON Data)

--Creating required schemas
CREATE OR REPLACE SCHEMA JANA_DB.EXTN_STAGES;
CREATE OR REPLACE SCHEMA JANA_DB.STAGE_TBLS;
CREATE OR REPLACE SCHEMA JANA_DB.INTG_TBLS;

--Creating file format object
CREATE OR REPLACE FILE FORMAT
TYPE = JSON;

--Creating stage object
CREATE OR REPLACE STAGE
STORAGE_INTEGRATION = azsf_jana_feb22
URL =

--Listing files in the stage
LIST

--Creating Stage Table to store RAW Data
CREATE OR REPLACE TABLE
(raw_file variant);

--Copy the RAW data into a Stage Table
COPY INTO
FROM
file_format=
FILES=('pets_data.json');

--View RAW table data
SELECT * FROM

--Extracting single column
SELECT raw_file:Name::string as Name FROM

--Extracting Array data
SELECT raw_file:Name::string as Name,
raw_file:Pets[0]::string as Pet
FROM

--Get the size of ARRAY
SELECT as PETS_AR_SIZE
FROM

--Extracting nested data
SELECT raw_file:Name::string as Name,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
as State
FROM

--Parsing entire file
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[0]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from
UNION ALL
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[1]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from
UNION ALL
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[2]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from
WHERE Pets is not null;

--Creating/Loading parsed data to another table
CREATE TABLE JANA_DB.INTG_TBLS.PETS_DATA
AS
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[0]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from
UNION ALL
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[1]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from
UNION ALL
SELECT raw_file:Name::string as Name,
raw_file:Gender::string as Gender,
raw_file:DOB::date as DOB,
raw_file:Pets[2]::string as Pets,
raw_file:Address."House Number"::string as House_No,
raw_file:Address.City::string as City,
as State,
raw_file:Phone.Work::number as Work_Phone,
raw_file:Phone.Mobile::number as Mobile_Phone
from
WHERE Pets is not null;

--Viewing final data
SELECT * from JANA_DB.INTG_TBLS.PETS_DATA;

[Pardhu]

This was a good video it will help to others

Can you make more videos about snowflake like real time scenarios

[rajinikanthgudimalla]

Thanks for uploading....keep more for others learning

[MrErPratikParab]

Instead of parsing array index 0 to n-1, (for pet details), can we store array max size in one variable, and Run loop from index 0 to varaible-1 time, and pass value

[muralikrishna-gkhx]

Hi sir in my stage i have json file without lading variant column using select how we can read

[Jubin]

Good presentation. Just one suggestion. We are unable to see the result of the queries since it is hidden by your face. If you can turn off the video camera while recording the session, then it would be helpful.

[kumarevk]

Can we have a local variable where we keep a JSON data and load into a temporary table? Instead of having JSON in S3. Please clarify.

[MrVenkatesh]

Hi Sir, If I have to Automate the process to run daily, do I need to create multiple tasks which runs one by one? Do we have any option to create a task flow option which runs one by one?

[SaiKumarGaddam-gx]

why json support only variant data type in snowflake

[trumper]

Pls upload plain vedios i.e without adding your face vedio snippet.

Since most of corner information missing,
Also looks your are comfortable in explain.

[shaikyusuf]

Hi Sir,
Can you please attach the Raw data (json) file that would be very help!!.

[ushakiran]

HOW TO RECTIFY THIS ERROR
Object does not exist, or operation cannot be performed.

[saraneegupta]

If on day1, json contains 100 colums, next day 96..will it throw error while loading into table

[Majnua]

Sir, not able to find Json sample file. plz help me on this