はじめに

VerticaにはFlex Tableという、半構造化データをそのままロードできる機能がありますが、このFlex Table機能では、ネストされたデータを持つJSONファイルもそのままロードできます。

Flex Tableの基本については、以下の記事をご参照ください。
　JSONデータをVerticaのテーブルにロードする方法(Flex Table　基本編)
　　　https://www.ashisuto.co.jp/cm/analytics-database/flex_table_data_road.html

本記事では、以下の内容についてご紹介します。

・ネストされたデータを持つJSONファイルをFlex Tableへロードする方法
・ロードしたデータをSQLで検索する方法

ネストされたデータを持つJSONファイルをFlex Tableへロードする方法

ネストされたデータを持つJSONファイルをFlex Tableへロードする場合は、fjsonparserに「flatten_maps=false」オプションを指定することでロードすることができます。

実行例

JSONファイルの確認

「restaurant」という項目の中に「_name_」、「cuisine」、「location」、「menu」がネストして格納されています。
「location」や「menu」については、更にネストして、データが格納されています。

$ cat /home/dbadmin/restaurant.json
{
  "restaurant" : {
      "_name_" : "Bob's pizzeria",
      "cuisine" : "Italian",
      "location" : {"city" : "Cambridge", "zip" : "02140"},
      "menu" : [{"item" : "cheese pizza", "price" : "$8.25"},
                {"item" : "chicken pizza", "price" : "$11.99"},
                {"item" : "spinach pizza", "price" : "$10.50"}]
  }
}

JSONファイルをFlex Tableへロード

fjsonparserに「flatten_maps=false」オプションを指定し、JSONファイルをロードします。

dbadmin=> CREATE FLEX TABLE rests();
CREATE TABLE

dbadmin=> COPY rests FROM '/home/dbadmin/restaurant.json' PARSER fjsonparser (flatten_maps=false);
 Rows Loaded
-------------
           1
(1 row)

ロードしたデータをSQLで検索する方法

項目名の確認

SQLでデータを検索するには項目名を指定する必要がありますが、Flex Tableは通常テーブルと同じ様にテーブル定義を確認することはできません。
MAPTOSTRING関数を利用することで、Flex Tableに格納されているデータを読み取り可能なLONG VARCHAR形式で確認することができます。これを利用し、項目名を確認します。

dbadmin=> SELECT MAPTOSTRING(__raw__) FROM rests;
                                                                                                                                                                                                                                                                         MAPTOSTRING                                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 {
    "restaurant": {
        "_name_": "Bob's pizzeria",
        "cuisine": "Italian",
        "location": {
            "city": "Cambridge",
            "zip": "02140"
        },
        "menu": {
            "0": {
                "item": "cheese pizza",
                "price": "$8.25"
            },
            "1": {
                "item": "chicken pizza",
                "price": "$11.99"
            },
            "2": {
                "item": "spinach pizza",
                "price": "$10.50"
            }
        }
    }
}
(1 row)

ネストされたデータをSQLで検索する

ネストされたデータをSQLで検索する方法は2つあります。
　1.列名指定のはじめに、「__raw__」を記載し、続けて括弧内に項目名を指定する
　2.最上位の項目を指定し、続けて括弧内にネストされた項目名を指定する

1.列名指定のはじめに、「__raw__」を記載し、続けて括弧内に項目名を指定する

dbadmin=> SELECT __raw__['restaurant']['location']['city'] FROM rests;
  __raw__
-----------
 Cambridge
(1 row)

2.最上位の項目を指定し、続けて括弧内にネストされた項目名を指定する

dbadmin=> SELECT restaurant['location']['city'] from rests;
 restaurant
------------
 Cambridge
(1 row)

参考情報

Querying Nested Data
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/FlexTables/QueryNestedData.htm

検証バージョンについて

この記事の内容はVertica 9.2で確認しています。

更新履歴

2019/10/03　本記事を公開