This Confluence has been LDAP enabled, if you are an ASF Committer, please use your LDAP Credentials to login. Any problems file an INFRA jira ticket please.

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

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'.

...

  1. Determine what precision/scale you would like to set for the decimal column in the table.
  2. For each decimal column in the table, update the column definition to the desired precision/scale using the ALTER TABLE command:

    Code Block
    sql
    sql
    ALTER TABLE foo CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

    If the table is not a partitioned table, then you are done.  If the table has partitions, then go on to step 3.

  3. If the table is a partitioned table, then find the list of partitions for the table:

    Code Block
    sql
    sql
    SHOW PARTITIONS foo;
     
    ds=2008-04-08/hr=11
    ds=2008-04-08/hr=12
    ...


  4. Each existing partition in the table must also have its DECIMAL column changed to add the desired precision/scale.

    This can be done with a single ALTER TABLE CHANGE COLUMN by using dynamic partitioning (available for ALTER TABLE CHANGE COLUMN in Hive 0.14 or later, with HIVE-8411):

    Code Block
    sql
    sql
    SET hive.exec.dynamic.partition = true;
     
    -- hive.exec.dynamic.partition needs to be set to true to enable dynamic partitioning with ALTER PARTITION
    -- This will alter all existing partitions of the table - be sure you know what you are doing!
    ALTER TABLE foo PARTITION (ds, hr) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);

     


    Alternatively, this can be done one partition at a time using ALTER TABLE CHANGE COLUMN, by specifying one partition per statement (This is available in Hive 0.14 or later, with HIVE-7971.):

    Code Block
    sql
    sql
    ALTER TABLE foo PARTITION (ds='2008-04-08', hr=11) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);
    ALTER TABLE foo PARTITION (ds='2008-04-08', hr=12) CHANGE COLUMN dec_column_name dec_column_name DECIMAL(38,18);
    ...


...

Warning
titleUNIONTYPE support is incomplete

The UNIONTYPE datatype was introduced in Hive 0.7.0 (HIVE-537), but full support for this type in Hive remains incomplete. Queries that reference UNIONTYPE fields in JOIN (HIVE-2508), WHERE, and GROUP BY clauses will fail, and Hive does not define syntax to extract the tag or value fields of a UNIONTYPE. This means that UNIONTYPEs are effectively lookpass-atthrough-only.

Union types can at any one point hold exactly one of their specified data types. You can create an instance of this type using the create_union UDF:

...

Missing values are represented by the special value NULL. To import data with NULL fields, check documentation of the SerDe used by the table. (The default Text Format uses LazySimpleSerDe which interprets the string \N as NULL when importing.)

Change Types

When hive.metastore.disallow.incompatible.col.type.changes is set to false, the types of columns in Metastore can be changed from any type to any other type.   After such a type change, if the data can be shown correctly with the new type, the data will be displayed.   Otherwise, the data will be displayed as NULL.

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