Exporting the Top SQL Template List

Function

This API is used to enable Top SQL and export the Top SQL templates. 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. The options can be: RDS for MySQL and GaussDB(for MySQL) databases.
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 The value can be: 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. The value can be: 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 Maximum length: 36
error_msg	String	Error message. Minimum length: 2 Maximum length: 512

Status code: 500

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

Example Requests

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 Responses

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
}