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:
- Definire uno schema fisso per un'API REST
- Impostare dinamicamente i tipi di dati per le colonne
- Applicare una struttura di tabella per evitare errori di trasformazione dovuti a colonne mancanti
- Nascondere le colonne dal set di risultati
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.
Nota
L'approccio descritto qui dovrebbe funzionare per molti servizi REST. Le lezioni future si baseranno su questo approccio applicando in modo ricorsivo schemi su colonne strutturate (record, elenco, tabella) e implementeranno implementazioni di esempio in grado di generare a livello di codice una tabella dello schema da documenti CSDL o JSON Schema .
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 Airports
People
. 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:
Colonna | Dettagli |
---|---|
Nome | Nome della colonna. Deve corrispondere al nome nei risultati restituiti dal servizio. |
Type | Tipo di dati M da impostare. Può trattarsi di un tipo primitivo (text , number , datetime e così via) o di un tipo ascritto (Int64.Type , Currency.Type e così via). |
La tabella dello schema hardcoded per la Airlines
tabella imposta le colonne AirlineCode
e Name
su e ha un aspetto simile al text
seguente:
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:
Parametro | Tipo | Descrizione |
---|---|---|
table | table | La tabella dei dati su cui si vuole applicare lo schema. |
schema | table | Tabella dello schema da cui leggere le informazioni sulle colonne con il tipo seguente: type table [Name = text, Type = type] . |
enforceSchema | number | (facoltativo) Enumerazione che controlla il comportamento della funzione. Il valore predefinito ( EnforceSchema.Strict = 1 ) garantisce che la tabella di output corrisponda alla tabella dello schema fornita aggiungendo eventuali colonne mancanti e rimuovendo colonne aggiuntive. L'opzione EnforceSchema.IgnoreExtraColumns = 2 può essere usata per mantenere colonne aggiuntive nel risultato. Quando EnforceSchema.IgnoreMissingColumns = 3 viene usato, le colonne mancanti e le colonne aggiuntive verranno ignorate. |
La logica per questa funzione è simile alla seguente:
- Determinare se sono presenti colonne mancanti nella tabella di origine.
- Determinare se sono presenti colonne aggiuntive.
- Ignorare le colonne strutturate (di tipo
list
,record
etable
) e le colonne impostate sutype any
. - Usare Table.TransformColumnTypes per impostare ogni tipo di colonna.
- Riordinare le colonne in base all'ordine in cui vengono visualizzate nella tabella dello schema.
- Impostare il tipo nella tabella stessa usando Value.ReplaceType.
Nota
L'ultimo passaggio per impostare il tipo di tabella rimuoverà la necessità dell'interfaccia utente di Power Query di dedurre le informazioni sul tipo quando si visualizzano i risultati nell'editor di query. In questo modo viene rimosso il doppio problema di richiesta visualizzato alla fine dell'esercitazione precedente.
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.
- Definire una tabella dello schema master (
SchemaTable
) che contiene tutte le definizioni dello schema. TripPin.Feed
Aggiornare ,GetPage
eGetAllPagesByNextLink
per accettare unschema
parametro.- Applicare lo schema in
GetPage
. - 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.Feed
funzioni , GetPage
e 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];
Nota
Questa GetPage
implementazione usa Table.FromRecords per convertire l'elenco di record nella risposta JSON in una tabella.
Uno svantaggio principale dell'uso di Table.FromRecords è che presuppone che tutti i record nell'elenco abbiano lo stesso set di campi.
Questa operazione funziona per il servizio TripPin, poiché i record OData sono guarenti per contenere gli stessi campi, ma questo potrebbe non essere il caso per tutte le API REST.
Un'implementazione più affidabile userebbe una combinazione di Table.FromList e Table.ExpandRecordColumn.
Le esercitazioni successive modificheranno l'implementazione per ottenere l'elenco di colonne dalla tabella dello schema, assicurandosi che nessuna colonna venga persa o mancante durante la conversione DA JSON a M.
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.