DML语句

​ InCloud ZNBASE支持标准的DML语句，对数据进行操作。

INSERT

​ INSERT语句可以往表中插入一行或者多行数据。执行一个单独的多行INSERT语句比多个单行的 INSERT 语句更快。往一张现有表中插入大量的数据时 ，建议使用多行 INSERT 语句代替多个单行INSERT 语句。

所需权限

​ 当目标表上没有外键约束时，任何用户拥有目标表所属数据库和模式的USAGE权限和目标表或目标列的INSERT权限，即可执行目标表中数据的插入操作。

​ 当目标表上存在外键约束时，任何用户除了拥有上述权限外，还需要所有被关联表所属数据库和模式的USAGE权限、被关联表的REFERENCES和SELECT权限，或被关联列的SELECT权限，才可执行目标表中数据的插入操作；

​ 如果需要ON CONFLICT DO UPDATE，则用户还需要拥有目标表或目标列的UPDATE和INSERT权限。

语法格式

INSERT语句的语法格式如下：

• common_table_expr

• target_elem

参数说明

• common_table_expr

与WITH关键字结合，组成WITH AS短语，其也被称为子查询部分。将需要频繁执行的SQL片段加个别名放到全局中，后面直接调用就可以，这样减少调用次数，优化执行效率。

• table_alias_name

表的别名。当使用AS table_alias_name子句即提供了别名时，实际的表名会隐藏。

• column_name

写入数据时要填充的列名。

• select_stmt

其为SELECT语句，了解其具体详情，可参看5.2.6节。

• DEFAULT VALUES

当需要使用默认值填充每一列时，使用DEFAULT VALUES来代替SELECT语句查询的结果。当需要使用默认值填充某一列时，请在SELECT语句中忽略该值或在适当位置使用DEFAULT字段。请参考示例5“插入并返回对应类型的值”部分。

• target_elem

target_list由若干个target_elem组成。RETURNING target_list，基于插入的行返回对应类型的值，其中target_list可以是表中的特定列名，*表示所有列也可以用标量表达式表示。RETURN NOTHIN，顾名思义就是什么也不返回。请参考示例5“插入和返回对应类型数值”部分。

• ON CONFLICT子句

参见下节5.2.1.4 on_conflict子句说明部分。

on_conflict子句

当遇到唯一键约束冲突时，加上ON CONFLICT分句会比单纯的INSERT更加好用。

• opt_conf_expr

• opt_where_clause

用来指定在何处更新，其结构为“关键字WHERE + 常规表达式”。

• set_clause_list

它是由若干个set_clause组成，其中某一个set_clause的结构如下：

如上图所示，set_clause可以分为single_set_clause和multiple_set_clause，其形式如下：

• single_set_clause

• multiple_set_clause

​ 正常情况下, 当插入数据的一个或多个列与唯一性约束冲突时，InCloud ZNBASE会返回错误。如果仍想更新被影响的行，请使用ON CONFLICT子句指定具有唯一键约束的列；DO UPDATE SET表达式指定要更新的列（支持所有UPDATE语句支持的SET表达式，包括带有WHERE的从句）。为避免更新时影响要插入的新行，将ON CONFLICT设置为DO NOTHING。

​ 想要进一步了解ON CONFLICT语句的用法，可参阅示例6 “ON CONFLICT时更新值”和示例7 “当ON CONFLICT时不要更新值”部分。

语法示例

下面的所有例子都假设你已经创建了一个表accounts：

> CREATE TABLE accounts( id INT DEFAULT unique_rowid(), balance DECIMAL );

示例1：插入一个单行。

>INSERT INTO accounts (balance, id) VALUES (10000.50, 1);

> SELECT * FROM accounts;

id | balance

+——+————-+

1 |10000.5

如果未列出列名，则INSERT语句将按声明顺序指定表的列：

> SHOW COLUMNS FROM accounts;

+————-+————-+———-+————————+

| Field | Type | Null | Default |

+————-+————-+———-+————————+

| id | INT | false | unique_rowid() |

| balance | DECIMAL | true | NULL |

+————-+————-+———-+————————+

> INSERT INTO accounts VALUES (2, 20000.75);

> SELECT * FROM accounts;

| id | balance |

+——+—————+

| 1 | 10000.50 |

| 2 | 20000.75 |

示例2：在现有表中插入多行

多行插入比多个单行INSERT语句快

>INSERT INTO accounts (balance, id) VALUES (10000.50, 1);

> SELECT * FROM accounts;
+——+————-+

| id | balance |

+——+————-+

| 1 | 10000.5 |

示例3：在新建表中插入多行数据

>CREATE SCHEMA db1.s1;

CREATE SCHEMA |

示例４：插入一个默认值

> INSERT INTO accounts (id) VALUES (8);

> INSERT INTO accounts (id, balance) VALUES (9, DEFAULT);

> SELECT * FROM accounts WHERE id in (8, 9);

| id | balance |

+——+————-+

| 8 | NULL |

| 9 | NULL |

> INSERT INTO accounts DEFAULT VALUES;

> SELECT * FROM accounts;

+——————————+—————+

| id | balance |

+——————————+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 8100.73 |

| 4 | 9400.1 |

| 5 | 350.1 |

| 6 | 150 |

| 7 | 200.1 |

| 8 | NULL |

| 9 | NULL |

| 142933248649822209 | NULL |

示例５：插入并返回对应类型的值

在这个例子中，RETURNING子句返回插入行的id值。

> INSERT INTO accounts (id, balance) VALUES (DEFAULT, 1000), (DEFAULT, 250) RETURNING id;

| id |

+——————— ——-+

| 190018410823680001 |

| 190018410823712769 |

示例6：ON_CONFLICT时更新对应类型的值：检测到唯一性冲突时InCloud ZNBASE将该行存储在名为excluded的临时表中。当发生冲突时，可以通过使用临时表excluded来更新对应的列：

> INSERT INTO accounts (id, balance) VALUES (8, 500.50) ON CONFLICT (id) DO UPDATE SET balance = excluded.balance;

> SELECT * FROM accounts WHERE id = 8;

+——+————-+

| id | balance |

+——+————-+

| 8 | 500.50 |

也可以使用现有的值来更新行：

> INSERT INTO accounts (id, balance) VALUES (8, 500.50) ON CONFLICT (id) DO UPDATE SET balance = accounts.balance + excluded.balance;

> SELECT * FROM accounts WHERE id = 8;

+——+————-+

| id | balance |

+——+————-+

| 8 | 1001.00 |

还可以使用WHERE子句来有条件地应用DO UPDATE SET表达式：

> INSERT INTO accounts (id, balance) VALUES (8, 700) ON CONFLICT (id) DO UPDATE SET balance = excluded.balance WHERE excluded.balance > accounts.balance;

> SELECT * FROM accounts WHERE id = 8;

+——+————-+

| id | balance |

+——+————-+

| 8 | 800 |

示例7：ON_CONFLICT时不更新对应类型的值: 遇到了一个唯一性冲突的错误：

> SELECT * FROM accounts WHERE id = 8;

+——+————-+

| id | balance |

+——+————-+

| 8 | 500.5 |

+——+————-+

> INSERT INTO accounts (id, balance) VALUES (8, 125.50);

错误: duplicate key value (id)=(8) violates unique constraint “primary”

在这个例子中，我们使用ON CONFLICT DO NOTHING来忽略唯一性错误，避免冲突行被更新：

> INSERT INTO accounts (id, balance) VALUES (8, 125.50) ON CONFLICT (id) DO NOTHING;

> SELECT * FROM accounts WHERE id = 8;

+——+————-+

| id | balance |

+——+————-+

| 8 | 500.5 |

+——+————-+

在这个例子中，ON CONFLICT DO NOTHING阻止第一行更新，同时允许插入第二行：

> INSERT INTO accounts (id, balance) VALUES (8, 125.50), (10, 450) ON CONFLICT (id) DO NOTHING;

> SELECT * FROM accounts WHERE id in (8, 10);

+——+————-+

| id | balance |

+——+————-+

| 8 | 500.5 |

| 10 | 450 |

MERGE INTO

​ MERGEINTO语句可以在满足条件时用B表数据更新A表数据，在不满足条件时则将B表中数据插入A表。但是有很多可选项，如满足条件时可以删除A表数据或抛出异常，不满足条件时也可抛出异常。

​ MERGEINTO可以单独执行更新或者插入。MERGEINTO命令使用一条语句从一个数据源中完成对表的更新或者插入等操作，避免了额外的SELECT操作。

所需权限

用户需要相关表的SELECT和UPDATE权限，如若执行DELETE操作，还需要DELETE权限。

语法格式

• insert_target

• merge_rest

• match_list

• match_item

• notmatch_list

• notmatch_item

• signal_clause

参数说明

• insert_target

目标表的表名，可以取别名。

• merge_rest

MERGE INTO操作的源数据，可以是另一个表也可以是一条SELECT语句，注意表或SELECT到的数据应和目标表的表结构相同（同样列数，同列类型相同）。

• match_list

match_item的集合。

• match_item

当满足on条件的时候所做的update、delete、signal操作，可以在WHEN MATCHED关键字后添加额外条件。

• notmatch_list

notmatch_item的集合。

• notmatch_item

当不满足on条件的时候所做的insert、signal操作，可以在WHEN NOT MATCHED关键字后添加额外条件。

• signal_clause

当满足某条件时，可以选择使用signal语句来抛出异常，该语句已经进行的操作会进行回滚。

语法示例

以下示例假定已经创建了manager和employee两表并插入数据

创建employee表

> CREATE TABLE employee (employeeid INT NOT NULL, name STRING, salary FLOAT);

> INSERT INTO employee VALUES (1,’1’,1000), (2,’2’,2000), (3,’3’,3000),(4,’4’,4000), (5,’5’,5000), (6,’6’,6000);

创建manager表

> CREATE TABLE manager (managerid INT NOT NULL, name STRING, salary FLOAT);

> INSERT INTO manager VALUES (3,’3’,3666),(4,’4’,4666), (6,’6’,6666),(7,’7’,7666), (8,’8’,8666);

示例1，仅有MATCH：

> MERGE INTO employee AS em USING manager AS ma ON em.employeeid = ma.managerid WHEN MATCHED AND em.employeeid = 3 THEN UPDATE SET salary = ma.salary WHEN MATCHED AND em.employeeid = 4 THEN DELETE;

> SELECT * FROM employee;

employeeid | name | salary

+—————-+———+————+

1 | 1 | 1000

2 | 2 | 2000

3 | 3 | 3666

5 | 5 | 5000

6 | 6 | 6000 |

示例2，仅有NOT MATCH：

> MERGE INTO employee AS em USING manager AS ma ON em.employeeid = ma.managerid WHEN NOT MATCHED AND ma.managerid = 7 THEN INSERT VALUES ( ma.managerid, ma.NAME, ma.salary );

> SELECT * FROM employee;

employeeid | name | salary

+——————+———+————+

1 | 1 | 1000

2 | 2 | 2000

3 | 3 | 3000

4 | 4 | 4000

5 | 5 | 5000

6 | 6 | 6000

7 | 7 | 7666 |

示例3，SIGNAL:

>MERGE INTO employee AS em USING manager AS ma ON em.employeeid = ma.managerid WHEN MATCHED THEN SIGNAL SQLSTATE’error1’ SET MESSAGE_TEXT ‘error2’;

pq: error1: error2 |

示例4，源表为SELECT语句：

> MERGE INTO employee AS em USING ( SELECT * FROM manager ) AS ma ON em.employeeid = ma.managerid WHEN NOT MATCHED THEN INSERT ( employeeid, salary ) VALUES ( ma.managerid, ma.salary );

> SELECT * FROM employee;

employeeid | name | salary

+——————+———+————+

1 | 1 | 1000

2 | 2 | 2000

3 | 3 | 3000

4 | 4 | 4000

5 | 5 | 5000

6 | 6 | 6000

7 | NULL | 7666

8 | NULL | 8666 |

示例5，MATCH and NOT MATCH:

> MERGE INTO employee AS em USING manager AS ma ON em.employeeid = ma.managerid WHEN MATCHED THEN UPDATE SET salary = ma.salary WHEN NOT MATCHED THEN INSERT VALUES ( ma.managerid, ma.NAME, ma.salary );

> SELECT * FROM employee; employeid | name | salary

+—————-+———+————+

1 | 1 | 1000

2 | 2 | 2000

3 | 3 | 3666

4 | 4 | 4666

5 | 5 | 5000

6 | 6 | 6666

7 | 7 | 7666

8 | 8 | 8666 |

UPDATE

更新表中某行数据。

所需权限

​ 当目标表上没有外键约束时，任何用户拥有目标表所属数据库和模式的USAGE权限和目标表或目标列的SELECT和UPDATE权限，即可执行目标表中数据的更新操作。

​ 当目标表上存在外键约束时，任何用户除了上述权限外，还需要所有被关联表所属数据库和模式的USAGE权限、被关联表的REFERENCES和SELECT权限，或被关联列的SELECT权限，才可执行目标表中数据的更新操作。

语法格式

• common_table_expr

• opt_index_falgs

• sort_clause

• limit_clause

参数说明

• common_table_expr

与WITH关键字结合，组成WITH AS短语，其也被称为子查询部分。将需要频繁执行的SQL片段加个别名放到全局中，后面直接调用就可以，这样减少调用次数，优化执行效率。

• table_name

要更新的行的表名。

