Hive backend : sql IDE working - but throwing error when creating slices

[mehd-io]

Hi there,
First - great project ! 👍

I'm trying to set up superset working with hive

• hive 1.1.0 w/ cloudera 5.9.1
• superset 0.15.4
• pyhive 0.2.1

First of all, the sqlalchemy URI wasn't clear. I finally end up with hive://user:password@localhost:10000/

SQL IDE work fine, I can query any table and see results but when I try to do a simple slice in table view mode, I got :
"No data." with the following logs :

11:25:06,870:DEBUG:pyhive.hive:TOpenSessionResp(status=TStatus(errorCode=None, errorMessage=None, sqlState=None, infoMessages=None, statusCode=0), sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret='b]\xe2W:\x80M\x01\xb1\x87t\xb2\xe2R\xb69', guid='\x96)Gb\x82\x08Iy\x8f\xeb\xb3\xe9N\x8fi\xe4')), configuration={}, serverProtocolVersion=5) 2017-02-14 11:25:06,870:INFO:pyhive.hive:USE default2017-02-14 11:25:06,870:DEBUG:pyhive.hive:TExecuteStatementReq(confOverlay=None, sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret='b]\xe2W:\x80M\x01\xb1\x87t\xb2\xe2R\xb69', guid='\x96)Gb\x82\x08Iy\x8f\xeb\xb3\xe9N\x8fi\xe4')), runAsync=False, statement='USEdefault') 2017-02-14 11:25:06,956:DEBUG:pyhive.hive:TExecuteStatementResp(status=TStatus(errorCode=None, errorMessage=None, sqlState=None, infoMessages=None, statusCode=0), operationHandle=TOperationHandle(hasResultSet=False, modifiedRowCount=None, operationType=0, operationId=THandleIdentifier(secret='g\xfa\xc0\xa0\xc5,I\xe1\x82\x99\xd5k\xd5\xc7\xac\x8c', guid='\\i\xf7\x05\x06tB\x15\xa0|6\x9a\xa1\x8d\x8bR'))) 2017-02-14 11:25:06,958:DEBUG:pyhive.hive:TCloseOperationResp(status=TStatus(errorCode=None, errorMessage=None, sqlState=None, infoMessages=None, statusCode=0)) 2017-02-14 11:25:06,958:INFO:pyhive.hive:SELECT FROM test.superset2WHEREinputdate>= '2016-02-14 11:25:06' ANDinputdate<= '2017-02-14 11:25:06' LIMIT 50000 2017-02-14 11:25:06,958:DEBUG:pyhive.hive:TExecuteStatementReq(confOverlay=None, sessionHandle=TSessionHandle(sessionId=THandleIdentifier(secret='b]\xe2W:\x80M\x01\xb1\x87t\xb2\xe2R\xb69', guid='\x96)Gb\x82\x08Iy\x8f\xeb\xb3\xe9N\x8fi\xe4')), runAsync=False, statement="SELECT\nFROMtest.superset2\nWHERE inputdate>= '2016-02-14 11:25:06'\n ANDinputdate<= '2017-02-14 11:25:06' LIMIT 50000") 2017-02-14 11:25:06,964:DEBUG:pyhive.hive:TExecuteStatementResp(status=TStatus(errorCode=40000, errorMessage="Error while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near 'FROM' 'test' '.' in select clause", sqlState='42000', infoMessages=["*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: ParseException line 2:0 cannot recognize input near 'FROM' 'test' '.' in select clause:28:27", 'org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:399', 'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:145', 'org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:215', 'org.apache.hive.service.cli.operation.Operation:run:Operation.java:337', 'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:425', 'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatement:HiveSessionImpl.java:396', 'sun.reflect.GeneratedMethodAccessor18:invoke::-1', 'sun.reflect.DelegatingMethodAccessorImpl:invoke:DelegatingMethodAccessorImpl.java:43', 'java.lang.reflect.Method:invoke:Method.java:606', 'org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:78', 'org.apache.hive.service.cli.session.HiveSessionProxy:access$000:HiveSessionProxy.java:36', 'org.apache.hive.service.cli.session.HiveSessionProxy$1:run:HiveSessionProxy.java:63', 'java.security.AccessController:doPrivileged:AccessController.java:-2', 'javax.security.auth.Subject:doAs:Subject.java:415', 'org.apache.hadoop.security.UserGroupInformation:doAs:UserGroupInformation.java:1714', 'org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59', 'com.sun.proxy.$Proxy25:executeStatement::-1', 'org.apache.hive.service.cli.CLIService:executeStatement:CLIService.java:245', 'org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:500', '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.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56', 'org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286', 'java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1145', 'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:615', 'java.lang.Thread:run:Thread.java:745', "*org.apache.hadoop.hive.ql.parse.ParseException:line 2:0 cannot recognize input near 'FROM' 'test' '.' in select clause:32:5", 'org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:204', 'org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:166', 'org.apache.hadoop.hive.ql.Driver:compile:Driver.java:460', 'org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1274', 'org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1261', 'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:143'], statusCode=3), operationHandle=None) 2017-02-14 11:25:06,988:INFO:root:Caching for the next None second

So it's seems that the query used through slices doesn't fit with hive requirements ?

[kaiosama]

Same problem here, cdh 5.7.0, hive 1.1.0-cdh5.7.0

My table is constructed as following:

CREATE EXTERNAL TABLE MYTABLE (`time_date` string, `week_day` string, `country` string, `geohash` string, `lat` double, `lon` double, `cnt` bigint) row format delimited fields terminated by ',' location '/user/cloudera/hive/mytable';

SQL labs works fine.

[kaiosama]

update: can create slices in "pivot table" or "map box" styles, haven't tried them all but still can't get the "table" style to work.

[mehd-io]

@kaiosama Did you try to put some column in "not grouped by" field ? On my side, that worked fine, and for the others slices, it simply appears that my date time wasn't parsed properly! However, still better to use presto over it to faster the queries.
Still I don't understand the error throwed above - but seems to be ignored