DocsICP FilingConsole
Documentation

DescribeSQLPatterns

Updated at:
Copy as MD
My Favorites

Queries the list of SQL patterns for an AnalyticDB for MySQL Data Lakehouse Edition cluster within a specified time range.

Operation description

  • Central public endpoint: adb.aliyuncs.com.

  • Regional public endpoint: adb.<region-id>.aliyuncs.com. Example: adb.cn-hangzhou.aliyuncs.com.

  • Regional VPC endpoint: adb-vpc.<region-id>.aliyuncs.com. Example: adb-vpc.cn-hangzhou.aliyuncs.com.

Try it now

Try this API in OpenAPI Explorer, no manual signing needed. Successful calls auto-generate SDK code matching your parameters. Download it with built-in credential security for local usage.

Test

RAM authorization

The table below describes the authorization required to call this API. You can define it in a Resource Access Management (RAM) policy. The table's columns are detailed below:

  • Action: The actions can be used in the Action element of RAM permission policy statements to grant permissions to perform the operation.

  • API: The API that you can call to perform the action.

  • Access level: The predefined level of access granted for each API. Valid values: create, list, get, update, and delete.

  • Resource type: The type of the resource that supports authorization to perform the action. It indicates if the action supports resource-level permission. The specified resource must be compatible with the action. Otherwise, the policy will be ineffective.

    • For APIs with resource-level permissions, required resource types are marked with an asterisk (*). Specify the corresponding Alibaba Cloud Resource Name (ARN) in the Resource element of the policy.

    • For APIs without resource-level permissions, it is shown as All Resources. Use an asterisk (*) in the Resource element of the policy.

  • Condition key: The condition keys defined by the service. The key allows for granular control, applying to either actions alone or actions associated with specific resources. In addition to service-specific condition keys, Alibaba Cloud provides a set of common condition keys applicable across all RAM-supported services.

  • Dependent action: The dependent actions required to run the action. To complete the action, the RAM user or the RAM role must have the permissions to perform all dependent actions.

Action

Access level

Resource type

Condition key

Dependent action

adb:DescribeSQLPatterns

list

*All Resource

*

 None None

Request parameters

Parameter

Type

Required

Description

Example
DBClusterId

string

Yes

The ID of the AnalyticDB for MySQL Data Lakehouse Edition cluster.

Note

You can call the DescribeDBClusters operation to query the details of all AnalyticDB for MySQL Data Lakehouse Edition clusters in a specified region, including the cluster IDs.

amv-8vb8de93v9b****
StartTime

string

No

The start time of the query. Specify the time in UTC in the yyyy-MM-ddTHH:mm:ssZ format.

Note

  • You can only query data from the last 14 days.

  • The interval between the start time and the end time cannot exceed 24 hours.

2022-09-06T03:06:00Z
EndTime

string

No

The end time of the query. Specify the time in UTC in the yyyy-MM-ddTHH:mm:ssZ format.

Note

The end time must be later than the start time.

2022-09-07T03:06:00Z
RegionId

string

Yes

The region ID.

cn-hangzhou
Keyword

string

No

The search keyword.

SELECT
Order

string

No

The sorting rule for the query results. The format is a JSON character string. Example: [{"Field":"AverageQueryTime","Type":"Asc"}]. The JSON character string contains the following parameters:

  • Field: the field by which to sort the results. Valid values:

    • PatternCreationTime: the earliest commit time of the pattern within the query time range.

    • AverageQueryTime: the average total time consumed by the pattern within the query time range.

    • MaxQueryTime: the maximum total time consumed by the pattern within the query time range.

    • AverageExecutionTime: the average execution duration of the pattern within the query time range.

    • MaxExecutionTime: the maximum execution duration of the pattern within the query time range.

    • AveragePeakMemory: the average peak memory usage of the pattern within the query time range.

    • MaxPeakMemory: the maximum peak memory usage of the pattern within the query time range.

    • AverageScanSize: the average amount of data read by the pattern within the query time range.

    • MaxScanSize: the maximum amount of data read by the pattern within the query time range.

    • QueryCount: the number of executions of the pattern within the query time range.

    • FailedCount: the number of failed executions of the pattern within the query time range.

  • Type: the sort order. The value is not case-sensitive. Valid values:

    • Asc: ascending order.

    • Desc: descending order.