• table_alias_name

表名的一个别名。语句中提供别名后，会完全隐藏真实的表名。

• column_name

想要更新列的名字。

• a_expr

可以是想要使用的新值、想要执行的聚合函或者是想要使用的量表达式。如果想要要使用其默认值填充所有列，请将a_expr替换为DEFAULT VALUES；如果想要使用其默认值填充特定的列，则在a_expr中保留该值或在适当的位置使用DEFAULT。

• select_stmt

其为SELECT语句，了解其具体详情，可参看4.2.6节。

• WHERE a_expr

a_expr必须是使用列(例如 <column> =<value>)返回布尔值的标量表达式。更新返回TRUE的行。如果语句中没有WHERE子句，UPDATE操作将更新表中所有行。

• target_list

RETURNING target_list，基于插入的行返回对应类型的值，其中target_list可以是表中的特定列名，*表示所有列也可以用标量表达式表示。RETURN NOTHIN，顾名思义就是什么也不返回。请参考示例6“更新和返回一些值”部分。

语法示例

示例1：更新一行中的某一列：

>SELECT * FROM accounts; +——+—————+—————+

| id | balance | customer |

+——+—————+—————+

| 1 | 10000.50 | Ilya |

| 2 | 4000.0 | Julian |

| 3 | 8700.0 | Dario |

| 4 | 3400.0 | Nitin |

(4 rows)

> UPDATE accounts SET balance = 5000.0 WHERE id = 2;

> SELECT * FROM accounts;

+——+—————+—————+

| id | balance | customer |

+——+—————+—————+

| 1 | 10000.50 | Ilya |

| 2 | 5000.0 | Julian |

| 3 | 8700.0 | Dario |

| 4 | 3400.0 | Nitin |

示例2：更新一行中的多个列的值

>UPDATE accounts SET (balance, customer) = (9000.0, ‘Kelly’) WHERE id = 2;

> SELECT * FROM accounts;

+——+—————+—————+

| id | balance | customer |

+——+—————+—————+

| 1 | 10000.50 | Ilya |

| 2 | 9000.0 | Kelly |

| 3 | 8700.0 | Dario |

| 4 | 3400.0 | Nitin |

(4 rows)

> UPDATE accounts SET balance = 6300.0, customer = ‘Stanley’ WHERE id = 3;

> SELECT * FROM accounts;

+——+—————+—————+

| id | balance | customer |

+——+—————+—————+

| 1 | 10000.50 | Ilya |

| 2 | 9000.0 | Kelly |

| 3 | 6300.0 | Stanley |

| 4 | 3400.0 | Nitin |

示例3：使用 SELECT 语句进行更新

>SELECT * FROM accounts;

> UPDATE accountsSET (balance, customer)=(SELECT balance, customer FROM accounts WHERE id = 2) WHERE id = 4;

> SELECT * FROM accounts;

+——+—————+—————+

| id | balance | customer |

+——+—————+—————+

| 1 | 10000.50 | Ilya |

| 2 | 9000.0 | Kelly |

| 3 | 6300.0 | Stanley |

| 4 | 9000.0 | Kelly |

示例4：使用默认值进行更新

> UPDATE accounts SET balance = DEFAULT where customer = ‘Stanley’;

> SELECT * FROM accounts;

+——+—————+—————+

| id | balance | customer |

+——+—————+—————+

| 1 | 10000.50 | Ilya |

| 2 | 9000.0 | Kelly |

| 3 | NULL | Stanley |

| 4 | 9000.0 | Kelly |

示例5：更新所有列 如果没有使用 WHERE 子句去指定了要更新的行，会更新所有行的数据。

> UPDATE accounts SET balance = 5000.0;

> SELECT * FROM accounts;

+——+————-+—————+

| id | balance | customer |

+——+————-+—————+

| 1 | 5000.0 | Ilya |

| 2 | 5000.0 | Kelly |

| 3 | 5000.0 | Stanley |

| 4 | 5000.0 | Kelly |

示例6：更新和返回一些值

> UPDATE accounts SET balance = DEFAULT WHERE id = 1 RETURNING id;

+——+

| id |

+——-+

| 1 |

UPSERT

​ 该UPSERT 语句在语义上等效于INSERT ONCONFLICT，但是两者的性能特征可能略有不同。在指定的值不违反唯一性约束的情况下，它会插入行，在指定的值违反唯一性约束的情况下，它会更新行。

所需权限

​ 当目标表上没有外键约束时，任何用户拥有目标表所属数据库和模式的USAGE权限和目标表的INSERT，SELECT和UPDATE权限，即可执行目标表中数据的插入和更新操作。

​ 当目标表上存在外键约束时，任何用户除了上述权限外，还需要所有被关联表所属数据库和模式的USAGE权限、被关联表的REFERENCES和SELECT权限，或被关联列的SELECT权限，才可执行目标表中数据的插入和更新操作。

语法格式

参数说明

• common_table_expr

与WITH关键字结合，组成WITHAS短语，其也被称为子查询部分。将需要频繁执行的SQL片段加个别名放到全局中，后面直接调用就可以，这样减少调用次数，优化执行效率。

• table_name

要更新的行的表名。

• table_alias_name

表名的一个别名。语句中提供别名后，会完全隐藏真实的表名。

• column_name

想要更新列的名字。

• select_stmt

其为SELECT语句，了解其具体详情，可参看4.2.6节。

• target_list

RETURNING target_list，基于插入的行返回对应类型的值，其中target_list可以是表中的特定列名，*表示所有列也可以用标量表达式表示。RETURN NOTHIN，顾名思义就是什么也不返回。请参考示例5“插入和返回对应类型数值”部分。

UPSERT与 INSERT ON CONFLICT比较

​ 前面部分已经说明，UPSERT 语句在语义上等效于INSERT ON CONFLICT，但是两者的性能特征可能略有不同。本小节便对其进行简单的比较说明：

​ UPSER仅仅考虑主键所在的列是否存在唯一性冲突。主键所在的列不存在冲突而非主键列存在冲突时，则执行会出现错误。

而INSERT ON CONFLICT则较为灵活，它可以考虑除了主键列，其他的列是否存在唯一性冲突的情况；虽然在一些复杂的情况下会使得代码变得冗长，但却能有效避免非主键列冲突存在时，操作失败的情况的发生。

​ 在这里通过一个简单的例子进行说明下：

​ 假定a 和 b是主键列，则下面的UPSERT与INSERT ON CONFLICT的结果相同：

> UPSERT INTO t (a, b, c) VALUES (1, 2, 3);

> INSERT INTO t (a, b, c) VALUES (1, 2, 3) ON CONFLICT (a, b) DO UPDATE SET c = excluded.c;

注：下节“语法示例”中的“示例4：Upsert 失败”也对这点进行了说明。

语法示例

示例1：Upsert一行（无冲突）

> SELECT * FROM accounts;

+——+—————+

| id | balance |

+——+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

+——+—————+

> UPSERT INTO accounts (id, balance) VALUES (3, 6325.20);

> SELECT * FROM accounts;

+——+—————+

| id | balance |

+——+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 6325.2 |

在这个例子中，id列是主键，由于UPSERT语句中的id值与当前表中的值不冲突，所以会直接插入表中。

示例2：Upsert 多行数据。

> SELECT * FROM accounts;

+——+—————+

| id | balance |

+——+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 6325.2 |

+——+—————+

> UPSERT INTO accounts (id, balance) VALUES (4, 1970.4), (5, 2532.9), (6, 4473.0);

> SELECT * FROM accounts;

+——+—————+

| id | balance |

+——+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 6325.2 |

| 4 | 1970.4 |

| 5 | 2532.9 |

| 6 | 4473.0 |

+——+—————+ |

在这个例子中，UPSERT语句插入多行数据

示例3：Upsert 更行一行数据 (在主键有冲突)

> SELECT * FROM accounts;

+——+—————+

| id | balance |

+——+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 6325.2 |

| 4 | 1970.4 |

| 5 | 2532.9 |

| 6 | 4473.0 |

+——+—————+

> UPSERT INTO accounts (id, balance) VALUES (3, 7500.83);

> SELECT * FROM accounts;

+——+—————+

| id | balance |

+——+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 7500.83 |

| 4 | 1970.4 |

| 5 | 2532.9 |

| 6 | 4473.0 |

示例4：Upsert 失败(在非主键列中存在冲突 )

> SELECT * FROM accountst;

+——+—————+

| id | balance |

+——+—————+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 7500.83 |

| 4 | 1970.4 |

| 5 | 2532.9 |

| 6 | 4473.0 |

+——+—————+

> UPSERT INTO accounts VALUES (7, 1970.4);

错误：重复键值(balance)=(1970.4)违反唯一性约束 在这种情况下，INSERT ON CONFLICT便显得较为灵活，可见下面的这个例子： > INSERT INTO accounts VALUES (7, 1970.4) ON CONFLICT (balance) DO UPDATE SET id = excluded.id

> SELECT * FROM accounts;

+——+—————-+

| id | balance |

+——+—————-+

| 1 | 10000.5 |

| 2 | 20000.75 |

| 3 | 7500.83 |

| 4 | 1970.4 | \

| 5 | 2532.9 |

| 6 | 4473.0 |

| 7 | 1970.4 |

DELETE

DELETE 语句删除一个表中的行数据。

所需权限

​ 当目标表上没有外键约束时，任何用户拥有目标表所属数据库和模式的USAGE权限和目标表的SELECT和DELETE权限，即可执行目标表中数据的删除操作。

​ 当目标表上存在外键约束时，任何用户除了拥有目标表的SELECT和DELETE权限外，还需要所有被关联表所属数据库和模式的USAGE权限、被关联表的SELECT和REFERENCES权限，才可执行目标表中数据的删除操作。

语法格式

参数说明

• common_table_expr

与WITH关键字结合，组成WITH AS短语，其也被称为子查询部分。将需要频繁执行的SQL片段加个别名放到全局中，后面直接调用就可以，这样减少调用次数，优化执行效率。

• table_name

要删除的行所在表的表名。

• table_alias_name

表名的一个别名。语句中提供别名后，会完全隐藏真实的表名。

• column_name

想要更新列的名字。

• WHERE a_expr

当不使用WHERE a_expr子句时，DELETE操作会删除表中的所有行中的数据；

a_expr必须是使用列(例如 <column> =<value>)返回布尔值的标量表达式。删除表达式返回TRUE的行。

• target_list

RETURNING target_list，基于插入的行返回对应类型的值，其中target_list可以是表中的特定列名，*表示所有列也可以用标量表达式表示。RETURN NOTHIN，顾名思义就是什么也不返回。请参考示例5“插入和返回对应类型数值”部分。

DELETE操作的分类

​ 当不使用WHERE子句时，DELETE操作会删除表中所有的行；

​ 当使用WHERE子句时，根据表达式中使用的列在表中不同的作用删除操作又可以分为两种情况：使用主键/唯一键删除数据、使用非唯一的列删除数据。

• 使用主键/唯一键删除数据

使用有Primary Key or Unique 约束的列作为条件删除数据可以确保语句是确定、即某个列的值不会出现在两行数据中，因此不会无意中删除其他行的数据。

• 使用非唯一的列删除数据

使用非唯一的列来删除数据时，如果表中该列字段值使WHERE条件后a_expr.表达式的值为TRUE，就删除该行数据。使用非唯一的列字段删除数据，很容易导致删除计划之外的数据。

可参见下节“语法示例”中的“示例２”

语法示例

示例1：删除所有行的数据

当我们在DELETE语句中不使用WHERE条件时，删除所有列的数据。

> DELETE FROM account_details;

DELETE 7

示例2：删除特定行的数据，使用主键/唯一键删除数据

account_id是表的主键，我们要删除account_id等于1的数据。 由于只会有一行数据的account_id等于1，所以我们不会删除其他行的数据

> DELETE FROM account_details WHERE account_id = 1 RETURNING *;

+——————+————-+———————+

| account_id | balance | account_type |

+——————+————-+——————-+

| 1 | 32000 | Savings |

使用非唯一的列删除数据

> DELETE FROM account_details WHERE balance =30000 RETURNING *;

+——————+————-+———————+

| account_id | balance | account_type |

+——————+————-+———————+

| 2 | 30000 | Checking |

| 3 | 30000 | Savings |

+——————+———— +———————+

在这个示例中，删除了两行。

TRUNCATE

删除一个特定表的所有数据。

所需权限

​ 当目标表没有被其他表关联时，任何用户拥有目标表所属数据库和模式的USAGE权限和目标表的DROP权限、目标表所属模式的CREATE权限，即可执行目标表中数据的清空操作。

​ 当目标表为其他表的被关联表时，使用CASCADE关键字才可执行，任何用户拥有上述权限以及所有关联表的DROP权限和这些表所属模式的CREATE权限，即可执行目标表及关联表中数据的清空操作。

语法格式

参数说明

• table_name

要进行TRUNCATE操作的表的名字

• CASCADE

删除在删除中的表上具有外键约束的所有表。

CASCADE不会列出被截断的依赖表，因此应谨慎使用。

• RESTRICT

如果任何其他的表具有在该表上的外键依赖，则不删除这个表。如果在语句中不指定字段，则默认的情况下为RESTRLCT。

​ 对于一张要进行TRUNCATE的表，CASCADE会TRUNCATE所有对这个表有依赖的表，但是不会列出已经TRUNCATE的表，这可能会导致一些难以恢复的损失。所以为了避免潜在的损失，最好显式地TRUNCATE每一张表。

