AnalyticDB:MAX_PT() function

Prerequisites

• An AnalyticDB for MySQL cluster of V3.2.4.0 or later is created.

  Note

  • To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

  • To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

• A MaxCompute project is created in the same region as the AnalyticDB for MySQL cluster.

Usage notes

• The MAX_PT() function is suitable only for MaxCompute external tables. If you invoke the MAX_PT() function on internal tables or other types of external tables, an error occurs. For information about how to create a MaxCompute external table, see CREATE EXTERNAL TABLE.

• The MaxCompute external tables for which the MAX_PT() function is used must be partitioned tables and have data in at least one partition. If the preceding requirements are not met, the MAX_PT() function fails to be executed.

Syntax

MAX_PT(<table_full_name>)

Parameter

<table_full_name>: the name of the partitioned MaxCompute external table. Format: <database>.<tableName> or <tableName>. The parameter must be of the STRING type.

Return value

The alphabetically largest value of the level-1 partition key column is returned. The data type of the return value is determined by the data type of the partition key column defined in the MaxCompute external table. Supported data types: BOOLEAN, BIGINT, and STRING.

Example

Create an external table named customer that has the following partitions: 20241209, 20241210, and 20241211. Set the name of the partition key column to dt.

CREATE EXTERNAL TABLE IF NOT EXISTS  customer (
	id int,
	name varchar(1023),
	age int,
	dt STRING
) ENGINE='ODPS'
TABLE_PROPERTIES='{
	"accessid":"LTAILd4****",
	"endpoint":"http://service.cn-hangzhou.maxcompute.aliyun.com/api",
	"accesskey":"4A5Q7ZVzcYnWMQPysX****",
	"partition_column":"dt",
	"project_name":"test_adb",
	"table_name":"person"
}';

Query the number of rows in the partition whose partition key column value is the largest in the customer table.

SELECE COUNT(*) FROM customer WHERE dt=MAX_PT('customer');

In this example, AnalyticDB for MySQL accesses only the 20241211 partition of the MaxCompute external table. This prevents scanning on all partitions and significantly improves the query performance and overall computing efficiency.