Managing Views

A View is a virtual table which is based on one or more real sources. A View offers a flat representation of data from one or more Data Sources contained in rows and columns like those in the real DB table or from other View models. They can also be expressions made up of multiple or aggregated columns. If column definitions are not defined on the View table, they are derived from the projected columns of the view’s select transformation which is defined after the AS keyword.

Views created in the PIP settings can be used as Assets or Identities information points. The Views can contain data from any one or multiple defined Data Sources. You can add functions, JOIN statements, and WHERE clauses to a view data as if the data were coming from one single table.

Constraints on views are not enforced unless they are specified on an internal view, in which case they will be automatically added to the materialization target table. However, non-access pattern View constraints are still useful for other purposes, such as to convey relationships for optimization and for discovery by clients.

Creating Common View Queries

To create a new View or update an existing View, click Manage.
Note: It is only possible to start creating Views after at least one Data Source is created.

A query field is displayed. In this query field, you can write DDL commands that declare the creation of Views, setting for them name, attributes list and options, as well as connecting them to Data Sources in the "as select" phrase. Users can write up to 4MB of DDL content for larger database schemas.

This is the general structure of the statement:

CREATE VIEW {table-name} [
    (<view-element> (,<view-element>) (,<constraint>))
] 
[OPTIONS (<options-clause>)]
AS {transformation_query}

<table-element> ::= 
{column-name} [<data-type> <element-attr> <options-clause>]

<data-type> ::=
varchar | boolean | integer | double | date | timestamp .. (see Data Types)

<element-attr> ::=
[AUTO_INCREMENT] [NOTNULL] [PRIMARY KEY] [UNIQUE] [INDEX] [DEFAULT {expr}]

<constraint> ::=
CONSTRAINT {constraint-name} (
    PRIMARY KEY <columns> |
    FOREIGN KEY (<columns>) REFERENCES tbl (<columns>) |
    UNIQUE <columns> |
    ACCESSPATTERN <columns> |
    INDEX <columns>
)

<columns> ::= 
( {column-name} [,{column-name}]* )

<options-clause> ::=
<key> <value>[,<key>, <value>]*

As mentioned, in the DDL creating views you can use SQL-like commands that reflect the way you want to use data from Data Sources, connect models from one or multiple Data Sources, even from different types, filter data, and manipulate data. For that, you can use commands like CAST, CONCAT, JOIN, WHERE, etc.

Here are some examples for the "as select" part of the DDL:

1. One-to-one like the Data Source - select * from DS
2. A subset of the fields from Data Source - select a, b, c from DS
3. A manipulation of fields from Data Source - examples:
   1. concatenate two fields into one - Concat(a, b) as 'x'
      • Example: Concat 2 columns - CONCAT(field_A, field_B) as 'AB'
   2. concatenate using the || operator - (field_A || '-' || field_B || '-' || field_C) as 'ABC'
   3. Set value using CASE - select first_name, last_name, case when "location" = 'California' then 'CA' else "location" end from bank_users.
   4. Combining two tables into one using Join - select bu.first_name, bu.last_name, bu."role", bu.department, bc.project_id, bc.project_role from bank_users bu join bank_context bc on bu.uid = bc.uid

You can use line breaks and comments to better organize your views DDL. You can add comments in the views code panel by using single line comments or multi-line comments:

• Single line comment by -- prefix
  Example: --single line comment test
• Multi-line comments by using /* */
  Example:

  /*
  multi line
  comment
  test
  */
  

You can see some examples below:

