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.
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.
-- 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;
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
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.
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?
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.
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
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
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..
You know what? I have developed a personal likeness for you Janar.... so good and audible!
Thank you
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.
This is the only video explaining Loading JSON from Azure.Kudos to you.Thanks
You are just Amazing!!!! The minute details you share feels speechless. Thanks a Zillion!!!
Thank you so much for your detailed explanation.
Can you please upload more videos, the content is very useful and explained in a very clear manner with Examples. Thankyou
Very well explained ,Kudos!
Very nice explanation
Thanks for uploading....keep more for others learning
You are great sir🎉 Thank you.
This was a good video it will help to others
Can you make more videos about snowflake like real time scenarios
Awesome 👍👍
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.
-- 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;
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
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.
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?
Go through my Tasks for scheduling video, you will get answer
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.
why json support only variant data type in snowflake
To process semi-strucctured data like json, avro, parquet files snowflake has defined a new data type called Variant.
Hi sir in my stage i have json file without lading variant column using select how we can read
There is a concept called external table, I have explained in my playlist, pls go through that
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.
A local variable can't store large file data and that will be a manual headache..
Hi Sir,
Can you please attach the Raw data (json) file that would be very help!!.
HOW TO RECTIFY THIS ERROR
Object does not exist, or operation cannot be performed.
Mostly the object is not created or the user doesn't have access to perform that operation on that object
Your videos are very helpful to learn.
Can you please share the queries also anna.
Queries are available in the description of the videos
@@mrjana520Thank you anna
Hi, can you help me in completing my capstone project
Reach me on jana.snowflake2@gmail.com
How to make array size dynamic
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
If on day1,json contains 100 colums,next day 96..will it throw error while loading into table
If those fields are not nullable in the target table then it will throw error otherwsie it will not throw
Sir, not able to find Json sample file. plz help me on this
I can be reachable on jana.snowflake2@gmail.com
Why null is coming from Ravi though there are two pets
Please watch the full video and listen carefully, I have explained why null is coming and how to avoid that null record.
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
Go all the way down, see the last query, we can avoid all those union all using FLATTEN function..
Sir you did not show , how u created the integration...please show
It is there in the other video named Sonwflake-AWS integration, please follow the videos in playlist where i have placed they order wise.
{
"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 }
}
Share your email I’d please
Hi sir dicuess about cet concept
What is cet?
@@mrjana520 common table expression standard SQL feature sir please share u r number will want to talk
That is CTE not cet. I can be reachable on jana.snowflake2@gmail.com
@@mrjana520 sorry sir I did spell mistake
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..
reach me on jana.snowflake2@gmail.com
Please provide data in comments
{
"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 }
}
to parse max array items you skipped, i guess if 1000 items cant be parsed using union all, Please explain that Sir, Thank You
Yes, I missed that part, we can handle this using FLATTEN, please check the queries in the description, please check