TripPin parte 6 - Esquema

Este tutorial em várias partes aborda a criação de uma nova extensão de fonte de dados para o Power Query. O tutorial destina-se a ser feito sequencialmente — cada lição baseia-se no conector criado nas aulas anteriores, adicionando gradualmente novas capacidades ao seu conector.

Nesta lição, vocês:

Uma das grandes vantagens de um serviço OData em relação a uma API REST padrão é a sua definição $metadata. O documento $metadata descreve os dados encontrados neste serviço, incluindo o esquema de todas as suas Entidades (Tabelas) e Campos (Colunas). A OData.Feed função utiliza esta definição de esquema para definir automaticamente a informação do tipo de dados. Assim, em vez de terem todos os campos de texto e números (como se faria Json.Document), os utilizadores finais recebem datas, números inteiros, horas, e assim por diante, proporcionando uma melhor experiência geral ao utilizador.

Muitas APIs REST não têm uma forma programática de determinar o seu esquema. Nestes casos, precisa de incluir definições de esquemas dentro do seu conector. Nesta lição, defines um esquema simples e codificado fixamente para cada uma das tuas tabelas e aplicas o esquema aos dados que lês do serviço.

No geral, impor um esquema nos dados devolvidos pelo seu conector tem múltiplos benefícios, tais como:

• Definir os tipos de dados corretos
• Remoção de colunas que não precisam de ser mostradas aos utilizadores finais (como IDs internos ou informações de estado)
• Garantir que cada página de dados tem a mesma forma, adicionando quaisquer colunas que possam estar em falta numa resposta (uma forma comum das APIs REST indicarem um campo null)

Visualização do esquema existente com Table.Schema

O conector criado na lição anterior apresenta três tabelas do serviço TripPin: Airlines, Airports, e People. Execute a seguinte consulta para visualizar a Airlines tabela:

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

Nos resultados, são devolvidas quatro colunas:

• @odata.id
• @odata.editLink
• Código da Companhia Aérea
• Nome

As colunas "@odata.*" fazem parte do protocolo OData, e não são algo que queiras ou precises mostrar aos utilizadores finais do teu conector. AirlineCode e Name são as duas colunas que queres manter. Se olhar para o esquema da tabela (usando a função útil Table.Schema), todas as colunas da tabela têm um tipo de dado de Any.Type.

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

O Table.Schema devolve muitos metadados sobre as colunas de uma tabela, incluindo nomes, posições, informação de tipo e muitas propriedades avançadas, como Precisão, Escala e Comprimento Máximo. As lições futuras fornecem padrões de design para definir estas propriedades avançadas, mas por agora só precisa de se preocupar com o tipo atribuído (TypeName), tipo primitivo (Kind), e se o valor da coluna poderá ser null (IsNullable).

Definição de uma tabela de esquemas simples

A tua tabela de esquemas vai ser composta por duas colunas:

A tabela de esquema codificado para a tabela Airlines define as suas colunas AirlineCode e Name para text, e apresenta-se da seguinte forma:

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

A Airports tabela tem quatro campos que queres manter (incluindo um do tipo record):

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

Finalmente, a People tabela tem sete campos, incluindo listas (Emails, AddressInfo), uma coluna anulável (Gender) e uma coluna com um tipo atribuído (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}
    })

A função auxiliar SchemaTransformTable

A SchemaTransformTable função auxiliar descrita nesta secção é usada para impor esquemas nos seus dados. Assume os seguintes parâmetros:

A lógica para esta função é algo assim:

1. Determine se faltam colunas na tabela de origem.
2. Determina se há colunas extra.
3. Ignore colunas estruturadas (do tipo list, , e record), e colunas definidas como tabletype any.
4. Use Table.TransformColumnTypes para definir cada tipo de coluna.
5. Reordenar as colunas com base na ordem em que aparecem na tabela de esquemas.
6. Defina o tipo na própria tabela usando Value.ReplaceType.

O seguinte código auxiliar pode ser copiado e colado na sua extensão:

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;

