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!
Questo esercizio fa parte del corso
Introduction to NoSQL
Istruzioni dell'esercizio
- Use dot-notation to retrieve the
cityfield from thecity_metacolumn in thehost_citiestable. - Use dot-notation to query the nested
countryfield from thecity_metacolumn in thehost_citiestable.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
# 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)