Created on 05-31-2019 12:50 AM - edited 09-16-2022 07:25 AM
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
Created 05-31-2019 08:01 AM
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!
Created on 06-03-2019 01:20 AM - edited 06-03-2019 01:26 AM
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.
Created on 06-03-2019 05:02 AM - edited 06-03-2019 05:03 AM
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.
Created 06-03-2019 05:48 AM
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)