Top-Kプロジェクションの作成方法

公開日: 公開日：2019年11月14日（木）
更新日: 更新日：2025年12月18日（木）
サブカテゴリー: 基本操作; チューニング

タグ: #プロジェクション

はじめに

Verticaの大きな特徴であるプロジェクションの概要は、こちらの記事でご紹介しました。
本稿では、Top-Kプロジェクションについて、詳しく解説します。

Top-Kプロジェクションとは

Top-Kプロジェクションを使用すると、指定した列のパーティション内、上位K行の結果セットをプロジェクションとして保持できます。この場合、プロジェクション作成時に指定したSQLが発行されると、Verticaは実際には検索処理を行わず、Top-Kプロジェクションを参照するため、検索処理にかかるオーバーヘッドをなくすことができます。また、データがロードされるとTop-Kプロジェクションの内容も最新の結果セットに更新されるため、常に最新の結果を得ることができます。

［イメージ］
1ヶ月の中で消費電力が最も高い時間とその消費電力を確認するSQLを実行する場合。

SELECT date,time,power FROM power_consumption
LIMIT 1 OVER (PARTITION BY date ORDER BY power DESC);

Top-Kプロジェクションの作成方法

Top-KプロジェクションはDatabase Designerを使って自動で作成することはできないため、手動でCREATE PROJECTION文を作成、実行します。

［構文］

CREATE PROJECTION [ IF NOT EXISTS ] projection-name
…[ (
……..{ projection-col | grouped-clause
……… [ ENCODING encoding-type ]
……… [ ACCESSRANK integer ]
……..} [,…]
…..)
…]
AS SELECT {table-col | expr-with-table-cols } [,…] FROM [[database.]schema.]table [ [AS] alias]
… LIMIT num-rows OVER (PARTITION BY column-expr ORDER BY column-expr)
… [ KSAFE [ k-num ] ]

［パラメータ］

