TripPin parte 6 – Esquema

Este tutorial de várias partes aborda a criação de uma nova extensão de fonte de dados para o Power Query. O tutorial deve ser feito sequencialmente — cada lição se baseia no conector criado nas lições anteriores, adicionando progressivamente novos recursos ao conector.

Nesta lição, você:

Uma das grandes vantagens de um serviço OData em relação a uma API REST padrão é sua definição de $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 usa essa definição de esquema para definir automaticamente as informações de tipo de dados. Portanto, em vez de obter todos os campos de texto e número (como você faria com Json.Document), os usuários finais obtêm datas, números inteiros, horas e assim por diante, proporcionando uma melhor experiência geral do usuário.

Muitas APIs REST não têm uma maneira de determinar programaticamente seu esquema. Nesses casos, você precisa incluir definições de esquema no conector. Nesta lição, você define um esquema simples e codificado para cada uma de suas tabelas e impõe o esquema nos dados lidos do serviço.

No geral, a imposição de um esquema nos dados retornados pelo conector tem vários benefícios, como:

• Definindo os tipos de dados corretos
• Removendo colunas que não precisam ser mostradas aos usuários finais (como IDs internas ou informações de estado)
• Garantindo que cada página de dados tenha a mesma forma adicionando colunas que possam estar ausentes de uma resposta (uma maneira comum para as APIs REST indicarem que um campo deve ser null)

Exibindo o esquema existente com Table.Schema

O conector criado na lição anterior exibe três tabelas do serviço TripPin: Airlines, Airportse People. Execute a seguinte consulta para exibir a Airlines tabela:

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

Nos resultados, quatro colunas são retornadas:

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

As colunas "@odata.*" fazem parte do protocolo OData e não é algo que você deseja ou precisa mostrar aos usuários finais do conector. AirlineCode e Name são as duas colunas que você deseja manter. Se você examinar o esquema da tabela (usando a função útil Table.Schema), todas as colunas da tabela têm um tipo de dados de Any.Type.

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

Table.Schema retorna muitos metadados sobre as colunas em uma tabela, incluindo nomes, posições, informações de tipo e muitas propriedades avançadas, como Precisão, Escala e MaxLength. As lições futuras fornecem padrões de design para definir essas propriedades avançadas, mas por enquanto você só precisa se preocupar com o tipo atribuído (TypeName), o tipo primitivo (Kind) e se o valor da coluna pode ser null (IsNullable).

Definindo uma tabela de esquema simples

Sua tabela de esquema será composta por duas colunas:

O esquema de tabela codificado para a tabela Airlines define as colunas AirlineCode e Name como text, e se apresenta assim:

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

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

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

Por fim, a People tabela tem sete campos, incluindo listas (Emails, AddressInfo), uma coluna anulável (Gender) e uma coluna com um tipo inscrito (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 seção é usada para impor esquemas em seus dados. Ele usa os seguintes parâmetros:

A lógica dessa função é semelhante a esta:

1. Determine se há colunas ausentes da tabela de origem.
2. Determine se há colunas extras.
3. Ignorar colunas estruturadas (do tipo list, record e table) e colunas definidas como type any.
4. Use Table.TransformColumnTypes para definir cada tipo de coluna.
5. Reordene colunas com base na ordem em que aparecem na tabela de esquema.
6. Defina o tipo na própria tabela usando Value.ReplaceType.

O seguinte código auxiliar pode ser copiado e colado em 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;

Atualizando o conector TripPin

Para usar o novo código de imposição de esquema, faça as seguintes alterações no conector.

1. Defina uma tabela de esquema mestre (SchemaTable) que contém todas as definições de esquema.
2. Atualize o TripPin.Feed, o GetPage e o GetAllPagesByNextLink para aceitar um parâmetro schema.
3. Aplique seu esquema em GetPage.
4. Atualize o código da tabela de navegação para encapsular cada tabela com uma chamada para uma nova função (GetEntity). Essa função oferece mais flexibilidade para manipular as definições de tabela no futuro.

Tabela de esquema mestre

Agora consolide suas definições de esquema em uma única tabela e adicione uma função auxiliar (GetSchemaForEntity) que permite pesquisar a definição com base em um nome de 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 &"'";

Adicionando suporte de esquema a funções de dados

Agora, adicione um parâmetro opcional schema ao TripPin.Feed. GetPagee GetAllPagesByNextLink funções. Esse parâmetro permite que você passe o esquema (quando desejar) para as funções de paginação, em que ele é aplicado aos resultados que você obtém 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 => ...

Você também precisa atualizar todas as chamadas para essas funções para garantir que você transmita o esquema corretamente.

Impondo o esquema

A real imposição de 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];

Adicionando a função GetEntity

A GetEntity função encapsula sua chamada para TripPin.Feed. Ele pesquisa uma definição de esquema com base no nome da entidade e cria a URL de solicitação completa.

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;

Em seguida, você atualiza sua função TripPinNavTable para chamar GetEntity, em vez de fazer todas as chamadas diretamente. A principal vantagem dessa atualização é que ela permite que você continue modificando o código de criação da entidade, sem precisar tocar na lógica da 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;

Juntando as peças

Depois que todas as alterações de código forem feitas, compile e execute novamente a consulta de teste que chama Table.Schema para a tabela Airlines.

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

Sua tabela Airlines agora tem apenas as duas colunas que você definiu em seu esquema:

Se você executar o mesmo código na tabela People...

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

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

Uma coisa importante a se notar é que essa implementação de SchemaTransformTable não modifica os tipos das colunas list e record, mas as colunas Emails e AddressInfo ainda são tipadas como list. Esse comportamento ocorre porque Json.Document mapeia corretamente matrizes JSON para listas M e objetos JSON para registros M. Se você expandir a lista ou a coluna de registro no Power Query, todas as colunas expandidas serão do tipo any. Tutoriais futuros irão melhorar a implementação para recursivamente definir informações de tipo para tipos complexos aninhados.

Conclusion

Este tutorial forneceu uma implementação de exemplo para impor um esquema em dados JSON retornados de um serviço REST. Embora este exemplo use um formato de tabela de esquema codificado simples, a abordagem pode ser expandida criando dinamicamente uma definição de tabela de esquema de outra fonte, como um arquivo de esquema JSON ou um serviço de metadados/ponto de extremidade exposto pela fonte de dados.

Além de modificar tipos de coluna (e valores), seu código também está definindo as informações de tipo corretas na própria tabela. Definir essas informações de tipo beneficia o desempenho ao executar dentro do Power Query, pois a experiência do usuário sempre tenta inferir informações de tipo para exibir as filas de interface do usuário certas para o usuário final, e as chamadas de inferência podem acabar disparando outras chamadas para as APIs de dados subjacentes.

Se você exibir a tabela Pessoas usando o conector TripPin da lição anterior, todas as colunas terão um ícone "tipo qualquer" (até mesmo as colunas que contêm listas):

Se você executar a mesma consulta com o conector TripPin nesta lição, as informações de tipo agora serão exibidas corretamente.

Próximas etapas

TripPin Part 7 – Esquema avançado com tipos M