Leggere in inglese

Condividi tramite


Gestione dello schema

A seconda dell'origine dati, le informazioni sui tipi di dati e sui nomi delle colonne possono essere fornite o meno in modo esplicito. Le API REST OData gestiscono in genere questa operazione usando la definizione di $metadata e il metodo di Power Query OData.Feed gestisce automaticamente l'analisi di queste informazioni e l'applicazione ai dati restituiti da un'origine OData.

Molte API REST non hanno un modo per determinare a livello di codice lo schema. In questi casi è necessario includere una definizione di schema nel connettore.

Approccio hardcoded semplice

L'approccio più semplice consiste nel impostare come hardcoded una definizione di schema nel connettore. Questo è sufficiente per la maggior parte dei casi d'uso.

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

  • Impostazione dei tipi di dati corretti.
  • La rimozione di colonne che non devono essere visualizzate agli utenti finali ,ad esempio ID interni o informazioni sullo stato.
  • Assicurarsi che ogni pagina di dati abbia la stessa forma aggiungendo tutte le colonne che potrebbero non essere presenti in una risposta (le API REST indicano in genere che i campi devono essere Null omettendoli completamente).

Visualizzazione dello schema esistente con Table.Schema

Si consideri il codice seguente che restituisce una tabella semplice dal servizio di esempio OData TripPin:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    asTable

Nota

TripPin è un'origine OData, quindi realisticamente sarebbe più utile usare semplicemente la OData.Feed gestione automatica dello schema della funzione. In questo esempio si considererà l'origine come un'API REST tipica e si userà Web.Contents per illustrare la tecnica di hardcoding di uno schema a mano.

Questa tabella è il risultato:

Tabella dei dati di TripPin Airline.

È possibile usare la funzione utile Table.Schema per controllare il tipo di dati delle colonne:

let
    url = "https://services.odata.org/TripPinWebApiService/Airlines",
    source = Json.Document(Web.Contents(url))[value],
    asTable = Table.FromRecords(source)
in
    Table.Schema(asTable)

Risultato di Table.Schema applicato ai dati di TripPin Airline.

Sia AirlineCode che Name sono di any tipo. 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. Per il momento è consigliabile 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ò essere un tipo primitivo (text, number, datetime e così via) o un tipo ascritto (Int64.Type, Currency.Type e così via).

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

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

Quando si esaminano alcuni degli altri endpoint, considerare le tabelle dello schema seguenti:

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}
    })

La People tabella include sette campi, inclusi lists (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}
    })

È possibile inserire tutte queste tabelle in una singola tabella SchemaTabledello schema master :

SchemaTable = #table({"Entity", "SchemaTable"}, {
        {"Airlines", Airlines},
        {"Airports", Airports},
        {"People", People}
    })

Tabella degli schemi.

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:

  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 sul tipo any.
  4. Utilizzare 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.

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, che a volte può comportare una doppia chiamata all'API.

Combinazione delle funzionalità

Nel contesto maggiore di un'estensione completa, la gestione dello schema verrà eseguita quando viene restituita una tabella dall'API. In genere questa funzionalità viene eseguita al livello più basso della funzione di paging (se presente), con le informazioni sull'entità passate da una tabella di spostamento.

Poiché gran parte dell'implementazione di paging e tabelle di spostamento è specifica del contesto, l'esempio completo di implementazione di un meccanismo di gestione dello schema hardcoded non verrà illustrato qui. In questo esempio TripPin viene illustrato l'aspetto di una soluzione end-to-end.

Approccio sofisticato

L'implementazione hardcoded descritta in precedenza offre un buon lavoro per assicurarsi che gli schemi rimangano coerenti per semplici repsoning JSON, ma è limitato all'analisi del primo livello della risposta. I set di dati annidati in modo approfondito traggono vantaggio dall'approccio seguente, che sfrutta i tipi M.

Di seguito è riportato un aggiornamento rapido dei tipi nella lingua M dalla specifica del linguaggio:

Un valore tipo è un valore che classifica altri valori. Si dice che un valore classificato in base a un tipo sia conforme a quel tipo. Il sistema di tipi del linguaggio M è costituito dai tipi seguenti:

  • I tipi primitivi, che classificano i valori primitivi (binary, date, datetime, durationdatetimezone, recordnullnumberlogicaltextlisttime, type) e includono anche diversi tipi astratti (function, table, anye ).none
  • Tipi di record, che classificano i valori dei record in base ai nomi dei campi e ai tipi valore.
  • Tipi di elenco, che classificano gli elenchi usando un singolo tipo di base di elementi.
  • Tipi di funzione, che classificano i valori delle funzioni in base ai tipi dei relativi parametri e valori restituiti.
  • Tipi di tabella, che classificano i valori della tabella in base a nomi di colonna, tipi di colonna e chiavi.
  • Tipi nullable, che classificano il valore Null oltre a tutti i valori classificati da un tipo di base.
  • Tipi, che classificano i valori tipi.

Usando l'output JSON non elaborato che si ottiene (e/o cercando le definizioni nella $metadata del servizio), è possibile definire i tipi di record seguenti per rappresentare i tipi complessi OData:

LocationType = type [
    Address = text,
    City = CityType,
    Loc = LocType
];

CityType = type [
    CountryRegion = text,
    Name = text,
    Region = text
];

LocType = type [
    #"type" = text,
    coordinates = {number},
    crs = CrsType
];

CrsType = type [
    #"type" = text,
    properties = record
];

Si noti come LocationType fa riferimento a CityType e LocType per rappresentare le colonne strutturate.

Per le entità di primo livello che si desidera rappresentare come tabelle, è possibile definire i tipi di tabella:

AirlinesType = type table [
    AirlineCode = text,
    Name = text
];
AirportsType = type table [
    Name = text,
    IataCode = text,
    Location = LocationType
];
PeopleType = type table [
    UserName = text,
    FirstName = text,
    LastName = text,
    Emails = {text},
    AddressInfo = {nullable LocationType},
    Gender = nullable text,
    Concurrency  Int64.Type
];

È quindi possibile aggiornare la SchemaTable variabile (che è possibile usare come tabella di ricerca per i mapping da entità a tipo) per usare queste nuove definizioni di tipo:

SchemaTable = #table({"Entity", "Type"}, {
    {"Airlines", AirlinesType},
    {"Airports", AirportsType},
    {"People", PeopleType}
});

È possibile fare affidamento su una funzione comune (Table.ChangeType) per applicare uno schema ai dati, in modo analogo a quello usato SchemaTransformTable nell'esercizio precedente. A differenza di SchemaTransformTable, Table.ChangeType accetta un tipo di tabella M effettivo come argomento e applicherà lo schema in modo ricorsivo per tutti i tipi annidati. La firma è:

Table.ChangeType = (table, tableType as type) as nullable table => ...

Nota

Per una maggiore flessibilità, la funzione può essere usata nelle tabelle e negli elenchi di record (che è il modo in cui le tabelle vengono rappresentate in un documento JSON).

Sarà quindi necessario aggiornare il codice del connettore per modificare il schema parametro da a table a typee aggiungere una chiamata a Table.ChangeType. Anche in questo caso, i dettagli per farlo sono molto specifici dell'implementazione e quindi non vale la pena approfondire in dettaglio qui. Questo esempio di connettore TripPin esteso illustra una soluzione end-to-end che implementa questo approccio più sofisticato alla gestione dello schema.