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 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 obtêm 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
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)
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 define 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 | número | (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:
- Determinar se há colunas ausentes na tabela de origem.
- Determine se há colunas extras.
- Ignorar colunas estruturadas (do tipo
list,recordetable) e colunas definidas como tipotype any. - Usar Table.TransformColumnTypes para definir cada tipo de coluna.
- Reordene colunas com base na ordem em que aparecem na tabela de esquema.
- 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.
- Definir uma tabela de esquema mestre (
SchemaTable) que contém todas as definições de esquema. - Atualizar o
TripPin.Feed,GetPageeGetAllPagesByNextLinkpara aceitar um parâmetroschema. - Impor o esquema em
GetPage. - 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;
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 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:
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.
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 outras chamadas 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):
Executando a mesma consulta com o conector TripPin desta lição, agora você verá que as informações de tipo são exibidas corretamente.
