Creating and querying a view

Now that you know the basics of creating a table and querying data from a table, learn how to create a more complex query using views.

Learning goal

  • How to create a view
  • How to query a view

Learning path

In the following example, you will use the columns from the created Faker table. In this derived computation that can be saved as a view, the orders are aggregate in half minute intervals based on their status.

Copy and paste the following CREATE VIEW statement to the SQL window:

DROP VIEW IF EXISTS summaries;
CREATE VIEW summaries AS
SELECT window_start, window_end, order_status, COUNT(*) as order_count, SUM(amount) as total_amount
FROM TABLE(
    TUMBLE(TABLE orders, DESCRIPTOR(order_time), INTERVAL '20' SECONDS))
GROUP BY window_start, window_end, order_status;

If your previous query is still running, click on the Stop button under the SQL window to stop the job. Afterwards, to create the view, you need to click on the Execute button under the SQL window.

Before selecting data from this view, you can change the Sample Behavior to sample all messages on the Settings tabs. This way the sampled output is not limited to a given amount of messages.

Add the following SQL query to the SQL window to run the query:
SELECT * FROM summaries

To execute the query, you need to click on the Execute button under the SQL window.

After a short amount of time, the samples of the aggregated result continously show up under the Results tab. As it is an aggregated view, the results can appear slower after another.

Next step

As you need to output the queried data to some kind of an output, the next step is to learn how to add a sink to your SQL job pipeline.