Skip to main content
luckes
luckes
Silver ⭐️⭐️⭐️
June 9, 2022
Solved

impala jdbc doesn't work for preparestatement when using upsert

I am using the latest version of impala JDBC Driver 2.6.27.1032, when my sql is  "upsert into my.testtable(id,name,insert_time) values(?,?,?)" and using preparestatement, then the column "insert_time" will be replaced to "upsert_time", so my sql be changed to "upsert into my.testtable(id,name,upsert_time) values(?,?,?)"  and i get a error "errorMessage:AnalysisException: Unknown column 'UPSERTby' in column permutation",。

How can I solve this problem？

please!

ShankerSharma
ShankerSharmaAnswer
Gold ⭐️
June 10, 2022

Hello Team,

We have tested the java code internally and it worked fine for us.

ClouderaJDBC version:- 2.6.27.1032

Java code:-
========

import java.sql.*;
import java.math.*;

public class test{

public static void main(String args[]){
try{
Class.forName("com.cloudera.impala.jdbc41.Driver");
Connection con=DriverManager.getConnection("jdbc:impala://<<hostname>>

:21050;UseNativeQuery=1");
String sql = "upsert into user_info(id, name, address, email, insert_time) values (?,?,?,?,?)";
PreparedStatement statement = con.prepareStatement(sql);
statement.setInt(1, 102);
statement.setString(2, "Peter");
statement.setString(3, "New York");
statement.setString(4, "John@xyz.com");
statement.setTimestamp(5, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));
statement.addBatch();
statement.executeBatch();
statement.close();
con.close();
}

catch(Exception e){
System.out.println(e);
}

}
}

Please let us know if it helps.

View in thread

10 replies

luckes
luckesAuthor
Silver ⭐️⭐️⭐️
June 9, 2022

impala jdbc driver can using method 

replaceFirst("insert","upsert")

to replace the first "insert" keyword？

Translate
ShankerSharma
Gold ⭐️
ShankerSharma
Gold ⭐️
June 9, 2022

Please elaborate a little more on the issue:-
Also please share the steps you are performing and share the table DDL for the same.

Translate
luckes
luckesAuthor
Silver ⭐️⭐️⭐️
June 9, 2022

the table DDL is:

create table mid.user_info(id int,name string,address string,email string,insert_time timestamp,primary key(id)) partition by hash(id) partitions 6 stored as kudu;

 

public class test {
 public static void main(String[] args) throws ClassNotFoundException, SQLException {
 Class.forName("com.cloudera.impala.jdbc.Driver");
 Connection connection = DriverManager.getConnection("jdbc:impala://impala.db.com:8006/default;auth=noSasl", "impala", "6712dxgvwe");
 String sql = "upsert into mid.user_info(id, name, address, email, insert_time) values(?,?,?,?,?)";
 PreparedStatement statement = connection.prepareStatement(sql);
 statement.setInt(1, 100);
 statement.setString(2, "John");
 statement.setString(3, "New York");
 statement.setString(4, "John@iis.com");
 statement.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
 statement.addBatch();
 statement.executeBatch();

 statement.close();
 connection.close();
 }
}

 when I execute it, I got a error as:

Exception in thread "main" java.sql.SQLException: [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: Unknown column 'UPSERT_time' in column permutation
), Query: UPSERT INTO `mid`.`user_info`(`id`, `name`, `address`, `email`, `UPSERT_time`) VALUES (100, CAST('John' AS CHAR(4)), CAST('New York' AS CHAR(8)), CAST('John@iis.com' AS CHAR(12)), '2022-06-09 17:52:10.3808341').
at com.cloudera.impala.hivecommon.api.HS2Client.executeStatementInternal(Unknown Source)
at com.cloudera.impala.hivecommon.api.HS2Client.executeStatement(Unknown Source)
at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeHelper(Unknown Source)
at com.cloudera.impala.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.execute(Unknown Source)
at com.cloudera.impala.jdbc.common.SPreparedStatement.executePreparedAnyBatch(Unknown Source)
at com.cloudera.impala.jdbc.common.SPreparedStatement.executeBatch(Unknown Source)
Caused by: com.cloudera.impala.support.exceptions.GeneralException: [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: TStatus(statusCode:ERROR_STATUS, sqlState:HY000, errorMessage:AnalysisException: Unknown column 'UPSERT_time' in column permutation
), Query: UPSERT INTO `mid`.`user_info`(`id`, `name`, `address`, `email`, `UPSERT_time`) VALUES (100, CAST('John' AS CHAR(4)), CAST('New York' AS CHAR(8)), CAST('John@iis.com' AS CHAR(12)), '2022-06-09 17:52:10.3808341').
... 6 more 

 

Translate
mszurap
Platinum ⭐️
mszurap
Platinum ⭐️
June 9, 2022

Hi @luckes , thanks for reporting this. Based on your descriptinon yes, it seems the upsert is replaced everywhere to insert by the driver. Please open a support case through MyCloudera support portal to have this routed to the proper team for enhancement.

Other ideas:

- have you checked if this behavior can be observed with the latest JDBC driver version too?

- please check if the "UseNativeQuery=1" helps in the JDBC connection string

- does it work if you avoid the "insert" from the column ("insert_time") names, so for example with a "modification_time" column name?

 

Thank you

 Miklos Szurap, Customer Operations Engineer, Cloudera

Translate
ShankerSharma
Gold ⭐️
ShankerSharmaAnswer
Gold ⭐️
June 10, 2022

Hello Team,

We have tested the java code internally and it worked fine for us.

ClouderaJDBC version:- 2.6.27.1032

Java code:-
========

import java.sql.*;
import java.math.*;

public class test{

public static void main(String args[]){
try{
Class.forName("com.cloudera.impala.jdbc41.Driver");
Connection con=DriverManager.getConnection("jdbc:impala://<<hostname>>

:21050;UseNativeQuery=1");
String sql = "upsert into user_info(id, name, address, email, insert_time) values (?,?,?,?,?)";
PreparedStatement statement = con.prepareStatement(sql);
statement.setInt(1, 102);
statement.setString(2, "Peter");
statement.setString(3, "New York");
statement.setString(4, "John@xyz.com");
statement.setTimestamp(5, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));
statement.addBatch();
statement.executeBatch();
statement.close();
con.close();
}

catch(Exception e){
System.out.println(e);
}

}
}

Please let us know if it helps.

Translate
luckes
luckesAuthor
Silver ⭐️⭐️⭐️
June 10, 2022

It works by adding the parameter “UseNativeQuery=1” to the URL！

thanks a lot!

Translate
Terms & ConditionsAccessibility statement