[Hive] 테이블 생성(create table, view table, internal or external table 차이점, aws s3로 location 설정하는 법, 파일 포맷 설정하는 법)

기본 구조 

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

 

내부테이블 (Internal or Managed Table)

• 관리현 테이블이라고 하면 hive 속성(hive.metastore.warehouse.dir)에 default path(hdfs://user/hive/warehouse/~~)가 설정 되어있고 이곳에 테이블과 메타데이터가 생성 된다. default path는 변경 가능하다.
• 테이블을 삭제하면 파일과 메타데이터가 삭제 된다.

 

외부 테이블(External Table)

• 외부 테이블은 HDFS나 Location을 설정하여 완전 다른 저장소의 데이터를 테이블로 관리 할 수 있도록 스키마만 저장하여 데이터 웨어하우싱이 가능하게 해주는 테이블이다.
• 외부 저장 파일 시스템을 설정하는 개념으로 테이블을 삭제 하더라도 파일은 남아 있다.
• External과 Location이 있어야한다.
• Location은 S3, Blob storage 또는 원격 HDFS를 지정 할 수도 잇따.

 

Materialized View

• 집계 함수를 사용하여 쿼리 형식으로 테이블을 만들어 집계 내용을 반복적으로 작성하지 않고 테이블 조회 만으로 값을 볼 수 있게 해주는 테이블

속성

• comment - 테이블의 설명을 넣을 수 있음
• partitioned by - 하이브 쿼리를 수행 할 때 파일이 너무 많아지면 속도가 느려짐, 이를 방지하기 위해 partition을 나눌 수 있도록 구성하는 속성
• row format - 테이블의 데이터가 적재 될때 데이터를 어떤 형식으로 저장할지 지정하는 속성
• tblproperties- 테이블 속성을 정의 할 수 있음 특히 transaction이나 mr or tez 같은 속성을 넣을 수 있음
  • 'skip.header.line.count'='1’ - 해당 파일에 첫번째 row 삭제
  • ‘transaction’ = ‘true’ - transaction 활성화
• stored as - 테이블을 생성할 때 file format을 어떻게 지정 할지 정함 external table은 파일 형식이 자유로움, 하지만 internal table에 transaction 속성을 주게 된다면 파일 형식에 제약이 있음 orc로 지정 해야함
• select절을 이용하여 테이블 생성 가능 단 external table은 안된다.

stored as - File Format

| TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
| RCFILE      -- (Note: Available in Hive 0.6.0 and later)
| ORC         -- (Note: Available in Hive 0.11.0 and later)
| PARQUET     -- (Note: Available in Hive 0.13.0 and later)
| AVRO        -- (Note: Available in Hive 0.14.0 and later)
| JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

column 제약 조건

column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

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)

기본 명령어

Table 조회

hive> show tables;
OK
Time taken: 0.661 seconds

Create table - 테이블 생성

# Internal table 생성
CREATE TABLE [if not exists] patient_internal_table(
  patient_id INT,
  name STRING,
  age INT,
  address STRING
)
STORED AS ORC # ORC 포맷을 저장
LOCATION 's3a://{bucket_name}/{folder_path}' # aws s3로 location 설정하는 법 
TBLPROPERTIES ("transactional" = "true"); # internal table만 transaction 설정 가능

# External table 생성
CREATE EXTERNAL TABLE [if not exists] patient_external_table(
  patient_id INT,
  name STRING,
  age INT,
  address STRING
)
PARTITIONED BY (year string, month string, day string) # 파티션 날짜 별로 나누기
STORED AS parquet # parquet 파일로 생성
LOCATION 'hdfs://{folder_path}' # hdfs로 location설정

# materialized view table 생성 
CREATE MATERIALIZED VIEW aggregation_patient
AS
SELECT count(patient_id)
FROM patient_external_table
group by patient_id

테이블 상세 조회

DESCRIBE airline_delay; # view table은 되지 않습니다.

테이블 수정

# 이름 변경
ALTER TABLE [table_name] RENAME TO [table_name];
# external table 설정
ALTER TABLE 테이블명 SET TBLPROPERTIES('EXTERNAL'='TRUE');
ALTER TABLE 테이블명 SET TBLPROPERTIES('EXTERNAL'='FALSE');

 

 

 

 

참고 문헌 

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable

https://118k.tistory.com/349

https://excelsior-cjh.tistory.com/42