Unit 5-hive data types – primitive and complex data

HIVE DATA TYPES

1. Hive Data Types – Primitive and Complex Data Types in Hive

2. Hive Data Types are the most fundamental thing you must know before working with Hive Queries . Data Types in Hive specifies the column type in Hive tables. The article describes the two categories of Hive Data types that are  primitive data type  and  complex data type  along with their example.

3. Data Types in Hive specifies the column/field type in the Hive table. It specifies the type of values that can be inserted into the specified column.

4. Primitive Type Numeric Date/time String Miscellaneous

5. 1. Numeric Type The Numeric data type in Hive is categorized into Integral data type Floating data type 1.1 Integral data type a. TINYINT  – (1-byte signed integer ranging from -128 to 127) b. SMALLINT  – (2-byte signed integer ranging from -32, 768 to 32, 767) c. INTEGER  – (4-byte signed integer ranging from -2, 147, 483, 648 to 2, 147, 483, 647)

6. d. BIGINT  – (8-byte signed integer ranging from -9, 223, 372, 036, 854, 775, 808 to 9, 223, 372, 036, 854, 775, 807) In Hive, Integral literals are assumed to be INTEGER by default unless they cross the range of INTEGER values. If we want to use a low integral value like 100 to be treated as TINYINT, SMALLINT, or BIGINT, then we will use the following postfixes (shown in the below table) with the number.

7. Type Postfix Example TINYINT Y 100Y SMALLINT S 100S BIGINT L 100L

8. 1.2 Floating data type a. FLOAT It is a 4-byte single-precision floating-point number. b. DOUBLE It is an 8-byte double-precision floating-point number. c. DOUBLE PRECISION It is an alias for DOUBLE. It is only available starting with Hive 2.2.0 d. DECIMAL It was introduced in Hive 0.11.0. It is based on Java’s BigDecimal . DECIMAL types support both scientific and non-scientific notations. In Hive 0.11.0 and 0.12, the precision of the DECIMAL type is fixed and limited to 38 digits. As of Hive 0.13, user can specify the scale and precision during table creation using the syntax: DECIMAL(precision, scale)

9. If precision is not specified, then by default, it is equal to 10. If the scale is not specified, then by default, it is equal to 0. DECIMAL provides more precise values and greater range than DOUBLE. e. NUMERIC It started with Hive 3.0.0. The NUMERIC data type is the same as the DECIMAL type.

10. 2. Date/Time data type: a. TIMESTAMP Timestamps were introduced in Hive 0.8.0. It supports traditional UNIX timestamp with the optional nanosecond precision. The supported Timestamps format is yyyy -mm- dd hh:mm:ss [.f…] in the text files. If they are in any other format, declare them as the appropriate type and use  UDF(User Defined Function)  to convert them to timestamps.

11. b. DATE Dates were introduced in Hive 0.12.0. DATE value describes a particular year/month/day in the form of YYYY-MM-DD. For example- DATE ‘2020-02-04’ It does not have a time of day component. The range of value supported for the DATE type is 0000-01-01 to 9999-12-31. c. INTERVAL Hive Interval data types are available only after starting with Hive version 1.2 or above. Hive accepts the interval syntax with unit specifications. We have to specify the units along with the interval value. For example, INTERVAL ‘1’ DAY refers to the day time.

12. 3. String data type a. STRING In Hive, String literals are represented either with the single quotes(‘ ’) or with double-quotes(“ ”). Hive uses C-style escaping. b. VARCHAR In Hive, VARCHAR data types are of different lengths, but we have to specify the maximum number of characters allowed in the character string. If the string value assigned to the varchar is less than the maximum length, then the remaining space will be freed out. Also, if the string value assigned is more than the maximum length, then the string is silently truncated. The length of the varchar is between( 1 to 65535 ). Trailing whitespace is important in varchar and will affect the comparison results.

