TripPin パート 6 - スキーマ
このマルチパート チュートリアルでは、Power Query 用の新しいデータ ソース拡張機能の作成について説明します。 このチュートリアルは順番に実行することを目的としています。各レッスンは前のレッスンで作成したコネクタに基づいて構築され、コネクタに新しい機能が段階的に追加されます。
このレッスンの内容:
- REST API の固定スキーマを定義する
- 列のデータ型を動的に設定する
- 列の欠落による変換エラーを回避するためのテーブル構造を適用する
- 結果セットの列を非表示にする
標準の REST API と比べた OData サービスの大きな利点の 1 つが、$metadata 定義です。 $metadata のドキュメントでは、このサービスで取得できるデータについて、すべてのエンティティ (テーブル) とフィールド (列) のスキーマを含めて説明しています。 OData.Feed 関数は、このスキーマ定義を使用してデータ型情報を自動的に設定します。そのため、エンド ユーザーは (Json.Document の場合のように) すべてのテキスト フィールドと数値フィールドを取得する代わりに、日付、整数、時刻などを取得します。 全体的に優れたユーザー エクスペリエンスを提供します。
多くの REST API には、プログラムによってスキーマを特定する方法がありません。 このような場合は、使用するコネクタ内にスキーマ定義を含める必要があります。 このレッスンでは、テーブルごとに簡単なハードコーディングされたスキーマを定義し、サービスから読み取ったデータにスキーマを適用します。
Note
ここで説明する方法は、多くの REST サービスに対して有効です。 この後のレッスンでは、この方法に基づいて、構造化された列 (レコード、リスト、テーブル) にスキーマを再帰的に適用し、CSDL または JSON スキーマのドキュメントからスキーマ テーブルをプログラムによって生成できるサンプル実装を示します。
全体として、コネクタによって返されるデータにスキーマを適用すると、次のような複数の利点があります。
- 正しいデータ型の設定
- エンド ユーザーに表示する必要のない列 (内部 ID や状態情報など) の削除
- 応答から欠落している可能性のある列を追加することにより、データの各ページの構造を確実に同じにする (REST API でフィールドを null にする必要があることを示す一般的な方法)
Table.Schema を使用した既存のスキーマの表示
前のレッスンで作成したコネクタには、TripPin サービスの 3 つのテーブル (Airlines、 Airports、および People) が表示されます。 次のクエリを実行して、Airlines テーブルを表示します。
let
source = TripPin.Contents(),
data = source{[Name="Airlines"]}[Data]
in
data
結果には 4 つの列が返されます。
- @odata.id
- @odata.editLink
- AirlineCode
- 名前
「@odata.*」列は OData プロトコルの一部であり、コネクタのエンド ユーザーに表示する必要があるものではありません。 AirlineCode と Name が、保持する 2 つの列です。 テーブルのスキーマを (便利な Table.Schema 関数を使用して) 確認すると、テーブル内のすべての列のデータ型が Any.Type であることがわかります。
let
source = TripPin.Contents(),
data = source{[Name="Airlines"]}[Data]
in
Table.Schema(data)
Table.Schema からは、テーブル内の列に関する多くのメタデータが返されます。これには、名前、位置、型情報のほか、多くの詳細プロパティ (Precision、Scale、MaxLength など) が含まれます。
この後のレッスンで、これらの詳細プロパティを設定するための設計パターンを示しますが、ここでは指定の型 (TypeName) とプリミティブ型 (Kind)、それに列の値が null であるかどうか (IsNullable) についてのみ考慮する必要があります。
単純なスキーマテーブルの定義
このスキーマ テーブルは 2 つの列で構成されます。
| 列 | 詳細 |
|---|---|
| 名前 | 列の名前。 これは、サービスによって返される結果の名前と一致する必要があります。 |
| Type | 設定する M データ型。 これは、プリミティブ型 (text、number、datetime など)、または指定の型 (Int64.Type、Currency.Type など) にすることができます。 |
Airlines テーブルのハードコーディングされたスキーマ テーブルでは、その AirlineCode および Name 列を text に設定し、次のようになります。
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
});
Airports テーブルには、保持する 4 つのフィールド (record 型のものを含む) があります。
Airports = #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
});
最後に、People テーブルには 7 つのフィールドがあり、これにはリスト (Emails、AddressInfo)、"null を許容する" 列 (Gender)、"指定の型" を持つ列 (Concurrency) が含まれます。
People = #table({"Name", "Type"}, {
{"UserName", type text},
{"FirstName", type text},
{"LastName", type text},
{"Emails", type list},
{"AddressInfo", type list},
{"Gender", type nullable text},
{"Concurrency", Int64.Type}
})
SchemaTransformTable ヘルパー関数
次に示す SchemaTransformTable ヘルパー関数は、データにスキーマを適用するために使用されます。 使用できるパラメーターは次のとおりです。
| パラメーター | 型 | 説明 |
|---|---|---|
| テーブル | テーブル | スキーマの適用対象であるデータのテーブル。 |
| schema | テーブル | 次の型を持つ列の情報の読み取り元スキーマ テーブル: type table [Name = text, Type = type]。 |
| enforceSchema | 数値 | (省略可能) 関数の動作を制御する列挙型。 既定値 ( EnforceSchema.Strict = 1) を使用すると、欠落している列を追加し、余分な列を削除することによって、出力テーブルと提供されたスキーマ テーブルとの一致が保証されます。 EnforceSchema.IgnoreExtraColumns = 2 オプションを使用すると、結果に含まれる余分な列を保持できます。 EnforceSchema.IgnoreMissingColumns = 3 を使用すると、欠落している列と余分な列の両方が無視されます。 |
この関数のロジックは次のようになります。
- ソース テーブルに欠落している列があるかどうかを確認します。
- 余分な列があるかどうかを確認します。
- 構造化列 (
list、record、table型のもの) と、type anyに設定されている列を無視します。 - TransformColumnTypes を使用して各列の型を設定します。
- スキーマ テーブルに表示される順序に基づいて列の順序を変更します。
- Value.ReplaceType を使用して、テーブル自体に対して型を設定します。
Note
テーブルの型を設定する最後の手順によって、クエリ エディターで結果を表示するときに、Power Query UI で型情報を推論する必要がなくなります。 これにより、前のチュートリアルの最後に確認された、重複する要求の問題が解決します。
次のヘルパー コードをコピーして、ご自身の拡張機能に貼り付けることができます。
EnforceSchema.Strict = 1; // Add any missing columns, remove extra columns, set table type
EnforceSchema.IgnoreExtraColumns = 2; // Add missing columns, do not remove extra columns
EnforceSchema.IgnoreMissingColumns = 3; // Do not add or remove columns
SchemaTransformTable = (table as table, schema as table, optional enforceSchema as number) as table =>
let
// Default to EnforceSchema.Strict
_enforceSchema = if (enforceSchema <> null) then enforceSchema else EnforceSchema.Strict,
// Applies type transforms to a given table
EnforceTypes = (table as table, schema as table) as table =>
let
map = (t) => if Type.Is(t, type list) or Type.Is(t, type record) or t = type any then null else t,
mapped = Table.TransformColumns(schema, {"Type", map}),
omitted = Table.SelectRows(mapped, each [Type] <> null),
existingColumns = Table.ColumnNames(table),
removeMissing = Table.SelectRows(omitted, each List.Contains(existingColumns, [Name])),
primativeTransforms = Table.ToRows(removeMissing),
changedPrimatives = Table.TransformColumnTypes(table, primativeTransforms)
in
changedPrimatives,
// Returns the table type for a given schema
SchemaToTableType = (schema as table) as type =>
let
toList = List.Transform(schema[Type], (t) => [Type=t, Optional=false]),
toRecord = Record.FromList(toList, schema[Name]),
toType = Type.ForRecord(toRecord, false)
in
type table (toType),
// Determine if we have extra/missing columns.
// The enforceSchema parameter determines what we do about them.
schemaNames = schema[Name],
foundNames = Table.ColumnNames(table),
addNames = List.RemoveItems(schemaNames, foundNames),
extraNames = List.RemoveItems(foundNames, schemaNames),
tmp = Text.NewGuid(),
added = Table.AddColumn(table, tmp, each []),
expanded = Table.ExpandRecordColumn(added, tmp, addNames),
result = if List.IsEmpty(addNames) then table else expanded,
fullList =
if (_enforceSchema = EnforceSchema.Strict) then
schemaNames
else if (_enforceSchema = EnforceSchema.IgnoreMissingColumns) then
foundNames
else
schemaNames & extraNames,
// Select the final list of columns.
// These will be ordered according to the schema table.
reordered = Table.SelectColumns(result, fullList, MissingField.Ignore),
enforcedTypes = EnforceTypes(reordered, schema),
withType = if (_enforceSchema = EnforceSchema.Strict) then Value.ReplaceType(enforcedTypes, SchemaToTableType(schema)) else enforcedTypes
in
withType;
TripPin コネクタの更新
ここで、新しいスキーマ適用コードを使用するように、コネクタに次の変更を行います。
- すべてのスキーマ定義を保持するマスター スキーマ テーブル (
SchemaTable) を定義します。 schemaパラメーターを受け取るようにTripPin.Feed、GetPage、GetAllPagesByNextLinkを更新します。GetPageでスキーマを適用します。- ナビゲーション テーブル コードを更新して、新しい関数 (
GetEntity)の呼び出しで各テーブルをラップします。これにより、将来テーブル定義をより柔軟に操作できるようになります。
マスター スキーマ テーブル
ここでは、スキーマ定義を 1 つのテーブルに統合し、エンティティ名 (GetSchemaForEntity("Airlines") など) に基づいて定義を検索できるヘルパー関数 (GetSchemaForEntity) を追加します。
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})},
{"Airports", #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
})},
{"People", #table({"Name", "Type"}, {
{"UserName", type text},
{"FirstName", type text},
{"LastName", type text},
{"Emails", type list},
{"AddressInfo", type list},
{"Gender", type nullable text},
{"Concurrency", Int64.Type}
})}
});
GetSchemaForEntity = (entity as text) as table => try SchemaTable{[Entity=entity]}[SchemaTable] otherwise error "Couldn't find entity: '" & entity &"'";
データ関数へのスキーマ サポートの追加
次に、TripPin.Feed、GetPage、GetAllPagesByNextLink 関数に省略可能な schema パラメーターを追加します。
これにより、スキーマを (必要に応じて) ページング関数に渡すことができるようになります。そのスキーマは、サービスから返される結果に適用されます。
TripPin.Feed = (url as text, optional schema as table) as table => ...
GetPage = (url as text, optional schema as table) as table => ...
GetAllPagesByNextLink = (url as text, optional schema as table) as table => ...
また、これらの関数のすべての呼び出しを更新して、スキーマを確実に正しく渡すようにします。
スキーマの適用
実際のスキーマの適用は GetPage 関数で行われます。
GetPage = (url as text, optional schema as table) as table =>
let
response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),
body = Json.Document(response),
nextLink = GetNextLink(body),
data = Table.FromRecords(body[value]),
// enforce the schema
withSchema = if (schema <> null) then SchemaTransformTable(data, schema) else data
in
withSchema meta [NextLink = nextLink];
Note
この GetPage の実装では、Table.FromRecords を使用して、JSON 応答に含まれるレコード リストをテーブルに変換します。
Table.FromRecords を使用する場合の大きな欠点は、リスト内のすべてのレコードが同じフィールド セットを持つと想定していることです。
これは TripPin サービスに対しては機能します。なぜなら、OData レコードが同じフィールドを含むことが保証されるためです。しかし、これはすべての REST API に当てはまるとは限りません。
Table.FromList と Table.ExpandRecordColumn を組み合わせて使用すれば、より堅牢な実装になります。
この後のチュートリアルでは、スキーマ テーブルから列リストを取得するように実装を変更し、JSON から M への変換時に列が消失したり欠落したりしないようにします。
GetEntity 関数の追加
GetEntity 関数を使用すると、呼び出しが TripPin.Feed にラップされます。
エンティティ名に基づいてスキーマ定義が検索され、完全な要求 URL が作成されます。
GetEntity = (url as text, entity as text) as table =>
let
fullUrl = Uri.Combine(url, entity),
schemaTable = GetSchemaForEntity(entity),
result = TripPin.Feed(fullUrl, schemaTable)
in
result;
その後に、すべての呼び出しをインラインで実行するのではなく、TripPinNavTable 関数を更新して GetEntity を呼び出します。
これの主な利点は、ナビゲーション テーブル ロジックに触れることなく、エンティティ作成コードの変更を続けられることです。
TripPinNavTable = (url as text) as table =>
let
entitiesAsTable = Table.FromList(RootEntities, Splitter.SplitByNothing()),
rename = Table.RenameColumns(entitiesAsTable, {{"Column1", "Name"}}),
// Add Data as a calculated column
withData = Table.AddColumn(rename, "Data", each GetEntity(url, [Name]), type table),
// Add ItemKind and ItemName as fixed text values
withItemKind = Table.AddColumn(withData, "ItemKind", each "Table", type text),
withItemName = Table.AddColumn(withItemKind, "ItemName", each "Table", type text),
// Indicate that the node should not be expandable
withIsLeaf = Table.AddColumn(withItemName, "IsLeaf", each true, type logical),
// Generate the nav table
navTable = Table.ToNavigationTable(withIsLeaf, {"Name"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
in
navTable;
まとめ
すべてのコード変更が終わったら、Airlines テーブルの Table.Schema を呼び出すテスト クエリをコンパイルして再実行します。
let
source = TripPin.Contents(),
data = source{[Name="Airlines"]}[Data]
in
Table.Schema(data)
これで、Airlines テーブルには、そのスキーマで定義した 2 つの列だけが表示されます。
People テーブルに対して同じコードを実行すると...
let
source = TripPin.Contents(),
data = source{[Name="People"]}[Data]
in
Table.Schema(data)
使用した指定の型 (Int64.Type) も正しく設定されているのが確認できます。
注意を要する重要な点は、SchemaTransformTable のこの実装では、list および record 列の型は変更されず、一方で Emails および AddressInfo 列は引き続き list として型指定されています。 これは、Json.Document によって JSON 配列が M リストに、JSON オブジェクトが M レコードに正しくマップされるためです。 Power Query でリストまたはレコードの列を展開する場合、展開された列はすべて any 型になる可能性があります。 この後のチュートリアルでは、入れ子になった複合型の型情報を再帰的に設定するように実装を改善します。
まとめ
このチュートリアルでは、REST サービスから返される JSON データにスキーマを適用するサンプル実装を示しました。 このサンプルでは簡単なハードコーディングされたスキーマ テーブル形式を使用していますが、JSON スキーマ ファイルや、データ ソースによって公開されるメタデータ サービスとエンドポイントなどの別のソースからスキーマ テーブル定義を動的に構築することで、このアプローチを拡張できます。
コードでは、列の型 (および値) を変更することに加えて、テーブル自体に対して正しい型情報を設定しています。 この型情報を設定すると、Power Query 内で実行する場合のパフォーマンスが向上します。これは、ユーザー エクスペリエンスで常に型情報を推論してエンド ユーザーに適切な UI キューを表示しようとし、推論呼び出しによって基になるデータ API へのその他の呼び出しがトリガーされる可能性があるためです。
前のレッスンの TripPin コネクタを使用して People テーブルを表示すると、すべての列 (リストを含む列であっても) に "any 型" アイコンが表示されます。
このレッスンの TripPin コネクタで同じクエリを実行すると、今度は型情報が正しく表示されます。
