データ プライバシー ファイアウォールの背景

Power Query を短期間でも使用したことがある人なら、おそらく経験したことがあるであろう、ある問題があります。 それは、クエリの実行時に突然エラーが発生して、オンライン検索をしても、クエリを調整しても、キーボードを叩いてみても、一切解決方法が見つからないという問題です。 具体的には、次のようなエラーです。

Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

または、次のような場合もあります。

Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

これらの Formula.Firewall エラーは、Power Query のデータ プライバシー ファイアウォール (単にファイアウォールとも呼ばれます) によって引き起こされます。この機能は、世界中のデータ アナリストを苛立たせるためだけに存在しているように思えるかもしれません。 しかし、実際のところ、ファイアウォールには重要な目的があります。 この記事では、この機能をより深く理解していただくために、その仕組みについて詳しく説明していきます。 機能について理解を深めれば、今後ファイアウォール エラーに遭遇した際に、問題より効果的に診断し、修正できる可能性があります。

紹介

データ プライバシー ファイアウォールの目的はシンプルです。この機能は、Power Query によって、ソース間で意図しないデータ漏えいが発生するのを防ぐために存在します。

なぜこれが必要かというと、 開発者が作成した M によって、OData フィードに SQL 値が渡される場合があるからです。 しかし、これは意図的なデータ漏えいです。 マッシュアップの作成者は、それが実行されることを知っていることになります (少なくとも、知っているべきです)。 それではなぜ、意図しないデータ漏えいに対する保護措置が必要なのでしょうか?

その理由は、 フォールディングです。

フォールディングとは?

フォールディング とは、M 内の式 (フィルター、名前変更、結合など) を、生のデータ ソース (SQL、OData など) に対する操作へと変換することを意味する用語です。 Power Query のメリットの大部分は、ユーザーがユーザー インターフェイスを通じて実行した操作を、複雑な SQL やその他のバックエンド データ ソース言語に自動的に変換できるという事実に支えられています。使用されている言語をユーザーが理解している必要はありません。 ユーザーは、ネイティブなデータ ソース操作のパフォーマンス上の利点を、使いやすい UI を通じて享受することができます。すべてのデータ ソースを、一般的なコマンド セットを使って変換できるのです。

PQ では、フォールディングの際、あるソースからデータを取得して別のソースへと渡すことが、特定のマッシュアップを実行する最も効率的な方法だと判断される場合があります。 たとえば、通常、小さな CSV ファイルを大きな SQL テーブルに結合する場合、CSV ファイルを読み取り、SQL テーブル全体を読み取った後、それらをローカル コンピューター上で結合するのは非効率です。 CSV データを SQL ステートメント内にインライン化し、SQL データベースに結合を実行させるほうが効率的です。

これこそが、意図しないデータ漏えい原因となりうるのです。

たとえば、従業員の社会保障番号を含んだ SQL データを外部の OData フィードの結果と結合した後に、SQL の社会保障番号が OData サービスに送信されることにふと気づいたとしたらどうでしょう。 まずい状況ですよね?

そのような事態を回避することが、ファイアウォールの目的なのです。

それはどのように機能しますか?

ファイアウォールは、1 つのソースから、データが意図せずに別のソースへと送信されるのを防ぐために存在します。 実にシンプルです。

では、このミッションはどのようにして達成されるのでしょうか?

これは、M クエリをパーティションと呼ばれるものに分割し、次のルールを適用することで実現されます。

  • パーティションは、互換性のあるデータ ソースにアクセスするか、他のパーティションを参照することができるが、それら両方を参照することはできない。

シンプルですが、これだけではまだわかりにくいでしょう。 パーティションとは何でしょうか? 2 つのデータ ソースに "互換性がある" とはどういうことでしょうか? また、パーティションからデータ ソースにアクセスしたりパーティションを参照したりする必要がある場合に、なぜファイアウォールが必要になるのでしょうか?

これら疑問ごとに、上記のルールの意味を確認していきましょう。

パーティションとは何か?

