はじめに
Verticaの大きな特徴であるプロジェクションの概要は、こちらの記事でご紹介しました。本稿では、Top-Kプロジェクションについて、詳しく解説します。
Top-Kプロジェクションとは
Top-Kプロジェクションを使用すると、指定した列のパーティション内、上位K行の結果セットをプロジェクションとして保持できます。この場合、プロジェクション作成時に指定したSQLが発行されると、Verticaは実際には検索処理を行わず、Top-Kプロジェクションを参照するため、検索処理にかかるオーバーヘッドをなくすことができます。また、データがロードされるとTop-Kプロジェクションの内容も最新の結果セットに更新されるため、常に最新の結果を得ることができます。［イメージ］
1ヶ月の中で消費電力が最も高い時間とその消費電力を確認するSQLを実行する場合。
|
1 2 |
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 ] ]
…[ (
……..{ 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を参照してください。 |
［作成例］
|
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=> 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リストの順序で指定する必要があります成功例.
|
1 2 3 4 5 6 7 |
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 |
失敗例.
|
1 2 3 4 |
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句を指定する必要があります成功例.
|
1 2 3 4 5 6 7 |
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 |
失敗例.
|
1 2 3 4 5 |
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 Projectionshttps://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/AggregatedData/TopKQueryProjections.htm
ライブアグリゲートプロジェクションの作成方法
http://vertica-tech.ashisuto.co.jp/lap/
