[Hive] HiveQL (실습 필요)

Hyunjun Kim·2025년 8월 25일

Data_Engineering

목록 보기

144/153

5 HiveQL

HiveQL은 ANSI SQL 과 호환되는 부분이 많기는 하지만, RDBMS와는 차이가 많다. 따라서 SQL을 작성하는 과정에서 꼭 공식문서를 참고하는 것이 좋다.

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

HiveQL의 가장 큰 특징은 row의 PK가 없다는 것이다. 이에 따라서 query를 활용하는 방식도 다르니 주의해야한다.

5.1 Create table

5.1.1 Syntax

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]

beeline -u jdbc:hive2://localhost:10000/default -n hive

5.1.2 airline table

create external table airline
(
Year	int
,Month	int
,DayofMonth	int
,DayOfWeek	int
,DepTime	int
,CRSDepTime	int
,ArrTime	int
,CRSArrTime	int
,UniqueCarrier	string
,FlightNum	int
,TailNum	int
,ActualElapsedTime	int
,CRSElapsedTime	int
,AirTime	int
,ArrDelay	int
,DepDelay	int
,Origin	string
,Dest	string
,Distance	int
,TaxiIn	int
,TaxiOut	int
,Cancelled	boolean
,CancellationCode	char(1)
,Diverted	boolean
,CarrierDelay	int
,WeatherDelay	int
,NASDelay	int
,SecurityDelay	int
,LateAircraftDelay	int
)
ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
location "hdfs://ha-nn-uri/user/hive/warehouse/default.db/airline" /* same to "warehouse/default.db/airline" */

location 에 namespace 를 포함한 full path 가 없다면, hive 에 로그인한 유저의 경로를 기본 path 로 해서 상대경로에 생성된다.
- namespace 는 core-site.xml, hdfs-site.xml 에서 확인
- hive user 인 경우 /user/hive 가 기본경로
- location "hdfs://ha-nn-uri/user/hive/warehouse/default.db/airline"에서 ha-nn-uri는 어디서 왔냐?
  - EMR primary node 에서 vi /etc/hadoop/conf.empty/core-site.xml 들어가보면 name : fs.defaultFS, value :hdfs://ha-nn-uri로 되어 있음
  - hdfs dfs ls root 하면 이 HDFS 클러스터 네임 스페이스의 루트로 접근하게 되는 것.

ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.OpenCSVSerde' :
- FORMAT SERDE(Serializer/Deserializer) OpenCSVSerde 는 하둡 내장하고 있는 SERDE인데, CSV 파일 콤마 기준 해석할 수 있음
- FIELDS TERMINATED BY char,LINES TERMINATED BY char 안 쓰더라도 여기에서 파싱을 해준다.

show create table airline;

5.2 Insert

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 테이블을 읽도록 디자인한다.

메타데이터 데이터베이스를 Hive로 이용하고자 할 때, 보통 DB를 통째로 덤프한 Hive 테이블을 읽도록 설계한다. 이는 유저의 행동 이벤트 데이터를 분석할 때 주로 필요하다.

예를 들어, 이벤트 데이터는 userid, action, time과 같은 형태로 들어오는데, 누가 어떤 행동을 얼마나 했는지 분석하기 위해서는 해당 유저의 메타데이터 정보가 필요하다. 따라서 이벤트 데이터를 분석하려면 메타데이터 테이블과 조인이 필수적이다.

이러한 경우, 메타데이터를 실시간으로 조회하는 대신, 일정 주기(예: 1시간 단위)로 DB 전체를 Hive 테이블로 덤프하고, 조인 시 최신 덤프 테이블만 사용하도록 설계하는 방식이 많이 활용된다.

예를 들어 1시 단위로 덤프한 테이블을 hour1이라 하고, 2시 단위 덤프는 hour2라 하면, 2시에 들어온 데이터를 분석할 때는 hour2 테이블만 참조하여 조인한다. 이렇게 하면 대용량 이벤트 데이터와 메타데이터를 효율적으로 결합할 수 있다.

5.2.1 실습

거의 쓸 일은 없겠지만 실습을 해 보자.

create table emp (
    empno int
    ,user_name string
    ,age int
    ,dept string
);

insert into table emp values (1, "Paul", 24, "engineering");
insert into table emp values (2, "John", 25, "engineering");
insert into table emp values (3, "Nancy", 21, "marketing");
insert into table emp values (3, "Yarn", 29, "engineering");
select * from emp limit 4;

