Querying semi-structured data in Snowflake
With Snowflake, semi-structured data can be stored in its most raw form. Here, information about a handful of Olympic host cities is stored in the city_meta
column of the host_cities
table. This column takes type VARIANT
, allowing for unstructured data to be stored in this single column. The data takes the form below:
In this exercise, you'll practice querying this data using both bracket and dot notations. A connection object conn
for the olympics
database has been created for you. Good luck!
This exercise is part of the course
Introduction to NoSQL
Exercise instructions
- Use dot-notation to retrieve the
city
field from thecity_meta
column in thehost_cities
table. - Use dot-notation to query the nested
country
field from thecity_meta
column in thehost_cities
table.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# Build a query to pull city and country names
query = """
SELECT
city_meta:____,
____:____
FROM host_cities;
"""
# Execute query and output results
results = conn.cursor().execute(query).fetch_pandas_all()
print(results)