Cannot create a materialized view for the _query_trace table

I am using QuestDB Enterprise and I want to know which users are sending queries to the cluster and how long the queries take to execute. I know I can parse the information from the logs, but that's a bit cumbersome.

I noticed QuestDB recently added both query tracing and materialized views. I check query tracing and it has exactly what I need, the timestamp, a query text that I don't really need, the execution time, and the principal executing the query.

But this _query_trace table only keeps data around for a short time, and I cannot find any configuration option in the docs to make the data never expire.

I was thinking of creating a materialized view, so I did this:

create materialized view query_stats AS (
    select ts, principal, md5(query_text) as query_hash, count() as executions, sum(execution_micros) as execution_micros, avg(execution_micros) as avg_execution_micros
    FROM _query_trace
    SAMPLE BY 1h
) PARTITION BY DAY;
drop materialized view query_stats_text;

It seems to work, but it is not auto-refreshing as expected. In any case, I would prefer to have the data without any aggregations, and materialized queries also don't support that.

Any hints to keep this auditing data around for a longer period?

Solution

The _query_trace table is a special system table, and not a proper user table, and as such it is not subject to the same options a user table would have.

In this case, probably an option would be creating a table with DEDUP by timestamp, principal, and query_hash, and then execute this query on a schedule.

INSERT INTO audit_table
SELECT ts, principal, md5(query_text) as query_hash, query_text, execution_micros
    FROM _query_trace
   where ts >= (select ts from audit_table limit -1)

You can execute this query using the REST API at the frequency you need for auditing, maybe once every few minutes. The condition will make sure you only need rows generated at the same exact moment or after the last registered query, and since you have DEDUP, the latest registered query will not be inserted twice.