Atualização do conector TripPin

Para utilizar o novo código de aplicação de esquemas, faça as seguintes alterações ao seu conector.

1. Defina uma tabela mestra de esquemas (SchemaTable) que contenha todas as suas definições de esquema.
2. Atualize o TripPin.Feed, GetPage e GetAllPagesByNextLink para aceitar um schema parâmetro.
3. Aplica o teu esquema em GetPage.
4. Atualize o código da sua tabela de navegação para envolver cada tabela com uma chamada para uma nova função (GetEntity). Esta função dá-lhe mais flexibilidade para manipular as definições das tabelas no futuro.

Tabela de esquemas mestres

Agora consolide as suas definições de esquema numa única tabela e adicione uma função auxiliar (GetSchemaForEntity) que lhe permita consultar a definição com base no nome de uma entidade (por exemplo, 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 &"'";

Adição de suporte de esquemas às funções de dados

Agora adicione um parâmetro opcional schema às funções , TripPin.Feed, e GetPageGetAllPagesByNextLink. Este parâmetro permite-te passar o esquema (quando quiseres) para as funções de paginação, onde é aplicado aos resultados que recebes do serviço.

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

Também precisas de atualizar todas as chamadas destas funções para garantir que passas o esquema corretamente.

Aplicação do esquema

A aplicação real do esquema é feita na sua função 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];

Adição da função GetEntity

A função GetEntity encapsula a chamada para TripPin.Feed. Procura uma definição de esquema com base no nome da entidade e constrói o URL completo do pedido.

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;

Depois, atualiza a função TripPinNavTable para chamar GetEntity, em vez de fazer todas as chamadas in-line. A principal vantagem desta atualização é que te permite continuar a modificar o código de construção da tua entidade, sem teres de mexer na lógica da tua tabela de navegação.

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;

Compilando todos os elementos

Depois de todas as alterações de código forem feitas, compila e reexecuta a consulta de teste que pede Table.Schema a tabela das companhias aéreas.

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

A sua tabela de companhias aéreas tem agora apenas as duas colunas que definiu no seu esquema:

Se executares o mesmo código contra a tabela Pessoas...

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

Note que o tipo atribuído que usou (Int64.Type) também estava corretamente definido.

Um aspeto importante a notar é que esta implementação de SchemaTransformTable não modifica os tipos de colunas de list e record , mas as Emails colunas e AddressInfo continuam a ser tipadas como list. Este comportamento ocorre porque Json.Document mapeia corretamente os arrays JSON para M listas, e os objetos JSON para M registos. Ao expandir a coluna de lista ou de registo no Power Query, todas as colunas expandidas são do tipo any. Os tutoriais futuros irão melhorar a implementação para atribuir recursivamente a informação dos tipos para tipos complexos aninhados.

Conclusion

Este tutorial forneceu uma implementação de exemplo para impor um esquema em dados JSON devolvidos de um serviço REST. Embora este exemplo utilize um formato simples de tabela de esquema codificado, a abordagem pode ser expandida construindo dinamicamente uma definição de tabela de esquema a partir de outra fonte, como um ficheiro de esquema JSON, ou um serviço/endpoint de metadados exposto pela fonte de dados.

Para além de modificar os tipos (e valores) das colunas, o seu código também define a informação correta do tipo na própria tabela. Definir este tipo de informação beneficia o desempenho quando é executado dentro do Power Query, pois a experiência do utilizador tenta sempre inferir informação de tipo para mostrar as filas de UI corretas ao utilizador final, e as chamadas de inferência podem acabar por desencadear outras chamadas para as APIs de dados subjacentes.

Se visualizares a tabela People usando o conector TripPin da lição anterior, todas as colunas têm um ícone 'escreve qualquer' (até as colunas que contêm listas):

Se executar a mesma consulta com o conector TripPin desta lição, a informação de tipo é agora apresentada corretamente.

Próximos passos

TripPin Parte 7 - Esquema Avançado com Tipos M