语法示例

示例1：Truncate 一张表(没有外键依赖)

> SELECT * FROM t1;

+——+———+

| id | name |

+——+———+

| 1 | foo |

| 2 | bar |

+——+———+

(2 rows)

> TRUNCATE t1;

> SELECT * FROM t1;

+——+———+

| id | name |

+——+———+ +——+———+

(0 rows)

不具有外键约束，能够对这个表进行TRUNCATE操作。 |

示例2：Truncate一张表和它依赖的表

使用关键字CASCADE来TRUNCATE一个有依赖的表

> TRUNCATE customers;

错误: “customers”对”orders”有外键依赖，所以不加CASCADE字不会对表进行任何操作。

> TRUNCATE customers CASCADE;

> SELECT * FROM customers;

+——+———-+

| id | email |

+——+———-+ +——+———-+

(0 rows)

> SELECT * FROM orders;

+——+—————+——————+

| id | customer | orderTotal |

+——+—————+——————+ +——+—————+——————+

(0 rows)

​ 由于表orders对表customers有外键依赖。因此，在TRUNCATE操作使用关键字CASCAD后，在对表customers进行TRUNCATE操作时也对表orders进行RTRUNCATE操作。

SELECT

​ 简单SELECT子句是读取和处理现有数据的主要SQL语法。当用作独立语句时，简单SELECT子句也称为“ SELECT语句”。但是，它也是一个选择子句，可以与其他结构组合以形成更复杂的选择查询。

简单SELECT子句

​ 简单SELECT子句是读取和处理现有数据的主要SQL语法。当用作独立语句时，简单SELECT子句也称为“SELECT语句”。但是，它也是一个选择子句，可以与其他结构组合以形成更复杂的选择查询。

所需权限

​ 任何用户拥有查询操作涉及到的所有表，视图和序列所属数据库和模式的USAGE权限和目标对象的SELECT权限，即可执行查询数据的操作。

语法格式

参数说明

• DISTINCT 或者ALL

当使用ALL关键字或者在默认情况下，SELECT操作将返回所有的行，但不删除重复的行；当使用DISTINCT关键字时，将会删除返回结果中重复的行。

• DISTINCT ON ( a_expr [, …] )

a_expr[,…],由一个或多个标量表达式组成的列表。

在没有ON关键字的情况下，如果两行通过SELECT操作计算得到的所有结果均相等的，则它们重复的；当使用ON关键字时，如果ON关键子列出的标量表达式相等，则认为两行是重复的。

当使用DISTINCT ON判定两行重复时，将用FROM中第一行的数据，按照在ORDER BY指定的顺序来计算剩余的目标表达式。如果ORDER BY未指定，InCloud ZNBASE将随机的选择这些重复行中的任意一个作为第一行。

• target_elem

可以为一个标量表达式，也可以为*；

当其为一个标量表达式时，用来计算每个结果行中的一列；当其为*时，自动从FROM子句中检索所有列。如果target_elem包含聚合函数，则可以使用GROUP BY子句进一步控制聚合。

• table_ref

检索数据的表的表达式，在FROM子句中使用两个或多个表的表达式（用逗号隔开）等效于使用一个交叉连接（CROSS JOIN）表达式。详情可参见4.2.6.4章节的部分。

• AS OF SYSTEM TIME

检索时间戳中存在的数据

注意：由于系统时间返回历史数据，读取得到的结果可能会过时。

• as_snapshot_clause

快照子句。检索快照中存在的数据.

• WHERE a_expr

SELECT操作的筛选语句。a_expr必须是使用列(例如 <column> =<value>)返回布尔值的标量表达式。只检索表达式返回值为TRUE的行。

• GROUP BY + a_expr

GROUP BY子句。它的作用是通过一定的规则(在a_expr中指定)，将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。在target_elem或HAVING中使用聚合函数时，请在GROUP BY之后列出列分组。

• HAVING + a_expr

HAVING子句。增加HAVING 子句原因是：WHERE关键字无法与聚合函数一起使用。HAVING 子句为筛选分组后的各组数据提供了便利。a_expr可以其仅检索a_expr返回TRUE的聚合函数的组合，a_expr必须是使用聚合函数（例如，<聚合函数>=<值）返回布尔值的标量表达式。HAVING子句的作用类似于WHERE子句，但其适用于聚集函数。

• WINDOW window_definition_list

由window_definition组成的窗口定义列表，window_definition的语法结构如下：

其具体的参数如下：

​ 1. window_name

窗口的名称。

​ 2.window_specification

窗口定义列表。具体的参数如下：

1. opt_existing_window_name:

现有窗口框架的一个可选的名称。其是在不同的窗口定义中定义的。

​ 2. opt_partition_clause

分区子句。该子句是可选的，当添加该子句时，会根据expr_list中的表达式建立相关的地理分区信息。欲了解更多分区(PARTITION)的内容，可参见本文档4.1.7节的相关内容。

1. opt_sort_clause

排序语句。根据指定的列对结果集进行排序。

sortby_list

排序规则的列表。由一个或若干个sortby子句组成，sortby的语法结构如下图所示

1. a_expr

标量表达式。

2.table_name

表的名称。

1. index_name

索引的名称。

1. opt_asc_desc

可选的关键字。可以为ASC、DESC，也可以不指定；在默认情况下，使用升序(ASC)来进行排序，当使用DESC时，按照降序来进行排序。

1. opt_frame_clause

可选的框架子句，其中包含框架边界和/或EXCLUDE子句。

• frame_extent

语法示例

示例1：检索特定列

> SELECT id, name, balance FROM accounts; id | name | balance

+——+—————————-+————-+

1 | Bjron Fairclough | 1200

2 | Bjron Fairclough | 2500

3 | Arturo Nevin | 250

4 | Tullia Romijnders | 250

(4 rows) Time: 2.072351ms

示例2：检索所有列

> SELECT * FROM accounts;

id | balance | name | type | state_opened

+——+————-+—————————-+—————+———————+

1 | 1200 | Bjron Fairclough | checking | AL

2 | 2500 | Bjron Fairclough | savings | AL

3 | 250 | Arturo Nevin | checking | AK

4 | 250 | Tullia Romijnders | savings | AK

(4 rows) Time: 1.79647ms |

示例3：使用单独的条件过滤

> SELECT name, balance FROM accounts WHERE balance < 300;

name | balance

+—————————-+————-+

Arturo Nevin | 250

Tullia Romijnders | 250

(2 rows) Time: 2.491975ms |

示例4：多个条件过滤数据

在WHERE 子句中，使用AND或者OR联合多个条件。还可以使用NOT创建负过滤器。

> SELECT * FROM accounts WHERE balance > 2500 AND NOT type = ‘checking’;

+——+—————————-+————-+————-+———————+

| id | name | balance | type | state_opened |

+——+—————————-+————-+————-+———————+

| 4 | Tullia Romijnders | 3000 | savings| AK |

| 6 | Ruarc Mathews | 3000 |savings | OK |

示例5：SELECT无重复的行：没有主键或者唯一性约束的列可能存在相同的值：

> SELECT name FROM accounts WHERE state_opened = ‘AK’;

+—————————-+

| name |

+—————————-+

| Arturo Nevin |

| Tullia Romijnders |

| Tullia Romijnders |

使用DISTINCT可以去除检索数据中的重复数据。如下例所示：

> SELECT DISTINCT name FROM accounts WHERE state_opened = ‘AK’;

name

+—————————-+

Arturo Nevin T

ullia Romijnders

(2 rows) Time: 6.095287ms

示例6：使用列表过滤：使用WHERE IN(<逗号分隔的值列表>)执行OR搜索指定的列中列出的值

> SELECT name, balance, state_opened FROM accounts WHERE state_opened IN (‘AK’, ‘AL’, ‘OK’);

name | balance | state_opened

+—————————-+————-+———————+

Bjron Fairclough | 1200 | AL Bjron

Fairclough | 2500 | AL Arturo

Nevin | 250 | AK Tullia

Romijnders | 250 | AK Tullia

Romijnders | 3000 | AK Ruarc

Mathews | 3000 | OK

(6 rows) Time: 3.14486ms |

示例7：变更输出列的名称：在输出中，不是输出检索表中的列的名字，而是通过AS自定义名字：

> SELECT name AS NY_accounts, balance FROM accounts WHERE state_opened = ‘AK’;

ny_accounts | balance

+—————————+————-+

Arturo Nevin | 250

Tullia Romijnders | 250

Tullia Romijnders | 3000

(3 rows) Time: 3.095645ms |

这个操作不会改变检索表中列的名字。如果要改变表中列的名字，可以使用RENAME COLUMN实现。

示例8：搜索字符串值

在语句中使用LIKE关键字在列中搜索部分匹配的字符串，支持以下通配符匹

a．“%”匹配0或多个任意字符；

b．“_”匹配1个任意字符；

c．“[charlist]”匹配字符列charlist中的任意一个字符；

d．[^charlist]或[!charlist] 不匹配字符列中的任意一个字符。

参见下面的例子：

> SELECT id, name, type FROM accounts WHERE name LIKE ‘Anni%’;

id | name | type

+——+————————+———+

7 | Annibale Karga | NY

8 | Annibale Karga | WA

(2 rows) Time: 3.731277ms |

示例9：在整个列上使用聚合函数：通过使用聚合函数作为一个target_elem,可以在整个列上进行计算。

> SELECT name,MIN(balance) FROM accounts;

min

+——-+

250 (1 row) Time: 3.387223ms |

也可以把聚合函数检索到的值作为WHERE子句表达式的一部分。

> SELECT id, name, balance FROM accounts WHERE balance = ( SELECT MIN(balance) FROM accounts );

id | name | balance

+——+—————————+————-+

3 | Arturo Nevin | 250

4 | Tullia Romijnders | 250

(2 rows) Time: 4.07241ms |

示例10：在检索出的行数据上进行聚合函数操作

通过过滤语句，只可以对检索到的行进行计算:

> SELECT SUM(balance) FROM accounts WHERE state_opened IN (‘AK’, ‘NY’, ‘WA’);

sum

+———+

3500

(1 row) Time: 3.110797ms |

示例11：筛选加入聚合函数中的列

可以在target_elem中使用FILTER(其中<boolean表达式>)来过滤由聚合函数处理的行;</boolean表达式>对于FILTER子句的布尔表达式，那些返回FALSE或NULL的值不会被输入到聚合函数中。

> SELECT count(*) AS unfiltered, count(*) FILTER (WHERE balance > 1500) AS filtered FROM accounts;

unfiltered | filtered

+——————+—————+

8 | 5

(1 row) Time: 7.270074ms |

示例12：创建聚合组

可以将这些行分成组，然后在每一行上执行聚合函数，而不是对整个检索到的行集执行聚合函数。在创建聚合组时，必须将用作target_elem的每个列包含在GROUP BY 中。

> SELECT state_opened AS state, SUM(balance) AS state_balance FROM accounts WHERE state_opened IN (‘AK’, ‘NY’, ‘WA’,’OK’,’AL’) GROUP BY state_opened;

state | state_balance +

-———+———————-+

AL | 3700

AK | 3500

OK | 3000

NY | 3000

WA | 2500

(5 rows) Time: 4.731798ms |

示例13：过滤聚合组

使用HAVING过滤聚合组，HAVING子句必须返回布尔值。

>SELECT state_opened, AVG(balance) AS avg FROM accounts GROUP BY state_opened HAVING AVG(balance) BETWEEN 300 AND 3000;

state_opened | avg

+——————-+———————————-+

WA | 2500

AL | 1850

AK | 1166.6666666666666667

OK | 3000

NY | 3000

(5 rows) Time: 3.792289ms |

示例14：在Having子句中使用聚合函数

聚合函数也可以在HAVING子句中使用，而不需要作为一个被包含的target_elem。

>SELECT name, state_opened FROM accounts WHERE state_opened in (‘AL’, ‘AK’) GROUP BY name, state_opened HAVING COUNT(name) > 1;

name | state_opened

+—————————+———————+

Bjron Fairclough | AL

Tullia Romijnders | AK

(2 rows) Time: 2.749112ms

ORDER BY子句

​ ORDER BY子句控制返回或处理行的顺序。它可以用于任何选择查询，包括INSERT或UPSERT的操作数，以及DELETE和UPDATE语句。ORDERBY 子句的语法格式如下：

ORDER BY子句的由若干个逗号分隔的ORDER规范列表(也可以没有)和关键字ASC/DESC(也可以没有)组成。

每个ORDER规范可以是下列任意形式之一:

• a_expr

任意标量表达式。它使用计算该表达式的结果作为排序键。

• PRIMARY KEY + table_name

表的主键。它使用给定表的主键作为排序键。该表必须是数据源的一部分。

• INDEX + table_name + @ + index_name

表示法索引@。它使用给定表的索引的索引列作为排序键。该表必须是数据源的一部分。

• 可选的关键字ASC和DESC，用来指定排列的顺序，默认值为ASC。

当关键字为ASC时，按照排序键的升序(从小到大)来排列；当关键字为DESC时，按照排序键的降序(从大到小)来排列。

SELECT子句

语法格式

简单SELECT操作可以包含简单操作子句、VALUES子句、table子句和集合操作，下面对其进行进一步的说明。

参数说明

• simple_select_clause

简单SELECT子句。请参阅4.2.6.1章节的内容。

• values_clause

VALUES子句。

