Data Management (DMS) provides the logical data warehouse feature that allows you to query and synchronize data across databases based on the unified SQL syntax. The databases may run on different engines, reside in different regions, and belong to different database instances and storage systems such as online transaction processing (OLTP) and online analytical processing (OLAP) systems. This topic describes the SQL syntax supported by logical data warehouses.
The SQL syntax for defining and querying logical and materialized views is extended based on the Calcite syntax. The SQL syntax for querying views is the same as the Calcite syntax. For more information about the Calcite syntax, see Calcite SQL language.
ScenariosYou can use logical data warehouses to analyze and synchronize data in an agile manner.
- If you need to perform quick analysis on business data, you can define a logical view based on the raw data, such as a MySQL database or table, and query the logical view. If you need to perform further analysis based on the original analysis results, you can define another logical view based on the original logical view.
- The materialized view feature is based on data synchronization. When a materialized view is defined, a data synchronization task is also created. The data synchronization task can synchronize the query results to the destination table. The destination table contains the full data that is initially written to the table and the incremental data that is continuously written to the table. You can configure the incremental data synchronization settings based on your requirements. The real-time data synchronization feature is available only for single-table queries.
Create a logical view
Syntax for creating a logical view:
CREATE VIEW <view_name> AS <query_statement>;
||The syntax prefix for creating a logical view.|
||The name of the logical view.|
||The SELECT statement.
Query a logical viewAll logical views are stored in a schema named
lv. Therefore, if you need to query a logical view, you must prefix the view name with
For example, to query a logical view named
filtered_view, use the following query statement:
SELECT * FROM lv.filtered_view;
Delete a logical viewWhen you delete a logical view, you do not need to prefix the view name with
DROP VIEW view_name;
View all logical viewsYou can use the following statement to query all defined logical views:
- Refresh metadata: If a DDL operation is performed on an imported table, the metadata
change may not be synchronized to the logical data warehouse at the earliest opportunity.
In this case, you can use the following statement to refresh the metadata:
- View imported databases: You can use the following statement to display all databases
imported to the current logical data warehouse:
Create a materialized viewTo define a materialized view, you must specify the information about the destination table for which the materialized view is created. Syntax for creating a materialized view:
CREATE M VIEW <view_name> ON TABLE <target_db>.<target_table> AS <query_statement> WITH(<parameters>);
||The syntax prefix for creating a materialized view. M is short for materialized.|
||The name of the materialized view.|
||Specifies the destination table for which the materialized view is created. The destination
table must be a table that has been imported to the logical data warehouse. Example:
||The SELECT statement.|
||Specifies the extension configuration.
CREATE M VIEW mymv ON TABLE mydb2.mytable AS SELECT id, name, MD5(name) AS name_md5 FROM mydb1.mytable WHERE id > 100 WITH('pk'='id');
View all materialized viewsYou can use the following statement to query all defined materialized views and their statuses:
SHOW M VIEWS;
Query a materialized viewAll materialized views are stored in a schema named
mv. Therefore, if you need to query a materialized view, you must prefix the view name with
For example, to query a materialized view named
view_name, use the following query statement:
SELECT * FROM mv.view_name;
SELECT * FROM mydb2.mytable;.
Deletes a materialized viewTo delete a materialized view, use the following statement. You do not need to prefix the view name with
DROP M VIEW view_name;
|IS NULL||Specifies whether the value is NULL.||
|IS NOT NULL||Specifies whether the value is not NULL.||
|>=||Greater than or equal to||-|
|<=||Less than or equal to||-|
|<>||Not equal to||-|
|MOD||Returns the remainder of a number divided by another number.||
|POW||Returns the base to the exponent power.||
|SQRT||Returns the square root of the given number.||
|REPLACE||Replaces specific content in a string with new content.||
|MD5||Calculates the message-digest algorithm 5 (MD5) hash of a string.||
|TRIM||Removes specific characters from both ends of a string.||
|LOWER||Converts all uppercase letters in a string to lowercase letters.||
|UPPER||Converts all lowercase letters in a string to uppercase letters.||
|CHAR_LENGTH||Calculates the length of a string.||
Aggregate functions are used in GROUP BY clauses.
|COUNT(*)||Returns the number of rows.||-|
|AVG(numeric)||Returns the average value.||-|
|SUM(numeric)||Returns the sum of all input values.||-|
|MAX(numeric)||Returns the maximum value among all input values.||-|
|MIN(numeric)||Returns the minimum value among all input values.||-|