Saturday 6 September 2014

Recursively Pass Variables from a file to Hive Query in Shell

Sometimes, we have to recursively pass multiple variables into the where clause of Hive query.
For a hive example,

Select * from store where store_lat = ${lat} AND store_long = ${long};

The variable values are read from a small csv file.

#! /bin/bash
export IFS=","
while read SRLID StoreID StoreLatitude StoreLongitude Range
do ["${SRLID}" = "[SRLID]"] && continue//filter the head
     echo "$SRLID $StoreID $StoreLatitude $StoreLongitude\n" >> report.txt
     hive -hivevar lat=$StoreLatitude -hivevar long=$StoreLongitude -f hive.hql  | sed 's/[\t]/,/g'
>> report.txt
done < Test_001.csv


The hive query output uses '\t' as delimiter. So we replace '\t' as ',' in report.txt

Reference:
http://www.ryanchapin.com/fv-b-4-759/Running-Dynamically-Generated-Hive-Queries-From-a-Shell-Script.html http://stackoverflow.com/questions/12464636/how-to-set-variables-in-hive-scripts
http://stackoverflow.com/questions/13702476/hive-query-output-delimiter

No comments:

Post a Comment