TripPin Parte 6 – Esquema

Este tutorial de várias partes aborda a criação de uma nova extensão de fonte de dados para 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 conector.

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 $metadata . O $metadata documento descreve os dados encontrados nesse serviço, incluindo o esquema para todas as suas Entidades (Tabelas) e Campos (Colunas). A função 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 de ), os usuários finais obterão datas, números inteiros, horas e assim por diante, fornecendo uma melhor experiência geral do OData.FeedJson.Document 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 em seu conector. Nesta lição, você definirá um esquema simples e 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 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 de documentos de esquema CSDL ou JSON.

Em 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)
  • Garantir que cada página de dados tenha a mesma forma adicionando quaisquer colunas que possam estar ausentes de 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 e Airports 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
  • AirlineCode
  • Nome

Linhas aéreas sem esquema.

As colunas ".*" fazem parte do protocolo OData e não são algo que você deseja ou precisa mostrar aos usuários finais @odata do seu conector. AirlineCode e Name são as duas colunas que você deseja manter. Se você olhar para o esquema da tabela (usando a função Table.Schema útil), poderá ver que todas as colunas na 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 Precision, Scale e MaxLength. As 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 ( ), tipo primitivo ( ) e se o valor da coluna pode ser TypeName Kind 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 M que você vai definir. Pode ser um tipo primitivo ( , , e assim por diante) ou um tipo atribuído text ( , e assim por number datetime Int64.Type Currency.Type diante).

A tabela de esquema em código para a tabela definirá suas colunas e Airlines como e terá esta AirlineCode Name text 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}
    });

Por fim, a tabela tem sete campos, incluindo listas ( , ), uma coluna que pode ser anulada ( ) e uma coluna com People um tipo Emails atribuído ( AddressInfo Gender 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 usa os seguintes parâmetros:

Parâmetro Tipo Descrição
tabela tabela A tabela de dados na que você deseja impor seu esquema.
esquema tabela A tabela de esquema da qual ler informações de coluna, com o seguinte tipo: type table [Name = text, Type = type] .
enforceSchema número (opcional) Uma enum que controla o comportamento da função.
O valor padrão ( ) garante que a tabela de saída corresponderá à tabela de esquema fornecida adicionando colunas ausentes e removendo EnforceSchema.Strict = 1 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 essa função é semelhante a esta:

  1. Determine se há colunas ausentes da tabela de origem.
  2. Determine se há colunas extras.
  3. Ignore colunas estruturadas (do list tipo , e ) e record table 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. De 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 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 código auxiliar a seguir pode ser copiado e copiado 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 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 contém todas as suas definições de esquema.
  2. Atualize TripPin.Feed , e para aceitar um parâmetro GetPage GetAllPagesByNextLink schema .
  3. Im impõe 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 ( ) isso lhe dará mais flexibilidade para manipular as definições GetEntity — 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 ( ) que permite que você procure a definição com base em um nome de entidade GetSchemaForEntity (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 a esquema a funções de dados

Agora você adicionará um parâmetro opcional às funções schema TripPin.Feed , e GetPage GetAllPagesByNextLink . Isso permitirá que você passe o esquema (quando quiser) para as funções de paging, em que ele será aplicado aos resultados obtidos 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.

Impor o esquema

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

[Observação] Essa GetPage implementação usa Table.FromRecords para converter a lista de registros na resposta JSON em uma tabela. Uma grande desvantagem ao usar Table.FromRecords é que ele pressu que todos os registros na lista tenham o mesmo conjunto de campos. Isso funciona para o serviço TripPin, pois os registros OData são inserdos para conter os mesmos campos, mas isso 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. 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 ausente durante a conversão de JSON para M.

Adicionando a função GetEntity

A GetEntity função envolverá sua chamada para 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 , em vez de fazer todas as chamadas em GetEntity linha. A principal vantagem disso é que ele permitirá que você continue modificando seu código de criação de entidade, sem precisar tocar na lógica da tabela de nav.

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 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 companhias aéreas.

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

Agora você vê que a tabela companhias aéreas tem apenas as duas colunas que você definiu em seu esquema:

Companhias aéreas com esquema.

Se você executar o mesmo código em relação à tabela People...

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

Você verá que o tipo astranscritaed que você usou ( Int64.Type ) também foi definido corretamente.

Pessoas com esquema.

Uma coisa importante a observar é que essa implementação do SchemaTransformTable não modifica os tipos de list record colunas e, mas as Emails AddressInfo colunas e ainda são digitadas como list . Isso ocorre porque o Json.Document mapeará corretamente as matrizes JSON para as listas M e os objetos JSON para os registros m. Se você fosse expandir a coluna de lista ou de registro em Power Query, verá que todas as colunas expandidas serão do tipo qualquer. Os tutoriais futuros melhorarão a implementação para definir recursivamente as 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 uma definição de tabela de esquema dinamicamente 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 os 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 durante a execução dentro de 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 os usuários finais, 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 de lista da lição anterior, verá que todas as colunas têm um ícone ' tipo qualquer ' (até mesmo as colunas que contêm listas):

Pessoas sem esquema.

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

Pessoas com esquema.

Próximas etapas

Parte 7-esquema avançado com tipos M