【Hive】Hive / HiveQL ~ テーブル作成 ~

■ はじめに

https://dk521123.hatenablog.com/entry/2020/02/25/231235
https://dk521123.hatenablog.com/entry/2020/06/02/183823

の続き。

長くなったので、テーブル作成だけで分冊。

目次

【1】テーブル作成
 1)CREATE TABLE:内部テーブル作成
 2)CREATE EXTERNAL TABLE:外部テーブル作成
【2】Partition 機能
【3】テーブル・オプション
 1)行の区切りフォーマット
 2)ファイルフォーマットを指定
 3)ヘッダー行を飛ばす
【4】その他
 1)SHOW CREATE TABLE [テーブル名]

【1】テーブル作成

* テーブルは、以下の2種類ある
 ~~~~~
 1)内部テーブル
 2)外部テーブル
 ~~~~~

1)CREATE TABLE:内部テーブル作成

内部テーブルを作成する
 ⇒ 実体は、HDFS(Hadoop Distributed File System)上のファイル
 ⇒ /user/hive/warehouseの下に、データ置き場用のディレクトリが作られる

構文

-- テーブル作成
CREATE TABLE <テーブル名> (
  <項目名> <データ型>,
  …
)
[オプション項目] ...;

-- あったら、作成
CREATE TABLE IF NOT EXISTS  <テーブル名> (
...

データ型

以下の公式サイトを参照。

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

使用上の注意

* DROP TABLE を実行すると、データ置場のディレクトリも削除される

サンプル

-- SELECT文 から作成することも可能。
CREATE TABLE hello_world_clone
AS
SELECT
  *
FROM
  hello_world
;

2)CREATE EXTERNAL TABLE:外部テーブル作成

* 外部テーブル(※)を作成する
 => ディレクトリパス(データ置場)を指定してテーブルを作成する
 => データ自体は、外部ファイル

※ 外部ファイル について
ローカルファイルシステム又はAmazon S3上に置かれているデータファイルを
まるで内部テーブルのように扱える仕組み

https://qiita.com/holy_road_ss/items/d55f2d539bf146c2e38d
https://www.atmarkit.co.jp/ait/articles/1701/01/news022_3.html
構文

-- データ置場を指定してテーブルを作成する
CREATE EXTERNAL TABLE <テーブル名> (
  項目名 型,
  …
)
-- パスであってファイル名含むフルパスではない
LOCATION 'ディレクトリパス';

-- あったら作る
CREATE EXTERNAL TABLE IF NOT EXISTS <テーブル名>(
  …

サンプル

CREATE EXTERNAL TABLE hello (
  name STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION "s3://your-s3-buket/hello";

-- AWS 上での例
CREATE EXTERNAL TABLE person (
  id BIGINT,
  name STRING
)
PARTITIONED BY (created_date INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
LOCATION 's3://your-s3-buket/demo/';
-- s3://your-s3-buket/demo/created_date=20200602/xxxx.csv

使用上の注意

* LOCATION は、パスであって、ファイル名含むフルパスではない
* DROP TABLE を実行しても、データ置場のディレクトリも削除されない

【2】Partition 機能

以下の関連記事を参照のこと。

https://dk521123.hatenablog.com/entry/2020/07/16/224332

【3】テーブル・オプション

1)行の区切りフォーマット

サンプル

CREATE TABLE xxx (
  id BIGINT
)
ROW FORMAT DELIMITED
  -- 項目の区切り文字が「,」
  FIELDS TERMINATED BY ","
  -- 行の区切り文字が「\n」
  LINES TERMINATED BY '\n';

2)ファイルフォーマットを指定

サンプル

CREATE TABLE xxx (
  id BIGINT
)
-- PARQUET形式として指定
-- (列志向・カラムナフォーマット: Parquet, ORC / TEXTFILE etc)
STORED AS PARQUET
-- 圧縮形式:snappy
TBLPROPERTIES("parquet.compression"="SNAPPY")
;

https://qiita.com/yskazuma/items/db543d45d32013cfcbb9

3)ヘッダー行を飛ばす

CREATE TABLE xxx (
  id BIGINT
)
-- TBLPROPERTIES = table properties
TBLPROPERTIES ('skip.header.line.count'='1');

https://qiita.com/Takemura-T/items/5f683753063d7bc52cc9

【4】その他

1)SHOW CREATE TABLE [テーブル名]

* 既存のテーブル定義を出力してくれる

サンプル

hive> SHOW CREATE TABLE sample_table;

OK
CREATE TABLE sample_table
  id string,
 ....

関連記事

Hive / HiveQL ~ 基礎知識編 ~
https://dk521123.hatenablog.com/entry/2019/11/25/235219
Hive / HiveQL ~ 入門編 ~
https://dk521123.hatenablog.com/entry/2020/02/25/231235
Hive / HiveQL ~ 基本編 ~
https://dk521123.hatenablog.com/entry/2020/06/02/183823
Hive / HiveQL ~ パーティション / 基本編 ~
https://dk521123.hatenablog.com/entry/2020/07/16/224332
Hive / HiveQL ~ データ圧縮あれこれ ~
https://dk521123.hatenablog.com/entry/2021/08/06/172502
Hive / HiveQL ~ HiveQL関数編 ~
https://dk521123.hatenablog.com/entry/2020/03/10/224640
Hive / HiveQL ~ 設定プロパティ ~
https://dk521123.hatenablog.com/entry/2020/09/19/152136
Hive / HiveQL ~ あれこれ編 ~
https://dk521123.hatenablog.com/entry/2020/03/04/225943
hive で固定値を挿入するには
https://dk521123.hatenablog.com/entry/2020/09/22/000000
Hive に関するトラブルシューティング
https://dk521123.hatenablog.com/entry/2020/11/20/174039
Hive / Partition に関するトラブルシューティング
https://dk521123.hatenablog.com/entry/2020/05/28/175428
Apache Hadoop ~ 環境設定 / Linux編 ~
https://dk521123.hatenablog.com/entry/2020/01/01/000000