HueでCSVデータからHiveのテーブルを作る

4日目です。

本日はHadoopアドベントカレンダー2013立候補がいないようなので、あえて書く@choplinさんが今 (22:30)書いて下さっているとのこと。凄い!ありがとうございます!
#決して無理強いはしてないはず、、、、、と信じたい(汗

本日のお題はHueとHiveについてです。

Hiveでテーブルを作成してデータをロードする場合には、1) CREATE TABLEを使用してテーブルを作成し、2) LOAD DATA INPATHなどを使ってデータをロードする、というような手順を踏みます。あるいは、既にデータがHDFS上にある場合、外部テーブルを利用することもできます。

テーブルを作成してデータをロードする例

CREATE TABLE a (a INT, b INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
LOAD DATA LOCAL INPATH 'a.txt' INTO TABLE a;

外部テーブルの例

CREATE EXTERNAL TABLE b (a INT, b INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/user/training/test.csv';

ロードしようとしているデータがCSV形式のファイルの場合、さらには、データの先頭行がヘッダになっている場合、Hue3.5の新しい機能が役立つかもしれません。

Hue 3.5を使用したCSVデータからテーブルの作成

Hue 3.5のビルド方法は過去のブログを参照して下さい。以下のgethueのブログにあるように、Hue 3.5では、CSVファイルからHiveのテーブルを簡単に作成することができます。

gethue-jp: Hadoopチュートリアル:Hiveテーブルの作成とクォートされたCSVデータのロード

このブログで公開されているビデオは英語のままなので、あらためて手順を書いてみます。また、日本語が含まれているファイルがどうなるのかを試してみます。

Metastore Managerでテーブルを作成する

HueのMetastore Managerは、Hiveのメタストアを管理する機能を提供します。まず、gethueのブログに従い、FDICからCSVデータをダウンロードします。

fdic続いてダウンロードしたファイルから新しいテーブルを作成します。(以降、全ての画像はクリックで拡大するはず)

hue_metastore図1. MetaStore Manager

テーブル名はbanksにし、入力ファイルの右側のボタンを押して入力ファイルを選択します。

Hue_meta_create1図2 テーブル名の指定とファイルの選択

ファイル名の選択画面はこのようになります。HDFSにアップロードする必要があるので、「ファイルをアップロード」ボタンを押し、ファイルを選択します。

Hue_meta_create2図3 ファイルの選択

ファイルが選択できました。Nextボタンを押して進みます。

続いて区切り文字を選択します。デフォルトはカンマのようです。無事にデータを参照できているようです。

Hue_meta_create4図4 区切り文字の選択

このデータの先頭にはヘッダ行がありました。以下のようにうまく認識されているようです。必要に応じてこの画面で調整します。(今回は特に変更しません)

Hue_meta_create7図5 列の定義

テーブルを作成ボタンを押して、テーブルを作りましょう!

はい、できました(笑。作成したテーブルの列、データのサンプリング、プロパティを見てみます。

hue_meta_banks図6 banksテーブルの列

hue_meta_banks_sample図7 banksテーブルのサンプル

hue_meta_banks_properies図8 banksテーブルのプロパティ

もちろん、HiveやImpalaでクエリを実行することもできます。今回はBeeswaxでHiveのクエリを実行してみます。

hue_hive_banks_query図9 Hiveのクエリを実行

hue_hive_banks_query_result図10 Hiveのクエリの結果

まずはうまくいったようです。

日本語データはどうか?

では、日本語はどうでしょうか?

日本郵便株式会社のホームページには、住所と郵便番号のデータがCSV形式で公開されています。(件数はそれほど多くありませんが)このデータを使ってみましょう。

http://www.post.japanpost.jp/zipcode/download.html

japanpost利用したデータは、「読み仮名データの促音・拗音を小書きで表記するもの」の全国一括データです。zipファイルをダウンロードして展開します。

[training@huetest Downloads]$ unzip ken_all.zip
Archive:  ken_all.zip
inflating: KEN_ALL.CSV
[training@huetest Downloads]$

メタストアマネージャーから新しいテーブルの作成を選び、展開した KEN_ALL.CSVを選び、HDFSにアップロードして、、、次へ!

hue_metastore_postal2図11 日本語データのロード

hue_metastore_postal_bad図12 日本語の文字化けが、、、

うぉ、失敗しました。。。

そういえば以前も同じ問題に遭遇したなぁ。この郵便番号データはSJISで提供されています。従って、アップロード前にUTF-8に変換する必要があるんですねぇ。今回はiconvを使って変換します。

[training@huetest Downloads]$ iconv -f sjis -t utf8 KEN_ALL.CSV > KEN_ALL_UTF8.CSV
[training@huetest Downloads]$ head KEN_ALL_UTF8.CSV
01101,"060  ","0600000","ホッカイドウ","サッポロシチュウオウク","イカニケイサイガナイバアイ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0<br />01101,"064  ","0640941","ホッカイドウ","サッポロシチュウオウク","アサヒガオカ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0<br />01101,"060  ","0600041","ホッカイドウ","サッポロシチュウオウク","オオドオリヒガシ","北海道","札幌市中央区","大通東",0,0,1,0,0,0<br />01101,"060  ","0600042","ホッカイドウ","サッポロシチュウオウク","オオドオリニシ(1-19チョウメ)","北海道","札幌市中央区","大通西(1〜19丁目)",1,0,1,0,0,0<br />01101,"064  ","0640820","ホッカイドウ","サッポロシチュウオウク","オオドオリニシ(20-28チョウメ)","北海道","札幌市中央区","大通西(20〜28丁目)",1,0,1,0,0,0<br />01101,"060  ","0600031","ホッカイドウ","サッポロシチュウオウク","キタ1ジョウヒガシ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0<br />01101,"060  ","0600001","ホッカイドウ","サッポロシチュウオウク","キタ1ジョウニシ(1-19チョウメ)","北海道","札幌市中央区","北一条西(1〜19丁目)",1,0,1,0,0,0<br />01101,"064  ","0640821","ホッカイドウ","サッポロシチュウオウク","キタ1ジョウニシ(20-28チョウメ)","北海道","札幌市中央区","北一条西(20〜28丁目)",1,0,1,0,0,0<br />01101,"060  ","0600032","ホッカイドウ","サッポロシチュウオウク","キタ2ジョウヒガシ","北海道","札幌市中央区","北二条東",0,0,1,0,0,0<br />01101,"060  ","0600002","ホッカイドウ","サッポロシチュウオウク","キタ2ジョウニシ(1-19チョウメ)","北海道","札幌市中央区","北二条西(1〜19丁目)",1,0,1,0,0,0
[training@huetest Downloads]$

変換できました。半角仮名が入っているところが不安ですが、もう一度試してみましょう。

hue_metastore_postal2x図13 区切り文字の選択(日本語)

うまくいっているようです。

hue_metastore_postal3x図14 列名の選択(日本語)

ただ、このデータにはヘッダ行が含まれていないため、自分で定義する必要があります。郵便番号データのREADMEによると、このデータの各列は以下のような意味のようです。

全国地方公共団体コード(JIS X0401、X0402)……… 半角数字
(旧)郵便番号(5桁)……………………………………… 半角数字
郵便番号(7桁)……………………………………… 半角数字
都道府県名 ………… 半角カタカナ(コード順に掲載) (注1)
市区町村名 ………… 半角カタカナ(コード順に掲載) (注1)
町域名 ……………… 半角カタカナ(五十音順に掲載) (注1)
都道府県名 ………… 漢字(コード順に掲載) (注1,2)
市区町村名 ………… 漢字(コード順に掲載) (注1,2)
町域名 ……………… 漢字(五十音順に掲載) (注1,2)
一町域が二以上の郵便番号で表される場合の表示 (注3) (「1」は該当、「0」は該当せず)
小字毎に番地が起番されている町域の表示 (注4) (「1」は該当、「0」は該当せず)
丁目を有する町域の場合の表示 (「1」は該当、「0」は該当せず)
一つの郵便番号で二以上の町域を表す場合の表示 (注5) (「1」は該当、「0」は該当せず)
更新の表示(注6)(「0」は変更なし、「1」は変更あり、「2」廃止(廃止データのみ使用))
変更理由 (「0」は変更なし、「1」市政・区政・町政・分区・政令指定都市施行、「2」住居表示の実施、「3」区画整理、「4」郵便区調整等、「5」訂正、「6」廃止(廃止データのみ使用))

日本語を列名にすると使いにくいので、適当な英語にしてみます。

hue_metastore_postal_4x図15 修正後の列名の選択(日本語)

「テーブルを作成」ボタンを押してテーブルを作成します。

hue_metastore_postal_5x図16 テーブル作成中

Metastore Managerでテーブルを確認してみましょう。

hue_metastore_postal6x図17 postalテーブルの列

hue_metastore_postal7x図18 postalテーブルのサンプル

うまくいきました!

データ中のダブルクォートをうまく扱いたい

ところが、もうひとつ問題が。

このファイルのデータはダブルクォートで囲まれています。HueのMetastore Managerで作成したテーブルはダブルクォートを理解しないので、対応する必要があります。

以前ブログに書いたHive用のSerDe、CSVSerDeを使ってみます。本家にHive 0.11対応版のJarファイルが用意されていたので、今回はJarをダウンロードして使います。

https://github.com/ogrodnek/csv-serde

ogrodnek-csv-serde · GitHub図19 CSVSerDeのダウンロード

BeeswaxのQuery Editorを開き、ファイルのリソースでダウンロードしたJarファイルを追加します。

csvserde1図20 BeeswaxのQuery Editor

左側のメニューからファイルのリソースで「Add」ボタンを押し、先ほどダウンロードしたJarファイルを追加(HDFSにおかれる)します。

csvserde2図21 リソース(CSVSerDe)の追加

Beeswax(もしくはhiveシェル)からALTER TABLEを実行します。

ALTER TABLE postal SET SERDE 'com.bizo.hive.serde.csv.CSVSerde';

csvserde4図22 ALTER TABLEの実行

きれいにダブルクォートが外れていますね。美しい!

alter3図23 ALTER TABLE後

ダブルクォートもきれいに外れていますね。最後にお約束。Hiveでクエリを実行してみます。

query1図24 クエリ実行

query2図25 クエリ実行中

query3図26 クエリ結果

まとめ

Hueの最新版(3.5)でしか利用できないとはいえ、GUIからテーブルの作成が簡単にできるのは嬉しいですね。HueはSparkも操作できるようになりますし(未検証)、今後もますます目が離せないHadoop用のツールになると思います。是非皆さんも使ってみて下さい!

Pocket

Leave a Reply

Your email address will not be published. Required fields are marked *

CAPTCHA


日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)