Tech Blog 〜ぞうの日記

HadoopやLinux関連の技術的な内容の紹介です

（４日目）HueでCSVデータからHiveのテーブルを作る

hadoop

元記事

 

４日目です。

本日は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,0,1,0,0,0<br />01101,"064  ","0640820","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｵｵﾄﾞｵﾘﾆｼ(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,0,1,0,0,0<br />01101,"064  ","0640821","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾆｼ(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,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用のツールになると思います。是非皆さんも使ってみて下さい！