expr_list为任意标量表达式的组合。VALUES计算由标量表达式指定的一个或一组行值。它通常用于在更大的命令中生成“常量表”，但可以单独使用。当指定多个行时，所有行必须具有相同数量的元素。生成的表格列的数据类型是通过组合显示或推断出现在该列中的表达式的类型来确定的。

举例如下：

>SELECT 1 AS column1, ‘one’ AS column2 UNION ALL SELECT 2, ‘two’ UNION ALL SELECT 3, ‘three’;

推断值的类型

• table_clause

表子句。其语法格式图如下：

TABLE表达式(表表达式)。用来限定想要检索数据的表的相关条件。

其参数table_ref的相关说明，请参阅4.2.6.4章节的相关内容。

• set_operation

集合操作。请参阅4.2.9章节的内容。

TABLE表达式

table表达式的语法格式如下：

​ table表达式在简单选择子句的FROM子句中定义数据源(可参见4.2.6.1章节)，或作为TABLE的参数(如本章节所示)。JOIN操作是一种特殊的表表达式，关于JOIN的部分可参见4.2.8章节。

table 表达式的参数说明如下：

• table_name

表或者视图的名字。

• table_alias_name

要在别名表表达式中使用的名称。

• name

列名的一个或多个别名，用于别名表表达式中。

别名表表达式会在当前查询的上下文中临时重命名表和列。语法格式如下：

| <table expr> AS <name> SELECT c.x FROM (SELECT COUNT(*) AS x FROM users) AS c; #在这种情况下，表表达式等效于它的左操作数，并为整个表起一个新名称，并且列保留其原始名称。 <table expr> AS <name>(<colname>, <colname>, …) SELECT c.x FROM (SELECT COUNT(*) FROM users) AS c(x); #在这种情况下，除了上一种的特性外，列也被重命名。

• index_name

索引名称。其是可选的,可组成table_name@index_name，表示使用指定表的索引来进行SELECT操作。

• func_application

一个函数返回的结果集。表表达式可以使用一个函数一个函数返回的结果作为数据源。语法格式如下：

其形式为：函数的名称，后跟左括号，后跟零或多个标量表达式，也可以为*，后跟右括号。

• preparable_stmt

支持的语句的返回的结果。语句可以是ALTER、CREATE、DELETE、SELECT等。

• select_stmt

SELECT查询操作。可以使用一个SELECT查询操作作为一个子查询语句。子查询语句的格式：

括号之间可以跟任何SELECT查询语句，例如简单SELECT子句。

• joined_table

连接表达式。请参见4.2.8.4章节的内容。

SELECT操作

​ 选择查询读取和处理InCloud ZNBASE中的数据。在使用SELECT查询操作时，可以选择嵌套使用，也可以直接使用。如下面的语法格式图所示：

嵌套使用时(即图中的select_with_parens),其语法格式图如下图所示：

​ 其是由若干个其他的SELECT操作组合使用，但是在使用过程，可以分析出其至少要使用一个select_no_parens来结束这个组合的SELECT过程。下面部分中对嵌套使用的情况select_no_parens进行说明。

语法格式

​ select_no_parens比简单SELECT子句更通用：它们可以将一个或多个选择子句与set操作组合在一起，并可以请求特定的顺序或行数限制，来完成一个较为复杂的查询过程。其语法格式如下图所示：

由其语法格式可知，其使用情况如下：

1. 可以直接使用简单SELECT操作(可参见4.2.6.3章节中的说明);
2. 可以将一个或多个选择子句与set操作组合在一起，并可以请求特定的顺序或行数限制，来完成一个较为复杂的查询过程;
3. 可以在2) 的基础上加上with子句来使用。

参数说明

• simple_select

简单SELECT操作。请参见4.2.6.3章节的内容。

• select_clause

有效的SELECT子句，可以是SELECT子句请参见4.2.6.3章节的内容，也可以时自身的组合。

• sort_clause

可选ORDER BY子句。请参见4.2.6.2章节的内容。

• limit_clause

可选LIMIT子句。有关详细信息，请参见4.2.6.5.3章节中LIMIT子句的内容。

• offset_clause

可选OFFSET子句。有关详细信息，请参见4.2.6.5.3章节中OFFSET子句的内容。

LIMIT和OFFSET子句

• LIMIT子句

由语法图可以看出：

该子句可以为：LIMIT + count的形式，此时操作限制为仅检索count的行数，count为跟在关键字LIMIT后面的常数，请参见示例1；

该子句还有一种形式，即语法图分支二所示，该形式又可以分为两种情况：

1. 当使用FIRST关键字时，表示取前count行的数据，请参见示例2;

2. 当使用NEXT关键字时，此时需要和OFFSET关键字组合使用(二者的简单组合，参见示例3)，OFFSET n 指定了一个起始位置，从该位置开始取接下来的count行，参见示例4。

• OFFSET子句

该子句将操作限制为跳过前偏移量行数。它通常与LIMIT组合使用，以便通过检索的行“分页”。如果想限制结果的数量，但是超出了初始集，那么使用OFFSET继续到下一个结果集。这通常用于对大型表进行分页，其中不需要立即检索所有值。

示例

示例1：使用LIMIT + count形式的子句：

> SELECT * FROM accounts LIMIT 5; id | balance | name | type | state_opened

+——+————-+—————————+————-+———————+

1 | 1200 | Bjron Fairclough | checking| AL

2 | 2500 | Bjron Fairclough | savings | AL

3 | 250 | Arturo Nevin | checking | AK

4 | 250 | Tullia Romijnders | savings | AK

5 | 3000 | Tullia Romijnders | savings | AK |

示例2：使用FEATCH FIRST + count形式的子句：

取前count行的数据

> SELECT * FROM accounts FETCH FIRST 2 ROW ONLY;

id | balance | name | type | state_opened

+——+————-+—————————+—————+———————+

1 | 1200 | Bjron Fairclough | checking | AL

2 | 2500 | Bjron Fairclough | savings | AL |

• rows)

示例3：LIMIT与OFFSET组合使用：

> SELECT id, name FROM accounts LIMIT 5 OFFSET 5;

id | name

+——+————————+

6 | Ruarc Mathews

7 | Annibale Karga

示例4：当使用NEXT关键字时，此时需要和OFFSET关键字组合使用：OFFSET n 相当于指定了一个起始位置，从该位置开始取NEXT的count行。

> SELECT * FROM accounts OFFSET 5 rows FETCH NEXT 2 ROW ONLY;

id | balance | name | type | state_opened

+——+————-+————————+—————+———————+

6 | 3000 | Ruarc Mathews | savings | OK

7 | 3000 | Annibale Karga | checking | NY

(2 rows) |

SELECT的优化

使用EXPLAIN或EXPLAIN ANALYSE的输出，为SELECT优化提供支持的信息。现在已经有的优化思想有：

• 级别越少的查询执行得越快。

• 重组查询以减少处理级别通常会提高性能。避免扫描整个表，这是访问数据最慢的方式。可以通过创建至少包含查询在其WHERE子句中筛选的列之一的索引来避免这种情况。

• 基于EXPLAIN语句输出，可以用以下方式确定查询是否涉及全表扫描：

• 通过查看Field属性值为table的行的Description值，获取查询使用的索引。

• 通过查看Field属性值为spans的行的Description值，获得索引中需要被扫描的Key值范围。

下面对其语法进行说明:

EXPLAIN

EXPLAIN语句返回InCloud ZNBASE的可解释语句的查询计划。然后可以使用这些信息来优化查询。

语法格式

EXPLAIN的语法格式如下：

参数说明

参数说明：

• VERBOSE

尽可能多的显示关于查询计划的信息。

• TYPES

包括InCloud ZNBASE选择用来计算中间SQL表达式的数据类型。

• OPT

显示由基于成本的优化器生成的查询计划树。

• DISTSQL

生成到分布式SQL物理查询计划树的URL。在生成的URL中的片段标识符(#)之后，将物理查询计划编码为字节字符串。片段没有发送到web服务器;相反，浏览器等待web服务器返回decode.html资源，然后web页面上的JavaScript将片段解码为物理查询计划图。因此，查询计划不会被InCloudZNBASE集群外部的服务器记录，也不会公开给公共internet。

• preparable_stmt

想要EXPLAIN的语句。基本任何的语句都可以和EXPLAIN组合使用，比如，CREARTE、INSERT、UPDATTE或者DELETE等等。

下面对各个参数的具体含义进行解释：

• 默认情况下

在这种情况下，EXPLAIN包含关于查询计划的最少细节（详情请参阅4.2.7.1.4示例1），但如果想要查询使用哪些索引和索引键的范围，这种用法很有用。详情请参阅4.2.7.1.4示例9。

• VERBOSE关键字

包括每个处理阶段设计的SQL表达式，提供关于查询的那个部分在每个级别上表示的更详细的信息。

包括关于每个级别使用哪些列的详细信息，以及该级别上结果的属性。

详情请参阅4.2.7.1.4示例2。

• TYPE关键字

该模式包括查询计划中使用的值的类型。 它还包括每个处理阶段涉及的SQL表达式，并包括每个级别使用的列。详情请参阅4.2.7.1.4示例3。

• OPT关键字

该选项显示基于成本优化器生成的查询计划树。详情请参阅4.2.7.1.4示例4。

• DISTSQL关键字

该选项为物理查询计划生成一个URL，该计划提供有关如何执行查询的高级信息。可参见4.2.7.1.4示例8中的内容。

上述字段除了可以单独使用外，还可以进行组合使用，下面对他们的组合情况进行说明：

• (OPT，VERBOSE)、

使用OPT和VERBOSE字段组合，显示计划查询优化程序使用的成本明细。可参见4.2.7.1.4示例5中的内容。

• (OPT，TYPES)

使用OPT和TYPES字段组合，显示花费和类型的详细信息。可参见4.2.7.1.4示例5中的内容。

• (OPT，ENV)

使用OPT和ENV字段的组合，显示优化程序使用情况的详请（包括统计信息）。可参见4.2.7.1.4示例7中的内容。

返回结果形式

执行成功的EXPLAIN语句将返回具有以下字段的表：

• Tree

查询计划的层次结构的树状表示形式。

• Field

查询计划的属性名称。分布式和向量化的属性适用于整个查询计划。所有其他属性都应用于树列中的查询计划节点。

• Description

有关字段中的参数的附加信息。

• Columns

提供给层次结构较低层的流程的列。包含在类型和详细的输出中。

• Ordering

结果在层次结构的每一层呈现给流程的顺序，以及结果集在每一层的其他属性。该字段包含在使用TYPES和VERBOSE选项的输出结果中。

语法示例

示例1：不使用任何参数的EXPLAIN语句：

> EXPLAIN SELECT * FROM accounts WHERE id > 2 ORDER BY balance DESC;

tree | field | description

+—————-+————+—————————+

sort |

| │ | order | -balance └── scan | | | table | accounts@primary | spans | ALL | filter | id > 2

1. rows)

示例2：使用VERBOSE选项的EXPLAIN语句：

1. rows)

示例3：使用TYPES选项的EXPLAIN语句：

1. rows)

示例4：使用OPT选项的EXPLAIN语句：

1. rows)

示例5：使用(OPT，VERBOSE)组合的EXPLAIN语句：

1. ows)

示例6：使用(OPT，TYPES)组合的EXPLAIN语句：

1. ows)

示例7：使用(OPT，ENV)组合的EXPLAIN语句：

1. ows)

示例8：使用DISTSQL选项的EXPLAIN语句：

结果如下图所示：

示例9：查找查询使用的索引和键的范围：

可以使用默认情况下的EXPLAIN语句，来获取查询使用了哪些索引和键的范围，从而可以帮助判定查询是否进行了全表范围的扫描。

首先，按照如下命令创建表explain_test：

由创建表的过程可知，v没有对列进行索引，因此，单独对其进行筛选的查询将会扫描整个表：

由上面的情况及返回的结果可以看出对全表进行了扫描；

现在在v上创建一个索引，在进行同样的操作：

由结果可以发现，为表创建索引之后，可以避免全表扫描的情况的发生。

EXPLAIN ANALYZE

EXPLAIN ANALYZE语句执行SQL查询，并为带有执行统计信息的物理查询计划生成URL。查询计划提供有关SQL执行的信息，这些信息可用于弄清时间花费在哪里，处理器（即，根据输入规范处理输入行流并对其进行处理的组件）多久没有运行，从而解决慢速查询的问题。

语法格式

其语法格式如下：

在InCloud ZNBASE中EXPLAIN ANALYZE又可以被称为EXPLAIN ANALYSE。下面对其参数进行说明。

参数说明

• DISTSQL

（默认）生成到分布式SQL物理查询计划树的链接。

• preparable_stmt

要进行EXPLAIN ANALYSE操作的语句。基本任何的语句都可以和EXPLAIN ANALYSE组合使用，比如，CREARTE、INSERT、UPDATTE或者DELETE等等。

返回结果

• 执行成功的EXPLAIN ANALYSE语句返回结果的各参数的说明：

• automatic

如果为true，则查询是分布式的。

• url

为物理查询计划生成的URL，提供有关如何执行查询的高级信息。

• DistSQL计划查看器

它是用来显示物理查询计划以及执行统计信息，下面是其结果相关变量属性的说明：

• <ProcessorName>/<n>

用于将数据读入SQL执行引擎的处理器和处理器ID。处理器是获取输入行流，根据规范对其进行处理并输出一个行流的组件。 例如，“聚合器”聚合输入行。

