(Hive) Hive Error while compiling statement: FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations. - HIVE ALTER TABLE 명령어 사용법 정리 (INSERT, DROP, UPDATE X)

Issue

 

테이블 mytable에서 part_yrmn이 '202203'인 것을 제외하려고 다음과 같이 입력했더니

Delete from mytable where part_yrmn='202203'

 

다음과 같은 에러가 떴다.

 

 

Hive에서 INSERT...VALUES, UPDATE와 DELETE문을 사용할 수 없어서 뜨는 에러다.

다음과 같이 해결하면 된다.

---

Solution

 

1) 

먼저, Hive 0.14.0부터 INSERT...VALUES, UPDATE, DELETE가 full ACID support에서 사용가능하게 되었다.

즉, Hive 0.14.0 아래 버전을 쓰고 있다면 위 statements를 사용할 수 없다.

 

대신 ALTER TABLE statements를 사용하면 된다.

 

사실 Hive 공식 docs의 ALTER TABLE Statements를 보면 정말 많은 ALTER TABLE 문들이 나와있다.

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/ 

LanguageManual DDL - Apache Hive - Apache Software Foundation

 

하지만 꼭 알아야 하는 건

1) table 이름, 속성 바꾸기

2) partition 관련한 것들, 즉 partition 추가/삭제/변경 등 (Alter Partition은 그냥 다 봐도 좋을 듯 싶다)

3) column 추가/삭제/변경 등

 

 

Alter Table

 

-Rename Table

ALTER TABLE table_name RENAME TO new_table_name;

ex. rename the table log_messages to logmsgs

ALTER TABLE log_message RENAME TO logmsgs;

 

-Alter Table Properties

ALTER TABLE table_name SET TBLPROPERTIES table_properties;
 
table_properties:
  : (property_name = property_value, property_name = property_value, ... )

ex. 

ALTER TABLE log_messages SET TBLPROPERTIES ('notes' = 'The process id is no longer captured; this column is always NULL');

 

 

Alter Partition

 

-Add Partitions

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
 
partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

 

-Drop Partitions

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]
  [IGNORE PROTECTION] [PURGE];            -- (Note: PURGE available in Hive 1.2.0 and later, IGNORE PROTECTION not available 2.0.0 and later)

 

ex.

-- ADD PARTITION
ALTER TABLE log_messages ADD IF NOT EXISTS
PARTITION (year=2011, month=1, day=1) LOCATION '/logs/2011/01/01'
PARTITION (year=2011, month=1, day=2) LOCATION '/logs/2011/01/02'
PARTITION (year=2011, month=1, day=3) LOCATION '/logs/2011/01/03'
...;

-- CHAGE PARTITION LOCATION
ALTER TABLE log_messages PARTITION(year=2011, month=12, day=2)
SET LOCATION 's3n://ourbucket/logs/2011/01/02'; -- not move data from old location, nor does it delete old data

-- DROP PARTITION
ALTER TABLE log_messages DROP IF EXISTS PARTITION(year=2011, month=12, day=2);

 

Alter Columns

-Change Columns

ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];

 

컬럼명이나 타입이 바뀌지 않고 위치만 바뀐다고 하더라도 col_old_name, col_new_name, column_type은 반드시 명시해야 한다. AFTER는 optional이고, 맨 처음 등장해야하는 컬럼이면 FIRST를 쓰면 된다.

 

ex.

ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'The hours, minutes, and seconds part of the timestamp'
AFTER severity;

 

-Add/Replace Columns

ALTER TABLE table_name 
  [PARTITION partition_spec]                 -- (Note: Hive 0.14.0 and later)
  ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
  [CASCADE|RESTRICT]                         -- (Note: Hive 1.1.0 and later)

ex.

-- ALTER columns
ALTER TABLE log_messages ADD COLUMNS (
 app_name STRING COMMENT 'Application name',
 session_id LONG COMMENT 'The current session id');
 
-- REPLACE columns
ALTER TABLE log_messages REPLACE COLUMNS (
 hours_mins_secs INT COMMENT 'hour, minute, seconds from timestamp',
 severity STRING COMMENT 'The message severity',
 message STRING COMMENT 'The rest of the message');

 

2) 

Hive 0.14.0부터 위 statements를 사용할 수 있지만, table 속성을 transactional로 설정해둬야한다.

 

 

table 속성 확인 방법

DESCRIBE FORMATTED tablename

 

Hive Enable ACID Transactions

SET hive.support.concurrency=true;
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
# The follwoing are not required if you are using Hive 2.0
SET hive.enforce.bucketing=true;
SET hive.exec.dynamic.partition.mode=nostrict;
# The following parameters are required for standalone hive metastore
SET hive.compactor.initiator.on=true;
SET hive.compactor.worker.threads=1

 

그리고 INSERT ... VALUES, UPDATE, DELETE를 사용하면 된다. 하지만 0.14.0 hive에서는 또 안 돌아갈테니 버전을 고려해서 처음부터 1)로 쿼리를 만드는 게 더 나아보인다.

 

INSERT ... VALUES

INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

 

UPDATE

UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

 

DELETE

DELETE FROM tablename [WHERE expression]

 

 

 

Reference

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

How to rename a table in Hive? -

https://sparkbyexamples.com/apache-hive/hive-enable-and-use-acid-transactions/

Hive - How to Enable and Use ACID Transactions? - Spark by {Examples}

https://www.revisitclass.com/hadoop/how-to-rename-a-table-in-hive/

How to rename a table in Hive? -

https://demo.gethue.com/hue/editor?editor=477903 

Hue - SQL Editor

책 "Probrmming Hive"