# Logical Views

A logical view is a virtual table defined by a SQL query. Unlike a physical table, a logical view does not store data. Instead, it dynamically computes results from underlying tables each time the view is queried.

Logical views are currently available for Trino only. Hive support is planned for a future release.

## Benefits and Use Cases

Logical views provide several key benefits:

- **Query Reusability**: Define complex queries once and reuse them across multiple analyses
- **Reduced Change Amplification**: Update data transformations in one place instead of modifying multiple queries
- **Virtual Data Marts**: Create logical data structures without duplicating data
- **BI Tool Compatibility**: Convert Treasure Data native types to BI tool-compatible types (e.g., timestamp)


### Common Use Cases

**BI Tool Timestamp Compatibility**: PlazmaDB tables store timestamps as Unix time (integer) or strings. Create logical views with timestamp columns to allow BI tools to directly use timestamp fields without custom calculations.

**Event Log Transformation**: Transform event logs (such as Email Open/Bounce/Click events from Engage Studio) into aggregated metrics by customer or email address for easier analysis.

**Simplify Complex Queries**: Encapsulate common filtering, aggregation, or joining logic in views for data analysts to query directly.

**Data Masking and Security**: Use DEFINER mode to provide controlled access to specific columns or filtered rows without granting direct table access to users.

## Creating Logical Views

Logical views are created using SQL CREATE VIEW statements in the Data Workbench query editor.

### Using Trino SQL


```sql
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

-- With security mode specification
CREATE VIEW view_name
SECURITY DEFINER  -- or INVOKER
AS
SELECT * FROM table_name;
```

