[Hive] 로컬 CSV 데이터를 Hive 테이블에 Load하기

이번 글에서는 client에 있는 원하는 데이터를 Hive table로 만드는 과정을 정리해봤습니다. 저는 현재 업무 중 하나로 음성 DB를 다루는 일을 수행하고 있는데요. 이와 관련한 간단한 데이터를 놓고 실습을 진행해보겠습니다.

전체적인 프로세스는 다음과 같습니다.

1. 파일을 하둡 HDFS 에 원하는 location에 Upload합니다.

2. Upload 된 파일을 읽기 위해 임시 외부 테이블을 생성합니다.데이터 적재가 완료되면 삭제해야 할 것입니다.

3. 임시 외부 테이블 데이터를 읽어 최종 내부 테이블에 적재합니다.
   • 이때 파일 포맷은 ORC 또는 PARQUET 로 변경합니다.

4. 최종 내부 테이블에 정상적으로 데이터가 쌓였는지 확인합니다.

5. 임시 외부 테이블을 DROP 하고 Upload 한 파일을 삭제합니다.

데이터 준비

[hadoop] [user@user-MacBookPro-5 ~/Downloads 14:40:35] hadoop fs -put /Users/user/Downloads/example-2021-06-11.txt /ns/new/speech_db/txt
[hadoop] [user@user-MacBookPro-5 ~/Downloads 14:41:07] hadoop fs -ls /ns/new/speech_db/txt
Found 1 items
-rw-rw-r--   3 user new        108 2021-05-22 14:41 /ns/new/speech_db/txt/example-2021-06-11.txt
[hadoop] [user@user-MacBookPro-5 ~/Downloads 14:41:14]

다음 파일을 가지고 테이블을 생성하도록 하겠습니다.

https://s3-us-west-2.amazonaws.com/secure.notion-static.com/77e95408-8c22-413c-ad70-a006f95228b3/example-2021-06-11.txt

INDX, PATH_WAV, UTTERANCE 으로 구성되었고 \t(탭) 으로 구분된 파일입니다.

CSV, JSON 등 여러 파일 포맷과 RDBMS, Log 등 여러 소스를 가지고 테이블을 생성할 수 있으며, 어떤 마이그레이션 툴을 사용하던지 공용 하둡 HDFS 에 Upload 되면 테이블을 생성할 수 있습니다.

임시 외부 테이블 생성

---

1. HDFS 내의 지정된 위치를 가리키는 외부 테이블을 임시 테이블로 생성

   -- 테이블이 있다면 삭제(DROP) 합니다.
   DROP TABLE IF EXISTS speech_db.speech_external_db;
    
   -- 테이블을 생성합니다. 첫번째 행은 Skip 합니다.
   CREATE EXTERNAL TABLE speech_db.speech_external_db (
     INDX int
     , PATH_WAV string
     , UTTERANCE string
   )
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '\t' -- \t 으로 필드를 구분합니다.
   LINES TERMINATED BY '\n'  -- \n 으로 라인을 구분합니다.
   STORED AS TEXTFILE
   LOCATION '/ns/new/speech_db/txt' -- 업로드된 파일의 부모 디렉토리를 지정합니다.
   TBLPROPERTIES ('skip.header.line.count'='1');

2. 임시 외부 테이블 데이터 조회 확인

   SELECT * FROM speech_db.speech_external_db LIMIT 2;
    
   +----------------------+--------------------------+------------------------------------+
   | speech_external_db.indx  | speech_external_db.path_wav  |      speech_external_db.utterance      |
   +----------------------+--------------------------+------------------------------------+
   | 1                    | /root/1.wav              | This is an example                 |
   | 2                    | /root/2.wav              | Let us learn apache hive together  |
   +----------------------+--------------------------+------------------------------------+
   1 row selected (0.274 seconds)

최종 내부 테이블 생성

---

