Page History

...

Hive 0.11 and 0.12 have the precision of the DECIMAL type fixed and limited to 38 digits.

As of Hive 0.13 users can specify scale and precision when creating tables with the DECIMAL datatype using a DECIMAL(precision, scale) syntax:.  If scale is not specified, it defaults to 0 (no fractional digits). If no precision is specified, it defaults to 10.

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

Decimal Type Incompatibilities between Hive 0.12.0 and 0.13.0

Hive users who on 0.12 or earlier who have tables with Decimal columns will be required to migrate their tables, after upgrading to Hive 0.13.0 or later. With the changes in the Decimal data type in Hive 0.13.0, the pre-Hive 0.13.0 columns (of type "decimal") will be treated as being of type decimal(10,0).  What this means is that existing data being read from these tables will be treated as 10-digit integer values, and data being written to these tables will be converted to 10-digit integer values before being written.

Upgrading Pre-Hive 0.13.0 Decimal Columns

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. For each existing partition in the table, the same set of ALTER TABLE CHANGE COLUMN commands must be issued, this time on the partition (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); ...

The Decimal datatype is discussed further in Floating Point Types below.

...