Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

how to run hive SQL file from java?

Solved Go to solution

how to run hive SQL file from java?

New Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: how to run hive SQL file from java?

Contributor

@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");
    }
5 REPLIES 5
Highlighted

Re: how to run hive SQL file from java?

Contributor

@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");
    }

Re: how to run hive SQL file from java?

New Contributor

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.

Re: how to run hive SQL file from java?

New Contributor

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.

Re: how to run hive SQL file from java?

New Contributor

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

Re: how to run hive SQL file from java?

Contributor

@Devaraj Thaha

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

Thanks !