Managing Views

Virtual Views in PlainID allow users to define customized views of their data without modifying the underlying data source or schema. Users can create logical, flat representations of their data by selecting and organizing specific columns from one or more Data Sources. These Views behave like virtual tables that contain rows and columns similar to real database tables or other View models.

Views can include expressions, aggregated columns, or transformations, and support data manipulations such as combining multiple columns, casting data types, and using SQL functions. This allows customers to create logical data representations that best fit the templates needed for Policies, while maintaining the integrity of the original data sources.

Additionally, Views support advanced SQL-like logic such as JOIN, WHERE, CAST, and CONCAT, making it possible to connect models from multiple Data Sources, filter, and transform data as needed.

Views created in the PIP settings can be used as Assets or Identities information points. They can contain data from one or multiple Data Sources, and can include functions, JOIN statements, and WHERE clauses as if the data were from a single table.

For more information on using Virtual Views, see Caching Views.

Creating Common View Queries

To create or update a View, click Manage in the PIP settings.
Note: At least one Data Source must exist before you can create Views.

In the query field, you can write DDL commands to create Views, define columns, and link them to Data Sources using the AS SELECT phrase. Users can write up to 4MB of DDL content for larger schemas.

General Structure

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

<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>]*

When creating Views, you can use SQL commands to define how data is projected from the Data Sources. Examples:

Direct View:
```
select * from DS
```
Subset of fields:
```
select a, b, c from DS
```

Manipulated fields:

Concatenate two fields:
```
CONCAT(field_A, field_B) as AB
```

Concatenate using ||:

(field_A || '-' || field_B || '-' || field_C) as ABC

Conditional transformation:

select first_name, last_name, case when location = 'California' then 'CA' else location end from bank_users

Join multiple tables:

select bu.first_name, bu.last_name, bc.project_id 
from bank_users bu 
join bank_context bc on bu.uid = bc.uid

You can add comments in the DDL to improve readability:

Single-line comment: -- comment
Multi-line comment:
```
/*
multi-line
comment
*/
```

Example

Creating Specific PAA View Queries

Specific View queries enable users to define customized queries for individual PAAs within a group, allowing for unique behavior across environments.

To create a specific PAA View query:

Go to the Views tab in the PIP settings.
Under the Common Query field, select Advanced to reveal the specific PAAs.
Verify that the specific View query does not exist in the Common Query field.
Locate the desired PAA and input the specific View query in its field.
Replicate the query across other PAAs with variations only where behavior differs.

Example

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;

Warning

Using different View names in specific View fields causes schema inconsistencies.

See Caching Views for more information on Virtual Views.

PIP Configuration Settings

The PIP Configuration Sync toggle allows users to configure DDLs, Models, Connection Settings, and Views without disrupting active operations.

When on, changes in the PIP settings are automatically deployed to the PIP Operator and take immediate effect.
When off, the PIP enters Configuration Sync mode, allowing safe editing of settings without affecting active configurations. Changes are applied after toggling Sync back on and saving.

When a new PAA is added, the toggle is automatically turned off so users can adjust its connection settings.

Guidelines for PIP Settings

To prevent errors and maintain synchronization across PAAs, follow these rules:

A View must not appear more than once in the common or specific View query input fields.
A View must not appear in both the common and specific View input fields.
Specific PAA Views must be defined across all PAAs in the group with matching names and columns.
Only View names are validated. Columns and fields are not validated.
Update all PAAs when new Data Sources are added before turning on Configuration Sync.

If validation fails, the system displays a list of issues to resolve before re-validating.

Limitations

If a View references a Data Source, ensure it is configured either in the specific PAA or in the common query field.
Procedure or function name duplication is not validated by PlainID but by Teiid during deployment.

See PAA Health Monitoring for more details.