TripPin parte 6 - Esquema

  • Artigo

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 seguido sequencialmente; cada lição se baseia no conector criado nas lições anteriores, adicionando incrementalmente novos recursos a ele.

Nesta lição, você vai:

  • Definir um esquema fixo para uma API REST
  • Definir dinamicamente tipos de dados para colunas
  • Impor uma estrutura de tabela para evitar erros de transformação devido a colunas ausentes
  • Ocultar colunas do conjunto de resultados

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 nesse serviço, incluindo o esquema de todas as suas Entidades (Tabelas) e Campos (Colunas). A função OData.Feed usa essa definição de esquema para definir automaticamente informações de tipo de dados, portanto, em vez de obter todos os campos de texto e número (como você faria em Json.Document), os usuários finais obterão datas, números inteiros, horas e assim por diante, fornecendo 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ê precisará incluir definições de esquema no conector. Nesta lição, você definirá um esquema simples e embutido em código para cada uma de suas tabelas e imporá o esquema nos dados lidos do serviço.

Observação

A abordagem descrita aqui deve funcionar para muitos serviços REST. As próximas lições se basearão nessa abordagem, impondo recursivamente esquemas em colunas estruturadas (registro, lista, tabela) e fornecerão implementações de exemplo que podem gerar programaticamente uma tabela de documentos de esquema JSON ou CSDL.

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

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

Visualizando o esquema existente com Table.Schema

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

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

Nos resultados, você verá quatro colunas retornadas:

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

Airlines no schema.

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ê vai querer manter. Se você examinar o esquema da tabela (usando a função Table.Schema útil), poderá ver que todas as colunas da tabela têm um tipo de dados Any.Type.

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

Airlines Table.Schema.

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 próximas lições fornecerão 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 nulo (IsNullable).

Definindo uma tabela de esquema simples

Sua tabela de esquema será composta por duas colunas:

Coluna Detalhes
Nome O nome da coluna. Isso deve corresponder ao nome nos resultados retornados pelo serviço.
Tipo O tipo de dados da linguagem M que você vai definir. Pode ser um tipo primitivo (text, number, datetime e assim por diante) ou um tipo atribuído (Int64.Type, Currency.Type e assim por diante).

A tabela de esquema embutida em código para a tabela Airlines definirá as colunas AirlineCode e Name como text e terá esta aparência:

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

A tabela Airports 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 tabela People 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 de SchemaTransformTable

A função auxiliar SchemaTransformTable descrita abaixo será usada para impor esquemas em seus dados. Ele usa os seguintes parâmetros:

Parâmetro Tipo Descrição
tabela tabela A tabela de dados em que você deseja impor o esquema.
esquema tabela A tabela de esquema da qual ler as informações da coluna, com o seguinte tipo: type table [Name = text, Type = type].
enforceSchema number (opcional) Uma enumeração que controla o comportamento da função.
O valor padrão (EnforceSchema.Strict = 1) garante que a tabela de saída corresponda à tabela de esquema fornecida adicionando colunas ausentes e removendo colunas extras.
A opção EnforceSchema.IgnoreExtraColumns = 2 pode ser usada para preservar colunas extras no resultado.
Quando EnforceSchema.IgnoreMissingColumns = 3 for usado, as colunas ausentes e as colunas extras serão ignoradas.

A lógica para essa função tem esta aparência:

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

Observação

A última etapa para definir o tipo de tabela removerá a necessidade da interface do usuário do Power Query inferir informações de tipo ao exibir os resultados no editor de consultas. Isso remove o problema de solicitação dupla que você viu no final do tutorial anterior.

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

Agora você fará as alterações a seguir no conector para usar o novo código de imposição de esquema.

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

Tabela de esquema mestre

Agora você consolidará suas definições de esquema em uma única tabela e adicionará uma função auxiliar (GetSchemaForEntity) que permitirá 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 você adicionará um parâmetro opcional schema às funções TripPin.Feed, GetPage e GetAllPagesByNextLink. Isso permitirá que você passe o esquema (quando desejar) para as funções de paginação, em que ele será aplicado aos resultados que você obterá 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 atualizará todas as chamadas para essas funções para garantir que você passe o esquema corretamente.

Impor o esquema

A imposição real do esquema será feita em 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];

[Observação] Essa implementação de GetPage usa Table.FromRecords para converter a lista de registros da resposta JSON em uma tabela. Uma grande desvantagem em usar Table.FromRecords é que ela pressupõe que todos os registros da lista têm o mesmo conjunto de campos. Isso funciona no serviço TripPin, uma vez que é garantido que os registros OData contém os mesmos campos, mas isso pode não ser o caso de todas as APIs REST. Uma implementação mais robusta usaria uma combinação de Table.FromList e Table.ExpandRecordColumn. Os tutoriais posteriores alterarão a implementação para obter a lista de colunas da tabela de esquema, garantindo que nenhuma coluna seja perdida ou falte durante a conversão de JSON para M.

Adicionando a função GetEntity

A função GetEntity encapsulará sua chamada para TripPin.Feed. Ela pesquisará uma definição de esquema com base no nome da entidade e criará 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ê atualizará a função TripPinNavTable para chamar GetEntity, em vez de fazer todas as chamadas em linha. A principal vantagem disso é que ela permitirá que você continue modificando seu código de criação de entidade, sem precisar mexer 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;

Como reunir tudo

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 Linhas Aéreas.

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

Agora você verá que a tabela Linhas Aéreas tem apenas as duas colunas que você definiu no esquema:

Airlines With Schema.

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

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

Você verá que o tipo atribuído usado (Int64.Type) também foi definido corretamente.

People With Schema.

Algo importante a observar é que essa implementação de SchemaTransformTable não modifica os tipos das colunas list e record, mas as colunas Emails e AddressInfo permanecem tipadas como list. Isso ocorre porque Json.Document mapeará corretamente matrizes JSON para listas da M, e objetos JSON para registros da M. Se você for expandir a lista ou a coluna de registro no Power Query, verá que todas as colunas expandidas serão do tipo Qualquer. Os próximos tutoriais vão aprimorar a implementação para definir recursivamente informações de tipo para tipos complexos aninhados.

Conclusão

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 simples embutido em código, 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 corretas para o usuário final, e as chamadas de inferência podem acabar disparando chamadas adicionais para as APIs de dados subjacentes.

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

People without Schema.

Executando a mesma consulta com o conector TripPin desta lição, agora você verá que as informações de tipo são exibidas corretamente.

People with Schema.

Próximas etapas

TripPin Parte 7 – Esquema avançado com Tipos da M