この記事では、HiveでJSON配列を解析する方法について詳しく解説します。アプリケーションのページ埋め込みデータなど、複数のフィールドがJSON配列として保存されるケースはよくあります。データプラットフォームでこれらの埋め込みデータを解析する必要がある場合、HiveのJSON解析機能を理解しておくことが重要です。

Hiveの組み込みJSON解析関数

1. get_json_object

• 構文：get_json_object(json_string, '$.キー名')
• 説明：JSON文字列を解析し、指定されたパスの内容を返します。入力されたJSON文字列が無効な場合はNULLを返します。この関数は一度に一つのデータ項目のみを返します。
• 例：

select get_json_object('{"name":"tanaka","age":25}','$.name');

結果：

name
tanaka

nameフィールドとageフィールドの両方を解析するには、次のように記述します：

select get_json_object('{"name":"tanaka","age":25}','$.name'), get_json_object('{"name":"tanaka","age":25}','$.age');

しかし、解析するフィールドが多数ある場合、この方法は非効率です。そこでjson_tuple関数が使用されます。

2. json_tuple

• 構文：json_tuple(json_string, k1, k2 ...)
• 説明：JSON文字列を解析し、複数のJSONキーを指定して対応する値を返します。入力されたJSON文字列が無効な場合はNULLを返します。
• 例：

select b.name, b.age from userData a lateral view json_tuple('{"name":"tanaka","age":25}','name','age') b as name, age;

結果:

name	age
tanaka	25

注意：上記のjson_tuple関数では$は不要です。

json_tuple関数に$を含めると解析に失敗します：

select b.name, b.age from userData a lateral view json_tuple('{"name":"tanaka","age":25}','$.name','$.age') b as name, age;

結果:

name	age
NULL	NULL

フィールドがすべてNULLになります。json_tuple関数では$を付けないようにしてください。さもないと解析できません。

まとめ：json_tupleはget_json_objectの利点として、一度に複数のJSONフィールドを解析できる点があります。しかし、JSON配列がある場合、これらの関数では処理できません。

HiveでのJSON配列解析

1. ネストされたサブクエリによるJSON配列の解析

Hiveテーブルにjson_strフィールドがあり、その内容が以下のような場合を考えてみましょう：

json_str
[{"site":"yahoo.co.jp","title":"ヤフー"},{"site":"google.co.jp","title":"グーグル"}]

このフィールドを以下のような構造に解析したい場合：

site	title
yahoo.co.jp	ヤフー
google.co.jp	グーグル

このJSON配列を解析するには、先に紹介した2つの関数だけでは不十分です。以下で紹介するいくつかの関数が必要です。

explode関数

• 構文：explode(Array OR Map)
• 説明：explode()関数はarrayまたはmap型のデータを入力として受け取り、その要素を行ごとに出力します。つまり、Hiveの列にある複雑なarrayまたはmap構造を複数行に展開する関数です。列を行に変換する関数とも呼ばれます。
• 例：

-- arrayの解析
hive> select explode(array('X','Y','Z'));
OK
X
Y
Z

-- mapの解析
hive> select explode(map('X',100,'Y',200,'Z',300));
OK
X       100
Y       200
Z       300

regexp_replace関数

• 構文: regexp_replace(string A, string B, string C)
• 説明：文字列Aの中でJava正規表現Bに一致する部分を文字列Cに置換します。場合によってはエスケープ文字を使用する必要があります。
• 例：

hive> select regexp_replace('helloworld', 'low|orl', '');
OK
heword

上記の例では、文字列内の"low"または"orl"を空文字に置換しています。

これらの関数を使用して、json_strフィールドの内容を解析してみましょう：

まず、JSON配列の要素を解析して各行に変換します：