13. c. CHAR CHAR data types are fixed-length. The values shorter than the specified length are padded with the spaces. Unlike VARCHAR, trailing spaces are not significant in CHAR types during comparisons. The maximum length of CHAR is fixed at  255. 4. Miscellaneous data type a. BOOLEAN Boolean types in Hive store either true or false. b. BINARY BINARY type in Hive is an array of bytes. This is all about Hive Primitive Data Types. Let us now study Hive Complex Data Types.

14. Hive Complex Data Type

15. 1. arrays Array in Hive is an ordered sequence of similar type elements that are indexable using the zero-based integers. Arrays in Hive are similar to the arrays in JAVA. array< datatype > Example : array(‘ Data’,’Flair ’). The second element is accessed as array[1]. 2. maps Map in Hive is a collection of key-value pairs, where the fields are accessed using array notations of keys (e.g., [‘key’]). map< primitive_type , data_type > Example:  ‘first’ -> ‘John’, ‘last’ -> ‘ Deo ’, represented as map(‘first’, ‘John’, ‘last’, ‘ Deo ’). Now ‘John’ can be accessed with map[‘first’]. 3. structs STRUCT in Hive is similar to the STRUCT in C language. It is a record type that encapsulates a set of named fields, which can be any primitive data type. We can access the elements in STRUCT type using DOT (.) notation. STRUCT < col_name : data_type [ COMMENT col_comment ], ...> Example:  For a column c3 of type STRUCT {c1 INTEGER; c2 INTEGER}, the c1 field is accessed by the expression c3.c1. 4. union UNION type in Hive is similar to the UNION in C. UNION types at any point of time can hold exactly one data type from its specified data types. The full support for UNIONTYPE data type in Hive is still incomplete. UNIONTYPE< data_type , data_type , ...> Handling of NULL Values In Hive data types, the missing values are represented by the special value NULL. So, this was all in Hive Data Types. Hope you like our explanation.

16. Types of Hive Built-in Operators Operator Operand Types Description A=B All primitive types TRUE if expression A is equal to expression B. Otherwise FALSE. A!=B All primitive types TRUE if expression A is not equal to expression B. Otherwise FALSE. A<B All primitive types TRUE if expression A is less than expression B. Otherwise FALSE. A <= B All primitive types TRUE if expression A is less than or equal to expression B. Otherwise FALSE. A > B All primitive types TRUE if expression A is greater than expression B. Otherwise FALSE. A >= B All primitive types TRUE if expression A is greater than or equal to expression B. Otherwise FALSE. A IS NULL All types TRUE if expression A evaluates to NULL. Otherwise FALSE. A IS NOT NULL All types FALSE if expression A evaluates to NULL. Otherwise FALSE. A LIKE B String TRUE if string pattern A matches to B. Otherwise FALSE.       A REGEXP B String

17. Operator    Operand types Description A+B   All number types   Gives the result of adding A and B A-B   All number types   Gives the result of subtracting B from A A*B All number types Gives the result of multiplying A and A/B All number types Gives the result of dividing A by B A % B All number types Gives the remainder resulting from dividing A by B A & B All number types Gives the result of bitwise AND of A and B A | B All number types Gives the result of bitwise OR of A and B A ^ B All number types Gives the result of bitwise XOR of A and B ~A All number types Gives the result of bitwise NOT of A.

18. Operator Operand types Description A AND B Boolean TRUE if both A and B are TRUE. Otherwise FALSE. NULL if A or B is NULL. A OR B Boolean RUE if either A or B or both are TRUE, FALSE OR NULL is NULL. Otherwise FALSE. NOT A Boolean TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE. ! A Boolean Same as NOT A.

19. Operator Operand types Description A || B strings Concatenates the operands – shorthand for  concat (A,B) Operator Operand types Description A[n] A is an Array and n is an int Returns the nth element in the array A. The first element has index 0 M[key] M is a Map<K, V> and key has type K Returns the value corresponding to the key in the map S.x S is a struct Returns the x field of S.