Zenn
🐥

How to Create Database & Table in Hive

2021/11/30に公開
メモ
hive
tech

Hive での databse, table 作成の書き方のまとめ。

Database

Database の作成に関する official document はこちら。

Create

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];
  • REMOTE (Hive 4.0.0 or later)
    Hive の Data connector で使える設定 (to be checked)
  • LOCATION/ MANAGEDLOCATION　(Hive 4.0.0 or later)
    どちらも database 内の table data を保存する default directory を指定するオプションだが、LOCATION は external table の、MANAGEDLOCATION は managed table のデータを保存する場所を指定する[1]。MANAGEDLOCATION は hive.metastore.warehouse.dir 以下を指定することが推奨されている
  • WITH DBPROPERTIES (Hive 0.7 or later)
    ユーザや所属などといった meta data を database に自由に付与できる

Example

CREATE DATABASE IF NOT EXISTS sample_database MANAGEDLOCATION '/user/user_name/warehouse';

Delete

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
  • RESTRICT|CASCADE (after Hive 0.8)
    RESTRICT (default) はデータベースが空でない場合は削除しないオプションで、CASCADE はデータベースが空でなくても削除するオプション

Table

Table の作成に関する official document はこちら。

Create

Column の使用可能な変数の型は以下の official document を参照。
なお、Hive では primary key などの行の constraint は Hive 3.0.0 以降からのみなので注意[2]。

テーブル定義を自分で作成する場合

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)

  • TEMPORARY
    Session 終了と共に削除される、current session のみで使用可能な table が作成される。

  • PARTITIONED BY
    指定された partition 毎に異なる directory を作成し分割して保存・参照できる。さらに CLUSTERED BY と SORTED BY を用いて bucket 化して sort しておくと query が早くなることもある。

  • ROW FORMAT
    Hive では規定の format (or custom format) によりデータの読み込み・書き込みが行われる。ROW FORMAT ではその際の Serialization and De-serialization (SerDe) を指定できる。

    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, ...)]

  • STORAGE FORMAT
    Hive に保存する table data の format を指定でき、default では TEXTFILE となっており、最近の defacto standard は ORC らしい[3]。

    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)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

  • LOCATION
    主に external table で data を保存する場所を指定する際に用いられる。

  • TBLPROPERTIES
    作成する table の設定を記述する。例えば、

    • "immutable"="true|false" : INSERT INTO による update を許すかどうか
    • "transactional"="ture|false"
      などがある。

  • AS select statement
    AS 以下に SELECT 文を繋げることで、既存の table から直接 table を作成することができる。 この場合、テーブル定義は書いても書かなくてもどちらでも良い。

Example

CREATE TABLE IF NOT EXISTS db_name.table_name (
    col_1 INT,
    col_2 STRING
)
COMMENT 'example table'
PARTITIONED BY (`date` INT, hour TINYINT)
CLUSTERED BY (col_1) SORTED BY (col_1 DESC) INTO 3 BUCKETS
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = "\t",
   "quoteChar"     = "'",
   "escapeChar"    = "\\"
)  
STORED AS TEXTFILE
TBLPROPERTIES ("immutable"="false")
-- continue SELECT statement if insert from existing tables
-- AS
-- SELECT ...
;

既存のテーブルの定義を用いる場合

LIKE 句を用いることで、既存の table 定義を用いて新しい table を作成できる。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

Delete

DROP TABLE [IF EXISTS] table_name [PURGE];     -- (Note: PURGE available in Hive 0.14.0 and later)
  • PURGE (Hive 0.14.0 or later)
    Default では削除されたデータは .Trash/Current に移動されるが、PURGE を指定した場合、データは直接削除される。
脚注

  1. Hive で通常利用される table は managed table である。一方、 external table は関連した meta data Hive に持たせることで外部データを連携する方法で、例えば DROP した場合でもデータ自体は消去されない。 (Comparison between Hive Internal Tables vs External Tables) ↩︎

  2. https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-Constraints ↩︎

  3. 使い分けは Hadoop File Formats, when and what to use? ↩︎

Discussion