Querying SQL Execution Information

Function

This API is used to query SQL execution information.

Calling Method

For details, see Calling APIs.

Authorization Information

Each account has all the permissions required to call all APIs, but IAM users must be assigned the required permissions.

If you are using role/policy-based authorization, see Permissions Policies and Supported Actions for details on the required permissions.

If you are using identity policy-based authorization, the following identity policy-based permissions are required.

Action	Access Level	Resource Type (*: required)	Condition Key	Alias	Dependencies
dws:cluster:getQueryPropertyForDMS	Read	cluster *	g:ResourceTag/<tag-key> g:EnterpriseProjectId	dws:dmsDdlExamine:getOrCreate	-

URI

GET /v1/{project_id}/clusters/{cluster_id}/dms/queries/{query_id}

**Table 1** Path Parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Definition Project ID. To obtain the value, see Obtaining a Project ID. Constraints N/A Range N/A Default Value N/A
cluster_id	Yes	String	Definition Cluster ID. For details about how to obtain the value, see Obtaining the Cluster ID. Constraints The value must be a valid DWS cluster ID. Range It is a 36-digit UUID. Default Value N/A
query_id	Yes	String	Definition Query ID. Constraints N/A Range N/A Default Value N/A

**Table 2** Query Parameters
Parameter	Mandatory	Type	Description
ctime	No	Long	Definition Collection time, which is a timestamp. Constraints N/A Range N/A Default Value N/A

Request Parameters

None

Response Parameters

Status code: 200

**Table 3** Response body parameters
Parameter	Type	Description
code	Integer	Definition Response code. Range N/A
msg	String	Definition Response information. Range N/A
data	ListQueriesDto object	Definition Response data. Range N/A

