Views and synonyms
This section introduces all the supported View commands.
Lenses supports the typical SQL commands supported by a relational database:
- SHOW VIEWS
view is a virtual table, generated dynamically based on the results of a
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.
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 access this as customer_reports.
Creating a view
To create a view:
CREATE VIEW <viewname> AS <query>
viewname is the name of the virtual table that is used to access the records in the view, and query is a standard
Then we can query the view as follows:
SELECT * FROM customer_emails
A view acts as a virtual 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
To delete a view:
DROP VIEW <viewname>
If you wish to modify an existing view, use the syntax above to delete it, and then create a new view with the same name.
Inspecting a view
To see a definition of a view. You can use the following syntax:
SHOW VIEW <viewname>
Creating a synonym
To create a synonym:
CREATE SYNONYM <name> FOR <table>
Deleting a synonym
To delete a synonym:
DROP SYNONYM <name>
If you wish to modify an existing synonym, use the syntax above to delete it, and then create a new synonym with the same 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.
If 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.
CREATE VIEW customer_emails AS SELECT name , email FROM customers
There is no reason to specify the projection each time.
The benefit is more significant when we want to select a higher number of fields - say a topic with 50 fields, and we want to select only 15.
The statement that is used to generate the view can consist of one or more tables. One 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.
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 like this:
SELECT * FROM orders
Finally, another use case is to define a filter that is commonly used. If a topic contains 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"
Alternatively, we can set up 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