[{"Field":"AverageQueryTime","Type":"Asc"}]
PageNumber

integer

No

The page number. Valid values: integers greater than 0 and up to the maximum integer value. Default value: 1.

2
PageSize

integer

No

The number of entries per page. Valid values:

  • 10 (default value)

  • 30

  • 50

  • 100

10
Lang

string

No

The language configuration. Valid values:

  • zh: Simplified Chinese (default language).

  • en: English.

  • ja: Japanese.

  • zh-tw: Traditional Chinese.

zh
UserName

string

No

The name of the database account that executes the SQL statement.

test_user
SqlPatternHash

integer

No

The hash value of the SQL pattern, which is used to filter a specific SQL pattern.

Response elements

Element

Type

Description

Example

object

The detailed information of the SQL patterns.

PageNumber

integer

The page number.

2
PageSize

integer

The number of entries per page.

10
TotalCount

integer

The total number of entries.

20
PatternDetails

array<object>

The list of patterns.

object

The detailed information of the SQL pattern.

SQLPattern

string

The SQL statement of the pattern.

SELECT * FROM KEPLER_META_NODE_STATIC_INFO WHERE elastic_node = ? OR (elastic_node = ? AND enable = ?)
PatternId

string

The ID of the SQL pattern.

5575924945138******
User

string

The name of the database user that submitted the SQL statement related to the pattern.

test
AccessIp

string

The IP address of the client that submitted the SQL statement related to the pattern.

192.168.xx.xx
Tables

string

The list of tables scanned by the SQL pattern.

tpch.orders
PatternCreationTime

string

The earliest submission time of the pattern within the specified time range.

2022-09-06 05:06:00
AverageQueryTime

number

The average total time consumed by the pattern within the specified time range. Unit: milliseconds.

4
MaxQueryTime

integer

The maximum total time consumed by the pattern within the specified time range. Unit: milliseconds.

2341
AverageExecutionTime

number

The average execution time of the pattern within the specified time range. Unit: milliseconds.

234.78
MaxExecutionTime

integer

The maximum execution time of the pattern within the specified time range. Unit: milliseconds.

2142
AveragePeakMemory

number

The average peak memory usage of the pattern within the specified time range. Unit: bytes.

234.22
MaxPeakMemory

integer

The maximum peak memory usage for the SQL statement related to the pattern. Unit: bytes.

234149
AverageScanSize

number

The average amount of data read by the pattern within the specified time range. Unit: bytes.

234149.23
MaxScanSize

integer

The maximum amount of data read by the SQL statement related to the pattern. Unit: bytes.

32212254
QueryCount

integer

The number of executions of the pattern within the specified time range.

345
FailedCount

integer

The number of failed executions of the pattern within the specified time range.

18
Blockable

boolean

Specifies whether the execution of the current SQL pattern can be blocked. Valid values:

  • true: The execution can be blocked.

  • false: The execution cannot be blocked.

Note

Currently, AnalyticDB for MySQL only supports blocking SELECT and INSERT statements.

true
QueryTimeSum

number

The total time consumed. Unit: milliseconds.

5
QueryTimePercentage

number

The percentage of the time consumed by a single pattern within the query time range out of the total time consumed by all patterns. Unit: %.

10
PeakMemorySum

number

The total peak memory usage. Unit: bytes.

5
PeakMemoryPercentage

number

The percentage of the total peak memory usage of a single pattern within the query time range out of the total peak memory usage of all patterns. Unit: %.

10
ScanSizeSum

number

The total amount of scanned data. Unit: bytes.

5
ScanSizePercentage

number

The percentage of the total amount of data scanned by a single pattern within the query time range out of the total amount of data scanned by all patterns. Unit: %.

80
AverageOperatorCost

number

The average CPU cost. Unit: milliseconds.

5
MaxOperatorCost

number

The maximum CPU cost. Unit: milliseconds.

5
OperatorCostSum

number

The total CPU cost. Unit: milliseconds.

5
OperatorCostPercentage

number

The percentage of the total CPU cost. Unit: %.

20
AverageScanCost

number

The average scan time.

5
MaxScanCost

number

The maximum scan time.

