Managing Views

​

Benefits

• Standardize common queries across your organization.
• Share these standard queries with others.
• Allow for finer-grained control over query results.
• Prevent accidental writes to tables.

​

Before you start

• An Account within a Professional, Innovation, or Enterprise Edition Organization. For more information, see Create a Wherobots Account.
  • Both Admin and User roles have access to Views in Wherobots Cloud.
• A writable schema in a Wherobots-managed catalog. The first example also requires an existing table with country and region columns; replace the example’s org_catalog.joins.polygons table name with yours. The geospatial example creates its own source tables.

​

Create a view

1. Log in to Wherobots Cloud.
2. Start a notebook with a runtime of your choice.
3. Open the notebook and execute the following code to initiate the SedonaContext.
   from sedona.spark import *
   config = SedonaContext.builder().getOrCreate()
   sedona = SedonaContext.create(config)
   
4. Replace the catalog, schema, and source table names in the following code with yours, then run it to create a view. Choose a suffix that is unique within your schema.

   Show code explanation

   This code performs two main actions:

   1. View Creation: This example defines a SQL view named org_catalog.joins.polygons_derived_view_unique_suffix_01.
      • A view is a saved query that runs against a table, rather than a new table itself. This example queries the org_catalog.joins.polygons table.
      • The view’s query groups the data by country and region and calculates the total number of polygons for each group, aliasing this count as polygon_count.
   2. Verification: This example queries the newly created view and uses the .show() method to display the results, allowing you to verify within the notebook that the view was created successfully and inspect the aggregated data.
   # Use a unique suffix for your view name to avoid naming conflicts
   view_suffix = "unique_suffix_01"
   
   # Create the view
   sedona.sql(f"""
       CREATE OR REPLACE VIEW
       org_catalog.joins.polygons_derived_view_{view_suffix}
       AS SELECT
           country,
           region,
           count(*) as polygon_count
       FROM
           org_catalog.joins.polygons
       GROUP BY
           country,
           region
   """)
   
   # Query your new view to verify its contents
   sedona.sql(f"SELECT * FROM org_catalog.joins.polygons_derived_view_{view_suffix}").show()
   

   Show parameter explanation

   Parameter	Description
   org_catalog.joins	Catalog and schema path. Replace with your specific catalog and schema names.
   polygons	Name of the table being queried. Replace with your specific table name.
   _derived_view_	A specific prefix chosen for the new view’s name. Replace with a naming convention that best serves your organization’s needs.
   unique_suffix_01	The unique identifier for the view (from the view_suffix variable in the code). Replace with your own unique suffix.
   country, region	Columns in the source table used for the GROUP BY operation. Replace these with the columns you wish to aggregate.
   polygon_count	The alias (the new name) given to the count(*) column. Replace this with any valid column name you prefer (e.g., total, shape_count).

   To see the new view in Wherobots Cloud, navigate to the Data Hub. Click on the view to verify its details, schema, and SQL definition.

​

Update a view

# The string must match the name of the view you previously created
view_suffix = "unique_suffix_01"

# Update the view with a new SQL definition
sedona.sql(f"""
    CREATE OR REPLACE VIEW
    org_catalog.joins.polygons_derived_view_{view_suffix}
    AS SELECT
        country,
        region,
        count(*) as polygon_count
    FROM
        org_catalog.joins.polygons
    GROUP BY
        country,
        region
    HAVING count(*) >= 2
""")

# Query the updated view
sedona.sql(f"SELECT * FROM org_catalog.joins.polygons_derived_view_{view_suffix}").show()

​

Rename a view

# The suffix should match the view you created previously
view_suffix = "unique_suffix_01"

# Rename the view
sedona.sql(f"""
    ALTER VIEW
    org_catalog.joins.polygons_derived_view_{view_suffix}
    RENAME TO
    org_catalog.joins.polygons_derived_view_rename_{view_suffix}
""")

​

Delete a view

# The suffix should match the view you wish to delete
view_suffix = "unique_suffix_01"