最も基本的なレベルで言うと、パーティションは 1 つ以上のクエリ ステップのコレクションです。 最も小規模なパーティションは (少なくとも現在の実装では) 1 つのステップによるものです。 きわめて大きなパーティションになると、複数のクエリにまたがることもあります (詳細については、後述します)。

ステップがわからない場合は、 [適用したステップ] ウィンドウでクエリを選択すると、Power Query エディター ウィンドウの右側でそれらを確認できます。 ステップを見れば、データを最終的な形に変換するために行ったすべての操作がわかります。

他のパーティションを参照するパーティション

ファイアウォールをオンにした状態でクエリが評価された場合、ファイアウォールによって、クエリとそのすべての依存関係がパーティション (一連のステップ) に分割されます。 あるパーティションが別のパーティション内の何かを参照している場合は、ファイアウォールによって、その参照が Value.Firewall という特殊な関数の呼び出しへと置き換えられます。 つまり、ファイアウォールを使用した場合、パーティション間でのランダムなアクセスはできません。 すべての参照は、ファイアウォールを通過するために変更を加えられます。 ファイアウォールはゲートキーパーだと考えてください。 あるパーティションから別のパーティションを参照する場合、それを行うにはファイアウォールからアクセス許可を得る必要があります。また、参照先のデータを参照元のパーティションに渡せるかどうかは、ファイアウォールによって制御されます。

これだけでは説明が抽象的すぎるかもしれないので、例を見ていきましょう。

SQL データベースから一部のデータを取得する、Employees というクエリがあるとします。 また、Employees を参照する別のクエリ (EmployeesReference) もあるとします。

shared Employees = let
    Source = Sql.Database(…),
    EmployeesTable = …
in
    EmployeesTable;

shared EmployeesReference = let
    Source = Employees
in
    Source;

これらのクエリは、2 つのパーティションに分割されます。1 つは Employees クエリ用、もう 1 つは EmployeesReference クエリ (Employees パーティションを参照するクエリ) 用です。 ファイアウォールをオンにして評価した場合、これらのクエリは次のように書き換えられます。

shared Employees = let
    Source = Sql.Database(…),
    EmployeesTable = …
in
    EmployeesTable;

shared EmployeesReference = let
    Source = Value.Firewall("Section1/Employees")
in
    Source;

Employees クエリへのシンプルな参照が、Value.Firewall の呼び出しへと置き換えられている点に注目してください。これに、Employees クエリの完全な名前が指定されています。

EmployeesReference が評価されると、Value.Firewall("Section1/Employees") の呼び出しがファイアウォールによってインターセプトされます。要求されたデータが EmployeesReference パーティションに渡されるかどうか (およびその方法) は、ファイアウォールによって制御されます。 ファイアウォールでは、要求の拒否、要求されたデータのバッファー処理 (元のデータ ソースへのさらなるフォールディングが発生するのを防ぐ) など、任意の数の処理を実行できます。

このように、パーティション間を流れるデータはファイアウォールによって制御されます。

データ ソースに直接アクセスするパーティション

たとえば、1 つのステップを含んだ、Query1 というクエリを定義したとします (この単一ステップのクエリは、1 つのファイアウォール パーティションに対応することになります)。この 1 つのステップで、2 つのデータ ソース (SQL データベース テーブルと CSV ファイル) にアクセスするとします。 この場合、ファイアウォールはどのように機能するでしょうか?パーティション参照が存在せず、インターセプトの対象となる Value.Firewall の呼び出しもありません。 前に説明した規則を再確認しましょう。

  • パーティションは、互換性のあるデータ ソースにアクセスするか、他のパーティションを参照することができるが、それら両方を参照することはできない。

パーティションが 1 つでデータ ソースが 2 つあるこのクエリを実行するには、その 2 つのデータ ソースに "互換性" がある必要があります。 つまり、両方のソース間でデータを共有できる必要があります。 Power Query UI の観点から言うと、SQL データ ソースと CSV データ ソースのプライバシー レベルが、両方とも [公開] (または両方とも [組織]) になっている必要があります。 両方とも [非公開] としてマークされている場合や、一方が [公開] でもう一方が [組織] の場合、またはその他のプライバシー レベルの組み合わせでマークされている場合、両者を同じパーティションで評価するのは安全ではありません。 そのようにすると、(フォールディングによって) 安全でないデータ漏えいが発生する可能性があります。また、ファイアウォールによってそれを防ぐ方法はありません。

