TableauからHiRDBに接続する(第2回)

# はじめに
前回、HiRDBでのTableau対応を見据え、コネクターの概要について確認しました。

https://qiita.com/Hitachi_Kazuaki/items/f3851d8b6bf899a04231

今回はコネクターの主要な要素である、capabilityとTableau Dialect file(Dialect)を実際に記述
してみます。

# とりあえず接続してみる
前回ご説明したように、TableauからDBへの接続はTableau側の処理をSQLへ変換し、DBに対し実行することで行われます。

![tableau_connector_abs.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3896270/294361de-90e3-f6e5-d4d8-ec0d56c79ff1.png)
実際にどのようなSQLがDBに渡されているかを確認するため、既存のコネクターで動作を確認してみます。

前回の方針で決めた通り、Tableau公式から公開されているPostgreSQL用コネクターを参考に、HiRDB用コネクターを開発します。

PostgreSQL用コネクター:

https://github.com/tableau/connector-plugin-sdk/tree/master/samples/plugins

そこで、まずはPostgreSQL用コネクターを用い、PostgreSQLに接続してみます。

コネクター作成ツールであるTableau Connector SDKにはコネクターにより正しくDBに接続し、 データを操作できているかをテストするTDVD※というテストツールがあります。

※TDVD : the Tableau Data source Verification Tool

テストを実行し、DBにどのようなSQLが渡されているかを確認します。

### Tableauから渡されるSQL
テストの実行結果、およびテストケースごとにDBへ渡されたSQLの一覧がCSVで出力されます。
例えば以下のようなSQLが渡されていました。

例1 : 絶対値を求めるテストケース
```:Tableau側の処理
ABS(num0)
```

```:DBに渡されたSQL
SELECT
  ABS("Calcs"."num0")
FROM "TESTV1"."Calcs" "Calcs"
GROUP BY ABS("Calcs"."num0")
```

絶対値を求めるTableau側の処理「ABS(num0)」が、PostgreSQLで絶対値を求めるABS関数に
変換されたSELECT句が生成されています。

コネクターの動作イメージがなんとなくつかめました。

<br>
ほかにもいくつか確認していると、気になるSQLがありました。

例2 : 日時データから年を取得するテストケース
```:Tableau側の処理
YEAR(DATETIME(datetime0))
```
```:DBに渡されたSQL
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が渡されるようにすれば、正しく実行できそうです。
<br>
イメージがつかめたため、実際にコネクターを作ってみます。

# capabilityを設定してみる
### capabilityの仕様
Tableau側の処理をSQLに変換する際の制限や、接続先DBの仕様(LIMIT句が使えるかなど)を記述するのがcapabilityです。

capabilityはコネクターの仕様を定義するmanifestファイルのcustmizationsタグの中に記述します。

### 作業の方針
capabilityで設定できる項目の一覧が以下のページにあります。

https://tableau.github.io/connector-plugin-sdk/docs/capabilities

capabilityの設定項目は100以上あります。

時間はかかりますが設定項目1つ1つの内容を確認し、HiRDBの仕様に合わせパラメーターを設定していきます。

### 実際に書いてみる
HiRDB用のcapabilityの設定項目をいくつか実際に書いてみます。
```:例1
<customization name="CAP_QUERY_SUBQUERIES_WITH_TOP" value="yes"/>
```
「CAP_QUERY_SUBQUERIES_WITH_TOP」は接続するDBがサブクエリ内でTOPやLIMIT句を実行できるかを示すパラメーターです。
HiRDBはLIMIT句の使用が可能なので「yes」にします。
<br>
```:例2
<customization name="CAP_CREATE_TEMP_TABLES" value="yes"/>
```
「CAP_CREATE_TEMP_TABLES」は一時表をサポートしているかを示すパラメーターです。

「no」に設定した場合:
複雑な処理を実行する際、副問い合わせ中にORDER BY句やLIMIT句が含まれるSQLが生成される場合があります。
```:noの場合のSQL
SELECT row1 FROM (SELECT row2 FROM table1 LIMIT 10);
```
「yes」に設定した場合:
副問い合わせの部分を一時表として、扱うようになります。

HiRDBは副問い合わせ中のLIMIT句には非対応ですが、一時表には対応しているため「yes」に設定します。
<br>
```:例3
<customization name="CAP_QUERY_SORT_BY_DEGREE" value="yes"/>
```
「CAP_QUERY_SORT_BY_DEGREE」はORDER BY句に列の位置番号を使えるかを示すパラメーターです。

「no」に設定した場合:
下のようにORDER BY句に集合関数が指定されたSQLが生成されることがあります。
```:noの場合のSQL
SELECT row1,SUM(row1) FROM table1 ORDER BY SUM(row1);
```
「yes」に設定した場合:
ORDER BY句の列指定が位置番号による指定になります。
```:yesの場合のSQL
SELECT row1,SUM(row1) FROM table1 ORDER BY 2;
```
HiRDBではORDER BY句に集合関数を指定することが出来ず、指定した場合構文エラーになるのですが、位置番号による指定であれば構文エラーにならずに処理を実行することができます。
そのためこのパラメータには「yes」を設定します。

<br>
このような形で残りの項目についても、接続するDBの仕様に合わせ設定を検討していきます。

# Dialectを書いてみる
### Dialectの仕様
前回も触れましたが、DialectはTableau側の関数や演算などの処理をSQLに変換する際の具体的なルールを定義するファイルです。

https://tableau.github.io/connector-plugin-sdk/docs/dialect

記述のルールについては前回の記事をご参照ください。

https://qiita.com/Hitachi_Kazuaki/items/f3851d8b6bf899a04231

### 作業方針
関数、演算については引数の型ごとに処理が異なるため、取り得る引数の型ごとに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):

https://github.com/tableau/connector-plugin-sdk/blob/master/samples/plugins/postgres_jdbc/dialect.tdd

### 実際に書いてみる
実際にDialectを書いてみると、以下の2パターンに分類できます。
<div class="table">
            <table>
                <tr>
                  <th width="35%">分類名</th>
                  <th>説明</th>
                </tr>
                <tr>
                  <td>PostgreSQL用コネクター流用型</td>
                  <td>PostgreSQL用コネクターのDialectと同じ記述で対応可能なもの</td>
                </tr>
                <tr>
                  <td>独自実装型</td>
                  <td>PostgreSQL用コネクターのDialectを参考にすることが出来ず、独自で実装する必要があるもの</td>
                </tr>
              </table>
            </div>
それぞれの記述例を見てます。

### PostgreSQL用コネクター流用型
絶対値を求める処理などが該当します。
整数の絶対値を求める場合、TableauではABS関数を用います。
```:Tableau側の処理
ABS(%1) ※%1は整数引数
```
PostgreSQLでもABS関数を使用することにより、同じように整数の絶対値を求めることができます。
そのため、参考用のPostgreSQLのDialectでは以下のように記述されています。
```:PostgreSQL用Dialect
<function group='numeric' name='ABS' return-type='real'>
  <formula>ABS(%1)</formula>
  <argument type='real' />
