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.

Background information

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.

Scenarios

You 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.

logical view

Create a logical view

Syntax for creating a logical view:

CREATE VIEW <view_name> AS <query_statement>;
Item Description
CREATE VIEW The syntax prefix for creating a logical view.
view_name The name of the logical view.
query_statement The SELECT statement.
  • You can use Database name.Table name to reference a database and a table that have been imported to the current logical data warehouse. Example:
    CREATE VIEW joined_view AS SELECT tb1.id AS id, tb2.name AS name FROM mydb.tb1 JOIN mydb.tb2 ON tb1.id = tb2.id;
  • Cascading definitions are supported. You can use lv.Logical view name to reference a defined logical view. Example:
    CREATE VIEW filtered_view AS SELECT * FROM lv.joined_VIEW WHERE id > 100;
  • Join queries are supported. You can perform join queries by joining a defined logical view with an imported table. Example:
    CREATE VIEW filtered_view2 AS SELECT * FROM lv.joined_view v1 JOIN mydb.tb1 t1 ON v1.id = t1.id where t1.id>100;

Query a logical view

All 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 lv..

For example, to query a logical view named filtered_view, use the following query statement:

SELECT * FROM lv.filtered_view;

Delete a logical view

When you delete a logical view, you do not need to prefix the view name with lv.. Example:
DROP VIEW view_name;

View all logical views

You can use the following statement to query all defined logical views:
SHOW VIEWS;

Others

  • 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:
    REFRESH;
  • View imported databases: You can use the following statement to display all databases imported to the current logical data warehouse:
    SHOW CATALOGS;

Materialized view

Create a materialized view

To 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>);
Item Description
CREATE M VIEW The syntax prefix for creating a materialized view. M is short for materialized.
view_name The name of the materialized view.
ON TABLE 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: mydb.target_table.
query_statement The SELECT statement.
WITH Specifies the extension configuration.
  • Create a DTS task that continuously runs to migrate incremental data.

    A DTS migration task that is created by default cannot be used to migrate incremental data. If you need to create a DTS task that continuously runs to migrate incremental data, use the WITH ('inc'='true') clause. Example:

    CREATE M VIEW mv04264 ON TABLE myadb.test04264
    AS SELECT * FROM rds1.mytable
    WITH('inc'='true');
    Note
    • A DTS migration task that is created by default cannot be used to migrate incremental data and does not generate costs.
    • A DTS task that continuously runs to migrate incremental data generates costs. The created data migration instance is of the small type and uses the pay-as-you-go billing method.

      For more information about the pricing of DTS data migration instances, see Pricing.

  • Configure the primary key for the destination table.

    For example, WITH ('pk'='id') specifies that the ID column in the specified destination table is used as the primary key.

Example:

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 views

You can use the following statement to query all defined materialized views and their statuses:
SHOW M VIEWS;

Query a materialized view

All 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 mv..

For example, to query a materialized view named view_name, use the following query statement:

SELECT * FROM mv.view_name;
Note If the destination table is the mytable table in the mydb2 database, the preceding query statement is automatically converted to the following query statement for querying the destination table: SELECT * FROM mydb2.mytable;.

Deletes a materialized view

To delete a materialized view, use the following statement. You do not need to prefix the view name with mv..
DROP M VIEW view_name;

Operators

Logical operators

Operator Description Example
AND Logical AND a > 1 AND b < 10
OR Logical OR a > 1 OR b < 10
NOT Logical NOT NOT(a > 1)
IS NULL Specifies whether the value is NULL. a IS NULL
IS NOT NULL Specifies whether the value is not NULL. a IS NOT NULL
> Greater than -
< Less than -
= Equal to -
>= Greater than or equal to -
<= Less than or equal to -
<> Not equal to -

Numeric operators

Operator Description Example
+ Addition -
- Subtraction -
* Multiplication -
/ Division -

Functions

Numeric functions

Function Description Example
MOD Returns the remainder of a number divided by another number. MOD(x, 2)
POW Returns the base to the exponent power. POW(x, y)
SQRT Returns the square root of the given number. SQRT(x)

String functions

Function Description Example
REPLACE Replaces specific content in a string with new content. REPLACE(name, 'old', 'new')
MD5 Calculates the message-digest algorithm 5 (MD5) hash of a string. MD5(name)
|| Concatenates strings. name1 || name2
TRIM Removes specific characters from both ends of a string. TRIM('a' FROM name)
LOWER Converts all uppercase letters in a string to lowercase letters. LOWER('aBc')
UPPER Converts all lowercase letters in a string to uppercase letters. UPPER('abc')
CHAR_LENGTH Calculates the length of a string. CHAR_LENGTH('abc')

Aggregate functions

Aggregate functions are used in GROUP BY clauses.

Function Description Example
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. -