• <index>@<table>

使用的索引。

• Out

输出的列。

• @<n>

一个常数，列相对于输入的索引。

• Render

呈现输出的阶段。

• unordered / ordered

一种同步器，它接收一个或多个输出流，并将它们合并以供处理器使用。有序同步器用于合并有序流，并使行保持排序顺序。

• left(@<n>)=right(@<n>)

连接中使用的相等列。

• rows read

处理器读取的行数。

• stall time

处理器不工作的时间。随着查询沿树向下进行，这被汇总为停顿时间数（即，停顿时间加起来并与先前时间重叠）。

• stored side

存储为内存哈希表的较小表。

• max memory used

多少内存（如果有）用于缓冲行。

• by hash

路由器，一种组件，它接受一个输入行流，并根据路由算法将其发送到节点。例如，哈希路由器对行的各列进行哈希处理，然后将结果发送到正在汇总结果行的节点。

• max disk used

多少磁盘（如果有）用于缓冲行。 如果没有足够的内存来缓冲行，则路由器和处理器将溢出到磁盘缓冲中。

• rows routed

路由器发送了多少行，可用于了解网络使用情况。

• bytes sent

发送的实际字节数（即行的编码）。 这仅在进行网络通信时才有意义。

• Response

响应返回给客户端。

语法示例

EXPLAIN ANALYZE 执行查询并生成具有执行统计信息的物理查询计划的链接：

注意：在使用EXPLAIN ANALYSE时首先要执行下面的命令，以确保分布式SQL可用

结果如下图所示：

如果对表中的各参数的含义不清楚，请参阅4.2.7.3章节中关于DistSQL计划查看器的相关介绍。

EXPLAIN在SQL优化中的使用

本小节中，对几种引起SQL语句执行缓慢的常见原因进行了分析，并在分析的过程中使用EXPLAIN作为工具，帮助说明执行的过程。

全表扫描

全表扫描是导致查询速度慢的最常见原因，可以是SELECT语句设计不妥，也可以是索引使用不正确引起的。当基于不在主键或任何辅助索引中的列检索单行时，通常会得到较差的性能：

1. rows)

Time: 4.115095ms

想要了解为什么该查询的性能较差，可使用EXPLAIN，可参见4.2.7.1章节的内容。

1. rows)

表格中各部分的参数说明如下：

• table | accounts @ primary

表示用于扫描表（accounts）的索引（）。

• spans | ALL

在type列上没有二级索引的情况下，InCloud ZNBASE扫描用户表的每一行（按主键（id）排序），直到找到具有正确名称值的行。

• filter

筛选的条件。在这里为type = ‘savings’。

• 解决的办法：引进二级索引进行筛选，下面对其进行说明：

在type上添加索引,进行SELECT操作，发现性能提升SQL执行的时间缩短。

再次使用EXPLAIN查看新的查询计划：

从表中的数据可以看出InCloud ZNBASE首先从以二级索引（accounts @ accounts_type_idx）开始进行检索。因为它是按名称排序的，所以查询可以直接跳转到相关值（/“savings”-/“savings”）。由于查询仍需要返回不在辅助索引中的值，所以InCloud ZNBASE获取与type的值存储在一起的主键(id)(主键与二级索引中的数据条目总是一起存储)，跳到主索引中的值，然后返回完整的行。

由于accounts表很小(小于64 MiB)，主索引和所有二级索引都包含在一个单一的范围内，该范围只有一个承租人。但是，如果表更大，主索引和次索引可以位于不同的范围内，每个范围都有自己的租赁者。在这种情况下，如果租赁者在不同的节点上，查询将需要更多的网络跃点，从而进一步增加延迟。

• 解决方法：通过存储其他列的二级索引进行筛选

当存在需要对特定列进行筛选的查询，但是检索表总列的一个子集时，可以通过将这些附加列存储在辅助索引中来提高性能，从而避免查询也需要扫描主索引。

下面通过一个示例来进行说明：

假设需要经常检索name和type属性，即使已经存在type上的二级索引，检索的效率仍不是特别的高，因为name字段仍存在主表上，检索时仍需要访问一次主表。

1. rows)

重新建立索引，将name字段也储存在该索引中：

连接来自不同表的数据

当连接来自不同表的数据时，如果没有设计好SELECT语句也会造成性能的不佳，下面通过一个例子来进行说明：

例如，假设想要统计某一天开始骑行的用户数量。为此，需要使用JOIN从rides表获取相关的rides，然后将每个rides的rider_id映射到users表中的相应id，每个映射只计算一次:

从下往上读，可以看到InCloudZNBASE首先执行一次全表扫描，以获取指定范围内的所有start_time的行，然后对users执行另一次全表扫描，以查找匹配的行并将结果返回。

考虑到连接的WHERE条件，rides的全表扫描特别浪费。

• 解决方法：根据WHERE存储联接键的条件创建二级索引

为了加快查询速度，可以在WHERE条件（rides.start_time）上创建一个二级索引，存储连接键（rides.rider_id）：

请注意，InCloud ZNBASE现在开始使用rides@rides_start_time_idx二级索引来检索相关rides，而无需扫描整个rides表。

低效的连接

哈希连接比查找连接的代价更大，并且需要更多的内存。因此，基于成本的优化器会尽可能使用查找连接。对于以下查询，基于成本的优化器无法执行查找连接，因为查询没有rides可用的表主键的前缀，因此必须读取整个表并搜索匹配项，这导致查询速度很慢查询：

• 解决方案：提供主键以允许查找连接，为了加快查询速度，可以通过提供主键，从而保证基于成本的优化程序能够执行查找连接而不是使用哈希连接：

JOIN

连接类型

JOIN的类型如下面语法图所示：

参数join_outer的含义如下：

外连接关键字OUTER。OUTER关键字不是必须的，可以省略。由上图可知，连接可以分为外连接和内连接。而外连接又可以分为三种：左外连接、右外连接和全外连接。

假设存在两个表A与B，表A是左边的表，表B是右边的表。

• 内连接(INNER JOIN)

典型的连接运算。使用像=或 <>(不等号) 之类的比较运算符。包括相等连接和自然连接。内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索students和courses表中学生学号相同的所有行，即仅返回左侧和右侧操作数与条件匹配的行。可以理解为，返回的是表A与表B中的交集。

• 左外连接(LEFT OUTER JOIN 或 LEFT JOIN)

以左侧的表为主要，对于右侧没有匹配的左行，右侧的列返回NULL值。可以理解为，产生A的完全集，而B中匹配的则有值，没有匹配的则用NULL代替。

• 右外连接(RIGHT OUTER JOIN 或 RIGHT JOIN)

以右侧的表为主要，对于左侧没有匹配的右行，左侧的列返回NULL值。可以理解为，产生B的完全集，而A中匹配的则有值，没有匹配的则用NULL代替。

• 全外连接(FULL OUTER JOIN 或 FULL JOIN)

对于连接一侧没有匹配的每一行，将返回非匹配一侧的列的NULL值。可以理解为，产生A和B的并集。对于没有匹配的记录，则用NULL代替。

连接需要的条件

任意的连接形式(内连接、左外连接、右外连接或全连接)可以和任意的连接条件(自然连接、using条件连接或on条件连接)进行组合，其中USING条件连接和ON条件连接的语法图如下所示：

• JOIN … USING + (name_list)

join … using运算中需要给定一个属性名的列表即name_list,它的两个输入中都必须由指定名称的属性。将命名的列从左右行成对比较；如果两列成对相等，则认为左行与右行匹配。

举例说明如下：

> SELECT * FROM student JOIN takes USING(ID);

student 为学生的信息所在的表，ID为学生的学号;

takes 为学生选课信息所在的表，ID为选择某一门课程的学生的学号。 |

• JOIN … ON + a_expr

对布尔标量表达式求值，以确定是否符合条件，从而决定是否执行后续的操作。

举例说明如下：

> SELECT * FROM student JOIN takes ON student.ID = take.ID;

student 为学生的信息所在的表，ID为学生的学号;

takes 为学生选课信息所在的表，ID为选择某一门课程的学生的学号。

• CROSS JOIN

没有任何条件。左侧的每一行都被认为与右侧的每一行都匹配。

举例说明如下：

> SELECT * FROM student CROSS JOIN takes;

student 为学生的信息所在的表，ID为学生的学号;

takes 为学生选课信息所在的表，ID为选择某一门课程的学生的学号;

对两个表进行笛卡尔乘积，即将左表中的每一行都“乘”右表中的每一行。 |

• NATURAL

USING使用左表表达式和右表表达式中都存在的所有列名生成隐式条件。

举例说明如下：

> SELECT * FROM student NATURAL JOIN takes;

student 为学生的信息所在的表，ID为学生的学号;

takes 为学生选课信息所在的表，ID为选择某一门课程的学生的学号;

两个表中都有学生的学号，会将两个表中学生学号相等的行作为结果返回。

连接策略

InCloud ZNBASE支持以下执行连接的算法:

合并连接(Merge joins)、散列连接(Hash joins)、查询连接(Lookup joins)。

合并连接：

​ 要执行两个表的合并联接，必须在相等列上对两个表建立索引，并且任何索引必须具有相同的顺序。与散列连接相比，合并连接提供更好的计算性能和更有效的内存使用。当为合并排序表和索引时，InCloud ZNBASE默认选择使用合并连接而不是散列连接。当不满足合并条件时，InCloud ZNBASE会借助于较慢的散列连接。合并连接只能在分布式查询处理中使用。

​ 合并连接作用在两个表的索引的列上，具体的操作过程如下:

1. InCloud ZNBASE检查相等列上的索引，并且它们的顺序是相同的。 ASC或DESC)。

2. InCloud ZNBASE从每个表中各取一行并对其进行比较。

• 对于内部连接:

如果行相等，InCloud ZNBASE返回行；如果有多个匹配项，则返回匹配项的笛卡尔积；如果行不相等，InCloud ZNBASE将丢弃低值行，并在下一行重复此过程，直到处理所有行。

• 对于外部连接:

如果行相等，InCloud ZNBASE返回行；如果有多个匹配项，则返回匹配项的笛卡尔积；如果行不相等，InCloud ZNBASE对不匹配的列返回NULL，并在下一行重复这个过程，直到处理完所有行。

散列连接：

​ 如果不能使用合并连接，则InCloud ZNBASE将使用散列连接。散列连接在计算的花销上非常大，并且需要额外的内存。

Hash连接作用在两个表上，具体的操作过程如下:

1. InCloud ZNBASE读取两个表并尝试选择较小的表；

2. InCloud ZNBASE在较小的表上创建内存中的散列表。如果哈希表太大，就会溢出到磁盘存储(这可能会影响性能)。

3. InCloud ZNBASE扫描大表，查找散列表中的每一行。

查询连接：

​ 基于成本的优化器决定何时使用查找连接是有益的。当两个表的尺寸之间存在较大的不平衡时，就会使用查找连接，因为它只读较小的表，然后在较大的表中查找匹配的项。查找连接要求再等式列上建立起右边的表(较大的表)的索引

查找联接作用在两个表上，具体的操作过程如下:

1. InCloud ZNBASE读取小表中的每一行；

2. InCloud ZNBASE扫描(或“查找”)较大的表，查找与较小的表匹配的行，并输出匹配的行。

JOIN表达式

JOIN表达式，也称为连接表达式，连接是一种特殊的表表达式。其根据特定列(也即相等的值的条件)组合两个或多个表表达式的结果。连接表达式在简单选择子句FROM子中定义数据源(可参见4.2.6.1章节)，或作为TABLE的参数，如下图语法格式所示：

JOIN表达式的相关参数说明如下：

• joined_table

另一个连接操作的表达式。

• table_ref

一个表表达式。可参见4.2.6.4章节中关于表表达式的说明。

• a_expr

用作ON连接条件的标量表达式。想要了解ON连接条件的相关内容，可参见4.2.8.1章节中关于ON连接条件的说明。

• name

用作USING连接条件的列名。想要了解USING连接条件的相关内容，可参见4.2.8.1章节中关于USING连接条件的说明。

集合运算

​ SQL作用在关系上的union、intersect和expect运算对应于数学集合论中的∪、∩和-运算。在SELECT语句中涉及到集合关键字的应用，其与SELECT子句组合使用组成集合操作子句。其语法格式如下：

其参数说明如下：

• select_clause

SELECT子句。其返回值为一个结果集，因此可以对两个SELECT子句进行UNION、INTERSECT 或者EXCEPT这样的集合运算。

• all_or_distinct

关键字ALL、DISTINCT或者不写。当其为ALL时，会保存集合运算所得结果中重复的元组；当其为DISTINCT时，会去除集合运算所得的结果中的重复的元组；当不指定的时候，等效于DISTINCT，会去除集合运算所得结果中的重复的元组。

下面结合一个具体的示例对它们的用法进行说明。

首先，创建了一个数据库，创建建表section的语句及插入的元素如下：

>CREATE TABLE section(ID INT primary key,course_id TEXT, sec_id INT,semester TEXT,year INT);

> SELECT * FROM section; id | course_id | sec_id | semester | year

+——+—————-+————+—————+———+

1 | BIO-301 | 1 | Summer | 2010

2 | BIO-101 | 1 | Summer | 2009

3 | CS-101 | 1 | Fall | 2009

4 | CS-101 | 1 | Spring | 2010

