...
- 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 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); ...
...