はじめに

Flex Tableは半構造化データを簡単にテーブル形式で扱えるようになる便利な機能です。
ただし、ロードをしただけでは通常のテーブルとは使い勝手が異なります。
本記事では、Flex Tableを通常のテーブルと同じように扱いやすくする機能をご紹介します。

キーテーブルとビューを有効化

COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW関数を使うことで、Flex Table のキーテーブルと、ビューを有効化します。
それにより、Flex Tableの列定義を確認したり、「SELECT * FROM テーブル名」での参照が可能となります。

構文

以下が構文です。

COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW('Flex Table名')

実行例

事前準備＆確認

JSONの確認
$ cat test.json
{"name": "Everest", "type":"mountain", "height":29029, "hike_safety": 34.1}
{"name": "Mt St Helens", "type":"volcano", "height":29029, "hike_safety": 15.4}
{"name": "Denali", "type":"mountain", "height":17000, "hike_safety": 12.2}
{"name": "Kilimanjaro", "type":"mountain", "height":14000 }
{"name": "Mt Washington", "type":"mountain", "hike_safety": 50.6}

テーブルの作成
dbadmin=> CREATE FLEX TABLE mountains();
CREATE TABLE

データロード
dbadmin=> COPY mountains FROM '/tmp/work/test.json' parser fjsonparser();
 Rows Loaded
-------------
           5
(1 row)

★Flex Tableを作成すると、キーテーブルとビューが自動的に作成されます。
「mountains」だけでなく「mountains_keys」と「mountains_view」も作成されていることを確認します。
dbadmin=> \d
List of tables
Schema | Name | Kind | Owner | Comment
——–+—————-+——-+———+———
public | mountains | table | dbadmin |
public | mountains_keys | table | dbadmin |
(2 rows)

dbadmin=> \dv
List of views
Schema | Name | Owner | Comment
——–+—————-+———+———
public | mountains_view | dbadmin |
(1 row)

★どちらもこの時点でデータ表示および参照不可であることを確認します。
dbadmin=> SELECT * FROM mountains_keys;
 key_name | frequency | data_type_guess
----------+-----------+-----------------
(0 rows)

dbadmin=> SELECT * FROM mountains_view;
                                                            status                                                   
------------------------------------------------------------------------------------------------------------------------------
 Please run compute_flextable_keys_and_build_view() to update this view to reflect real and virtual columns in the flex table
(1 row)

★COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW関数を実行します。
dbadmin=> SELECT COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW('mountains');
                                 COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
--------------------------------------------------------------------------------------------------------
 Please see public.mountains_keys for updated keys
The view public.mountains_view is ready for querying
(1 row)

★「mountains_keys」「mountains_view」共に参照可能となったことを確認します。
dbadmin=> SELECT * FROM mountains_keys;
  key_name   | frequency | data_type_guess
-------------+-----------+-----------------
 hike_safety |         4 | Numeric(6,2)
 height      |         4 | Integer
 type        |         5 | Varchar(20)
 name        |         5 | Varchar(26)
(4 rows)

dbadmin=> SELECT * FROM mountains_view;
     name      |   type   | height | hike_safety
---------------+----------+--------+-------------
 Kilimanjaro   | mountain |  14000 |
 Everest       | mountain |  29029 |       34.10
 Mt St Helens  | volcano  |  29029 |       15.40
 Denali        | mountain |  17000 |       12.20
 Mt Washington | mountain |        |       50.60
(5 rows)

参考

COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/FlexTables/COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW.htm

Flex Tableの概要
https://www.ashisuto.co.jp/cm/analytics-database/flex_table.html

JSONデータをVerticaのテーブルにロードする方法(Flex Table　基本編)
https://www.ashisuto.co.jp/cm/analytics-database/flex_table_data_road.html

ネストされたデータを持つJSONファイルをFlex Tableへロードする
https://www.ashisuto.co.jp/cm/analytics-database/flex_table_nested.html

検証バージョンについて

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

更新履歴

2021/04/08　fjsonparser()の指定方法を修正

2020/09/11　本記事を公開