同じパーティション内で互換性のないデータ ソースにアクセスしようとすると、どうなるでしょうか?

Formula.Firewall: Query 'Query1' (step 'Source') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

この記事の冒頭で示したエラー メッセージの 1 つですが、先ほどよりは、この意味が理解できるのではないでしょうか。

この互換性の要件 、当該のパーティション内でのみ適用されるという点に注意してください。 パーティションが他のパーティションを参照している場合は、参照先パーティションのデータ ソースが相互に互換性を持っている必要はありません。 なぜなら、ファイアウォールによってデータをバッファー処理できるので、元のデータ ソースに対するそれ以上のフォールディングは回避されるからです。 データはメモリに読み込まれ、元からそこにあるもののように扱われます。

両方を参照できない理由

たとえば、1 つのステップ (1 つのパーティションに対応) を含んだクエリを定義して、他の 2 つのクエリ (つまり、他の 2 つのパーティション) にアクセスするとします。 その際、同じステップ内で、SQL データベースにも直接アクセスしたいとしたらどうでしょう? あるパーティションから他のパーティションを参照する場合、互換性のあるデータ ソースに直接アクセスすることができないのはなぜなのでしょうか?

先にも説明したように、あるパーティションから別のパーティションを参照する場合、ファイアウォールは、そのパーティションに送られるすべてのデータのゲートキーパーとして機能します。 そのためには、どのようなデータが許可されるのかを、ファイアウォールが制御できる必要があります。 パーティション内でアクセスされるデータ ソースに加えて、他のパーティションから送られてくるデータも存在する場合、ファイアウォールはゲートキーパーの役目を果たせなくなります。送られてきたデータが、内部でアクセスされるデータ ソースのいずれかに、知らない間に漏えいする可能性があるからです。 そのため、ファイアウォールでは、他のパーティションにアクセスするパーティションがデータ ソースに直接アクセスすることは禁止されます。

それでは、あるパーティションが他のパーティションを参照する場合に、データ ソースにも直接アクセスしようとした場合にはどうなるのでしょうか?

Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

この記事の冒頭で示したもう 1 つのエラー メッセージですが、先ほどよりは、この意味が理解できるのではないでしょうか。

パーティションの詳細

これまでの内容からもおわかりかと思いますが、クエリがどのようにパーティション分割されるかは、きわめて重要な問題です。 他のクエリを参照するステップと、データ ソースにアクセスするステップがある場合、おわかりかもしれませんが、特定の場所にパーティション境界を引くとファイアウォール エラーが発生し、別の場所に引くとクエリを実行できるという状況が生まれます。

それでは、クエリは厳密にはどのようにしてパーティション分割されるのでしょうか?

このセクションは、ファイアウォール エラーが発生する理由と、その解決方法 (可能な場合) を理解するうえで、最も重要なセクションかもしれません。

