Versions Compared

Old Version 74

changes.mady.by.user Karen Coppage

Saved on

compared with

New Version Current

changes.mady.by.user Karen Coppage

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Paragraph about int64 timestamps in parquet

...

Numeric Types

  • TINYINT (1-byte signed integer, from -128 to 127)
  • SMALLINT (2-byte signed integer, from -32,768 to 32,767)
  • INT/INTEGER (4-byte signed integer, from -2,147,483,648 to 2,147,483,647)
  • BIGINT (8-byte signed integer, from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
  • FLOAT (4-byte single precision floating point number)
  • DOUBLE (8-byte double precision floating point number)
  • DOUBLE PRECISION (alias for DOUBLE, only available starting with Hive 2.2.0)
  • DECIMAL
    • Introduced in Hive 0.11.0 with a precision of 38 digits
    • Hive 0.13.0 introduced user-definable precision and scale
  • NUMERIC (same as DECIMAL, starting with Hive 3.0.0)

Date/Time Types

String Types

...

Integral literals are assumed to be INT by default, unless the number exceeds the range of INT in which case it is interpreted as a BIGINT, or if one of the following postfixes is present on the number.

Type

Postfix

Example

TINYINT

Y

100Y

SMALLINT

S

100S

BIGINT

L

100L 


Info
titleVersion

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

...

Timestamps are interpreted to be timezoneless and stored as an offset from the UNIX epoch. Convenience UDFs for conversion to and from timezones are provided (to_utc_timestamp, from_utc_timestamp).
All existing datetime UDFs (month, day, year, hour, etc.) work with the TIMESTAMP data type.

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

Timestamps in Parquet files may be stored as int64 (as opposed to int96) by setting hive.parquet.write.int64.timestamp=true and hive.parquet.timestamp.time.unit to a default storage time unit. ("nanos", "micros", "millis"; default: "micros"). Note that because only 64 bits are stored, int64 timestamps stored as "nanos" will be stored as NULL if outside the range of 1677-09-21T00:12:43.15 and 2262-04-11T23:47:16.8.

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

...

Date types can only be converted to/from Date, Timestamp, or String types. Casting with user-specified formats is documented here.

Valid casts to/from Date type

Result

cast(date as date)

Same date value

cast(timestamp as date)

The year/month/day of the timestamp is determined, based on the local timezone, and returned as a date value.

cast(string as date)

If the string is in the form 'YYYY-MM-DD', then a date value corresponding to that year/month/day is returned. If the string value does not match this formate, then NULL is returned.

cast(date as timestamp)

A timestamp value is generated corresponding to midnight of the year/month/day of the date value, based on the local timezone.

cast(date as string)

The year/month/day represented by the Date is formatted as a string in the form 'YYYY-MM-DD'.

Intervals

Supported Interval Description

Example

MeaningSince

Intervals of time units:

SECOND / MINUTE / DAY / MONTH / YEAR

INTERVAL '1' DAY

an interval of 1 day(s)

Hive 1.2.0 (HIVE-9792).

Year to month intervals, format: SY-M

S: optional sign (+/-)
Y: year count
M: month count

INTERVAL '1-2' YEAR TO MONTH

shorthand for:

INTERVAL '1' YEAR +
INTERVAL '2' MONTH

Hive 1.2.0 (HIVE-9792).

Day to second intervals, format: SD H:M:S.nnnnnn

S: optional sign (+/-)

D: day countH: hours 
M: minutes
S: seconds
nnnnnn: optional nanotime		INTERVAL '1 2:3:4.000005' DAY

shorthand for:

INTERVAL '1' DAY+
INTERVAL '2' HOUR +
INTERVAL '3' MINUTE +
INTERVAL '4' SECOND +
INTERVAL '5' NANO

Hive 1.2.0 (HIVE-9792).

Support for intervals with constant numbersINTERVAL 1 DAY

aids query readability / portability 

Hive 2.2.0 (HIVE-13557).

Support for intervals with expressions:
this may involve other functions/columns.
The expression must return with a number (which is not floating-point) or with a string.

INTERVAL (1+dt) DAYenables dynamic intervalsHive 2.2.0 (HIVE-13557).

Optional usage of interval keyword

Note

the usage of the INTERVAL keyword is mandatory
for intervals with expressions (ex: INTERVAL (1+dt) SECOND)


1 DAY
'1-2' YEAR TO MONTH

INTERVAL 1 DAY
INTERVAL '1-2' YEARS TO MONTH

Hive 2.2.0 (HIVE-13557).

Add timeunit aliases to aid portability / readability:

 SECONDS / MINUTES / HOURS / DAYS / WEEKS / MONTHS / YEARS

2 SECONDS2 SECONDHive 2.2.0 (HIVE-13557).

Decimals 
Anchor
decimal
decimal
Anchor
Decimals
Decimals

...

Code Block
sql
sql
CREATE TABLE foo (
  a DECIMAL, -- Defaults to decimal(10,0)
  b DECIMAL(9, 7)
)

For usage, see Floating Point Types 82706456 in the Literals section below.

...

Allowed Implicit Conversions


void

boolean

tinyint

smallint

int

bigint

float

double

decimal

string

varchar

timestamp

date

binary

void to

true

true

true

true

true

true

true

true

true

true

true

true

true

true

boolean to

false

true

false

false

false

false

false

false

false

false

false

false

false

false

tinyint to

false

false

true

true

true

true

true

true

true

true

true

false

false

false

smallint to

false

false

false

true

true

true

true

true

true

true

true

false

false

false

int to

false

false

false

false

true

true

true

true

true

true

true

false

false

false

bigint to

false

false

false

false

false

true

true

true

true

true

true

false

false

false

float to

false

false

false

false

false

false

true

true

true

true

true

false

false

false

double to

false

false

false

false

false

false

false

true

true

true

true

false

false

false

decimal to

false

false

false

false

false

false

false

false

true

true

true

false

false

false

string to

false

false

false

false

false

false

false

true

true

true

true

false

false

false

varchar to

false

false

false

false

false

false

false

true

true

true

true

false

false

false

timestamp to

false

false

false

false

false

false

false

false

false

true

true

true

false

false

date to

false

false

false

false

false

false

false

false

false

true

true

false

true

false

binary to

false

false

false

false

false

false

false

false

false

false

false

false

false

true


Save