Exporting and importing the query history

The Presto (Java) coordinator stores the query history in system.runtime.queries table. But system.runtime.queries table truncates when you restart Presto (Java), resulting in loss of query history. To mitigate this issue, you can export query history as a csv file and also import the query history from the system.runtime.queries table to a non-system table.

It is recommended to periodically export the query history to avoid losing it.

To import and export the query history, you must install the Presto (Java) CLI. For more information, see Connecting to Presto (Java) server.

Exporting query history

To export the query history, run the following command.

export PRESTO_PASSWORD=<your api_key>

./presto --server https://<port:host> --catalog system \
--schemaruntime --execute "select * from queries" \
--user ibmlhapikey --output-format CSV_HEADER > history.csv --password

This command generates a CSV file, which contains exported query history.

Example

./presto --server https://8dac613f-ba5b-4c3c-8c96-
ce8de101f7cf.cdc406pd09pasng7elgg.databases.appdomain.cloud:30929 \
--execute "select * from system.runtime.queries" --output-format CSV_HEADER \
--user ibmlhapikey output-format CSV > history.csv --password

Importing query history

To import the query history, create a schema in a catalog in which you have the write access.

create schema <non-system-catalog.schema-name> with (location=' s3a://<bucket-name>/<schema-name>')

Example

./presto --server https://8dac613f-ba5b-4c3c-8c96-\
ce8de101f7cf.cdc406pd09pasng7elgg.databases.appdomain.cloud:30929 \
--execute "create schema iceberg_data.query_history with \
(location='s3a://8dac613f-ba5b-4c3c-8c96-ce8de101f7cf-customer/query_history')" \
--user ibmlhapikey --password

Create a table in same catalog.

This table must have same metadata as that of system.runtime.queries table. Use CREATE TABLE AS SELECT statement to create this table.

create table <non-system-table-name> as select * from system.runtime.queries where 1=0;

Example

./presto --server https://8dac613f-ba5b-4c3c-8c96-ce8de101f7cf.cdc406pd09pasng7elgg.databases.appdomain.cloud:30929
--execute "create table iceberg_data.query_history.queries as select * from system.runtime.queries where 1=0"
--user ibmlhapikey --password

To import the query history into the table that you have just created table, run the following query periodically.

INSERT INTO <non-system-table-name>
SELECT *
FROM system.runtime.queries
WHERE query_id NOT IN (SELECT query_id FROM <non-system-table-name>);

Example

./presto --server \
https://8dac613f-ba5b-4c3c-8c96-ce8de101f7cf.cdc406pd09pasng7elgg.databases.appdomain.cloud:3092 \
–-execute "insert into iceberg_data.query_history.queries select * from system. runtime.queries \
where query_id not in (select query_id from iceberg_data.query_history.queries)"
--user ibmlhapikey --password

To retrieve query history from both tables, use following statement.

select * from <non-system-table-name> union select * from `system.runtime.queries` order by created;

Example

./presto --server \
https://23b06b14-342b-4ed2-841d-7f02ca9ae788.cdc406pd09pasng7elgg.databases.appdomain.cloud:31530 \
--execute " select * from iceberg_data.query_history.queries union \
select * from system.runtime.queries order by created " \
--output-format=CSV_HEADER --user ibmlhapikey --password