DECIMAL Data Type (CDH 6.0 / Impala 3.0 or higher only)

DECIMAL[(precision[, scale])]

Precision	In-memory Storage
1 - 9	4 bytes
10 - 18	8 bytes
19 - 38	16 bytes

• Leading zeros are not stored.
• Trailing zeros are stored.

• Each DECIMAL value takes up as many bytes as the precision of the value, plus:

  • One extra byte if the decimal point is present.
  • One extra byte for negative values.

When you use DECIMAL values in arithmetic operations, the precision and scale of the result value are determined as follows. For better readability, the following terms are used in the table below:

• P1, P2: Input precisions

• S1, S2: Input scales

• L1, L2: Leading digits in input DECIMALs, i.e., L1 = P1 - S1 and L2 = P2 - S2

When you use DECIMAL values in built-in functions, the precision and scale of the result value are determined as follows:

• The result of the SUM aggregate function on a DECIMAL value is:

  • Precision: 38

  • Scale: The same scale as the input column

• The result of AVG aggregate function on a DECIMAL value is:

  • Precision: 38

  • Scale: max(Scale of input column, 6)

Impala performs implicit conversions between DECIMAL and other numeric types as below:

• DECIMAL is implicitly converted to DOUBLE or FLOAT when necessary even with a loss of precision. It can be necessary, for example when inserting a DECIMAL value into a DOUBLE column. For example:

  CREATE TABLE flt(c FLOAT);
  INSERT INTO flt SELECT CAST(1e37 AS DECIMAL(38, 0));
  SELECT CAST(c AS DECIMAL(38, 0)) FROM flt;
  
  Result: 9999999933815812510711506376257961984

  The result has a loss of information due to implicit casting. This is why we discourage using the DOUBLE and FLOAT types in general.

• DOUBLE and FLOAT cannot be implicitly converted to DECIMAL. An error is returned.
• DECIMAL is implicitly converted to DECIMAL if all digits fit in the resulting DECIMAL.
  For example, the following query returns an error because the resulting type that guarantees that all digits fit cannot be determined .

  SELECT GREATEST (CAST(1 AS DECIMAL(38, 0)), CAST(2 AS DECIMAL(38, 37)));

• Integer values can be implicitly converted to DECIMAL when there is enough room in the DECIMAL to guarantee that all digits fit. The integer types require the following numbers of digits to the left of the decimal point when converted to DECIMAL:

  • BIGINT: 19 digits

  • INT: 10 digits

  • SMALLINT: 5 digits

  • TINYINT: 3 digits

  For example:

  CREATE TABLE decimals_10_8 (x DECIMAL(10, 8));
  INSERT INTO decimals_10_8 VALUES (CAST(1 AS TINYINT));

  The above INSERT statement fails because TINYINT requires room for 3 digits to the left of the decimal point in the DECIMAL.

  CREATE TABLE decimals_11_8(x DECIMAL(11, 8));
  INSERT INTO decimals_11_8 VALUES (CAST(1 AS TINYINT));

  The above INSERT statement succeeds because there is enough room for 3 digits to the left of the decimal point that TINYINT requires.

In UNION, the resulting precision and scales are determined as follows.

• Precision: max (L1, L2) + max (S1, S2)

  If the resulting type does not fit in the DECIMAL type, an error is returned. See the first example below.

• Scale: max (S1, S2)

Examples for UNION:

• DECIMAL(20, 0) UNION DECIMAL(20, 20) would require a DECIMAL(40, 20) to fit all the digits. Since this is larger than the max precision for DECIMAL, Impala returns an error. One way to fix the error is to cast both operands to the desired type, for example DECIMAL(38, 18).

• DECIMAL(20, 2) UNION DECIMAL(8, 6) returns DECIMAL(24, 6).

• INT UNION DECIMAL(9, 4) returns DECIMAL(14, 4).

  INT has the precision 10 and the scale 0, so it is treated as DECIMAL(10, 0) UNION DECIMAL(9. 4).

To avoid potential conversion errors, use CAST to explicitly convert between DECIMAL and other types in decimal assignments like in INSERT and UNION statements, or in functions like COALESCE:

• You can cast the following types to DECIMAL: FLOAT, TINYINT, SMALLINT, INT, BIGINT, STRING

