Exporting and importing the query history- Developer

The PrestoPresto (Java) coordinator stores the query history in system.runtime.queries table. T system.runtime.queries table truncates the query history 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 Developer edition

About this task

To import and export the query history, you must install the PrestoPresto (Java) CLI and generate the keystore. For more information, see con-presto-serv.html.

Procedure

  1. To export the query history, run the following command. 
    export PRESTO_PASSWORD=<your-password>
    Then run this command, 
    ./presto --server https://<host:port> --catalog system --schema \
runtime --execute "select * from queries" --user ibmlhadmin --truststore-\
path <path-to-trustore-file> --truststore-password <truststore-pwd> --output-format CSV_HEADER > \
history.csv --password
    Example:
    export PRESTO_PASSWORD=<your-password>
    ./presto --server https://shiftier1.fyre.ibm.com:8443 --catalog system --schema \
runtime --execute "select * from queries" --user ibmlhadmin --truststore-\
path presto.jks --truststore-password changeit --output-format CSV_HEADER > \
history.csv --password

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

  2. Create a table in a catalog, which has a write access. The new table must have the same metadata as of system.runtime.queries.
    1. To create a table, run. 
      create table <non-system-table-name> as select * from
system.runtime.queries where 1=
      Example:
      ./presto --server https://shiftier1.fyre.ibm.com:8443 \
--keystore-path /Users/user1/Desktop/certs/truststore.jks \
--keystore-password mypassword –execute ” create table\
hive_data.query_history.queries as select * from system.runtime.queries where 1=0” \
--user ibmlhadmin --password
  3. To import query history into the non-system table, that you created, you can use this command 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://shiftier1.fyre.ibm.com:8443 \
--keystore-path /Users/user1/Desktop/certs/truststore.jks \
--keystore-password\mypassword –execute ” INSERT INTO hive_data.query_history.queries\
SELECT * FROM system.runtime.queries \
WHERE query_id NOT IN (SELECTquery_id FROM <non-system-table-name>);” \
--user ibmlhadmin –password
  4. 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://shiftier1.fyre.ibm.com:8443 \
--keystore-path /Users/user1/Desktop/certs/truststore.jks \
--keystore-password mypassword –execute ” \
select * from hive_data.query_history.queries union select * from
system.runtime.queries order by created” \
--user ibmlhadmin –password