Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

impala-jdbc: Unexpected cast error from BigDecimal to DECIMAL

avatar
New Contributor

Hi,

I'm writing to a Kudu table using impala-jdbc 2.6.4.1005.

 

I got this error when inserting a BigDecimal with value 7896163500 to DECIMAL(20,2).

[Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: Possible loss of precision for target table 'ST.RFQ_SPOT'.
Expression '7896163500' (type: BIGINT) would need to be cast to DECIMAL(20,2) for column 'req_amount'

However, the value I'm inserting would fit also in a DECIMAL(12,2).

I cannot understand why I get this error from the driver.

 

In addition, if I try to manually make the cast, I get the result as expected:

select cast (cast(7896163500 as BIGINT) as DECIMAL(12,2))

1	7896163500.00

 

4 REPLIES 4

avatar
Expert Contributor

Hello @cittae 

 

This is an expected event.

 

In Impala, by default - If you specify a value such as 7896163500 to go into a DECIMAL column, Impala checks if the column has enough precision to represent the largest value of that integer type, and raises an error if not. 

 

Here 7896163500 is a BIGINT and BIGINT's maximum storage is 8 Bytes. Since we have 2 fractional digits here, you see the below error,

 

Expression '7896163500' (type: BIGINT) would need to be cast to DECIMAL(20,2) for column 'req_amount'

 

Therefore, use an expression like (cast(7896163500 as BIGINT) as DECIMAL(12,2)) for DECIMAL columns as you have already done to insert the details. Hope this helps!

 

For more: https://www.cloudera.com/documentation/enterprise/5-6-x/topics/impala_decimal.html

 

Thanks!

avatar
New Contributor

Thank you @Gomathinayagam,

inserting data with explicit casting worked:

 

INSERT INTO ST.X VALUES ('test',CAST(7896163500 AS DECIMAL(16,2)))

SELECT * FROM ST.X
id req_amount
1 test 7896163500.00

 

I still have a problem when making a batch insert, using Cloudera JDBC Driver 2.6.4.

java.sql.SQLException: [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: Possible loss of precision for target table 'ST.X'.
Expression 'cast(350000 as decimal(16,2))' (type: DECIMAL(21,2)) would need to be cast to DECIMAL(16,2) for column 'req_amount'
), Query: INSERT INTO ST.X VALUES ('123456789-xyz', CAST(350000 AS DECIMAL(16,2))) ,('321564987-zyx',7896163500).
        at com.cloudera.impala.hivecommon.api.HS2Client.executeStatementInternal(Unknown Source) ~[impala-jdbc4-2.6.4.1005.jar:?]
        at com.cloudera.impala.hivecommon.api.HS2Client.executeStatement(Unknown Source) ~[impala-jdbc4-2.6.4.1005.jar:?]
        at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeHelper(Unknown Source) ~[impala-jdbc4-2.6.4.1005.jar:?]
        at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeHelperParameter(Unknown Source) ~[impala-jdbc4-2.6.4.1005.jar:?]
        at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.execute(Unknown Source) ~[impala-jdbc4-2.6.4.1005.jar:?]
        at com.cloudera.impala.jdbc.common.SPreparedStatement.executePreparedAnyBatch(Unknown Source) ~[impala-jdbc4-2.6.4.1005.jar:?]
        at com.cloudera.impala.jdbc.common.SPreparedStatement.executeBatch(Unknown Source) ~[impala-jdbc4-2.6.4.1005.jar:?]
		....

It seems that the second record does not respect the defined PreparedStatement. From the exception:

INSERT INTO ST.X VALUES 
('123456789-xyz', CAST(350000 AS DECIMAL(16,2))) ,
('321564987-zyx',7896163500)

PreparedStatement: 

INSERT INTO ST.X VALUES (?, CAST(? AS DECIMAL(16,2)))

 

 

Inserting one record at a time works nice.

 

avatar
Expert Contributor

Hi @cittae 

 

No problem. It seems like the error you are facing is the same as it was before.

 

Can you try inserting the values by modifiying the query like below?

 

 

INSERT INTO ST.X VALUES 
('123456789-xyz', CAST(350000 AS DECIMAL(16,2))),
('321564987-zyx', CAST(7896163500 AS DECIMAL(16,2)))

 

The INSERT query you used has a casting missing for the second record.

 

 

avatar
New Contributor

I agree with you. The stacktrace shows that the sql misses the cast for the second record.

 

Unfortunately I do not have any control on the effective query, since it is done via JDBC.

This is a code replicating the issue:

 

String sql = "INSERT INTO ST.X VALUES (?, CAST(? AS DECIMAL(16,2)))";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
	ps.setString(1, id1);
	ps.setBigDecimal(2, amount1);
	ps.addBatch();

	ps.setString(1, id2);
	ps.setBigDecimal(2, amount2);
	ps.addBatch();

	ps.executeBatch();
}

 

 

When I execute the same code by adding the batch just once, it works correctly.

It seems like the driver "cleans up" the query, removing the cast:

 

INSERT INTO ST.X VALUES 
('123456789-xyz', CAST(350000 AS DECIMAL(16,2))) ,
('321564987-zyx',7896163500)