Querying SQL Execution Information

Function

This API is used to query SQL execution information.

Call Method

For details, see Calling APIs.

URI

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

**Table 1** URI parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Project ID. For details about how to obtain the ID, see Obtaining Project ID.
cluster_id	Yes	String	Cluster ID. For details about how to obtain the ID, see Obtaining the Cluster ID.
query_id	Yes	String	Query ID

**Table 2** Query parameters
Parameter	Mandatory	Type	Description
ctime	No	Long	Collection time

Request Parameters

None

Response Parameters

Status code: 200

**Table 3** Response body parameters
Parameter	Type	Description
code	Integer	Response code
msg	String	Response message
data	ListQueriesDto object	Response data

**Table 4** ListQueriesDto
Parameter	Type	Description
virtual_cluster_id	Integer	Virtual cluster ID
ctime	Long	Collection time
pid	String	Session ID
inst_name	String	Instance name
waiting	Boolean	Whether the backend is currently waiting on a lock. If yes, the value is true.
enqueue	String	Resource status in workload management
warning	String	Warnings related to SQL self-diagnosis and tuning
query	String	Query statement
lane	String	Fast or slow lane.
db_name	String	Database name
priority	String	Priority of a job in the resource pool. The value can be 1, 2, 4, or 8 (rush, high, medium, or low).
query_id	String	Internal query_id used for statement execution
query_band	String	Job type, which can be set using the guc parameter query_band. The default value is a null string.
job_name	String	The value is obtained from the query_band field. The position is 0.
job_inst	String	The value is obtained from the query_band field. The position is 1.
user_name	String	Username used for connecting to the backend
application_name	String	Name of the application that is connected to the backend.
client_address	String	IP address of the client connected to this backend.
client_hostname	String	Host name of the client.
client_port	String	TCP port number used by a client to communicate with the backend
start_time	Long	Time when a statement starts to be executed
block_time	Long	Block time before the statement is executed. The unit is ms.
duration	Long	Duration that a statement has been executed. The unit is ms.
estimate_total_time	Long	Estimated execution time of a statement. The unit is ms.
estimate_left_time	Long	Estimated remaining time of statement execution. The unit is ms.
resource_pool	String	Resource pool used by the user
control_group	String	Cgroup used by the statement.
min_peak_memory	Integer	Minimum memory peak of a statement across all DNs. The unit is MB.
max_peak_memory	Integer	Maximum memory peak of a statement across all DNs. The unit is MB.
average_peak_memory	Integer	Average memory usage during statement execution. The unit is MB.
memory_skew_percent	Integer	Memory usage skew of a statement among DNs.
estimate_memory	Integer	Estimated memory used by a statement. The unit is MB.
spill_info	String	Information about the statement spilling to disks on all DNs.
min_spill_size	Integer	Minimum spilled data among all DNs when a spill occurs, in MB. The default value is 0.
max_spill_size	Integer	Maximum spilled data among all DNs when a spill occurs, in MB. The default value is 0.
average_spill_size	Integer	Average spilled data among all DNs when a spill occurs, in MB. The default value is 0.
spill_skew_percent	Integer	DN spill skew.
min_dn_time	Long	Minimum execution time of a statement across all DNs. The unit is ms.
max_dn_time	Long	Minimum execution time of a statement across all DNs. The unit is ms.
average_dn_time	Long	Average execution time of a statement across all DNs. The unit is ms.
dntime_skew_percent	Integer	Execution time skew of a statement among DNs.
min_cpu_time	Long	Minimum CPU time of a statement across all DNs. The unit is milliseconds.
max_cpu_time	Long	Maximum CPU time of a statement across all DNs. The unit is milliseconds.
total_cpu_time	Long	Total CPU time of a statement across all DNs. The unit is milliseconds.
cpu_skew_percent	Integer	CPU time skew of a statement among DNs.
average_peak_iops	Integer	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.
iops_skew_percent	Integer	I/O skew of a statement among DNs.
max_peak_iops	Integer	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.
min_peak_iops	Integer	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.
query_plan	String	Query plan
query_status	String	Real-time running status (active, idle, idle in transaction, idle in transaction(aborted), fastpath function call, disabled) of the current query statement.
wlm_status	String	Running status (pending, running, finished, aborted, active, unknown) of the current query statement in the resource pool.
wlm_attrib	String	Statement attribute (ordinary, simple, complicated, internal)
system_query	Boolean	Whether a query is a system query
backend_start	Long	Time when this process was started, that is, when the client connected to the server
elapsed_time	Long	Execution time so far.
curr_xact_start	Long	Time when the current transaction was started (NULL if no transactions are active). If the current query is the first of its transaction, the value of this column is the same as that of the query_start column.
state_change	Long	Time when the status was changed in the previous time
query_start	Long	Time when a statement starts to be executed
query_elapsed_time	Long	Actual execution duration of the statement, in seconds.

Example Request

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

Example Response

Status code: 200

{
  "code" : 0,
  "msg" : "OK",
  "data" : {
    "ctime" : 1699062846000,
    "pid" : "140535026615872",
    "waiting" : false,
    "duration" : 0,
    "enqueue" : "",
    "warning" : "",
    "query" : "WLM fetch collect info from data nodes",
    "lane" : "",
    "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" : "",
    "wlm_attrib" : "",
    "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 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");

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

        DwsClient client = DwsClient.newBuilder()
                .withCredential(auth)
                .withRegion(DwsRegion.valueOf("<YOUR REGION>"))
                .build();
        ShowQueryDetailRequest request = new ShowQueryDetailRequest();
        request.withCtime(<ctime>L);
        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

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.getenv("CLOUD_SDK_AK")
    sk = os.getenv("CLOUD_SDK_SK")

    credentials = BasicCredentials(ak, sk) \

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

    try:
        request = ShowQueryDetailRequest()
        request.ctime = <ctime>
        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")

    auth := basic.NewCredentialsBuilder().
        WithAk(ak).
        WithSk(sk).
        Build()

    client := dws.NewDwsClient(
        dws.DwsClientBuilder().
            WithRegion(region.ValueOf("<YOUR REGION>")).
            WithCredential(auth).
            Build())

    request := &model.ShowQueryDetailRequest{}
	ctimeRequest:= int64(<ctime>)
	request.Ctime = &ctimeRequest
	response, err := client.ShowQueryDetail(request)
	if err == nil {
        fmt.Printf("%+v\n", response)
    } else {
        fmt.Println(err)
    }
}

          

        

      
     

For more SDK sample codes of programming languages, visit API Explorer and click the Sample Code tab. Example codes can be automatically generated.

Status Code

Status Code	Description
200	The SQL execution information is queried successfully.
400	Request error.
401	Authentication failed.
403	You do not have required permissions.
404	No resources found.
500	Internal server error.
503	Service unavailable.