# 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: ```sql -- Default is off set save_query_result = on ``` - Save query results globally: ```sql -- 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: ```sql -- Default is 24 set query_result_timeout = 48 ``` - Turn on query results to save time globally: ```sql -- 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: ```sql -- Default is 100 set query_result_maxsize = 200 ``` - Set the maximum value of query results globally: ```sql -- 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: ```sql 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-variables.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 ```sql 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-variables.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 ```text 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 ```text 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 ```text 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 ```text 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 ```text 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 ```text 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 ```