Amazon Redshift announces support for late binding views

[LookerExperts]

This content, written by Bruce Sandell, was initially posted in Looker Blog on Jan 30, 2018. The content is subject to limited support.

Amazon Redshift support for Late Binding Views.

A Late Binding View is a view that is not tied to the underlying database objects that it references. It is particularly beneficial for Amazon Redshift users that are storing current or more frequently used data in Redshift and historical or less frequently used data in Amazon S3.

Using Late Binding Views, you are able to create a single view that includes data in both Amazon Redshift and Amazon Redshift Spectrum External Tables, providing a single, comprehensive data set for your reporting needs without users having to worry about whether data is stored in Amazon Redshift or Amazon S3. Late Binding Views are the only type of view supported by Redshift Spectrum.

Prior to adding the functionality for Late Binding Views, you could only create a view that referenced existing database objects, and you could not drop the objects referenced within the view without first dropping the view or dropping the table using the CASCADE clause. In cases where the table was being dropped and recreated, any associated views would need to be recreated as well.

Late Binding Views don’t verify the objects referenced until the view is queried. This means that you can create views against tables that may not exist yet, and also that you can drop any of the database objects referenced by the view without first having to drop the view.

How they work

To create a Late Binding View, include the WITH NO SCHEMA BINDING clause when creating your view. When creating a view with the WITH NO SCHEMA BINDING clause, all tables and views referenced in the SELECT statement must be qualified with a schema name.

To create a view that includes data from both Redshift and S3, use a Late Binding View. For example:

CREATE VIEW all_web_logs_vw as
SELECT * FROM public.web_logs
UNION ALL
SELECT * FROM spectrum.historical_web_logs
WITH NO SCHEMA BINDING;

In this example, a Late Binding View is required because the view references an External Table.

Even when no External Tables are included, it may still be convenient to use the WITH NO SCHEMA BINDING clause in cases where any of the tables referenced in the view do not exist at the time the view is created, or if the tables referenced by the view may be dropped and recreated.

Amazon Redshift has also provided a new System Information Function, pg_get_late_binding_vew_cols, which provides metadata related to all of the columns in all Late Binding Views. For detailed information about the usage of the new function, check out from AWS.

Benefits for Looker wsers

Besides being able to query both current and historical data in a single view as detailed above, we are in the process of developing some new Looker functionality that takes advantage of Late Binding Views, so stay tuned for more updates!

For more information

To learn more about Late Binding Views, please refer to the CREATE VIEW section of the .