Managing views

View database objects allow the result of a query to be stored. In Amazon Redshift, views run each time a view is mentioned in a query. The advantage of using a view instead of a table is that it can allow access to only a subset of data on a table, join more than one table in a single virtual table, and act as an aggregated table, and it takes up no space on the database since only the definition is saved, hence making it convenient to abstract complicated queries. In this recipe, we will create views to store queries for the underlying tables.

Getting ready

To complete this recipe, you will need access to any SQL interface such as a SQL client or query editor.

How to do it…

Let's create a view using the CREATE VIEW command. We will use the following steps to create a view:

1. Create a finance.customer_vw view based on the results of the query on finance.customer:

   CREATE VIEW finance.customer_vw 
   AS
   SELECT customer_number,
          first_name,
          last_name,
          EXTRACT(year FROM date_of_birth) AS year_of_birth
   FROM finance.customer;

2. To verify that a view has been created, you can use the following command:

   SELECT table_schema as schema_name,
          table_name as view_name,
          view_definition
   FROM information_schema.views
   WHERE table_schema not in ('information_schema', 'pg_catalog')
   ORDER by schema_name,
            view_name;

   Note

   This script will provide an output of the views created under a particular schema and the SQL script for the view.

3. We can now select directly from the finance.customer_vw view, just like with any another database object, like so:

   SELECT * from finance.customer_vw limit 5;

   Note

   Here, the finance.customer_vw view abstracts the date_of_birth personally identifiable information (PII) from the underlying table and provides the user an abstracted view of only the essential data for that year to determine the age group.

   This is the expected output:

   outputcustomer_number,first_name,last_name,year_of_birth 
   1 foo bar 1980
   2 john smith 1990
   3 spock spock 1970
   4 scotty scotty 1975
   5 seven of nine 1990 

4. To delete the previously created view, you can use the following command:

   DROP VIEW finance.customer_vw ;