Managing Views and Synonyms

This section introduces all the supported View commands. The Table-query (or bound query) has been developed to support the typical SQL commands supported by a relational database: CREATE, DROP, TRUNCATE, DELETE, alongside non-standard SHOW VIEWS.

In Lenses, a view is a virtual table, generated dynamically based on the results of a SELECT statement. A view looks and acts just like a real table, but is always created on-the-fly as required, so it is always up to date.

A synonym is an alias for a table. This is useful if you have a topic with a long unwieldy name, like customer_reports_emea_april_2018_to_march_2018 and you want to simply access this as customer_reports.

Creating a View

Standard SQL syntax is used to create a view:

CREATE VIEW <viewname> AS <query>

Where viewname is the name of the virtual table that is used to access the records in the view, and query is a standard SELECT statement.

Then we can query the view as follows:

SELECT *
FROM customer_emails

If it looks like a table, and talks like a table, then it is a table. A view is presented to the user exactly like a normal table. This means that a view can be filtered even more or that a projection can be applied to a view:

SELECT *
FROM customer_emails
WHERE name like 'sam%'

Deleting a View

If you wish to remove a view that is no longer needed or needs to be updated, you must first delete the old one. This can be done using the following syntax:

DELETE VIEW <viewname>

Inspecting a View

Sometimes you may wish to confirm the definition of a view. You can do this using the following syntax:

SHOW VIEW <viewname>

Creating a Synonym

Standard SQL syntax is used to create a synonym:

CREATE SYNONYM <name> FOR <table>

Deleting a Synonym

If you wish to remove a synonym that is no longer needed or if you want to update an existing synonym, you must delete the old one first. You can do that with the following syntax:

DELETE SYNONYM <name>

Common Use Cases

Three common reasons for using a view are creating a projection from a table with a large number of fields, representing joins as a single table, and creating a preset filter. We will cover each scenario with an example.

Projection Subset

Let us say we have a table called customers which contains full customer records - name, email, age, registration date, country, password, and many others – and we find ourselves repeatedly querying it for just name and email. A view could be created that returns just the name and email as a projection.

Then the create view statement would look like this:

CREATE VIEW customer_emails AS
SELECT name, email
FROM customers

Then, we no longer need to tediously specify the projection each time. Clearly the benefit is larger when we want to select a greater number of fields - say a topic with 50 fields and we want to select only 15 - in this scenario a view would be a huge time saver.

Representing Joins

The statement that is used to generate the view can consist of one or more tables, so one common use case of views is to represent joined tables as if they were a single table. This avoids the need for writing a complex join query each time.

For example:

CREATE VIEW orders AS
    SELECT c.customer_id
        , c.customer_name
        , c.customer_email
        , o.order_id
        , o.order_date
        , a.address
        , a.postcode
    FROM customers c JOIN orders o ON c.customer_id = o.customer_id
        JOIN addresses a ON o.delivery_address_id = a.address_id

Then we can select from this join as easy as:

SELECT *
FROM orders

Preset Filter

Finally, another common use case is to define a filter that is commonly used. Let us say we had a topic that contained transactions and we often found ourselves searching for transactions from the UK. We could run this query each time:

SELECT *
FROM transactions
WHERE country = "UK"

Or we could setup a view with this filter pre-applied:

CREATE VIEW transactions_uk AS
SELECT *
FROM transactions
WHERE country = "UK"

Then use a SELECT query as follows:

SELECT *
FROM transactions_uk

Clearly in this example we are saving only a small amount of effort, but it is easy to see how this can be a timesaver if the filter clause is complicated, or we have many of them. In the transactions scenario, we could have easily created views for many different countries.