Space and Time Views
What it is and why we built it
Navigating through complex datasets and executing multifaceted queries is a common challenge for many data-driven applications. Developers may find themselves writing and running the same complex, multi-table queries over and over again. This can be both time-consuming and error-prone, especially as your data grows and evolves. In order to support a better developer workflow, we’re excited to announce that we’ve introduced support for views in Space and Time.
At its core, a view is like a saved shortcut for a query that you can reference in future SQL statements. Space and Time already allows you to save and name your queries on the Studio interface, but with the added support for views, you can reference your saved query in a new SQL statement and easily use it to compose more complex logic for your smart contracts in a cost-efficient way. Additionally, this upgrade will enable faster query query results for faster loading on your app, dashboards, BI tools, etc.
How it works
A view is a type of virtual table; it is a named SQL query stored in the database. Typical uses include:
- Structuring data in a manner consumers will find more natural, simple, etc.
- Restricting data access/visibility (e.g. limiting table columns)
- Providing a backwards-compatible interface for a table whose schema has changed
When you query a standard view, the database runs the underlying SQL query on the base tables and returns the results. It's essentially a way to save and reuse complex queries, but it doesn't store the result data, so every time the view is queried, the underlying query needs to be executed again.
Space and Time supports standard views and also offers support for two specialized types of views: materialized views and parameterized views.
Materialized views
A materialized view is a view whose results are materialized prior to execution. In other words, on some user-configured impulse (be it a successful commit to the referenced table or just a passed time interval), the data stored in the materialized view is refreshed. From the user’s perspective, operating on materialized views is quite straightforward: they are queried the same as views, and creating one only requires additional configuration regarding the refresh.
Parameterized views
A parameterized view is an extension of a view that adds parameters. Parameters are variables which the user must provide some values for when executing the view. Parameterized views thus enable templating of common queries, which further enhances productivity when interacting with the database.
What It Enables
Space and Time’s support for standard, materialized, and parameterized views opens up a wealth of new use cases for analytics:
- Complex analytic operations: If you're dealing with complex data analytics involving multiple heavy computations, like time-series analysis, multi-dimensional analysis, or any other sophisticated operations, views can significantly speed up these operations by storing pre-computed results, allowing you to generate insights more efficiently.
- Faster dashboards: Let's say your application contains a dashboard that shows recent user activity, key performance metrics, and other information that updates regularly. Instead of running resource-heavy queries every few seconds to refresh the dashboard, you can use a materialized view to store the results of those queries. This reduces the load on the database and allows the dashboard to be updated more quickly.
- Personalized user experiences: Parameterized views are perfect for scenarios where you need to retrieve specific information based on individual user profiles or actions. If you want to display a personalized activity feed for each user in your app, with a parameterized view, you can create a query template with a placeholder for the user ID, so you don’t have to write unique queries for each user. This not only simplifies your code but also improves your app's performance. Let’s say you want to create a graph for NFT purchases by time range. Instead of simply providing the current state of the blockchain, you can easily perform historical analysis by using a few simple parameters, like time range, transaction hash, smart contract address, or user address information.
