[Hive] 데이터베이스, 테이블 기본 명령어

데이터베이스

• Hive는 실제 데이터베이스가 아니다.
• 실제 데이터는 HDFS에 저장되지만, 저장되는 데이터에 대한 메타 데이터를 관리하는 메타 스토어이다.

데이터베이스 생성

-- IF NOT EXISTS를 사용할 것을 권장한다.
CREATE DATABASE IF NOT EXISTS d1;

데이터베이스 정보 보기

DESCRIBE DATABASE d1;

데이터베이스 COMMENT 달기

CREATE DATABASE IF NOT EXISTS d2
COMMENT 'this is a database';

DESCRIBE DATABASE EXTENDED d2;

데이터베이스 생성시 DBPROPERTIES 속성 넣기 (생성한 사람, 생성 일자)

CREATE DATABASE IF NOT EXISTS d3
WITH DBPROPERTIES('creator'='hong', 'date'='2023-12-01');

DESCRIBE DATABASE EXTENDED d3;

생성된 모든 데이터베이스 보기

SHOW DATABASES;

데이터베이스 접속

USE d2;

테이블

Internal

• Hive가 메타 데이터와 실제 데이터 모두 관리한다.
• Internal 데이터에 접근하려면 hive를 통해서만 가능하다.

Internal 테이블 생성

테이블에 적재할 TEXTFILE

CREATE TABLE IF NOT EXISTS table1 (
    col1 string,
    col2 array<string>,
    col3 string,
    col4, int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' -- 컬럼 값 구분은 ','
COLLECTION ITEMS TERMINATED BY ':' -- 컬렉션 내부 배열 값 구분은 ':'
LINES TERMINATED BY '\n' -- 라인 구분은 '\n'
STORED AS TEXTFILE;

 

** 하이브에서 지원하는 다른 파일 형식 : Parquet, RC, ORC, etc..

Internal 테이블 생성 (ROW 이하절 생략시, default 값을 사용한다.)

• FIELDS 기본 TERMINATED BY :  Ctrl + A
• LINES 기본 TERMINATED BY : \n
• STORED AS 기본 : TEXTFILE 

CREATE TABLE IF NOT EXISTS table2 (
    col1 string,
    col2 array<string>,
    col3 string,
    col4 int
)

External

Hive가 메타 데이터만 관리한다. 실제 데이터에 대한 트랜잭션이 반영되지 않는다.

External 테이블 생성

-- INTERNAL 테이블 만드는 방법과 같으나 "EXTERNAL"을 명시적으로 적는다. 
CREATE EXTERNAL TABLE IF NOT EXISTS <table> (
    col1 string,
    col2 array<string>,
    col3 string,
    col4, int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' -- 컬럼 값 구분은 ','
COLLECTION ITEMS TERMINATED BY ':' -- 컬렉션 내부 배열 값 구분은 ':'
LINES TERMINATED BY '\n' -- 라인 구분은 '\n'
STORED AS TEXTFILE;

☆ Internal vs. External

가장 큰 차이는 테이블을 DROP 할 때 발생한다.

• Internal : 메타데이터와 실제 테이블 데이터 모두 손실된다.
• External : 메타데이터만 손실되고, 실제 데이터는 HDFS에서 그대로 존재한다. 메타 데이터 손실 이후 Hive에서는 지운 데이터에 연결이 되지 않는다.

데이터 저장 Location

기본 저장 Location 확인하기

SET hive.metastore.warehouse.dir;

테이블 생성시 Location을 명시적으로 지정하기

CREATE EXTERNAL TABLE IF NOT EXISTS table3 (
    col1 string,
    col2 array<string>,
    col3 string,
    col4 int
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ':'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/hong/table3'  -- 데이터 저장 위치를 '/user/hong/table3' 디렉터리로 설정
;

데이터 적재하기

☆ 파일을 테이블에 적재하기 (append)

-- Local 디렉터리에 있는 경우
LOAD DATA LOCAL INPATH '/home/hong/data.txt' INTO TABLE table1;

-- HDFS에 있는 경우
LOAD DATA INPATH '/home/hong/data.txt' INTO TABLE table1;

☆ 파일을 테이블에 덮어쓰기 (overwrite)

LOAD DATA LOCAL INPATH '/home/hong/data.txt' OVERWRITE INTO TABLE table1;

☆ 소스 테이블에서 타겟 테이블로 데이터 적재하기 (insert into)

• 소스의 테이블 스키마와 타겟 테이블의 스키마가 반드시 일치하지 않아도 된다. 컬럼의 개수가 달라도 된다.
• 소스 테이블을 타겟 테이블에 insert할 때, 만약 타겟 테이블의 컬럼에 값을 넣지 않으면 null 값이 적용된다.

INSERT INTO TABLE tab SELECT col1, col2, col3 FROM emp_tab;

☆ 소스 테이블에서 타겟 테이블로 데이터 덮어쓰기 (insert overwrite)

INSERT OVERWRITE TABLE tab SELECT col1, col2, col3 FROM emp_tab WHERE col3 = 'Developer';

다중 소스 테이블에서 단일 타겟 테이블로 데이터 적재하기 

FROM emp_tab INSERT INTO TABLE developer_tab 
SELECT col1, col2, col3
WHERE col3 = 'Developer'
INSERT INTO TABLE manager_tab
SELECT col1, col2, col3
WHERE col3 = 'Mgr'
;

ALTER 

테이블의 스키마를 변경하는 데 사용한다. 예를 들면, 새 열을 추가하거나 열 이름 변경, 테이블 이름 변경 등..

테이블 이름 변경하기

ALTER TABLE tab RENAME TO tab1;

테이블에 열 추가하기

ALTER TABLE tab ADD COLUMNS (col4 string, col5 int);

 

** 새로 추가된 열의 컬럼 값은 NULL로 채워진다.

테이블 컬럼 순서 변경하기

-- col1을 col3 뒤로 옮기기
ALTER TABLE tab CHANGE col1 col1 int AFTER col3;

 

** 실제 데이터 파일에서 값의 순서는 고정이나, 메타 데이터의 컬럼 순서만 변경하기 때문에 많은 주의가 필요하다.
예를 들면, col1 → col3 뒤로 옮겨서 3번째 컬럼이 col1(int)이 되었는데, 데이터 파일의 3번째 값은 string으로 되어있어
타입 불일치로 null로 읽혀지는 상황   

테이블 컬럼 이름 변경하기

ALTER TABLE tab CHANGE COLUMN col2 new_col2 string;

테이블 컬럼 제거하기

ALTER TABLE tab1 REPLACE COLUMNS (id int, name string);

TBLPROPERTIES 변경하기

ALTER TABLE tab1 SET TBLPROPERTIES('auto.purge'='true')

--  INPUT/OUTPUT 파일 포맷 변경 (TEXTFILE → Avro)
ALTER TABLE tab1 SET FILEFORMAT avro;

etc

SELECT에서 데이터 컬럼명 보기

SET hive.cli.print.header = true;

TBLPROPERTIES 확인하기

DESC FORMATTED tab1;