이번 글에서는 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]

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

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

Hive는 관리 혹은 내부 테이블 (managed table)과 외부 테이블 (external table) 두 가지 유형의 테이블을 나누어서 사용합니다.

내부 테이블 (Internal or Managed Table)

관리형 테이블을 생성하면 파일이 기본 위치인 /user/hive/warehouse/databasename.db/tablename/ 에 저장됩니다. 외부 테이블과는 다르게, 관리 테이블 또는 파티션이 삭제 (drop)되면 해당 테이블 또는 파티션과 연관된 데이터 및 메타 데이터가 삭제됩니다. 

CREATE TABLE speech_db (
    path_wav STRING,
    uttr STRING,
    sex INT
)
COMMENT 'table comment';

외부 테이블 (External Table)

외부 테이블(External Table)은 이미 HDFS에 존재하는 원본 데이터를 기반으로 테이블을 만들기 때문에 스키마만 정해주면 됩니다. 그래서 파일와 스키마를 따로 관리하기 좋습니다. 파일이 HDFS 상에 이미 있을 때 외부 테이블을 사용하고, 테이블이 삭제 되더라도 파일은 남아 있습니다.

CREATE EXTERNAL TABLE speech_db(
    path_wav STRING,
    uttr STRING,
    sex INT
    )
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/file/path/some/where';

Hive는 명령이 많기 때문에 대부분의 기존 SQL 명령을 지원합니다. 가장 일반적으로 사용되는 Hive DDL (Data Definition Language, 데이터 정의 언어) 명령을 예제와 함께 알아 보겠습니다.

Beeline을 사용하여 Hive에 연결 된 상태에서 시작해야 한다.

Hive DDL database Commands

여러 Hive DDL 명령 중에서 가장 일반적으로 사용되는 DDL 명령에 대해 설명합니다. DDL 명령은 데이터베이스, 테이블을 생성(Create)하고, 테이블의 구조를 수정(modify)하고, 데이터베이스와 테이블을 삭제(drop)하는 데 사용됩니다.

Create Database

CREATE DATABASE IF NOT EXISTS speech_db;

데이터베이스가 이미 존재하는 경우 데이터베이스 db_name이 이미 존재한다는 오류가 표시됩니다. 데이터베이스를 생성하기 전에 이미 존재하는지 확인하려면 IF NOT EXISTS 절을 사용하십시오. LOCATION 절을 사용하여 데이터베이스의 위치를 변경할 수 있습니다.

기본적으로 Hive는 데이터베이스를웨어 하우스 위치 / user / hive / warehouse에 저장합니다. 아래 명령은 데이터베이스의 위치를 변경합니다.

CREATE DATABASE temp LOCATION '/user/hive/warehouse';

Show Databases

0: jdbc:hive2://> SHOW DATABASES;
OK
+-----------------------+
|     database_name     |
+-----------------------+
| aa                    |
| speech_db             |
+-----------------------+
51 rows selected (0.117 seconds)

Use Database

0: jdbc:hive2://>USE speech_db;

Describe Database

0: jdbc:hive2://>DESCRIBE DATABASE speech_db;
0: jdbc:hive2://>DESCRIBE SCHEMA speech_db; 
0: jdbc:hive2://>DESCRIBE DATABASE EXTENDED speech_db;
0: jdbc:hive2://>DESCRIBE SCHEMA EXTENDED speech_db;
0: jdbc:hive2://>DESCRIBE DATABASE speech_db;
+----------+----------+---------------------------------------------+-------------+-------------+-------------+
| db_name  | comment  |                  location                   | owner_name  | owner_type  | parameters  |
+----------+----------+---------------------------------------------+-------------+-------------+-------------+
| speech_db|          | hdfs://hadoop/user/speech_db.db             | hdfs        | USER        |             |
+----------+----------+---------------------------------------------+-------------+-------------+-------------+
1 row selected (0.07 seconds)
0: jdbc:hive2://>

Drop Database

jdbc:hive2://>DROP DATABASE speech_db;
jdbc:hive2://>DROP DATABASE speech_db CASCADE;
jdbc:hive2://>DROP DATABASE speech_db RESTRICT;

Hive DDL Table Commands

Hive는 Managed, External, Temporary and Transactional 테이블과 같은 다양한 유형의 테이블을 지원합니다. 여기서 예제를 간단하게 만들기 위해 Hive Managed 테이블을 생성하겠습니다.

Hive Managed 테이블은 Hive가 HDFS의 메타 데이터 및 실제 테이블 데이터 / 파일을 소유하고 관리하는 내부 테이블입니다.

Create Table

CREATE TABLE IF NOT EXISTS speech_db.user (
 id int,
 name string,
 age int,
 gender string )
 COMMENT 'Employee Table'
 ROW FORMAT DELIMITED
 FIELDS TERMINATED BY ',';

