how to run hive SQL file from java?

[#1]

I have use case that to execute .sql file from java connecting to hive server. SQL file is added with the Jars(SQL file has many statements). the final output of it will be a text file. Basically I have to achieve the below command in java and read the output file out of it. I have gone through many blogs but no luck. Please help me with the examples. or how can i achieve this use case.

hive -f test.sql > test.txt

[#2 · Accepted solution]

@Devaraj Thaha

This is the best example for connecting to hive server from Java using JDBC : https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-JDBCClientSa...

You only need to modify the code little bit to read queries from your file and store the result set to a file instead of printing .

But if you are interested in directly executing "hive -f test.hql" from Java then you can create a runtime process and execute it .

p = Runtime.getRuntime().exec((new
 String[]{"hive","-f","/hive/scripts/test.hql"}));
    p.waitFor();

    BufferedReader reader =
         new BufferedReader(new InputStreamReader(p.getInputStream()));

    String line = "";
    while ((line = reader.readLine())!= null) {
sb.append(line + "\n");
    }

[#3]

Hi @rtrivedi

I have the same question and find this answer. And it worked properly!

But I have another question about this answer. Why can't use:

String command = "hive -f /hive/scripts/test.hql";
p =Runtime.getRuntime().exec(command); 

or

String command = "sh hive -f /hive/scripts/test.hql";
p =Runtime.getRuntime().exec(command); 

I try both of them and they didn't work.

[#4]

I think I find the answer.

The reason that

String command = "hive -f /hive/scripts/test.hql";
p =Runtime.getRuntime().exec(command); 

or

String command = "sh hive -f /hive/scripts/test.hql";
p =Runtime.getRuntime().exec(command); 

can't work is:

the Runtime.getRuntime().exec() method bring the full string to bash. But the bash parser can't understand the command. Bash parser need to parse the command step by step:

1. Read data to execute
2. Process quotes
3. Split the read data into commands
4. Parse special operators
5. Perform expansions
6. Split the command into a command name and arguments
7. Execute the command

A full string can't be parsed.

[#5]

Thanks a lot, RTrivedi for your response. It's working. And for my use-case, i used JSCH to connect hive server.

[#6]

@Devaraj Thaha

Great to know that your problem is resolved . Can you please accept this answer .

Thanks !