例えば、SQL Databese を データソース とするとき、Sql.Database 関数 (Power Query) を利用してささっと データ を取得できるようになっている。このとき、クエリ フォールディング がサポートされるので、集計 フィルタリング マージなど Power Query エディタ の操作でほぼ完了するのだ。
Sql.Database(
server as text,
database as text,
optional options as nullable record
) as table
クエリ フォールディング が機能しないことを前提に Power Query の クエリ を定義しておくことになるのだけど、ネイティブ クエリ の記述が必要になることは少ない。本当にそれが必要なのか充分に検討が必要という感じではある。
ダイアログ でいうと [SQL statement] に ここでは T-SQL を記述することが可能。ただし、この機能を使うことで起きることなどはよーく検討しておいてねと。SQL で記述できる、やったぜ！ っていうのは、期待していないことが追加されることもある。
ここの Limitations and issues がとても大事。
Query folding / クエリ フォールディング
行や列の絞り込みや マージ など多くの操作が データソース に プッシュできなくなる。なので、可能な限り処理が データソース で行われるように ネイティブ クエリ を定義することが必要。
Native database query security / ネイティブ データベース クエリのセキュリティ
デスクトップ 環境で ネイティブ クエリ を使用するには、既定の動作では 承認が必要 となる。この セキュリティ 機能自体を オフ にすることは可能だけれども、うっかりやらかすときって安全機構を無視していることが多いのでは？
動作としては ネイティブ クエリ が データソース に発行されるとき、未知の クエリ については承認操作が必要になるというもの。悪意の有無関係なく承認されていない クエリ は ブロック される。
ここで思うのは、セキュリティ 機能を無効にしたくないし、なんども承認するのも面倒だよなと。
デフォルメ 強めの例を出す。
let
Source = Sql.Database(
SeverName, DatabaseName,
[
Query = "SELECT t.区分ID,t.区分名 FROM dbo.商品区分 as t "
& "WHERE t.区分名 ='" & CategoryName & "'"
]
)
in
Source
クエリ名 もしくは パラメータ名: CategoryName の値が新たに登場するたび承認が必要になる。
CategoryName の値が不運にも ' OR '' = ' となってしまったときどうなるだろうか。大事にならないとは思うけど、まぁよくないよね。少なくとも、大丈夫だと断言できるに足る説明納得ができるかどうかだ。
パラメータも使いたいので Value.NativeQuery
Value.NativeQuery(
target as any,
query as text,
optional parameters as any,
optional options as nullable record
) as any
Value.NativeQuery の parameters 引数を使って ネイティブ クエリ(T-SQL) に パラメータ 値を渡す。
let
Target = Sql.Database(
SeverName, DatabaseName
),
Query = Value.NativeQuery(
Target,
"SELECT t.区分ID,t.区分名 FROM dbo.商品区分 as t "
& "WHERE t.区分名 = @prm1",
[
prm1 = CategoryName
]
)
in
Query
ネイティブ クエリ は都度変わるということがなくなるから承認は一回だけ。
CategoryName の値が不運にも ' OR '' = ' となってしまっても、問題なくデータを取得することができない。
思ったこと🙄
ネイティブ クエリ を表現する文字列を単純な文字列結合で実現するのもよろしくないよなと。
その他