AnalyticDB for MySQL

https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/
Admin
AnalyticDB for MySQL is a cloud-native data warehouse that provides SQL-based analytics, real-time...
Tokens:1,259,250
Snippets:6,950
Trust Score:10
Update:2 days ago
Show doc for...
Tokens:
Raw
### Create tables item and store_sales for foreign key example

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/alter-table

Example CREATE TABLE statements for the 'item' and 'store_sales' tables used in the foreign key example.

```sql
CREATE TABLE item
(
 i_item_sk BIGINT NOT NULL,
 i_current_price BIGINT,
 PRIMARY KEY(i_item_sk)
)
DISTRIBUTED BY HASH(i_item_sk);
```

```sql
CREATE TABLE store_sales
(
 ss_sale_id BIGINT,
 ss_store_sk BIGINT,
 ss_item_sk BIGINT NOT NULL,
 PRIMARY KEY(ss_sale_id)
);
```

--------------------------------

### MAKEDATE example with string arguments

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/date-and-time-functions

Example query using MAKEDATE with VARCHAR arguments ('2022','31') and ('2022','32'), returning the same dates as the numeric example.

```sql
select makedate('2022','31'), makedate('2022','32');
```

```sql
+------------------------+------------------------+
| makedate('2022', '31') | makedate('2022', '32') |
+------------------------+------------------------+
| 2022-01-31             | 2022-02-01             |
```

--------------------------------

### SHOW CREATE TABLE example and result

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/show

Example of SHOW CREATE TABLE on table adb_demo.customer, followed by the returned result set.

```sql
SHOW CREATE TABLE adb_demo.customer;
```