**Table 4** ListQueriesDto
Parameter	Type	Description
virtual_cluster_id	Integer	Definition Virtual cluster ID. Range N/A
ctime	Long	Definition Collection time. Range N/A
pid	String	Definition Session ID. Range N/A
inst_name	String	Definition Instance name. Range N/A
waiting	Boolean	Definition Whether the backend is currently waiting on a lock. If yes, the value is true. Range N/A
enqueue	String	Definition Resource status. Range N/A
warning	String	Definition Warnings and warnings related to SQL self-diagnosis tuning. Range N/A
query	String	Definition Query statement. Range N/A
lane	String	Definition Fast or slow lane. Range fast: fast lane slow: slow lane.
db_name	String	Definition Database name. Range N/A
priority	String	Definition Priority of a task in the resource pool. Range 1: highest 2: high 4: medium 8: low
query_id	String	Definition Internal query ID used for statement execution. Range N/A
query_band	String	Definition Job type, which can be set using the GUC parameter query_band. The default value is an empty string. Range N/A
job_name	String	Definition The value is obtained from the query_band field. The position is 0. Range N/A
job_inst	String	Definition The value is obtained from the query_band field. The position is 1. Range N/A
user_name	String	Definition Username used for connecting to the backend. Range N/A
application_name	String	Definition Name of the application that is connected to the backend. Range N/A
client_address	String	Definition IP address of the client connected to the backend. Range N/A
client_hostname	String	Definition Host name of the client. Range N/A
client_port	String	Definition TCP port number used by a client to communicate with the backend. Range N/A
start_time	Long	Definition Time when the statement starts to be executed. Range N/A
block_time	Long	Definition Block time before the statement is executed, in ms Range N/A
duration	Long	Definition Duration that a statement has been executed, in ms. Range N/A
estimate_total_time	Long	Definition Estimated execution time of a statement, in ms. Range N/A
estimate_left_time	Long	Definition Estimated remaining time of statement execution, in ms. Range N/A
resource_pool	String	Definition Resource pool used by the user. Range N/A
control_group	String	Definition Cgroup used by the statement. Range N/A
min_peak_memory	Integer	Definition Minimum memory peak of a statement across all DNs, in MB. Range N/A
max_peak_memory	Integer	Definition Maximum memory peak of a statement across all DNs, in MB. Range N/A
average_peak_memory	Integer	Definition Average memory usage during statement execution, in MB. Range N/A
memory_skew_percent	Integer	Definition Memory usage skew of a statement among DNs. Range N/A
estimate_memory	Integer	Definition Estimated memory used by a statement, in MB. Range N/A
spill_info	String	Definition Statement spill information on all DNs. Range N/A
min_spill_size	Integer	Definition Minimum spilled data among all DNs when a spill occurs, in MB. The default value is 0. Range N/A
max_spill_size	Integer	Definition Maximum spilled data among all DNs when a spill occurs, in MB. The default value is 0. Range N/A
average_spill_size	Integer	Definition Average spilled data among all DNs when a spill occurs, in MB. The default value is 0. Range N/A
spill_skew_percent	Integer	Definition DN spill skew when a spill occurs. Range N/A
min_dn_time	Long	Definition Minimum execution time of a statement across all DNs, in ms. Range N/A
max_dn_time	Long	Definition Maximum execution time of a statement across all DNs, in ms. Range N/A
average_dn_time	Long	Definition Average execution time of a statement across all DNs, in ms. Range N/A
dntime_skew_percent	Integer	Definition Execution time skew of a statement among DNs. Range N/A
min_cpu_time	Long	Definition Minimum CPU time of a statement across all DNs, in ms. Range N/A
max_cpu_time	Long	Definition Maximum CPU time of a statement across all DNs, in ms. Range N/A
total_cpu_time	Long	Definition Total CPU time of a statement across all DNs, in ms. Range N/A
cpu_skew_percent	Integer	Definition CPU time skew of a statement among DNs. Range N/A
average_peak_iops	Integer	Definition Average IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. Range N/A
iops_skew_percent	Integer	Definition I/O skew of a statement among DNs. Range N/A
max_peak_iops	Integer	Definition Maximum IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. Range N/A
min_peak_iops	Integer	Definition Minimum IOPS peak of a statement across all DNs. It is counted by ones in a column-store table and by ten thousands in a row-store table. Range N/A
query_plan	String	Definition Query plan. Range N/A
query_status	String	Definition: Real-time running status of the current query statement. Range: active: A query is being executed in the background. idle: The background is waiting for a new client command. idle in transaction: The background is in a transaction, but the query cannot be performed currently. idle in transaction (aborted): This state is similar to idle in transaction except that a statement in a transaction causes an error. fastpath function call: A fast-path function is being executed in the background. disabled: This state is reported if track_activities is disabled in the background.
wlm_status	String	Definition Status of the current query statement in the resource pool. Range pending running finished aborted active unknown
wlm_attrib	String	Definition Statement attribute. Range ordinary simple complicated internal
system_query	Boolean	Definition Whether the query is a system query. Range N/A
backend_start	Long	Definition Time when this process was started, that is, when the client connected to the server. Range N/A
elapsed_time	Long	Definition Execution time so far. Range N/A
curr_xact_start	Long	Definition Time when the current transaction was started (NULL if no transactions are active) If the current query is the first of its transaction, this column is equal to the query_start column. Range N/A
state_change	Long	Definition Time when the last status was changed. Range N/A
query_start	Long	Definition Time when the statement starts to be executed. Range N/A
query_elapsed_time	Long	Definition Actual execution duration of the statement, in seconds. Range N/A

Example Requests

GET https://{Endpoint}/v1/{project_id}/clusters/{cluster_id}/dms/queries/{query_id}?ctime=1699062846000

Example Responses

Status code: 200

Query succeeded.

