Exporting and importing the query history

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

watsonx.data on IBM Software Hub

About this task

To import and export the query history, you must install the Presto CLI and generate the keystore. For more information, see Connecting to a Presto server.

Procedure

  1. To export the query history, run the following commands. 
    export PRESTO_PASSWORD=<your-cpd-password>
    ./presto --server https://<route_url> --catalog system --schema \
runtime --execute "select * from queries" --user admin --truststore-\
path <path-to-trustore-file> --truststore-password <truststore-pwd> --output-format CSV_HEADER > \
history.csv --password
    Example:
    export PRESTO_PASSWORD=<your-cpd-password>
    ./presto --server https://$route_url --catalog system --schema \
runtime --execute "select * from queries" --user admin --truststore-\
path presto.jks --truststore-password changeit --output-format CSV_HEADER > \
history.csv --password

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

  2. To import the query history, complete the following steps.
    1. Create a schema in a catalog in which you have the write access. 
      create schema iceberg_data.query_history with (location='s3a://hive-bucket/query_history')
      Example:
      ./presto --server https://${route_url} --execute "create schema iceberg_data.query_history with (location='s3a://iceberg-bucket/query_history')" \
--user admin --truststore-path presto.jks \
--truststore-password changeit --password
    2. 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://${route_url} --execute "create table iceberg_data.query_history.queries as select * from system.runtime.queries" \
--user admin \
--truststore-path presto.jks \
--truststore-password changeit \
--password
    3. To import the query history into the table that you have created, 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://${route_url} \
--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 admin --truststore-path presto.jks \
--truststore-password changeit --password
  3. To retrieve query from both the tables, use following statement. 
    select * from <non-system-table-name> union select * from system.runtime.queries order by created;
    Example:
    ./presto --server https://${route_url} \
--execute " select * from iceberg_data.query_history.queries union select * from system.runtime.queries order by created " \
--user admin \
--truststore-path presto.jks \
--truststore-password changeit --password