Power Query カスタムコネクタのネイティブクエリサポート
注意
この記事では、カスタムコネクタのネイティブクエリサポート、および クエリフォールディング の実装に関する高度なトピックについて説明します。 この記事は、あなたがこれらの概念についてすでに実用的な知識を持っていることを前提としています。
Power Queryカスタムコネクタの詳細については、 Power Query SDKの概要にアクセスしてください。
Power Queryでは、データソースに対してカスタムネイティブクエリを実行し、探しているデータを取得することができます。 また、このプロセスとPower Query内部で行われるその後の変換プロセスを通して、クエリのフォールディングを維持する機能を有効にすることもできます。
この記事の目的は、カスタム・コネクターにこのような機能を実装する方法を紹介することです。
この記事では、SQL ODBCドライバをデータソースとして使用するサンプルを出発点としています。 ネイティブクエリ機能の実装は、現在SQL-92標準に準拠したODBCコネクタでのみサポートされています。
サンプルのコネクタは SQL Server Native Client 11.0 ドライバを使用しています。 このチュートリアルに従うために、このドライバーがインストールされていることを確認してください。
サンプルコネクタの完成版は、GitHubリポジトリのFinishフォルダから見ることもできます。
サンプルのコネクターの SqlCapabilities
レコードに、 Sql92Translation
という名前のレコード・フィールドと、 PassThrough という値のレコード・フィールドがあります。 この新しいフィールドは、Power Queryを使用してバリデーションなしでネイティブクエリを渡すために必要です。
SqlCapabilities = Diagnostics.LogValue("SqlCapabilities_Options", defaultConfig[SqlCapabilities] & [
// Place custom overrides here
// The values below are required for the SQL Native Client ODBC driver, but might
// not be required for your data source.
SupportsTop = false,
SupportsDerivedTable = true,
Sql92Conformance = 8 /* SQL_SC_SQL92_FULL */,
GroupByCapabilities = 4 /* SQL_GB_NO_RELATION */,
FractionalSecondsScale = 3,
Sql92Translation = "PassThrough"
]),
先に進む前に、このフィールドがコネクタに表示されていることを確認してください。 そうでないと、コネクタで宣言されていないためにサポートされていないケイパビリティを使うことになったときに、後で警告やエラーに直面することになります。
コネクタ ファイルをビルドし(.mez または .pqx として)、Power BI Desktop にロードして手動でテストし、ネイティブ クエリのターゲットを定義します。
注意
今回は、AdventureWorks2019サンプルデータベースを使用します。 しかし、選択したデータベースの仕様が決まったら、好きなSQL Serverデータベースに従って必要な変更を加えることができます。
この項目でネイティブ・クエリー・サポートは、ユーザーに3つの値を入力するよう要求することによって実行されます:
- サーバー名
- データベース名
- データベースレベルでのネイティブクエリ
Power BI Desktop 内で、Get Data エクスペリエンスに移動し、SqlODBC Sample という名前のコネクタを見つけます。
コネクタ・ダイアログでは、サーバーのパラメータとデータベース名を入力します。 [OK] をクリックします。
新しいナビゲーター・ウィンドウが表示されます。 Navigatorでは、サーバーとその中のデータベースの階層ビューを表示するSQLドライバーからのネイティブ・ナビゲーション動作を見ることができます。 AdventureWorks2019データベースを右クリックし、データの変換を選択します。
すべてのネイティブクエリはデータベースレベルで実行されるため、この選択によりPower Queryエディタが表示され、ネイティブクエリのターゲットがプレビューされます。 最後のステップの数式バーを見て、ネイティブクエリを実行する前に、コネクタがどのようにネイティブクエリのターゲットにナビゲートすべきかをよりよく理解してください。 この場合、数式バーには以下の情報が表示される:
= Source{[Name="AdventureWorks2019",Kind="Database"]}[Data]
Sourceは前のステップの名前で、この場合、パラメータが渡されたコネクタの公開関数になります。 リストとその中のレコードは、テーブルを特定の行にナビゲートするのに役立つ。 行は、NameフィールドがAdventureWorks2019と等しく、KindフィールドがDatabaseと等しいというレコードの条件で定義されます。 行が見つかったら、リスト{}
の外側の[Data]
によって、Power QueryはDataフィールド(この場合はテーブル)内の値にアクセスすることができます。 このナビゲーションをよりよく理解するには、前のステップ(ソース)に戻ることができます。
これでターゲットが特定されたので、フォーミュラバーの fx アイコンを選択して、ナビゲーションステップの後にカスタムステップを作成する。
数式バー内の数式を以下の数式に置き換え、 Enterを選択する。
= Value.NativeQuery( AdventureWorks2019_Database, "SELECT TOP (1000) *
FROM [Person].[Address]")
この変更を適用すると、データ・ソースに対してネイティブ・クエリを実行する許可を求める警告が数式バーの下に表示されます。
Edit Permissionを選択する。 新しいネイティブ・データベース・クエリダイアログが表示され、ネイティブ・クエリを実行する可能性について警告します。 この場合、このSQL文は安全であることがわかっているので、 実行 を選択してコマンドを実行する。
クエリを実行すると、Power Query エディタにクエリのプレビューが表示されます。 このプレビューでは、コネクタがネイティブ・クエリを実行できることを検証します。
前のセクションで集めた情報をもとに、今度はその情報をコネクタのコードに変換することが目標です。
この変換を達成する方法は、新しい NativeQueryProperties レコード・フィールドをコネクタの Publish レコード(この場合は SqlODBC.Publish
レコード)に追加することです。 NativeQueryProperties
レコードは、コネクタが Value.NativeQuery
関数とやり取りする方法を定義する上で重要な役割を果たします。
新記録フィールドは2つのフィールドからなる:
- NavigationSteps: このフィールドは、ナビゲーションがどのように実行されるか、またはコネクタによって処理されるかを定義します。 これには、
Value.NativeQuery
関数を使用してクエリを実行する特定のデータに移動する手順の概要を示すレコードの一覧が含まれています。 それぞれのレコード内では、このようなナビゲーションが希望するターゲットに到達するためには、どのようなパラメータが必要であるかを定義します。 - DefaultOptions: このフィールドは、
Value.NativeQuery
オプションレコードに特定のオプションパラメータをどのように含めるか、または追加するかを特定するのに役立ちます。 データ ソースに対してクエリを実行するときに使用できる一連のデフォルトのオプションが用意されています。
ナビゲーションのステップは2つのグループに分類できます。 最初の値には、エンドユーザーが入力する値(この場合はサーバー名やデータベース名など)が含まれます。 2番目は、データ取得中にユーザーに表示されないフィールド名など、特定のコネクタ実装によって導出される値を含みます。 これらのフィールドには、 Name
、 Kind
、 Data
などがあり、コネクタの実装に依存します。
このケースでは、2つのフィールドからなるナビゲーション・ステップが1つだけありました:
- Name:このフィールドはエンドユーザーから渡されたデータベースの名前である。 この場合、
AdventureWorks2019
であったが、このフィールドは常にエンドユーザーがデータ取得時に入力したものをそのまま渡すべきである。 - Kind:このフィールドはエンドユーザーには見えない情報で、コネクタやドライバの実装に固有のものです。 この場合、この値は、アクセスされるべきオブジェクトのタイプを特定します。 この実装では、このフィールドは文字列で構成される固定値になります
Database
。
このような情報は次のコードに変換されます。 このコードは、 SqlODBC.Publish
レコードに新しいフィールドとして追加する必要があります。
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
}
]
重要
フィールドの名前は大文字と小文字が区別され、上記のサンプルに示されているように使用する必要があります。 フィールドに渡されるすべての情報 (ConstantValue
、 IndexName
または FieldDisplayName
) は、コネクタの M コードから派生する必要があります。
ユーザーが入力した値から渡される値には、FieldDisplayName
と IndexName
のペアを使用できます。 固定または事前定義されていて、エンドユーザーが渡すことができない値の場合は、ConstantValue
と IndexName
のペアを使用できます。 この意味において、NavigationSteps レコードは 2 つのフィールドで構成されます。
- Indices:
Value.NativeQuery
関数のターゲットを含むレコードに移動するために使用するフィールドと値を定義します。 - FieldAccess: ターゲット (通常はテーブル) を保持するフィールドを定義します。
DefaultOptions
フィールドを使用すると、コネクタのネイティブ クエリ機能を使用するときに、オプションのパラメーターを Value.NativeQuery
関数に渡すことができます。
ネイティブ クエリの後にクエリ フォールディングを保持するには、コネクタにクエリ フォールディング機能があると仮定して、EnableFolding = true
に次のサンプル コードを使用できます。
NativeQueryProperties = [
NavigationSteps = {
[
Indices = {
[
FieldDisplayName = "database",
IndexName = "Name"
],
[
ConstantValue = "Database",
IndexName = "Kind"
]
},
FieldAccess = "Data"
]
},
DefaultOptions = [
EnableFolding = true
]
]
これらの変更を加えたら、コネクタを構築し、テストと検証のために Power BI Desktop に読み込みます。
新しいカスタム コネクタを配置した Power BI Desktop で、データの取得 エクスペリエンスからコネクタを起動します。 コネクタを起動すると、ダイアログに Native query という名前の長いテキスト フィールドが表示され、括弧内にコネクタが機能するために必要なフィールドが含まれていることがわかります。 コネクタのテスト時に以前に入力したサーバー、データベース、SQL ステートメントと同じ値を入力します。
OK を選択すると、実行されたネイティブ クエリのテーブル プレビューが新しいダイアログに表示されます。
[OK] を選択します。 新しいクエリが Power Query エディター内に読み込まれるので、必要に応じてコネクタのさらなるテストを行うことができます。
注意
コネクタにクエリ フォールディング機能があり、 Value.NativeQuery
のオプション レコードの一部として EnableFolding=true
を明示的に定義している場合は、Power Query エディターでさらに変換がソースにフォールド バックするかどうかを確認して、コネクタをさらにテストできます。