What is the difference between Statement.setMaxRows vs Statement.setFetchsize in Hive

Question

I am running queries against Hive. The same queries are supposed to work with other JDBC drivers, meaning, other relational databases.

I can't use the method Statement.setFetchSize, because it is not supported in the Hive JDBC 0.13.0.

I am trying to work this around, therefore, I came to another similar method: Statement.setMaxRows

In which cases should I use Statement.setMaxRows vs Statement.setFetchsize?

Is it possible to use them interchangeably?

Thanks.

Answer 1

No, you can't use them interchangeably. They do different things. The setMaxRows = number of rows that can be returned overall. setFetchSize = number that will be returned in each database roundtrip i.e.

setFetchSize Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement.

setMaxRows Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number.

In fact since setFetchSize is a hint the driver is free to ignore this and do what it sees fit. So don't worry about Hive JDBC not supporting this.

Note that all that setMaxRows is doing is

reducing the size of the ResultSet object. It won't affect the speed of the query. setMaxRows doesn't change the actual SQL - using top/limit/rownum e.g. - so it doesn't change the work the DB does. The query will return more results than your limit if there are more results to return, then truncate them to fit your ResultSet.

This answer does a good job of explaining how setFetchSize is important:

very important to performance and memory-management within the JVM as it controls the number of network calls from the JVM to the database and correspondingly the amount of RAM used for ResultSet processing.

---

Btw, setFetchSize can be set on java.sql.Statement as well as java.sql.ResultSet. The default value is set by the Statement object that created the result set. The fetch size may be changed at any time. And Hive JDBC has it's own HiveQueryResultSet with a setFetchSize method.