hql Script Error Handling in Beeline Execution

APentyala — Tue, 22 Apr 2025 06:23:41 GMT

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?

Re: hql Script Error Handling in Beeline Execution

ggangadharan — Wed, 23 Apr 2025 07:46:46 GMT

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 ~]#

question Re: hql Script Error Handling in Beeline Execution in Support Questions

hql Script Error Handling in Beeline Execution

Re: hql Script Error Handling in Beeline Execution