項目	説明
IF NOT EXISTS	指定された名前でオブジェクトが既に存在する場合はメッセージを出力します。このオプションを省略してオブジェクトが存在する場合、ROLLBACKエラーメッセージを返します。どちらの場合も、オブジェクトは作成されません。オブジェクトが存在しない場合は作成し、存在する場合は既存のオブジェクトを利用する、SQLスクリプトを作成する場合に役立ちます。関連情報はON_ERROR_STOPを参照してください。
[database.]schema	プロジェクションとテーブルが含まれるスキーマ名を指定します。データベース名を指定する場合は、現在起動中のデータベースを指定します。
projection	作成するプロジェクション名を指定します。プロジェクション名は、同じスキーマ内のシーケンス、テーブル、プロジェクション、ビュー、モデルの中で一意の必要があります。
projection‑col	プロジェクションの列名を指定します。指定しない場合、SELECT文の中で指定したテーブルに含まれる列名を使用します。
grouped‑clause	GROUPED句を参照してください。
ENCODING encoding‑type	列のエンコードタイプを指定します。デフォルトはAUTOに設定されます。
ACCESSRANK integer	列のデフォルトのアクセスランクを上書きします。このパラメータは、Verticaが列にアクセスする速度を調整します。詳細は「デフォルトの列ランキングの上書き」を参照してください。
table‑col expr‑with‑table‑cols	プロジェクションに含めるテーブルの列名または式を指定します。指定する場合は、プロジェクションとテーブルの列名と式の指定順は、完全に一致している必要があります。
FROM table [ [AS] ALIAS]	プロジェクションで利用するテーブルを指定します。エイリアスで修飾することもできます。
GROUP BY column‑expr[,…]	SELECTリストの中から、1つ以上の列名を指定します。最初のcolumn-exprはSELECTリストの最初の列名、2番目のcolumn-exprはSELECTリストの2番目の列名を指定します。
LIMIT num‑rows	指定したパーティションから返される行数を指定します。
OVER (PARTITION BY column‑expr [,…]	パーティション化する列名を指定します。指定する列は、SELECTリストに含まれている1つ以上の列名を指定します。最初のcolumn-exprはSELECTリストの最初の列名、2番目のcolumn-exprはSELECTリストの列名の順序で指定します。
KSAFE [ k‑num ]	プロジェクションのK-Safetyを指定します。k-numはシステムのK-Safety以上である必要があります。プロジェクションがセグメント化されていない場合は、このパラメータは無視されます。 k-numを省略すると、VerticaはシステムのK-Safetyを使用します。詳細はK-Safetyを参照してください。

［作成例］

dbadmin=> CREATE PROJECTION trades_topk AS
dbadmin-> SELECT　　　　　　　　　　　　　　　# プロジェクションの作成に必要なSQLを指定
dbadmin->     symbol,
dbadmin->     trade_time last_trade,
dbadmin->     price last_price
dbadmin-> FROM
dbadmin->     trades
dbadmin-> LIMIT 1　　　　　　　　　　　　　　# 返す行数はLIMIT句で指定
dbadmin-> OVER(
dbadmin(>     PARTITION BY symbol
dbadmin(>         ORDER BY trade_time DESC
dbadmin(> );
WARNING 6852:  Live Aggregate Projection "trades_topk" will be created for "trades". Data in "trades" will be neither updated nor deleted
WARNING 4468:  Projection <public.trades_topk> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION
dbadmin=>

dbadmin=> SELECT REFRESH('public.trades');　# プロジェクションの作成は実際にはREFRESH実行時に行われる

                                                                                                                                             REFRESH                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Refresh completed with the following outcomes:
Projection Name: [Anchor Table] [Status] [Refresh Method] [Error Count] [Duration (sec)]
----------------------------------------------------------------------------------------
"public"."trades_topk": [trades] [refreshed] [scratch] [0] [0]

(1 row)

作成時の注意事項

PARTITION BY句とORDER BY句への列の指定について

PARTITION BY句とORDER BY句に指定する列は、SELECTリストの順序で指定する必要があります
成功例.

CREATE PROJECTION readings_topk (meter_id, recent_date, recent_value)
AS SELECT meter_id, reading_value,reading_date FROM readings
LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_value DESC);
WARNING 6852:  Live Aggregate Projection "readings_topk" will be created for "readings". Data in "readings" will be neither updated nor deleted
WARNING 4116:  No super projections created for table public.readings.
HINT:  Default super projections will be automatically created with the next DML
CREATE PROJECTION

失敗例.

dbadmin=> CREATE PROJECTION readings_topk (meter_id, recent_date, recent_value)
dbadmin-> AS SELECT meter_id, reading_value,reading_date FROM readings
dbadmin-> LIMIT 5 OVER (PARTITION BY meter_id ORDER BY reading_date DESC);
ERROR 6198:  ORDER BY columns/expressions in the OVER() clause must be the first SELECT columns/expressions not specified by PARTITION BY clause, and must be specified in SELECT list order

ORDER BY句に指定されているreading_dateが、SELECTリストの順序どおりでは無いためエラーとなる

LIMIT句の指定について

結果セットに返す行数の指定はWHERE句ではなく、LIMIT句を指定する必要があります
成功例.

dbadmin=> CREATE PROJECTION trades_topk AS
dbadmin-> SELECT symbol, trade_time last_trade, price last_price FROM trades
dbadmin-> LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);
WARNING 6852:  Live Aggregate Projection "trades_topk" will be created for "trades". Data in "trades" will be neither updated nor deleted
WARNING 4468:  Projection <public.trades_topk> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

失敗例.

dbadmin=> CREATE PROJECTION trades_topk AS
dbadmin-> SELECT symbol, trade_time last_trade, price last_price FROM (
dbadmin(>     SELECT symbol, trade_time, price, ROW_NUMBER()
dbadmin(>     OVER(PARTITION BY symbol ORDER BY trade_time DESC) rn FROM trades) trds WHERE rn <=1;
ERROR 5664:  Subqueries not allowed in projection definition

成功例のSELECT文と同じ結果を返すが、LIMIT句を指定していないためエラーとなる

制限事項

・Top-Kプロジェクションは、1つのテーブルのみを参照できます。
・Top-Kプロジェクションに、含まれる列の削除、変更はできません。(※)
・Top-Kプロジェクションが、参照しているテーブルに対しては、次の操作はできません。(※)
　- DELETE
　- UPDATE
　- MERGE

(※)実行するためには、Top-Kプロジェクションを削除する必要があります。

参考情報

Top-K Projections
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/AggregatedData/TopKQueryProjections.htm

ライブアグリゲートプロジェクションの作成方法
https://www.ashisuto.co.jp/cm/analytics-database/lap.html

検証バージョンについて

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

更新履歴

2019/11/14　本記事を公開

Analytics Database

Top-Kプロジェクションの作成方法

はじめに

Top-Kプロジェクションとは

Top-Kプロジェクションの作成方法

作成時の注意事項

PARTITION BY句とORDER BY句への列の指定について

LIMIT句の指定について

制限事項

参考情報

検証バージョンについて

更新履歴