save_query_result 保存查询结果支持

save_query_result 保存查询结果支持

开启 save_query_result 后，MatrixOne 会保存查询结果。

对保存查询结果有影响的参数有三个：

save_query_result：开启/关闭保存查询结果。
query_result_timeout：设置保存查询结果的时间。
query_result_maxsize：设置单个查询结果最大值。

限制

只支持保存带有返回结果的语句，如 SELECT, SHOW, DESC, EXECUTE 语句
对于 SELECT 语句，只保存以 /* cloud_user */ 和 /* save_result */ 固定开头的 SELECT 语句的结果。

开启保存查询结果设置

仅对当前会话开启保存查询结果：

1. --  默认为 off
2. set save_query_result = on

对全局开启保存查询结果：

1. --  默认为 off
2. set global save_query_result = on

全局开启也可在启动 MatrixOne 之前，修改配置文件 cn.toml，插入以下代码并保存：

1. [cn.frontend]
2. saveQueryResult = "on"  // 默认为 off

设置保存时间

设置保存时间单位为小时。

仅对当前会话开启查询结果保存时间：

1. -- 默认为 24
2. set query_result_timeout = 48

对全局开启查询结果保存时间：

1. -- 默认为 24
2. set global query_result_timeout = 48

全局开启也可在启动 MatrixOne 之前，修改配置文件 cn.toml，插入以下代码并保存：

1. [cn.frontend]
2. queryResultTimeout = 48  // 默认为 24

Note: 保存时间如果设置的值比上一次设置的短，不影响之前的保存结果。

设置单个查询结果的最大值

设置单个查询结果的最大值单位为 MB。

仅对当前会话设置查询结果的最大值：

1. -- 默认为 100
2. set query_result_maxsize = 200

对全局设置查询结果的最大值：

1. -- 默认为 100
2. set global query_result_maxsize = 200

全局开启也可在启动 MatrixOne 之前，修改配置文件 cn.toml，插入以下代码并保存：

1. [cn.frontend]
2. queryResultMaxsize = 200 // 默认为 100

Note: 单个查询结果的最大值如果设置的值比上一次设置的小，不影响之前的保存结果大小。

查询元数据信息

查询元数据信息可以使用下面的 SQL 语句：

1. select * from meta_scan(query_id) as u;
2. 当前 account_id
3. select query_id from meta_scan(query_id) as u;

元数据信息如下：

列名	类型	备注
query_id	uuid	查询结果 ID
statement	text	执行的 SQL 语句
account_id	uint32	账户 ID
role_id	uint32	角色 ID
result_path	text	保存查询结果的路径，默认保存路径为 matrixone 文件夹 mo-data/s3，如需修改默认保存的路径，需修改配置文件中的 `data-dir = “mo-data/s3”`。如需查阅配置文件参数说明，参见通用参数配置
created_time	timestamp	创建时间
result_size	float	结果大小，单位为 MB。
tables	text	SQL 所用到的表
user_id	uint32	用户 ID
expired_time	timestamp	查询结果的超时时间
column_map	text	查询如果有同名的列结果名，result scan 会对列名做重映射

保存查询结果

你可以将查询结果保存在你的本地磁盘或 S3 中。

语法结构

1. MODUMP QUERY_RESULT query_id INTO s3_path
2. [FIELDS TERMINATED BY 'char']
3. [ENCLOSED BY 'char']
4. [LINES TERMINATED BY 'string']
5. [header 'bool']
6. [MAX_FILE_SIZE unsigned_number]

query_id：是 UUID 的字符串。
s3_path：是查询结果文件保存的路径。默认保存路径为 matrixone 文件夹 mo-data/s3，如需修改默认保存路径，需修改配置文件中的 data-dir = "mo-data/s3"。如需查阅配置文件参数说明，参见通用参数配置

1. root@rootMacBook-Pro 02matrixone % cd matrixone/mo-data
2. root@rootMacBook-Pro mo-data % ls
3. tn-data         etl             local           logservice-data s3

Note: 如果你需要导出 csv 文件。路径需要以 etl: 开头。

[FIELDS TERMINATED BY ‘char’]：可选参数。字段分割符号，默认为单引号 '。
[ENCLOSED BY ‘char’]：可选参数。字段包括符号，默认为引双号 “。
[LINES TERMINATED BY ‘string’]：可选参数。行结束符号，默认为换行符号 \n。
[header ‘bool’]：可选参数。bool 类型可以选择 true 或 false。csv 文件第一行为各个列名的标题行。
[MAX_FILE_SIZE unsigned_number]：可选参数。文件最大文件大小，单位为 KB。默认为 0。

示例

示例 1

