save_query_result Support to save query results

After save_query_result is enabled, MatrixOne will save the query results.

There are three parameters that affect the saving of query results:

save_query_result: Turn on/off save query results.
query_result_timeout: Set the time to save the query results.
query_result_maxsize: Sets the maximum value of a single query result.

limit

Only support for saving statements with return results, such as SELECT, SHOW, DESC, EXECUTE statements
For SELECT statements, only the results of SELECT statements starting with /* cloud_user */ and /* save_result */ are saved.

Enable save query result settings

Save query results only for the current session:

-- Default is off
set save_query_result = on

Save query results globally:

-- Default is off
set global save_query_result = on

Set save time

Set the unit of saving time to hours.

Only enable query result storage time for the current session:

-- Default is 24
set query_result_timeout = 48

Turn on query results to save time globally:

-- Default is 24
set global query_result_timeout = 48

Note: Save time If the set value is shorter than the last set, it will not affect the previous save result.

Set the maximum value of a single query result

Set the maximum unit of a single query result to MB.

Set the maximum value of query results only for the current session:

-- Default is 100
set query_result_maxsize = 200

Set the maximum value of query results globally:

-- Default is 100
set global query_result_maxsize = 200

Note: The maximum value of a single query result. If the set value is smaller than the last set, it will not affect the previous saved result size.

Query metadata information

To query metadata information, you can use the following SQL statement:

select * from meta_scan(query_id) as u;
Current account_id
select query_id from meta_scan(query_id) as u;

The metadata information is as follows:

Column Name	Type	Notes
query_id	uuid	query result ID
statement	text	Execution SQL statement
account_id	uint32	Account ID
role_id	uint32	Role ID
result_path	text	The path to save the query results. The default save path is matrixone folder mo-data/s3. If you need to modify the default saved path, you need to modify the `data-dir = "mo-data/s3"` in the configuration file. To view the configuration file parameter description, see [General Parameter Configuration] (../../System-Parameters/system-parameter.md)
created_time	timestamp	creation time
result_size	float	Result size, unit in MB.
tables	text	tables used in SQL
user_id	uint32	User ID
expired_time	timestamp	Timeout time for query result
column_map	text	Query If there is a column result name with the same name, result scan will remap the column name

Save query results

You can save the query results on your local disk or S3.

Syntax Structure

MODUMP QUERY_RESULT query_id INTO s3_path
     [FIELDS TERMINATED BY 'char']
     [ENCLOSED BY 'char']
     [LINES TERMINATED BY 'string']
     [header 'bool']
     [MAX_FILE_SIZE unsigned_number]

query_id: is a string of UUID.
s3_path: is the path to save the query result file. The default save path is matrixone folder mo-data/s3. If you need to modify the default save path, you need to modify data-dir = "mo-data/s3" in the configuration file. To check the configuration file parameter description, see [General Parameter Configuration] (../../System-Parameters/system-parameter.md)

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

Note: If you need to export the csv file. The path needs to start with etl:.

[FIELDS TERMINATED BY 'char']: Optional parameter. Field splitting symbol, defaults to single quotes '.
[ENCLOSED BY 'char']: Optional parameter. Fields include symbols, default to quotation double signs ".
[LINES TERMINATED BY 'string']: Optional parameter. The line end symbol, default is the line break symbol \n.
[header 'bool']: Optional parameter. The bool type can be selected as true or false. csv file first behavior is the title line of each column name.
[MAX_FILE_SIZE unsigned_number]: Optional parameter. The maximum file size of the file is KB. The default is 0.

Example

Example 1

mysql> set global save_query_result = on;
mysql> set global query_result_timeout = 24;
mysql> set global query_result_maxsize = 200;
mysql> create table t1 (a int);
mysql> insert into t1 values(1);
mysql> /* cloud_user */select a from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.16 sec)
-- Query the most recently executed query ID in the current session
mysql> select last_query_id();
+---------------------------------------------+
| last_query_id() |
+---------------------------------------------+
| f005ebc6-a3dc-11ee-bb76-26dd28356ef3 |
+---------------------------------------------+
1 row in set (0.12 sec)
-- Get the query result of this query ID
mysql> select * from result_scan('f005ebc6-a3dc-11ee-bb76-26dd28356ef3') as t;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
-- View the metadata of this query ID
mysql> select * from meta_scan('f005ebc6-a3dc-11ee-bb76-26dd28356ef3') as t;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| query_id | statement | account_id | role_id | result_path | create_time | result_size | tables | user_id | expired_time | ColumnMap |+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| f005ebc6-a3dc-11ee-bb76-26dd28356ef3 | select a from t1 | 0 | 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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

