Administration Toolsを利用したクエリスペシフィックプロジェクションの作成方法

1 はじめに
2 クエリスペシフィックプロジェクション作成手順
3 補足
4 注意事項
5 検証バージョンについて
6 更新履歴

はじめに

本記事ではAdministration Toolsを使用したクエリスペシフィックプロジェクションの作成方法を紹介します。クエリスペシフィックプロジェクションの詳細とManagement Consoleからの手順は、以下の記事を参照してください。

Management Consoleを利用したクエリスペシフィックプロジェクションの作成方法

クエリスペシフィックプロジェクション作成手順

【1】あらかじめ、パフォーマンスを向上したいクエリを記載したsqlファイルを用意しておきます。

[dbadmin@]$ ls -l /tmp/test.sql
-rw-r--r-- 1 dbadmin verticadba 362 12月 11 11:35 /tmp/test.sql

1 2	[dbadmin@]$ ls -l /tmp/test.sql -rw-r--r-- 1 dbadmin verticadba 362 12月 11 11:35 /tmp/test.sql

【2】”admintools”と入力し、Administration Toolsを起動します。

[dbadmin@]$ admintools

1	[dbadmin@]$ admintools

【3】Administration Toolsの「Main Menu」が表示されます。

【4】[6 Configuration Menu]を選択します。

【5】[2 Run Database Designer]を選択します。

【6】実行するデータベースを選択後、[了解]を選択します。

【7】データベースのパスワードを入力後、[了解]を選択します。

【8】Database Designerの実行結果の出力先を指定後、[了解]を選択します。

【9】任意のデザイン名を入力後、[了解]を選択します。

【10】デザインタイプで「Incremental」を選択後、[了解]を選択します。

【11】Database Designerを実行するスキーマを選択後、[了解]を選択します。

【12】「Update statistics」、「Deploy design」を選択後、[了解]を選択します。

Database Designerが作成したプロジェクションの内容を確認してから手動で作成したい場合は「Deploy design」のチェックを外して実行します。この場合は【手順8】で指定したディレクトリ内に作成される「デザイン名_deploy.sql」の内容を実行することでプロジェクションが作成されます。

【13】上記【1】で作成したクエリを指定後、[了解]を選択します。

【14】K-safetyの値を「1」に設定後、[了解]を選択します。
※1ノード構成の場合は表示されません。
※K-safetyはVerticaの高可用性を担保するためのパラメーターになります。詳細はマニュアルを参照ください。

【15】[Proceed]を選択し、Database Designerを実行します。

【16】Database Designerが完了するまで待機します。

Database Designer started.

    For large databases a design session could take a long time; allow it to complete uninterrupted.
    Use Ctrl+C if you must cancel the session.

    Setting up design session...

    Examining table data...

    Loading queries from '/tmp/test.sql'.
        Processed 1 SQL statement(s), all accepted and considered in the design.

    Creating design and deploying projections...
        [100%] Design in progress...  Completed  of  .

    Query optimization results...ress...

         Query 1 optimization ratio or status is 1


Deploying and generating deployment script...
        [100%] Deploying/Dropping projections...  Completed 1 of 1 projections.

    Completed 1 of 1 projections.
Design script is located in /home/dbadmin/dbd/qsp/table1_pro_cs_query_design.sql
 Deployment script is located in /home/dbadmin/dbd/qsp/table1_pro_cs_deploy.sql

Database Designer finished.
Press  to return to the Administration Tools menu.

Database Designer started.

For large databases a design session could take a long time; allow it to complete uninterrupted.

Use Ctrl+C if you must cancel the session.

Setting up design session...

Examining table data...

Loading queries from '/tmp/test.sql'.

Processed 1 SQL statement(s), all accepted and considered in the design.

Creating design and deploying projections...

[100%] Design in progress... Completed of .

Query optimization results...ress...

Query 1 optimization ratio or status is 1

Deploying and generating deployment script...

[100%] Deploying/Dropping projections... Completed 1 of 1 projections.

Completed 1 of 1 projections.

Design script is located in /home/dbadmin/dbd/qsp/table1_pro_cs_query_design.sql

Deployment script is located in /home/dbadmin/dbd/qsp/table1_pro_cs_deploy.sql

Database Designer finished.

Press to return to the Administration Tools menu.

補足

「クエリスペシフィックプロジェクション作成手順」 > 【手順12】で作成された「デザイン名_deploy.sql」の内容と実行方法です。

【deploy.sqlファイルの例】

CREATE PROJECTION public.table1_DBD_1_rep_table1_pro_cs /*+createtype(D)*/
(
　日付 ENCODING AUTO,               <--＋プロジェクションが保持する列
　顧客ID ENCODING COMMONDELTA_COMP,    ｜・クエリの実行に必要な列のみが選択
　売上高 ENCODING AUTO              <--＋・各列に最適な圧縮タイプが選択
)
AS
　SELECT            <--＋
　　日付,              ｜プロジェクション作成時に必要なSELECT文
　　顧客ID,            ｜・プロジェクションの作成に必要な列のみが選択
　　売上高             ｜・クエリ実行、圧縮に最適なソートが選択されている
　FROM public.table1   ｜
　ORDER BY 日付     <--＋
UNSEGMENTED ALL NODES;  <--セグメンテーションに関する設定
 