5
ScanCostSum

number

The total data scan cost. Unit: milliseconds.

5
ScanCostPercentage

number

The percentage of the total data scan cost. Unit: %.

5
RequestId

string

The request ID.

F3174013-5B7A-5A47-9FE0-6B5D397BD86A
AccessDeniedDetail

string

The details of the access denial. This field is returned only when RAM authentication fails.

{ "PolicyType": "AccountLevelIdentityBasedPolicy", "AuthPrincipalOwnerId": "1*****************7", "EncodedDiagnosticMessage": "AQIBIAAAAOPdwKY2QLOvgMEc7SkkoJfj1kvZwsaRqNYMh10Tv0wTe0fCzaCdrvgazfNb0EnJKETgXyhR+3BIQjx9WAqZryejBsp1Bl4qI5En/D9dEhcXAtKCxCmE2kZCiEzpy8BoEUt+bs0DmlaGWO5xkEpttypLIB4rUhDvZd+zwPg4EXk4KSSWSWsurxtqDkKEMshKlQFBTKvJcKwyhk62IeYly4hQ+5IpXjkh1GQXuDRCQ==", "AuthPrincipalType": "SubUser", "AuthPrincipalDisplayName": "2***************9", "NoPermissionType": "ImplicitDeny", "AuthAction": "adb:DescribeExcessivePrimaryKeys" }

Examples

Success response

JSON format

{
  "PageNumber": 2,
  "PageSize": 10,
  "TotalCount": 20,
  "PatternDetails": [
    {
      "SQLPattern": "SELECT * FROM KEPLER_META_NODE_STATIC_INFO WHERE elastic_node = ? OR (elastic_node = ? AND enable = ?)",
      "PatternId": "5575924945138******",
      "User": "test",
      "AccessIp": "192.168.xx.xx",
      "Tables": "tpch.orders",
      "PatternCreationTime": "2022-09-06 05:06:00",
      "AverageQueryTime": 4,
      "MaxQueryTime": 2341,
      "AverageExecutionTime": 234.78,
      "MaxExecutionTime": 2142,
      "AveragePeakMemory": 234.22,
      "MaxPeakMemory": 234149,
      "AverageScanSize": 234149.23,
      "MaxScanSize": 32212254,
      "QueryCount": 345,
      "FailedCount": 18,
      "Blockable": true,
      "QueryTimeSum": 5,
      "QueryTimePercentage": 10,
      "PeakMemorySum": 5,
      "PeakMemoryPercentage": 10,
      "ScanSizeSum": 5,
      "ScanSizePercentage": 80,
      "AverageOperatorCost": 5,
      "MaxOperatorCost": 5,
      "OperatorCostSum": 5,
      "OperatorCostPercentage": 20,
      "AverageScanCost": 5,
      "MaxScanCost": 5,
      "ScanCostSum": 5,
      "ScanCostPercentage": 5
    }
  ],
  "RequestId": "F3174013-5B7A-5A47-9FE0-6B5D397BD86A",
  "AccessDeniedDetail": "{\n    \"PolicyType\": \"AccountLevelIdentityBasedPolicy\",\n    \"AuthPrincipalOwnerId\": \"1*****************7\",\n    \"EncodedDiagnosticMessage\": \"AQIBIAAAAOPdwKY2QLOvgMEc7SkkoJfj1kvZwsaRqNYMh10Tv0wTe0fCzaCdrvgazfNb0EnJKETgXyhR+3BIQjx9WAqZryejBsp1Bl4qI5En/D9dEhcXAtKCxCmE2kZCiEzpy8BoEUt+bs0DmlaGWO5xkEpttypLIB4rUhDvZd+zwPg4EXk4KSSWSWsurxtqDkKEMshKlQFBTKvJcKwyhk62IeYly4hQ+5IpXjkh1GQXuDRCQ==\",\n    \"AuthPrincipalType\": \"SubUser\",\n    \"AuthPrincipalDisplayName\": \"2***************9\",\n    \"NoPermissionType\": \"ImplicitDeny\",\n    \"AuthAction\": \"adb:DescribeExcessivePrimaryKeys\"\n}"
}

Error codes

See Error Codes for a complete list.

Release notes

See Release Notes for a complete list.