CODEWITHSUNDEEP
hivehiveql
jone sterling
jone sterling

Reputation: 27

Why can I get the FAILED:Invalid table alias or column reference ‘ ’: (possible column names are: line) when I queried in Hive?

I have a table which struct is:

line      string

and the content is:

product_id product_date orderattribute1 orderattribute2 orderattribute3     orderattribute4 ciiquantity ordquantity price
1 2014-09 2 1 1 1 1 3 153
1 2014-01 2 1 1 1 1 1 153
1 2014-04 2 2 1 1 1 1 164
1 2014-02 2 1 1 1 3 4 162
1 2014-07 2 1 1 1 9 23 224
1 2014-08 2 1 1 1 1 7 216
1 2014-03 2 1 1 1 3 13 180
1 2014-08 2 2 1 1 4 6 171
1 2014-05 2 1 1 1 3 7 180
....
(19000 lines omited)

the total price of every line above is ordquantity*price

I want to get the total price of every month like this:

month   sum
201401  ****
201402  ****

Accoding to just 10 lines in the table above,the sum of month 201408 is 7*216+6*171 which is derived from (1 2014-08 2 1 1 1 1 7 216 and 1 2014-08 2 1 1 1 1 7 216).

I use the code:

create table product as select sum(ordquantity*price) as sum from text3 group by product_date;

and I got the FAILED:

FAILED:Invalid table alias or column reference 'product_date': (possible column names are: line)

I am not familiar with Hive,I don't know how to solv the problem.

Upvotes: 0

Views: 2400

Answers (1)

D Sai Krishna
D Sai Krishna

Reputation: 178

Did you just create the table with correct schema? Well in case, if you didn't 

CREATE TABLE product
(product_id INT 
product_date STRING
orderattribute1 INT
orderattribute2 INT
orderattribute3 INT
orderattribute4 INT
ciiquantity INT
ordquantity INT
price INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

And the code for your requirment,

SELECT product_date, SUM(ordquantity*price) FROM product
GROUP BY product_date;

Hope I answered your question. Yippee!!

Upvotes: 1

Related Questions