TripPin parte 6 - Schema

Questa esercitazione in più parti illustra la creazione di una nuova estensione dell'origine dati per Power Query. L'esercitazione è destinata a essere eseguita in sequenza: ogni lezione si basa sul connettore creato nelle lezioni precedenti, aggiungendo in modo incrementale nuove funzionalità al connettore.

In questa lezione verranno illustrate le procedure seguenti:

Uno dei vantaggi elevati di un servizio OData rispetto a un'API REST standard è la definizione di $metadata. Il documento $metadata descrive i dati trovati in questo servizio, incluso lo schema per tutte le relative entità (tabelle) e campi (colonne). La OData.Feed funzione usa questa definizione di schema per impostare automaticamente le informazioni sul tipo di dati, in modo che invece di ottenere tutti i campi di testo e numero (ad esempio da Json.Document), gli utenti finali ottengono date, numeri interi, ore e così via, offrendo un'esperienza utente complessiva migliore.

Molte API REST non hanno un modo per determinare a livello di codice lo schema. In questi casi, sarà necessario includere le definizioni dello schema all'interno del connettore. In questa lezione si definirà uno schema semplice e hardcoded per ognuna delle tabelle e si applichererà lo schema ai dati letti dal servizio.

In generale, l'applicazione di uno schema sui dati restituiti dal connettore offre diversi vantaggi, ad esempio:

• Impostazione dei tipi di dati corretti
• Rimozione di colonne che non devono essere visualizzate agli utenti finali (ad esempio ID interni o informazioni sullo stato)
• Verifica che ogni pagina di dati abbia la stessa forma aggiungendo eventuali colonne che potrebbero non essere presenti in una risposta (un modo comune per le API REST per indicare che un campo deve essere null)

Visualizzazione dello schema esistente con Table.Schema

Il connettore creato nella lezione precedente mostra tre tabelle del servizioAirlines TripPin, e AirportsPeople. Eseguire la query seguente per visualizzare la Airlines tabella:

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

Nei risultati verranno visualizzate quattro colonne restituite:

• @odata.id
• @odata.editLink
• AirlineCode
• Nome

Le colonne "@odata.*" fanno parte del protocollo OData e non devono essere visualizzate agli utenti finali del connettore. AirlineCode e Name sono le due colonne che si desidera mantenere. Se si esamina lo schema della tabella (usando la funzione Table.Schema utile), è possibile notare che tutte le colonne della tabella hanno un tipo di dati .Any.Type

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

Table.Schema restituisce molti metadati sulle colonne di una tabella, inclusi nomi, posizioni, informazioni sul tipo e molte proprietà avanzate, ad esempio Precisione, Scala e MaxLength. Le lezioni future forniranno modelli di progettazione per l'impostazione di queste proprietà avanzate, ma per il momento è necessario preoccuparsi solo del tipo ascritto (TypeName), del tipo primitivo (Kind) e se il valore della colonna potrebbe essere Null (IsNullable).

Definizione di una tabella dello schema semplice

La tabella dello schema sarà composta da due colonne:

La tabella dello schema hardcoded per la Airlines tabella imposta le colonne AirlineCode e Name su e ha un aspetto simile al textseguente:

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

La Airports tabella contiene quattro campi da mantenere (incluso uno di tipo record):

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

Infine, la People tabella include sette campi, inclusi elenchi (Emails, AddressInfo), una colonna nullable (Gender) e una colonna con un tipo ascritto (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}
    })

Funzione helper SchemaTransformTable

La SchemaTransformTable funzione helper descritta di seguito verrà usata per applicare schemi ai dati. È necessario specificare i seguenti parametri:

La logica per questa funzione è simile alla seguente:

1. Determinare se sono presenti colonne mancanti nella tabella di origine.
2. Determinare se sono presenti colonne aggiuntive.
3. Ignorare le colonne strutturate (di tipo list, recorde table) e le colonne impostate su type any.
4. Usare Table.TransformColumnTypes per impostare ogni tipo di colonna.
5. Riordinare le colonne in base all'ordine in cui vengono visualizzate nella tabella dello schema.
6. Impostare il tipo nella tabella stessa usando Value.ReplaceType.

Il codice helper seguente può essere copiato e incollato nell'estensione:

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;

Aggiornamento del connettore TripPin