Show Tables

SHOW TABLES; // This shows all tables from the current database
SHOW TABLES in emp; // This shows all tables in the emp database
+--------------------+
|      tab_name      |
+--------------------+
| user.              |
+--------------------+
1 rows selected (0.067 seconds)
0: jdbc:hive2://>

Describe Table

DESCRIBE FORMATTED user;
DESCRIBE EXTENDED user;

Truncate Table

Truncate table은 테이블을 자르는 데 사용되며 이는 테이블의 모든 내용과 테이블의 구조를 삭제함을 의미합니다.

TRUNCATE TABLE speech_db;

Alter Table

ALTER TABLE user RENAME TO user2;

Drop Table

DROP TABLE user2;
DROP TABLE speech_db.user2;
DROP TABLE IF EXISTS user2 PURGE;

Hive는 Hadoop 용 데이터웨어 하우스 데이터베이스이며 모든 데이터베이스 및 테이블 데이터 파일은 기본적으로 /user/hive/warehouse HDFS 위치에 저장되며, 기타 Hadoop 호환 파일 시스템의 사용자 지정 위치에 Hive 데이터웨어 하우스 파일을 저장할 수도 있습니다.

Hive를 사용할 때, 2 개의 서로 다른 데이터 저장소에 대해 알아야합니다.

• Hive Metastore

• Hive Data warehouse Location (실제 테이블 데이터가 저장된 위치)

Hive Metastore

Hive Metastore는 데이터베이스 및 테이블에 대한 Meta 데이터를 저장하는 데 사용되며 기본적으로 Derby 데이터베이스를 사용합니다. 이것을 MySQL 및 Postgress 등과 같은 RDBMS 데이터베이스로 변경할 수 있습니다. 기본적으로 Metastore 데이터베이스 이름은 metastore_db입니다.

Hive Table Location (Hive Data warehouse)

Hive는 기본적 /user/hive/warehouse으로 HDFS 파일 시스템의 위치에 테이블 파일을 저장 합니다. Hive를 사용하기 전에 HDFS에서 이러한 디렉터리를 만들어야합니다.

이 위치에서 사용자가 만든 모든 데이터베이스의 디렉터리와 사용하는 테이블 이름이있는 하위 디렉터리를 찾을 수 있습니다.

1. 속성에서 스토리지 경로 가져 오기

위치가 다른 경우 hive.metastore.warehouse.dir속성 에서 경로를 가져올 수 있으며 Hive Beeline CLI터미널 에서 다음 명령을 실행하여 가져올 수 있습니다 .

set hive.metastore.warehouse.dir;
hive.metastore.warehouse.dir=/user/hive/warehouse

2. 구성 파일에서 스토리지 경로 가져 오기

$HIVE_HOME/conf/hive-site.xml파일 에서 hive.metastore.warehouse.dir 속성 값을 찾아 경로를 가져올 수도 있습니다.

3. Hive 명령 사용해서 경로 가져 오기

아래 명령을 실행하여 테이블의 하이브 스토리지 경로를 가져올 수도 있습니다.

hive -S -e "DESCRIBE FORMATTED table_name;" | grep 'Location' | awk '{ print $NF }'

테이블 위치는 SHOW CREATE TABLE하이브 터미널에서 명령을 실행하여 가져올 수도 있습니다 .

SHOW CREATE TABLE table_name;
(or)
DESCRIBE FORMATTED table_name;

4. Hive 테이블 파티션 위치

Hive에 파티션 된 테이블 이 있고 각 파티션 파일의 위치가 다른 경우 아래 명령을 사용하여 HDFS에서 각 파티션 파일 위치를 가져올 수 있습니다 .

DESCRIBE FORMATTED db_name.table_name PARTITION (name=value)

Reference

[1] https://cwiki.apache.org/confluence/display/Hive/Home#Home-HiveDocumentation

하이브는 다음과 같은 기본 데이터 유형 (primitive type) 과 복잡한 데이터 유형 (complex type)을 지원합니다. 이러한 데이터 유형은 테이블의 열(columns)과 연관이 있습니다.

data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)

Hive Misc Types (Boolean & Binary)

Hive Numeric Types

Hive String Types

Hive Date & Time Types

Hive Complex Types (Array, Map, Struct)

• Spark와 마찬가지로 Hive는 Array, Map, Struct 및 union을 포함하는 복잡한 데이터 유형도 지원합니다.
  • Array는 list of elements 을 저장하는 데 사용됩니다.
  • Map은 key / value pair를 저장하는 데 사용됩니다.
  • Struct는 부모 및 자식 연결을위한 것 입니다.