例えば、SQL Databese をデータソースとするとき、Sql.Database 関数 (Power Query) を利用してささっとデータを取得できるようになっている。このとき、クエリフォールディングがサポートされるので、集計フィルタリングマージなど Power Query エディタの操作でほぼ完了するのだ。

Sql.Database (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 / ネイティブデータベースクエリのセキュリティ
デスクトップ環境でネイティブクエリを使用するには、既定の動作では 承認が必要 となる。このセキュリティ機能自体をオフにすることは可能だけれども、うっかりやらかすときって安全機構を無視していることが多いのでは？
動作としてはネイティブクエリがデータソースに発行されるとき、未知のクエリについては承認操作が必要になるというもの。悪意の有無関係なく承認されていないクエリはブロックされる。

ここで思うのは、セキュリティ機能を無効にしたくないし、なんども承認するのも面倒だよなと。

デフォルメ強めの例を出す。

Power Query

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 (Power Query)

Value.NativeQuery(
    target as any,
    query as text,
    optional parameters as any,
    optional options as nullable record
) as any

Value.NativeQuery の parameters 引数を使ってネイティブクエリ(T-SQL) にパラメータ値を渡す。

Power Query(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 '' = ' となってしまっても、問題なくデータを取得することができない。

思ったこと🙄

ネイティブクエリを表現する文字列を単純な文字列結合で実現するのもよろしくないよなと。

Power Query workout - Value.NativeQuery

パラメータも使いたいので Value.NativeQuery

思ったこと🙄

その他