Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Hive query output to text with header and column name having space

Hive query output to text with header and column name having space

New Contributor

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?

 

 

Don't have an account?
Coming from Hortonworks? Activate your account here