org.apache.hadoop.hive.ql.exec.StatsTask. MetaException(message:Cannot change stats state for a transactional table

[#1]

Hi All,

We are getting the below error intermittently for some tables and we have hive 3.1 . I am passing a sample query like insert into table test as select * from some table in Nifi PutHiveSQL processor . 

Hive target table is managed table and stored as avro format

Error :

PutHive3QL[id=d1652e76-54f7-30cf-8dc8-b1934cee3c26] Failed to update Hive for StandardFlowFileRecord[uuid=7447e7f0-4d1e-40fa-9965-5c98f7d11341,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1618924236521-4, container=default, section=4], offset=154416, length=3631],offset=0,name=20210421_test_branch_info,size=3631] due to java.sql.SQLException: Error while compiling statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask. MetaException(message:Cannot change stats state for a transactional table ra_test.test_branch_info without providing the transactional write state for verification (new write ID -1, valid write IDs null; current state {"BASIC_STATS":"true","COLUMN_STATS":{}; new state {"BASIC_STATS":"true","COLUMN_STATS":{}); it is possible that retrying the operation will succeed, so routing to retry: java.sql.SQLException: Error while compiling statement: FAILED: Execution Error,

Option tried : if we rerun the query for the same table through Nifi PutHiveSQL , then Nifi processor executes the query fine . sometimes it fails in the first run.

Hive version : 3.0 and Nifi 1.11.4

Please assist what option to be checked in Hive side on this error.

Thanks

[#2]

Hi ,

Can you add this below property in Hive service advance configurations and restart the stale configs and check the issue?

##  hive.txn.stats.enabled=false

[#3 · Accepted solution]

Hi ,

Thanks for the information . We have added this single property set hive.txn.stats.enabled=false and still we were getting the issue intermittenly from Nifi.

My Solution architect found the cause for the issue after investigating Hive github code I believe. 

So we added to these two property below 

set hive.txn.stats.enabled=false

set hive.stats.autogather=false 

The  error disappeared and never come again .I would like to understand how to relate the hive github with those properties or how do have to troubleshoot this kind of strange issue .

[#4]

Hello,

When you are facing this issue? For what type of query?

How you are created these tables? Is the client (if non beeline or hive) creating the Table have a ACID read/write capability?

Can you provide your DDL of this query?

[#5]

Hi ,

Thanks for the reply . As i mentioned that i have run the insert ino tableA select * from tableB (Assume Delta calculation query). Yes , we have managed table in hive and hive version is 3.0 where ACID are enabled by default .We have created the Hive managed table through HUE and executed the delta query via NiFi PutHQL processor and we got the error intermittently .