Hive CAST(from_datatype as to_datatype) function is used to convert from one data type to another for example to cast String to Integer(int), String to Bigint, String to Decimal, Decimal to Int data types, and many more. This cast() function is referred to as the type conversion function which is used to convert data types in Hive.
In this article, I will explain Hive cast function syntax and examples of how to cast a string to int, bigint, and decimal data types.
Hive also supports implicit conversions for primitive data types.
Hive Cast Function Syntax
Hive CAST function converts from data type to another type, below is the syntax.
CAST(from_datatype AS to_datatype);
Note: When you casting String value outside of the data type ranges, it returns NULL value. Let’s see with an example.
Hive Cast Function Example
Now let’s see how to cast data types in practical with examples.
If you are using PySpark and loaded Hive data into DataFrame you can read PySpark Cast Data type with Example
Hive CAST String to Integer Data Types
Hive support casting String into several numeric data types like TINYINT (1-byte signed integer, from -128 to 127), SMALLINT (2-byte signed integer, from -32,768 to 32,767) and INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647) and offcourse bigint.
When you casting String value within the ranges of each data type, you will get numeric as an output. when a value is out of range you will get NULL.
0: jdbc:hive2://> select cast('100' as int);
OK
+-------+
| _c0 |
+-------+
| 100 |
+-------+
1 row selected (2.671 seconds)
0: jdbc:hive2://> select cast('1.78' as int);
OK
+------+
| _c0 |
+------+
| 1 |
+------+
1 row selected (0.325 seconds)
0: jdbc:hive2://> select cast('126' as tinyint), cast('128' as tinyint);
OK
+------+-------+
| _c0 | _c1 |
+------+-------+
| 126 | NULL |
+------+-------+
1 row selected (0.269 seconds)
In the last example, 128 is out of range for TINYINT hence it returns NULL value when you cast.
Hive CAST String to Bigint Data Type
Hive Bigint support very large number which is 8-byte signed integer, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
0: jdbc:hive2://> select cast('9223372036854775805' as bigint);
OK
+----------------------+
| _c0 |
+----------------------+
| 9223372036854775805 |
+----------------------+
1 row selected (0.241 seconds)
0: jdbc:hive2://>
Hive CAST String to Float, Double & Decimal Data Type
Let’s see some examples of converting string/integer to Precision floating-point number types like float, double and decimal
0: jdbc:hive2://> select cast('1.78' as float);
OK
+-------+
| _c0 |
+-------+
| 1.78 |
+-------+
1 row selected (0.242 seconds)
0: jdbc:hive2://> select cast(1 as float);
OK
+------+
| _c0 |
+------+
| 1.0 |
+------+
1 row selected (0.222 seconds)
0: jdbc:hive2://> select cast('1.78' as double);
OK
+-------+
| _c0 |
+-------+
| 1.78 |
+-------+
1 row selected (0.309 seconds)
0: jdbc:hive2://> select cast('1.78' as decimal);
OK
+------+
| _c0 |
+------+
| 2 |
+------+
1 row selected (0.301 seconds)
0: jdbc:hive2://> select cast('1.23' as decimal);
OK
+------+
| _c0 |
+------+
| 1 |
+------+
1 row selected (0.199 seconds)
Note that casting floating String to Decimal rounds the value to closest integer value.
Hive CAST String to Boolean Data Type
Boolean values are true and false, besides these, if you try to convert any other value to boolean cast() function returns true by default.
0: jdbc:hive2://> select cast('true' as boolean);
OK
+-------+
| _c0 |
+-------+
| true |
+-------+
1 row selected (5.541 seconds)
0: jdbc:hive2://> select cast('12345' as boolean);
OK
+-------+
| _c0 |
+-------+
| true |
+-------+
1 row selected (0.316 seconds)
0: jdbc:hive2://> select cast('false' as boolean);
OK
+--------+
| _c0 |
+--------+
| false |
+--------+
1 row selected (0.314 seconds)
Changing Data type of a Column on Hive Table
In case if you wanted to change the data type of a Column on Hive metadata.
ALTER TABLE table_name CHANGE old_column_name new_column_name new_data_type
Conclusion
In this article, you have learned cast() is a type conversion function that is used to convert one data type to another type and also saw some examples of converting a string to int, bigint, float, decimal, double and binary types.
Happy Learning !!
