Working with Boolean in Apache Hive and Impala

George Boole described an algebraic logic system in the mid-19th century. The BOOLEAN data type is named after him. Boolean data type is a data type that has one of two possible values (usually true and false) meant to represent the two true values of logic and boolean algebra.

BOOLEAN in Relational Database Systems:
We use NUMBER(1) in Oracle to store Boolean values. In SQL Server and MySQL, we use BIT data type that accepts 1 or 0.  In PostgreSQL, Apache Hive and Apache Impala, we have BOOLEAN data type that accept TRUE or FALSE values. 

Let’s see how to implement Boolean data type in Apache Hive and Apache Impala.

CREATE TABLE Customers(
CustomerID INT,
CustomerName STRING,
ActiveStatus BOOLEAN
);

In the above example, the column ActiveStatus is assigned with BOOLEAN data type. The below data has been inserted through Impala-shell.

INSERT INTO Customers VALUES(100, ‘John’, true);
INSERT INTO Customers VALUES(200, ‘Kate’, false);
INSERT INTO Customers VALUES(300, ‘Julia’, true);
INSERT INTO Customers VALUES(400, ‘Maddy’, false);

If you look at the table properties in both Apache Hive and Impala, the following will be displayed.

This slideshow requires JavaScript.

Notes:
Do not use quotation marks around the TRUE and FALSE literal values.
You can write the literal values in uppercase, lowercase, or mixed case.
The values queried from a table are always returned in lowercase, true or false.

If you want to convert the true and false values into numbers, use the below command.

SELECT *, CAST(ActiveStatus AS INT) FROM Customers;



Hope you liked this post.
Please click on the follow button to get updates on latest posts.