5 | CS-190 | 1 | Spring | 2009

6 | CS-190 | 2 | Spring | 2009

7 | CS-315 | 1 | Spring | 2010

8 | CS-319 | 1 | Spring | 2010

9 | CS-319 | 2 | Spring | 2010

10 | CS-347 | 1 | Fall | 2009

11 | EE-181 | 1 | Spring | 2009

12 | FIN-201 | 1 | Spring | 2010

13 | HIS-351 | 1 | Spring | 2010

14 | MU-199 | 1 | Spring | 2010

15 | PHY-101 | 1 | Fall | 2009

16 | ECE-101 | 1 | Fall | 2009

17 | ECE-101 | 2 | Fall | 2009

18 | ECE-101 | 3 | Fall | 2009

19 | ECE-101 | 4 | Fall | 2009

20 | ECE-101 | 1 | Spring | 2010

21 | ECE-101 | 2 | Spring | 2010

(21 rows) |

接着，构建下面两个集合：

• 在2009年秋季学期开设的所有课程的集合：

> SELECT course_id FROM section WHERE semester = ‘Spring’ and year = 2010；

course_id

+—————-+

CS-101

CS-315

CS-319

CS-319

FIN-201

HIS-351

MU-199

(7 rows) |

• 在2009年秋季学期开设的所有课程的集合：

> SELECT course_id FROM section WHERE semester = ‘Fall’ and year = 2009;

course_id

+—————-+

CS-101

CS-347

PHY-101

(3 rows) |

UNION

UNION称为并运算。与SELECT子句不同，UNION运算自动去除重复。

下面给出一个示例：

找出在2009年秋季开课，或者在2010年春季开课或两个学期都开课的所有课程写出下面的语句：

> ( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 ) UNION ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 );

course_id

+—————-+

CS-101

CS-315

CS-319

FIN-201

HIS-351

MU-199

CS-347

PHY-101

(8 rows) |

由图中可见，得到的结果中没有重复的元素，如果我们想要保留所有的结果（含重复的），就必须用UNION ALL代替UNION：

>( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 ) UNION ALL ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 );

course_id

+—————-+

CS-101

CS-347

PHY-101

CS-101

CS-315

CS-319

CS-319

FIN-201

HIS-351

MU-199

(10 rows) |

不难看出，此时已经返回的结果集中已经包含了重复的元素。

下面给出一个拓展的例子：

已知，ECE-101在2009年秋季学期开设4个课程，在2010年春季学期开设2个课程，找出在2009年秋季开课，或者在2010年春季开课或两个学期都开课的所有课程：

使用UNION ALL时，可以发现，返回的结果中有6个 ECE-101元组：

>( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 ) UNION ALL ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 );

course_id

+—————-+

…

ECE-101

ECE-101

ECE-101

ECE-101

ECE-101

ECE-101

…

使用UNION时，返回的结果中只有1个 ECE-101元组。而且，返回集的行数也比使用UNION ALL时少了，证明去掉了重复的行：

>( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 ) UNION ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 );

course_id

+—————-+

…

ECE-101

…

(9 rows)

INTERSECT

​ 交运算。INTERSECT运算自动去除重复的元组。如果想要保留所有的重复，就必须用INTERSECT ALL代替INTERSECT。

​ 下面给出一个示例：找出在2009年秋季和2010年春季同时开课的所有课程：

写出下面的语句：

> ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 ) INTERSECT ALL ( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 );

course_id

+—————-+

CS-101

(1 row) |

下面给出一个拓展的例子：

已知，ECE-101在2009年秋季学期开设4个课程，在2010年春季学期开设2个课程，找出在2009年秋季和2010年春季同时开课的所有课程：

使用INTERSECT ALL时，返回的结果中有2个 ECE-101元组:

> ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 ) INTERSECT ALL ( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 );

course_id

+—————-+

CS-101

ECE-101

ECE-101

使用INTERSECT时，返回的结果中只有1个 ECE-101元组，而且，返回集的行数也比使用INTERSECT ALL时少了，证明删除了重复的行:

>( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 ) INTERSECT ( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 );

course_id

+—————-+

CS-101

ECE-101

(2 rows

EXCEPT

差运算,EXCEPT运算从其第一个输入中输出所有不出现在第二个输入中的元组，也即它执行差集操作。此运算在执行差集操作之前自动去除输入中的重复的元组。

下面给出一个示例：

找出在2009年秋季学期开课但不在2010年春季学期开课的所有课程

写出下面的语句：

> ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 ) EXCEPT ( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010);

course_id

+—————-+

CS-347

PHY-101

(2 rows) |

下面给出一个拓展的例子：

已知，ECE-101在2009年秋季学期开设4个课程，在2010年春季学期开设2个程，找出在2009年秋季开设的课程而不在2010年春季开课的所有课程：

使用EXCEPT ALL时，返回的结果中有2个 ECE-101元组。

> ( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 ) EXCEPT ALL ( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 );

course_id

+—————-+

CS-347

PHY-101

ECE-101

ECE-101

EXCEPT时，返回的结果中没有 ECE-101元组。 而且，返回集的行数也比使用EXCEPT ALL时少了，证明删除了重复的行。

>( SELECT course_id FROM SECTION WHERE semester = ‘Fall’ AND YEAR = 2009 ) EXCEPT ( SELECT course_id FROM SECTION WHERE semester = ‘Spring’ AND YEAR = 2010 );

course_id

+—————-+

CS-347

PHY-101

DUMP

本章对InCloud ZNBASE的DUMP功能进行讲解。

Dump包含两部分功能：

• 数据导出，例如语法格式和参数。

• 数据备份，例如语法格式和参数。

语法格式

InCloud ZNBASE中使用的dump语法功能，它包括数据的导出（CSV文件）和数据备份。

Dump总体语法树如下：

该语法树分为两部分：

• CSV数据导出

DUMP TO CSV file_location FROM select_stmt opt_with_options; DUMP TO CSV file_location FROM TABLE table_name opt_with-options;

• 数据备份

DUMP target TO SST file_location opt_as_of_clause opt_incremental opt_with_options; |

数据导出

CSV文件格式的数据导出。

语法格式

参数说明

• Dump_format

指定数据导出的格式，数据导出时该参数只能是CSV。

• file_location

指定数据导出的路径,由于file_location可以使用第三方存储，包括aws, aurze,googlecloud，NFS等。但一般情况下使用NFS, 即nodelocal:///存储在本地。

注：

1．如果使用nodelocal在多节点的集群中进行存储备份文件，数据文件将会存储在集群中任意节点的数据文件下的extern目录里。则会造成存储备份文件节点的数据文件与其它节点不一致。所以在集群中备份时，需要对每个节点启动服务时，加参—external-io-dir=绝对路径，让集群里每个节点有相同的NFS挂载点去存储备份文件。

2．然而—external-io-dir指定的位置参数通常会包含需要URI-encoded的特殊字符。该情况下，使用Javascript的encodeURIComponent函数或者Go语言的url来处理。QueryEscape函数对参数进行uri编码。其它语言类似。

3．如果用户环境需要HTTP或者HTTPS代理服务去付出连接，则在启动ZNBase服务时，可以设置标准的HTTP_PROXY和HTTPS_PROXY环境变量。

可用file_location如下表。

• select_stmt

指定要导出的Query查询语句的结果集。Select语句可以加where条件和join。使导出符合需要的数据集。

• Table_name

指定要导出的表名。表示导出整张表数据。相当于select_stmt的select * from table。

• opt_with_options

CSV数据导出的参数。如下表所示。

注意：当数据导出时，with nullas=value 是指定NULL关键字导出为value,指定时，则将NULL关键字导出为指定字符串；当未指定时，NULL关键字则导出为‘\N’，数据库中的‘\N’则导出为‘\\N’（按照转义规则以此类推）。

• 导出csv文件名规则

Drdb导出csv文件名是自动生成的，不可指定，格式为——节点号.编号.文件类型。

如示例情况，一般从n1.0.csv文件名开始。n1表示ZNBase集群中第一个节点，0表示导出csv的第一个文件，编号从0开始计数。数据导出单个csv文件默认大小为10万条数据，当导出的表或者query查询数据超过10万条时，会自己分割多个csv文件存储。比如在节点2导出的表有100万条数据。则dump导出csv，就会生成10个csv文件，文件名分别为n2.0.csv, n2.1.csv, n2.2.csv, ……., n2.9.csv。每个文件存储10万条数据。

语法示例

示例1：导出一个表:

导出表defaultdb.test数据到目录nodelocal:///csv下，文件名为n1.0.csv。csv文件名是ZNBase自动生成的。不可指定csv文件名。n1表示第一个节点，0表示导出csv文件的编号，即第一个csv文件。数据行数是7，大小14byte。目录nodelocal见参数说明file_location。

> DUMP TO CSV “nodelocal:///csv“ FROM TABLE defaultdb.test;

filename | rows | bytes

+—————+———+———-+

n1.0.csv | 7 | 14

(1 row) Time: 14.08ms |

示例2：使用SELECT导出：

导出select * from test where id <6的结果集到nodelocal下csv目录中，csv文件名为n1.0.csv。数据行数是5，大小10byte。nodelocal见file_location参数说明。

> DUMP TO CSV “nodelocal:///csv” FROM SELECT * FROM test WHERE id < 6 WITH delimiter = ‘|’;

filename | rows | bytes

+—————+———+———-+

n1.0.csv | 5 | 10

(1 row) Time: 12.379ms |

示例3：通过非分布式导出，导出SQL shell脚本：

该方式是用命令导出数据到csv文件中。此处是导出select * from defaultdb.test结果集存储到my.csv里，format指定导出文件格式

$ ./bini sql —insecure -e “SELECT * FROM defaultdb.test;” —format=csv > my.csv |

示例4：使用encryption_passphrase对表进行加密备份，备份后生成文件均为加密文件，无法直接查看或进行导入。

> DUMP TO CSV “nodelocal:///1” FROM TABLE data.t1 WITH encryption_passphrase=”abcdefg”;

filename | rows | bytes

+—————+———+———-+

n1.0.csv | 4 | 52

(1 row) Time: 62.746809ms |

数据备份

语法格式

​ 备份作用于表的外键约束，表序列，视图，相互依赖的表，分为全量备份和增量备份。

全量备份：

​ 全量备份的文件的大小与实际的数据大小差不多，并且需要比增量备份更多的资源。可以对给定的时间戳进行完全备份，并(可选地)包含可用的

增量备份：

​ 增量备份比完整备份更小，生成速度更快，因为增量备份只包含用户指定的一组基本备份(其中必须包含一个完整备份，并且可以包含多个增量备份)以来更改的数据。用户可以根据给定的时间戳或revision history进行增量备份。

限制：

1. 增量备份只能在全量备份的最近时间戳的垃圾回收期间之前创建。这是因为增量备份是通过查找自全量备份中最近的时间戳为依据为已经创建或修改的数据来创建的。垃圾回收处理期间，该时间戳可能会被删掉,可以通过参数ttlseconds设置垃圾回收时间。

2. 如果一个或者很多表在上次的全量备份后被create，drop，truncate，在这种情况下没有必要再去增量备份。因为增量备份多数针对表的数据的修改变化而备份，当整张表或者多张表级的数据被操作时，应该去做新的全量备份，而不是增量备份。

3. 目前模式的备份还原操作不支持根据给定时间戳以及revision history等功能

建议：

​ 备份过程通过将工作分配给所有节点，将其对集群性能的影响降到最低。每个节点只备份它存储数据的一个特定子集，不会有两个节点备份相同的数据。所以，为了最佳性能，建议总是使用特定的时间戳(至少10秒)启动备份。

例如:

DUMP … AS OF SYSTEM TIME ‘-10s’;

​ 由于备份可能会与其它语句和事务产生竞争，所以我们通过这种方式来降低这种竞争可能性来提高性能。然而，由于as of system time’-10s’可能返回历史数据，导致备份时读取的数据可能是已经过时的。

​ 建议每天对集群cluster进行自动化备份。需要客户端client需要发送dump语句到cluster。每次备份完成后，client将会收到备份响应。

​ 当ZNBase初始化完一个备份时，它将启动一个job去执行dump，可以通过show jobs查看备份情况。也可以通过pause job, resume job, cancel job来控制备份。

参数说明

• targets

指定备份的表、模式或者库名

• dump_format

指定备份的文件格式，备份时该参数只能是SST。

• file_location

指定备份的路径。可用方式如下表。

• opt_as_of_clause

指定以哪个时间戳进行备份。即备份基于时间戳的数据版本。时间戳离当前系统时间的间隔必须比集群的最后一次垃圾回收离当前系统时间间隔更小。固定语法AS OF SYSTEM TIME。测试时间戳可用select cluster_logical_timestamp();

> SELECT cluster_logical_timestamp();

cluster_logical_timestamp

+————————————————+

1574166258732738293.0000000000

(1 row) Time: 945.743µs

即可以用

DUMP TABLE test TO SST “nodelocal:///test” AS OF SYSTEM TIME [SELECT cluster_logical_timestamp()];

• opt_incremental

指定增量备份。固定语法是INCREMENTAL FROM，后跟全量备份做为增量备份的基础或者全量加增量备份做为多级增量备份的基础数据。例如：dump database test to sst “nodelocal:///foo”;先进行全量备份。dump database test to sst “nodelocal:///foo/inc” incremental from “nodelocal:///foo”;再进行增量备份。Nodelocal:///foo/inc是增量备份。Nodelocal:///foo是全量备份。多级备份见语法示例4。