1. mysql> set global save_query_result = on;
2. mysql> set global query_result_timeout = 24;
3. mysql> set global query_result_maxsize = 200;
4. mysql> create table t1 (a int);
5. mysql> insert into t1 values(1);
6. mysql> /* cloud_user */select a from t1;
7. +------+
8. | a    |
9. +------+
10. |    1 |
11. +------+
12. 1 row in set (0.16 sec)
13. -- 查询当前会话中最近执行的查询 ID
14. mysql> select last_query_id();
15. +--------------------------------------+
16. | last_query_id()                      |
17. +--------------------------------------+
18. | f005ebc6-a3dc-11ee-bb76-26dd28356ef3 |
19. +--------------------------------------+
20. 1 row in set (0.12 sec)
21. -- 获取这个查询 ID 的查询结果
22. mysql> select * from result_scan('f005ebc6-a3dc-11ee-bb76-26dd28356ef3') as t;
23. +------+
24. | a    |
25. +------+
26. |    1 |
27. +------+
28. 1 row in set (0.01 sec)
29. -- 查看这个查询 ID 的元数据
30. mysql> select * from meta_scan('f005ebc6-a3dc-11ee-bb76-26dd28356ef3') as t;
31. +--------------------------------------+------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+-----------+
32. | query_id                             | statement        | account_id | role_id | result_path                                                         | create_time         | result_size          | tables | user_id | expired_time        | ColumnMap |
33. +--------------------------------------+------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+-----------+
34. | f005ebc6-a3dc-11ee-bb76-26dd28356ef3 | select a from t1 |          0 |       0 | SHARED:/query_result/sys_f005ebc6-a3dc-11ee-bb76-26dd28356ef3_1.blk | 2023-12-26 18:53:01 | 0.000003814697265625 | t1     |       0 | 2023-12-27 18:53:01 | a -> a    |
35. +--------------------------------------+------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+-----------+
36. 1 row in set (0.01 sec)

38. -- 将查询结果保存到本地
39. MODUMP QUERY_RESULT 'f005ebc6-a3dc-11ee-bb76-26dd28356ef3' INTO 'etl:your_local_path';

示例 2

1. mysql> set global save_query_result = on;
2. mysql> set global query_result_timeout = 24;
3. mysql> set global query_result_maxsize = 200;
4. mysql> create table t1 (a int);
5. mysql> insert into t1 values(1);
6. mysql> /* save_result */select a from t1;
7. +------+
8. | a    |
9. +------+
10. |    1 |
11. +------+
12. 1 row in set (0.02 sec)

14. mysql> select last_query_id();
15. +--------------------------------------+
16. | last_query_id()                      |
17. +--------------------------------------+
18. | afc82394-a45e-11ee-bb9a-26dd28356ef3 |
19. +--------------------------------------+
20. 1 row in set (0.00 sec)

22. mysql> select * from result_scan('afc82394-a45e-11ee-bb9a-26dd28356ef3') as t;
23. +------+
24. | a    |
25. +------+
26. |    1 |
27. +------+
28. 1 row in set (0.01 sec)

30. mysql> select * from meta_scan('afc82394-a45e-11ee-bb9a-26dd28356ef3') as t;
31. +--------------------------------------+------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+-----------+
32. | query_id                             | statement        | account_id | role_id | result_path                                                         | create_time         | result_size          | tables | user_id | expired_time        | ColumnMap |
33. +--------------------------------------+------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+-----------+
34. | afc82394-a45e-11ee-bb9a-26dd28356ef3 | select a from t1 |          0 |       0 | SHARED:/query_result/sys_afc82394-a45e-11ee-bb9a-26dd28356ef3_1.blk | 2023-12-27 10:21:47 | 0.000003814697265625 | t1     |       0 | 2023-12-28 10:21:47 | a -> a    |
35. +--------------------------------------+------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+-----------+
36. 1 row in set (0.00 sec)

示例 3

1. mysql> set global save_query_result = on;
2. mysql> set global query_result_timeout = 24;
3. mysql> set global query_result_maxsize = 200;
4. mysql> create table t1 (a int);
5. mysql> insert into t1 values(1);
6. mysql> show create table t1;
7. +-------+--------------------------------------------+
8. | Table | Create Table                               |
9. +-------+--------------------------------------------+
10. | t1    | CREATE TABLE `t1` (
11. `a` INT DEFAULT NULL
12. ) |
13. +-------+--------------------------------------------+
14. 1 row in set (0.02 sec)

16. mysql> select * from meta_scan(last_query_id()) as t;
17. +--------------------------------------+----------------------+------------+---------+---------------------------------------------------------------------+---------------------+-----------------------+--------+---------+---------------------+----------------------------------------------+
18. | query_id                             | statement            | account_id | role_id | result_path                                                         | create_time         | result_size           | tables | user_id | expired_time        | ColumnMap                                    |
19. +--------------------------------------+----------------------+------------+---------+---------------------------------------------------------------------+---------------------+-----------------------+--------+---------+---------------------+----------------------------------------------+
20. | 617647f4-a45c-11ee-bb97-26dd28356ef3 | show create table t1 |          0 |       0 | SHARED:/query_result/sys_617647f4-a45c-11ee-bb97-26dd28356ef3_1.blk | 2023-12-27 10:05:17 | 0.0000858306884765625 |        |       0 | 2023-12-28 10:05:17 | Table -> Table, Create Table -> Create Table |
21. +--------------------------------------+----------------------+------------+---------+---------------------------------------------------------------------+---------------------+-----------------------+--------+---------+---------------------+----------------------------------------------+
22. 1 row in set (0.00 sec)

