0

The following ItemReader gets a list of thousands accounts (acc).

The database that the ItemReader will connected to in order to retrieve the data is HIVE. I don’t have permission to create any table, only read option.

 @Bean
 @StepScope
public ItemReader<OmsDto> omsItemReader(@Value("#{stepExecutionContext[acc]}") List<String> accountList) {

    String inParams = String.join(",", accountList.stream().map(id -> 
"'"+id+"'").collect(Collectors.toList()));

    String query = String.format("SELECT ..... account IN (%s)", inParams); 
    
    BeanPropertyRowMapper<OmsDto> rowMapper = new BeanPropertyRowMapper<>(OmsDto.class);
    rowMapper.setPrimitivesDefaultedForNullValue(true);
    JdbcCursorItemReader<OmsDto> reader = new JdbcCursorItemReader<OmsDto>();

    reader.setVerifyCursorPosition(false);
    reader.setDataSource(hiveDataSource());
    reader.setRowMapper(rowMapper);
    reader.setSql(query);
    reader.open(new ExecutionContext());
    return reader;
}

This is the error message that I get when using ItemReader:

Caused by: org.springframework.batch.item.ItemStreamException: Failed to initialize the reader
at org.springframework.batch.item.support.AbstractItemCountingItemStreamItemReader.open(AbstractItemCountingItemStreamItemReader.java:153) ~[spring-batch-infrastructure-4.2.4.RELEASE.jar:4.2.4.RELEASE]

 Caused by: java.sql.SQLException: Error executing query
at com.facebook.presto.jdbc.PrestoStatement.internalExecute(PrestoStatement.java:279) ~[presto-jdbc-0.243.2.jar:0.243.2-128118e]
at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:228) ~[presto-jdbc-0.243.2.jar:0.243.2-128118e]
at com.facebook.presto.jdbc.PrestoPreparedStatement.<init>(PrestoPreparedStatement.java:84) ~[presto-jdbc-0.243.2.jar:0.243.2-128118e]
at com.facebook.presto.jdbc.PrestoConnection.prepareStatement(PrestoConnection.java:130) ~[presto-jdbc-0.243.2.jar:0.243.2-128118e]
at com.facebook.presto.jdbc.PrestoConnection.prepareStatement(PrestoConnection.java:300) ~[presto-jdbc-0.243.2.jar:0.243.2-128118e]
at org.springframework.batch.item.database.JdbcCursorItemReader.openCursor(JdbcCursorItemReader.java:121) ~[spring-batch-infrastructure-4.2.4.RELEASE.jar:4.2.4.RELEASE]
... 63 common frames omitted
Caused by: java.lang.RuntimeException: Error fetching next at https://prestoanalytics-ch2-p.sys.comcast.net:6443/v1/statement/executing/20201118_131314_11079_v3w47/yf55745951e0beccc234c98f36005723457073854/0 returned an invalid response: JsonResponse{statusCode=502, statusMessage=Bad Gateway, headers={cache-control=[no-cache], content-length=[107], content-type=[text/html]}, hasValue=false} [Error: <html><body><h1>502 Bad Gateway</h1>
The server returned an invalid or incomplete response.
</body></html>
]

I was sure that the root cause is because of the driver but I have tested the driver with the same SQL this time using DriverManager and its run perfectly.

 @Component
 public class OmsItemReader implements ItemReader<OmsDto>, StepExecutionListener {

private ItemReader<OmsDto> delegate;
public SikOmsItemReader() {
    
    Properties properties = new Properties();
    properties.setProperty("user", "....");
    properties.setProperty("password", "...");
    properties.setProperty("SSL", "true");
    
    Connection connection = null;
    
    try {
        connection = DriverManager.getConnection("jdbc:presto://.....", properties);
        Statement statement = connection.createStatement();
        
        ResultSet resultSet = statement.executeQuery(

I am not sure what is the different ? Is it the driver or sparing batch ?

I am looking for a workaround. How can I retrieve thousands of accounts via IN clauses with spring batch ?

Thank you

angus
  • 3,210
  • 10
  • 41
  • 71
  • What is the problem you are trying to solve? – Mahmoud Ben Hassine Nov 18 '20 at 09:30
  • Hello Mahmoud, Apologized for the missing information. My main problem is that ItemReader is not working (I added part of the error message). I was sure that its related to the big number of values within the IN but I have tested this with a custom ItemReader using ResultSet and its working. – angus Nov 18 '20 at 14:03
  • You don't need to call `reader.open(new ExecutionContext());`, Spring Batch will do it. Your `omsItemReader` method should return `JdbcCursorItemReader` and not `ItemReader` so that Spring Batch correctly creates a proxy for the reader. For your error `Caused by: java.lang.RuntimeException: Error fetching next at ... The server returned an invalid or incomplete response`, I can't help without a [minimal complete example](https://stackoverflow.com/help/minimal-reproducible-example) that reproduces the issue. – Mahmoud Ben Hassine Nov 19 '20 at 11:48

0 Answers0