• opt_with_options

可用参数revision history。该参数用于基本时间戳的历史版本数据的备份。

例如：DUMP table … TO SST … WITH revision_history。

关于参数revision history的备份：

使用revision history进行全量备份，可以备份在垃圾回收期间引起的所有的修改，包括指定的时候戳。

使用revision history进行增量备份，可以备份自上次备份以来，在垃圾回收期间（包括指定的时间戳之前）所做的每一个更改。即使以前的全量备份或增量备份没有加revision history参数，也可以revision history进行增量备份。

可用参数encryption_passphrase。该参数用于备份时给备份文件加密。

例如：DUMP table … TO SST … WITH encryption_passphrase=”abcdefg”。

使用encryption_passphrase对备份文件进行加密后，不使用密码无法查看和还原该备份文件。

语法示例

示例1：备份一张表：

​ 备份表test到sst文件格式到nodelocal:///f1路径下。Job_id是备份的job编号，status是备份的状态。Fraction_completed是备份程度。Succeeded是已经完成，所以fraction_completed是1，failed是0。如果备份状态是running，则fraction_completed是[0,1]数字，即0<fraction_completed<1。

​ Status表示job的状态，分别是pending, running, paused, failed, succeeded,canceled。运行状态有三种draining names, waiting for GC TTL, RocksDB compaction。rows表示备份的数据有4行。index_entries是备份数据的索引数。系统记录是0。备份大小106bytes。

> DUMP TABLE TEST TO SST “nodelocal:///f1“;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

504868487008911361 | succeeded | 1 | 4 | 0 | 0 | 106

(1 row) Time: 140.795ms |

示例2：增量备份：

​ 增量备份表示在原来全量备份的基础上，备份出已经修改过的表数据。例如此例对表test删除2条数据，插入2条数据，所以rows是4。大小82bytes。Nodelocal:///f1是全量备份文件路径，nodelocal:///f1/INC是增量备份路径。

> DUMP TABLE TEST TO SST “nodelocal:///f1/INC” INCREMENTAL FROM “nodelocal:///f1”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

504878667702337537 | succeeded | 1 | 4 | 0 | 0 | 82

(1 row) Time: 65.568ms

示例3：基本时间戳的备份：

​ 时间戳为负数表示备份之前的数据副本。不可为正数，因为正数表示备份未来的时间戳，但该时间戳会大于当时备份时系统的timestamp报错。

DUMP TABLE defaultdb.test TO SST “nodelocal:///f1/TIME“ AS OF SYSTEM TIME ‘-1s‘;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

504879798109405185 | succeeded | 1 | 6 | 0 | 0 | 164

(1 row) Time: 69.611ms |

示例4：基于时间戳的增量备份的第二次增量备份：

​ 表test有四条数据。nodelocal:///test是全量备份。nodelocal:///time/inc是基于时戳之前1秒相对于全量备份nodelocal:///test的增量备份。nodelocal:///time/inc2是基于时间戳，相对于全量备份nodelocal:///test和增量备份nodelocal:///time/inc的第二次增量备份。

注：不建议多层级的增量备份，如果增量备份的层次越高，还原时也要写相应的多级备份文件路径，会增加还原的复杂度。

> select * from test; id | name

+——+——————+

1 | zcvx

2 | asdfg

3 | zzzzz

9 | qqeweqeqwe

> dump table test to sst “nodelocal:///time/inc” as of system time ‘-1s’ incremental from “nodelocal:///test”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

505384033024311297 | succeeded | 1 | 7 | 0 | 0 | 117

> dump table test to sst “nodelocal:///time/inc2” as of system time ‘-1s’ incremental from “nodelocal:///test”, “nodelocal:///test/inc”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

505384207086092289 | succeeded | 1 | 5 | 0 | 0 | 93 (1 row) Time: 68.037ms |

示例5：使用encryption_passphrase对数据库进行备份，备份后生成文件均为加密文件，无法直接查看或进行导入。

> DUMP DATABASE data TO SST “nodelocal:///test_file” WITH encryption_passphrase=”abcdefg”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

589771061973319681 | succeeded | 1 | 6 | 0 | 0 | 144

(1 row) Time: 99.837587ms

WITH

​ 本章对WITH语法的作用和使用方法进行说明。WITH提供了一种方式来书写在一个大型查询中使用的辅助语句。这些语句通常被称为公共表表达式或CTE，它们可以被看成是定义只在一个查询中存在的临时表。在WITH子句中的每一个辅助语句可以是一个SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一个主语句，主语句也可以是SELECT、INSERT、UPDATE或DELETE。

语法格式

WITH语法的语法格式如下：

添加RECURSIVE代表该语句是一条递归WITH查询。一个递归WITH查询的通常形式总是一个非递归项，然后是UNION（或者UNION ALL），再然后是一个递归项，其中只有递归项能够包含对于查询自身输出的引用。递归查询通常用于处理层次或者树状结构的数据。

参数说明

• table_alias_name

  为大型查询定义的新表名，仅限于该语句内使用

• name

  为大型查询定义的新列名

• preparable_stmt

  大型查询语句，或者查询语句结果间的union等

• insert_stmt

  详见dml语句章节

• update_stmt

  详见dml语句章节

• delete_stmt

• upsert_stmt

  详见dml语句章节

• select_stmt

  详见dml语句章节

示例

普通WITH语句

>Select * from test

2

4

>with table_a as (select * from test) select * from table_a;

-—-

2

4

递归WITH查询

我们将创建一个新表来演示递归查询。

>CREATE TABLE employees ( employee_id serial PRIMARY KEY, full_name VARCHAR NOT NULL, manager_id INT );

该表有三列：employee_id,full_name,manager_id分别为员工ID，员工姓名和员工的经理的ID。

以下语句将示例数据插入到表中：

>INSERT INTO employees ( employee_id, full_name, manager_id ) VALUES ( 1, ‘Michael North’, NULL ), ( 2, ‘Megan Berry’, 1 ), ( 3, ‘Sarah Berry’, 1 ), ( 4, ‘Zoe Black’, 1 ), ( 5, ‘Tim James’, 1 ), ( 6, ‘Bella Tucker’, 2 ), ( 7, ‘Ryan Metcalfe’, 2 ), ( 8, ‘Max Mills’, 2 ), ( 9, ‘Benjamin Glover’, 2 ), ( 10, ‘Carolyn Henderson’, 3 ), ( 11, ‘Nicola Kelly’, 3 ), ( 12, ‘Alexandra Climo’, 3 ), ( 13, ‘Dominic King’, 3 ), ( 14, ‘Leonard Gray’, 4 ), ( 15, ‘Eric Rampling’, 4 ), ( 16, ‘Piers Paige’, 7 ), ( 17, ‘Ryan Henderson’, 7 ), ( 18, ‘Frank Tucker’, 8 ), ( 19, ‘Nathan Ferguson’, 8 ), ( 20, ‘Kevin Rampling’, 8 );

以下一个递归查询返回ID为2的经理的所有下属员工：

>WITH RECURSIVE subordinates AS ( SELECT employee_id, manager_id, full_name FROM employees WHERE employee_id = 2 UNION SELECT e.employee_id, e.manager_id, e.full_name FROM employees e INNER JOIN subordinates s ON s.employee_id = e.manager_id ) SELECT * FROM subordinates;

LOAD

​ 本章对InCloud ZNBASE的数据导入和还原的使用方法进行说明：

1. 数据导入，例如语法和参数；

2. 数据还原，例如语法和参数；

语法格式

​ 这部分主要介绍在InCloud ZNBASE中使用的load功能，包括数据的导入（CSV文件，PGDUMP文件，MySQLDUMP文件）和数据的还原。

Load语法格式如下：

LOAD语法格式分为两部分组成:

数据导入功能

​ 1. CSV/TSV文件导入

1. Postgres dump 文件

2. MySQL dump 文件

3. ZNBASE dump 文件

数据还原功能

注意：Load数据导入不能用在事务中使用。

权限需求：

​ Load功能需要管理角色才能执行Load，root用户默认属于admin角色。导入数据时，集群中所有节点的资源除了提供正常的服务以外，将会被导入功能消耗所有节点部分的CPU和RAM资源。在导入数据时，每个节点都会被分配相等部分的数据导入，所以必须有足够的临时空间去存储。此外，数据作为普通表被持久化。因此要有足够的空间来保存最终的数据副本。节点的第一个store或者默认的store存储目录，必须有足够的可用存储空间来保存其部分数据。在bini start时，如设—max-disk-temp-storage，则指定的目录必须足够大，以便存储节点存储该数据的临时空间。

整体参数说明

• table_name

指定表名，包含CSV/TSV数据导入，DUMP导入，数据还原。

• schema_name

指定表名，包含CSV/TSV数据导入，DUMP导入，数据还原。

• database_name

指定还原的数据库名。只在还原功能中用到。

• load_format

指定导入格式，可选参数有PGDUMP, MySQLDUMP。特殊说明在LOAD INTO 语法中可指定为CSV文件。

• file_location

指定数据导入的文件路径。CREATE USING file_location为表结构文件路径，load_format ( file_location )为dump数据文件路径。

• talbe_elem_list

导入表结构的列元素属性类型。

• file_location_list

CSV/TSV数据文件路径

• full_dump_location

全量备份文件路径，此处load用于还原。

• incremental_dump_location

增量备份文件路径，此处load用于还原。

• timestamp

指定基于时间戳的还原。

• kv_option_list

load语法附带参数，根据具体使用业务场景而定。

注意：有关导入导出NULL关键字处理如下

当数据导入时:

with nullif=value是指定值为value的字符串识别为NULL关键字，指定时，则识别指定的字符串为NULL关键字。

当未指定时，则默认识别‘\N’为NULL关键字。当输入类似于‘\N’,‘\\N’时，应用转义的处理方式，也即输入‘\N’,则需要按照转义规则输入‘\\N’，以此类推。

CSV/TSV导入

​ CSV和TSV数据文件格式导入功能。

语法格式

​ 向已存在表中导入数据，使用LOAD INTO …语法，LOAD INTO针对有主键的表默认采用INSERT的形式导入数据，若存在主键，则无法插入，提示主键冲突；不存在主键时，将会直接插入数据,当表不存在时，CSV数据导入，建表有两种方式。一种用脚本建表CREATE USING，一种直接指定导入表结构。

语法示例：

LOAD TABLE TEST CREATE USING “nodelocal:///table/test.sql” CSV DATA (“nodelocal:///csv/n1.0.csv”) WITH nullif = ‘’;

LOAD TABLE TEST (id INT, name VARCHAR, primary key(id)) CSV DATA(“nodelocal:///csv/n1.0.csv”) WITH OPTIONS skip = ‘1’;

参数说明

• table_name

指定导入表的名称。用例表名是TEST。

• file_location

指定建表脚本文件路径和文件名。要求表结构脚本中表名和table_name一致，列数到csv数据的列数一致，列类型属性满足csv数据导入需要。用例中是nodelocal:///table/test.sql。

• table_elem_list

指定导入表的列元素属性。用例中是id INT, name VARCHAR, primary key(id)。

• load_format

指定文件格式，在此处为CSV。

• insert_column_list

指定将数据导入的列。

• file_location_list

指定导入数据文件路径和文件名。用例中是nodelocal:///csv/n1.0.csv。其它类型的file_location_list如下表所示。

说明：

默认的full_location_list是在ZNBase-data文件里extern目录里存放。但在集群时，由于某节点备份和导出的数据文件在ZNBase-data，而导致该节点数据副本与其它节点不一致。例如当外部数据文件存放在集群中某一个节点的数据目录里的extern时，当还原和导入时，需要对集群中所有节点的数据目录下extern都要包含相应的数据文件，否则会报“其它节点数据目录下没有相应的数据文件。原因是还原节点的数据目录比集群中其它节点数据文件多了extern文件数据。

解决方案：

使用参—external-io-dir，此参数用在启动集群中节点服务中，写绝对路径。使用此参数时，需要集群中每个节点的启动参数都得加相应的—external-io-dir。如果只有其中一个节点有该参数，该结点的备份和导出目录为此参数指定的绝对路径。但其它节点刚是在cockraoch-data文件下extern，则会引起集群中节点的数据目录不一致。

• kv_option_list

指定数据导入时的参数。具体情况如下表。

语法示例

示例1：往表中导入一个CSV文件:

创建表customers，往表customers导入csv数据，该CSV数据文件存储amazon s3。

Amazon S3:

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME ) * )

CSV DATA (‘s3://acme-co/customers.csv?AWS_ACCESS_KEY_ID=[placeholder]&AWS_SECRET_ACCESS_KEY=[placeholder]&AWS_SESSION_TOKEN=[placeholder]‘); |

Azure存储CSV数据文件方式:

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME ))

CSV DATA (‘azure://acme-co/customer-import-data.csv?AZURE_ACCOUNT_KEY=hash&AZURE_ACCOUNT_NAME=acme-co’);

Google Cloud存储CSV数据文件方式:

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME ))

CSV DATA (‘gs://acme-co/customers.csv’); |

示例2：使用文件指定导入表的模式。

表结构脚本customers-create-table.sql在gs://acme-co下，csv文件customers.csv在gs://acme-co

LOAD TABLE customers CREATE USING ‘gs://acme-co/customers-create-table.sql’ CSV DATA (‘gs://acme-co/customers.csv’);

示例3：往表中导入多个CSV文件。

此方法是多个csv并发导入。当该表数据在于10万条以上，则会分割成多个csv文件，单个csv最大存储10万数据。则在该大表导入时，需要用多csv文件并发导入。

Google Cloud:

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME ))

