Error: TExecuteStatementResp(status=TStatus(statusCode=3, infoMessages=["*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 2:2 Expression not in GROUP BY key 'police_station':28:27"

[pu-iffort]

I have created connection between HIVE and Superset, i am using the dataset stored in the HIVE in the form of table.

While trying to create a table (Chart), i am getting below error.

**Unexpected error

Error: TExecuteStatementResp(status=TStatus(statusCode=3, infoMessages=["*org.apache.hive.service.cli.HiveSQLException:Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 2:2 Expression not in GROUP BY key 'police_station':28:27", 'org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:335', 'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:199', 'org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:260', 'org.apache.hive.service.cli.operation.Operation:run:Operation.java:247', 'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:541', 'org.apache.hive.service.cli.session.HiveSessionImpl:executeStatement:HiveSessionImpl.java:516', 'sun.reflect.GeneratedMethodAccessor20:invoke::-1', 'sun.reflect.DelegatingMethodAccessorImpl:invoke:DelegatingMethodAccessorImpl.java:43', 'java.lang.reflect.Method:invoke:Method.java:498', '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:422', 'org.apache.hadoop.security.UserGroupInformation:doAs:UserGroupInformation.java:1953', 'org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59', 'com.sun.proxy.$Proxy42:executeStatement::-1', 'org.apache.hive.service.cli.CLIService:executeStatement:CLIService.java:282', 'org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:563', 'org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1557', 'org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1542', '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:1149', 'java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:624', 'java.lang.Thread:run:Thread.java:750', "*org.apache.hadoop.hive.ql.parse.SemanticException:Line 2:2 Expression not in GROUP BY key 'police_station':45:18", 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genAllExprNodeDesc:SemanticAnalyzer.java:12673', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genExprNodeDesc:SemanticAnalyzer.java:12613', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4565', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genSelectPlan:SemanticAnalyzer.java:4338', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPostGroupByBodyPlan:SemanticAnalyzer.java:10566', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genBodyPlan:SemanticAnalyzer.java:10505', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:11418', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genPlan:SemanticAnalyzer.java:11288', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:genOPTree:SemanticAnalyzer.java:12074', 'org.apache.hadoop.hive.ql.parse.CalcitePlanner:genOPTree:CalcitePlanner.java:593', 'org.apache.hadoop.hive.ql.parse.SemanticAnalyzer:analyzeInternal:SemanticAnalyzer.java:12164', 'org.apache.hadoop.hive.ql.parse.CalcitePlanner:analyzeInternal:CalcitePlanner.java:330', 'org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer:analyze:BaseSemanticAnalyzer.java:285', 'org.apache.hadoop.hive.ql.Driver:compile:Driver.java:659', 'org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1826', 'org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1773', 'org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1768', 'org.apache.hadoop.hive.ql.reexec.ReExecDriver:compileAndRespond:ReExecDriver.java:126', 'org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:197'], sqlState='42000', errorCode=10025, errorMessage="Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 2:2 Expression not in GROUP BY key 'police_station'"), operationHandle=None) **

The query generated in superset is below:

SELECT
police_station AS police_station,
co_first_name AS co_first_name,
event_type AS event_type,
arrive_time AS arrive_time
FROM default.temp_table2
GROUP BY
1,
2,
3,
4
LIMIT 1000;

In my observation, Hive is unable to understand the reference generated in the SQL query by superset (i.e. 1,2,3,4).
Is there any way to make it work, so that i can create table using the same dataset and connection.

Will really appreciate your input.
Thanks in Advance.

[dosubot[bot]]

Hello @pu-iffort! I'm here to help you with your software development questions, bugs, and contributing. Let me know how I can assist you!

The error you're encountering is due to Hive not supporting positional references in the GROUP BY clause, such as 1, 2, 3, 4. Instead, Hive requires explicit column names in the GROUP BY clause.

To resolve this issue, you need to configure Superset to generate SQL queries with explicit column names in the GROUP BY clause instead of using positional references. Unfortunately, I couldn't find specific documentation or settings in Superset that directly address this configuration for Hive. However, you can try the following general approach:

1. Custom SQL: Use the SQL Lab feature in Superset to write a custom SQL query with explicit column names in the GROUP BY clause. For example:

   SELECT
     `police_station` AS `police_station`,
     `co_first_name` AS `co_first_name`,
     `event_type` AS `event_type`,
     `arrive_time` AS `arrive_time`
   FROM `default`.`temp_table2`
   GROUP BY
     `police_station`,
     `co_first_name`,
     `event_type`,
     `arrive_time`
   LIMIT 1000;

2. Modify the Database Engine Specification: If you have access to the Superset codebase, you might consider modifying the HiveEngineSpec to alter how queries are generated for Hive. This would involve changing the logic that constructs the GROUP BY clause to use column names instead of positional references.

These steps should help you work around the issue and successfully create charts using your Hive dataset in Superset.

To continue talking to Dosu, mention @dosu.