hql Script Error Handling in Beeline Execution

[#1]

Hi All,

I’ve created a .hql file containing multiple INSERT  statements  for Hive tables and I'm calling the script using Beeline. I need help with capturing error codes in a log file.

Also, if any INSERT statement fails, I want the continue the  remaining statements  and need to be write a log into file.

I am using below command but it is writing more details log 

> archive_$(date +\%Y\%m\%d).log 2>&1'

Please advise how to handle this? 

[#2 · Accepted solution]

• When using the -f flag with Beeline to execute an HQL script file, Beeline follows a fail-fast approach. If any single SQL statement fails, Beeline will stop execution immediately and exit with an error.
• This behavior helps ensure data integrity , it avoids executing dependent or subsequent statements that may rely on a failed operation (e.g., missing table, failed insert).
• Beeline does not support built-in error handling or continuation of script execution after a failed statement within the same script file.
• To continue executing remaining statements even if one fails:
• Use a Bash (or similar) wrapper script to:
  
  Loop over each statement/block.
  Execute each one using a separate beeline -e "<statement>"
  Check the exit code after each call.
  Log errors to a file (e.g., stderr, exit code, or custom messages).
  Proceed to the next statement regardless of the previous outcome.
  
  
• Sample BASH Script

[root@node4 ~]# cat run_hive_statements.sh
#!/bin/bash

# Set the HQL file to read SQL-like Hive queries from
HQL_FILE="sample_hql_success_failure.hql"

# Log file for capturing errors or output, named with the current date
LOG_FILE="error_log_$(date +%Y%m%d).log"

# Read the contents of the HQL file into a variable
HQL_CONTENT=$(cat "$HQL_FILE")

# Preprocess the file: ensure each SQL statement ends with a newline after semicolon
STATEMENTS=$(sed 's/;/;\n/g' "$HQL_FILE")

# Loop through each SQL-like statement (semicolon-separated)
for STATEMENT in "${STATEMENTS[@]}"; do

  # Clean up the statement: remove empty lines and comments
  SQL_SCRIPT=$(echo "$STATEMENT" | sed '/^\s*$/d' | sed '/^--.*$/d')

  # If the cleaned SQL statement is not empty, proceed
  if [ -n "$SQL_SCRIPT" ]; then

    # Split potential multiline statements using awk with record separator as ';'
    echo "$SQL_SCRIPT" | awk '
      BEGIN { RS=";" }
      {
        gsub(/^[ \t\r\n]+|[ \t\r\n]+$/, "", $0);  # Trim leading/trailing whitespace
        if(length($0)) print $0 ";"
      }
    ' | while read -r STATEMENT; do

      # Execute each final cleaned and trimmed SQL statement
      echo "Executing: $STATEMENT"
      beeline -n hive -p hive -e "$STATEMENT" >> "$LOG_FILE" 2>&1
      EXIT_CODE=$?
      if [ "$EXIT_CODE" -ne 0 ]; then
         echo "Error executing statement:"
         echo "$FINAL_STATEMENT;" >> "$LOG_FILE"
         echo "Beeline exited with code: $EXIT_CODE" >> "$LOG_FILE"
      fi
    done
  fi
done
[root@node4 ~]#​

• Sample HQL file

[root@node4 ~]# cat sample_hql_success_failure.hql
-- This script demonstrates both successful and failing INSERT statements

-- Successful INSERT
CREATE TABLE IF NOT EXISTS successful_table (id INT, value STRING);
INSERT INTO successful_table VALUES (1, 'success1');
INSERT INTO successful_table VALUES (2, 'success2');
SELECT * FROM successful_table;

-- Intentionally failing INSERT (ParseException)
CREATE TABLE IF NOT EXISTS failing_table (id INT, value INT);
INSERT INTO failing_table VALUES (1, 'this_will_fail);
SELECT * FROM failing_table;

-- Another successful INSERT (will only run if the calling script continues)
CREATE TABLE IF NOT EXISTS another_successful_table (id INT, data STRING);
INSERT INTO another_successful_table VALUES (101, 'continued_data');
SELECT * FROM another_successful_table;

-- Intentionally failing INSERT (table does not exist)
INSERT INTO non_existent_table SELECT * FROM successful_table;
[root@node4 ~]#​

• Sample ERROR log created as part of the script. 

[root@node4 ~]# grep -iw "error" error_log_20250423.log
Error: Error while compiling statement: FAILED: ParseException line 1:54 character '<EOF>' not supported here (state=42000,code=40000)
Error: Error while compiling statement: FAILED: SemanticException [Error 10001]: Line 1:12 Table not found 'non_existent_table' (state=42S02,code=10001)
[root@node4 ~]#​