示例 4

1. mysql> set global save_query_result = on;
2. mysql> set global query_result_timeout = 24;
3. mysql> set global query_result_maxsize = 200;
4. mysql> create table t1 (a int);
5. mysql> insert into t1 values(1);
6. mysql> desc t1;
7. +-------+---------+------+------+---------+-------+---------+
8. | Field | Type    | Null | Key  | Default | Extra | Comment |
9. +-------+---------+------+------+---------+-------+---------+
10. | a     | INT(32) | YES  |      | NULL    |       |         |
11. +-------+---------+------+------+---------+-------+---------+
12. 1 row in set (0.03 sec)

14. mysql> select * from meta_scan(last_query_id()) as t;
15. +--------------------------------------+-----------+------------+---------+---------------------------------------------------------------------+---------------------+---------------------+------------+---------+---------------------+----------------------------------------------------------------------------------------------------------------+
16. | query_id                             | statement | account_id | role_id | result_path                                                         | create_time         | result_size         | tables     | user_id | expired_time        | ColumnMap                                                                                                      |
17. +--------------------------------------+-----------+------------+---------+---------------------------------------------------------------------+---------------------+---------------------+------------+---------+---------------------+----------------------------------------------------------------------------------------------------------------+
18. | 143a54b6-a45d-11ee-bb97-26dd28356ef3 | desc t1   |          0 |       0 | SHARED:/query_result/sys_143a54b6-a45d-11ee-bb97-26dd28356ef3_1.blk | 2023-12-27 10:10:17 | 0.00016021728515625 | mo_columns |       0 | 2023-12-28 10:10:17 | Field -> Field, Type -> Type, Null -> Null, Key -> Key, Default -> Default, Extra -> Extra, Comment -> Comment |
19. +--------------------------------------+-----------+------------+---------+---------------------------------------------------------------------+---------------------+---------------------+------------+---------+---------------------+----------------------------------------------------------------------------------------------------------------+
20. 1 row in set (0.00 sec)

示例 5

1. mysql> CREATE TABLE numbers(pk INTEGER PRIMARY KEY, ui BIGINT UNSIGNED, si BIGINT);
2. Query OK, 0 rows affected (0.02 sec)

4. mysql> INSERT INTO numbers VALUES (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807);
5. Query OK, 2 rows affected (0.01 sec)

7. mysql> SET @si_min = -9223372036854775808;
8. Query OK, 0 rows affected (0.00 sec)

10. mysql> PREPARE s2 FROM 'SELECT * FROM numbers WHERE si=?';
11. Query OK, 0 rows affected (0.01 sec)

13. mysql> EXECUTE s2 USING @si_min;
14. +------+------+----------------------+
15. | pk   | ui   | si                   |
16. +------+------+----------------------+
17. |    0 |    0 | -9223372036854775808 |
18. +------+------+----------------------+
19. 1 row in set (0.02 sec)

21. mysql> select * from meta_scan(last_query_id()) as t;
22. +--------------------------------------+---------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+------------------------------+
23. | query_id                             | statement                                                                                         | account_id | role_id | result_path                                                         | create_time         | result_size          | tables | user_id | expired_time        | ColumnMap                    |
24. +--------------------------------------+---------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+------------------------------+
25. | e83b8df2-a45d-11ee-bb98-26dd28356ef3 | EXECUTE s2 USING @si_min // SELECT * FROM numbers WHERE si=? ; SET @si_min = -9223372036854775808 |          0 |       0 | SHARED:/query_result/sys_e83b8df2-a45d-11ee-bb98-26dd28356ef3_1.blk | 2023-12-27 10:16:13 | 0.000019073486328125 |        |       0 | 2023-12-28 10:16:13 | pk -> pk, ui -> ui, si -> si |
26. +--------------------------------------+---------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+------------------------------+
27. 1 row in set (0.00 sec)

示例 6

1. mysql> set global save_query_result = on;
2. mysql> set global query_result_timeout = 24;
3. mysql> set global query_result_maxsize = 200;
4. mysql> create table t1 (a int);
5. mysql> insert into t1 values(1);
6. mysql> select * from t1;
7. +------+
8. | a    |
9. +------+
10. |    1 |
11. +------+
12. 1 row in set (0.00 sec)

14. mysql> select * from meta_scan(last_query_id()) as t;
15. ERROR 20405 (HY000): file query_result_meta/sys_c16859e4-a462-11ee-bba0-26dd28356ef3.blk is not found