Hive中如何重命名/过滤未指定别名的count(distinct(id))派生列？

Got it, let's tackle this problem step by step. You don't need to drop and recreate the table—there are two straightforward ways to handle this:

1. Filter the column directly (no table structure changes needed) #

When you create a Hive table using CREATE TABLE ... AS SELECT without aliasing a derived column, Hive automatically assigns a default name to it following the pattern _c0, _c1, _c2, etc. In your case:

• The date column retains its original name since it's explicitly referenced
• The count(distinct(id)) column will be named _c1 (as it's the second column in your SELECT clause)

First, confirm the exact column name and data type by running this quick check:

DESCRIBE temp;

Once you have the correct default name (it's almost certainly _c1 here), you can use it directly in your filter query. For example, if you want to get dates where the distinct ID count is greater than 10:

SELECT date, _c1 AS distinct_id_count
FROM temp
WHERE _c1 > 10;

The AS distinct_id_count part is optional but makes your query results easier to read.

2. Permanently rename the column (cleaner for future use) #

If you want to avoid the clunky _c1 name for all future queries, you can rename the column using Hive's ALTER TABLE command. Again, start with DESCRIBE temp to confirm the current column name and its data type (count functions typically return BIGINT or INT).

Assuming the column is _c1 and its type is BIGINT, run this command:

ALTER TABLE temp
CHANGE COLUMN _c1 distinct_id_count BIGINT;

Make sure the data type matches exactly what DESCRIBE shows you—if it's INT instead of BIGINT, use that. After running this, you can query the table with the proper, readable alias going forward:

SELECT date, distinct_id_count
FROM temp
WHERE distinct_id_count > 10;

内容的提问来源于stack exchange，提问作者thebytewalker