TripPin パート 6 - スキーマ

このマルチパート チュートリアルでは、Power Query 用の新しいデータ ソース拡張機能の作成について説明します。 このチュートリアルは順番に行う予定です。各レッスンは、前のレッスンで作成したコネクタに基づいて構築され、コネクタに新しい機能を段階的に追加します。

このレッスンでは、次の操作を行います。

標準 REST API よりも OData サービスの大きな利点の 1 つは 、$metadata定義です。 $metadataドキュメントでは、すべてのエンティティ (テーブル) とフィールド (列) のスキーマを含め、このサービスで見つかったデータについて説明します。 OData.Feed関数は、このスキーマ定義を使用して、データ型情報を自動的に設定します。 そのため、( Json.Documentから取得した場合と同様に) すべてのテキスト フィールドと数値フィールドを取得する代わりに、エンド ユーザーは日付、整数、時刻などを取得し、全体的なユーザー エクスペリエンスを向上させます。

多くの REST API には、スキーマをプログラムで決定する方法がありません。 このような場合は、コネクタ内にスキーマ定義を含める必要があります。 このレッスンでは、各テーブルに単純なハードコーディングされたスキーマを定義し、サービスから読み取ったデータにスキーマを適用します。

全体として、コネクタから返されるデータにスキーマを適用するには、次のような複数の利点があります。

• 正しいデータ型の設定
• エンド ユーザーに表示する必要のない列の削除 (内部 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
• 航空会社コード
• 名前

"@odata.*" 列は OData プロトコルの一部であり、コネクタのエンド ユーザーに表示したい、または表示する必要があるものではありません。 AirlineCode と Name は、保持する 2 つの列です。 (便利な Table.Schema 関数を使用して) テーブルのスキーマを見ると、テーブル内のすべての列に Any.Typeのデータ型があります。

let
    source = TripPin.Contents(),
    data = source{[Name="Airlines"]}[Data]
in
    Table.Schema(data)

Table.Schema は 、名前、位置、型情報、精度、小数点以下桁数、MaxLength などの多くの高度なプロパティなど、テーブル内の列に関する多くのメタデータを返します。 今後のレッスンでは、これらの高度なプロパティを設定するための設計パターンを提供しますが、現時点では、接写型 (TypeName)、プリミティブ型 (Kind)、列値が null (IsNullable) かどうかについてのみ考慮する必要があります。

単純なスキーマ テーブルの定義

スキーマ テーブルは、次の 2 つの列で構成されます。

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 テーブルには、リスト (Emails、AddressInfo)、null 許容列 (Gender)、および型 () がConcurrencyいた列など、7 つのフィールドがあります。

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 ヘルパー関数は、データにスキーマを適用するために使用されます。 次のパラメーターを受け取ります。

この関数のロジックは次のようになります。

1. ソース テーブルに列がないかどうかを確認します。
2. 追加の列があるかどうかを判断します。
3. 構造化列 ( list、 record、および tableの型) と、 type anyに設定された列は無視します。
4. Table.TransformColumnTypes を使用して、各列の種類を設定します。
5. スキーマ テーブルに表示される順序に基づいて列を並べ替えます。
6. Value.ReplaceType を使用して、テーブル自体に型を設定します。

次のヘルパー コードは、拡張機能にコピーして貼り付けることができます。

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 コネクタの更新

新しいスキーマ強制コードを使用するには、コネクタに次の変更を加えます。

1. すべてのスキーマ定義を保持するマスター スキーマ テーブル (SchemaTable) を定義します。
2. TripPin.Feed パラメーターを受け入れるように、GetPage、GetAllPagesByNextLink、およびschemaを更新します。
3. GetPageでスキーマを適用します。
4. ナビゲーション テーブル コードを更新して、新しい関数 (GetEntity) を呼び出して各テーブルをラップします。 この関数を使用すると、将来、テーブル定義をより柔軟に操作できます。

マスター スキーマ テーブル

スキーマ定義を 1 つのテーブルに統合し、エンティティ名 (GetSchemaForEntity など) に基づいて定義を検索できるヘルパー関数 (GetSchemaForEntity("Airlines")) を追加します。

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 &"'";

データ関数へのスキーマサポートの追加

次に、schema、TripPin.Feed、およびGetPage関数に省略可能なGetAllPagesByNextLink パラメーターを追加します。 このパラメーターを使用すると、サービスから返される結果に適用されるページング関数にスキーマを渡すことができます (必要な場合)。

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];

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)

使用した ascribed 型 (Int64.Type) も正しく設定されていることに注意してください。

重要なのは、この SchemaTransformTable の実装では、 list 列と record 列の型は変更されませんが、 Emails 列と AddressInfo 列は listとして型指定されます。 この動作は、 Json.Document が JSON 配列を M リストに、JSON オブジェクトを M レコードに正しくマップするためです。 Power Query でリストまたはレコード列を展開すると、展開されたすべての列の種類が any。 今後のチュートリアルでは、入れ子になった複合型の型情報を再帰的に設定するように実装を改善します。

Conclusion

このチュートリアルでは、REST サービスから返される JSON データにスキーマを適用するためのサンプル実装を提供しました。 このサンプルでは単純なハードコーディングされたスキーマ テーブル形式を使用しますが、JSON スキーマ ファイルや、データ ソースによって公開されるメタデータ サービス/エンドポイントなど、別のソースからスキーマ テーブル定義を動的に構築することで、このアプローチを拡張できます。

コードでは、列の型 (および値) の変更に加えて、テーブル自体の正しい型情報も設定しています。 この型情報を設定すると、ユーザー エクスペリエンスが常にエンド ユーザーに適切な UI キューを表示するように型情報を推論しようとするため、Power Query 内で実行するとパフォーマンスが向上し、推論呼び出しによって基になるデータ API への他の呼び出しがトリガーされる可能性があります。

前のレッスンの TripPin コネクタを使用して People テーブルを表示すると、すべての列に 'type any' アイコン (リストを含む列も含む) が表示されます。

このレッスンの TripPin コネクタで同じクエリを実行すると、型情報が正しく表示されるようになりました。

次のステップ

TripPin パート 7 - M 型の高度なスキーマ