{
  "code" : 0,
  "msg" : "OK",
  "data" : {
    "ctime" : 1699062846000,
    "pid" : "140535026615872",
    "waiting" : false,
    "duration" : 0,
    "enqueue" : "",
    "warning" : "",
    "query" : "WLM fetch collect info from data nodes",
    "lane" : "fast",
    "priority" : null,
    "virtual_cluster_id" : 0,
    "inst_name" : "cn_5002",
    "db_name" : "postgres",
    "query_id" : "145522562959855061",
    "query_band" : "",
    "job_name" : "",
    "job_inst" : "",
    "user_name" : "Ruby",
    "application_name" : "workload",
    "client_address" : "",
    "client_hostname" : "",
    "client_port" : "",
    "start_time" : 0,
    "block_time" : 0,
    "estimate_total_time" : 0,
    "estimate_left_time" : 0,
    "resource_pool" : "default_pool",
    "control_group" : "",
    "min_peak_memory" : 0,
    "max_peak_memory" : 0,
    "average_peak_memory" : 0,
    "memory_skew_percent" : 0,
    "estimate_memory" : 0,
    "spill_info" : "",
    "min_spill_size" : 0,
    "max_spill_size" : 0,
    "average_spill_size" : 0,
    "spill_skew_percent" : 0,
    "min_dn_time" : 0,
    "max_dn_time" : 0,
    "average_dn_time" : 0,
    "dntime_skew_percent" : 0,
    "min_cpu_time" : 0,
    "max_cpu_time" : 0,
    "total_cpu_time" : 0,
    "cpu_skew_percent" : 0,
    "average_peak_iops" : 0,
    "iops_skew_percent" : 0,
    "max_peak_iops" : 0,
    "min_peak_iops" : 0,
    "query_plan" : "",
    "query_status" : "active",
    "wlm_status" : "active",
    "wlm_attrib" : "ordinary",
    "system_query" : false,
    "backend_start" : 0,
    "elapsed_time" : 0,
    "curr_xact_start" : 0,
    "state_change" : 0,
    "query_start" : 0,
    "query_elapsed_time" : 0
  }
}

SDK Sample Code

The SDK sample code is as follows.

     package com.huaweicloud.sdk.test;

import com.huaweicloud.sdk.core.auth.ICredential;
import com.huaweicloud.sdk.core.auth.BasicCredentials;
import com.huaweicloud.sdk.core.exception.ConnectionException;
import com.huaweicloud.sdk.core.exception.RequestTimeoutException;
import com.huaweicloud.sdk.core.exception.ServiceResponseException;
import com.huaweicloud.sdk.dws.v2.region.DwsRegion;
import com.huaweicloud.sdk.dws.v2.*;
import com.huaweicloud.sdk.dws.v2.model.*;


public class ShowQueryDetailSolution {

    public static void main(String[] args) {
        // The AK and SK used for authentication are hard-coded or stored in plaintext, which has great security risks. It is recommended that the AK and SK be stored in ciphertext in configuration files or environment variables and decrypted during use to ensure security.
        // In this example, AK and SK are stored in environment variables for authentication. Before running this example, set environment variables CLOUD_SDK_AK and CLOUD_SDK_SK in the local environment
        String ak = System.getenv("CLOUD_SDK_AK");
        String sk = System.getenv("CLOUD_SDK_SK");
        String projectId = "{project_id}";

        ICredential auth = new BasicCredentials()
                .withProjectId(projectId)
                .withAk(ak)
                .withSk(sk);

        DwsClient client = DwsClient.newBuilder()
                .withCredential(auth)
                .withRegion(DwsRegion.valueOf("<YOUR REGION>"))
                .build();
        ShowQueryDetailRequest request = new ShowQueryDetailRequest();
        request.withClusterId("{cluster_id}");
        request.withQueryId("{query_id}");
        try {
            ShowQueryDetailResponse response = client.showQueryDetail(request);
            System.out.println(response.toString());
        } catch (ConnectionException e) {
            e.printStackTrace();
        } catch (RequestTimeoutException e) {
            e.printStackTrace();
        } catch (ServiceResponseException e) {
            e.printStackTrace();
            System.out.println(e.getHttpStatusCode());
            System.out.println(e.getRequestId());
            System.out.println(e.getErrorCode());
            System.out.println(e.getErrorMsg());
        }
    }
}
 
 
  

     # coding: utf-8

