Hive: Run Hive Script File Having Batch of HQL Queries

Hive Command Line Options

To get help, run "hive -H" or "hive --help".
Usage (as it is in Hive 0.9.0):

usage: hive
 -d,--define <key=value>          Variable substitution to apply to hive
                                  commands. e.g. -d A=B or --define A=B
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
 -h <hostname>                    Connecting to Hive Server on remote host
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable substitution to apply to hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -p <port>                        Connecting to Hive Server on port number
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

Example of running a script non-interactively


   $HIVE_HOME/bin/hive -f /home/my/hive-script.sql



Example of running a script non-interactively and in silent mode

   $HIVE_HOME/bin/hive -f -S /home/my/hive-script.sql

Example of running a query from the command line


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

Example of dumping data out from a query into a file using silent mode


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

SOURCE: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Cli

Step 1: Open a file and write your HIVE queries and save it as filename.hql

e.g. 

h1.hql file has only one following line

select * from ipdpt_tony limit 100

Step 2:

You can now run the h1.hql using command explained above i.e.

> hive -f h1.hql 

If you want to redirect the output to a file, then

> hive -f h1.hql > result.dat

If you want to run the abive command from some script like Shell, Perl, or Python, then you can directly use the system call and use the line "hive -f h1.hql > result.dat"

e.g. in python:

import os
os.system("date")
os.system("hive -S -f h1.hql > d.txt")

There you go!!!

Now, you can automate the HQL stuff in any script language.