次に示すのは、パーティション分割ロジックの概要です。

  • 初期パーティション分割
    • 各クエリ内のステップごとにパーティションを作成します
  • 静的フェーズ
    • このフェーズは評価結果には依存しません。 代わりに、クエリがどのように構造化されているかに依存します。
    • パラメーターのトリミング
      • パラメーター的パーティション、つまり次のいずれかのパーティションをトリミングします。
        • 他のパーティションを参照しない
        • 関数呼び出しを含んでいない
        • 循環型ではない (つまり、それ自身を参照しない)
      • パーティションを "削除" すると、そのパーティションが参照している他のパーティションも実質的にその対象に含まれるので注意してください。
      • パラメーター パーティションをトリミングすることで、データ ソース関数の呼び出し内で使用されるパラメーター参照 (たとえば、Web.Contents(myUrl)) を機能させることができます ("パーティションからデータ ソースと他のステップを参照することはできない" というエラーを回避できます)。
    • グループ化 (静的)
      • パーティションがマージされます (次のパーティション間の分離は維持されます)。
        • 異なるクエリ内のパーティション
        • 他のパーティションを参照するパーティションと参照しないパーティション
  • 動的フェーズ
    • このフェーズは評価結果に依存します (さまざまなパーティションによってアクセスされるデータ ソースに関する情報など)。
    • トリミング
      • 次のすべての要件を満たすパーティションをトリミングします。
        • データ ソースにアクセスしない
        • データ ソースにアクセスするパーティションを参照しない
        • 循環型ではない
    • グループ化 (動的)
      • 不要なパーティションがトリミングされたら、可能な限り大きいソース パーティションを作成します。
      • 各入力が次に該当する場合は、すべてのパーティションをそれらの入力パーティションとマージします。
        • 同じクエリの一部である
        • 他のパーティションを参照しない
        • 現在のパーティションによってのみ参照される
        • クエリの結果 (つまり、最後のステップ) ではない
        • 循環型ではない

上記の内容が意味すること

上記の複雑なロジックがどのように機能するのかを、例を使って見ていきましょう。

次に示のはサンプル シナリオです。 これは、テキスト ファイル (Contacts) と SQL データベース (Employees) の非常にシンプルなマージです。SQL サーバーがパラメーター (DbServer) となっています。

3 つのクエリ

この例で使用する 3 つのクエリの M コードを次に示します。

shared DbServer = "montegoref6" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true];
shared Contacts = let

    Source = Csv.Document(File.Contents("C:\contacts.txt"),[Delimiter="   ", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ContactID", Int64.Type}, {"NameStyle", type logical}, {"Title", type text}, {"FirstName", type text}, {"MiddleName", type text}, {"LastName", type text}, {"Suffix", type text}, {"EmailAddress", type text}, {"EmailPromotion", Int64.Type}, {"Phone", type text}, {"PasswordHash", type text}, {"PasswordSalt", type text}, {"AdditionalContactInfo", type text}, {"rowguid", type text}, {"ModifiedDate", type datetime}})

in

    #"Changed Type";
shared Employees = let

    Source = Sql.Databases(DbServer),

    AdventureWorks = Source{[Name="AdventureWorks"]}[Data],

    HumanResources_Employee = AdventureWorks{[Schema="HumanResources",Item="Employee"]}[Data],

    #"Removed Columns" = Table.RemoveColumns(HumanResources_Employee,{"HumanResources.Employee(EmployeeID)", "HumanResources.Employee(ManagerID)", "HumanResources.EmployeeAddress", "HumanResources.EmployeeDepartmentHistory", "HumanResources.EmployeePayHistory", "HumanResources.JobCandidate", "Person.Contact", "Purchasing.PurchaseOrderHeader", "Sales.SalesPerson"}),

    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"ContactID"},Contacts,{"ContactID"},"Contacts",JoinKind.LeftOuter),

    #"Expanded Contacts" = Table.ExpandTableColumn(#"Merged Queries", "Contacts", {"EmailAddress"}, {"EmailAddress"})

in

    #"Expanded Contacts";

次に示すのは、依存関係を示した概要ビューです。

クエリの依存関係の図。

パーティション分割を行う

少し掘り下げて、図にステップを追加し、パーティション分割のロジックについて説明していきましょう。 次の図は、3 つのクエリを示したものです。初期のファイアウォール パーティションが緑色で示されています。 各ステップが独自のパーティションで開始されるという点に注意してください。

初期のファイアウォール パーティション。

次に、パラメーター パーティションをトリミングします。 これにより、DbServer が Source パーティションに暗黙的に含められます。

トリミングされたファイアウォール パーティション。

次に、静的グループ化を実行します。 この操作では、別々のクエリ内のパーティション間で分離が維持されます (たとえば、Employees の最後の 2 つのステップは、Contacts のステップとはグループ化されません)。また、他のパーティションを参照するパーティション (Employees の最後の 2 つのステップなど) と、そうでないパーティション (Employees の最初の 3 つのステップなど) との間でも分離が維持されます。

