5-2) hive 테이블 1

1. 테이블

• 하이브에서 테이블은 HDFS 상에 저장된 파일과 디렉토리 구조에 대한 메타 정보
• 실제 저장된 파일의 구조에 대한 정보와 저장 위치, 입력 포맷, 출력 포맷, 파티션 정보, 프로퍼티에 대한 정보 등 다양한 정보를 가지고 있음

1) 테이블 생성

• CREATE 명령을 이용하여 테이블 생성

-- 테이블 생성 쿼리 
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 [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)

-- LIKE 를 이용하여 기 존재하는 테이블과 동일하게 테이블 생성 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

-- 테이블의 데이터를 구분하는 기준 포맷 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

-- 테이블의 데이터를 저장하는 파일의 타입 
file_format:
  : SEQUENCEFILE
  | 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)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

-- 테이블의 제약조건 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

• 저장 위치
  • LOCATION
    • 테이블의 저장위치는 테이블에 쓰는 데이터의 저장위치
    • 사용자가 입력하지 않으면 데이터베이스의 저장위치 아래 테이블 이름의 폴더로 기본 생성
• 테이블 타입
  • MANAGED
    • 테이블 생성 시 옵션을 따로 주지 않으면 MANAGED 테이블 생성
    • 세션이 종료되어도 테이블의 데이터와 파일은 유지
    • 테이블을 DROP하면 파일도 함께 삭제
  • EXTERNAL
    • MANAGED 테이블과는 파일 삭제 정책을 제외하고 나머지는 동일
    • EXTERNAL 테이블은 DROP하면 파일은 그대로 유지
    • 사용자의 실수로 인한 파일 삭제를 방지하기 위해 EXTERNAL 테이블로 관리하는 것이 좋음
  • TEMPORARY
    • 현재 세션에서만 사용하는 테이블 생성
    • 현재 세션이 종료되면 제거되기 때문에 임시 테이블 생성에 사용
• 파티션
  • PARTITIONED BY
    • 파티션은 폴더 구조로 데이터를 분할하여 저장
    • PARTITIONED BY에 지정한 컬럼의 정보를 이용하여 폴더 단위로 데이터 생성
      • 하이브는 폴더 단위로 데이터를 읽기 때문에 파티션이 없다면 테이블의 모든 데이터를 읽음
      • 이를 방지하기 위해 파티션을 이용하여 데이터 조회 속도를 높일 수 있음

-- 일자를 기준으로 파티션 생성 
CREATE TABLE tbl(
  col1 STRING
) PARTITIONED BY (yymmdd STRING);

-- 데이터 저장 구조 
hdfs://tbl/yymmddval=20180501/0000_0
hdfs://tbl/yymmddval=20180502/0000_0
hdfs://tbl/yymmddval=20180503/0000_0

-- 조회
SELECT yymmdd, count(1)
  FROM tbl
 WHERE yymmdd between '20180501' and '20180503'
 GROUP BY yymmdd

• 버켓팅
  • 버켓팅은 CLUSTERED BY를 이용하여 설정
  • 일반적으로 SORTED BY와 함께 사용
  • 설정한 버켓의 개수에 지정한 컬럼의 데이터를 해쉬처리하여 저장
  • 버켓팅한 테이블은 조인시에 속도가 빨라짐

-- col2 를 기준으로 버켓팅 하여 20개의 파일에 저장 
CREATE TABLE tbl(
  col1 STRING,
  col2 STRING
) CLUSTERED BY col2 SORTED BY col2  INTO 20 BUCKETS

• 스큐
  • 스큐는 값을 분리된 파일에 저장하여 특정한 값이 자주 등장할 때 속도를 높이는 방법
  • SKEWED BY

-- col1의 col_value 값을 스큐로 저장  
CREATE TABLE tbl (
  col1 STRING,
  col2 STRING
) SKEWED BY (col1) on ('col_value');