CREATE VIEW {table-name} [(
   <view-element> (,<view-element>)
(,<constraint>))] [OPTIONS (<options-clause>)]
AS {transformation_query}
<table-element> ::=
{column-name} [<data-type> <element-attr> <options-clause>]
<data-type> ::=
varchar | boolean | integer | double | date | timestamp .. (see Data Types)
<element-attr> ::=
[AUTO_INCREMENT] [NOTNULL] [PRIMARY KEY] [UNIQUE] [INDEX] [DEFAULT {expr}]
<constraint> ::=
CONSTRAINT {constraint-name} (
PRIMARY KEY <columns> |
FOREIGN KEY (<columns>) REFERENCES tbl (<columns>)
UNIQUE <columns> |
ACCESSPATTERN <columns>
INDEX <columns>
<columns> ::=
( {column-name} [,{column-name}]* )
<options-clause> ::=
<key> <value>[,<key>, <value>]*

Creating Specific PAA View Queries

Specific View queries in PlainID enable users to create customized queries that apply to individual PAAs within each group. This functionality offers users greater control over functions and permissions assigned to different PAAs.

To create a specific PAA View query:

1. Locate the Views tab in the PIP settings.
2. Under the Common Query field, select Advanced to reveal the specific PAAs .
3. Verify that the specific View query does not exist in the Common Query field.
4. Locate the PAA you wish to modify and input the specific View query in the designated field below it.
5. Replicate the specific View query in the remaining PAAs with different behavior variations. Ensure that the queries are identical except where there are behavior changes.

In the example below, there is a PAA group containing PAAs called EU-AWS, US-GCP, and CA-AZR. There is a specific View query in EU-AWS and duplicate query in US-GCP and CA-AZR. They all have the same View names, but have different parameters that allow them to behave differently:

EU-AWS

CREATE view accounts OPTIONS (UPDATABLE 'false', materialized 'true', "teiid_rel: MATVIEW_TTL" 3600000, "teiid_rel: ALLOW_MATVIEW_MANAGEMENT" 'true') as select account_id, account_type, account_branch from vdb.SH.accounts;

US-GCP

CREATE view accounts OPTIONS (UPDATABLE 'false') as select account_id, account_type, account_branch from vdb.SH.accounts;

CA-AZR

CREATE view accounts OPTIONS (UPDATABLE 'false') as select account_id, account_type, account_branch from vdb.SH.accounts;

The PIP Configuration settings allow users to receive clearer error messages to the UI when managing common and specific PAA queries:

See PIP Caching for more information on Virtual Views.

PIP Configuration Settings

Users can configure DDLs, Models, Connection Settings, and Views without disrupting ongoing operations or configurations by toggling the PIP Configuration Sync Off. This toggle setting improves control and maintenance capabilities for the PAA.

When the toggle is on, any changes made in the PIP settings are automatically deployed to the PIP Operator and any changes made affect the ongoing operations and configurations.

When the toggle is off, the PIP goes into Configuration Sync mode. This mode allows users to make changes to the PIP settings without impacting ongoing operations or configurations while the previously saved PIP settings continue to function. Any changes made to the PIP settings are deployed after toggling the PIP Configuration Sync on and saving changes.

When a new PAA is added, the PIP Configuration Sync turns off so that the user can manage the connection settings for the newly added PAA.

Guidelines for PIP Settings

Users must ensure that they follow the steps for the PAA views above (see Creating Specific PAA View Queries) to avoid errors and out-of-sync PIP Settings that appear in the PAA Agent services.

Rules for PIP Settings
When configuring the common and specific PAA views, ensure that:

• A View does not appear more than once in the common Views query input field.

• A View does not appear more than once in a specific View query input field

• A View does not appear in the common View and specific View query input fields.

• A specific PAA view is defined (named) in the rest of the PAAs in the group, including view names and columns, with their appropriate behavior.

• Only View names are considered in the validation. PlainID does not validate the column/fields Views.

• The PAA properties based on the models from the Data Source are defined, then toggle the PIP Configuration Sync on, and save.

• New Data Sources are updated across all PAAs before turning on the PIP Configuration Sync for the modified PAAs

If validation fails, users will receive a prompt with a list of issues to fix before attempting to validate again.

Limitations for PIP Settings
Users should be aware of the following limitations when working with PIP Settings.

• If a user introduces a View from a Data Source, they must ensure that the View is properly configured on the specific PAA or in the common Views query field.

• Procedure/function name duplication is not validated. It is validated by Teiid when deploying changes to the PIP Operator.

See PAA Health Monitoring for more information.