Support Questions
Find answers, ask questions, and share your expertise

Offset not working in hive

Offset not working in hive

New Contributor

Hello everyone,

I have an error during the request compilation when i use the OFFSET clause in the request :

 

SELECT * FROM tableName order by id limit 50000 offset 50000

 


Errors details :
org.hibernate.engine.jdbc.spi.SqlExceptionHelper - [Cloudera][ImpalaJDBCDriver](500051) ERROR processing query/statement. Error Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS, infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: ParseException line 1:125 missing EOF at 'offset' near '50000':17:16, org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:400, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:187, org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:271, org.apache.hive.service.cli.operation.Operation:run:Operation.java:337, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:439, org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:416, org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:282, org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:503, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1313, org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1298, org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39, org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39, org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor:process:HadoopThriftAuthBridge.java:747, org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286, java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1149, java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:624, java.lang.Thread:run:Thread.java:748, *org.apache.hadoop.hive.ql.parse.ParseException:line 1:125 missing EOF at 'offset' near '50000':21:5, org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:216, org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:170, org.apache.hadoop.hive.ql.Driver:compile:Driver.java:524, org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1358, org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1345, org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:185], sqlState:42000, errorCode:40000, errorMessage:Error while compiling statement: FAILED: ParseException line 1:125 missing EOF at 'offset' near '50000'), Query: SELECT * FROM tableName order by id limit 50000 offset 50000.

The OFFSET clause is normally supported by the HIVE database and i don't understand why this error occurs.

 

Nota:  Impala JDBC connector (release 2.6.15) is used to process the request in a SpringBoot application (configured with Datasource)


Any idea ?

Thanks in advance for your help