question Re: impala jdbc doesn't work for preparestatement when using upsert in Support Questions

impala jdbc doesn't work for preparestatement when using upsert

luckes — Thu, 09 Jun 2022 02:55:58 GMT

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!

Re: impala jdbc doesn't work for preparestatement when using upsert

luckes — Thu, 09 Jun 2022 03:31:15 GMT

impala jdbc driver can using method

replaceFirst("insert","upsert")

to replace the first "insert" keyword？

Re: impala jdbc doesn't work for preparestatement when using upsert

ShankerSharma — Thu, 09 Jun 2022 07:29:18 GMT

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

Re: impala jdbc doesn't work for preparestatement when using upsert

luckes — Thu, 09 Jun 2022 09:55:59 GMT

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

Re: impala jdbc doesn't work for preparestatement when using upsert

mszurap — Thu, 09 Jun 2022 10:25:23 GMT

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

Re: impala jdbc doesn't work for preparestatement when using upsert

luckes — Fri, 10 Jun 2022 01:19:46 GMT

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

Re: impala jdbc doesn't work for preparestatement when using upsert

ShankerSharma — Fri, 10 Jun 2022 04:56:35 GMT

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.

Re: impala jdbc doesn't work for preparestatement when using upsert

luckes — Fri, 10 Jun 2022 06:02:03 GMT

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

thanks a lot!

Re: impala jdbc doesn't work for preparestatement when using upsert

luckes — Fri, 10 Jun 2022 07:06:55 GMT

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

Re: impala jdbc doesn't work for preparestatement when using upsert

mszurap — Fri, 10 Jun 2022 07:23:22 GMT

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.

Re: impala jdbc doesn't work for preparestatement when using upsert

luckes — Fri, 10 Jun 2022 07:36:41 GMT

I'm sure my source code file has UTF-8 encoding and I compiling the class by maven.

When my SQL is set to ：

upsert into user_info(id, name, address, email, insert_time) values (?,cast(? as string),cast(? as string),?,?)

it's works, but the url parameter “UseNativeQuery=1” does't works!