How to capture a hive exit status or error code us

Question

Executing an insert query in hive using the JDBC API. But the query is not running. Could someone suggest what is going wrong. Also, please let me know how to capture the error code thrown by hive while running the queries. Hive version 0.13.0

When i am running the queries generated in the log in the command line they are working fine.

public static void onSuccess() {
        // Write to log on Success
        LOGGER.info("Job Succeeded, Updating the " + hiveDB + "." + logTable + " with status SUCCESS");
        String insertOnSuccess = "insert into table " + hiveDB + "." + logTable + " select " + currentJobID + "," + "'"
                + startTime + "'" + "," + "'" + stopTime + "'" + "," + runTime + "," + "\'SUCCESS\' from " + hiveDB
                + "." + "dual" + " limit 1; ";
        commonDB.InsertToTable(insertOnSuccess);
        JobMailer.PostMail("IB Load Successfully completed", "Load completed");

    }


public void InsertToTable(String insertquery) {
        try {

            stm = hiveConn.createStatement();
            stm.executeUpdate(insertquery);
        } catch (SQLException e) {
            LOGGER.error("Running the insert query for :" + insertquery);
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
        } finally {
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException e) {
                    LOGGER.error(e.getMessage());
                    ;
                }
            }
        }
    }

Here is my error log:

16/02/12 12:31:09 ERROR hiveconnector.CommonDBUtilities: Running the insert quer y for :insert into table installbase.IB_log select 25,'2016-02-12 12:26:43.037', '2016-02-12 12:31:09.057',22982400,'SUCCESS' from installbase.dual limit 1; 16/02/12 12:31:09 INFO hiveconnector.JobMailer: Sending Mail with :IB Load Succe ssfully completed 16/02/12 12:31:09 INFO hiveconnector.MainApp: Inserted record to the installbase .data_usage_governance_log Table 16/02/12 12:31:10 ERROR hiveconnector.CommonDBUtilities: Running the insert quer y for :Insert into table installbase.data_usage_governance_log select Data_Asset _Reference,File_Name,Origin_System,Transfer_System,'2016-02-12 12:26:43.037',Col umn_Reference,Element_Reference,Rule_Priority,Delete_By_Date,Classification,Geog raphic_Inclusion,Geographic_Restriction,Group_Inclusion,Group_Restriction,Reserv ed from installbase.data_usage_governance_master

Answer 1

Too bad you are stuck with Hive 0.13, because...

Starting with Hive 0.14.0, HiveServer2 operation logs are available for Beeline clients. https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-HiveServer2Logging

Once the log dispatch is activated server-side, you can retrieve these log entries from your Java code -- either asynchronously, or en masse when execution is over, with something like...

  private static void DumpHiveMessages (java.sql.Statement stmtGeneric)
  { org.apache.hive.jdbc.HiveStatement stmtExtended ;
    try
    { stmtExtended =(org.apache.hive.jdbc.HiveStatement)stmtGeneric ;
      for (String sLogMessage : stmtExtended.getQueryLog())
      { JustTraceIt("HIVE SAYS>" +sLogMessage) ;    } 
      if (stmtExtended.hasMoreLogs())
      { JustTraceIt("WARNING>(...log stream still open...") ; }
    }
    catch (Exception duh)
    { JustTraceIt("WARNING>Error while accessing Hive log stream");
      JustTraceIt("WARNING>" +MakeSenseOfDirtyHadoopException(duh)) ;
    }
  }

That stuff is not really documented, but there's the source code for HiveStatement that shows several non-JDBC-standard methods such as getQueryLog and hasMoreLogs -- also getYarnATSGuid for Hive 2+ and other stuff for Hive 3+.
Here is the link to the "master" branch on GitHub, switch to whichever version you are using (possibly an old 1.2 for compatibility with Spark).

Answer 2

I think there are still some issues in Hive insert/update/delete functionality is hive 1.2.1 . But you seem to be still in hive 0.14. So I wouldn't recommend using these functionalities yet. Please see similar issue happening to other users:

hive 1.2.1 error on delete command