Invantive SQLでは、リリース22.0.14からデータベースビュー（database-views）を定義して使用できるようになりました。データベースビューは、Invantive SQLおよびBIツールのエンドユーザーを対象としています。
この記事では、データベースビューの作成方法と使用方法を示し、データベースビューとInvantiveがドライバーとともに提供するビューとを比較します。
データベースビューの作成
データベースビューは、create [or replace] [force] view文を使用して作成できます。
次のデータベースビューは、Exact Online内の2つのjournalsテーブル間の関連付けを作成します。ここでは、XMLテーブルのyes/noのマッチ情報が、RESTテーブルのjournalsのすべての列に追加されます。
create or replace force view testjoin
as
select coalesce(t1.code, t2.code_attr) code
, t2.OWNBANKACCOUNT_GLPAYMENTINTRANSIT_MATCHING_ATTR
, t1.Description
from. ExactOnlineREST..journals@eol t1
full
outer
join ExactOnlineXML..journals@eol t2
on t1.code = t2.code_attr
ビューの内容を取得する
新しく作成されたビューは、セッション中、カタログDataDictionary、スキーマCustomの下にあるデータディクショナリに保存されます。そのため、完全修飾名はDataDictionary.Custom.TESTJOINとなります。
ビューの作成、変更、削除の際には、そのビューがデータディクショナリにあることを指定する必要はありません。結局のところ、ビューが存在できる場所は1か所しかないためです。一方、クエリを実行する際には、DataDictionaryドライバーのエイリアスを使用して参照元を指定することが必要です。
したがって、次のようなクエリを使用すれば、ビューの内容を簡単に取得できます。
select *
from testjoin@DataDictionary
現時点では、InvantiveデータベースのDataDictionaryConnectionStringプロパティで定義されたデータベースにビュー定義を永続的に保存する方法はありません。ただし、たとえばInvantive Cloudの「On Startup SQL」を使用して、データベースを開く際にビュー定義を再読み込みすることは可能です。
ビューの定義は、Invantive Query Tool内でカーソルをビュー名の上に置いた状態でF4キーを押すことで取得できます。また、データディクショナリビューSystemViewsを通じて常にビュー定義を取得することも可能です。
select PROVIDER_NAME
, Name
, QUERY
from SYSTEMVIEWS@DataDictionary
where PROVIDER_NAME = 'DataDictionary'
結果は次のとおりです:
テーブル関数を反復処理するためのビュー
多くのプラットフォームでは、データにアクセスするためにテーブル関数（table functions）を提供しています。これらのテーブル関数は、1つ以上の必須パラメーターを要求することが多く、完全なデータセットを取得するには、値の範囲にわたって反復処理を行う必要があります。
1つ以上の必須パラメーターを持つテーブル関数は、OData／Power BIでは利用できません。
ビューを使用すると、すべてのデータを取得するプロセスを簡素化できます。たとえば、Teamleader Focusでは、連絡先の住所を取得するには、連絡先住所用のテーブル関数を1回評価する必要があります。これは、以下に示すようにビューを使用することで実現できます。
select d.*
, p.* prefix with 'user_'
from Teamleader.V2.Contacts p
join Teamleader.V2.ContactAddressesByContactId(p.id) d
このようなビューへのアクセスは、非常に多くのAPI呼び出しを引き起こす可能性があることにご注意ください。where句を使用したフィルターは、通常、テーブル関数にアクセスする前に評価されるため、パフォーマンス上の理由から、駆動テーブル（上記の例ではContacts）の列値でフィルタリングすることを推奨します。
複数のデータコンテナーを使用するビュー
データベースビューは、クエリが複数のデータコンテナーにまたがるデータを組み合わせる場合に、特に価値を発揮します。これにより、多くの手作業を省くことができます。次のクエリは、Exact Onlineの管理会社とActiveCampaignの会社を比較します。
create or replace force view compare_companies_across_data_containers_v
as
select coalesce(t1.name, t2.name)
name
label 'Relationship name
, t1.code
exact_online_code
label 'Exact Online Relation Number
, t2.id
active_campaign_id
Tag 'ActiveCampaign ID
from AccountsIncremental@eol t1
full
outer
join Accounts@ac t2
on t2.name = t1.name
select *
from COMPARE_COMPANIES_ACROSS_DATA_CONTAINERS_V@DataDictionary
その結果、各社名がExact OnlineおよびActiveCampaignのどちらの番号で登録されているかを示す差異のリストが得られます。
Exact Onlineの複数の国またはサブスクリプションを扱うビュー
Exact Onlineは国ごとに別々の環境を持っています。たとえば、区分（division）123123は、ベルギーとオランダでは異なる会社を指します。ビューを使用すれば、たとえばPower BIにおいて、複数の国のすべての会社を一度に処理することが可能です。データベース内に、Exact Onlineのサブスクリプションごとにデータコンテナーを作成します（たとえば、エイリアスeolnlとeolbeを使用）。次のクエリは、オランダとベルギーの顧客／仕入先を統合する例です。
create or replace force view eolall_accounts_r
as
select *
from AccountsIncremental@eolnl
--
-- Union allは、列IdがGUIDデータ型で一意な値を持つため、
-- 重複排除を省略できる分、unionよりも高速です。
--
union all
select *
from AccountsIncremental@eolbe
select *
from eolall_accounts_r@DataDictionary
このビューは、スタートアップSQLに組み込むことができます。
エラーのあるビュー
ビューは作成時に構文的な正しさがチェックされますが、実行はされません。forceオプションを指定することで、構文エラーのあるSQL文であってもビューとして使用できます。実行時には、現在アクティブなデータベースにおいて構文と有効性の両方がチェックされます。
Power BIでビューを使用する
データベースビューは、ドライバービューと同様に、Power BI、Power Query、およびAzure Data Factoryで使用できます。ビュー定義は、データベースの「On Startup SQL」に含めることを推奨します。
以下で説明するようにキャッシュをパージすると、そのビューはテーブルの一覧に自動的に表示されるようになります。
On Startup SQLで数十個を超えるデータベースビューを定義すると、データベースを開く際の速度が大幅に低下します。
Power BIにおける新しいビューバージョン
ビューが別のバージョンに置き換えられたり、新しいビューで拡張されたりした場合、一般的に、その新しいバージョンはInvantive Bridge OnlineおよびInvantive App Onlineでは表示されないか、部分的にしか表示されません。新しい定義がPower BIで表示されるようになるまでに1週間ほどかかることがありますが、Invantive CloudのSQLエディターでは即座に表示されます。
Invantive Bridge OnlineおよびInvantive App Onlineは、大量のデータであってもできるだけパフォーマンスを維持するために、いわゆる「キャッシング（caching）」を積極的に使用しています。
このキャッシュをリセットすることで、ビューの新しいバージョンの読み込みを強制できます。キャッシュをリセットすると、Exact Onlineの*Incrementalテーブルのような増分テーブルの古いバージョンを含め、キャッシュされているすべての要素が削除されます。読み込み時には、最初の1回は顕著な遅延が発生しますが、それ以降はパフォーマンスは以前と同様になります。
Invantive Bridge Onlineのキャッシュをリセットするには、以下の手順を実行します。
- 資格情報内で、Power BIまたはPowerBI.comでどのCloudユーザーが使用されているかを確認します。
- そのCloudユーザーでBridge Onlineにログオンします。
- 右上にあるメニューを開きます。
- 以下の画像に示すように「Reset Cache」を選択します。
- Power BIで、利用可能なテーブルの一覧を読み込みます。
- 新しいビュー定義が表示されない場合は？
- その場合は、Bridge Online Monitoringで、読み込みリクエストの「From Cache」からチェックマークが外れているかどうかを確認してください。「From Cache」にチェックが入っている場合は、キャッシュのリセットが失敗しているようです。もう一度キャッシュのリセットを試してください。
ドライバービュー
データベースビューは、特定のドライバー用に固定（提供）されているビューを補完するものです。これらのドライバービューは、特定のドライバーに属するテーブルのデータのみを組み合わせることができます。ドライバービューは、そのドライバーが対象とするプラットフォームの利用を簡素化しますが、変更することはできません。
データベースビューとドライバービューの違いは、以下の表のとおりです。
| データベースビュー | ドライバービュー | |
|---|---|---|
| すべてのサイトで利用可能 | いいえ | はい |
| ユーザーによるカスタマイズ可能 | はい | いいえ |
| 複数のドライバー間でデータを組み合わせ可能 | はい | いいえ |