TripPin μέρος 6 - Σχήμα

Άρθρο
05/17/2023

Αυτή η εκμάθηση πολλαπλών τμημάτων καλύπτει τη δημιουργία μιας νέας επέκτασης προέλευσης δεδομένων για το Power Query. Το εκπαιδευτικό βοήθημα προορίζεται να γίνει διαδοχικά. Κάθε μάθημα βασίζεται στη σύνδεση που δημιουργήθηκε σε προηγούμενα μαθήματα, προσθέτοντας σταδιακά νέες δυνατότητες στη σύνδεσή σας.

Σε αυτό το μάθημα, θα κάνετε τα εξής:

Ορισμός σταθερού σχήματος για ένα API REST
Δυναμικός ορισμός τύπων δεδομένων για στήλες
Επιβολή δομής πίνακα για την αποφυγή σφαλμάτων μετασχηματισμού λόγω στηλών που λείπουν
Απόκρυψη στηλών από το σύνολο αποτελεσμάτων

Ένα από τα μεγάλα πλεονεκτήματα μιας υπηρεσίας OData σε σχέση με ένα τυπικό REST API είναι ο $metadata ορισμός της. Το έγγραφο $metadata περιγράφει τα δεδομένα που βρίσκονται σε αυτήν την υπηρεσία, συμπεριλαμβανομένου του σχήματος για όλες τις οντότητες (πίνακες) και τα πεδία (στήλες). Η OData.Feed συνάρτηση χρησιμοποιεί αυτόν τον ορισμό σχήματος για να ορίσει αυτόματα πληροφορίες τύπου δεδομένων , επομένως, αντί να λάβει όλα τα πεδία κειμένου και αριθμών (όπως θα κάνατε από Json.Documentτο ), οι τελικοί χρήστες λαμβάνουν ημερομηνίες, ακέραιους αριθμούς, ώρες και ούτω καθεξής, παρέχοντας μια καλύτερη συνολική εμπειρία χρήστη.

Πολλά API REST δεν έχουν τρόπο να προσδιορίσουν μέσω προγραμματισμού το σχήμα τους. Σε αυτές τις περιπτώσεις, θα χρειαστεί να συμπεριλάβετε ορισμούς σχήματος στη σύνδεσή σας. Σε αυτό το μάθημα θα ορίσετε ένα απλό, ενσωματωμένο σχήμα για κάθε έναν από τους πίνακές σας και θα επιβάλετε το σχήμα στα δεδομένα που διαβάζετε από την υπηρεσία.

Σημείωμα

Η προσέγγιση που περιγράφεται εδώ θα πρέπει να λειτουργεί για πολλές υπηρεσίες REST. Τα μελλοντικά μαθήματα θα βασιστούν σε αυτή την προσέγγιση επιβάλλοντας αναδρομικά σχήματα σε δομημένες στήλες (εγγραφή, λίστα, πίνακας) και παρέχοντας δείγματα υλοποιήσεων που μπορούν να δημιουργήσουν μέσω προγραμματισμού έναν πίνακα σχήματος από έγγραφα CSDL ή σχήματος JSON.

Συνολικά, η επιβολή ενός σχήματος στα δεδομένα που επιστρέφονται από τη σύνδεσή σας έχει πολλά πλεονεκτήματα, όπως:

Ορισμός των σωστών τύπων δεδομένων
Κατάργηση στηλών που δεν χρειάζεται να εμφανίζονται στους τελικούς χρήστες (όπως εσωτερικά αναγνωριστικά ή πληροφορίες κατάστασης)
Εξασφάλιση ότι κάθε σελίδα δεδομένων έχει το ίδιο σχήμα προσθέτοντας τυχόν στήλες που μπορεί να λείπουν από μια απόκριση (ένας συνηθισμένος τρόπος για να υποδείξουν τα API REST σε ένα πεδίο πρέπει να είναι null)

