Skip to main content
APentyala
Silver ⭐️⭐️
Apr 22, 2025 6:23 AM UTC
Solved

hql Script Error Handling in Beeline Execution

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? 

ggangadharan
Gold ⭐️
Apr 23, 2025 7:46 AM UTC
  • 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 ~]#​

1 reply

ggangadharan
Gold ⭐️
Gold ⭐️
Apr 23, 2025 7:46 AM UTC
  • 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 ~]#​
Translate