Hive: SemanticException [Error 10002]: Line 3:21 Invalid column reference 'name'

Question

I am using the following hive query script for the version 0.13.0

DROP TABLE IF EXISTS movies.movierating;
DROP TABLE IF EXISTS movies.list;
DROP TABLE IF EXISTS movies.rating;
DROP DATABASE IF EXISTS movies;

ADD JAR /usr/local/hadoop/hive/hive/lib/RegexLoader.jar;

CREATE DATABASE IF NOT EXISTS movies;

CREATE EXTERNAL TABLE IF NOT EXISTS movies.list (id STRING, name STRING, genre STRING)
ROW FORMAT SERDE 'com.cisco.hadoop.loaders.RegexSerDe'with SERDEPROPERTIES(
 "input.regex"="^(.*)\\:\\:(.*)\\:\\:(.*)$",
 "output.format.string"="%1$s %2$s %3$s");

 CREATE EXTERNAL TABLE IF NOT EXISTS movies.rating (id STRING, userid STRING, rating STRING, timestamp STRING)
 ROW FORMAT SERDE 'com.cisco.hadoop.loaders.RegexSerDe'
 with SERDEPROPERTIES(
 "input.regex"="^(.*)\\:\\:(.*)\\:\\:(.*)\\:\\:(.*)$",
 "output.format.string"="%1$s %2$s %3$s %4$s");

 LOAD DATA LOCAL INPATH 'ml-10M100K/movies.dat' into TABLE movies.list;
 LOAD DATA LOCAL INPATH 'ml-10M100K/ratings.dat' into TABLE movies.rating;

 CREATE TABLE movies.movierating(id STRING, name STRING, genre STRING, rating STRING);

 INSERT OVERWRITE TABLE movies.movierating
 SELECT list.id, list.name, list.genre, rating.rating from movies.list list LEFT JOIN movies.rating rating ON (list.id=rating.id) GROUP BY list.id;

The issue is when I execute the script without the "GROUP BY" clause it works fine. But when I execute it with the "GROUP BY" clause, I get the following error

FAILED: SemanticException [Error 10002]: Line 4:21 Invalid column reference 'name'

Any ideas what is happening here?

Appreciate your help

Thanks!

Answer 1

If you group by a column, your select statement can only select a) that column, b) columns derived only from that column, or c) a UDAF applied to other columns.

In this case, you're only grouping by list.id, so when you try to select list.name, that's invalid. Think about it this way: what if your list table contained the following two entries:

id|name |genre
--+-----+------
01|name1|comedy
01|name2|horror

What would you expect this query to return:

select list.id, list.name, list.genre from list group by list.id;

In this case it's nonsensical. I'm guessing that id in reality is a primary key, but note that hive does not know this, so the above data set is perfectly valid.

With all that in mind, it's not clear to me how to fix it because I don't know the desired output. For example, let's say without the group by (just the join), you have as output:

id|name |genre |rating
--+-----+------+-------
01|name1|comedy|'pretty good'
01|name1|comedy|'bad'
02|name2|horror|'9/10'
03|name3|action|NULL

What would you want the output to be with the group by? What are you trying to accomplish by doing the group by?