Shulou(Shulou.com)06/03 Report--

This article focuses on "what are the data types of HIVE", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn what data types HIVE has.

1. Numeric type suffix example TINYINTY100YSMALLINTS100SBIGINTL100L

INTEGER is introduced as a synonym for INT in Hive 2.2.0 (HIVE-14950).

2. Decimals

The decimal type of Hive is based on Java BigDecimal, and BigDecimal is used in java to represent decimal types of arbitrary precision. All regular numeric operations (such as +, -, *, /) and related UDFs (such as Floor,Ceil,Round, etc.) use and support Decimal. You can convert Decimal and other numerical types to and from each other, and Decimal supports both scientific and unscientific counting. Therefore, whether your dataset contains data such as 4.004E + 3 (scientific notation) or 4004 (non-scientific notation) or a combination of both, you can use Decimal.

Starting with Hive 0.13, users can use the DECIMAL (precision, scale) syntax to define precision and scale of the Decimal data type when creating a table. If precision is not specified, the default is 10. If scale is not specified, it defaults to 0 (no decimal places).

CREATE TABLE foo (

A DECIMAL,-Defaults to decimal (1010)

B DECIMAL (9,7)

)

Values greater than BIGINT need to be processed with the BD suffix and Decimal (38pl 0), for example:

Select CAST (18446744073709001000BD AS DECIMAL (38P0)) from my_table limit 1

Decimal is not compatible between Hive 0.12.0 and 0.13.0, so versions prior to 0.12 need to be migrated before they can be used. For more information, please see the official website.

Type support version TIMESTAMPNote: Only available starting with Hive 0.8.0DATENote: Only available starting with Hive 0.12.0INTERVALNote: Only available starting with Hive 1.2.0

1. Timestamps

Support for traditional UNIX timestamps and optional nanosecond precision.

-supported transformations:

-Integer number type: interpreted as UNIX timestamp in seconds

-floating point value type: interpreted as UNIX timestamp in seconds with decimal precision

-string: accords with JDBC java.sql.Timestamp format "YYYY-MM-DD HH:MM:SS.fffffffff" (9 decimal places precision)

The timestamp is interpreted as timeless and stored as an offset from the Unix era. Provides a convenient UDFs (to_utc_timestamp,from_utc_timestamp) for converting to and from time zones.

All existing date-time UDFs (month, day, year, hour, etc.) use the TIMESTAMP data type.

Timestamps in Text files must use the format yyyy-mm-dd hh:mm:ss [.f...] . If they are in a different format, declare them as the appropriate type (INT,FLOAT,STRING, etc.) and use UDF to convert them to timestamps.

At the table level, alternative timestamp formats can be supported by providing the format to the SerDe attribute "timestamp.formats" (from version 1.2.0 with HIVE-9298). For example, yyyy-MM-dd'T'HH:mm:ss.SSS,yyyy-MM-dd'T'HH:mm:ss.

2. Dates

The DATE value describes a specific year / month / day in the format YYYY-MM-DD. For example, DATE'2013-01-01'. The date type has no time component. The supported values of the Date type range from 0000-01-01 to 9999-12-31, depending on the original support of the Java Date type.

Date types can only be converted between Date, Timestamp, and or String types.

3. Character type

1.Strings

String literals can be represented in single quotation marks (') or double quotation marks ("). Hive uses C-style escape in strings.

2. Varchar

The Varchar type is created with a length specifier between 1 and 65355, which defines the maximum number of characters allowed in a string. If the string value to be converted / assigned to the varchar value exceeds the length specifier, the string is silently truncated. The character length is determined by the number of code points contained in the string.

Like strings, trailing spaces are important in varchar and affect the result of the comparison.

Non-generic UDFs cannot directly use the varchar type as an input parameter or return value. The string UDFs can be created, and the varchar value will be converted to strings and passed to UDF. To use the varchar parameter directly or return a varchar value, create a GenericUDF.

If the data type information is obtained based on the reflection-based method, there may be scenarios that do not support varchar. This includes some SerDe function implementations.

3. Char

Character types are similar to Varchar, but they are of fixed length, meaning that values shorter than the specified length value are filled with spaces, but trailing spaces are not important during the comparison. The maximum length is fixed at 255.

CREATE TABLE foo (bar CHAR (10))

5. Complex type

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.