TripPin parte 7 - Esquema avançado com tipos M

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:

Na aula anterior, definiste os teus esquemas de tabela usando um sistema simples de "Tabela de Esquemas". Esta abordagem de tabela de esquema funciona para muitas APIs/Conectores de Dados REST. Mas serviços que retornam conjuntos de dados completos ou profundamente aninhados podem beneficiar da abordagem deste tutorial, que utiliza o sistema de tipos M.

Esta lição guia-o pelos seguintes passos:

1. Adicionar testes unitários.
2. Definição de tipos M personalizados.
3. Aplicar um esquema usando tipos.
4. Refatorar código comum em ficheiros separados.

Adição de testes unitários

Antes de começares a usar a lógica avançada de esquemas, precisas de adicionar um conjunto de testes unitários ao teu conector para reduzir a probabilidade de partir algo inadvertidamente. Os testes unitários funcionam assim:

1. Copie o código comum do exemplo UnitTest para o seu TripPin.query.pq ficheiro.
2. Adicione uma declaração de secção no topo do seu TripPin.query.pq ficheiro.
3. Crie um registo partilhado (chamado TripPin.UnitTest).
4. Defina um Fact para cada teste.
5. Liga Facts.Summarize() para fazer todos os testes.
6. Faça referência à chamada anterior como valor partilhado para garantir que é avaliada quando o projeto for executado no Visual Studio.

section TripPinUnitTests;

shared TripPin.UnitTest =
[
    // Put any common variables here if you only want them to be evaluated once
    RootTable = TripPin.Contents(),
    Airlines = RootTable{[Name="Airlines"]}[Data],
    Airports = RootTable{[Name="Airports"]}[Data],
    People = RootTable{[Name="People"]}[Data],

    // Fact(<Name of the Test>, <Expected Value>, <Actual Value>)
    // <Expected Value> and <Actual Value> can be a literal or let statement
    facts =
    {
        Fact("Check that we have three entries in our nav table", 3, Table.RowCount(RootTable)),
        Fact("We have Airline data?", true, not Table.IsEmpty(Airlines)),
        Fact("We have People data?", true, not Table.IsEmpty(People)),
        Fact("We have Airport data?", true, not Table.IsEmpty(Airports)),
        Fact("Airlines only has 2 columns", 2, List.Count(Table.ColumnNames(Airlines))),        
        Fact("Airline table has the right fields",
            {"AirlineCode","Name"},
            Record.FieldNames(Type.RecordFields(Type.TableRow(Value.Type(Airlines))))
        )
    },

    report = Facts.Summarize(facts)
][report];

Selecionar executar no projeto avalia todos os factos e dá-lhe um resultado de relatório que se assemelha a isto:

Usando alguns princípios do desenvolvimento orientado por testes, agora adiciona um teste que atualmente falha, mas que em breve pode ser reimplementado e corrigido (até ao final deste tutorial). Especificamente, adicionas um teste que verifica um dos registos aninhados (Emails) que recebes na entidade Pessoas.

Fact("Emails is properly typed", type text, Type.ListItem(Value.Type(People{0}[Emails])))

Se executares o código novamente, deverás ver agora que tens um teste que falha.

Agora só precisas de implementar a funcionalidade para que isto funcione.

Definição de tipos M personalizados

A abordagem de aplicação de esquemas na lição anterior usou "tabelas de esquema" definidas como pares Nome/Tipo. Funciona bem quando se trabalha com dados achatados/relacionais, mas não suporta definições de tipos em registos/tabelas/listas aninhadas, nem permite reutilizar definições de tipos entre tabelas/entidades.

No caso do TripPin, os dados nas entidades Pessoas e Aeroportos contêm colunas estruturadas, e até partilham um tipo (Location) para representar informação de endereço. Em vez de definir pares Nome/Tipo numa tabela de esquema, é necessário definir cada uma destas entidades usando declarações personalizadas de M Type.

Aqui está uma rápida recapitulação sobre os tipos na linguagem M da Especificação da Linguagem.

Um valor de tipo é um valor que classifica outros valores. Diz-se que um valor classificado por um tipo está em conformidade com esse tipo. O sistema do tipo M consiste nos seguintes tipos de tipos:

• Tipos primitivos, que classificam valores primitivos (binary, date, datetime, datetimezoneduration, list, , logical, null, , number, recordtexttime) typee também incluem vários tipos abstratos (function, table, any, e )none
• Tipos de registro, que classificam valores de registro com base em nomes de campo e tipos de valor
• Tipos de lista, que classificam listas usando um único tipo de base de item
• Tipos de função, que classificam valores de função com base nos tipos de seus parâmetros e valores de retorno
• Tipos de tabela, que classificam valores de tabela com base em nomes de coluna, tipos de coluna e chaves
• Tipos anuláveis, que classificam o valor nulo além de todos os valores classificados por um tipo base
• Tipos de tipo, que classificam valores que são tipos

Usando a saída JSON bruta que obtém (e/ou procurando as definições no $metadata do serviço), pode definir os seguintes tipos de registo para representar os tipos complexos OData:

LocationType = type [
    Address = text,
    City = CityType,
    Loc = LocType
];

CityType = type [
    CountryRegion = text,
    Name = text,
    Region = text
];

LocType = type [
    #"type" = text,
    coordinates = {number},
    crs = CrsType
];

CrsType = type [
    #"type" = text,
    properties = record
];

Note como a LocationType referencia o CityType e LocType para representar as colunas estruturadas.

