by 
Because we’re so excited about Python worksheets, Snowpark and all that jazz and because we just love giving you those pesky JSON parsing exercises – this week, we’ve combined the two!
Here’s your start up code!
create or replace table week43 as select
parse_json('{
"company_name": "Superhero Staffing Inc.",
"company_website": "https://www.superherostaffing.com",
"location": {
"address": "123 Hero Lane",
"city": "Metropolis",
"state": "Superstate",
"zip": "98765",
"country": "United Superlands"
},
"superheroes": [
{
"id": "1",
"name": "Captain Incredible",
"real_name": "John Smith",
"powers": [
"Super Strength",
"Flight",
"Invulnerability"
],
"role": "CEO",
"years_of_experience": 10
},
{
"id": "2",
"name": "Mystic Sorceress",
"real_name": "Jane Doe",
"powers": [
"Magic",
"Teleportation",
"Telekinesis"
],
"role": "CTO",
"years_of_experience": 8
},
{
"id": "3",
"name": "Speedster",
"real_name": "Jim Brown",
"powers": [
"Super Speed",
"Time Manipulation",
"Phasing"
],
"role": "COO",
"years_of_experience": 6
},
{
"id": "4",
"name": "Telepathic Titan",
"real_name": "Sarah Johnson",
"powers": [
"Telepathy",
"Mind Control",
"Telekinesis"
],
"role": "CFO",
"years_of_experience": 9
}
]
}
') as json;Your job is to create a stored procedure using Python worksheets to parse out the data and make it look like this:
A shameless plug, but if you need a little help, I do cover JSON parsing using Snowpark in the below video:
Remember, if you want to participate:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge
3 responses to “Week 43 – Intermediate”
-
Thanks for the challenge. Still getting used to this. Finding the feedback cycle different with the worksheets
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_43.py.info
-
This is the kind of challenge I like! I had some fun with this one, challenging myself to do it without converting the Snowflake dataframe to a Pandas one.
From my understanding, converting the Snowflake dataframe into a pandas one will execute the SQL in Snowflake to retrieve the Snowflake dataframe, then store the whole thing in memory whilst you work with it using the pandas dataframe. As I have avoided using pandas, my procedure runs as if it is executing a single SQL command.
This doesn’t really mean much when you are running the procedure inside Snowflake as a Python worksheet, but I would expect my non-pandas solution to have much better performance when executing on a local machine, as the work will be pushed down to Snowflake instead of executed locally
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
Nice way to learn more about snowpark and manipulating data with python
- Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_43.py
Leave a Reply
You must be logged in to post a comment.