Snowflake - Loading Semi Structured Data - JSON

[@charlesa9431]

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

[@mrjana520]

Thank you

[@tanukuwest3119]

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.

[@VasukiM-sy3ls]

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

[@swapnagandi1287]

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

[@prabhakarreddyk194]

Thank you so much for your detailed explanation.

[@mrjana520]

-- 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 JANA_DB.EXTN_STAGES.FILE_FORMAT_JSON TYPE = JSON; --Creating stage object CREATE OR REPLACE STAGE JANA_DB.EXTN_STAGES.STAGE_JSON STORAGE_INTEGRATION = azsf_jana_feb22 URL = 'azure://optumstagejana22.blob.core.windows.net/datalakejana'; --Listing files in the stage LIST @JANA_DB.EXTN_STAGES.STAGE_JSON; --Creating Stage Table to store RAW Data CREATE OR REPLACE TABLE JANA_DB.STAGE_TBLS.PETS_DATA_JSON_RAW (raw_file variant); --Copy the RAW data into a Stage Table COPY INTO JANA_DB.STAGE_TBLS.PETS_DATA_JSON_RAW FROM @JANA_DB.EXTN_STAGES.STAGE_JSON file_format= JANA_DB.EXTN_STAGES.FILE_FORMAT_JSON FILES=('pets_data.json'); --View RAW table data SELECT * FROM JANA_DB.STAGE_TBLS.PETS_DATA_JSON_RAW; --Extracting single column SELECT raw_file:Name::string as Name FROM JANA_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 JANA_DB.STAGE_TBLS.PETS_DATA_JSON_RAW; --Get the size of ARRAY SELECT max(ARRAY_SIZE(RAW_FILE:Pets)) as PETS_AR_SIZE FROM JANA_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 JANA_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 JANA_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 JANA_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 JANA_DB.STAGE_TBLS.PETS_DATA_JSON_RAW 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, raw_file:Address.State::string as State, raw_file:Phone.Work::number as Work_Phone, raw_file:Phone.Mobile::number as Mobile_Phone from JANA_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 JANA_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 JANA_DB.STAGE_TBLS.PETS_DATA_JSON_RAW WHERE Pets is not null; --Viewing final data SELECT * from JANA_DB.INTG_TBLS.PETS_DATA;

[@mrjana520]

{ "Name": "Ravi", "Gender": "Male", "DOB": "1990-03-21", "Pets": ["Dog", "Cat"], "Address": { "House Number": "123/4", "City": "Hyderabad", "State": "Telangana" }, "Phone": { "Work": 123456789, "Mobile": 987654321 } }, { "Name": "Latha", "Gender": "Female", "DOB": "1995-04-16", "Pets": ["Dog", "Rabbit", "Mouse"], "Address": { "House Number": "567/8", "City": "Bangalore", "State": "Karnataka" }, "Phone": { "Work": 112233445, "Mobile": 998877665 } }

[@rajendrakumarakuthota6389]

Share your email I’d please

[@riyazriyyu936]

Very well explained ,Kudos!

[@mounikakaja3902]

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

[@venkis2072]

Very nice explanation

[@madhusudhanchinnala1986]

You are great sir🎉 Thank you.

[@subashinisevell4439]

Awesome 👍👍

[@rajinikanthgudimalla5454]

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

[@INDIAN-9528]

This was a good video it will help to others Can you make more videos about snowflake like real time scenarios

