SQL syntax supported by logical data warehouses-Data Management-阿里云帮助中心

The logical data warehouse feature breaks down the barriers between online analytical processing (OLAP) and online transaction processing (TP) systems. These systems often use different engines, instances, regions, and storage. This feature provides cross-database data query and data synchronization capabilities based on a unified SQL syntax. This topic describes the SQL syntax supported by logical data warehouses.

Background information

The query syntax and functions for logical data warehouses are mostly compatible with MySQL. Therefore, you can write query statements and functions using MySQL syntax. The syntax is also extended to support the creation and deletion of logical views.

For more information, see Core features.

Scenarios

To quickly analyze your business data, you can define a logical view over raw data, such as tables in a MySQL database, and query it directly. For further analysis, you can also define new logical views that cascade from the existing view.

Logical views

Create a logical view

The syntax for creating a logical view is as follows:

CREATE VIEW <db_name>.<view_name> AS <query_statement>;

Item	Description
`db_name`	The name of the DBLink.
`view_name`	The custom name of the logical view.
`query_statement`	The SELECT statement: Reference an imported table in the logical data warehouse using `dblink.schema.table`. For example: `CREATE VIEW public.joined_view AS SELECT tb1.id AS id, tb2.name AS name FROM dblink_order.db1.tb1 JOIN dblink_product.product_db.tb2 ON tb1.id = tb2.id;` Cascading definitions are supported. Reference other defined logical views using `public.viewname`. For example: `CREATE VIEW public.filtered_view AS SELECT * FROM public.joined_VIEW WHERE id > 100;` Join queries are supported. Join other defined logical views with imported tables. For example: `CREATE VIEW filtered_view2 AS SELECT * FROM public.joined_view v1 JOIN dblink_order.db1.tb1 t1 ON v1.id = t1.id where t1.id>100;`

Query a logical view

All logical views are saved in a public schema named public. Therefore, you must prefix the view name with public. when you query a logical view.

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

SELECT * FROM public.filtered_view;

Delete a logical view

The syntax for deleting a logical view is as follows:

DROP VIEW public.view_name;

View all logical views

To view all logical views in the public database, use the following syntax:

SHOW VIEWS public;

Other

You can view the list of virtual databases.

To view all created virtual databases, including the public database, use the following syntax:

SHOW CATALOGS;

Operators

Logical operators

Function	Description	Example
AND	Logical AND	`a > 1 AND b < 10`
OR	Logical OR	`a > 1 OR b < 10`
NOT	Logical NOT	`NOT(a > 1)`

Comparison operators

Function	Description	Example
>	Greater than.	`a > 1`
<	Less than.	`a < 1`
=	Equal to.	`a = 1`
>=	Greater than or equal to.	`a >= 1`
<=	Less than or equal to.	`a <= 1`
<>	Not equal to.	`a <> 1`
!=	Not equal to.	`a != 1`
is null	Checks if a value is null.	`a is null`
is not null	Checks if a value is not null.	`a is not null`
is distinct from	A null-safe operator that compares two values. It avoids null results and always returns true or false.	`select null is distinct from null`
is not distinct from	A null-safe operator that compares two values. It avoids null results and always returns true or false.	`select null is not distinct from null`
greatest(value1, value2, .., valuen)	Returns the greatest value from a list of values.	-
least(value1, value2, .., valuen)	Returns the maximum value.	-
all	Compares a single value with the return values of a subquery.	`select 21 < all (values 19, 20, 21)`
any/some	Compares a single value with the return values of a subquery.	`select 'hello' = any (values 'hello', 'world')`
like	Searches for a specified pattern in a column within a WHERE clause.	`select * from (values ('abc'), ('bcd'), ('cde')) as t (name) where name like '_b%'` Note `%` represents zero, one, or multiple characters. `_` represents a single character.

Numeric operators

Function	Description
+	Addition
-	Subtraction
*	Multiplication
/	Division

Functions

Mathematical functions

