Views
Amorphic Views is a feature that allows users to create views on structured data. These views can be shared with other authorized users and tags in the organization.
You can now create multiple views with the same name in the Amorphic application. However, each view must have a unique name within a specific domain, and the domain name will be visible with the view name.
The Amorphic Views page provides the capability to browse various views, generate a new view, and access further details regarding a specific view. Utilizing domain filters can assist you in locating the desired view.
Create New Views
You can create new Views in Amorphic by using the “New View” functionality of Amorphic application.
To generate a new view, you will need essential details such as the Domain, Target Location, View Type, etc. It is important to note that amorphic views can only be generated using structured datasets, for which the user has permission to access.
The following information is needed to create a new view:
| Properties | Details |
|---|---|
| View Name | View name must be 3-120 alphanumeric, _ characters only, and unique under a given Domain. |
| Description | Description of the view being created. |
| View Type | Type of view. Eg: Standard, Materialized. |
| Domain | Logical grouping of views used as schema name in datawarehouse. |
| Target Location | Type of datawarehouse where you want to create the view. |
| Assume Role | Enabled to utilize user IAM role for query execution during view creation, this functionality is only relevant for views intended for Athena as their target location. This feature is not available for generating views on system datasets or when the user manages to create more than 220 datasets and views. |
| Auto Refresh | Materialized views are the only type of view for which auto refresh feature is available in Redshift. You have the option to either activate or deactivate auto-refresh, or manually refresh the view at your discretion. |
| Keywords | Keywords indexed & searchable in app. Choose meaningful keywords to flag related datasets for easy retrieval at a later time. |
| Data Classifications | List of classifications created based on different categories to protect the data more efficiently. |
| Sql statement | Sql statement used to create a view or edit a view Eg: create view as select * from amorphic.tables; |
For a Multi tenancy deployment, you can use Amorphic to run queries and create views across different tenants or databases. To access an object from another tenant or database, you need to utilize the complete path notation for referencing datasets, which includes the tenant name, schema name, and object name. (e.g. tenant.schema.object.)
For cross-tenant view creation , you have to use 'with no schema binding' at the end of the query. (e.g. create view {domain1}.{viewName} as select * from {tenant2}.{domain_2}.{datasetName} with no schema binding).
To access a particular column, use tenant_name.schema_name.object_name.column_name.
View Details
Standard Views:
From the details tab, you can access view details. Some important fields to consider in view details are:
- IsActive: Determines if there is a valid view in the data warehouse that the user can query.
- View Status: Status can be create_complete, create_failed, update_rollback_complete, update_complete, create_in_progress, update_in_progress.
- Schema Details: Shows underlying schema of the views.
While creating the view:
- If a view is successfully created IsActive is marked as yes and View Status will be shown as create_complete
- If a view is fails to get created IsActive is marked as no, View Status will be shown as create_failed and Error Message will be displayed
If the view has been deleted manually in the backend or using aws console, then the IsActive is marked no, ViewStatus will be deleted_from_backend and respective Error Message is displayed.
- For standard views created in redshift, if views are created by "WITH NO SCHEMA BINDING" option, The source view will not consider these views as dependent views.
SQL satement for createing view with no schema binding:
Create view DomainName.View2 as select * from DomainName.View1 WITH NO SCHEMA BINDING
Here, the View2 is not considered a dependent view of View1. However, if the View1 is deleted, view2 cannot be queried.
Materialized Views:
Apart from basic view details, Important fields which are to be considered in materialized view details are:
- AutoRefresh: Redshift performs refresh on the views based on workload. It can be either enabled or disabled. You will still be able to perform manual refresh.
- RefreshStatus: The status of the refresh. For example statuses, check the Refresh Status documentation.
- LastRefreshCompletionTime: Time when the materialized view completed the last refresh.
Edit Views
Standard Views:
To edit a view, consisting statements beginning with CREATE OR REPLACE VIEW.
While updating the view:
- If an update fails on a failed view IsActive will stay as no and View Status will be shown as create_failed and Error Message will be displayed
- If an update is successful on a failed view IsActive will change to yes and View Status will be shown as create_complete
- If an update fails on a successfully created view IsActive will stay as yes and View Status will be shown as update_rollback_complete and Error Message will be displayed
- If an update is successful on a successfully created view IsActive will stay as yes and View Status will be shown as update_complete
A view cannot be edited or deleted when the View Status is create_in_progress or update_in_progress.
Materialized Views:
A materialized view is a type of database view that stores the results of a query. To edit a materialized view, it must not be in active state. Start by typing create materialized view and then make changes to the view. If the materialized view is in active state, you can only update its metadata fields.
While updating the view:
- If an update fails on a failed view IsActive will stay as no and View Status will be shown as create_failed and Error Message will be displayed
- If an update is successful on a failed view IsActive will change to yes and View Status will be shown as create_complete
- If an update is successful on a successful view IsActive will stay as yes and View Status will be shown as update_complete
A materialized view cannot be edited or deleted when the View Status is create_in_progress or update_in_progress.
If you don't finish an action like creating or updating something within 15 minutes, it will fail and you will receive an error message saying "Request Timed Out". If this happens, you need to check the problem and try again. For example, if you are creating a view and it takes too long, you will get a time out error. You need to fix the query and try creating it again.
Refresh Materialized View
It can be refreshed manually or by selecting the auto-refresh option. If the auto-refresh option is included in the SQL statement and is selected as well when the view is created or updated, the auto-refresh option will be taken as priority.
You can enable or disable auto-refresh on a materialized view at any time by editing the view.
View Versions
This section keeps track of all SQL statements used to create/update a view as versions.
Users can delete or switch to existing versions. If an SQL statement is used to update the view,
which is already part of one of the previous versions, it will switch to the older version instead of creating a new version.
Switch to a different version
- Versions will be automatically enabled for newly created views.
- The first version for existing views will be created when users update the view with a new SQL statement.
Views use case
An example of using Amorphic Views could be for a situation where:
A company where various departments such as finance, sales, and marketing need to access and analyze sales data. Each department may have unique reporting requirements and use different filters, sorts, and aggregations on the same data.
With Amorphic Views, the departments can create their own views on the sales data, apply the necessary filters and sorting strategies, and share them with other authorized members within their department. This allows each department to quickly access the data they need without having to create their own separate reports each time.
The domain name ensures that each view is unique and easily distinguishable, making it easier for users to find and access the right view for their needs.