Hive: Covert String to Boolean

Question

I have two HIVE tables and I need to convert String column from table 1 to Boolean column to table 2. The only function what I found is CAST but it works only in case the input is numeric value. For example this return "false" what is correct:

SELECT CAST(0 AS BOOLEAN); # Result is true

But my source is STRING column which consist values as "false" and then the cast function return the following result what is not correct for me:

SELECT CAST("false" AS BOOLEAN); # Result is true

Please do you know other way how to convert STRING column into BOOLEAN column in HIVE?

Answer 1

it is a little workaround but it works. i used the case function. The sample:

# Create TMP_TABLE1
CREATE TEMPORARY TABLE  my_db.TMP_TABLE1
(   
    bool_val_str STRING
);

# Create TMP_TABLE2
CREATE TEMPORARY TABLE  my_db.TMP_TABLE2
(   
    bool_val_bool BOOLEAN
);

# Insert values into TMP_TABLE1
INSERT INTO my_db.TMP_TABLE1 VALUES ("false"),("False"),("FALSE"),("true"),("True"),("TRUE");

# ===========================================================
# THE CONVERSION IS HERE:
# Insert values into TMP_TABLE2 with conversion to boolean
# ===========================================================
INSERT INTO my_db.TMP_TABLE2 
(
    bool_val_bool
) 
    SELECT 
        CASE lower(bool_val_str) WHEN "true" THEN true WHEN "false" THEN false ELSE NULL END
    FROM 
         my_db.TMP_TABLE1;