Bash command to backfill Hive table—running multiple Hive commands with changing date variables

Question

Trying to figure out a way to backfill partitions of a ds partitioned Hive table.

I know how to run a Hive command from CLI, e.g.

$HIVE_HOME/bin/hive -e 'select a.col from tab1 a'

What I would like to do is provide a .txt file of different DS and have a new job run for each of those DS's, e.g.

    $HIVE_HOME/bin/hive -e 'INSERT OVERWRITE PARTITION ds = $DS_VARIABLE_HERE 
                            select a.col from tab1 a where ds = $DS_VARIABLE_HERE'

But I'm not so sure how to do this

I'm thinking of trying out

cat date_file.txt | hive -e 'query here'

But I'm not sure how to place the variable from the date_file file into the Hive query string.

BalaramRaju · Accepted Answer · 2016-06-27 19:09:55Z

1

My suggestion is to use shell command to iterate through the values:

Option 1:

If you have fixed set of values you want to iterate through then

DS_VARIABLE_HERE=('val1' 'val2' 'val3')

for ((i=0;i<${#DS_VARIABLE_HERE[@]};i++))
do
$HIVE_HOME/bin/hive -e "INSERT OVERWRITE PARTITION ds = ${DS_VARIABLE_HERE[$i]} select a.col from tab1 a where ds = ${DS_VARIABLE_HERE[$i]}"
done

Option 2:

if you want to iterate through lets say 1 to 10

for ((i=1;i<=10;i++))
do
$HIVE_HOME/bin/hive -e "INSERT OVERWRITE PARTITION ds = ${i} select a.col from tab1 a where ds = ${i}"
done

answered Jun 27, 2016 at 19:09

BalaramRaju

4392 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BalaramRaju Over a year ago

Since u want the values in .txt file. just use option 1. put the following part of code in .txt file : DS_VARIABLE_HERE=('val1' 'val2' 'val3') and in the shell script use "source file.txt"

Tyler Wood Over a year ago

The for ((i = ...) option, it would be difficult if the numbers aren't 0 padded

Collectives™ on Stack Overflow

Bash command to backfill Hive table—running multiple Hive commands with changing date variables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related