Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Offset not working in hive

Highlighted

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