Member since
07-15-2024
7
Posts
2
Kudos Received
0
Solutions
04-23-2025
12:46 AM
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 ~]#
... View more
03-18-2025
01:14 AM
Hi, @APentyala Thanks for pointing out this. Impala drivers also works well on this. Both Impala and Hive drivers can work on this. I will replace the images so that it matches the descriptions 👍🏻
... View more
02-04-2025
06:33 AM
Use CAST to convert to TIMESTAMP type. SELECT CAST('2024-11-05 10:03:17.872195' AS TIMESTAMP) AS timestamp_value; We can also try TIMESTAMP WITH LOCAL TIME ZONE, This helps retain precision when dealing with timezones. SELECT CAST('2024-11-05 10:03:17.872195' AS TIMESTAMP WITH LOCAL TIME ZONE);
... View more