기본 location: 'hdfs://ha-nn-uri/user/hive/warehouse/emp’
PK가 없으므로 4개의 데이터가 모두 들어간다.
내부적으로 Tez execution engine으로 실행되기 때문에 쭈르륵 남는 로그는 Tezrrk 맵리듀스 작업을 하면서 수행되는 로그라고 보면 됨.

5.3 LOAD DATA

LOAD DATA는 HIVE명령어 인데,
HDFS 또는 다른 저장소에 있는 데이터를 table 로 mv 또는 copy한다.

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)

[LOCAL] 경로가 local하면 이 스크립트 실행하는 로컬머신에 있는 file path 고 이걸 안 주고 INPATH에서 그냥 하면 요거는 HDFS에서 찾는 path.
load data 커맨드는 hive 의 table 의 경로로 데이터를 copy/move 하는 것이므로 주의한다.
inpath 가 같은 hdfs 라면 move 를 한다.(원본 데이터가 없어진다)
INTO TABLE tablesname : 테이블에서 아까 Create table 할 때 지정된 location 으로 옮기게 된다.

5.3.1 실습

LOAD DATA INPATH '/data/input' INTO TABLE airline INPUTFORMAT 'textfile' SERDE'org.apache.hadoop.hive.serde2.OpenCSVSerde';

금방 된다. 왜냐면 그냥 HDFS에서 HDFS로, 같은 클러스터에서 옮기는 거니까. 사실상 경로만 바뀌게 됨.

조회

select year, month, dayofmonth as day, origin, deptime, dest, arrtime, distance, ActualElapsedTime as elapsed_t
from airline
limit 3;

이후 실습을 위해 원본을 복원한다.

#!/bin/bash
from="/user/hive/warehouse/default.db/airline/*.csv"
to="/data/input"
hdfs dfs -cp $from $to/

5.4 Select

Select 는 일반 SQL과 사용법이 유사하다. 자세한 내용은 매뉴얼을 참고하는 것이 가장 정확하다.

[WITH CommonTableExpression (, CommonTableExpression)*]    (Note: Only available starting 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 과 관련된 설정이 성능에 큰 영향을 미친다.
즉각적인 응답을 요하는 작업에는 적합하지 않다.
- HiveQL은 기본적으로 YARN에 작업을 제출하고, 해당 작업이 MapReduce 과정을 거쳐 실행된 후 결과를 반환하는 방식이기 때문이다. 또한 Hive는 정렬 기준을 한 가지로만 지정할 수 있고, 쿼리 종류나 정렬 요건에 따라 실행 시간이 길어질 수 있다. 따라서 실시간 또는 즉시 응답이 필요한 분석에는 적합하지 않다.
대량의 데이터다 보니 limit 문을 항상 붙이는 습관을 들어 줘야 한다.

5.4.1 실습

select DepDelay, year, month, dayofmonth as day, origin, deptime, dest, arrtime, distance
from airline
where Year=2008
and DepDelay > 0
order by DepDelay desc
limit 10;

위 명령어를 입력하면 Tez에서 실행 계획을 세우고 실행을 함.

5.5 Update, Delete

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

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

5.6 Alter Table

Alter table 을 통해서 table 의 속성이나 schema, partition 정보 등을 변경할 수 있다.

Alter table 은 테이블의 속성을 변경하는 것인만큼 주의해서 사용해야한다.
대상되는 데이터가 많다 보니 Alter Talbe을 수행하는 몇몇 오퍼레이션들은 굉장히 부하를 많이 받을 수도 있고 실패할 수도 있음

항상 매뉴얼을 확인하자.

대표적으로 많이 사용하는 alter table 명령어 몇 개만 알아보자.

5.6.1 rename table

ALTER TABLE table_name RENAME TO new_table_name;

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

5.6.2 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];

column의 이름 변경이 필요할 때 사용한다.

타입변경은 replace column 을 해야한다.

alter table emp_2 change column empno emp_no int;

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

replace columns 에서는 모든 column 정보를 삭제후 다시 만드는 작업이다.
- 바꿀 컬럼만 쓰는 게 아니라 모든 컬럼을 써야 함.
- drop column 을 위해 사용하기도 한다.
- column 의 타입 변경이 필요할 때 사용한다.

alter table emp_2 add columns (adding string);

alter table emp_2 replace columns (emp_no bigint, user_name string, age int, dept string);

show create table emp_2;
select * from emp_2 limit 5;

alter table emp_2 replace columns (emp_no bigint, user_name string, age int, dept string);

show create table emp_2;
select * from emp_2 limit 5;

5.6.4 add partition

