目次
はじめに
VerticaにはFlex Tableという、半構造化データをそのままロードできる機能がありますが、このFlex Table機能では、ネストされたデータを持つJSONファイルもそのままロードできます。Flex Tableの基本については、以下の記事をご参照ください。
JSONデータをVerticaのテーブルにロードする方法(Flex Table 基本編)
http://vertica-tech.ashisuto.co.jp/flex_table_data_road/
本記事では、以下の内容についてご紹介します。
・ネストされたデータを持つJSONファイルをFlex Tableへロードする方法
・ロードしたデータをSQLで検索する方法
・ロードしたデータをSQLで検索する方法
ネストされたデータを持つJSONファイルをFlex Tableへロードする方法
ネストされたデータを持つJSONファイルをFlex Tableへロードする場合は、fjsonparserに「flatten_maps=false」オプションを指定することでロードすることができます。実行例
JSONファイルの確認
「restaurant」という項目の中に「_name_」、「cuisine」、「location」、「menu」がネストして格納されています。「location」や「menu」については、更にネストして、データが格納されています。
|
1 2 3 4 5 6 7 8 9 10 11 |
$ 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ファイルをロードします。|
1 2 3 4 5 6 7 8 |
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形式で確認することができます。これを利用し、項目名を確認します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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__」を記載し、続けて括弧内に項目名を指定する
|
1 2 3 4 5 |
dbadmin=> SELECT __raw__['restaurant']['location']['city'] FROM rests; __raw__ ----------- Cambridge (1 row) |
2.最上位の項目を指定し、続けて括弧内にネストされた項目名を指定する
|
1 2 3 4 5 |
dbadmin=> SELECT restaurant['location']['city'] from rests; restaurant ------------ Cambridge (1 row) |
参考情報
Querying Nested Datahttps://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/FlexTables/QueryNestedData.htm
検証バージョンについて
この記事の内容はVertica 9.2で確認しています。更新履歴
2019/10/03 本記事を公開- 投稿タグ
- load, データロード, Flex Table, フレックステーブル, json