import os
from huaweicloudsdkcore.auth.credentials import BasicCredentials
from huaweicloudsdkdws.v2.region.dws_region import DwsRegion
from huaweicloudsdkcore.exceptions import exceptions
from huaweicloudsdkdws.v2 import *

if __name__ == "__main__":
    # The AK and SK used for authentication are hard-coded or stored in plaintext, which has great security risks. It is recommended that the AK and SK be stored in ciphertext in configuration files or environment variables and decrypted during use to ensure security.
    # In this example, AK and SK are stored in environment variables for authentication. Before running this example, set environment variables CLOUD_SDK_AK and CLOUD_SDK_SK in the local environment
    ak = os.environ["CLOUD_SDK_AK"]
    sk = os.environ["CLOUD_SDK_SK"]
    projectId = "{project_id}"

    credentials = BasicCredentials(ak, sk, projectId)

    client = DwsClient.new_builder() \
        .with_credentials(credentials) \
        .with_region(DwsRegion.value_of("<YOUR REGION>")) \
        .build()

    try:
        request = ShowQueryDetailRequest()
        request.cluster_id = "{cluster_id}"
        request.query_id = "{query_id}"
        response = client.show_query_detail(request)
        print(response)
    except exceptions.ClientRequestException as e:
        print(e.status_code)
        print(e.request_id)
        print(e.error_code)
        print(e.error_msg)
 
 
  

     package main

import (
	"fmt"
	"github.com/huaweicloud/huaweicloud-sdk-go-v3/core/auth/basic"
    dws "github.com/huaweicloud/huaweicloud-sdk-go-v3/services/dws/v2"
	"github.com/huaweicloud/huaweicloud-sdk-go-v3/services/dws/v2/model"
    region "github.com/huaweicloud/huaweicloud-sdk-go-v3/services/dws/v2/region"
)

func main() {
    // The AK and SK used for authentication are hard-coded or stored in plaintext, which has great security risks. It is recommended that the AK and SK be stored in ciphertext in configuration files or environment variables and decrypted during use to ensure security.
    // In this example, AK and SK are stored in environment variables for authentication. Before running this example, set environment variables CLOUD_SDK_AK and CLOUD_SDK_SK in the local environment
    ak := os.Getenv("CLOUD_SDK_AK")
    sk := os.Getenv("CLOUD_SDK_SK")
    projectId := "{project_id}"

    auth, err := basic.NewCredentialsBuilder().
        WithAk(ak).
        WithSk(sk).
        WithProjectId(projectId).
        SafeBuild()

    if err != nil {
        fmt.Println(err)
        return
    }

    hcClient, err := dws.DwsClientBuilder().
         WithRegion(region.ValueOf("<YOUR REGION>")).
         WithCredential(auth).
         SafeBuild()


    if err != nil {
        fmt.Println(err)
        return
    }

    client := dws.NewDwsClient(hcClient)

    request := &model.ShowQueryDetailRequest{}
	request.ClusterId = "{cluster_id}"
	request.QueryId = "{query_id}"
	response, err := client.ShowQueryDetail(request)
	if err == nil {
        fmt.Printf("%+v\n", response)
    } else {
        fmt.Println(err)
    }
}
 
 
  

For SDK sample code of more programming languages, see the Sample Code tab in API Explorer. SDK sample code can be automatically generated.

Status Codes

Status Code	Description
200	Query succeeded.
400	Request error.
401	Authentication failed.
403	You do not have required permissions.
404	No resources found.
500	Internal server error.
503	Service unavailable.

Parent Topic: Host Monitoring

Previous topic: Querying Table Skew Rate or Dirty Page Rate

Next topic: Querying the Metrics of a Cluster