Hive DML 语句操作报错

一时失言乱红尘 2022-05-15 13:27 477阅读 0赞

按照MySQL 方式创建的表，

hive> desc tt;
    OK
    id                  	int                	                    
    name                	string            	                    
    age                 	int              	                    
    Time taken: 0.123 seconds, Fetched: 3 row(s)
    
    hive> select * from tt;
    OK
    1	tom	12
    2	hanmei	13
    3	lilei	14

进行DML语句时报错，搜索了些资料，特记录如下.报错日志如下：

hive> delete from tt where id=1;
    FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction 
    manager that does not support these operations

在hive.xml 文件中配置如下属性：

<property>
        <name>hive.txn.manager</name>
        <value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
        <description>
          Set to org.apache.hadoop.hive.ql.lockmgr.DbTxnManager as part of turning on Hive
          transactions, which also requires appropriate settings for hive.compactor.initiator.on,
          hive.compactor.worker.threads, hive.support.concurrency (true), hive.enforce.bucketing
          (true), and hive.exec.dynamic.partition.mode (nonstrict).
          The default DummyTxnManager replicates pre-Hive-0.13 behavior and provides
          no transactions.
        </description>
      </property>
     
      <property>
            <name>hive.compactor.initiator.on</name>
    		<value>true</value>
      </property>
        <property>
            <name>hive.compactor.worker.threads</name>
    		<value>1</value>
      </property>
       <property>
            <name>hive.support.concurrency</name>
    		<value>true</value>
      </property>
         <property>
            <name>hive.enforce.bucketing</name>
    		<value>true</value>
      </property>
    
         <property>
            <name>hive.exec.dynamic.partition.mode</name>
    		<value>nonstrict</value>
      </property>

添加hive元数据，使用mysql存储,初始化时表数据是空的.

MariaDB [hive]> INSERT INTO NEXT_LOCK_ID VALUES(1);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hive]> INSERT INTO NEXT_COMPACTION_QUEUE_ID VALUES(1);
    Query OK, 1 row affected (0.02 sec)
    
    MariaDB [hive]> INSERT INTO NEXT_TXN_ID VALUES(1);
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [hive]> COMMIT;
    Query OK, 0 rows affected (0.00 sec)

若不添加，直接进hive ，执行show table 命令就会报错;

hive> show tables;
    FAILED: Error in acquiring locks: Error communicating with the metastore

按如下方式创建一个表：

hive> create table t1(id int, name string,age int) 
        > clustered by (id) into 8 buckets 
        > stored as orc TBLPROPERTIES ('transactional'='true')
        > ;

之后就可以直接操作delete 语句

hive> delete from t1 where t1.id=1;
    WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
    Query ID = hadoop_20180830082934_7b82f109-27b5-45a2-9ee7-51594ec7dbea
    Total jobs = 1
    Launching Job 1 out of 1
    Number of reduce tasks determined at compile time: 8
    In order to change the average load for a reducer (in bytes):
      set hive.exec.reducers.bytes.per.reducer=<number>
    In order to limit the maximum number of reducers:
      set hive.exec.reducers.max=<number>
    In order to set a constant number of reducers:
      set mapreduce.job.reduces=<number>
    Starting Job = job_1535543680929_0002, Tracking URL = http://s201:8088/proxy/application_1535543680929_0002/
    Kill Command = /soft/hadoop/bin/hadoop job  -kill job_1535543680929_0002
    Hadoop job information for Stage-1: number of mappers: 0; number of reducers: 8
    2018-08-30 08:30:19,196 Stage-1 map = 0%,  reduce = 0%