Views

Overview

Views are SQL queries that act like virtual tables in a database system and display results returned from a specified SQL query. In AP, views enable users to manage and analyze data more efficiently, serving as a powerful abstraction layer that simplifies complex SQL queries into reusable and manageable components.

SQL views are versatile tools in data management, offering numerous data capabilities:

• Join: Views can seamlessly integrate data from multiple tables through SQL JOIN operations. This capability is invaluable when you need to combine related datasets for comprehensive analyses.
• Filter: Views can filter data to focus on specific records, making it easier to work with subsets of data pertinent to particular analyses or reports.
• Aggregate: With views, you can perform aggregation queries, such as SUM, AVG, MAX, and COUNT, to summarize data. This is particularly useful for generating high-level reports from detailed data records.
• Enrich: Views can also enhance data by incorporating calculated columns or by formatting existing data in a way that is more suitable for user requirements or specific analyses.

Types of views

AP provides utilize two primary types of SQL views.

• Logical views: These are the standard types of views that do not store data physically. They are essentially SQL queries which execute every time the view is accessed. Logical views are ideal for real-time data analysis and scenarios where data changes frequently.
• Materialized views: Unlike logical views, materialized views store the query result as a physical table on the disk. This type of view is particularly useful for datasets that do not change frequently but require fast read access. Materialized views improve performance by storing the computed result, reducing the load on compute resources during each query execution. Note that in AP, materialized views are created as regular tables. A benefit of tables over "native" materialized views is the ability to drop tables which are part of the SQL query of the view.

Manage views

The following section covers how to view, create, update and remove views.

View views

1. Click Views in the left side menu to list all views.
2. Click the name of a view to see more information.

Create view

1. Click the Create new button from the top-right corner.

2. Enter the following information.

   Field	Description
   Name	The name of the view
   Description	A description of the view
   Tags	Free text tags which categorizes the view
   Schema	The schema in which to store the view
   View name	The name of the data warehouse view, meaning the name as it will appear in the data warehouse
   SQL query	The SQL query uses to retrieve data for the view

Edit view

1. Find and click the view to edit in the list.
2. Open the context menu by clicking the icon in the top-right corner.
3. Click Edit.
4. Edit values in the relevant sections.
5. Click Save at the bottom of the section.
6. Close the dialog by clicking the close icon in the top-left corner.

Edit SQL query

1. Find and click the view to edit in the list.
2. Click the context menu in the top-right corner.
3. Click Edit the SQL query.
4. In the SQL editor, edit the SQL query.
5. Click Save.

Remove view

1. Find and click the view to remove in the list.
2. Open the context menu by clicking the icon in the top-right corner.
3. Click Remove.