Function	Return value type	Description	Example
mod(x)	Same as the type of x by default	Modulo operation.	`mod(101, 4)`
pow(x,y)	Same as the type of x by default	Exponentiation.	`pow(3, 2)`
sqrt(x)	Same as the type of x by default	Square root.	`sqrt(9)`
abs(x)	Same as the type of x by default	Absolute value.	`abs(3)`
cbrt(x)	double	Cube root.	`cbrt(3)`
ceil(x)	-	Alias for the ceiling function.	-
ceiling(x)	Same as the type of x by default	Returns the smallest integer that is greater than the specified numeric expression.	`ceiling(5.5)`, returns 6.
degrees(x)	double	Converts radians to degrees.	`degrees(1)`, returns 57.
e(x)	double	Returns Euler's constant.	-
exp(x)	double	Returns Euler's constant raised to the power of x.	-
floor(x)	Same as the type of x by default	Returns the largest integer that is smaller than x.	`floor(5.5`), returns 5.
from_base(string, radix)	bigint	Converts the value of a string into a base-radix number.	-
ln(x)	double	Returns the natural logarithm of x.	-
log2(x)	double	Returns the base-2 logarithm of x.	`log2(2)`, returns 1.
log10(x)	double	Returns the base-10 logarithm of x.	`log10(10)`, returns 1.
pi()	-	Returns the constant pi.	-
power(x, p)	double	Returns x raised to the power of p.	-
rand()	-	Alias for the random function.	-
random()	double	Returns a pseudo-random value in the range of `[0,1.0)`.	-
secure_rand()	-	Alias for the secure_random function.	-
secure_random()	double	Returns a secure, encrypted random value in the range of `[0, 1.0)`.	-
secure_random(lower, upper)	-	Returns an encrypted, secure random value in the range of `[lower, upper)`.	-
round(x)	Same as the type of x by default	Returns x rounded to the nearest integer.	`round(3.5)`, returns 4.
round(x, d)	Same as the type of x by default	Returns x rounded to d decimal places.	`round(3.14159, 2)`, returns 3.14.
sign(x)	Same as the type of x by default	Returns the sign of x. When x is positive, it returns 1. When x is 0, it returns 0. When x is negative, it returns -1. When x is NaN, it returns NaN.	`sign(1)`, returns 1.
to_base(x, radix)	varchar	Returns the string representation of x in base-radix.	-
truncate(x)	double	Returns x with the decimal part truncated.	truncate(2.5), returns 2. truncate(-2.5), returns -2.
truncate(x, n)	double	Returns x truncated to n decimal places. n can be a negative number to truncate n digits to the left of the decimal point.	truncate(REAL '12.333', -1), returns 10.0. truncate(REAL '12.333', 0), returns 12.0. truncate(REAL '12.333', 1), returns 12.3.
acos(x)	double	Returns the arc cosine of x.	-
asin(x)	double	Returns the arc sine of x.	-
atan(x)	double	Returns the arc tangent of x.	-
atan2(y, x)	double	Returns the arc tangent of y/x.	-
cos(x)	double	Returns the cosine of x.	-
cosh(x)	double	Returns the hyperbolic cosine of x.	-
sin(x)	double	Returns the sine of x.	-
tan(x)	double	Returns the tangent of x.	-
tanh(x)	double	Returns the hyperbolic tangent of x.	-
infinity()	double	Returns the constant that represents positive infinity.	-
is_finite(x)	boolean	Determines whether x is a finite value.	-
is_infinite(x)	boolean	Determines whether x is an infinite value.	-
is_nan(x)	boolean	Determines whether x is not a number (NaN).	-
nan()	double	Returns the constant that represents not a number (NaN).	-

String functions

