Flex Tableの検索性能を向上させる方法

公開日: 公開日：2020年01月17日（金）
更新日: 更新日：2025年12月17日（水）
サブカテゴリー: 基本操作; チューニング

タグ: #Flex Table; #半構造化データ

はじめに

Flex Tableは、JSON形式などの半構造化データを容易に取り込むことができ、かつ通常のテーブルと同様にSQL文によるデータ参照を可能にする優れた機能です。
ただし、Verticaに格納されるデータ自体は非構造化状態を保つため、条件検索を行う場合においては検索性能に課題を残す場合があります。
本記事では、上記のような条件検索を高速に行うための方法をご紹介します。

Flexテーブルの概要については以下の記事をご参照ください。
　・Flex Tableの概要
　・JSONデータをVerticaのテーブルにロードする方法(Flex Table　基本編)

仮想列の実体化

Flex Tableにロードされた仮想列のうち、頻繁に検索に使用する列を実際の列として実体化することができます。この実体化された列を検索条件として使用することで、検索性能を向上させることが可能です。

なお、仮想列を実体化させる方法は以下の2つの方法があります。
　・方法1：Flex Tableを作成する際に実体化列を持つHybrid Flex Tableとして定義する
　・方法2：既存のFlex Tableの仮想列を実体化列へと昇格させる

以降の説明で使用する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}

Hybrid Flex Tableの作成方法

構文

CREATE FLEX TABLE <テーブル名> (<実体化列名> <データ型> DEFAULT <キー名>::<データ型> [, 繰返し]);

※<実体化列名>は<キー名>と同じ名称にします。

実行例

# テーブルの作成
dbadmin=> CREATE FLEX TABLE mountains_hybrid(
dbadmin(>     name varchar(40) default name::varchar(40),
dbadmin(>     type varchar(20) default type::varchar(20)
dbadmin(> );
CREATE TABLE

# テーブル定義の確認
dbadmin=> \d mountains_hybrid
                                List of Fields by Tables
 Schema |      Table       | Column  |          Type          |  Size  | Not Null | Primary Key | Foreign Key
--------+------------------+---------+------------------------+--------+----------+-------------+-------------
 public | mountains_hybrid | __raw__ | long varbinary(130000) | 130000 | t        | f           |             <=非構造化データ格納列
 public | mountains_hybrid | name    | varchar(40)            |     40 | f        | f           |             <=実体化列
 public | mountains_hybrid | type    | varchar(20)            |     20 | f        | f           |             <=実体化列
(3 rows)

※横幅を短くするため、default列の表示を省略しています。

# データロード
dbadmin=> COPY mountains_hybrid FROM '/home/dbadmin/mountains.json' PARSER fjsonparser();
 Rows Loaded
-------------
           5
(1 row)

※データロードの方法は通常のFlex Tableの場合と同じです。

既存Flex Tableの仮想列を実体化列へ昇格させる方法

構文

ALTER TABLE <テーブル名> ADD COLUMN <実体化列名> <データ型> DEFAULT <キー名>::<データ型>;

※<実体化列名>は<キー名>と同じ名称にします。

実行例

# 前提：mountains_materializeはFlex Tableとして作成済みの状態

# 仮想列を実体化列へ昇格
dbadmin=> ALTER TABLE mountains_materialize ADD COLUMN name VARCHAR(40) default name::VARCHAR(40);
ALTER TABLE

dbadmin=> ALTER TABLE mountains_materialize ADD COLUMN type VARCHAR(20) default type::VARCHAR(20);
ALTER TABLE

# テーブル定義の確認
dbadmin=> \d mountains_materialize 
                                List of Fields by Tables
 Schema |   Table               |    Column    |          Type          |  Size  |  Not Null | Primary Key | Foreign Key
--------+-----------------------+--------------+------------------------+--------+-----------+-------------+-------------
 public | mountains_materialize | __identity__ | int                    |      8 |  t        | f           |             <=自動生成列
 public | mountains_materialize | __raw__      | long varbinary(130000) | 130000 |  t        | f           |             <=非構造化データ格納列
 public | mountains_materialize | name         | varchar(40)            |     40 |  f        | f           |             <=実体化列
 public | mountains_materialize | type         | varchar(20)            |     20 |  f        | f           |             <=実体化列
(4 rows)

※横幅を短くするため、default列の表示を省略しています。

# データロード
dbadmin=> COPY mountains_materialize FROM '/home/dbadmin/mountains.json' PARSER fjsonparser();
 Rows Loaded
-------------
           5
(1 row)

※データロードの方法は通常のFlex Tableの場合と同じです。

検索性能比較

16万件まで増大させたサンプルデータを用いて、仮想化列を検索条件に使用した場合と実体化列を検索条件に使用した場合の検索性能を比較します。
比較に使用するテーブルは「mountains（通常のFlex Table）」と上記実行例で作成した「mountains_hybrid」「mountains_materialize」の計3テーブルを使用します。

dbadmin=> \timing
Timing is on.

# 仮想化列の検索（通常のFlex Table）
dbadmin=> SELECT AVG(height) FROM mountains WHERE type = 'volcano';
  AVG
-------
 29029
(1 row)

Time: First fetch (1 row): 1457.245 ms. All rows formatted: 1457.376 ms
=> 約1.45秒

# 実体化列の検索①（Hybrid Flex Table）
dbadmin=> SELECT AVG(height) FROM mountains_hybrid WHERE type = 'volcano';
  AVG
-------
 29029
(1 row)

Time: First fetch (1 row): 446.880 ms. All rows formatted: 446.986 ms
=> 約0.45秒

# 実体化列の検索②（既存Flex Tableの仮想列実体化）
dbadmin=> SELECT AVG(height) FROM mountains_materialize WHERE type = 'volcano';
  AVG
-------
 29029
(1 row)

Time: First fetch (1 row): 435.705 ms. All rows formatted: 435.809 ms
=> 約0.44秒

上記の検証では、列の実体化により、約3倍の検索性能の向上が確認できます。

実体化列のライセンス容量

Flex Tableにロードされた非構造化データは、実サイズの1/10がライセンス容量として計上されます。
実体化した列については、対象列の実サイズがライセンス容量として計上されます。

参考情報

・Creating Flex Tables
　https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/FlexTables/CreatingFlexTables.htm

・Materializing Flex Tables
　https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/FlexTables/MaterializingFlexTables.htm

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

・JSONデータをVerticaのテーブルにロードする方
　https://www.ashisuto.co.jp/cm/analytics-database/flex_table_data_road.html

検証バージョンについて

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

更新履歴

2020/01/17　本記事を公開

Analytics Database