<div id="readability-page-1" class="page"><div><h3 data-ke-size="size23">Issue</h3>

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

다음과 같은 에러가 떴다.
<figure data-origin-width="1270" data-origin-height="285" data-ke-mobilestyle="alignCenter"><img src="https://blog.kakaocdn.net/dn/xbwJN/btrBLYlIiiX/gYlNzXPOgqcivuki4P72y1/img.png" data-ke-mobilestyle="alignCenter"></figure>


Hive에서 INSERT...VALUES, UPDATE와 DELETE문을 사용할 수 없어서 뜨는 에러다.
다음과 같이 해결하면 된다.
<hr contenteditable="false" data-ke-type="horizontalRule" data-ke-style="style6">
<h3 data-ke-size="size23">Solution</h3>

1)&nbsp;
먼저, 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 문들이 나와있다.
<a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/">https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/</a>&nbsp;
<figure id="og_1652198461763" contenteditable="false" data-ke-type="opengraph" data-ke-align="alignCenter" data-og-type="website" data-og-title="LanguageManual DDL - Apache Hive - Apache Software Foundation" data-og-description="ADD, ADMIN, AFTER, ANALYZE, ARCHIVE, ASC, BEFORE, BUCKET, BUCKETS, CASCADE, CHANGE, CLUSTER, CLUSTERED, CLUSTERSTATUS, COLLECTION, COLUMNS, COMMENT, COMPACT, COMPACTIONS, COMPUTE, CONCATENATE, CONTINUE, DATA, DATABASES, DATETIME, DAY, DBPROPERTIES, DEFERRE" data-og-host="cwiki.apache.org" data-og-source-url="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/" data-og-url="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/" data-og-image=""><a href="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/" target="_blank" rel="noopener" data-source-url="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/">

<div>
LanguageManual DDL - Apache Hive - Apache Software Foundation
ADD, ADMIN, AFTER, ANALYZE, ARCHIVE, ASC, BEFORE, BUCKET, BUCKETS, CASCADE, CHANGE, CLUSTER, CLUSTERED, CLUSTERSTATUS, COLLECTION, COLUMNS, COMMENT, COMPACT, COMPACTIONS, COMPUTE, CONCATENATE, CONTINUE, DATA, DATABASES, DATETIME, DAY, DBPROPERTIES, DEFERRE
cwiki.apache.org
</div>
</a></figure>

하지만 꼭 알아야 하는 건
1) table 이름, 속성 바꾸기
2) partition 관련한 것들, 즉 partition 추가/삭제/변경 등 (Alter Partition은 그냥 다 봐도 좋을 듯 싶다)
3) column 추가/삭제/변경 등


Alter Table

-Rename Table
<pre id="code_1652196198593" data-ke-language="sql" data-ke-type="codeblock"><code>ALTER TABLE table_name RENAME TO new_table_name;</code></pre>
ex. rename the table log_messages to logmsgs
<pre id="code_1652195857770" data-ke-language="sql" data-ke-type="codeblock"><code>ALTER TABLE log_message RENAME TO logmsgs;</code></pre>

-Alter Table Properties
<pre id="code_1652199288615" data-ke-language="sql" data-ke-type="codeblock"><code>ALTER TABLE table_name SET TBLPROPERTIES table_properties;
 
table_properties:
 : (property_name = property_value, property_name = property_value, ... )</code></pre>
ex.&nbsp;
<pre id="code_1652199469194" data-ke-language="sql" data-ke-type="codeblock"><code>ALTER TABLE log_messages SET TBLPROPERTIES ('notes' = 'The process id is no longer captured; this column is always NULL');</code></pre>


Alter Partition

-Add Partitions
<pre id="code_1652196790606" data-ke-language="sql" data-ke-type="codeblock"><code>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, ...)</code></pre>

-Drop Partitions
<pre id="code_1652196899168" data-ke-language="sql" data-ke-type="codeblock"><code>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)</code></pre>

ex.
<pre id="code_1652196721036" data-ke-language="sql" data-ke-type="codeblock"><code>-- 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);</code></pre>

Alter Columns
-Change Columns
<pre id="code_1652197958949" data-ke-language="sql" data-ke-type="codeblock"><code>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];</code></pre>

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

ex.
<pre id="code_1652198027549" data-ke-language="sql" data-ke-type="codeblock"><code>ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'The hours, minutes, and seconds part of the timestamp'
AFTER severity;</code></pre>

-Add/Replace Columns
<pre id="code_1652197582483" data-ke-language="sql" data-ke-type="codeblock"><code>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)</code></pre>
ex.
<pre id="code_1652197440293" data-ke-language="sql" data-ke-type="codeblock"><code>-- 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');</code></pre>

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


table 속성 확인 방법
<pre id="code_1652195307860" data-ke-language="sql" data-ke-type="codeblock"><code>DESCRIBE FORMATTED tablename</code></pre>

Hive Enable ACID Transactions
<pre id="code_1652195235579" data-ke-language="sql" data-ke-type="codeblock"><code>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</code></pre>

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

INSERT ... VALUES
<pre id="code_1652195151978" data-ke-language="sql" data-ke-type="codeblock"><code>INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]</code></pre>

UPDATE
<pre id="code_1652195151979" data-ke-language="sql" data-ke-type="codeblock"><code>UPDATE tablename SET column = value [, column = value ...] [WHERE expression]</code></pre>

DELETE
<pre id="code_1652195151980" data-ke-language="sql" data-ke-type="codeblock"><code>DELETE FROM tablename [WHERE expression]</code></pre>



Reference
 
</div></div>

sohyunwriter

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/ 
 
하지만 꼭 알아야 하는 건
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
https://sparkbyexamples.com/apache-hive/hive-enable-and-use-acid-transactions/
https://www.revisitclass.com/hadoop/how-to-rename-a-table-in-hive/
https://demo.gethue.com/hue/editor?editor=477903 
책 "Probrmming Hive"

(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)

(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)

댓글