Views

Views in Spice are virtual tables defined by SQL queries. They help simplify complex queries and promote reuse across different applications by encapsulating query logic in a single, reusable entity.

Defining a View

To define a view in the spicepod.yaml configuration file, specify the views section. Each view definition must include a name and a sql field.

Example

The following example demonstrates how to define a view named rankings that lists the top five products based on the total count of orders:

views:
  - name: rankings
    sql: |
      WITH a AS (
        SELECT products.id, SUM(count) AS count
        FROM orders
        INNER JOIN products ON orders.product_id = products.id
        GROUP BY products.id
      )
      SELECT name, count
      FROM products
      LEFT JOIN a ON products.id = a.id
      ORDER BY count DESC
      LIMIT 5

Fields

• name: The view's identifier, used for referencing in queries.
• sql: The SQL query defining the view, supporting joins, subqueries, and aggregations.
• acceleration: Views can be locally accelerated.

Limitations and Considerations

• Views are read-only; insert, update, and delete operations are not supported.
• Performance depends on SQL complexity and underlying data.
• Ensure queries are optimized to prevent slow execution.