Hive throwing ParseException while exporting a csv

hive -e 'SELECT *, MIN(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MIN_YEARLY, MAX(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MAX_YEARLY, AVG(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS AVG_YEARLY, STDDEV(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS STD_YEARLY FROM NSEDATA ORDER BY SYMBOL;' | sed 's/[[:space:]]+/,/g' > ~/output2.csv

​x
 
hive -e 'SELECT *, MIN(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MIN_YEARLY, MAX(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MAX_YEARLY, AVG(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS AVG_YEARLY, STDDEV(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS STD_YEARLY FROM NSEDATA ORDER BY SYMBOL;' | sed 's/[[:space:]]+/,/g' > ~/output2.csv​

Running this code, I am trying to export a csv but I get the following error:

ParseException line 2:3 cannot recognize input near ‘MIN’ ‘(‘ ‘HIGH’ in expression specification

Answer

Try to add table alias (d) and use select d.* instead of select *:

hive -e "SELECT d.*, 
MIN(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MIN_YEARLY, 
MAX(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MAX_YEARLY, 
AVG(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS AVG_YEARLY, 
STDDEV(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS STD_YEARLY 
FROM NSEDATA d 
ORDER BY SYMBOL" | sed 's/[[:space:]]+/,/g' > ~/output2.csv

 
hive -e "SELECT d.*, MIN(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MIN_YEARLY, MAX(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS MAX_YEARLY, AVG(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS AVG_YEARLY, STDDEV(HIGH) OVER(PARTITION BY SYMBOL ORDER BY TIMESTAMP) AS STD_YEARLY FROM NSEDATA d ORDER BY SYMBOL" | sed 's/[[:space:]]+/,/g' > ~/output2.csv​

Advertisement

Answer