S-JIS[2011-08-10/2011-08-12] 変更履歴

HiveQL TABLE

HiveQL（HiveのSQLっぽい独自言語）のテーブルに関するメモ。

Hiveでは操作対象のファイル（基本的にはHDFS上のファイル）をテーブルと呼んで扱う。
「テーブル」と呼んでいても、データの実体はファイル。

CREATE

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] テーブル名
[(項目名 型 [COMMENT コメント], …)]
[COMMENT テーブルのコメント]
[PARTITIONED BY (項目名 型 [COMMENT コメント], …)]
[CLUSTERED BY (項目名, …) [STORED BY (項目名 [ASC|DESC], …)] INTO バケット数 BUCKETS]
[ROW FORMAT 行フォーマット]
[
   STORED AS ファイルフォーマット]
 | STORED BY 'クラス名' [WITH SERDEPROPERTIES (〜)]
]
[LOCATION パス]
[TBLPROPERTIES (プロパティー名=値, …)]
[AS SELECT文]

CREATE TABLE

HiveのテーブルはCREATE TABLEで作成する。

create table テーブル名 (
  項目名 型,
  …
);

HiveQLはSQL（RDBMS）に似せているためHive上の扱いは“テーブル”だが、実体は（HDFS上の）ファイル。
（Hadoopを単独環境で動かしている場合はローカルのファイル）

create文を実行した時点で、（HDFSあるいはローカルの）/user/hive/warehouseの下にディレクトリー（データ置場）が作られる。

「create table」で作成したテーブルは、dropを実行するとデータ置場のディレクトリーも削除される。

CREATE EXTERNAL TABLE

CREATE TABLEにEXTERNALを付けると、ディレクトリー（データ置場）を指定してテーブルを作成する。

create external table テーブル名 (
  項目名 型,
  …
)
location 'フルパス';

locationによる場所指定はディレクトリーであってファイルではないので、注意。
create文を実行した時点でディレクトリーが存在しなければ作られる。ディレクトリーが存在していれば何もしないが、それがファイルだったらエラーになる。
テーブルにデータを入れると、指定したディレクトリーの中にファイルが作られることになる。
後から手動でファイルを追加すると、データが追加された状態になる。（ファイルの中身がテーブル定義と一致しているかどうかは、selectしたときに（毎回）チェックされる）

「create external table」で作成したテーブルは、dropを実行してもデータ置場のディレクトリー・ファイルは削除されない。

行フォーマット

CREATEの「ROW FORMAT」で行フォーマットを指定する。
ファイルを読み込むときにこのフォーマットに従って項目値を分割する。

create table テーブル名
〜
ROW FORMAT DELIMITED
[ FIELDS TERMINATED BY ',']
[ COLLECTION ITEMS TERMINATED BY ':']
[ MAP KEYS TERMINATED BY '=']
[ LINES TERMINATED BY '\n']
;

または

ROW FORMAT SERDE 'SerDeクラス名' [WITH SERDEPROPERTIES(プロパティー名=値, …)]
構文 説明 例 ファイルの
データ例
FIELDS フィールド（項目）の区切り文字を指定する。
デフォルトは0x01。テキストにとっては区切り文字無しと同義。[/2011-08-11]
すなわち、データをロードすると一行分のデータが1つ目の項目に全て入り、
2つ目以降の項目はNULLになる。
→テキストファイルに出力する際の注意点		 create table f(
  f1 string,
  f2 string
)
row format delimited
fields terminated by ',';		 aa,bb
foo,bar
COLLECTION ITEMS コレクション（配列）の区切り文字を指定する。
デフォルトは区切り文字無し。すなわち、データをロードすると
1項目分のデータが全て配列の1個目の要素（全体として要素数が1）となる。		 create table c(
  a array<string>
)
row format delimited
collection items terminated by ':';		 aa:bb:cc:dd
foo:bar:zzz
　
MAP KEYS マップのキーと値の区切り文字を指定する。
デフォルトは区切り文字無し。すなわち、データをロードすると
1項目分のデータが全てキーとなり、値はNULLとなる。		 create table m(
  m map<string, string>
)
row format delimited
map keys terminated by '=';		 aa=zzz
bb=foo
cc=bar
LINES 行の区切り文字を指定する。
と言いつつ、Hive0.7.1でサポートされているのは「\n」のみらしい＾＾；		 create table L(
  f1 string
)
row format delimited
lines terminated by '\n';		 aa
bb
cc
SERDE SerDe（シリアライズ・デシリアライズ）のクラスを指定する。 create table s(
  f1 string
)
row format serde 'sample.MySerDe'
with serdeproperties('p1' = 'v1');		 　

ファイルフォーマット

CREATEの「STORED AS」でファイルフォーマットを指定する。
デフォルトはTEXTFILE。