For further detail: [Trino create view syntax](https://trino.io/docs/423/sql/create-view.html)

### Examples

Create a view to filter columns:


```sql
CREATE VIEW region_without_comment AS
SELECT r_regionkey, r_name, r_dummy, time
FROM tpch_sf1.region;
```

Create a view with timestamp conversion:


```sql
CREATE VIEW orders_with_timestamp AS
SELECT
  order_id,
  customer_id,
  FROM_UNIXTIME(order_time) AS order_timestamp,
  amount
FROM orders;
```

Logical view queries can only be created and updated using SQL statements. However, you can edit view details (name, description, security mode) through the Data Workbench UI.

## Viewing and Querying Logical Views

### List Logical Views

In the UI, navigate to **Data Workbench** > **Databases** and select a database. Logical views are shown in the table list with a **view** type indicator.

![Logical views in table list](/assets/logical-view-table-list.785cdfb96192c886ccf89a6ed90780e00d722da802c5fc0d82d4ceabe87acdb0.4c190181.png)

Using SQL:


```sql
SELECT * FROM INFORMATION_SCHEMA.VIEWS;
```

### Query a Logical View

Query logical views just like regular tables using Trino:


```sql
SELECT * FROM region_without_comment WHERE r_name = 'ASIA';

SELECT * FROM customer_summary WHERE total_orders > 10;
```

### View Details

The view detail page shows:

- **Details**: Name, description, last updated, security mode, owner, created date, database
- **Records**: Preview data (select **Update Preview** to fetch up to 40 sample records)
- **Schema**: Column definitions and query text. Note that data types used in view schema are different from tables. For tables, [TD native data types](/products/customer-data-platform/data-workbench/databases/td-native-data-types) are used. For views, query engine data types are used (e.g., [Trino data types](https://trino.io/docs/423/language/types.html) for Trino).
- **Dependencies**: Tables and views referenced by this view


![Logical view details page](/assets/logical-view-details.c4dadffa2a5456e8a28bb8f1661b99f4b1036f34e6ccfb8273229f65ac6a42f6.4c190181.png)

The **Records** tab allows you to preview the view's data. Select **Update Preview** to fetch up to 40 sample records:

![Logical view preview](/assets/logical-view-preview.feb7fee1a60d573f253800cd9156232a781548cf894c373aa3699c2bfb6cc39f.4c190181.png)

## Managing Logical Views

### Update View Definition

To update a view definition, use the CREATE OR REPLACE VIEW statement:


```sql
CREATE OR REPLACE VIEW view_name AS
SELECT new_column1, new_column2
FROM table_name
WHERE new_condition;
```

When you use CREATE OR REPLACE VIEW, the view definition is completely replaced with the new query. All previous columns and logic are removed.

### Update Security Mode

You can change the security mode through the UI or SQL.

**Using the UI:**

1. Navigate to the view detail page
2. Select the menu (⋯) and choose **Edit Details**
3. In the **Edit Logical View Details** dialog:
  - Select **Definer** or **Invoker** under **Security Mode**
  - Optionally update the **Name** or **Description**
4. Select **Confirm**


![Edit Logical View Details dialog](/assets/logical-view-edit-details-dialog.e55c3c71ef4577f32e40badcd4c2eb9904ce516dcade6a1ff89fe06a9b57e635.4c190181.png)

**Using SQL:**

To change the security mode via SQL, use CREATE OR REPLACE VIEW with the new security mode specification:


```sql
-- Change to INVOKER mode
CREATE OR REPLACE VIEW view_name
SECURITY INVOKER
AS
SELECT * FROM table_name;

-- Change to DEFINER mode
CREATE OR REPLACE VIEW view_name
SECURITY DEFINER
AS
SELECT * FROM table_name;
```

When using CREATE OR REPLACE VIEW, you must also re-specify the complete query definition.

### Delete a Logical View

To delete a logical view, use the DROP VIEW statement:


```sql
DROP VIEW view_name;
```

Deleting a view does not delete underlying tables. If other views reference this view, they will fail when queried.

### Delegate View Ownership

The view owner is important when using DEFINER security mode, as queries execute with the owner's permissions.

Currently, ownership can only be changed via API. The target user must have Full Access permission on the database.

If a view owner is deleted and the view uses DEFINER mode, the view will no longer work.

## Permissions

Logical views inherit permissions from the database. The following operations are allowed based on database permission level:

| Database Permission | List/View | Create | Update | Delete | Query |
|  --- | --- | --- | --- | --- | --- |
| Full Access | ✓ | ✓ | ✓ | ✓ | ✓ |
| Manage Own | ✓ | ✓ | ✓ | ✓ | ✓ |
| General Access | ✓ | ✓ | ✓ | ✓ | ✓ |
| Query-only | ✓ | ✗ | ✗ | ✗ | ✓ |
| Import-only | ✓ | ✗ | ✗ | ✗ | ✗ |
| None | ✗ | ✗ | ✗ | ✗ | ✗ |


### Security Mode and Permissions

The [security mode](/products/customer-data-platform/data-workbench/databases/logical-view-security-modes) determines whose permissions are used to access referenced tables:

- **DEFINER mode** (default): Uses view owner's permissions
  - Query executors don't need permission to underlying tables
  - Useful for providing controlled access to sensitive data
- **INVOKER mode**: Uses query executor's permissions
  - Query executors must have permission to all underlying tables
  - Enforces user-level permissions


See [Logical View Security Modes](/products/customer-data-platform/data-workbench/databases/logical-view-security-modes) for detailed information.

## Limitations

### Capacity Limits

- Maximum 50 logical views per database
- Maximum 1,024 columns per logical view
- Maximum 256 KB for view definition query statement
- Maximum 10 levels of nested logical views


### Functional Limitations

- **Read-only**: DML operations (INSERT, UPDATE, DELETE) are not supported
- **Trino only**: Currently only Trino is supported; Hive support is planned for a future release
- **No cross-account queries**: Views cannot be used to reference tables of other accounts in Data Clean Room
- **No Parent Segment integration**: Logical views cannot be used as master tables or attribute tables in Parent Segments (support planned for a future release)
- **No column-level tags**: View columns cannot be annotated with tags for data classification
- **Schema stored at creation**: If underlying table columns change after view creation, the view schema is not automatically updated


### Column Permission Restrictions

If a user has a column permission with `except: false` (cannot view non-tagged columns), they cannot use logical views in queries, regardless of security mode.

### Lifecycle Independence

The lifecycle of a view and its referenced tables are independent:

- Referenced tables can be deleted at any time
- When a referenced table is deleted, queries against the view will fail
- The view query is validated by the query engine when created


## Query Engine Compatibility

- Currently, logical views are only supported for Trino
- Hive support is planned for a future release
- View names must be unique within a database
- SQL syntax may change when query engine versions are upgraded. If incompatible SQL syntax is used, views defined in old query engine versions may not work in a new query engine version. The view definition must be updated with the latest SQL syntax in that case.


## Best Practices

- **Use descriptive names**: Choose view names that clearly describe their purpose
- **Test before deploying**: Query the view to verify it returns expected results
- **Consider performance**: Complex views with multiple joins may impact query performance
- **Mind nesting depth**: Keep view nesting to a reasonable level (limit is 10 levels)
- **Use DEFINER mode for controlled access**: Allow Query-only users to access transformed data without granting table access
- **Monitor dependencies**: Check which views reference tables before deleting them