Προβολή του υπάρχοντος σχήματος με το Table.Schema

Η σύνδεση που δημιουργήθηκε στο προηγούμενο μάθημα εμφανίζει τρεις πίνακες από την υπηρεσία TripPin,Airlines και AirportsPeople. Εκτελέστε το παρακάτω ερώτημα για να προβάλετε τον Airlines πίνακα:

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

Στα αποτελέσματα θα δείτε τέσσερις στήλες να επιστρέφονται:

@odata.id
@odata.editLink
Κωδικός αεροπορικής εταιρείας
Ονομασία

Οι αεροπορικές εταιρείες δεν έχουν σχήμα.

Οι στήλες "@odata.*" αποτελούν μέρος του πρωτοκόλλου OData και όχι κάτι που θα θέλατε ή πρέπει να εμφανίσετε στους τελικούς χρήστες της σύνδεσής σας. AirlineCode και Name είναι οι δύο στήλες που θα θέλετε να διατηρήσετε. Εάν εξετάσετε το σχήμα του πίνακα (χρησιμοποιώντας τη συνάρτηση Handy Table.Schema ), μπορείτε να δείτε ότι όλες οι στήλες στον πίνακα έχουν τύπο Any.Typeδεδομένων .

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

Airlines Table.Schema.

Η Table.Schema επιστρέφει πολλά μετα-δεδομένα σχετικά με τις στήλες ενός πίνακα, συμπεριλαμβανομένων ονομάτων, θέσεων, πληροφοριών τύπου και πολλών σύνθετων ιδιοτήτων, όπως Ακρίβεια, Κλίμακα και MaxLength. Τα μελλοντικά μαθήματα θα παρέχουν μοτίβα σχεδίασης για τη ρύθμιση αυτών των σύνθετων ιδιοτήτων, αλλά προς το παρόν χρειάζεται να ανησυχείτε μόνο για τον τύπο που αποδίδεται (TypeName), τον στοιχειώδη τύπο (Kind) και εάν η τιμή στήλης μπορεί να είναι null (IsNullable).

Ορισμός ενός απλού πίνακα σχήματος

Ο πίνακας σχήματος θα αποτελείται από δύο στήλες:

Column	Λεπτομέρειες
Ονομασία	Το όνομα της στήλης. Αυτό πρέπει να συμφωνεί με το όνομα στα αποτελέσματα που επιστρέφονται από την υπηρεσία.
Τύπος	Ο τύπος δεδομένων M που πρόκειται να ορίσετε. Αυτός μπορεί να είναι ένας στοιχειώδης τύπος (`text`, `number`, `datetime`και ούτω καθεξής) ή ένας τύπος που αποδίδεται (`Int64.Type`, `Currency.Type`και ούτω καθεξής).

Ο πίνακας σχήματος hardcoded για τον Airlines πίνακα ορίζει τις στήλες του AirlineCode και Name σε text, και μοιάζει κάπως έτσι:

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

Ο Airports πίνακας έχει τέσσερα πεδία που θέλετε να διατηρήσετε (συμπεριλαμβανομένου ενός τύπου record):

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