• 테이블 포맷(Row Format)
  • 데이터를 컬럼 단위로 구분하는 구분자(delimeter)와 데이터를 해석하는 방법을 지정하는 서데(SerDe)를 지정

-- 하이브의 기본 구분자를 이용한 테이블 생성 
--   입력 데이터
$ cat sample.txt 
a,val1^val2^val3,key1:val1^key2:val2

-- ROW FORMAT을 이용한 테이블 생성 
CREATE TABLE tbl (
 col1 STRING,
 col2 ARRAY<STRING>, 
 col3 MAP<STRING, STRING>
) ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ','
   COLLECTION ITEMS TERMINATED BY '^'
   MAP KEYS TERMINATED BY ':';

-- 데이터 로드 
LOAD DATA LOCAL INPATH './sample.txt' INTO TABLE tbl;

-- 데이터 조회, 구분자에 따라 데이터가 구분 됨 
hive> select * from tbl;
OK
a   ["val1","val2","val3"]  {"key1":"val1","key2":"val2"}

-- 지정가능한 구분자 
  FIELDS TERMINATED BY '\t'            -- 칼럼을 구분하는 기준
  COLLECTION ITEMS TERMINATED BY ','   -- 리스트를 구분하는 기준
  MAP KEYS TERMINATED BY '='           -- 맵데이터의 키와 밸류를 구분하는 기준
  LINES TERMINATED BY '\n'             -- 로(row)를 구분하는 기준
  ESCAPED BY '\\'                      -- 값을 입력하지 않음
  NULL DEFINED AS 'null'               -- null 값을 표현(0.13 버전에서 추가)

-- RegEx 서데 
-- 127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326
CREATE TABLE apachelog (
  host      STRING,
  identity  STRING,
  user      STRING,
  time      STRING,
  request   STRING,
  status    STRING,
  size      STRING,
  referer   STRING,
  agent     STRING )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "input.regex" = "([^]*) ([^]*) ([^]*) (-|\\[^\\]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?"
);

-- JSON 서데 
CREATE TABLE my_table(
  a string, 
  b bigint 
) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;

-- CSV 서데 
CREATE TABLE my_table(
  a string, 
  b string
) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = "\t",
   "quoteChar"     = "'",
   "escapeChar"    = "\\"
)  
STORED AS TEXTFILE;

• 저장 포맷
  • STORED AS를 통해 데이터를 저장하는 파일 포맷을 지정

-- 저장 포맷을 ORC로 설정하고, ORC 관련 설정정보 전달 
CREATE TABLE tbl (
  col1 STRING
) STORED AS ORC 
TBLPROPERTIES ("orc.compress"="SNAPPY");

-- INPUTFORMAT, OUTPUTFORMAT을 따로 지정하는 것도 가능 
CREATE TABLE tbl1 (
  col1 STRING 
) STORED AS INPUTFORMAT  "com.hadoop.mapred.DeprecatedLzoTextInputFormat"
            OUTPUTFORMAT "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat";

 

2) 테이블 수정

• ALTER 명령을 통해 테이블 컬럼 정보를 수정
• CHANGE 명령을 통해 컬럼의 타입을 변경
• REPLACE 명령을 통해 모든 컬럼을 삭제 후, 새로 생성

-- tbl 테이블의 칼럼을 모두 삭제 후 x, y, z 칼럼으로 생성
ALTER TABLE tbl REPLACE COLUMNS (x INT COMMENT 'this is column x', y INT COMMENT 'this is column y', z INT COMMENT 'this is column z');

 

2. 데이터 타입

• 하이브에서 지원하는 데이터 타입

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)

CREATE TABLE tbl (
  col1 INT,
  col2 STRING,
  col3 ARRAY<STRING>,
  col4 MAP<STRING, STRING>,
  col5 STRUCT<age:INT, name:STRING>,
  col6 UNIONTYPE<int, double, array<string>, struct<a:int,b:string>>
);