CSV DATA ( ‘gs://acme-co/customers.csv’, ‘gs://acme-co/customers2.csv’, ‘gs://acme-co/customers3.csv’, ‘gs://acmeco/customers4.csv’,); |

示例4：往表中导入一个TSV文件。

导入tsv数据文件与csv方法一样。创建表customers结构，把gs://acme-co/customers.tsv数据导入到表customers中。

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME ) CSV DATA (‘gs://acme-co/customers.tsv’) WITH delimiter = e’\t’;

示例5：跳过注释行。

CSV文件里，被#标记的行数据不导入表customers中。

Google Cloud:

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME )) ) CSV DATA (‘gs://acme-co/customers.csv’) WITH comment = ‘#’;

示例6：跳过开始的n行数据。

使用skip参数表示导入数据时，跳过开始的n行数据。如skip=’2’则表示csv开始的前两行数据跳过，从第三行数据开始导入。

Google Cloud:

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME ))

CSV DATA (‘gs://acme-co/customers.csv’) WITH skip = ‘2’;

示例7：使用空字符串转换为null：

对于第三方数据库，像pg,mysql,db2,oracle，这些数据库导出csv文件格式中，对空字符或者null数据在csv里体现的形式不同，有些是空字符，有些是null。Null可以直接兼容，而空字符需要转换成null才可以用。所以此时使用nullif参数处理。

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME )) CSV DATA (‘gs://acme-co/customers.csv’) WITH nullif = ‘’;

示例8：导入压缩的CSV文件：

CSV文件是gs://acme-co/customers.csv.gz，往表customers里导入。

LOAD TABLE customers ( ID UUID PRIMARY KEY DEFAULT gen_random_uuid (), NAME TEXT, INDEX name_idx ( NAME ) ) CSV DATA (‘gs://acme-co/customers.csv.gz’);

示例9：向已存在表中导入CSV文件：

LOAD INTO 语法的WITH用法以及多CSV导入等等与LOAD 语法一致，因此不再进行赘述。

LOAD INTO customers CSV DATA (‘nodelocal:///n1.0.csv’);

示例10：导入加密文件：

LOAD TABLE data.s1 (c1 int,c2 string) CSV DATA (“nodelocal:///1/n1.0.csv”) WITH encryption_passphrase=”abcdefg”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

589819850206019585 | succeeded | 1 | 4 | 0 | 0 | 88

(1 row) Time: 81.415992ms

示例11：导入容错功能：

LOAD TABLE item CREATE USING ‘nodelocal:///item.sql’ CSV DATA (‘nodelocal:///itemRe.csv’,’nodelocal:///itemRe3.csv’,’nodelocal:///itemRe2.csv’,’nodelocal:///itemRe4.csv’) WITH save_rejected; job_id | status | fraction_completed | rows | index_entries | system_records | bytes | rejected_rows | rejected_address +——————————+—————-+——————————+———+———————-+————————+———-+———————-+———————————————————————+ 570581396938719233 | succeeded | 1 | 276 | 0 | 0 | 22206 | 25 | nodelocal:///rejected.570581396938719233.csv

LOAD TABLE item CREATE USING ‘nodelocal:///item.sql’ CSV DATA (‘nodelocal:///itemRe.csv’,’nodelocal:///itemRe3.csv’,’nodelocal:///itemRe2.csv’,’nodelocal:///itemRe4.csv’) WITH save_rejected,rejectrows=’20’; pq: “nodelocal:///itemRe4.csv”: row 44: parse “i_id” as INTEGER: too many parse errors,please check if there is a problem with the imported data file:

LOAD TABLE item CREATE USING ‘nodelocal:///item.sql’ CSV DATA (‘nodelocal:///itemRe.csv’,’nodelocal:///itemRe3.csv’,’nodelocal:///itemRe2.csv’,’nodelocal:///itemRe4.csv’) WITH save_rejected,rejectrows=’25’;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes | rejected_rows | rejected_address

+——————————+—————-+——————————+———+———————-+————————+———-+———————-+———————————————————————+ 570581928806776833 | succeeded | 1 | 276 | 0 | 0 | 22206 | 25 | nodelocal:///rejected.570581928806776833.csv

(1 row) |

Dump文件导入

​ DUMP文件导入（分三种）：Postgres dump 文件、MySQL dump 文件和InCloud ZNBASE dump文件

语法格式

在InCloud ZNBASE中，Dump文件的导入语法如图。Dump文件多为sql脚本文件。

参数说明

• Table_name

指导要导入的表名。导入库时不需要指定。

• Load_format

指定的dump文件格式。此处为PGDUMP/MYSQLDUMP，ZNBase的dump文件走PGDUMP。

• File_location

指定dump文件路径。和前面第三方路径一样。多用本地存储路径nodelocal。

• Kv_option_list

with option (kv_option_list)与with kv_option_list两种不同写法，效果一样。Dump文件导入参数如下表。

语法示例

示例1：导入一个Postgres database dump:

导入gs://acme-co/data.sql里所有表结构和数据。格式PGDUMP表示该脚本可以是postgres导出脚本文件，也可以是bini导出的脚本文件。

Google Cloud:

LOAD PGDUMP (‘gs://acme-co/data.sql‘);

示例2：NFS/nodelocal本地导入:

导入本地nodelocal挂载路径下acme-co/data.sql脚本中所有表结构和数据。

LOAD PGDUMP (‘nodelocal:///acme-co/data.sql’);

示例3：从Postgres database dump导入一张表：

从data.sql里只导入指定表employees的结构和表数据。且忽略外键。

Google Cloud:

LOAD TABLE employees FROM PGDUMP (‘gs://acme-co/data.sql‘) WITH skip_foreign_keys;

示例4：导入一个ZNBASE dump 文件：

ZNBASE dump 文件使用LOAD PGDUMP方式导入。

Google Cloud:

LOAD PGDUMP (‘gs://acme-co/data.sql‘);

示例5：导入MySQL datatabase dump:

导入整个脚本文件data.sql里的所有表和数据。语法应是mysql语法。使用MYSQLDUMP格式导入数据。

Google Cloud:

LOAD MYSQLDUMP (‘gs://acme-co/data.sql‘);

示例6：从一个MySQL database dump中导入一张表employess：

从脚本gs://acme-co/data.sql中导入表emplyess和数据且忽略外键。

Google Cloud:

LOAD TABLE employees FROM MYSQLDUMP (‘gs://acme-co/data.sql‘) WITH skip_foreign_keys

数据还原

语法格式

​ 在InCloud ZNBASE中，数据还原语法如图所示。Load可还原单表(LOAD TABLE)、单模式（LOAD SCHEMA）或者单库(LOAD DATABASE)，暂时无法还原关联多表的外键和视图。视图可通过ZNBase dump命令把视图结构导出sql脚本，再还原视图。外键需要在还原后有脚本单独添加。

​ TABLE table_name,SCHEMA schema,DATABASE database_name三选一。Incremental_dump_location可选参数，相对增量备份的还原，跟全量备份路径（见语法示例）。As of system time可选参数，表示基于时间戳的还原。

注：还原的多表多库后续会添加语法功能。当前1.0版本不支持多表多库的还原。

还原支持类型有两种，1.全量备份。2.增量备份。

​ 如果备份指定revision history参数，则还原时可以指定pint-in-time在revision history获取的时间内的时间戳。通过as of system time方式。如果不指定时间，刚还原以备份时的时间戳还原数据。则如同 还原没有revision history的备份情况。

注：

​ 还原时，通过将还原分配给所有节点，最小化其对集群性能的影响。恢复数据的子集(称为范围)均匀地分布在随机选择的节点中，每个范围最初只恢复到一个节点。一旦范围被恢复，节点就开始复制它。

​ 当还原失败或者被取消时，部分还原的数据将被正常的清除，这可能会对集群性能产生轻微的临时影响。

​ 还原如同备份一样，也是通过job挂载执行的，所以可以通过show jobs, pause job,resume job, cancel job来控制还原。

参数说明

• table_name

指定要还原的表名。用例中是TEST。

• database_name

指定要还原的库名。用例中是DATA。

• full_dump_location

指定要还原文件的路径。通常情况使用本地nodelocal。

说明：

​ 默认的full_dump_location是在ZNBase-data文件里extern目录里存放。但在集群时，由于某节点备份和导出的数据文件在ZNBase-data，而导致该节点数据副本与其它节点不一致。例如当外部数据文件存放在集群中某一个节点的数据目录里的extern时，当还原和导入时，需要对集群中所有节点的数据目录下extern都要包含相应的数据文件，否则会报“其它节点数据目录下没有相应的数据文件。原因是还原节点的数据目录比集群中其它节点数据文件多了extern文件数据。

解决方案：

​ 使用参—external-io-dir，此参数用在启动集群中节点服务中，写绝对路径。使用此参数时，需要集群中每个节点的启动参数都得加相应的—external-io-dir。如果只有其中一个节点有该参数，该结点的备份和导出目录为此参数指定的绝对路径。但其它节点刚是在cockraoch-data文件下extern，则会引起集群中节点的数据目录不一致。

• Incremental_dump_location

指定增量备份时的数据路径。还原时如果是使用增量备份数据还原，直接在load … from后面加增量备份文件路径，和全量备份路径用逗号隔开，可跟多个文件路径还原目标表。见语法示例6。

• AS OF SYSTEM TIME timestamp

指定还原时间戳。可以根据自己备份的情况指定，也可以用系统的timestamp。

例如AS OF SYSTEM TIEM [select cluster_logical_timestamp()]

> SELECT cluster_logical_timestamp(); cluster_logical_timestamp

+————————————————+

1574166258732738293.0000000000

(1 row) Time: 945.743µs

• kv_option_list

指定还原时参数。处理一些用户需求。

语法示例

示例1：还原单表到新的库中：

把nodelocal:///test里的备份文件中的表bank.test还原到newdb库中。

LOAD TABLE bank.test FROM “nodelocal:///test” WITH into_db=’newdb’;

示例2：还原表且忽略外键：

还原nodelocal:///test里表bank.test数据和表结构，且忽略与该表的外键约束。

LOAD TABLE bank.test FROM “nodelocal:///test” WITH skip_missing_foreign_key;

示例3：还原单库：

还原nodelocal:///test目录下库test的所有表结构和数据。

LOAD DATABASE test from “nodelocal:///test”;

示例4：还原库且忽略表的sequences:

还原nodelocal:///test目录下库test里的所有表结构和数据，且忽略表的sequences。

LOAD DATABASE test from “nodelocal:///test” WITH skip_missing_sequences;

示例5：Point-in-time 还原：

基于时间戳2017-02-26 10:00:00，还原表bank.customers。

LOAD TABLE bank.customers FROM ‘gs://acme-co-backup/database-bank-2017-03-27-weekly‘ AS OF SYSTEM TIME ‘2017-02-26 10:00:00’;

示例6：还原增量备份：

路径database-bank-2017-03-27-weekly为全量备份文件，03-28，03-29为多级增量备份路径。该用例是还原表bank.customers基于两级增量备份方式。

LOAD TABLE bank.customers FROM ‘gs://acme-co-backup/database-bank-2017-03-27-weekly‘, ‘gs://acme-co-backup/database-bank-2017-03-28-nightly‘, ‘gs://acme-co-backup/database-bank-2017-03-29-nightly‘;

示例7：还原系统表：

还原系统表system.users到newdb库中，模式为newdb.public.users。

LOAD TABLE system.users FROM “nodelocal:///users” WITH into_db = ‘newdb’;

注：

​ 一般系统表，例如system.users(system.public.users)备份时是指向system.public.users结构。但这种的备份系统表是无法直接还原的，因为一般用户不能往system系统库写入数据。所以备份的系统表需要使用into_db参数，往新库中还原。由于into_db指定newdb，会把DUMP中system库替换掉，所以是newdb.public.users。还原到into_db指定库中就成了普通表数据。

示例8：还原加密文件：

还原数据库data从test_file中的加密文件。

LOAD DATABASE data FROM “nodelocal:///test_file” WITH encryption_passphrase=”abcdefg”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

589773099167514625 | succeeded | 1 | 6 | 0 | 0 | 144

(1 row) Time: 96.90502ms |

注：

当用户未输入密码，提示：

file appears encrypted — try specifying encryption_passphrase

当用户密码输入错误，提示：

pq: nodelocal:///1/n1.0.csv: cipher: message authentication failed

示例9：模式的备份还原：

>CREATE SCHEMA sch;

>CREATE TABLE sch.test(a INT PRIMARY KEY, b INT UNIQUE, c STRING CHECK(c not in (‘a’, ‘b’, ‘c’, ‘d’)));

>DUMP SCHEMA sch TO SST “nodelocal:///sch_bk”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

579840077701578753 | succeeded | 1 | 14 | 8 | 0 | 422

(1 row)

>DROP SCHEMA sch CASCADE;

>LOAD SCHEMA sch FROM “nodelocal:///sch_bk”;

job_id | status | fraction_completed | rows | index_entries | system_records | bytes

+——————————+—————-+——————————+———+———————-+————————+———-+

579061853955129345 | succeeded | 1 | 14 | 8 | 0 | 422

(1 row) |