はじめに
前回、HiRDBでのTableau対応を見据え、コネクターの概要について確認しました。
今回はコネクターの主要な要素である、capabilityとTableau Dialect file(Dialect)を実際に記述
してみます。
とりあえず接続してみる
前回ご説明したように、TableauからDBへの接続はTableau側の処理をSQLへ変換し、DBに対し実行することで行われます。
実際にどのようなSQLがDBに渡されているかを確認するため、既存のコネクターで動作を確認してみます。
前回の方針で決めた通り、Tableau公式から公開されているPostgreSQL用コネクターを参考に、HiRDB用コネクターを開発します。
PostgreSQL用コネクター:
そこで、まずはPostgreSQL用コネクターを用い、PostgreSQLに接続してみます。
コネクター作成ツールであるTableau Connector SDKにはコネクターにより正しくDBに接続し、 データを操作できているかをテストするTDVD※というテストツールがあります。
※TDVD : the Tableau Data source Verification Tool
テストを実行し、DBにどのようなSQLが渡されているかを確認します。
Tableauから渡されるSQL
テストの実行結果、およびテストケースごとにDBへ渡されたSQLの一覧がCSVで出力されます。
例えば以下のようなSQLが渡されていました。
例1 : 絶対値を求めるテストケース
ABS(num0)
SELECT
ABS("Calcs"."num0")
FROM "TESTV1"."Calcs" "Calcs"
GROUP BY ABS("Calcs"."num0")
絶対値を求めるTableau側の処理「ABS(num0)」が、PostgreSQLで絶対値を求めるABS関数に
変換されたSELECT句が生成されています。
コネクターの動作イメージがなんとなくつかめました。
ほかにもいくつか確認していると、気になるSQLがありました。
例2 : 日時データから年を取得するテストケース
YEAR(DATETIME(datetime0))
SELECT
CAST(
EXTRACT(
YEAR FROM CAST("Calcs"."datetime0" AS TIMESTAMP))
AS INTEGER)
FROM "TESTV1"."Calcs" "Calcs"
GROUP BY
CAST(
EXTRACT(
YEAR FROM CAST("Calcs"."datetime0" AS TIMESTAMP))
AS INTEGER)
EXTRACT関数により、日時データから年の情報を取得するSQLですが、HiRDBにはEXTRACT関数はないため、 もしHiRDBにこのSQLを渡した場合エラーとなります。
テストケースを確認すると、Tableau側の処理としては型変換の実行後、 YEAR関数により日時データから年を取得するものでした。
今回はPostgreSQL用のコネクターを使用したため、 PostgreSQLで日時データから年や月、日を取得する際に使用する、EXTRACT関数が含まれるSQLが生成されたようです。
HiRDBで日時データから年を取得する際にはYEAR関数を使用するため、HiRDB用のコネクター開発の際にはEXTRACT関数の代わりに YEAR関数を使用するSQLが渡されるようにすれば、正しく実行できそうです。
イメージがつかめたため、実際にコネクターを作ってみます。
capabilityを設定してみる
capabilityの仕様
Tableau側の処理をSQLに変換する際の制限や、接続先DBの仕様(LIMIT句が使えるかなど)を記述するのがcapabilityです。
capabilityはコネクターの仕様を定義するmanifestファイルのcustmizationsタグの中に記述します。
作業の方針
capabilityで設定できる項目の一覧が以下のページにあります。
capabilityの設定項目は100以上あります。
時間はかかりますが設定項目1つ1つの内容を確認し、HiRDBの仕様に合わせパラメーターを設定していきます。
実際に書いてみる
HiRDB用のcapabilityの設定項目をいくつか実際に書いてみます。
<customization name="CAP_QUERY_SUBQUERIES_WITH_TOP" value="yes"/>
「CAP_QUERY_SUBQUERIES_WITH_TOP」は接続するDBがサブクエリ内でTOPやLIMIT句を実行できるかを示すパラメーターです。
HiRDBはLIMIT句の使用が可能なので「yes」にします。
<customization name="CAP_CREATE_TEMP_TABLES" value="yes"/>
「CAP_CREATE_TEMP_TABLES」は一時表をサポートしているかを示すパラメーターです。
「no」に設定した場合:
複雑な処理を実行する際、副問い合わせ中にORDER BY句やLIMIT句が含まれるSQLが生成される場合があります。
SELECT row1 FROM (SELECT row2 FROM table1 LIMIT 10);
「yes」に設定した場合:
副問い合わせの部分を一時表として、扱うようになります。
HiRDBは副問い合わせ中のLIMIT句には非対応ですが、一時表には対応しているため「yes」に設定します。
<customization name="CAP_QUERY_SORT_BY_DEGREE" value="yes"/>
「CAP_QUERY_SORT_BY_DEGREE」はORDER BY句に列の位置番号を使えるかを示すパラメーターです。
「no」に設定した場合:
下のようにORDER BY句に集合関数が指定されたSQLが生成されることがあります。
SELECT row1,SUM(row1) FROM table1 ORDER BY SUM(row1);
「yes」に設定した場合:
ORDER BY句の列指定が位置番号による指定になります。
SELECT row1,SUM(row1) FROM table1 ORDER BY 2;
HiRDBではORDER BY句に集合関数を指定することが出来ず、指定した場合構文エラーになるのですが、位置番号による指定であれば構文エラーにならずに処理を実行することができます。
そのためこのパラメータには「yes」を設定します。
このような形で残りの項目についても、接続するDBの仕様に合わせ設定を検討していきます。
Dialectを書いてみる
Dialectの仕様
前回も触れましたが、DialectはTableau側の関数や演算などの処理をSQLに変換する際の具体的なルールを定義するファイルです。
記述のルールについては前回の記事をご参照ください。
作業方針
関数、演算については引数の型ごとに処理が異なるため、取り得る引数の型ごとにSQLへの変換のルールを記述する必要があります。
例えば、数値データの足し算、文字列データの足し算では、同じ足し算でも処理の内容は異なります。
数値 : 1+1=2
文字列 : '1'+'1' = '11'
それを加味すると、全体で300種類以上の処理について、SQLへの変換ルールを記述する必要があります。
この負担を軽減できる、継承という機能がDialectにはあります。 継承機能により、多くのDBに共通した変換ルールをまとめたDefault Dialectなどを継承することで、Default Dialectとは異なる独自の変換を行いたい部分だけの記述で済みます。
しかし
残念ながら、現在はDefault Dialectの使用は非推奨となっています。
Default Dialect以外のDialectを継承することもできますが、最終的には全ての変換について妥当性を検証する必要があるため、今回は継承機能を使用せず、PostgreSQL用Dialectを参考にしてHiRDB用Dialectを作成します。
参考 (PostgreSQL用Dialect):
実際に書いてみる
実際にDialectを書いてみると、以下の2パターンに分類できます。
| 分類名 | 説明 |
|---|---|
| PostgreSQL用コネクター流用型 | PostgreSQL用コネクターのDialectと同じ記述で対応可能なもの |
| 独自実装型 | PostgreSQL用コネクターのDialectを参考にすることが出来ず、独自で実装する必要があるもの |
PostgreSQL用コネクター流用型
絶対値を求める処理などが該当します。
整数の絶対値を求める場合、TableauではABS関数を用います。
ABS(%1) ※%1は整数引数
PostgreSQLでもABS関数を使用することにより、同じように整数の絶対値を求めることができます。
そのため、参考用のPostgreSQLのDialectでは以下のように記述されています。
<function group='numeric' name='ABS' return-type='real'>
<formula>ABS(%1)</formula>
<argument type='real' />
</function>
処理のイメージは次のようになります。
HiRDBにおいて絶対値を求める場合にも、PostgreSQLと同様にABS関数を使用すればよいため、参考用のDialectと全く同じ記述で問題なく対応できます。
独自実装型
冒頭の例で示したような、日時から年を取得する処理などが該当します。
Tableauで日時から年を取得する場合、以下のようにYEAR関数を使用します。
YEAR(%1) ※%1は日時、返り値はint型
PostgreSQLで日時から年を取得する場合、EXTRACT関数を使用します。
EXTRACT(YEAR FROM %1) ※%1は日時、返り値はdouble precision型
ただし、Tableau側のYEAR関数では返り値がint型なのに対し、 PostgreSQLのEXTRACT関数は
返り値がdouble precision型であり、返り値の型が異なります。
返り値の型を揃えるために結果をCAST関数でint型にする必要があります。
そのため、参考用のPostgreSQLのDialectでは以下のように記述されています。
<function group='date' name='YEAR' return-type='int'>
<formula>CAST(EXTRACT(YEAR FROM %1) AS INTEGER)</formula>
<argument type='datetime' />
</function>
処理のイメージは次の通りです。
HiRDBにおいて日時から年を求める場合、YEAR関数を使用すればよいため、以下のように記述します。
<function group='date' name='YEAR' return-type='int'>
<formula>YEAR(%1)</formula>
<argument type='datetime' />
</function>
HiRDBのYEAR関数の返り値はint型であり、Tableau側のYEAR関数と同様のため、 結果に対しCAST関数による型変換を実行する必要はありません。
処理のイメージは次の通りです。
このような形で残りの多数の演算、関数に対してもDialectでの変換ルールを記載していきます。
結果
冒頭に出した型変換後に日時データから年を取得するテストケースについては下記のようになり、 HiRDBでも処理できるSQLとなりました。
例2 : 日時データから年を取得するテストケース(capability,Dialect設定後)
YEAR(DATETIME(datetime0))
SELECT
YEAR(CAST("Calcs"."datetime0" AS TIMESTAMP))
FROM "TESTV1"."Calcs" "Calcs"
GROUP BY YEAR(CAST("Calcs"."datetime0" AS TIMESTAMP))
次回
今回作成したcapabilityとDialectを使ってTableauからHiRDBに接続してみます。
商標
Tableauは、salesforce.com,Inc.の商標です。
その他記載の会社名、製品名は、それぞれの会社の商標もしくは登録商標です。
参考