Hive – Basic Data Type (9)

The data type

When creating a table, we need to specify the data type when creating a new column. Generally, in a data file, we can specify all the data as string, which is a string type, and then use the function to convert the data type to other data types, such as date, value, etc.

CREATE  TABLE [IF NOT EXISTS] [db_name.]table_name    
  ``[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type
Copy the code

Hive data types can be divided into five types, but the five types can be divided into two types. The first type is primitive data type, or basic data type. The other type is complex data structure, which we will learn in the next section

Numeric types

It is important to note that all of these data types are implementations of interfaces in Java, so the behavior details of these types are exactly the same as their Java counterparts. For example, STRING implements STRING in Java, FLOAT implements FLOAT in Java, and so on

The integer

INTEGER types include TINYINT, SMALLINT, INT/INTEGER, BIGINT. The default type is INT. In other words, when you store INTEGER numbers in a Hive table, the data type is INT, not TINYINT, SMALLINT, INT/INTEGER, or BIGINT. Examples are TINYINT and BIGINT

Only if the number you store exceeds the limit of the INT type can it take your BIGINT, or if you specify the data type when you store the data

floating-point

A double can hold a double value, and float is a single value.

We don’t use much of either type in Hive because it loses precision

A DECIMAL and NUMERIC

In an enterprise where we specify the number of digits and the precision of a value, we need to use the DECIMAL data type, which can specify any precision and number of digits for easy calculation. The usage is DECIMAL(precision, scale), where precision is precision and specifies how many significant digits there are, and scale is the number of DECIMAL digits after the DECIMAL point, such as DECIMAL(10,2) where there are 10 significant digits and 2 DECIMAL places.

Hive-0.11.0 and hive-0.12.0 fix the precision of DECIMAL types and limit them to 38 digits, starting with Hive-0.13.0 you can specify the size and precision of DECIMAL, The DECIMAL(Precision,scale) syntax can be used when creating tables using the DECIMAL type. DECIMAL(9,8) represents up to 9 digits, with the last 8 digits being decimals. This means that at most one digit is in front of the decimal point, and more than one digit is null. If no argument is specified, the default is DECIMAL(10,0), that is, there are no DECIMAL places, in which case 0.82 becomes 1.

The DECIMAL type provides precise values and a wider range for floating-point numbers than the DOUBLE type, which stores an exact representation of a number, and the DOUBLE type stores very close approximations. You can use DECIMAL when approximations of DOUBLE are insufficiently precise, as in financial applications, equal and unequal checks, and rounding operations, when values are outside the range of DOUBLE (< -10308 or > 10308) or very close to 0 (-10308> <… < 10^308), the type DECIMAL can also be used.

Hive’s DECIMAL type relies on Java’s BIGDECIMAL, which in Java means an immutable arbitrary precision DECIMAL number. Many operations on other numeric types are also applicable to DECIMAL. Persistence of DECIMAL supports both scientific and non-scientific counting, meaning that if you store data files in formats such as 4.004E3 and 4004, it is legal for BIGDECIMAL.

Time to type

HIVE provides the DATE type description year month day in the format yyyY-MM-DD. The value ranges from 0000.01.01 to 9999.12.31.

HIVE provides TIMESTAMP type description detailed time type with time zone information

The date type in Hive is not commonly used. Data of the date type is usually saved as string, which saves the trouble of converting the date format. Generally, the string type is string. VARCHAR and CHAR are not recommended.

We also mentioned above that it is not recommended to use the time type but the string type. However, if an existing table has a time type, you can use the following conversion method to convert it

Character types

STRING types in Hive can be expressed in single or double quotation marks, “” or” “

The VARCHAR type can be thought of as a length limited version of STRING. It can only store characters between 1 and 65535. If you convert a STRING to a VARCHAR type, it will be truncated if the length exceeds the limit. Affects the result of comparing strings

A non-generic UDF cannot directly use a VARCHAR type as an input parameter or return value. You can create a string UDF, and the VARCHAR value is converted to a string and passed to the UDF. To use the VARCHAR argument directly or to return the vARCHAR value, create a GenericUDF. If other contexts rely on reflection based methods to retrieve type information, there may be other contexts that do not support VARCHAR. This includes some SerDe implementations.

The CHAR type is similar to the VARCHAR type. Different real varchars are variable in length, ranging from 1 to 65535. However, the length of the CHAR type is immutable, which means you need to specify the length when creating the data. However, this length does not affect the comparison, and the maximum length supported is 255

Many people think that the timestamp is an integer number, but it is not. The timestamp in the database is actually readable, which is yyyY-MM-DD HH: MM :ss. FFFFFFF

Intervals

In SQL calculation, unix_timestamp and from_unixtime are used together: first, the Unix time from 1970 to the present integer seconds, then add and subtract to achieve the purpose of the time before and after, now let’s recognize a more convenient function on time add and subtract: INTERVAL

-- Current time Indicates the time 10 seconds ago
select current_timestamp(a)- INTERVAL 10 second;
-- one year from now
select current_timestamp(a)+ INTERVAL 1 year;
Copy the code

Year_month,month_day, day, hour, hour, minute and second can be added or subtracted according to the format of month,month, day, hour, hour, minute and second. The format is arbitrary delimited characters (usually Spaces), corresponding to year_month,month_day, etc. It is important to note that they must be adjacent and month_day comes first. For example, year_month cannot be month_year. It can’t be a year, a year

-- like a year and a month ago
select current_timestamp(a)- INTERVAL '1 1' year_month
Copy the code

Here you can replace current_timestamp() with an arbitrary time function, for example

select current_date()  + INTERVAL 1 year;
Copy the code

Other types of

BOOLEAN The BOOLEAN type indicates true or false

BINARY arrays I’ve never used before

For Boolean types, we usually use 0 and 1 instead, which makes it easier to use later.

Type conversion

The atomic data types of Hive can be converted implicitly, similar to Java type conversion. For example, if an expression is of the INT type, TINYINT is automatically converted to INT. However, Hive does not convert the atomic data types of Hive. INT is not automatically converted to TINYINT and will return an error unless CAST is used.

The implicit type conversion rules are as follows

Any integer type can be implicitly converted to a wider range of types, such as TINYINT to INT and INT to BIGINT.

All integer types, floats, and strings can be implicitly converted to DOUBLE.

TINYINT, SMALLINT, INT can all be converted to FLOAT.

BOOLEAN types cannot be converted to any other type.

Data type conversions can be performed using the CAST operation display

For example, CAST(‘1’ AS INT) converts the string ‘1’ to the integer 1; If a CAST fails, such AS CAST(‘X’ AS INT), the expression returns NULL.

Below we provide a type conversion table, in which true represents the row can be converted to the corresponding column type