Partilhar via

TripPin parte 6 - Esquema

  • Artigo

Este tutorial com 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 incrementalmente novos recursos ao seu conector.

Nesta lição, você irá:

  • 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 $metadata definição. O documento $metadata descreve os dados encontrados neste serviço, incluindo o esquema para 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), Json.Documentos 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ê precisará incluir definições de esquema no conector. Nesta lição, você definirá um esquema simples e codificado para cada uma de suas tabelas e aplicará o esquema nos dados lidos do serviço.

Nota

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

No geral, impor um esquema nos dados retornados pelo conector tem vários benefícios, como:

  • Definindo os tipos de dados corretos
  • Remoção de colunas que não precisam ser mostradas aos usuários finais (como IDs internos ou informações de estado)
  • Garantir que cada página de dados tenha a mesma forma, adicionando quaisquer colunas que possam estar faltando em uma resposta (uma maneira comum para APIs REST indicarem que um campo deve ser nulo)

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, você verá quatro colunas retornadas:

  • @odata.id
  • @odata.editLink
  • Código da companhia aérea
  • Nome

Companhias aéreas sem esquema.

As colunas "@odata.*" fazem parte do protocolo OData e não são algo que você gostaria ou precisa mostrar aos usuários finais do seu conector. AirlineCode e Name são as duas colunas que você vai querer manter. Se você observar o esquema da tabela (usando a prática função Table.Schema ), poderá ver que 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)

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 Precision, Scale e MaxLength. Lições futuras 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), 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:

Column Detalhes
Nome O nome da coluna. Isso deve corresponder ao nome nos resultados retornados pelo serviço.
Type O tipo de dados 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 codificada para a Airlines tabela define suas AirlineCode colunas e Name como text, e tem esta aparência:

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

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 abaixo será usada para impor esquemas em seus dados. Ele leva os seguintes parâmetros:

Parâmetro Tipo Description
tabela tabela A tabela de dados na qual você desejará impor seu esquema.
esquema tabela A tabela de esquema a partir da qual ler informações de coluna, com o seguinte tipo: type table [Name = text, Type = type].
enforceSchema Número (opcional) Um enum 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 que foi fornecida, adicionando quaisquer colunas ausentes e removendo colunas extras.
A EnforceSchema.IgnoreExtraColumns = 2 opção 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 esta função é mais ou menos assim:

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

Nota

A última etapa para definir o tipo de tabela removerá a necessidade de a 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 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;

Atualizando o conector TripPin

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

  1. Defina uma tabela de esquema mestre (SchemaTable) que contenha todas as suas definições de esquema.
  2. Atualize o TripPin.Feed, GetPagee GetAllPagesByNextLink aceite um schema parâmetro.
  3. Aplique seu esquema no GetPage.
  4. Atualize o código da tabela de navegação para envolver 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 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 você adicionará um parâmetro opcional schema às TripPin.Feedfunções , GetPagee GetAllPagesByNextLink . Isso permitirá que você passe o esquema (quando quiser) para as funções de paginação, onde será aplicado aos resultados que você obtiver de volta 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.

Aplicando o esquema

A aplicação do esquema real será feita em sua GetPage função.

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

Essa GetPage implementação usa Table.FromRecords para converter a lista de registros na resposta JSON em uma tabela. Uma grande desvantagem de usar Table.FromRecords é que ele assume que todos os registros na lista têm o mesmo conjunto de campos. Isso funciona para o serviço TripPin, já que os registros OData são garantidos para conter os mesmos campos, mas esse pode não ser o caso para todas as APIs REST. Uma implementação mais robusta usaria uma combinação de Table.FromList e Table.ExpandRecordColumn. Tutoriais posteriores alterarão a implementação para obter a lista de colunas da tabela de esquema, garantindo que nenhuma coluna seja perdida ou ausente durante a tradução de JSON para M.

Adicionando a função GetEntity

A GetEntity função encerrará sua chamada para o TripPin.Feed. Ele procurará 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á sua TripPinNavTable função para chamar GetEntity, em vez de fazer todas as chamadas em linha. A principal vantagem disso é que ele permitirá que você continue modificando seu código de construção de entidade, sem ter que 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;

Juntar tudo

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

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

Agora você vê que sua tabela Airlines tem apenas as duas colunas definidas em seu esquema:

companhias aéreas com esquema.

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 que você usou (Int64.Type) também foi definido corretamente.

Pessoas com esquema.

Uma coisa importante a notar é que esta implementação de SchemaTransformTable não modifica os tipos de list e record colunas, mas as Emails colunas e AddressInfo ainda são digitadas como list. Isso ocorre porque Json.Document mapeará corretamente matrizes JSON para listas M e objetos JSON para registros M. Se expandisse a coluna de lista ou registo no Power Query, veria que todas as colunas expandidas serão do tipo qualquer. Tutoriais futuros melhorarão 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 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 serviço/ponto de extremidade de metadados expostos 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. A definição destas informações de tipo beneficia o desempenho quando executada dentro do Power Query, uma vez que a experiência do utilizador tenta sempre inferir informações de tipo para apresentar as filas de IU corretas ao utilizador final e as chamadas de inferência podem acabar por acionar outras chamadas para as APIs de dados subjacentes.

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

Pessoas sem esquema.

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

Pessoas com esquema.

Próximos passos

TripPin Parte 7 - Esquema avançado com tipos M