Support Questions

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

impala jdbc doesn't work for preparestatement when using upsert

avatar
Explorer

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!

1 ACCEPTED SOLUTION

avatar
Expert Contributor

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 solution in original post

10 REPLIES 10

avatar
Explorer

impala jdbc driver can using method 

replaceFirst("insert","upsert")

to replace the first "insert" keyword?

avatar
Expert Contributor

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

avatar
Explorer

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 

 

avatar

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

avatar
Explorer

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

微信截图_20220610091652.png

avatar
Expert Contributor

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.

avatar
Explorer

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

thanks a lot!

avatar
Explorer

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

 result.jpg

avatar

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.