Hive 명령어 정리

---

◾ 데이터베이스 생성

CREATE DATABASE IF NOT EXISTS db_nm;

 

 

◾ 테이블 생성

• External : hive에서 테이블 drop할 경우 hdfs 경로 및 파일 보존
  • HDFS 경로의 데이터 뿐만 아니라 Amazon, Azure 등의 클라우드 스토리지로 지정 가능
• Managed : hive에서 테이블 drop할 경우 hdfs 경로 및 파일 삭제 

 

-- External Table
CREATE EXTERNAL TABLE IF NOT EXISTS db_nm.table_nm ( 
a string comment 'a',
b string comment 'b',
c string comment 'c'
) 
comment 'table comment'
PARTITIONED BY (DT STRING) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE 
LOCATION 'hdfs://' 
;

-- Managed(Internal) Table
CREATE TABLE IF NOT EXISTS db_nm.table_nm ( 
a string comment 'a',
b string comment 'b',
c string comment 'c'
) 
comment 'table comment'
PARTITIONED BY (DT STRING) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
STORED AS TEXTFILE  
;

 

 

 

◾ 테이블 DROP

• MANAGED 테이블
  • hdfs 데이터 삭제 
• EXTERNAL 테이블
  • tblproperties에 'external.table.purge'='TRUE'가 있으면  hdfs 데이터 삭제 됨
  • tblproperties에 'external.table.purge'='TRUE'가 없으면  hdfs 데이터 삭제  안됨
• drop table ... purge;
  • purge 키워드가 없으면 hdfs 데이터 삭제될 때 .Trash로 옮겨짐
  • purge 키워드가 있으면 hdfs 데이터 삭제될 때 .Trash로 옮겨지지 않고 바로 삭제
  • purge 키워드는 hdfs rm 명령어의 -skipTrash 옵션을 사용한 것과 동일 효과
• truncate table <table_name>;
  • 스키마는 유지하고 데이터만 지울 경우 사용 (Managed 테이블에서만 실행 가능)

 

DROP TABLE IF EXISTS db_nm.table_nm;

 

 

◾ 파티션 생성 (3가지 방법)

• MSCK REPAIR TABLE
  • hdfs 경로를 읽어서 존재하는 파티션 정보를 모두 추가
  • 해당 테이블의 hdfs 경로 전체를 읽기 때문에 데이터가 많은 경우 timeout이나 OOM 발생
  • hive.msck.repair.batch.size로 OOM 예방 가능
• ALTER TABLE ## ADD PARTITION
  • 수동으로 특정 파티션 정보를 추가할 수 있음
• ALTER TABLE ## ADD PARTITION ## LOCATION
  • hdfs 파티션 경로는 기본적으로 /tmp/dt=20200320의 (=) 형태를 갖춰야 제대로 인식을 하고 사용 가능하지만 hdfs 경로의 (=)을 지정해 줄 수 없는 경우 LOCATION을 사용하여 파티션과 hdfs경로를 매핑해 줄 수 있음
• 명령어 수행 중 에러 발생하지 않도록 IF NOT EXISTS 추가

 

MSCK REPAIR TABLE db_nm.table_nm;

ALTER TABLE db_nm.table_nm ADD IF NOT EXISTS PARTITION(dt='20200327');

ALTER TABLE db_nm.table_nm ADD IF NOT EXISTS PARTITION (dt='20200320') LOCATION '/tmp/audit/test/20200320';
ALTER TABLE db_nm.table_nm ADD IF NOT EXISTS PARTITION (dt='20200320',hour='00') LOCATION '/tmp/audit/test/20200320/00';

 

 

◾ 파티션 삭제

ALTER TABLE db_nm.table_nm DROP IF EXISTS PARTITION(dt='20200327');

 

 

◾ 테이블 스키마 조회

desc db_nm.table_nm;

 

 

◾ 테이블 스키마 조회

• hdfs 경로, 구분자 정보 등 포함, 자세히 볼 수 있음

 

desc formatted db_nm.table_nm;

 

 

◾ 테이블 Comment 조회

desc extended db_nm.table_nm;

 

 

◾ 테이블 이름 변경

• 파티션 재생성 할 필요 없음

 

ALTER TABLE table_name RENAME TO new_table_name;

 

 

◾ DELIMITER 변경

•  'field.delim' = 원하는 구분자로 변경

 

ALTER TABLE table_nm set serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim' = ',');

 

 

◾ 테이블 스키마 컬럼 추가

• 이 방법은 테이블 스키마 마지막에 컬럼을 추가할 때만 사용

 

ALTER TABLE db_nm.tb_nm ADD COLUMS (`col10` STRING COMMENT '10번째 컬럼', `col11` STRING);

 

 

◾ 스키마 Locaion 변경

• 파티션 존재하는 경우 파티션 모두 drop 해주고 다시 msck repair table로 파티션 생성 필요

 

ALTER TABLE table_nm SET LOCATION "hdfs://want/location";

 

 

◾ 컬럼명 및 컬럼타입 변경

-- 전체적용
ALTER TABLE db_nm.tb_nm CHANGE COLUMN old_col new_col STRING;

-- 파티션 많을 경우 파티션 별 적용
ALTER TABLE db_nm.tb_nm PARTITION(part_dt=20201124) CHANGE COLUMN old_col new_col CHAR(5);

 

 

◾ hive/beeline 현재 접속 user 확인

select current_user();

 

 

◾ Hive SQL로 CSV 파일 HDFS 경로에 저장

insert overwrite directory 'hdfs://clster-name/tmp/csv'
row format delimited fields terminated by ','
select * from tmp.csv_table;

 

 

◾ Hive SQL로 CSV 파일 로컬 서버 경로에 저장

insert overwrite local directory 'file:///tmp/csv'
row format delimited fields terminated by ','
select * from tmp.csv_table;

 

 

◾ 테이블 조회 (like 구문 사용)

use default;
show tables like 'sample.*';    -- default 데이터베이스에 sample로 시작하는 테이블 조회

 

 

◾ String Type으로 저장한 timestamp 컬럼 값을 date format으로 변환하는 방법

from_unixtime(cast(ds_timestamp as bigint) div 1000, 'yyyy-MM-dd HH')

 

 

◾ hql 파일로 여러 줄 실행할 때 에러 발생해도 계속 진행하는 옵션

hive --force=true -f test.hql

 

 

◾ Hive 쿼리 진행 안될 시 참고할 명령어

show locks;
show transactions;

 

 

◾ Hive 쿼리 결과 파일 merge (1개파일로 병합)

--mapreduce 엔진일 경우
set hive.merge.mapredfiles=true;

--tez 엔진일 경우
set hive.merge.tezfiles=true;

--파일 1개당 사이즈
set hive.merge.size.per.task=512000000;