Export Details about All SQL Statements

Updated on 2025-03-07 GMT+08:00

View PDF

Function

After SQL Explorer is enabled, you can create a SQL Insight task to export all SQL statement details by node, username, database, and operation type. This function is available only for paid instances.

URI

GET /v3/{project_id}/instances/{instance_id}/full-sql-search

**Table 1** URI parameters
Parameter	Mandatory	Type	Description
project_id	Yes	String	Explanation: Project ID of a tenant in a region To obtain this value, see Obtaining a Project ID. Constraints: N/A Values: The value can contain 32 characters. Only letters and digits are allowed. Default value: N/A
instance_id	Yes	String	Explanation: Unique ID of an instance Constraints: N/A Values: The value can contain 32 characters. Only letters and digits are allowed. Default value: N/A

**Table 2** Query parameters
Parameter	Mandatory	Type	Description
start_at	Yes	Long	Start time in the Unix timestamp format, in milliseconds
end_at	Yes	Long	End time in the Unix timestamp format, in milliseconds
task_ids	No	Array of long	SQL Insight task IDs. SQL Insight tasks that span more than one day will be split for parsing. Multiple tasks can be filtered and exported at the same time.
node_id	No	String	Node ID
keyword	No	String	One or multiple keywords separated by commas
fuzzy	No	String	Whether fuzzy match is used
user_list	No	String	One or multiple usernames separated by commas
db_list	No	String	One or multiple database names separated by commas
operation_list	No	String	One or multiple operation types separated by commas
client_ip_list	No	String	One or multiple client IP addresses separated by commas
thread_id_list	No	String	One or multiple thread IDs separated by commas
trx_id_list	No	String	One or multiple transaction IDs separated by commas
session_id_list	No	String	One or multiple session IDs separated by commas
status_list	No	String	One or multiple execution statuses separated by commas 0: successful 1: failed
sql_template_ids	No	String	One or multiple SQL template IDs separated by commas
cost_min	No	Double	Minimum execution duration, in milliseconds
cost_max	No	Double	Maximum execution duration, in milliseconds
scan_min	No	Long	Minimum rows scanned
scan_max	No	Long	Maximum rows scanned
affect_min	No	Long	Minimum rows affected
affect_max	No	Long	Maximum rows affected
return_min	No	Long	Minimum rows returned
return_max	No	Long	Maximum rows returned
sort_field	No	String	Sorting field execute_at: execution time execute_cost: execution duration lock_wait_time: lock wait time rows_examined: scanned rows rows_returned: returned rows
asc	No	Boolean	Sorting order true: ascending false: descending
page	No	Integer	Page number
limit	No	Integer	Records on each page. 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
full_sql_details	Array of Table 5 objects	Details about all SQL statements
total	Integer	Total number

**Table 5** full_sql_details
Parameter	Type	Description
sql	String	SQL statement
sql_template_id	String	SQL template ID
operate_type	String	Operation type
status	String	Status
error_no	String	Error code
database	String	Database
thread_id	String	Thread ID
client	String	Client
user	String	User
execute_at	Long	Execution start time in the Unix timestamp format, in milliseconds
query_time	Double	Execution duration, in milliseconds
lock_time	Double	Lock wait time (ms)
rows_examined	Long	Scanned rows
rows_sent	Long	Returned rows
rows_affected	Long	Updated rows
trx_id	Long	Transaction ID
cpu_time	Double	CPU time (μs)
send_bytes	Long	Output bytes
query_tables	String	All table names in the format of Database name.Table name\|Database name.Table name
innodb_io_read_bytes	Long	Physical I/O read bytes
innodb_io_read	Integer	Physical I/O reads
innodb_io_read_wait	Double	Physical I/O wait time, in milliseconds
innodb_lock_wait	Double	Row lock wait time, in milliseconds
innodb_queue_wait	Double	InnoDB lock wait time, in milliseconds
kernel_version	String	Kernel version for DDM instances
query_time_detail	String	Time required at each phase during SQL statement execution. This parameter is used by DDM instances.
session_id	String	Session ID
node_id	String	Node ID

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

GET https://das.cn-north-1.myhuaweicloud.com/v3/054e292c9880d4992f02c0196d3ea468/instances/da304cd5bbb944de828759bc7be3d3fein01/full-sql-search?start_at=1738733117000&end_at=1738808281000&task_ids=1128

Example Response

Status code: 200

{
  "full_sql_details": [
    {
      "sql": "select sleep(90)",
      "sql_template_id": "AAD913807ECE9C66",
      "operate_type": "select",
      "status": "success",
      "error_no": "",
      "database": "test_db",
      "thread_id": "5866666",
      "client": "100.79.3.154",
      "user": "root",
      "execute_at": 1738808280958,
      "query_time": 90000,
      "lock_time": 0,
      "rows_examined": 0,
      "rows_sent": 1,
      "rows_affected": 0,
      "trx_id": 0,
      "cpu_time": 0,
      "node_id": "fec05693c76c4f389561051db430324cno01"
    }
  ],
  "total": 1036
}