A questo punto verranno apportate le modifiche seguenti al connettore per usare il nuovo codice di imposizione dello schema.

1. Definire una tabella dello schema master (SchemaTable) che contiene tutte le definizioni dello schema.
2. TripPin.FeedAggiornare , GetPagee GetAllPagesByNextLink per accettare un schema parametro.
3. Applicare lo schema in GetPage.
4. Aggiornare il codice della tabella di spostamento per eseguire il wrapping di ogni tabella con una chiamata a una nuova funzione (GetEntity): in questo modo sarà possibile modificare le definizioni di tabella in futuro.

Tabella schema master

A questo punto si consolidano le definizioni dello schema in una singola tabella e si aggiungerà una funzione helper (GetSchemaForEntity) che consente di cercare la definizione in base a un nome di entità , ad esempio 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 &"'";

Aggiunta del supporto dello schema alle funzioni dati

A questo punto si aggiungerà un parametro facoltativo schema alle TripPin.Feedfunzioni , GetPagee GetAllPagesByNextLink . In questo modo sarà possibile passare lo schema (quando si vuole) alle funzioni di paging, in cui verrà applicato ai risultati restituiti dal servizio.

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

Si aggiorneranno anche tutte le chiamate a queste funzioni per assicurarsi di passare correttamente lo schema.

Applicazione dello schema

L'applicazione effettiva dello schema verrà eseguita nella GetPage funzione.

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

Aggiunta della funzione GetEntity

La funzione eseguirà il wrapping della GetEntity chiamata a TripPin.Feed. Cercherà una definizione dello schema in base al nome dell'entità e compilerà l'URL completo della richiesta.

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;

Si aggiornerà quindi la TripPinNavTable funzione per chiamare GetEntity, anziché eseguire tutte le chiamate inline. Il vantaggio principale è che consente di continuare a modificare il codice di compilazione dell'entità, senza dover toccare la logica della tabella di spostamento.

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;

Combinazione delle funzionalità

Dopo aver apportato tutte le modifiche al codice, compilare ed eseguire nuovamente la query di test che chiama Table.Schema la tabella Airlines.

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

Si noterà ora che la tabella Airlines include solo le due colonne definite nel relativo schema:

Se si esegue lo stesso codice sulla tabella Persone...

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

Si noterà che anche il tipo ascritto usato (Int64.Type) è stato impostato correttamente.

Un aspetto importante da notare è che questa implementazione di SchemaTransformTable non modifica i tipi di list colonne e record , ma le Emails colonne e AddressInfo sono ancora tipizzate come list. Questo perché Json.Document eseguirà correttamente il mapping delle matrici JSON agli elenchi M e agli oggetti JSON ai record M. Se si desidera espandere l'elenco o la colonna di record in Power Query, si noterà che tutte le colonne espanse saranno di tipo qualsiasi. Le esercitazioni future miglioreranno l'implementazione per impostare in modo ricorsivo le informazioni sul tipo per i tipi complessi annidati.

Conclusione

Questa esercitazione ha fornito un'implementazione di esempio per applicare uno schema sui dati JSON restituiti da un servizio REST. Anche se questo esempio usa un semplice formato di tabella dello schema hardcoded, l'approccio può essere espanso creando dinamicamente una definizione di tabella dello schema da un'altra origine, ad esempio un file di schema JSON o un servizio metadati/endpoint esposto dall'origine dati.

Oltre a modificare i tipi di colonna (e i valori), il codice imposta anche le informazioni sul tipo corrette nella tabella stessa. L'impostazione di queste informazioni sul tipo consente di migliorare le prestazioni durante l'esecuzione all'interno di Power Query, perché l'esperienza utente tenta sempre di dedurre le informazioni sul tipo per visualizzare le code dell'interfaccia utente corrette all'utente finale e le chiamate di inferenza possono generare altre chiamate alle API dati sottostanti.

Se si visualizza la tabella Persone usando il connettore TripPin della lezione precedente, si noterà che tutte le colonne hanno un'icona "type any" (anche le colonne che contengono elenchi):

Eseguendo la stessa query con il connettore TripPin di questa lezione, si noterà ora che le informazioni sul tipo vengono visualizzate correttamente.

Passaggi successivi

TripPin - Parte 7 - Schema avanzato con tipi M