hive> SELECT explode(split(regexp_replace(regexp_replace('[{"site":"yahoo.co.jp","title":"ヤフー"},{"site":"google.co.jp","title":"グーグル"}]', '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;'));
OK
{"site":"yahoo.co.jp","title":"ヤフー"}
{"site":"google.co.jp","title":"グーグル"}

上記SQLの簡単な説明：

SELECT explode(split(
    regexp_replace(
        regexp_replace(
            '[
                {"site":"yahoo.co.jp","title":"ヤフー"},
                {"site":"google.co.jp","title":"グーグル"}
            ]',
            '\\[|\\]' , ''),  // JSON配列の両側の角括弧を削除
        '\\}\\,\\{' , '\\}\\;\\{'),  // JSON配列要素間のカンマをセミコロンに置換
    '\\;')  // セミコロンを区切り文字として分割
);

なぜJSON配列要素間のカンマをセミコロンに置換する必要があるのでしょうか？

要素内の区切り文字もカンマであるため、要素間のカンマを置換しない場合、後のsplit関数で要素内のデータも分割してしまいます。これは望ましくありません。

前のステップでJSON配列を複数のJSON文字列に変換しました。次に、json_tuple関数を組み合わせてJSON内のフィールドを解析します：

select json_tuple(json, 'site', 'title') from (
    select explode(split(
        regexp_replace(
            regexp_replace('[{"site":"yahoo.co.jp","title":"ヤフー"},{"site":"google.co.jp","title":"グーグル"}]', '\\[|\\]',''),
            '\\}\\,\\{','\\}\\;\\{'),
        '\\;')) as json
) t;

上記のステートメントを実行すると、結果は以下のようになります：

yahoo.co.jp     ヤフー
google.co.jp    グーグル

2. LATERAL VIEWを使用したJSON配列の解析

productDataテーブルにproduct_idとjson_strフィールドがあり、その内容が以下のような場合を考えてみましょう：

product_id	json_str
101,102,103	[{"maker":"sony","price":"59900","stock":120},{"maker":"panasonic","price":"49800","stock":85},{"maker":"sharp","price":39900,"stock":200}]

目的：product_idフィールドとjson_strフィールドのpriceを解析します。

解析を開始します：

product_idフィールドの分割とJSON配列の複数のJSON文字列への変換：

select explode(split(product_id,',')) as product_num, 
       explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) as product_info 
from productData;

上記のステートメントを実行すると、エラーが発生します：

FAILED: SemanticException 3:0 Only a single expression in the SELECT clause is supported with UDTF's. Error encountered near token 'product_info'

これはUDTFを使用する場合、SELECT句では一つのフィールドのみがサポートされることを意味します。上記のステートメントではSELECT句に二つのフィールドがあるため、エラーが発生しています。

この問題を解決するために、Hiveの別の構文を紹介します：

lateral view

lateral viewはsplit、explodeなどのUDTFと一緒に使用され、一行のデータを複数行に分割します。その後、分割されたデータを集約できます。lateral viewはまず元のテーブルの各行に対してUDTFを呼び出し、UDTFは一行を一行または複数行に分割し、lateral viewは結果を組み合わせて、エイリアステーブルをサポートする仮想テーブルを生成します。

• 例：

ユーザーの趣味データを格納するhobbies_tableテーブルがあるとします。このテーブルには2つの列があり、第一列はname、第二列は趣味IDのid_list（配列）です：

name	id_list
sato	[1,2,3]
suzuki	[3,4,5]

すべてのユーザーで各趣味IDが出現する回数を統計したい場合：

趣味IDを解析します：

SELECT name, hobby_id 
FROM hobbies_table 
LATERAL VIEW explode(id_list) temp_table AS hobby_id;

上記SQLの実行結果：

name	hobby_id
sato	1
sato	2
sato	3
suzuki	3
suzuki	4
suzuki	5

2. hobby_idでグループ化して集約します：

SELECT hobby_id, count(1) user_count 
FROM hobbies_table 
LATERAL VIEW explode(id_list) temp_table AS hobby_id 
group by hobby_id;

結果：

hobby_id	user_count
1	1
2	1
3	2
4	1
5	1

lateral viewの紹介が終わったので、先ほどのUDTFを使用する際にSELECT句で一つのフィールドのみがサポートされる問題を解決してみましょう：

select product_num, get_json_object(product_json,'$.price') as price 
from productData 
LATERAL VIEW explode(split(product_id,',')) products as product_num 
LATERAL VIEW explode(split(regexp_replace(regexp_replace(json_str , '\\[|\\]',''),'\\}\\,\\{','\\}\\;\\{'),'\\;')) sales as product_json;

注意：上記のステートメントは3つのテーブルのデカルト積の結果であるため、この方法はデータ量が大きくない場合に適しています。

上記のステートメントを実行すると、以下のような結果が得られます：

product_id	price
101	59900
101	49800
101	39900
102	59900
102	49800
102	39900
103	59900
103	49800
103	39900

テーブルに他のフィールドがある場合、他のフィールドに基づいて結果に合致するデータをフィルタリングできます。

まとめ：lateral viewは通常UDTFと一緒に使用され、UDTFがSELECT句に複数のフィールドを含むことを許可しない問題を解決します。

以上で「HiveでのJSON配列解析方法」の解説を終わります。この記事が皆さんの疑問解決の一助となれば幸いです。