add partition을 하려면 External table을 선언하고 데이터를 원래 load를 해 놔야 함.

ℹ️ 실습에 사용하는 데이터는, P08-C02 AWS EMR Hadoop 실습 > 2 실습용 데이터 다운로드 에서 세팅한 데이터를 사용한다.

다음 script 로 /data/input 에 있는 연도별 데이터를 파티션 디렉토리 구성에 맞게 copy한다.

vi cp-data-hive.sh

실습을 위해 아래 내용 복붙하자

#!/bin/bash
hdfs dfs -mkdir /user/hive/warehouse/default.db/airline_partition

for year in {1987..2008}
do
        hdfs dfs -mkdir /user/hive/warehouse/default.db/airline_partition/year=${year}
        hdfs dfs -cp /data/input/${year}.csv /user/hive/warehouse/default.db/airline_partition/year=${year}/${year}.csv
done

./cp-data-hive.sh

table 을 선언한다.

create external table airline_partition
(
Month	int
,DayofMonth	int
,DayOfWeek	int
,DepTime	int
,CRSDepTime	int
,ArrTime	int
,CRSArrTime	int
,UniqueCarrier	string
,FlightNum	int
,TailNum	int
,ActualElapsedTime	int
,CRSElapsedTime	int
,AirTime	int
,ArrDelay	int
,DepDelay	int
,Origin	string
,Dest	string
,Distance	int
,TaxiIn	int
,TaxiOut	int
,Cancelled	boolean
,CancellationCode	char(1)
,Diverted	boolean
,CarrierDelay	int
,WeatherDelay	int
,NASDelay	int
,SecurityDelay	int
,LateAircraftDelay	int
)
PARTITIONED BY (year int)
ROW FORMAT SERDE'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
location "hdfs://ha-nn-uri/user/hive/warehouse/default.db/airline_partition" /* same to "warehouse/default.db/airline" */
;

PARTITIONED BY (year int) 에 해당하는 컬럼은 create external table talbename 에서 빠져야 함

데이터를 조회해본다.

select year, month, dayofmonth as day, origin, deptime, dest, arrtime, distance, ActualElapsedTime as elapsed_t
from airline_partition
limit 3;

아무 것도 나오지 않는다.
create 시 External table로 했었는데, External table은 hive가 그 데이터를 HDFS 을 통해서 데이터를 조작하면 특히 새로운 파티션이 추가되거나 삭제되었을 때 Hive는 그 내용을 모른다. 이미 자기가 인덱싱 하고 있는 경로는 모르겠는데 새로운 디렉토리가 생겼는지 등 변화가 생긴 것을 모른다. 그래서 이 친구한테 알려 줘야 함.

add partition

ALTER TABLE airline_partition ADD PARTITION (year='1987') location '/user/hive/warehouse/default.db/airline_partition/year=1987';

다시 조회해본다.

select year, month, dayofmonth as day, origin, deptime, dest, arrtime, distance, ActualElapsedTime as elapsed_t
from airline_partition
limit 3;

1987 이 아닌 다른 year 조건으로 조회해본다.

select year, month, dayofmonth as day, origin, deptime, dest, arrtime, distance, ActualElapsedTime as elapsed_t
from airline_partition
where year=2008
limit 3;

year가 1987인 것만 알려주었기 때문에 다른 디렉토리의 파티션들은 인식을 하지 못 함

5.7 MSCK

external table 인 경우, 데이터의 변경사항이 hive table 에 실시간으로 반영되지 않는다.
그동안 업데이트된 데이터를 한 번에 hive 테이블에 반영하고 싶을 때, MSCK를 쓴다.

주의할 점은, 데이터가 많으면 MSCK 작업이 오래걸릴 뿐만 아니라 부하 때문에 OOM으로 죽을 수도 있다.

이 경우 partition 별로 나누어서 수행하거나, batch.size 를 설정한다.

MSCK [REPAIR] TABLE table_name [ADD/DROP/SYNC PARTITIONS];

기본적으로는 뒤에 [ADD/DROP/SYNC PARTITIONS] 옵션들을 주지 않아도 알아서 잘 적용을 함

5.7.1 batch.size

조회

set hive.msck.repair.batch.size;

set 이지만 = 이 없으면 조회하는 것.
0이라면 batch.size 가 없다.(무제한)

세팅

set hive.msck.repair.batch.size=$num;

5.7.2 msck 수행

앞서서 추가하지 못한 남은 파티션을 msck 로 추가한다.

set hive.msck.repair.batch.size=3;
msck repair table airline_partition;