静的グループ化を行った後のファイアウォール パーティション。

次に、動的フェーズに入ります。 このフェーズでは、上記の静的パーティションが評価されます。 データ ソースにアクセスしないパーティションはトリミングされます。 その後、パーティションはグループ化され、可能な限り大きいソース パーティションが作成されます。 ただし、このサンプル シナリオの場合、残りのパーティションはすべてデータ ソースにアクセスするので、これ以上のグループ化は実行できません。 したがって、このサンプルのパーティションは、このフェーズでは変更されません。

考えてみましょう

Contacts クエリがテキスト ファイルから取得されるのではなく、( [データの入力] ダイアログなどを通じて) M 内でハードコードされた場合にはどうなるのかを見ていきましょう。

この場合、Contacts クエリはデータ ソースにアクセスしません。 そのため、動的フェーズの最初の部分でトリミングされます。

動的フェーズでトリミングされた後のファイアウォール パーティション。

Contacts パーティションが削除されると、Employees の最後の 2 つのステップは、Employees の最初の 3 つのステップを含んだパーティションを除き、パーティションを参照しなくなります。 したがって、これら 2 つのパーティションはグループ化されます。

結果のパーティションは次のようになります。

最終的なファイアウォール パーティション。

例: データ ソース間でデータを渡す

ここまでは、抽象的な説明をしてきました。 ここからは、ファイアウォール エラーが発生しやすい一般的なシナリオと、その解決手順を見ていきましょう。

たとえば、Northwind OData サービスからある会社名を検索し、その会社名を使って Bing 検索を実行したいとします。

まずは、会社名を取得する Company クエリを作成します。

let
    Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/", null, [Implementation="2.0"]),
    Customers_table = Source{[Name="Customers",Signature="table"]}[Data],
    CHOPS = Customers_table{[CustomerID="CHOPS"]}[CompanyName]
in
    CHOPS

次に、Company を参照する Search クエリを作成し、それを Bing に渡します。

let
    Source = Text.FromBinary(Web.Contents("https://www.bing.com/search?q=" & Company))
in
    Source

この時点で、問題が発生します。 Search を評価すると、ファイアウォール エラーが発生します。

Formula.Firewall: Query 'Search' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

これは、Search の Source ステップがデータ ソース (bing.com) を参照していて、同時に別のクエリ/パーティション (Company) も参照しているためです。 これは前述の規則に違反しています ("パーティションは、互換性のあるデータ ソースにアクセスするか、他のパーティションを参照することができるが、それら両方を参照することはできない")。

どうすればよいでしょうか。 1 つの方法として、ファイアウォールを完全に無効にする方法があります ( [プライバシー レベルを無視すると、パフォーマンスが向上する場合があります] というラベルが付いた [プライバシー] オプションを使用します)。 しかし、ファイアウォールを有効のままにしたい場合にはどうすればよいでしょうか?

ファイアウォールを無効にせずにエラーを解決するには、次のように、Company と Search を 1 つのクエリに結合します。

let
    Source = OData.Feed("https://services.odata.org/V4/Northwind/Northwind.svc/", null, [Implementation="2.0"]),
    Customers_table = Source{[Name="Customers",Signature="table"]}[Data],
    CHOPS = Customers_table{[CustomerID="CHOPS"]}[CompanyName],
    Search = Text.FromBinary(Web.Contents("https://www.bing.com/search?q=" & CHOPS))
in
    Search

これで、すべてが 1 つのパーティション内 で発生 するようになりました。 2 つのデータ ソースのプライバシー レベルに互換性があれば、ファイアウォールでの問題は起こらず、エラーは発生しません。

まとめ

このトピックは、掘り下げようと思えばさらに説明することもできますが、今回は入門的な内容なので、説明は以上です。 ファイアウォールについて理解が深まったのではないでしょうか。今回得た知識は、今後ファイアウォール エラーが発生した場合に、その理由を理解し、問題を修正するのに役立つでしょう。