HiveQL

• ANSI SQL과 많이 호환되지만 RDBMS와 차이가 많음
• https://cwiki.apache.org/confluence/display/Hive/LanguageManual
• 가장 큰 특징은 row의 PK가 없음 -> query 활용 방식도 다름

Create

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name
[(col_name data_type [column_constraint] [COMMENT col_comment], ...)]
[PARTITIONED BY (col_name data_type [COMMENT 'col_comment'], ...)]
[CLUSTERED BY (col_name, col_name,.......]
[COMMENT table_comment]
[ROW FORMAT row_format]
[FIELDS TERMINATED BY char]
[LINES TERMINATED BY char]
[LOCATION 'hdfs_path']
[STORED AS file_format]

Insert

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] 
select_statement1 FROM from_statement;

• PK가 없으므로 계속 데이터가 append 됨
• 대량의 데이터를 insert를 통해 넣는 것은 적합하지 않음
• 대량의 데이터를 주로 파일로 HDFS에 직접 쌓던가, load 명령어를 통해 넣으므로 insert를 쓸일이 거의 없음
• PK가 없으므로 metadata를 추가하는 용도로 적합하지 않음
  • 메타데이터 데이터 베이스를 hive 로 이용하고 싶을 때는, DB를 통째로 덤프한 hive 테이블을 읽도록 디자인

-- append new rows to tablename1
INSERT INTO TABLE tablename1 select_statement1 FROM from_statement; 

-- replace contents of tablename1
INSERT OVERWRITE TABLE tablename1 select_statement1 FROM from_statement; 

-- more complex example using WITH clause
WITH tablename1 AS (select_statement1 FROM from_statement) INSERT [OVERWRITE/INTO] TABLE tablename2 select_statement2 FROM tablename1;

-- using with phase
WITH table_a_tmp AS
(
SELECT col1, col2, 'test1' as col3
  FROM table_a
)
INSERT OVERWRITE TABLE table_b
SELECT col1, col2, col3
  FROM table_a_tmp;

제한

• 스트리밍 업데이트를 수신하지 않는 테이블에서 INSERT OVERWRITE는 테이블의 기존 데이터를 삭제하고 새 행을 씀
• 스트리밍 업데이트를 수신하는 테이블에는 INSERT OVERWRITE를 사용하지 않는 것이 좋음. INSERT OVERWRITE는 최근에 수신된 스트리밍 업데이트 행이나 INSERT OVERWRITE 실행 중에 도착하는 업데이트를 삭제하지 않음. 일반적으로 스트리밍 업데이트를 받는 테이블에 대해 INSERT OVERWRITE를 수행해야 하는 경우 INSERT OVERWRITE를 수행하기 전에 스트리밍 업데이트를 중지해야 함
• 파티셔닝 및 INSERT: TD는 데이터 파티셔닝(시간 기반 및 사용자 정의 파티셔닝)에 대한 자체 접근 방식을 구현하므로 표준 Hive INSERT INTO... PARTITION 절은 지원되지 않음

Select

• 일반 SQL과 사용법이 유사
• https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select

[WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available start
ing with Hive 0.13.0)
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]

• 정렬되어있지 않은 데이터를 조회하면 데이터 조회가 오래 걸림
• execution engine과 관련된 설정이 성능에 큰 영향을 미침
• 즉각적인 응답을 요하는 작업에는 적합하지 않음

Update, Delete

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

• where 조건에 맞는 대상을 모두 업데이트/삭제 하므로, where 조건을 주의해야함
• hive에는 unique key가 없으므로 단일 row의 update/delete는 불가능함
• Partitioning, Bucketing 컬럼은 업데이트/삭제 할 수 없음
  • add/drop partition을 써야함
• https://cwiki.apache.org/confluence/display/Hive/Hive+Transactions
  hive transaction 과 ACID를 지원하는 경우에만 사용할 수 있음
  • 사실상 사용할 수 없음

Alter Table

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

-- rename table
ALTER TABLE emp RENAME TO emp_2;
show create table emp_2;
show create table emp;

-- change column: 컬럼 이름 변경
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];

-- add/replace column: 컬럼 타입 변경 및 삭제
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)

Reference

• https://api-docs.treasuredata.com/en/tools/hive/writing_hive_queries/?_gl=1