Τέλος, ο People πίνακας έχει επτά πεδία, συμπεριλαμβανομένων λιστών (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 συνάρτηση βοηθητικής εφαρμογής που περιγράφεται παρακάτω θα χρησιμοποιηθεί για την επιβολή σχημάτων στα δεδομένα σας. Λαμβάνει τις ακόλουθες παραμέτρους:

Παράμετρος	Τύπος	Description
πίνακας	πίνακας	Ο πίνακας δεδομένων στον οποίο θέλετε να επιβάλετε το σχήμα σας.
σχήμα	πίνακας	Ο πίνακας σχήματος από τον οποίο θα διαβάσετε πληροφορίες στήλης, με τον ακόλουθο τύπο: `type table [Name = text, Type = type]`.
enforceSchema	Αριθμός	(προαιρετικό) Μια απαρίθμηση που ελέγχει τη συμπεριφορά της συνάρτησης. Η προεπιλεγμένη τιμή (`EnforceSchema.Strict = 1`) εξασφαλίζει ότι ο πίνακας εξόδου θα συμφωνεί με τον πίνακα σχήματος που παρέχεται με την προσθήκη τυχόν στηλών που λείπουν και την κατάργηση επιπλέον στηλών. Η `EnforceSchema.IgnoreExtraColumns = 2` επιλογή μπορεί να χρησιμοποιηθεί για τη διατήρηση επιπλέον στηλών στο αποτέλεσμα. Όταν `EnforceSchema.IgnoreMissingColumns = 3` χρησιμοποιείται, αμφότερες οι στήλες που λείπουν και οι επιπλέον στήλες θα αγνοηθούν.

Η λογική για αυτήν τη συνάρτηση μοιάζει κάπως έτσι:

Προσδιορίστε εάν λείπουν στήλες από τον πίνακα προέλευσης.
Προσδιορίστε εάν υπάρχουν επιπλέον στήλες.
Αγνοήστε τις δομημένες στήλες (τύπου list, recordκαι table) και τις στήλες που έχουν οριστεί σε type any.
Χρησιμοποιήστε το Table.TransformColumnTypes για να ορίσετε κάθε τύπο στήλης .
Αναδιάταξη στηλών με βάση τη σειρά εμφάνισης στον πίνακα σχήματος.
Ορίστε τον τύπο στον ίδιο τον πίνακα χρησιμοποιώντας τη value.ReplaceType.

Σημείωμα

Το τελευταίο βήμα για τον ορισμό του τύπου πίνακα θα καταργήσει την ανάγκη για το περιβάλλον εργασίας χρήστη του Power Query να συνάγει πληροφορίες τύπου κατά την προβολή των αποτελεσμάτων στο πρόγραμμα επεξεργασίας ερωτημάτων. Αυτό καταργεί το ζήτημα διπλής αίτησης που είδατε στο τέλος της προηγούμενης εκμάθησης.

Μπορείτε να αντιγράψετε και να επικολλήσετε τον παρακάτω κώδικα βοηθητικής εφαρμογής στην επέκτασή σας:

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) που περιέχει όλους τους ορισμούς σχήματος.
Ενημερώστε τα TripPin.Feed, GetPageκαι GetAllPagesByNextLink για να αποδεχτείτε μια schema παράμετρο.
Επιβάλλετε το σχήμα σας στο GetPage.
Ενημερώστε τον κώδικα του πίνακα περιήγησης για να αναδιπλώσετε κάθε πίνακα με μια κλήση σε μια νέα συνάρτηση (GetEntity) . Αυτό θα σας δώσει μεγαλύτερη ευελιξία για τον χειρισμό των ορισμών πίνακα στο μέλλον.

Πίνακας κύριου σχήματος

Τώρα θα ενοποιήσετε τους ορισμούς σχήματος σε έναν μοναδικό πίνακα και θα προσθέσετε μια συνάρτηση βοηθητικής εφαρμογής (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];

Σημείωμα

Αυτή η 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;

Συνδυάστε τα όλα

Μόλις πραγματοποιηθούν όλες οι αλλαγές κώδικα, συγκεντρώστε και επαναλάβετε το ερώτημα δοκιμής που απαιτεί Table.Schema τον πίνακα Airlines.

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

Τώρα βλέπετε ότι ο πίνακας Airlines έχει μόνο τις δύο στήλες που ορίσατε στο σχήμα του:

Αεροπορικές εταιρείες με σχήμα.

Εάν εκτελείτε τον ίδιο κώδικα στον πίνακα Άτομα...

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

Θα δείτε ότι ο τύπος που αποδόθηκε χρησιμοποιήσατε (Int64.Type) ορίστηκε επίσης σωστά.

