I have a hive able product with rating
Id productid rating ProdBarCode
42 96 5 881107178
168 151 5 884288058
110 307 4 886987260
58 144 4 884304936
90 648 4 891384754
271 346 4 885844430
62 21 3 879373460
279 832 3 881375854
237 514 4 879376641
I want to write a query find average product rating of product to pipe separated text file with header using hive -e"query" > output.txt
OUTPUT Format:-|Productid|average rating|
Solution:
hive -e " select C.value from (select 1 key, '|Productid|average rating|' value union all select 2 key , concat('|',concat_ws('|', Productid, averagerating),'|') value from (select CAST(A.productid AS STRING) AS Productid, CAST(A.averagerating AS STRING) AS averagerating from (select productid, avg(rating) averagerating from product group by productid sort by productid ) AS A where A.averagerating > 2) B sort by key) C " > output.txt
Is this query correct? Is there any other simple way to redirect the output in text file with header and column name with space(average rating)
Any suggestions?
