John1Tang created HIVE-24518: -------------------------------- Summary: Hive insert empty result failed Key: HIVE-24518 URL: https://issues.apache.org/jira/browse/HIVE-24518 Project: Hive Issue Type: Bug Components: SQL Affects Versions: 3.0.0 Environment: Hive on Spark.
Hive version: 3.0.0 Spark version: 2.4.3 Hadoop: 3.2.1 Reporter: John1Tang 1. create two table like this: {code:java} create table if not exists superset_dashboard_meta_dwd( dashboard_id int, dashboard_title string, slice_name_arr string, published int, provider string, changed_on string, is_deleted int, ingest_time bigint )stored as orc; create table if not exists superset_dashboard_meta_ods( dashboard_id int, dashboard_title string, slice_name_arr string, published int, provider string, changed_on string, is_deleted int, ingest_time bigint )stored as orc; {code} 2. execute the following sql twice(second time get empty result given that this sql compare difference between two data sets and insert the different data): {code:java} with minus_data as ( select dashboard_id, changed_on, dashboard_title, slice_name_arr, published, max(is_deleted) as is_deleted, max(provider) as provider, max(ingest_time) as ingest_time, count(*), max(source) source from ( select 1 as source, a.* from superset_dashboard_meta_ods a union all select 2 as source, b.* from superset_dashboard_meta_dwd b ) merged_data group by dashboard_id, changed_on, dashboard_title, slice_name_arr, published having count(*) = 1 ), cud_set as ( select dashboard_id, dashboard_title, slice_name_arr, published, provider, changed_on, 0 as is_deleted, ingest_time from minus_data where source = 1 union all select dashboard_id, dashboard_title, slice_name_arr, published, provider, changed_on, 1 as is_deleted, ingest_time from minus_data where source = 2 ) INSERT INTO TABLE superset_dashboard_meta_dwd select dashboard_id, dashboard_title, slice_name_arr, published, provider, changed_on, is_deleted, ingest_time from cud_set; {code} 3. will get error from hive: {code:java} 2020-12-10 11:39:07,079 INFO [Thread-147529] metastore.RetryingMetaStoreClient (RetryingMetaStoreClient.java:invoke(181)) - RetryingMetaStoreClient trying reconnect as tncdata (auth:SIMPLE)2020-12-10 11:39:07,079 INFO [Thread-147529] metastore.RetryingMetaStoreClient (RetryingMetaStoreClient.java:invoke(181)) - RetryingMetaStoreClient trying reconnect as tncdata (auth:SIMPLE)2020-12-10 11:39:07,079 INFO [Thread-147529] metastore.HiveMetaStoreClient (HiveMetaStoreClient.java:close(599)) - Closed a connection to metastore, current connections: 32020-12-10 11:39:07,079 INFO [Thread-147529] metastore.HiveMetaStoreClient (HiveMetaStoreClient.java:open(440)) - Trying to connect to metastore with URI thrift://cloud-host-11:90832020-12-10 11:39:07,079 INFO [Thread-147529] metastore.HiveMetaStoreClient (HiveMetaStoreClient.java:open(516)) - Opened a connection to metastore, current connections: 42020-12-10 11:39:07,080 INFO [Thread-147529] metastore.HiveMetaStoreClient (HiveMetaStoreClient.java:open(569)) - Connected to metastore.2020-12-10 11:39:07,095 ERROR [Thread-147529] exec.StatsTask (StatsTask.java:execute(114)) - Failed to run stats taskorg.apache.hadoop.hive.ql.metadata.HiveException: org.apache.thrift.transport.TTransportException at org.apache.hadoop.hive.ql.metadata.Hive.setPartitionColumnStatistics(Hive.java:4305) at org.apache.hadoop.hive.ql.stats.ColStatsProcessor.persistColumnStats(ColStatsProcessor.java:179) at org.apache.hadoop.hive.ql.stats.ColStatsProcessor.process(ColStatsProcessor.java:83) at org.apache.hadoop.hive.ql.exec.StatsTask.execute(StatsTask.java:108) at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:205) at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:97) at org.apache.hadoop.hive.ql.exec.TaskRunner.run(TaskRunner.java:76)Caused by: org.apache.thrift.transport.TTransportException at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:132) at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86) at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:429) at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:318) at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:219) at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_set_aggr_stats_for(ThriftHiveMetastore.java:4071) at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.set_aggr_stats_for(ThriftHiveMetastore.java:4058) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.setPartitionColumnStatistics(HiveMetaStoreClient.java:1917) at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.setPartitionColumnStatistics(SessionHiveMetaStoreClient.java:409) at sun.reflect.GeneratedMethodAccessor96.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.invoke(RetryingMetaStoreClient.java:212) at com.sun.proxy.$Proxy33.setPartitionColumnStatistics(Unknown Source) at sun.reflect.GeneratedMethodAccessor96.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient$SynchronizedHandler.invoke(HiveMetaStoreClient.java:2763) at com.sun.proxy.$Proxy33.setPartitionColumnStatistics(Unknown Source) at org.apache.hadoop.hive.ql.metadata.Hive.setPartitionColumnStatistics(Hive.java:4302) ... 6 more2020-12-10 11:39:07,096 INFO [Thread-147529] metastore.HiveMetaStoreClient (HiveMetaStoreClient.java:close(599)) - Closed a connection to metastore, current connections: 32020-12-10 11:39:08,014 INFO [HiveServer2-Background-Pool: Thread-217642] reexec.ReOptimizePlugin (ReOptimizePlugin.java:run(70)) - ReOptimization: retryPossible: falseFAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask2020-12-10 11:39:08,015 ERROR [HiveServer2-Background-Pool: Thread-217642] ql.Driver (SessionState.java:printError(1237)) - FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask2020-12-10 11:39:08,015 INFO [HiveServer2-Background-Pool: Thread-217642] ql.Driver (Driver.java:execute(2346)) - Completed executing command(queryId=tncdata_20201210113847_62f7cd7a-3d3b-47ed-b3ce-d18a29174c0d); Time taken: 20.086 seconds2020-12-10 11:39:08,015 INFO [HiveServer2-Background-Pool: Thread-217642] ql.Driver (Driver.java:checkConcurrency(276)) - Concurrency mode is disabled, not creating a lock manager2020-12-10 11:39:08,018 ERROR [HiveServer2-Background-Pool: Thread-217642] operation.Operation (SQLOperation.java:run(317)) - Error running hive query: org.apache.hive.service.cli.HiveSQLException: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:308) at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:223) at org.apache.hive.service.cli.operation.SQLOperation.access$700(SQLOperation.java:87) at org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork$1.run(SQLOperation.java:313) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:422) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1730) at org.apache.hive.service.cli.operation.SQLOperation$BackgroundWork.run(SQLOperation.java:326) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745) {code} 4. but after I execute the "truncate" action: {code:java} truncate table superset_dashboard_meta_dwd; {code} no matter how many times i execute step 2, no error shows. 5. to reveal the error, u can try "drop" and "create" action again after step 4. -- This message was sent by Atlassian Jira (v8.3.4#803005)