Άτομα με σχήμα.

Ένα σημαντικό πράγμα που πρέπει να λάβετε υπόψη είναι ότι αυτή η υλοποίηση SchemaTransformTable του δεν τροποποιεί τους τύπους και record των list στηλών, αλλά οι Emails στήλες και AddressInfo εξακολουθούν να πληκτρολογούνται ως list. Αυτό συμβαίνει γιατί Json.Document θα αντιστοιχιστεί σωστά πίνακες JSON σε λίστες M και αντικείμενα JSON σε εγγραφές M. Εάν αναπτύξετε τη λίστα ή τη στήλη εγγραφής στο Power Query, θα δείτε ότι όλες οι ανεπτυγμένες στήλες θα είναι τύπου any. Τα μελλοντικά προγράμματα εκμάθησης θα βελτιώσουν την υλοποίηση για να ορίζουν αναδρομικά πληροφορίες τύπου για ένθετους σύνθετους τύπους.

Συμπέρασμα

Αυτό το εκπαιδευτικό βοήθημα παρείχε ένα δείγμα υλοποίησης για την επιβολή ενός σχήματος στα δεδομένα JSON που επιστρέφονται από μια υπηρεσία REST. Παρόλο που αυτό το δείγμα χρησιμοποιεί μια απλή μορφή πίνακα σχήματος, η προσέγγιση μπορεί να επεκταθεί δημιουργώντας δυναμικά έναν ορισμό πίνακα σχήματος από μια άλλη προέλευση, όπως ένα αρχείο σχήματος JSON ή υπηρεσία μετα-δεδομένων/τελικό σημείο που εκτίθεται από την προέλευση δεδομένων.

Εκτός από την τροποποίηση τύπων στηλών (και τιμών), ο κωδικός σας καθορίζει επίσης τις σωστές πληροφορίες τύπου στον ίδιο τον πίνακα. Η ρύθμιση αυτής της πληροφορίας τύπου επωφελείται από τις επιδόσεις κατά την εκτέλεση εντός του Power Query, καθώς η εμπειρία χρήστη επιχειρεί πάντα να συναγάγει πληροφορίες τύπου για να εμφανίσει τις σωστές ουρές περιβάλλοντος εργασίας χρήστη στον τελικό χρήστη και οι κλήσεις συμπερασμάτων μπορεί να καταλήξουν να ενεργοποιούν άλλες κλήσεις στα υποκείμενα API δεδομένων.

Εάν προβάλετε τον πίνακα Άτομα χρησιμοποιώντας τη σύνδεση TripPin από το προηγούμενο μάθημα, θα δείτε ότι όλες οι στήλες έχουν ένα εικονίδιο "πληκτρολογήστε οποιαδήποτε" (ακόμα και τις στήλες που περιέχουν λίστες):

Άτομα χωρίς σχήμα.

Εάν εκτελέσετε το ίδιο ερώτημα με τη σύνδεση TripPin από αυτό το μάθημα, θα δείτε τώρα ότι οι πληροφορίες τύπου εμφανίζονται σωστά.

Άτομα με σχήμα.

Επόμενα βήματα

TripPin Part 7 - Σύνθετο σχήμα με τύπους M

Κοινή χρήση μέσω

TripPin μέρος 6 - Σχήμα

Προβολή του υπάρχοντος σχήματος με το Table.Schema

Ορισμός ενός απλού πίνακα σχήματος

Η συνάρτηση βοηθητικής εφαρμογής SchemaTransformTable

Ενημέρωση της σύνδεσης TripPin

Πίνακας κύριου σχήματος

Προσθήκη υποστήριξης σχήματος σε συναρτήσεις δεδομένων

Επιβολή του σχήματος

Προσθήκη της συνάρτησης GetEntity

Συνδυάστε τα όλα

Συμπέρασμα

Επόμενα βήματα

Σχόλια

Πρόσθετοι πόροι