-- Save the query results locally
MODUMP QUERY_RESULT 'f005ebc6-a3dc-11ee-bb76-26dd28356ef3' INTO 'etl:your_local_path';

Example 2

mysql> set global save_query_result = on;
mysql> set global query_result_timeout = 24;
mysql> set global query_result_maxsize = 200;
mysql> create table t1 (a int);
mysql> insert into t1 values(1);
mysql> /* save_result */select a from t1;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.02 sec)

mysql> select last_query_id();
+---------------------------------------------+
| last_query_id() |
+---------------------------------------------+
| afc82394-a45e-11ee-bb9a-26dd28356ef3 |
+---------------------------------------------+
1 row in set (0.00 sec)

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

mysql> select * from meta_scan('afc82394-a45e-11ee-bb9a-26dd28356ef3') as t;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| query_id | statement | account_id | role_id | result_path | create_time | result_size | tables | user_id | expired_time | ColumnMap |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Example 3

mysql> set global save_query_result = on;
mysql> set global query_result_timeout = 24;
mysql> set global query_result_maxsize = 200;
mysql> create table t1 (a int);
mysql> insert into t1 values(1);
mysql> show create table t1;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t1 | CREATE TABLE `t1` (
`a` INT DEFAULT NULL
) |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.02 sec)

mysql> select * from meta_scan(last_query_id()) as t;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| query_id | statement | account_id | role_id | result_path | create_time | result_size | tables | user_id | expired_time | ColumnMap |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 617647f4-a45c-11ee-bb97-26dd28356ef3 | show create table t1 | 0 | 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 |+--------------------------------------+----------------------+------------+---------+---------------------------------------------------------------------+---------------------+-----------------------+--------+---------+---------------------+----------------------------------------------+
1 row in set (0.00 sec)

Example 4

mysql> set global save_query_result = on;
mysql> set global query_result_timeout = 24;
mysql> set global query_result_maxsize = 200;
mysql> create table t1 (a int);
mysql> insert into t1 values(1);
mysql> desc t1;
+-------+---------+------+------+---------+-------+---------+
| Field | Type    | Null | Key  | Default | Extra | Comment |
+-------+---------+------+------+---------+-------+---------+
| a     | INT(32) | YES  |      | NULL    |       |         |
+-------+---------+------+------+---------+-------+---------+
1 row in set (0.03 sec)

mysql> select * from meta_scan(last_query_id()) as t;
+--------------------------------------+-----------+------------+---------+---------------------------------------------------------------------+---------------------+---------------------+------------+---------+---------------------+----------------------------------------------------------------------------------------------------------------+
| query_id                             | statement | account_id | role_id | result_path                                                         | create_time         | result_size         | tables     | user_id | expired_time        | ColumnMap                                                                                                      |
+--------------------------------------+-----------+------------+---------+---------------------------------------------------------------------+---------------------+---------------------+------------+---------+---------------------+----------------------------------------------------------------------------------------------------------------+
| 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 |
+--------------------------------------+-----------+------------+---------+---------------------------------------------------------------------+---------------------+---------------------+------------+---------+---------------------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Example 5

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

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

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

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

mysql> EXECUTE s2 USING @si_min;
+------+------+----------------------+
| pk   | ui   | si                   |
+------+------+----------------------+
|    0 |    0 | -9223372036854775808 |
+------+------+----------------------+
1 row in set (0.02 sec)

mysql> select * from meta_scan(last_query_id()) as t;
+--------------------------------------+---------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+------------------------------+
| query_id                             | statement                                                                                         | account_id | role_id | result_path                                                         | create_time         | result_size          | tables | user_id | expired_time        | ColumnMap                    |
+--------------------------------------+---------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+------------------------------+| 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 |
+--------------------------------------+---------------------------------------------------------------------------------------------------+------------+---------+---------------------------------------------------------------------+---------------------+----------------------+--------+---------+---------------------+------------------------------+
1 row in set (0.00 sec)

Example 6

mysql> set global save_query_result = on;
mysql> set global query_result_timeout = 24;
mysql> set global query_result_maxsize = 200;
mysql> create table t1 (a int);
mysql> insert into t1 values(1);
mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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