create table テーブル名
〜
STORED AS フォーマット名;
フォーマット名 説明 備考（SerDe・InputFormat・OutputFormatクラス）
TEXTFILE データをテキストファイルとして扱う。
（OutputFormatがHadoopデフォルトのTextOutputFormatでないのは、
TextOutputFormatではキーとテキストを出力するからだろう。
Hiveでは行フォーマットに従って加工したテキストのみを出力する）
→テキストファイルに出力する際の注意点		 org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
org.apache.hadoop.mapred.TextInputFormat
org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
SEQUENCEFILE データをシーケンスファイル（HadoopのSequenceFile）として扱う。
シーケンスファイルは任意のキーと値のペアでデータを保持する形式だが、
Hiveでは特定のクラスのみに限定している。		 org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
org.apache.hadoop.mapred.SequenceFileInputFormat
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
RCFILE 　 org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe
org.apache.hadoop.hive.ql.io.RCFileInputFormat
org.apache.hadoop.hive.ql.io.RCFileOutputFormat
INPUTFORMAT クラス
OUTPUTFORMAT クラス		 InputFormat・OutputFormatのクラスを指定する。 　

Hiveのテキストファイルの扱い方

ファイルフォーマットにTEXTFILEを指定した場合、実際に保存されるファイルはベタのテキストファイルとなる。[2011-08-11]
テーブルに書き込んだデータは行フォーマットに従って加工してファイルへ保存され、
読み込むときはその都度ファイルの内容を行フォーマットに従って解釈（項目毎に分割）する。

したがって、区切り文字に指定されている文字がデータに含まれていると、書き込み時と読み込み時で結果が異なることになる。

変な例
例えば行フォーマットでフィールドをカンマ区切りにしたとする。 
create table f (f1 string, f2 string)
row format delimited fields terminated by ',';
 　
ここで右記のようなデータ書き込みを行うと 
insert overwrite table f
select 'aa,bb', 'cc' from dual;
 注）dualという1行しか入っていないテーブルがある前提
ファイルの中は右のような状態になる。 
aa,bb,cc
 　
読み込むときはカンマ区切りとして解釈するので、
第1項目がaa、第2項目がbbで残りは捨てられる。		 
hive> select * from f;
OK
aa      bb
 　
もっと言えば、行区切りは改行コード「\n」なので
それを挿入データに指定すると
ファイルの中にも改行コードが出力され、
読み込む際には複数レコード存在している状態になる。		 
insert overwrite table f
select 'aa,bb', '\ncc,dd\nee,ff' from dual;
↓ファイルの中身
aa,bb,
cc,dd
ee,ff
hive> select * from f;
OK
aa      bb
cc      dd
ee      ff
 　

Hiveのシーケンスファイルの構造

シーケンスファイル（SequenceFile）という形式自体はHadoopで定義されている。（キーと値をそれぞれ任意のWritableで保持する形式）
だが、HiveのSEQUENCEFILEではキーは無視される（どんな型が入っていてもよい）。そして、値もText（またはBytesWritable）のみとなる。

ファイル形式でSEQUENCEFILEを指定した場合、キーは、読み込み時は無視され、書き込み時はBytesWritableの定数が出力される。
そして値は各項目を行フォーマットに従って“1つの文字列”に整形し、それが“1つのText”として出力され、読み込まれる。
（そのテキスト内のフィールド（項目）の区切りはデフォルトでは0x01、「FIELDS TERMINATED BY」が指定された場合はその文字で区切られる）

結局、TEXTFILEを指定した場合のテキスト（文字列）をシーケンスファイルの値として入出力している、という感じ。

→独自のWritableを扱いたい場合はSerDeを作成する

CREATE TABLE LIKE

別のテーブル定義をコピーして新しい（空の）テーブルを作成する。

create          table テーブル名 like 元テーブル名;
create external table テーブル名 like 元テーブル名 location 'フルパス';

CREATE TABLE AS SELECT

別のテーブル（の定義とデータ）をコピーして新しいテーブルを作成する。

create table テーブル名 as select 〜 from 元テーブル名 where 条件;

※コピーされる定義は項目名・データ型くらいで、その他の情報（SerDe等）は引き継がれない。[2011-08-12]

SHOW TABLES

SHOW TABLESでテーブル一覧を表示する。

show tables;
show tables '検索条件';

正規表現でテーブル名の検索条件を指定することが出来る。
正規表現なので「.*」が任意の文字列という意味になり、例えば「t.*」はtから始まるテーブルということになる。

DESCRIBE

DESCRIBEでテーブルの情報を表示する。DESCRIBEは省略して「DESC」を使用可能。

desc テーブル名;
desc extended テーブル名;
desc formatted テーブル名;

descのみだと、項目（と型）の一覧を表示するのみ。
extendedを付けると、詳細情報が表示される。
formattedを付けると、詳細情報が整形された分かりやすい状態で表示される。

「テーブル名.項目名」で項目の情報が表示される。

RENAME

ALTER TABLE RENAMEでテーブル名を変更できる。

alter table テーブル名 rename to 新テーブル名;

DROP TABLE

DROP TABLEでテーブルを削除する。

drop table テーブル名;
drop table if exists テーブル名;

CREATE TABLEで作ったテーブルは、dropするとデータ置場のディレクトリーも削除される（データファイルが消える）。
CREATE EXTERNAL TABLEで作ったテーブルは、dropしてもデータ置場のディレクトリー（およびファイル）は削除されずそのまま残る。

HiveQLへ戻る / Hive目次へ戻る / 技術メモへ戻る
メールの送信先：ひしだま