SQLとBIツールに独自のデータベースビューを使用する

[forums]

英語版はこちら

Invantive SQLでは、リリース22.0.14から、データベースビュー(database-views)を定義・使用できるようになりました。データベースビューは、Invantive SQLおよびBIツールのエンドユーザーを対象としています。

この記事では、データベースビューの作成方法と使用方法を紹介し、Invantiveがドライバーに付属させているビュー(views)と比較します。

データベースビューの作成

データベースビューは、create [or replace] [force] viewステートメントを使用して作成できます。

以下のデータベースビューは、Exact Online内の2つの仕訳帳テーブル間の関係を定義し、XMLテーブルからマッチング有無(wel/niet matchen)の情報を、RESTテーブルの仕訳帳のすべての列に追加します。

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

ビューの内容を取得する

新しいビューは、セッションの間、データディクショナリ(data dictionary)内のカタログ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'

結果は次のようになります。

テーブル関数を反復処理するためのビュー

多くのプラットフォームでは、データへのアクセス手段としてテーブル関数が提供されています。これらのテーブル関数には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句を使用したフィルターは、通常テーブル関数にアクセスする前に評価されるため、パフォーマンス上の理由から、ドライビングテーブル(上記の例ではContacten)の列値でフィルタリングすることをお勧めします。

複数のデータコンテナーにまたがるビュー

データベースビューが真価を発揮するのは、クエリが複数のデータコンテナーにまたがってデータを結合する場合です。これにより、多くの手作業を省くことができます。以下のクエリは、Exact Online管理内の取引先(relaties)とActiveCampaignを比較します。

create or replace force view compare_companies_across_data_containers_v
as
select coalesce(t1.name, t2.name) 
       name
       label 'Relatienaam'
,      t1.code 
       exact_online_code
       label 'Exact Online Relatienummer'
,      t2.id 
       active_campaign_id
       label '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は国ごとに別々の環境を持っています。例えば、部門(divisie)123123は、ベルギーとオランダでは異なる管理データとなります。ビューを使用することで、複数の国のすべての管理データを、例えばPower BIで一度に処理することが可能になります。Exact Onlineの契約ごとに、データベース内にデータコンテナーを作成してください。例えばエイリアスeolnlとeolbeを使用します。以下のクエリは、オランダとベルギーの取引先を結合する例です。

create or replace force view eolall_accounts_r
as
select *
from   AccountsIncremental@eolnl
--
-- Union allはunionより高速です。GUIDデータ型を持つId列は
-- 一意な値であるため、重複排除処理を省略できるためです。
--
union all
select *
from   AccountsIncremental@eolbe

select *
from   eolall_accounts_r@DataDictionary

このビューはスタートアップSQL(startup SQL)に設定できます。

エラーのあるビュー

ビューは作成時に構文の正しさがチェックされますが、実行はされません。forceオプションを指定すれば、構文エラーのあるSQLステートメントであっても作成することができます。選択(select)を行う時点で、構文と、現在アクティブなデータベースにおける有効性の両方がチェックされます。

Power BIでビューを使用する

データベースビューは、ドライバービュー(driver-views)と同様に、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 Editorではすぐに表示されます。

Invantive Bridge OnlineとInvantive App Onlineは、大量のデータであってもパフォーマンスをできるだけ良好に保つため、いわゆる「キャッシュ(caching)」を非常に積極的に活用しています。

このキャッシュをリセットすることで、新しいバージョンのビューの読み込みを強制することができます。キャッシュの再設定を行うと、Exact Onlineの*Incrementalテーブルなどの増分テーブルの古いバージョンを含め、すべてのキャッシュ要素が削除されます。読み込みの際、最初の1回は目に見える遅延が発生しますが、その後は以前と同程度のパフォーマンスに戻ります。

Invantive Bridge Onlineのキャッシュを再設定するには、以下の手順を実行します。

• Power BIまたはPowerBI.comの資格情報(credentials)で使用されているCloudユーザーを確認します。
• そのCloudユーザーでBridge Onlineにログインします。
• 右上のメニューを開きます。
• 下の画像に示されているように「キャッシュのリセット(Cache resetten)」を選択します。
• Power BIで利用可能なテーブルの一覧を読み込みます。
• 新しい定義が表示されない場合は?
• Bridge Online Monitoringで、該当する読み込みリクエストの「キャッシュから(Uit Cache)」にチェックが入っていないか確認してください。「キャッシュから」にチェックが入っている場合、キャッシュのリセットがうまくいかなかった可能性があります。再度キャッシュのリセットを試してください。

ドライバービュー(Driver-views)

データベースビューは、特定のドライバー用に用意された固定(同梱)のビューを、新しい機能で補完するものです。これらのドライバービューは、そのドライバーに属するテーブルのデータのみを結合できます。ドライバービューは、そのドライバーが対象とするプラットフォームの利用を簡素化しますが、変更することはできません。

データベースビューとドライバービューの違いは、以下の表のとおりです。

	データベースビュー	ドライバービュー
すべてのインストールで利用可能	いいえ	はい
ユーザーによるカスタマイズ可否	はい	いいえ
複数のデータコンテナーのテーブルを結合可能	はい	いいえ