impala jdbc doesn't work for preparestatement when using upsert

[#1]

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!

[#2]

impala jdbc driver can using method 

replaceFirst("insert","upsert")

to replace the first "insert" keyword？

[#3]

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

[#4]

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 

[#5]

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

[#6]

when I create case,I got like this page, Maybe I don't have clearance?

[#7 · Accepted solution]

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.

[#8]

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

thanks a lot!

[#9]

There is another problem here。

My java code:

import java.sql.*;
import java.time.LocalDateTime;

public class test {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.cloudera.impala.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:impala://impala.db.com:8006/mid;AuthMech=0;UseNativeQuery=1;", "impala", "impala");
        String sql = "upsert into user_info(id, name, address, email, insert_time) values (?,?,?,?,?)";
        PreparedStatement statement = conn.prepareStatement(sql);
        statement.setInt(1,106);
        statement.setString(2, "张三");
        statement.setString(3, "上海");
        statement.setString(4, "John@xyz.com");
        statement.setTimestamp(5, Timestamp.valueOf(LocalDateTime.now()));
        statement.addBatch();
        statement.executeBatch();
        statement.close();
        conn.close();
    }
}

I get a bad result when I execute it，The Chinese string appears to be truncated

 

[#10]

Hi @luckes , Please check if your source code file (test.java) has UTF-8 encoding and how are you compiling the class (for example when using Maven you might need to specify to use utf-8 encoding while compiling the classes. These special characters can be easily lost if somewhere the encoding is not set properly.

Alternatively you can use the unicode notation \uXXXX to make sure the character is properly understood by java.

For example 张 is:

https://www.compart.com/en/unicode/U+5F20

so in source code it looks like 

        statement.setString(2, "\u5f20\u4e09");

Of course it is rare that one needs to hardcode special characters in the source code, usually it is read from a datafile - where you can specify what encoding to use during reading.