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:

Snowflake table with a single column of type VARIANT.

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!

Diese Übung ist Teil des Kurses

Introduction to NoSQL

Kurs anzeigen

Anleitung zur Übung

Use dot-notation to retrieve the city field from the city_meta column in the host_cities table.
Use dot-notation to query the nested country field from the city_meta column in the host_cities table.

Interaktive Übung

Vervollständige den Beispielcode, um diese Übung erfolgreich abzuschließen.

# 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)

Code bearbeiten und ausführen