1. 최종 내부 테이블을 생성
   • 날짜로 파티셔닝을 진행하겠습니다. 파일 포맷은 ORC 로 생성합니다.

   -- 테이블이 없다면 생성합니다. ORC 파일 포맷으로 데이터를 저장합니다.
   CREATE TABLE IF NOT EXISTS speech_db.speech_internal_db (
     INDX int
     , PATH_WAV string
     , UTTERANCE string
   )
   PARTITIONED BY ( ymd string ) -- 파티셔닝 칼럼명은 ymd 입니다.
   STORED AS ORC;

2. 최종 테이블 Location 확인
   • /ns/new/speech_db.db/speech_internal_db 에 생성된 것을 확인할 수 있습니다.

   SHOW CREATE TABLE speech_db.speech_internal_db;
    
   +----------------------------------------------------+
   |                   createtab_stmt                   |
   +----------------------------------------------------+
   | CREATE TABLE `speech_db.speech_internal_db`(                          |
   |   `indx` int,                                      |
   |   `path_wav` string,                               |
   |   `utterance` string)                              |
   | PARTITIONED BY (                                   |
   |   `ymd` string)                                    |
   | ROW FORMAT SERDE                                   |
   |   'org.apache.hadoop.hive.ql.io.orc.OrcSerde'      |
   | STORED AS INPUTFORMAT                              |
   |   'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'  |
   | OUTPUTFORMAT                                       |
   |   'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' |
   | LOCATION                                           |
   |   'hdfs://hadoop-ns/new/speech_db.db/speech_internal_db'  |
   | TBLPROPERTIES (                                    |
   |   'transient_lastDdlTime'='1621662306')            |
   +----------------------------------------------------+
   16 rows selected (0.351 seconds)

3. 임시 테이블에서 데이터를 읽어 최종 테이블에 적재
   • 최종 테이블 파티션에 데이터가 있다면 OVERWRITE 되니 주의 해야합니다.

   -- 2021-06-11 파티션이 없다면 추가합니다.
   ALTER TABLE speech_db.speech_internal_db ADD IF NOT EXISTS PARTITION (ymd='2021-06-11');
    
   
   -- 최종 테이블의 2018-01-01 파티션에 데이터가 있다면 OVERWRITE 되니 주의 바랍니다.
   INSERT OVERWRITE TABLE speech_db.speech_internal_db PARTITION (ymd='2021-06-11')
   SELECT * FROM speech_db.speech_external_db;

4. 최종 테이블에 데이터가 잘 들어갔는지 확인

   SELECT * FROM speech_db.speech_internal_db WHERE ymd='2021-06-11';
    
   +----------------------+--------------------------+------------------------------------+---------------------+
   | speech_internal_db.indx  | speech_internal_db.path_wav  |      speech_internal_db.utterance      | speech_internal_db.ymd  |
   +----------------------+--------------------------+------------------------------------+---------------------+
   | 1                    | /root/1.wav              | This is an example                 | 2021-06-11          |
   | 2                    | /root/2.wav              | Let us learn apache hive together  | 2021-06-11          |
   +----------------------+--------------------------+------------------------------------+---------------------+
   2 rows selected (0.209 seconds)

5. 파티션 디렉토리가 생성 되었는지 확인

   [hadoop] [user@user-MacBookPro-5 ~/Downloads 14:41:14] hadoop fs -ls /ns/new/speech_db.db/speech_internal_db
   Found 1 items
   drwxrwxr-x   - user new          0 2021-05-22 15:01 /ns/new/speech_db.db/speech_internal_db/ymd=2021-06-11

6. 임시 테이블을 삭제

   DROP TABLE speech_db.speech_external_db;

7. 임시 테이블 LOCATION 디렉토리 삭제

   [doopey] [user ~/Downloads/hive 16:32:08] hadoop fs -rm -r /ns/new/speech_db/txt/example-2021-06-11.txt
   21/05/22 15:03:53 INFO fs.TrashPolicyDefault: Moved: 'hdfs://hadoop-ns/new/speech_db/txt/example-2021-06-11.txt' to trash at: hdfs://hadoop-ns/user/user/.Trash/Current/new/speech_db/txt/example-2021-06-11.txt