How to group values based on key in Alibaba MaxCompute?

Question

I am facing issues when trying to group the values based on the key in Alibaba MaxCompute. I used similar queries before in Alibaba Cloud MaxCompute but now it throws me an error.

For instance,

SELECT * FROM table GROUP BY key;

It should work but it throws me error like

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - column reference table.value should appear in GROUP BY key

Anyone could help me with this to understand why this error occurs.

Answer 1

In the earlier version of MaxCompute, select * from group by key is supported even when the columns that match * are not included in the GROUP BY key. Compatible with Hive, MaxCompute 2.0 prohibits this syntax unless the GROUP BY list is a column in all source tables

Examples:

Scenario 1: The GROUP BY key does not include all columns.

Incorrect Syntax:

SELECT * FROM t GROUP BY key;

Correct Syntax:

SELECT DISTINCT key FROM t;

Scenario 2: The GROUP BY key includes all columns.

Not recommended syntax:

SELECT * FROM t GROUP BY key, value; -- t has columns key and value

Recommended syntax:

SELECT DISTINCT key, value FROM t;