Function	Return value type	Description	Example
replace	varchar	Replace	`replace(name, 'old', 'new')`
md5	varchar	MD5 hash	`md5(name)`
\|\|	varchar	String concatenation	`name1 \|\| name2`
trim	varchar	Removes leading and trailing characters	`trim('a' FROM name)`
lower	varchar	Converts to lowercase	`lower('aBc')`
upper	varchar	Converts to uppercase	`upper('abc')`
char_length	bigint	String length	`char_length('abc')`
chr(n)	varchar	Returns the Unicode character at code point n as a single-character string.	-
codepoint(string)	integer	Returns the Unicode code point of the single character in the string.	-
concat(string1, ..., stringN)	varchar	A string concatenation function that connects two or more strings to form a new string.	`concat('hello', ' ', 'world')`, concatenates to 'hello world'.
hamming_distance(string1, string2)	bigint	Returns the Hamming distance between string1 and string2, which is the number of positions at which the corresponding characters are different. Note The two strings must have the same length.	-
left(string, length)	varchar	Returns a string that consists of the leftmost 'length' characters of the string.	-
length(string)	bigint	Returns the length of the string.	-
levenshtein_distance(string1, string2)	bigint	Returns the Levenshtein edit distance between string1 and string2. This is the minimum number of single-character edits (insertions, deletions, or substitutions) required to change string1 into string2.	-
lower(string)	varchar	Converts the string to lowercase.	`lower('ABC')`, returns 'abc'.
lpad(string, size, padstring)	varchar	Pads the left side of the string with padstring to make the string 'size' characters long. If 'size' is smaller than the length of the string, the result is truncated to 'size' characters. 'size' cannot be negative, and padstring must not be empty.	-
ltrim(string)	varchar	Removes leading spaces from the string.	-
replace(string, search)	varchar	Removes all occurrences of 'search' from the string.	-
replace(string, search, replace)	varchar	Replaces all occurrences of 'search' in the string with 'replace'.	`replace('hello old', 'old', 'new')`, returns 'hello new'.
reverse(string)	varchar	Returns the reversed string.	`reverse('abc')`, returns 'cba'.
rpad(string, size, padstring)	varchar	Pads the right side of the string with padstring to make the string 'size' characters long. If 'size' is smaller than the length of the string, the result is truncated to 'size' characters. 'size' cannot be negative, and padstring must not be empty.	-
rtrim(string)	varchar	Removes trailing spaces from the string.	-
split(string, delimiter)	-	Splits the string by the delimiter and returns an array.	-
split(string, delimiter, limit)	-	Splits the string by the delimiter and returns an array with a maximum size of 'limit'.	-
strpos(string, substring)	bigint	Returns the starting position of the first occurrence of substring in the string. Traversal starts from the first character. If not found, it returns 0.	-
strpos(string, substring, instance)	bigint	Returns the position of the nth occurrence of substring in the string. 'instance' must be a positive number. Traversal starts from the first character. If not found, it returns 0.	-
strpos(string, substring)	bigint	Returns the starting position of the last occurrence of substring in the string. Traversal starts from the first character. If not found, it returns 0.	-
strpos(string, substring, instance)	bigint	Returns the position of the nth occurrence of substring from the end of the string. 'instance' must be a positive number. The position is counted from the beginning of the string. If not found, it returns 0.	-
position(substring IN string)	bigint	Returns the starting position of the first occurrence of substring in the string. Traversal starts from the first character. If not found, it returns 0.	-
substring_index(str, delim, count)	varchar	Returns the substring from the string 'str' before 'count' occurrences of the delimiter 'delim'.	`substring_index('www.mysql.com', '.', 2)`
substr	-	An alias for the substring function.	-
substring(string FROM start)	varchar	Returns the substring of the string starting from the 'start' position.	`substring('foobarbar' FROM 4)`, returns barbar.
substring(string, start)	varchar	Returns the substring of the string starting from the 'start' position.	-
substring(string, start, length)	varchar	Returns a substring of 'length' characters from the string, starting at the 'start' position.	-
trim(string)	varchar	Removes leading and trailing spaces from the string.	-
upper(string)	varchar	Converts lowercase letters in the string to uppercase letters.	-
to_utf8(string)	varchar	Encodes the string into a UTF-8 binary representation.	-
from_utf8(binary)	varchar	Decodes the binary string from UTF-8 format. Invalid UTF-8 characters are replaced with the default character U+FFFD.	-
from_utf8(binary, replace)	varchar	Decodes the binary string from UTF-8 format. Invalid UTF-8 characters are replaced with a custom string.	-
key_sampling_percent(Varchar)	varchar	Generates a double value between 0 and 1.0 based on the hash of the given string.	-

Conditional expressions

Standard CASE expressions have two forms.

This form iterates through each value. When a value matches the expression, the statement returns the corresponding result. Otherwise, it returns the result from the ELSE branch.
```
CASE expression
     WHEN value1 THEN result1
     [WHEN value2 THEN result2]
     [ELSE result3]
END
```

This form iterates through each condition and returns the result for the first condition that evaluates to true.

CASE WHEN condition1 THEN result1
     [WHEN condition2 THEN result2]
     [ELSE result3]
END

Function	Description	Example
if(condition, true_value)	If the condition is true, it returns true_value. Otherwise, it returns NULL.	`if(true, 1)`, returns 1. `if(false, 1), returns NULL.`
if(condition, true_value, false_value)	If the condition is true, it returns true_value. Otherwise, it returns false_value.	`if(true, 1, 2)`, returns 1. `if(false, 1, 2)`, returns 2.
coalesce(value1, value2[, ...])	Returns the first non-NULL value.	`coalesce(NULL, NULL, 1)`, returns 1.
ifnull(expr1, expr2)	If expr1 is not NULL, it returns expr1. Otherwise, it returns expr2.	`ifnull(1,0)`, returns 1. `ifnull(NULL,10)`, returns 10.
nullif(expr1, expr2)	If expr1 is equal to expr2, it returns NULL. Otherwise, it returns expr1.	-
try(expression)	Returns NULL if the expression has a syntax error. It can identify three types of errors: Division by zero Failed type conversion (cast) or invalid function parameters Numeric value out of range	`coalesce(try(total_cost / packages), 0)`

Other functions

Function	Return value type	Description	Example
json_extract(json, json_path)	-	Returns the value specified by json_path from a JSON string. The return value is of the JSON type.	`SELECT json_extract(json, '$.store.book');`
regexp_like(string, pattern)	boolean	Evaluates the regular expression 'pattern' and determines if it is contained within the string.	`SELECT regexp_like('1a 2b 14m', '\d+b');`