...
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'. |
...
- Determine what precision/scale you would like to set for the decimal column in the table.
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.
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 ...
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); ...
...
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 |
...