TripPin Teil 6 – Schema

In diesem mehrteiligen Lernprogramm wird die Erstellung einer neuen Datenquellenerweiterung für Power Query behandelt. Das Lernprogramm soll sequenziell durchgeführt werden – jede Lektion baut auf dem connector auf, der in früheren Lektionen erstellt wurde, und fügt Ihrem Connector inkrementell neue Funktionen hinzu.

In dieser Lektion lernen Sie:

Einer der großen Vorteile eines OData-Diensts gegenüber einer standardmäßigen REST-API ist seine $metadata Definition. Das $metadata Dokument beschreibt die in diesem Dienst gefundenen Daten, einschließlich des Schemas für alle Entitäten (Tabellen) und Felder (Spalten). Die OData.Feed Funktion verwendet diese Schemadefinition, um Datentypinformationen automatisch festzulegen. Statt alle Text- und Zahlenfelder zu erhalten (wie bei Json.Document), erhalten Endbenutzer Datumsangaben, ganze Zahlen, Uhrzeiten usw., was eine bessere allgemeine Benutzererfahrung bietet.

Viele REST-APIs haben keine Möglichkeit, ihr Schema programmgesteuert zu bestimmen. In diesen Fällen müssen Sie Schemadefinitionen in Ihren Connector einschließen. In dieser Lektion definieren Sie ein einfaches, hartcodiertes Schema für jede Ihrer Tabellen und erzwingen das Schema für die Daten, die Sie aus dem Dienst gelesen haben.

Insgesamt hat das Erzwingen eines Schemas für die von Ihrem Connector zurückgegebenen Daten mehrere Vorteile, z. B.:

• Festlegen der richtigen Datentypen
• Entfernen von Spalten, die endbenutzern nicht angezeigt werden müssen (z. B. interne IDs oder Statusinformationen)
• Sicherstellen, dass jede Seite mit Daten dieselbe Form hat, indem Sie Spalten hinzufügen, die möglicherweise in einer Antwort fehlen (eine gängige Methode für REST-APIs, um anzugeben, dass ein Feld null angegeben werden sollte)

Anzeigen des vorhandenen Schemas mit "Table.Schema"

Der in der vorherigen Lektion erstellte Connector zeigt drei Tabellen aus dem TripPin-Dienst an: Airlines, , Airportsund People. Führen Sie die folgende Abfrage aus, um die Airlines Tabelle anzuzeigen:

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

In den Ergebnissen werden vier Spalten zurückgegeben:

• @odata.id
• @odata.editLink
• AirlineCode
• Name

Die Spalten "@odata.*" sind Teil des OData-Protokolls und nicht das, was Sie den Endbenutzern Ihres Connectors anzeigen möchten oder müssen. AirlineCode und Name sind die beiden Spalten, die Sie beibehalten möchten. Wenn Sie das Schema der Tabelle (mithilfe der praktischen Funktion "Table.Schema ") betrachten, weisen alle Spalten in der Tabelle einen Datentyp auf Any.Type.

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

Table.Schema gibt viele Metadaten zu den Spalten in einer Tabelle zurück, einschließlich Namen, Positionen, Typinformationen und vielen erweiterten Eigenschaften wie Precision, Scale und MaxLength. Zukünftige Lektionen bieten Entwurfsmuster für das Festlegen dieser erweiterten Eigenschaften, aber im Moment müssen Sie sich nur mit dem beschriebenen Typ (TypeName), dem Grundtyp (Kind) und der Frage, ob der Spaltenwert möglicherweise null ist (IsNullable), befassen.

Definieren einer einfachen Schematabelle

Ihre Schematabelle besteht aus zwei Spalten:

Die hartcodierte Schematabelle für die Tabelle Airlines legt deren AirlineCode- und Name-Spalten auf text fest und sieht wie folgt aus:

Airlines = #table({"Name", "Type"}, {
        {"AirlineCode", type text},
        {"Name", type text}
    });

Die Airports Tabelle enthält vier Felder, die Sie beibehalten möchten (einschließlich eines typs record):

Airports = #table({"Name", "Type"}, {
        {"IcaoCode", type text},
        {"Name", type text},
        {"IataCode", type text},
        {"Location", type record}
    });

Schließlich verfügt die People Tabelle über sieben Felder, einschließlich Listen (Emails, AddressInfo), einer nullfähigen Spalte (Gender) und einer Spalte mit einem beschriftten Typ (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}
    })

Die Hilfsfunktion "SchemaTransformTable"

Die SchemaTransformTable in diesem Abschnitt beschriebene Hilfsfunktion wird verwendet, um Schemas für Ihre Daten zu erzwingen. Es verwendet die folgenden Parameter:

Die Logik für diese Funktion sieht ungefähr wie folgt aus:

1. Ermitteln Sie, ob in der Quelltabelle fehlende Spalten vorhanden sind.
2. Ermitteln Sie, ob zusätzliche Spalten vorhanden sind.
3. Ignorieren Sie strukturierte Spalten (vom Typ list, record, und table) und Spalten, die auf type any gesetzt sind.
4. Verwenden Sie "Table.TransformColumnTypes ", um jeden Spaltentyp festzulegen.
5. Ordnen Sie Spalten basierend auf der Reihenfolge neu an, in der sie in der Schematabelle angezeigt werden.
6. Legen Sie den Typ für die Tabelle selbst mithilfe von Value.ReplaceType fest.