[@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

[@mrjana520]

No need to parse entire array, we can easily do with Flatten approach. I didn't cover flatten approach in this video. Please look at the Description of the video for code with flatten approach.

[@trumper9298]

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.

[@JacobGeorgeJames]

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.

[@shaikyusuf4932]

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

[@MrVenkatesh69]

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?

[@mrjana520]

Go through my Tasks for scheduling video, you will get answer

[@kumarevk4195]

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.

[@mrjana520]

A local variable can't store large file data and that will be a manual headache..

[@muralikrishna-gk4hx]

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

[@mrjana520]

There is a concept called external table, I have explained in my playlist, pls go through that

[@SaiKumarGaddam-g3x]

why json support only variant data type in snowflake

[@mrjana520]

To process semi-strucctured data like json, avro, parquet files snowflake has defined a new data type called Variant.

[@satishgunishetty6567]

Your videos are very helpful to learn. Can you please share the queries also anna.

[@mrjana520]

Queries are available in the description of the videos

[@satishgunishetty6567]

​@@mrjana520Thank you anna

[@ushakiran1624]

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

[@mrjana520]

Mostly the object is not created or the user doesn't have access to perform that operation on that object

[@SaiKumarGaddam-g3x]

Hi, can you help me in completing my capstone project

[@mrjana520]

Reach me on [e-mail]

[@saraneegupta7426]

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

[@mrjana520]

If those fields are not nullable in the target table then it will throw error otherwsie it will not throw

[@nagacnu03]

How to make array size dynamic

[@mrjana520]

There is something called Flatten, I didn't explain in the video but it is available in the queries given in the description, we can process whatever the array size is by using this Flatten

[@Majnu113a]

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

[@mrjana520]

I can be reachable on [e-mail]

[@ramakrishnatirumala428]

why you have taken pets[0] unianall pets[1]??/ if there are fifty animals in array .should we use 50 union all?? why dont you iterate each value in array in python or you can write Store procedure . and iterate that pets information....if i am not wrong

[@mrjana520]

Go all the way down, see the last query, we can avoid all those union all using FLATTEN function..

[@saraneegupta7426]

Why null is coming from Ravi though there are two pets

[@mrjana520]

Please watch the full video and listen carefully, I have explained why null is coming and how to avoid that null record.

[@masarathunnisa727]

Sir you did not show , how u created the integration...please show

[@mrjana520]

It is there in the other video named Sonwflake-AWS integration, please follow the videos in playlist where i have placed they order wise.

[@shaikjakeer1347]

Hi sir dicuess about cet concept

[@mrjana520]

What is cet?

[@shaikjakeer1347]

@@mrjana520 common table expression standard SQL feature sir please share u r number will want to talk

[@mrjana520]

That is CTE not cet. I can be reachable on [e-mail]

[@shaikjakeer1347]

@@mrjana520 sorry sir I did spell mistake

[@gsk320]

Please provide data in comments

[@mrjana520]

{ "Name": "Ravi", "Gender": "Male", "DOB": "1990-03-21", "Pets": ["Dog", "Cat"], "Address": { "House Number": "123/4", "City": "Hyderabad", "State": "Telangana" }, "Phone": { "Work": 123456789, "Mobile": 987654321 } }, { "Name": "Latha", "Gender": "Female", "DOB": "1995-04-16", "Pets": ["Dog", "Rabbit", "Mouse"], "Address": { "House Number": "567/8", "City": "Bangalore", "State": "Karnataka" }, "Phone": { "Work": 112233445, "Mobile": 998877665 } }

[@keerthanr1011]

to parse max array items you skipped, i guess if 1000 items cant be parsed using union all, Please explain that Sir, Thank You

[@mrjana520]

Yes, I missed that part, we can handle this using FLATTEN, please check the queries in the description, please check

[@PremKumar-wy3mx]

Hello sir, Can you please share this pets_data.json file in the description, I have downloaded different sample JSON file from internet but its displaying incorrectly so..

[@mrjana520]

reach me on [e-mail]

[@sunilmekala-d4w]

Hello Sir, while executing below query getting sql compilation error please help me 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, f1.value::string as Pet, 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:Mobile::number as Mobile_Phone FROM MYOWN_DB.STAGE_TBLS.PETS_DATA_JSON_RAW, table(flatten(raw_file:Pets))f1;

[@mrjana520]

There is issue in the lines, work_phone and Mobile_phone, please correct that and try