結論から言うと、クエリがどのような動作をしているか検証することが大事。データソースにその処理のほとんどもしくは一部をプッシュしたつもりになっているだけってこともある。クエリ診断機能を使うとよいし、利用するデータソースのプロファイラも使用するとさらによい。
Table.StopFolding がよい仕事をするっていう話。
Table.StopFolding(table as table) as table
Prevents any downstream operations from being run against the original source of the data in
table.
tableのデータの元のソースに対して下流の操作が実行されないようにします。
なるほど、動作はわかった。では、使いどころはどのようなときかな🤔と思っていて、ちょうどよいケースがあった。状況をシンプルに調整し再現した。データソースは Azure SQL database。
Power Query で記述しているクエリが評価されるとき、データソースに対しては変換されたネイティブクエリで問い合わせが行われる。当然、T-SQL と SQL Server コネクタでサポートされている変換のみだ。
let
Source = Sql.Database(ServerName, DatabaseName),
Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
RemovedOtherColumns = Table.SelectColumns(
Data_Customer,
{"CustomerKey", "Gender", "Age"}
),
AddedToColumn = Table.TransformColumns(
RemovedOtherColumns,
{"Age", each _ + 1, type number}
)
in
AddedToColumn
クエリフォールディングが機能して次のネイティブクエリ(T-SQL)になる。
select [_].[CustomerKey] as [CustomerKey],
[_].[Gender] as [Gender],
[_].[Age] + 1 as [Age]
from
(
select [CustomerKey],
[Gender],
[Age]
from [Data].[Customer] as [$Table]
) as [_]
T-SQL に翻訳できない Text.Proper 関数を後続の処理に追加した時、
let
Source = Sql.Database(ServerName, DatabaseName),
Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
RemovedOtherColumns = Table.SelectColumns(
Data_Customer,
{"CustomerKey", "Surname", "Age"}
),
AddedToColumn = Table.TransformColumns(
RemovedOtherColumns,
{"Age", each _ + 1, type number}
),
CapitalizedEachWord = Table.TransformColumns(
AddedToColumn,
{"Surname", Text.Proper, type text}
)
in
CapitalizedEachWord
想像していたネイティブクエリ(T-SQL)と異なることがある。
select [$Ordered].[CustomerKey],
[$Ordered].[Surname],
[$Ordered].[Age]
from
(
select [CustomerKey],
[Surname],
[Age]
from [Data].[Customer] as [$Table]
) as [$Ordered]
order by [$Ordered].[CustomerKey]
ネイティブクエリ(T-SQL)に翻訳可能なステップ(このクエリではAddedToColumnステップ)が利用されないことがある。
そこで Table.StopFolding を用いて明示的に境界を指定する。
let
Source = Sql.Database(ServerName, DatabaseName),
Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
RemovedOtherColumns = Table.SelectColumns(
Data_Customer,
{"CustomerKey", "Surname", "Age"}
),
AddedToColumn = Table.TransformColumns(
RemovedOtherColumns,
{"Age", each _ + 1, type number}
),
StopFolding = Table.StopFolding(AddedToColumn), // 👈ココ
CapitalizedEachWord = Table.TransformColumns(
StopFolding,
{"Surname", Text.Proper, type text}
)
in
CapitalizedEachWord
後続のステップCapitalizedEachWordはクエリフォールディングが検討される範囲から外れる。
select [_].[CustomerKey] as [CustomerKey],
[_].[Surname] as [Surname],
[_].[Age] + 1 as [Age]
from
(
select [CustomerKey],
[Surname],
[Age]
from [Data].[Customer] as [$Table]
) as [_]
想像通りのネイティブクエリになる。めでたし。
Table.StopFolding ではなく Table.Buffer でも想像通りのネイティブクエリになる。ただし、データをすべてローカルキャッシュとする作用があるので、クエリの評価パフォーマンスに大きく影響する。
let
Source = Sql.Database(ServerName, DatabaseName),
Data_Customer = Source{[Schema="Data",Item="Customer"]}[Data],
RemovedOtherColumns = Table.SelectColumns(
Data_Customer,
{"CustomerKey", "Surname", "Age"}
),
AddedToColumn = Table.TransformColumns(
RemovedOtherColumns,
{"Age", each _ + 1, type number}
),
Buffer = Table.Buffer(AddedToColumn), // 👈ココ
CapitalizedEachWord = Table.TransformColumns(
Buffer,
{"Surname", Text.Proper, type text}
)
in
CapitalizedEachWord
select [_].[CustomerKey] as [CustomerKey],
[_].[Surname] as [Surname],
[_].[Age] + 1 as [Age]
from
(
select [CustomerKey],
[Surname],
[Age]
from [Data].[Customer] as [$Table]
) as [_]
思ったこと🙄
Power Query だけでデータモミモミするよりも、データソースに View を用意する/その検討が必要。それが叶わない/そこまでの必要がないなら、どのようにデータソースのリソースを消費しているかを常に確認。Table.Buffer と ネイティブクエリの記述は最終秘密兵器。秘密のままにして使わないことが望ましい。
その他