Der folgende Hilfscode kann in Die Erweiterung kopiert und eingefügt werden:

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;

Aktualisieren des TripPin-Connectors

Um den neuen Schema-Durchsetzungscode zu verwenden, nehmen Sie die folgenden Anpassungen an Ihrem Connector vor.

1. Definieren Sie eine Masterschematabelle (SchemaTable), die alle Schemadefinitionen enthält.
2. Aktualisieren Sie TripPin.Feed, GetPage und GetAllPagesByNextLink, damit sie einen schema Parameter akzeptieren.
3. Erzwingen Sie Ihr Schema in GetPage.
4. Aktualisieren Sie ihren Navigationstabellencode so, dass jede Tabelle durch einen Aufruf einer neuen Funktion (GetEntity) umbrochen wird. Diese Funktion bietet Ihnen mehr Flexibilität, die Tabellendefinitionen in Zukunft zu bearbeiten.

Master-Schema-Tabelle

Konsolidieren Sie nun Ihre Schemadefinitionen in einer einzelnen Tabelle, und fügen Sie eine Hilfsfunktion (GetSchemaForEntity) hinzu, mit der Sie die Definition basierend auf einem Entitätsnamen (z GetSchemaForEntity("Airlines"). B. ) nachschlagen können.

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

Hinzufügen der Schemaunterstützung zu Datenfunktionen

Fügen Sie nun einen optionalen schema Parameter zu den TripPin.FeedFunktionen GetPageund GetAllPagesByNextLink Funktionen hinzu. Mit diesem Parameter können Sie das Schema, falls gewünscht, an die Seitenumbruchfunktionen übergeben, wo es auf die vom Dienst erhaltenen Ergebnisse angewendet wird.

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 => ...

Außerdem müssen Sie alle Aufrufe dieser Funktionen aktualisieren, um sicherzustellen, dass Sie das Schema ordnungsgemäß übergeben.

Durchsetzung des Schemas

Die tatsächliche Schemaerzwingung erfolgt in Ihrer GetPage Funktion.

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

Hinzufügen der GetEntity-Funktion

Die Funktion GetEntity umschließt Ihren Aufruf von TripPin.Feed. Es sucht eine Schemadefinition basierend auf dem Entitätsnamen und erstellt die vollständige Anforderungs-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;

Anschließend aktualisieren Sie Ihre TripPinNavTable-Funktion so, dass sie GetEntity aufruft, anstatt alle Aufrufe inline zu erstellen. Der Hauptvorteil dieses Updates besteht darin, dass Sie mit dem Ändern des Entitätsbaucodes fortfahren können, ohne ihre Navigationstabellenlogik berühren zu müssen.

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;

Alles zusammensetzen

Nachdem alle Codeänderungen vorgenommen wurden, kompilieren Sie und führen Sie die Testabfrage erneut aus, die Table.Schema für die Tabelle "Airlines" aufruft.

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

Die Tabelle "Airlines" weist jetzt nur die beiden Spalten auf, die Sie im Schema definiert haben:

Wenn Sie denselben Code für die Tabelle "Personen" ausführen...

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

Beachten Sie, dass der eingegebene Typ (Int64.Type) ebenfalls richtig festgelegt wurde.

Wichtig ist zu beachten, dass diese Implementierung von SchemaTransformTable nicht die Typen der list und record Spalten ändert, aber die Emails und AddressInfo Spalten werden weiterhin als list typisiert. Dieses Verhalten tritt auf, da Json.Document JSON-Arrays korrekt M-Listen und JSON-Objekte zu M-Datensätzen zuordnet. Wenn Sie die Listen- oder Datensatzspalte in Power Query erweitern, sind alle erweiterten Spalten vom Typ .any Zukünftige Lernprogramme verbessern die Implementierung, um Typinformationen für geschachtelte komplexe Typen rekursiv festzulegen.

Conclusion

In diesem Lernprogramm wurde eine Beispielimplementierung zum Erzwingen eines Schemas für JSON-Daten bereitgestellt, die von einem REST-Dienst zurückgegeben werden. Während in diesem Beispiel ein einfaches hartcodiertes Schematabellenformat verwendet wird, könnte der Ansatz erweitert werden, indem dynamisch eine Schematabellendefinition aus einer anderen Quelle erstellt wird, z. B. eine JSON-Schemadatei oder ein Metadatendienst/-endpunkt, der von der Datenquelle verfügbar gemacht wird.

Neben dem Ändern von Spaltentypen (und Werten) legt Ihr Code auch die richtigen Typinformationen für die Tabelle selbst fest. Das Festlegen dieser Typinformation profitiert von der Leistung der Ausführung innerhalb von Power Query, da die Benutzeroberfläche immer versucht, Typinformationen abzuleiten, um die richtigen UI-Warteschlangen für den Endbenutzer anzuzeigen, und die Ableitungsaufrufe können dazu führen, dass andere Aufrufe an die zugrunde liegenden Daten-APIs ausgelöst werden.

Wenn Sie die Tabelle "Personen" mithilfe des TripPin-Connectors aus der vorherigen Lektion anzeigen, weisen alle Spalten ein Symbol vom Typ "any" auf (auch die Spalten, die Listen enthalten):

Wenn Sie dieselbe Abfrage mit dem TripPin-Connector aus dieser Lektion ausführen, werden die Typinformationen jetzt richtig angezeigt.

Nächste Schritte

TripPin Teil 7 – Erweitertes Schema mit M-Typen