```sql
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                    |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customer | Create Table `customer` (
 `id` int NOT NULL,
 `name` varchar(50),
 `address` varchar(80),
 `gender` boolean,
 primary key (`id`)
) DISTRIBUTED BY HASH(`id`) INDEX_ALL='Y' STORAGE_POLICY='HOT' BLOCK_SIZE=8192 |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

--------------------------------

### MID example with negative position

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example query using MID with a negative start position. Returns 'aki' from 'Sakila' starting at position -5 with length 3.

```sql
SELECT mid('Sakila', -5, 3);
```

```sql
+----------------------+
| mid('Sakila', -5, 3) |
+----------------------+
| aki                  |
+----------------------+
```

--------------------------------

### MID example with positive position

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example query using MID with a positive start position. Returns 'ratica' from 'Quadratically' starting at position 5 with length 6.

```sql
SELECT mid('Quadratically',5,6);
```

```sql
+--------------------------+
| mid('Quadratically',5,6) |
+--------------------------+
| ratica                   |
+--------------------------+
```

--------------------------------

### CREATE SERVER Example

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/import-foreign-schema

Example of creating an external server named 'maxcompute_server' with a VPC endpoint and placeholder credentials.

```sql
CREATE SERVER maxcompute_server
TYPE = 'ODPS'
SERVER_PROPERTIES = '{
    "endpoint":"http://service.cn-hangzhou-vpc.maxcompute.aliyun-inc.com/api",
    "accessid":"STS.****************",
    "accesskey":"yourAccessKeySecret"
}';
```

--------------------------------

### SHOW CATALOGS example and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/show-catalogs

Example query to view all catalogs in the current instance, followed by the expected result set showing the built-in 'adb' catalog and an external catalog 'paimon_oss'.

```sql
SHOW CATALOGS;
```

```text
+------------+------------------+-----------------------------------------------------------------------------------+
| Catalog    | Type             | Comment                                                                           |
+------------+------------------+-----------------------------------------------------------------------------------+
| adb        | Managed Catalog  | A system built-in catalog contains this cluster's self-managed tables as default. |
| paimon_oss | External Catalog | NULL                                                                              |
+------------+------------------+-----------------------------------------------------------------------------------+
2 rows in set (0.10 sec)
```

--------------------------------

### SUBSTR example with FROM and FOR clauses and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example of SUBSTR using the FROM and FOR clauses to specify the starting position and length. It returns a substring of length 3 starting at position 6. The output shows 'wor'.

```sql
SELECT substr('helloworld' from 6 for 3);
```

```sql
+-----------------------------------+
| substr('helloworld' FROM 6 FOR 3) |
+-----------------------------------+
| wor                               |
+-----------------------------------+
```

--------------------------------

### SHOW DATABASES example and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/show

Example of SHOW DATABASES to list all databases in the current catalog (default adb). The output shows the databases: adb_test, MYSQL, adb_demo, INFORMATION_SCHEMA.

```sql
SHOW DATABASES;
```

```sql
+--------------------+
| Database           |
+--------------------+
| adb_test           |
| MYSQL              |
| adb_demo           |
| INFORMATION_SCHEMA |
+--------------------+
```

--------------------------------

### LENGTH function example

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example query using LENGTH to get the length of 'aliyun', returning 6.

```sql
SELECT length('aliyun');
```

```sql
+------------------+
| length('aliyun') |
+------------------+
|                6 |
+------------------+
```

--------------------------------

### SHOW TABLES example and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/show

Example of SHOW TABLES IN adb_demo to list tables in the adb_demo database. The output shows tables customer and json_test.

```sql
SHOW TABLES IN adb_demo;
```

```sql
+--------------------+
| Tables_in_adb_demo |
+--------------------+
| customer           |
| json_test          |
+--------------------+
```

--------------------------------

### LEFT function example

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example query using LEFT to get the first 5 characters of 'foobarbar', returning 'fooba'.

```sql
SELECT LEFT('foobarbar', 5);
```

```sql
+----------------------+
| LEFT('foobarbar', 5) |
+----------------------+
| fooba                |
+----------------------+
```

--------------------------------

### SHOW CREATE CATALOG Example

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/show-create-catalog

Example command to view the creation statement of the Paimon Catalog 'paimon_catalog_dlf'.

```sql
SHOW CREATE CATALOG paimon_catalog_dlf;
```

--------------------------------

### ST_GeometryType example query and result

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/accessor-functions

Example query using ST_GeometryType with ST_Point to get the geometry type, returning ST_Point.

```sql
SELECT ST_GeometryType(ST_Point(1, 4));  
```

```sql
+---------------------------------------+
|ST_GeometryType(ST_Point(1, 4))        |
+---------------------------------------+   
|                 ST_Point              | 
```

--------------------------------

### SHOW COLUMNS example and result

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/show

Example of SHOW COLUMNS on table adb_demo.customer, followed by the returned result set.

```sql
SHOW COLUMNS IN adb_demo.customer;
```

```sql
+---------+---------+------+------+---------+-------+
| Field   | Type    | Null | Key  | Default | Extra |
+---------+---------+------+------+---------+-------+
| id      | int     | NO   | PRI  | NULL    |       |
| name    | varchar | YES  |      | NULL    |       |
| address | varchar | YES  |      | NULL    |       |
| gender  | boolean | YES  |      | NULL    |       |
+---------+---------+------+------+---------+-------+
```

--------------------------------

### ST_Y example query and result

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/accessor-functions

Example query using ST_Y with ST_GeometryFromText to get the Y coordinate of a point, returning 2.0.

```sql
SELECT ST_Y(ST_GeometryFromText('POINT (1 2)'));
```

```sql
+------------------------------------------------+
|ST_Y(ST_GeometryFromText('POINT (1 2)'))        |
+------------------------------------------------+   
|                2.0                             | 
```

--------------------------------

### ST_SymDifference Example with ST_AsText

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/operation-functions

Example query using ST_SymDifference with two points and ST_AsText to get a readable result. The result is a MULTIPOINT.

```sql
SELECT ST_AsText(ST_SymDifference(ST_GeometryFromText('POINT (50 100)'), ST_GeometryFromText('POINT (50 150)')));   
```

```sql
+--------------------------------------------------------------------------------------------------------------+
|ST_AsText(ST_SymDifference(ST_GeometryFromText('POINT (50 100)'), ST_GeometryFromText('POINT (50 150)')))     |
+--------------------------------------------------------------------------------------------------------------+   
|                MULTIPOINT ((50 100), (50 150))                                                               |
```

--------------------------------

### Use database and show tables

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/create-database

Switches to the adb_demo database using USE, then lists its tables with SHOW TABLES. The output shows two tables: customer and test_table.

```sql
USE adb_demo;
```

```sql
SHOW TABLES;
+------------------------------+
| Tables_in_adb_demo           |
+------------------------------+
| customer                     |
| test_table                   |
+------------------------------+
```

--------------------------------

### ST_ExteriorRing Example with ST_AsText

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/operation-functions

Example query using ST_ExteriorRing with ST_GeometryFromText and ST_AsText to get a readable exterior ring. The result is a LINESTRING.

```sql
SELECT ST_AsText(ST_ExteriorRing(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 1))')));  
```

```sql
+------------------------------------------------------------------------------------+
|ST_AsText(ST_ExteriorRing(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 1))')))        |
+------------------------------------------------------------------------------------+   
|                LINESTRING (1 1, 4 1, 1 4, 1 1)                                     | 
```

--------------------------------

### Use adb.example_db as session database

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/use

Example 1: Uses the database example_db under the adb catalog. The second form omits the catalog name, which defaults to adb.

```sql
USE adb.example_db;
```

```sql
USE example_db;
```

--------------------------------

### RIGHT function example and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/variable-length-binary-functions

Example usage of RIGHT to get the rightmost character of a VARBINARY cast of '中国', and the resulting HEX output.

```sql
SELECT HEX(RIGHT(CAST('中国' AS VARBINARY),1));
```

```sql
+--------------------------------------------+
| HEX(RIGHT(CAST('中国' AS VARBINARY), 1))   |
+--------------------------------------------+
| BD                                         |
+--------------------------------------------+
```

--------------------------------

### SHOW CREATE CATALOG Example Output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/show-create-catalog

Example output of the SHOW CREATE CATALOG command, showing the Catalog name and the full CREATE EXTERNAL CATALOG statement.

```text
+-------------------+---------------------------------------------------+
| Catalog           | Create Catalog                                    |
+-------------------+---------------------------------------------------+
| paimon_catalog_dlf| CREATE EXTERNAL CATALOG `paimon_catalog_dlf`      |
|                   | PROPERTIES (                                      |
|                   |   'type'='paimon',                                |
|                   |   'paimon.catalog.type'='paimon',                 |
|                   |   'paimon.catalog.metastore'='rest',              |
|                   |   'paimon.catalog.uri'='cn-beijing-vpc.dlf.aliyuncs.com', |
|                   |   'paimon.catalog.token.provider'='dlf',          |
|                   |   'paimon.catalog.warehouse'='*****'              |
|                   | );                                                |
+-------------------+---------------------------------------------------+
1 row in set (0.24 sec)
```

--------------------------------

### APPROX_PERCENTILE example query and result

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/approximate-function

Example query to get the approximate value at the 40th percentile of the age column in the test table. The result is shown.

```sql
SELECT APPROX_PERCENTILE(age, 0.4) FROM test;
```

```sql
+------------------------------+
| APPROX_PERCENTILE(age, 0.4)  |
+------------------------------+
|   20                         |
+------------------------------+ 
```

--------------------------------

### Switch from paimon_catalog.example_db1 to iceberg_catalog.example_db2

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/use

Example 3: Switches the session database from paimon_catalog.example_db1 to iceberg_catalog.example_db2. The catalog_name must be specified when switching between catalogs.

```sql
USE iceberg_catalog.example_db2;
```

--------------------------------

### 配置BUILD自动调度时间

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/build

使用SET ADB_CONFIG命令配置调度参数时，start和end参数必须用反引号（`）引起来，否则参数解析错误导致配置不生效。示例表示在0:00~6:59时间段内调度BUILD任务。

