HiveからMapR-DB JSONテーブルにアクセスする #hive

本記事では前回のBinaryテーブルに続いてMapR-DBのJSONテーブルにHiveからアクセスする方法について紹介します。

Hiveの各型とJSONテーブルの型の対応についてはこちらのドキュメントに記載されています。
プリミティブな型はもちろん、Hiveで利用するmap, array, structといった型も利用することが出来ます。

それでは簡単なケースから見ていきましょう。
なお、本記事ではMapR 5.2.2 + MEP 3.0.2を使いました。

hive> create table dbjson_table (
      id string,
      bo boolean,
      d double,
      da date,
      f double,
      i int,
      s string,
      ts timestamp)
     STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler'
     TBLPROPERTIES("maprdb.table.name" = "/user/mapr/db/dbjson_table", "maprdb.column.id" = "id");


hive> create table dbjson_table2 (
      id string,
      bo boolean,
      d double,
      da date,
      f double,
      i int,
      s string,
      ts timestamp)
     STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler'
     TBLPROPERTIES("maprdb.table.name" = "/user/mapr/db/dbjson_table2", "maprdb.column.id" = "s");

hive> insert into table dbjson_table  values ("testid", true, 3.14, 20170401, 6.48, 4, "world", 123123123);
hive> insert into table dbjson_table2 values ("testid", true, 3.14, 20170401, 6.48, 4, "world", 123123123);

まずテーブルを２つ作ってみました。
どちらも"maprdb.table.name"でテーブルへのパスを指定していますが、"maprdb.column.id"で指定するキーを変更しています。
こちらを指定したカラムがJSONテーブルにおけるkeyとして扱われます。
実際にJSONテーブルでのデータの持ち方を確認してみます。

$ mapr dbshell
====================================================
*                  MapR-DB Shell                   *
* NOTE: This is a shell for JSON table operations. *
====================================================
Version: 5.2.2-mapr

MapR-DB Shell
maprdb mapr:> find /user/mapr/db/dbjson_table
{"_id":"testid","f":6.48,"bo":true,"d":3.14,"s":"world","i":{"$numberLong":4}}

maprdb mapr:> find /user/mapr/db/dbjson_table2
{"_id":"world","f":6.48,"id":"testid","bo":true,"d":3.14,"i":{"$numberLong":4}}

JSONテーブルのkeyを意味する"_id"の値が2つのテーブルで異なることが分かります。

続いてmap, array, structを使った場合の使用例は以下のようになります。

hive> CREATE TABLE sample_json_array_struct_map(
         DocId string,
         orders array<struct<ItemId:int,orderdate:string>>,
         map_data map<string,string>
     )
     STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler'
     TBLPROPERTIES("maprdb.table.name" = "/user/mapr/db/sample_json_array_struct_map","maprdb.column.id" = "DocId");

insertには適当なdummyテーブルを使ってみます

hive> insert into sample_json_array_struct_map 
      select "test1", 
             array(named_struct('ItemId', 10, 'orderdate', '20180401'), named_struct('ItemId', 20, 'orderdate', '20180402')), 
              map('aa', 'bb') from dummy ;

hive> select * from sample_json_array_struct_map;
OK
test1   [{"itemid":10,"orderdate":"20180401"},{"itemid":20,"orderdate":"20180402"}]     {"aa":"bb"}

次に既存のJSONテーブルに対してExternal Tableを作ってクエリを走らせてみます。
まずはJSONテーブルを適当に作ってみます。

$ cat json_hive.json
{"_id":"1234","username":"sam1234","name":"Sam", "prop": {"kind": "cat", "age":13}, "children": [ {"name": "cathy", "age": 3}, {"name": "mike", "age": 2}] }

$ mapr importJSON -src /tmp/json_hive.json -dst /user/mapr/db/json_hive

$ mapr dbshell
maprdb mapr:> find /user/mapr/db/json_hive
{"_id":"1234","username":"sam1234","name":"Sam","prop":{"kind":"cat","age":13},"children":[{"name":"cathy","age":3},{"name":"mike","age":2}]}

次にこのJSONテーブルに対してExternal Tableを作ってみます。

$ hive
hive> CREATE EXTERNAL TABLE hivejsonexternal (
  id string,
  username string,
  name string,
  prop struct<kind: string, age: int>,
  children array<struct<name: string, age: int>>
)
STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler' 
TBLPROPERTIES("maprdb.table.name" = "/user/mapr/db/json_hive","maprdb.column.id" = "id"); 

hive> desc hivejsonexternal;
OK
id                      string                  from deserializer
username                string                  from deserializer
name                    string                  from deserializer
prop                    struct<kind:string,age:int>     from deserializer
children                array<struct<name:string,age:int>>      from deserializer
Time taken: 0.222 seconds, Fetched: 5 row(s)

hive> select * from hivejsonexternal;
OK
1234    sam1234 Sam     {"kind":"cat","age":13} [{"name":"cathy","age":3},{"name":"mike","age":2}]

上述のようにJSONテーブルのカラムを指定する形でHiveテーブルを定義します。
上の例では全てのカラムを羅列しましたが、不必要なカラムがあれば記載する必要はありません。

hive> CREATE EXTERNAL TABLE hivejsonexternal_2 (
  id string,
  username string,
  prop struct<kind: string, age: int>
)
STORED BY 'org.apache.hadoop.hive.maprdb.json.MapRDBJsonStorageHandler' 
TBLPROPERTIES("maprdb.table.name" = "/user/mapr/db/json_hive","maprdb.column.id" = "id");

hive> select * from hivejsonexternal_4;
OK
1234    sam1234 {"kind":"cat","age":13}

注意すべきこととして、Hiveのカラム名はcase insensitiveなのでJSONテーブルのカラム名も全て小文字である必要があります。

以上、HiveからJSONテーブルへのアクセスでした。
次回はDrillからアクセスしたいと思います。