What are Vertica Query History Tables? – Explanation

The Vertica query history tables are used for various applications, for example, identify long running queries, identify skewed node, identify the load on Vertica cluster. Vertica supports management console to identify the cluster status. The Vertica database also provides tables that stores information about the requests that are made to server. Information includes query history, time, username, userid, etc. In this article, we will check Vertica query history tables available in the Vertica analytics database.

Before jumping into query history tables, let us check why query historical data is required?

Why Query History details are Required?

As mentioned in the previous section, you need to consider query history to identify the potential long running queries on the cluster.

• Identify Vertica cluster health. With the help of historical query data, you can identify the average time taken from server to execute queries.
• Identify long running queries. You can check historical data to check long running queries on the cluster so that you can modify the logic to improve performance of queries.
• You can use historical query data to troubleshoot the problems and capacity planning.
• You can verify the failed queries.

Vertica Query History Tables

Just like any other MPP, Vertical analytics database stores all executed query details in history tables. MPP databases like Netezza provides a shell script to load, query history tables.

There are two tables that you can use to get query history on Vertica cluster:

• query_requests
• dc_requests_issued

Vertica query_requests Query History Table

This table stores information about user-issued query requests. The query_requests table also stores query execution start, end time and duration in milliseconds.

Below is the query example to get latest executed queries from query_requests table:

SELECT * 
 FROM   query_requests 
 WHERE  request_type = 'QUERY' 
 ORDER  BY start_timestamp DESC; 

Vertica dc_requests_issued Query History Table

This table also stores information about user-issued query requests. This table also stores query submitted time as well.

Below is the query example to get latest executed queries from dc_requests_issued table:

SELECT * 
 FROM   dc_requests_issued 
 WHERE  request_type = 'QUERY' 
 ORDER  BY time DESC; 

You can use any of the above-mentioned tables based on your requirements.

Hope this helps ?