Help Center/ Data Admin Service/ API Reference/ APIs (in OBT)/ Cloud DBA/ Exporting the Top SQL Template List
Updated on 2024-12-04 GMT+08:00

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
}

Status Codes

Status Code

Description

200

Success

400

Client error

500

Server error

Error Codes

For details, see Error Codes.