select refresh('public.table1');  <--リフレッシュの実施
                                     プロジェクションの作成は実際にはREFRESH実行時に行われる

CREATE PROJECTION public.table1_DBD_1_rep_table1_pro_cs /*+createtype(D)*/

(

　日付 ENCODING AUTO, <--＋プロジェクションが保持する列

　顧客ID ENCODING COMMONDELTA_COMP, ｜・クエリの実行に必要な列のみが選択

　売上高 ENCODING AUTO <--＋・各列に最適な圧縮タイプが選択

)

　SELECT <--＋

　　日付, ｜プロジェクション作成時に必要なSELECT文

　　顧客ID, ｜・プロジェクションの作成に必要な列のみが選択

　　売上高｜・クエリ実行、圧縮に最適なソートが選択されている

　FROM public.table1 ｜

　ORDER BY 日付 <--＋

UNSEGMENTED ALL NODES; <--セグメンテーションに関する設定

select refresh('public.table1'); <--リフレッシュの実施

プロジェクションの作成は実際にはREFRESH実行時に行われる

【deploy.sqlファイルの実行方法】

dbadmin=> ¥i デザイン名_deploy.sql

【例) table1_pro_cs_deploy.sqlを実行する場合】

dbadmin=> ¥i table1_pro_cs_deploy.sql

1	dbadmin=> ¥i table1_pro_cs_deploy.sql

注意事項

sqlファイル内のクエリの記述

sqlファイルに書かれたクエリの最後にセミコロン(;)がない場合や間違った構文の場合は、クエリスペシフィックプロジェクションの作成時にエラーが発生します。

Database Designer started.

    For large databases a design session could take a long time; allow it to complete uninterrupted.
    Use Ctrl+C if you must cancel the session.

    Setting up design session...

    Examining table data...

    Loading queries from '/tmp/test.sql'.
        Processed 0 SQL statement(s), all accepted and considered in the design.

Unknown error while running Database Designer.
Err:     ERROR: No queries accepted
Design process may still be executing in the background.



Designer did not run successfully
Unknown error while running Database Designer.
Err:     ERROR: No queries accepted
Design process may still be executing in the background.

Press <Enter> to return to the Administration Tools menu.

Database Designer started.

For large databases a design session could take a long time; allow it to complete uninterrupted.

Use Ctrl+C if you must cancel the session.

Setting up design session...

Examining table data...

Loading queries from '/tmp/test.sql'.

Processed 0 SQL statement(s), all accepted and considered in the design.

Unknown error while running Database Designer.

Err: ERROR: No queries accepted

Design process may still be executing in the background.

Designer did not run successfully

Unknown error while running Database Designer.

Err: ERROR: No queries accepted

Design process may still be executing in the background.

Press <Enter> to return to the Administration Tools menu.

忘れずに、セミコロン(;)を追加してください。

$ cat /tmp/test.sql
select sum(lo_extendedprice*lo_discount) as revenue
from lineorder,date1
where lo_orderdate = d_datekey
  and d_year = 1993
  and lo_discount between 1 and 3
  and lo_quantity < 25;　 <-- セミコロンを追加

$ cat /tmp/test.sql

select sum(lo_extendedprice*lo_discount) as revenue

from lineorder,date1

where lo_orderdate = d_datekey

and d_year = 1993

and lo_discount between 1 and 3

and lo_quantity < 25;　 <-- セミコロンを追加

※クエリの記述に関する注意事項は、以下の記事もあわせてご覧ください。

スーパープロジェクション最適化時にクエリを指定する効果と注意事項

Administration Toolsの実行対象オブジェクト

Administration Toolsで「スーパープロジェクションを最適化する場合」と「クエリスペシフィックプロジェクションを作成する場合」では、実行対象のオブジェクトに違いがあります。

利用ツール	スーパープロジェクションを最適化する場合	クエリスペシフィックプロジェクションを作成する場合
Administration Tools	指定されたスキーマ単位	sqlファイルに記載されたテーブルが対象（スキーマ単位ではない）
Management Console	指定されたテーブル単位	sqlファイルに記載されたテーブルが対象

クエリのパフォーマンス

クエリスペシフィックプロジェクション作成後は、以下のクエリについてパフォーマンスが変化する可能性があるので、十分にパフォーマンス・テストをおこなってください。

・sqlファイルで指定したクエリ
・クエリスペシフィックプロジェクションが、作成されたテーブルを参照している他のクエリ（sqlファイルで指定外のクエリ）

※パフォーマンスが劣化した場合は、実行計画の確認や、ソート順序を確認するなど、どこに問題があるのか以下の記事を参考にチューニングを行うようにしてください。

SQLの実行計画を確認する方法
vsql上でプロジェクションのソート順を確認する方法
Management Consoleを利用したモニタリング～クエリ解析編～

検証バージョンについて

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

更新履歴

2021/11/01　11.0用に更新、
　「はじめに」の他記事リンク方法を変更、
　「注意事項」にクエリの記述, パフォーマンスを追加、
　「クエリスペシフィックプロジェクション作成手順」にK-safetyの画面を追加
2020/12/28　本記事を公開

投稿タグ: クエリスペシフィックプロジェクション, QSP

MENU