```sql
SET ADB_CONFIG RC_CSTORE_BUILD_SCHEDULE_PERIOD=`0,6`;
```

--------------------------------

### SUBSTR example with FROM clause and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example of SUBSTR using the FROM clause to specify the starting position. It returns the substring from position 6 to the end. The output shows 'world'.

```sql
SELECT substr('helloworld' FROM 6);
```

```sql
+-----------------------------+
| substr('helloworld' FROM 6) |
+-----------------------------+
| world                       |
+-----------------------------+
```

--------------------------------

### SUBSTR example with position and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example of SUBSTR with a single position argument. It returns the substring starting at position 6 to the end of the string. The output shows 'world'.

```sql
SELECT substr('helloworld', 6);
```

```sql
+-------------------------+
| substr('helloworld', 6) |
+-------------------------+
| world                   |
+-------------------------+
```

--------------------------------

### CREATE TABLE with partition lifecycle (correct example)

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/create-table-syntax

This example fixes the 'partition number must larger than 0' error by adding a LIFECYCLE clause to the partition definition.

```sql
CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT '',
  PRIMARY KEY (id, name)
) 
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name) LIFECYCLE 30;
```

--------------------------------

### REGEXP_REPLACE example 2: replace specific occurrence

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/regular-functions

Example query replacing only the third match (occurrence=3) starting from position 1. Only the third matched substring is replaced.