• You can cast DECIMAL to the following types: FLOAT, TINYINT, SMALLINT, INT, BIGINT, STRING, BOOLEAN, TIMESTAMP

Impala performs CAST between DECIMAL and other numeric types as below:

• Precision: If you cast a value with bigger precision than the precision of the destination type, Impala returns an error. For example, CAST(123456 AS DECIMAL(3,0)) returns an error because all digits do not fit into DECIMAL(3, 0)

• Scale: If you cast a value with more fractional digits than the scale of the destination type, the fractional digits are rounded. For example, CAST(1.239 AS DECIMAL(3, 2)) returns 1.24.

You can cast STRING of numeric characters in columns, literals, or expressions to DECIMAL as long as number fits within the specified target DECIMAL type without overflow.

• If scale in STRING > scale in DECIMAL, the fractional digits are rounded to the DECIMAL scale.

  For example, CAST('98.678912' AS DECIMAL(15, 1)) returns 98.7.

• If # leading digits in STRING > # leading digits in DECIMAL, an error is returned.

  For example, CAST('123.45' AS DECIMAL(2, 2)) returns an error.

• Numeric literals without a decimal point

  • The literals are treated as the smallest integer that would fit the literal. For example, 111 is a TINYINT, and 1111 is a SMALLINT.
  • Large literals that do not fit into any integer type are treated as DECIMAL.
  • The literals too large to fit into a DECIMAL(38, 0) are treated as DOUBLE.

• Numeric literals with a decimal point

  • The literal with less than 38 digits are treated as DECIMAL.
  • The literals with 38 or more digits are treated as a DOUBLE.
• Exponential notation is supported in DECIMAL literals.

• To represent a very large or precise DECIMAL value as a literal, for example one that contains more digits than can be represented by a BIGINT literal, use a quoted string or a floating-point value for the number and CAST the string to the desired DECIMAL type.

  For example: CAST('999999999999999999999999999999' AS DECIMAL(38, 5)))

The DECIMAL data type can be stored in any of the file formats supported by Impala:

• Impala can query Avro, RCFile, or SequenceFile tables that contain DECIMAL columns, created by other Hadoop components.

• Impala can query and insert into Kudu tables that contain DECIMAL columns. Kudu supports the DECIMAL type in CDH 6.1 and higher.

• The DECIMAL data type is fully compatible with HBase tables.

• The DECIMAL data type is fully compatible with Parquet tables.

• Values of the DECIMAL data type are potentially larger in text tables than in tables using Parquet or other binary formats.

You can issue an ALTER TABLE ... REPLACE COLUMNS statement to change the precision and scale of an existing DECIMAL column.

• For text-based formats (text, RCFile, and SequenceFile tables)

  • If the values in the column fit within the new precision and scale, they are returned correctly by a query.

  • If any values that do not fit within the new precision and scale:

    • Impala returns an error if the query option ABORT_ON_ERROR is set to true.
    • Impala returns a NULL and warning that conversion failed if the query option ABORT_ON_ERROR is set to false.

  • Leading zeros do not count against the precision value, but trailing zeros after the decimal point do.

• For binary formats (Parquet and Avro tables)

  • Although an ALTER TABLE ... REPLACE COLUMNS statement that changes the precision or scale of a DECIMAL column succeeds, any subsequent attempt to query the changed column results in a fatal error. This is because the metadata about the columns is stored in the data files themselves, and ALTER TABLE does not actually make any updates to the data files. The other unaltered columns can still be queried successfully.

  • If the metadata in the data files disagrees with the metadata in the metastore database, Impala cancels the query.

	DECIMAL in CDH 6.0 / Impala 3.0 or higher	DECIMAL in CDH 5.15 / Impala 2.12 or lower
Overall behavior	Returns either the result or an error.	Returns either the result or NULL with a warning.
Overflow behavior	Aborts with an error.	Issues a warning and returns NULL.
Truncation / rounding behavior in arithmetic	Truncates and rounds digits from the back.	Truncates digits from the front.
String cast	Truncates from the back and rounds.	Truncates from the back.

If you need to continue using the first version of the DECIMAL type for the backward compatibility of your queries, set the DECIMAL_V2 query option to FALSE:

SET DECIMAL_V2=FALSE;