Exporting the Top SQL Template List

Function

This API is used to export the Top SQL template list after Top SQL is enabled. This function is available only for paid instances. The maximum query interval is one hour.

URI

GET /v3/{project_id}/instances/{instance_id}/top-sql-templates

**Table 1** Path parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Project ID. To obtain this value, see Obtaining a Project ID.
instance_id	Yes	String	Instance ID.

**Table 2** Query parameters
Parameter	Mandatory	Type	Description
start_at	Yes	Long	Start time in Unix timestamp format, in milliseconds.
end_at	Yes	Long	End time in Unix timestamp format, in milliseconds.
datastore_type	Yes	String	Database type, which can be MySQL and TaurusDB
node_id	No	String	Node ID
sort	No	String	Sorting field (executeNum: execution times; totalCost: total duration; avgCost: average duration; totalScan: scanned rows; avgScan: average scanned rows). Default value: avgCost Enumerated values: executeNum totalCost avgCost totalScan avgScan
asc	No	Boolean	Sorting sequence. true indicates ascending order and false indicates descending order. Default value: false
offset	No	Integer	Offset. If offset is set to N, the resource query starts from the N+1 piece of data. The value is 0 by default, indicating that the query starts from the first piece of data. The value must be a number but cannot be a negative number.
limit	No	Integer	Number of records displayed on each page. The default value is 20 and the maximum value is 100.

Request Parameters

**Table 3** Request header parameters
Parameter	Mandatory	Type	Description
X-Auth-Token	Yes	String	User token.
X-Language	No	String	Request language type. Enumerated values: en-us zh-cn

Response Parameters

Status code: 200

**Table 4** Response body parameters
Parameter	Type	Description
top_sql_templates	Array of TopSqlTemplate objects	SQL templates.
total_count	Integer	Total number of SQL templates

**Table 5** TopSqlTemplate
Parameter	Type	Description
sql_template	String	SQL templates.
sql_sample_string	String	Example SQL.
sql_type	String	SQL type.
db_name	String	Database name.
execute_num	Long	Total executions.
total_cost	Double	Total duration (ms).
avg_cost	Double	Average duration (ms).
avg_rows_sent	Double	Avg. rows returned.
avg_rows_affected	Double	Avg. rows affected.
avg_lock_time	Double	Avg. lock wait time (ms).
total_rows_examined	Double	Total scanned rows.
avg_rows_examined	Double	Avg. rows scanned.
total_cost_ratio	String	Total duration (%).
total_examined_ratio	String	Scanned rows (%).
execute_num_ratio	String	Execution times (%).

Status code: 400

**Table 6** Response body parameters
Parameter	Type	Description
error_code	String	Error code. Minimum length: 8 characters Maximum length: 36 characters
error_msg	String	Error message. Minimum length: 2 characters Maximum length: 512 characters

Status code: 500

**Table 7** Response body parameters
Parameter	Type	Description
error_code	String	Error code. Minimum length: 8 characters Maximum length: 36 characters
error_msg	String	Error message. Minimum length: 2 characters Maximum length: 512 characters

Example Request

Exporting the Top SQL template list

GET https://das.cn-north-1.myhuaweicloud.com/v3/054c630ff780d4cc2f40c00d7f6fb21d/instances/6243b3fcf2f948578d46ed4c52fb54eein01/top-sql-templates?start_at=1611975464337&end_at=1611979064337&node_id=fec05693c76c4f389561051db430324cno01&sort=avgCost&asc=true&datastore_type=MySQL&offset=0&limit=10

Example Response

Status code: 200

Success

{
  "top_sql_templates" : [ {
    "db_name" : "db_01",
    "execute_num" : 30,
    "avg_rows_examined" : 0,
    "total_cost_ratio" : "100.00%",
    "avg_lock_time" : 0,
    "sql_template" : "SELECT sleep(?)",
    "avg_rows_affected" : 0,
    "avg_rows_sent" : 1,
    "avg_cost" : 5000.266666666666,
    "execute_num_ratio" : "23.81%",
    "total_examined_ratio" : "-",
    "sql_type" : "select",
    "total_cost" : 150008,
    "total_rows_examined" : 0,
    "sql_sample_string" : "select sleep(5)"
  } ],
  "total_count" : 1
}