CREATE VIEW - AnalyticDB - Alibaba Cloud Documentation Center

A view is a virtual table that consists of the results of a query from one or more tables but does not store actual data. Views can simplify complex queries and enhance data security. This topic describes how to execute the CREATE VIEW statement to create a view.

Usage notes

Compatibility of views between AnalyticDB for MySQL and MySQL:

Versions earlier than 3.1.9.0
AnalyticDB for MySQL is not compatible with the default behavior of MySQL. If a column is added to or removed from your view, the system identifies the change in the number of columns from the response of the SELECT * FROM <view_name>; statement, determines the view to be unavailable, and then reports the following error: View '<view_name>' is stale; it must be re-created.
V3.1.9.0 and later
AnalyticDB for MySQL is compatible with the default behavior of MySQL. When you use the CREATE VIEW statement to create a view, AnalyticDB for MySQL saves the statement after parsing the asterisk (*) in the statement into specific columns. In this case, no error occurs when you add or remove a column.

We recommend that you use AnalyticDB for MySQL clusters of V3.1.9.0 or later to prevent unexpected errors. We recommend that you use specific column names but not the asterisk (*) in the CREATE VIEW statement.

Note

To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.

The compatibility of AnalyticDB for MySQL clusters of V3.1.9.0 or later with MySQL behavior may cause special impacts. For example, when you rename Column C as Column D, the system fails to find Column C when it references Column A, Column B, and Column C, and then reports an error. This is an expected result because the parsing stage in which SQL syntax check and authentication are performed is earlier than the optimization stage in which columns are removed. The system reports an error in the parsing stage regardless of whether Column C is used for query. In AnalyticDB for MySQL of versions earlier than 3.1.9.0, the system only checks whether the number of columns in a view is the same as the number of columns that are referenced by the asterisk (*). In this case, no errors are reported, and the system maps Column C to the third column of the base table. When you query Column C after the RENAME operation, no errors are reported. This is an unexpected result.

If your business has requirements for compatibility with the special behavior of AnalyticDB for MySQL clusters of versions earlier than 3.1.9.0, you can add a hint to the CREATE VIEW statement or configure a specific global parameter.

Add the following hint to the CREATE VIEW statement for a single view: /*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/. Sample statement:
```
/*+LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE=false*/
CREATE VIEW v0
AS
SELECT * FROM base0;
```
Configure the following global parameter to apply the required setting for all views: SET ADB_CONFIG LOG_VIEW_SELECT_ASTERISK_MYSQL_MODE = false;.

Syntax

CREATE
[OR REPLACE]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name
AS select_statement;

Option	Required	Description
`OR REPLACE`	No	Creates a view based on whether an existing view uses the same name. The following rules apply: If no existing view uses the same name, AnalyticDB for MySQL creates a view. If an existing view uses the same name, AnalyticDB for MySQL deletes the existing view and creates a view. Note If this option is not specified, no view can be created when an existing view uses the same name.
`[SQL SECURITY]`	No	Defines the method for security authentication when data is queried from a view. Valid values: `INVOKER`: executes an SQL statement by using the permissions of an `invoker`. This authentication method allows the system to check whether the invoker has the following permissions when data is queried from a view: Permissions to query views Permissions to query the objects that are referenced by views Only an invoker that has the preceding permissions can query data from a view. `DEFINER`: executes an SQL statement by using the permissions of a `definer`. This authentication method allows the system to check whether the invoker and the definer have the following permissions when data is queried from a view: Permissions to query views (by the invoker) Permissions to query the objects that are referenced by views (by the definer) After permissions of the definer are revoked, data cannot be queried from views even if the invoker has permissions to query views. Note If this option is not specified, `INVOKER` is used as the default authentication method in AnalyticDB for MySQL. When an invoker queries data from a view, the invoker must have the permissions to query views and the objects that are referenced by views. This option is available for AnalyticDB for MySQL clusters of V3.1.4.0 or later. For information about how to query the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster? To update the minor version of a cluster, contact technical support.
`view_name`	Yes	The name of the view. Note When you name a view, you can also add the name of the database that contains the view before the name of the view. Example: `adb_demo.view`. If you do not specify a database, the view is created in the current database.
`select_statement`	Yes	The data source of the view.

Examples

Prepare data

Perform the following operations by using the privileged account of an AnalyticDB for MySQL cluster:

Create an account named user1.

CREATE USER user1 IDENTIFIED BY 'user1_pwd';

Create a table named t1 in an existing database named adb_demo.

Create Table `t1` (
 `id` bigint AUTO_INCREMENT,
 `id_province` bigint NOT NULL,
 `user_info` varchar,
 primary key (`id`)
) DISTRIBUTED BY HASH(`id`);

Insert test data into the t1 table.

INSERT INTO t1(id_province,user_info) VALUES (1,'Tom'),(1,'Jerry'),(2,'Jerry'),(3,'Mark');

Create views
Note
The following examples show how to specify different authentication methods when you create views. The data in the views comes from the t1 table.
- Create a view named v1 with SQL SECURITY set to INVOKER.
```
CREATE SQL SECURITY INVOKER VIEW v1 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- Create a view named v2 with SQL SECURITY set to DEFINER.
```
CREATE SQL SECURITY DEFINER VIEW v2 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
- Create a view named v3 and do not specify the SQL SECURITY option. In this case, the INVOKER authentication method is used.
```
CREATE VIEW v3 
  AS SELECT id_province,user_info FROM t1 WHERE id_province=1;
```
Compare permissions
- Use the privileged account to grant the user1 account the permissions to query the three views.
```
GRANT SELECT ON adb_demo.v1 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v2 TO 'user1'@'%';
GRANT SELECT ON adb_demo.v3 TO 'user1'@'%';
```
  In this case, after you use the user1 account to connect to the adb_demo database of the AnalyticDB for MySQL cluster, you can use the user1 account to query data only from the v2 view.
```
SELECT * FROM v2
```
  The following result is returned:
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```
  When you query data from the v1 or v3 view by using the SELECT statement, an error is returned.
```
SELECT * FROM v1
```
  or
```
SELECT * FROM v3
```
  The following error message is returned when you execute one of the preceding SELECT statements:
```
ERROR 1815 (HY000): [20049, 2021083110261019216818804803453927668] : Failed analyzing stored view
```
- After the user1 account is granted the permissions to query the three views, use the privileged account to grant the user1 account the permissions to query the t1 table.
```
GRANT SELECT ON adb_demo.t1 to user1@'%';
```
  In this case, after you use the user1 account to connect to the adb_demo database of the AnalyticDB for MySQL cluster, you can use the user1 account to query data from the v1, v2, and v3 views.
```
SELECT * FROM v1
```
  or
```
SELECT * FROM v2
```
  or
```
SELECT * FROM v3
```
  The following result is returned when you execute one of the preceding SELECT statements:
```
+-------------+-----------+
| ID_PROVINCE | USER_INFO |
+-------------+-----------+
|           1 | Tom       |
|           1 | Jerry     |
+-------------+-----------+
```

Best practices

For more information, see Permission management by using views.