# Delete the view
sedona.sql(f"DROP VIEW IF EXISTS org_catalog.joins.polygons_derived_view_rename_{view_suffix}")

​

List views

# We recommend using truncate=False to see the
# entire name of each view in your organization.
sedona.sql(f"SHOW VIEWS FROM org_catalog.joins").show(truncate=False)

​

Example: Create a view with geospatial data

1. In a Wherobots Notebook, initiate the SedonaContext if you haven’t already:
   from sedona.spark import *
   config = SedonaContext.builder().getOrCreate()
   sedona = SedonaContext.create(config)
   
2. Create the example schema, then load the sample datasets and write them to your catalog. If your organization uses a different writable managed catalog, replace org_catalog.joins throughout this example.

   Show code explanation

   This code loads two geospatial datasets from S3: a raster file (.tif) containing Central Park’s elevation data and a vector file (.parquet) with NYC building footprints. It writes both of these datasets into new Iceberg tables.
   
   # Replace this with a suffix unique within your organization.
   view_suffix = "geospatial_example_01"
   
   sedona.sql("CREATE SCHEMA IF NOT EXISTS org_catalog.joins")
   
   # URI of sample raster elevation data
   central_park_uri = 's3://wherobots-examples/data/onboarding_1/CentralPark.tif'
   
   # Load the raster into a Sedona DataFrame
   sedona.read\
                   .format("raster")\
                   .option("tileWidth", "256")\
                   .option("tileHeight", "256")\
                   .load(central_park_uri)\
                   .writeTo(f"org_catalog.joins.nyc_central_park_dem_{view_suffix}")\
                   .using("iceberg")\
                   .create()
   
   # URI of sample data in an S3 bucket
   geoparquet_uri = 's3://wherobots-examples/data/onboarding_1/nyc_buildings.parquet'
   
   # Load from S3 into a Sedona DataFrame
   sedona.read.format("geoparquet").load(geoparquet_uri)\
                   .writeTo(f"org_catalog.joins.nyc_buildings_geom_{view_suffix}")\
                   .using("iceberg")\
                   .create()
   
   
3. Create a view that joins the two tables and calculates the average elevation for each building.

   Show code explanation

   This code creates a SQL view that joins the building and elevation tables.Each raster tile contributes its elevation sum and covered-pixel count. The view divides the total sum by the total count to calculate the mean across all pixels in each building footprint, then keeps buildings with a positive mean elevation.
   sedona.sql(f'''
   CREATE VIEW org_catalog.joins.nyc_central_park_buildings_elevation_{view_suffix} AS
   WITH tile_stats AS (
       SELECT
           b.PROP_ADDR AS name,
           b.geom AS building_geom,
           RS_ZonalStatsAll(
               e.rast,
               ST_Transform(b.geom, 'epsg:4326', 'epsg:2263'),
               1,
               true
           ) AS stats
       FROM org_catalog.joins.nyc_buildings_geom_{view_suffix} AS b
       JOIN org_catalog.joins.nyc_central_park_dem_{view_suffix} AS e
         ON RS_Intersects(
             e.rast,
             ST_Transform(b.geom, 'epsg:4326', 'epsg:2263')
         )
   ), building_elevation AS (
       SELECT
           name,
           building_geom,
           SUM(stats.`sum`) / SUM(stats.`count`) AS elevation
       FROM tile_stats
       WHERE stats.`count` > 0
       GROUP BY name, building_geom
   )
   SELECT name, building_geom, elevation
   FROM building_elevation
   WHERE elevation > 0
   ''')
   
   Now, you can query or visualize your new view.

​

Usage considerations

• Currently, views from Unity Catalog are not accessible and will not appear in the Data Hub interface.
• Currently, creating views that reference tables stored in Unity Catalog is an unsupported operation.
• Within the Wherobots Managed Catalog, you have the flexibility to create views that reside in one existing catalog/schema while referencing data from another existing catalog/schema.