Para as entidades de topo (que queres representadas como Tabelas), defines os tipos de tabelas:

AirlinesType = type table [
    AirlineCode = text,
    Name = text
];

AirportsType = type table [
    Name = text,
    IataCode = text,
    Location = LocationType
];

PeopleType = type table [
    UserName = text,
    FirstName = text,
    LastName = text,
    Emails = {text},
    AddressInfo = {nullable LocationType},
    Gender = nullable text,
    Concurrency = Int64.Type
];

Depois, atualiza a sua SchemaTable variável (que usa como uma "tabela de consulta" para mapeamentos de entidades a tipos) para usar estas novas definições de tipos:

SchemaTable = #table({"Entity", "Type"}, {
    {"Airlines", AirlinesType },    
    {"Airports", AirportsType },
    {"People", PeopleType}    
});

Imposição de um esquema usando tipos

Dependes de uma função comum (Table.ChangeType) para impor um esquema aos teus dados, tal como usaste SchemaTransformTable na lição anterior. Ao contrário de SchemaTransformTable, Table.ChangeType aceita um tipo real de tabela M como argumento e aplica o seu esquema recursivamente para todos os tipos aninhados. A sua assinatura assemelha-se a esta:

Table.ChangeType = (table, tableType as type) as nullable table => ...

A lista completa do código da Table.ChangeType função pode ser encontrada no ficheiro Table.ChangeType.pqm .

Depois, precisa de atualizar o código do conector para alterar o schema parâmetro de table para type, e adicionar uma chamada a Table.ChangeType em GetEntity.

GetEntity = (url as text, entity as text) as table => 
    let
        fullUrl = Uri.Combine(url, entity),
        schema = GetSchemaForEntity(entity),
        result = TripPin.Feed(fullUrl, schema),
        appliedSchema = Table.ChangeType(result, schema)
    in
        appliedSchema;

GetPage é atualizado para usar a lista de campos do esquema (para saber os nomes do que expandir quando obtiver os resultados), mas deixa a imposição real do esquema para GetEntity.

GetPage = (url as text, optional schema as type) as table =>
    let
        response = Web.Contents(url, [ Headers = DefaultRequestHeaders ]),        
        body = Json.Document(response),
        nextLink = GetNextLink(body),
        
        // If we have no schema, use Table.FromRecords() instead
        // (and hope that our results all have the same fields).
        // If we have a schema, expand the record using its field names
        data =
            if (schema <> null) then
                Table.FromRecords(body[value])
            else
                let
                    // convert the list of records into a table (single column of records)
                    asTable = Table.FromList(body[value], Splitter.SplitByNothing(), {"Column1"}),
                    fields = Record.FieldNames(Type.RecordFields(Type.TableRow(schema))),
                    expanded = Table.ExpandRecordColumn(asTable, fields)
                in
                    expanded
    in
        data meta [NextLink = nextLink];

Confirmando que os tipos aninhados estão a ser configurados

A definição do seu PeopleType agora define o campo Emails para uma lista de texto ({text}). Se estiver a aplicar os tipos corretamente, a chamada para Type.ListItem no seu teste unitário deve agora estar a devolver type text em vez de type any.

Ao executar novamente os seus testes unitários, mostram que foram todos concluídos com sucesso.

Refatoração de código comum em ficheiros separados

Neste momento, a sua extensão tem quase tanto código "comum" como o código do conector TripPin. No futuro, estas funções comuns farão parte da biblioteca padrão de funções incorporada, ou poderão consultá-las a partir de outra extensão. Por agora, refatora o seu código da seguinte forma:

1. Mover as funções reutilizáveis para ficheiros separados (.pqm).
2. Defina a propriedade Build Action no ficheiro para Compilar para garantir que é incluído no seu ficheiro de extensão durante a compilação.
3. Defina uma função para carregar o código usando o Expression.Evaluate.
4. Carrega cada uma das funções comuns que queres usar.

O código para fazer esta refatoração está incluído no seguinte excerto:

Extension.LoadFunction = (fileName as text) =>
  let
      binary = Extension.Contents(fileName),
      asText = Text.FromBinary(binary)
  in
      try
        Expression.Evaluate(asText, #shared)
      catch (e) =>
        error [
            Reason = "Extension.LoadFunction Failure",
            Message.Format = "Loading '#{0}' failed - '#{1}': '#{2}'",
            Message.Parameters = {fileName, e[Reason], e[Message]},
            Detail = [File = fileName, Error = e]
        ];

Table.ChangeType = Extension.LoadFunction("Table.ChangeType.pqm");
Table.GenerateByPage = Extension.LoadFunction("Table.GenerateByPage.pqm");
Table.ToNavigationTable = Extension.LoadFunction("Table.ToNavigationTable.pqm");

Conclusion

Este tutorial fez várias melhorias na forma como se aplica um esquema aos dados que se obtém de uma API REST. O conector está atualmente a codificar diretamente a sua informação de esquema, o que traz um benefício de desempenho em tempo de execução, mas não consegue adaptar-se às alterações nos metadados do serviço ao longo do tempo. Os tutoriais futuros passarão para uma abordagem puramente dinâmica que irá inferir o esquema a partir do documento $metadata do serviço.

Para além das alterações de esquema, este tutorial adicionou Testes Unitários para o seu código e refatorou as funções auxiliares comuns em ficheiros separados para melhorar a legibilidade geral.

Próximos passos

TripPin Parte 8 - Adição de Diagnósticos