```sql
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3) as res;
```

```sql
+-----------+
| res       |
+-----------+
| abc def X |
+-----------+
```

--------------------------------

### ST_Point example and garbled output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/spatial-constructor-functions

Example of ST_Point(1,1) and its direct output, which shows garbled characters as noted in the documentation. Use ST_AsText to get readable text.

```sql
SELECT ST_Point(1,1);
                
```

```sql
+-----------------------+
| ST_Point(1,1)         |
+-----------------------+
|        ?      ?   |        
```

--------------------------------

### Use paimon_catalog.example_db as session database

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/use

Example 2: Uses the database example_db under the paimon_catalog external catalog. The catalog_name must be specified when using an external catalog.

```sql
USE paimon_catalog.example_db;
```

--------------------------------

### APPROX_DISTINCT example query and result

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/approximate-function

Example query to get the approximate count of distinct values in the age column of the test table. The result is approximate and shown for reference.

```sql
SELECT approx_distinct(age) from test;
```

```sql
+-----------------------+
| approx_distinct(age)  |
+-----------------------+
|   4                   |
+-----------------------+ 
```

--------------------------------

### SHOW SERVER WHERE SERVER_NAME Example

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/import-foreign-schema

Example of querying the external server named 'maxcompute_server'.

```sql
SHOW SERVER WHERE SERVER_NAME='maxcompute_server';
```

--------------------------------

### CREATE USER example with password

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/create-user

Creates a user named 'account2' with the password 'Account2'. Requires CREATE_USER permission and the password must meet length and character requirements.

```sql
CREATE USER if not exists 'account2' IDENTIFIED BY 'Account2';        
```

--------------------------------

### SUBSTR example with position and length and output

Source: https://help.aliyun.com/zh/analyticdb/analyticdb-for-mysql/developer-reference/strings-functions

Example of SUBSTR with a position and length argument. It returns a substring of length 3 starting at position 6. The output shows 'wor'.

```sql
SELECT substr('helloworld', 6, 3);
```

```sql
+----------------------------+
| substr('helloworld', 6, 3) |
+----------------------------+
| wor                        |
+----------------------------+
```
Tokens:
Raw