Monitoring and managing diagnostic data
Query History Monitoring and Management (QHMM) is a service that stores and manages the
diagnostic data such as queries history and query event-related information of the Presto engine in
the Minio bucket, wxd-system
. You can retrieve the stored history files for
analysis, debugging and monitoring purpose.
The Query history monitoring and management page in IBM® watsonx.data provides information for fetching the history data and analyzing the queries that are run.
watsonx.data on Red Hat® OpenShift®
watsonx.data SaaS on AWS
Before you begin
Ensure that you have the following information:
<instance-id>
: unique identifier of the watsonx.data instance. Save the instance ID for reference.<engine>
: the engine used for data processing. Here, Presto.<catalog>
: the catalog specific to QHMM. Save the catalog name for reference. Here we use,wxd_system_data
.<bucket>
: the MinIO bucket for storing the diagnostic data. Save the bucket name for reference. Here we use,wxd-system.
- Go to the Query workspace page and do the following:
- Run the following command to create a schema to organize the diagnostic data for QHMM.
Here, theCREATE SCHEMA IF NOT EXISTS <catalog>.diag WITH (location = 's3a://wxd-system/diag/');
<catalog>
name iswxd_system_data
. - Run the following command to create a table to store the query event
data.
Here, theCREATE TABLE IF NOT EXISTS <catalog>.diag.query_event_raw ( record VARCHAR, dt VARCHAR ) WITH ( external_location = 's3a://<bucket>/qhmm/<instance-id>/<engine>/<engine-id>/QueryEvent/', format = 'textfile', partitioned_by = ARRAY['dt'] );
<catalog>
name iswxd_system_data
. - Run the following command to create a table to store the query history data in JSON
format.
Here, theCREATE TABLE IF NOT EXISTS <catalog>.diag.query_history( query_id VARCHAR, query VARCHAR, state VARCHAR, source VARCHAR, created VARCHAR, started VARCHAR, "end" VARCHAR, dt VARCHAR, user VARCHAR) WITH ( external_location = 's3a://<bucket>/qhmm/<instance-id>/<engine>/<engine-id>/QueryHistory/', format = 'JSON', partitioned_by = ARRAY['dt','user'] );
<catalog>
name iswxd_system_data
.
- Run the following command to create a schema to organize the diagnostic data for QHMM.
About this task
The system stores the data in watsonx.data
MinIO buckets. Here, wxd-system.
Note: The system removes the data after
every 7 days, and the maximum storage capacity is 1 GB (exceeding limit trims the data to fit into 1
GB size).