Esquema de manipulação
Dependendo da sua fonte de dados, as informações sobre tipos de dados e nomes de colunas podem ou não ser fornecidas explicitamente. As APIs REST do OData normalmente lidam com isso usando a definição de $metadata, e o método Power Query OData.Feed
lida automaticamente com a análise dessas informações e a aplicação delas aos dados retornados de uma fonte OData.
Muitas APIs REST não têm uma maneira de determinar programaticamente seu esquema. Nesses casos, você precisará incluir uma definição de esquema no conector.
A abordagem mais simples é codificar uma definição de esquema em seu conector. Isso é suficiente para a maioria dos casos de uso.
No geral, impor um esquema nos dados retornados pelo conector tem vários benefícios, como:
- Definir 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 (APIs REST geralmente indicam que os campos devem ser nulos omitindo-os completamente).
Considere o código a seguir que retorna uma tabela simples do serviço de exemplo TripPin OData:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
asTable
Nota
O TripPin é uma fonte OData, portanto, realisticamente, faria mais sentido simplesmente usar o OData.Feed
tratamento automático de esquema da função. Neste exemplo, você tratará a fonte como uma API REST típica e usará Web.Contents
para demonstrar a técnica de codificação manual de um esquema.
Esta tabela é o resultado:
Você pode usar a função prática Table.Schema
para verificar o tipo de dados das colunas:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
Table.Schema(asTable)
Tanto o AirlineCode como o Name são do any
tipo. 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. Por enquanto, você deve se preocupar apenas com o tipo atribuído (TypeName
), tipo primitivo (Kind
), e se o valor da coluna pode ser nulo (IsNullable
).
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 (texto, número, 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 definirá suas AirlineCode
colunas e Name
como text
e terá esta aparência:
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})
Ao examinar alguns dos outros pontos de extremidade, considere as seguintes tabelas de esquema:
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}
})
A People
tabela tem sete campos, incluindo list
s (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}
})
Você pode colocar todas essas tabelas em uma única tabela SchemaTable
de esquema mestre:
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
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 as informações da 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:
- Determine se há colunas ausentes na tabela de origem.
- Determine se há colunas extras.
- Ignorar colunas estruturadas (do tipo
list
,record
etable
) e colunas definidas como tipoany
. - Use
Table.TransformColumnTypes
para definir cada tipo de coluna. - Reordene as colunas com base na ordem em que aparecem na tabela de esquema.
- 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, o que às vezes pode resultar em uma chamada dupla para a API.
No contexto maior de uma extensão completa, a manipulação de esquema ocorrerá quando uma tabela for retornada da API. Normalmente, essa funcionalidade ocorre no nível mais baixo da função de paginação (se existir), com informações de entidade passadas de uma tabela de navegação.
Como grande parte da implementação de tabelas de paginação e navegação é específica do contexto, o exemplo completo de implementação de um mecanismo de manipulação de esquema codificado não será mostrado aqui. Este exemplo do TripPin demonstra como uma solução completa pode parecer.
A implementação codificada discutida acima faz um bom trabalho em garantir que os esquemas permaneçam consistentes para repetições JSON simples, mas é limitada a analisar o primeiro nível da resposta. Conjuntos de dados profundamente aninhados se beneficiariam da seguinte abordagem, que aproveita os tipos M.
Aqui está uma rápida atualização sobre os tipos na linguagem M da Especificação de 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
,datetimezone
,duration
,list
,number
record
null
text
time
logical
type
) e 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 os valores da tabela com base em nomes de coluna, tipos de coluna e chaves.
- Tipos anuláveis, que classificam o valor null 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 você obtém (e/ou pesquisando as definições no $metadata do serviço), você pode definir os seguintes tipos de registro para representar 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
];
Observe como LocationType
faz referência ao CityType
e LocType
para representar suas colunas estruturadas.
Para as entidades de nível superior que você desejará representar como Tabelas, você pode definir tipos de tabela:
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
];
Em seguida, você pode atualizar sua SchemaTable
variável (que pode ser usada como uma tabela de pesquisa para mapeamentos de entidade para tipo) para usar estas novas definições de tipo:
SchemaTable = #table({"Entity", "Type"}, {
{"Airlines", AirlinesType},
{"Airports", AirportsType},
{"People", PeopleType}
});
Você pode confiar em uma função comum (Table.ChangeType
) para impor um esquema em seus dados, muito parecido com o que você usou SchemaTransformTable
no exercício anterior. Ao contrário SchemaTransformTable
do , Table.ChangeType
usa um tipo de tabela M real como argumento e aplicará seu esquema recursivamente para todos os tipos aninhados. A sua assinatura é:
Table.ChangeType = (table, tableType as type) as nullable table => ...
Nota
Para flexibilidade, a função pode ser usada em tabelas, bem como listas de registros (que é como as tabelas são representadas em um documento JSON).
Em seguida, você precisará atualizar o código do conector para alterar o schema
parâmetro de a table
para um type
e adicionar uma chamada ao Table.ChangeType
. Mais uma vez, os detalhes para fazê-lo são muito específicos da implementação e, portanto, não vale a pena entrar em detalhes aqui. Este exemplo de conector TripPin estendido demonstra uma solução de ponta a ponta que implementa essa abordagem mais sofisticada para lidar com o esquema.