</function>
```
処理のイメージは次のようになります。
![tableau_connector_abs.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3896270/6972cfa2-d208-012c-fdb4-3c2123a74647.png)

HiRDBにおいて絶対値を求める場合にも、PostgreSQLと同様にABS関数を使用すればよいため、参考用のDialectと全く同じ記述で問題なく対応できます。

### 独自実装型
冒頭の例で示したような、日時から年を取得する処理などが該当します。
Tableauで日時から年を取得する場合、以下のようにYEAR関数を使用します。

```:Tableau側の処理
YEAR(%1) ※%1は日時、返り値はint型
```
PostgreSQLで日時から年を取得する場合、EXTRACT関数を使用します。
```:PostgreSQLで年を取得
EXTRACT(YEAR FROM %1) ※%1は日時、返り値はdouble precision型
```
ただし、Tableau側のYEAR関数では返り値がint型なのに対し、 PostgreSQLのEXTRACT関数は
返り値がdouble precision型であり、返り値の型が異なります。
返り値の型を揃えるために結果をCAST関数でint型にする必要があります。

そのため、参考用のPostgreSQLのDialectでは以下のように記述されています。
```:PostgreSQL用Dialect
<function group='date' name='YEAR' return-type='int'>
  <formula>CAST(EXTRACT(YEAR FROM %1) AS INTEGER)</formula>
  <argument type='datetime' />
</function>
```
処理のイメージは次の通りです。

![tableau_connector_year_postgreSQL.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3896270/81fe1512-79ec-5f1b-8e98-e7aa16cf4a25.png)

HiRDBにおいて日時から年を求める場合、YEAR関数を使用すればよいため、以下のように記述します。
```:HiRDB用Dialect
<function group='date' name='YEAR' return-type='int'>
  <formula>YEAR(%1)</formula>
  <argument type='datetime' />
</function>
```
HiRDBのYEAR関数の返り値はint型であり、Tableau側のYEAR関数と同様のため、 結果に対しCAST関数による型変換を実行する必要はありません。

処理のイメージは次の通りです。
![tableau_connector_year.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3896270/425c38a2-6f8d-59ed-e2cd-437b2de9ade1.png)
このような形で残りの多数の演算、関数に対してもDialectでの変換ルールを記載していきます。

## 結果
冒頭に出した型変換後に日時データから年を取得するテストケースについては下記のようになり、 HiRDBでも処理できるSQLとなりました。

例2 : 日時データから年を取得するテストケース(capability,Dialect設定後)
```:Tableau側の処理
YEAR(DATETIME(datetime0))
```
```:DBに渡されたSQL
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.の商標です。

その他記載の会社名、製品名は、それぞれの会社の商標もしくは登録商標です。

# 参考

https://itpfdoc.hitachi.co.jp/pages/document_list/manuals/hirdbV10_win.html

https://www.postgresql.jp/document/16/html/sql.html