hive acid update and delete error

Question

I'm using hive1.2.1 and tez0.7 to do testing, but when I use acid table to do the update and delete , there are some issues happened,below is the table structure:

CREATE EXTERNAL TABLE IF NOT EXISTS working.dw_items_w
(
column defination
)
CLUSTERED BY (id) into 5000 buckets
STORED AS ORC
LOCATION '/sys/edw/working/dw_items_w2'
TBLPROPERTIES ("transactional"="true");

and update query is like below:

update working.dw_items_w
set 
PROCESS_FLAG =(case when (
(TGT_LSTG_STATUS_ID = 1 and (to_date(SALE_END) - to_date(TGT_AUCT_END_DT) ) <> 0 )
or  (TGT_LSTG_STATUS_ID in (1,2) and NEW_LSTG_STATUS_ID in (0,4) )   
) then  'D' 
when 
((TGT_LSTG_STATUS_ID =1 and NEW_LSTG_STATUS_ID = 1 and datediff(to_date(SALE_END) ,to_date(TGT_AUCT_END_DT) 
) = 0 )
or (TGT_LSTG_STATUS_ID = 2 and NEW_LSTG_STATUS_ID = 1)) then 'X' else PROCESS_FLAG end ),
NEW_LSTG_STATUS_ID = (case when TGT_LSTG_STATUS_ID = 0  AND NEW_LSTG_STATUS_ID = 0   AND to_date(SALE_END)
 <  date_sub(to_date( from_unixtime(unix_timestamp(),'yyyy-MM-dd') ), 92)
     AND to_date(SALE_END)  <> to_date('1969-12-31') then 1 else NEW_LSTG_STATUS_ID end) 

where PROCESS_FLAG = 'U';

issue like below:

at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:171) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.run(TezProcessor.java:137) at org.apache.tez.runtime.LogicalIOProcessorRuntimeTask.run(LogicalIOProcessorRuntimeTask.java:344) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable$1.run(TezTaskRunner.java:179) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable$1.run(TezTaskRunner.java:171) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1650) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable.callInternal(TezTaskRunner.java:171) at org.apache.tez.runtime.task.TezTaskRunner$TaskRunnerCallable.callInternal(TezTaskRunner.java:167) at org.apache.tez.common.CallableWithNdc.call(CallableWithNdc.java:36) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: Hive Runtime Error while processing row (tag=0) {"key":{"reducesinkkey0":{"transactionid":19,"bucketid":471,"rowid":0}},"value":ignored}} at org.apache.hadoop.hive.ql.exec.tez.ReduceRecordSource.pushRecord(ReduceRecordSource.java:302) at org.apache.hadoop.hive.ql.exec.tez.ReduceRecordProcessor.run(ReduceRecordProcessor.java:249) at org.apache.hadoop.hive.ql.exec.tez.TezProcessor.initializeAndRunProcessor(TezProcessor.java:148) ... 14 more

Daniel B Gray Daniel B Gray · Accepted Answer · 2017-02-12T18:29:22

Add the following to hive-site.xml

<property>
    <name>hive.enforce.bucketing</name>
    <value>true</value>
</property>
<property>
    <name>hive.compactor.initiator.on</name>
    <value>true</value>
</property>
<property>
    <name>hive.support.concurrency</name>
    <value>true</value>
</property>
<property>
    <name>hive.compactor.worker.threads</name>
    <value>1</value>
</property>
<property>
    <name>hive.txn.manager</name>
    <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>

Then make sure you are creating a ORC table with bucketing on predict:

create table if not exists foo.tableinfo ( schema_name varchar(32) , table_name varchar(64) , department varchar(64) , country varchar(64) , state varchar(64) , city varchar(64) , granularity int , notes varchar(256) ) clustered by (table_name) into 4 buckets STORED AS ORC TBLPROPERTIES ("orc.compress"="ZLIB", 'transactional'='true');

Then the following will work:

delete from foo.tableinfo where table_name = 'foo';

hive acid update and delete error

1 Answers