处理架构
根据数据源,有关数据类型和列名的信息可能或可能不会显式提供。 OData REST API 通常使用 $metadata 定义来处理此问题,而 Power Query OData.Feed
方法会自动解析此信息并将其应用于从 OData 源返回的数据。
很多 REST API 无法以编程方式确定其架构。 在这些情况下,需在连接器中包含架构定义。
最简单的方法是将架构定义硬编码到连接器中。 这对于大多数用例来说已足够。
总的来说,对连接器返回的数据强制应用架构具有多种优势,例如:
- 设置正确的数据类型。
- 删除无需向最终用户显示的列(例如内部 ID 或状态信息)。
- 通过添加响应中可能缺少的列(REST API 通常通过省略字段来表明这些字段应为 null),从而确保每个数据页面具有相同的形状。
请考虑使用以下代码,该代码可从 TripPin OData 示例服务返回一个简易表:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
asTable
备注
TripPin 是一个 OData 源,因此,只使用 OData.Feed
函数的自动架构处理更有意义。 在此示例中,你会将源视为典型 REST API,并使用 Web.Contents
来演示手动硬编码架构的技术。
此表的结果如下:
可使用方便的 Table.Schema
函数检查列的数据类型:
let
url = "https://services.odata.org/TripPinWebApiService/Airlines",
source = Json.Document(Web.Contents(url))[value],
asTable = Table.FromRecords(source)
in
Table.Schema(asTable)
AirlineCode 和 Name 均为 any
类型。 Table.Schema
将返回有关表中列的大量元数据,其中包括名称、位置、类型信息和许多高级属性,例如 Precision、Scale 和 MaxLength。 现在,只应关注归属类型 (TypeName
)、基元类型 (Kind
) 以及列值是否为 null (IsNullable
)。
架构表由两列组成:
列 | 详细信息 |
---|---|
名称 | 列的名称。 它必须与服务返回结果中的名称匹配。 |
类型 | 要设置的 M 数据类型。 它可以是基元类型(文本、数字、日期/时间等),也可以是归属类型(Int64.Type、Currency.Type 等)。 |
Airlines
表的硬编码架构表会将其 AirlineCode
和 Name
列设为 text
,如下所示:
Airlines = #table({"Name", "Type"}, {
{"AirlineCode", type text},
{"Name", type text}
})
查看其他某些终结点时,请考虑以下架构表:
Airports
表有四个要保留的字段(包括一个 record
类型):
Airports = #table({"Name", "Type"}, {
{"IcaoCode", type text},
{"Name", type text},
{"IataCode", type text},
{"Location", type record}
})
People
表有七个字段,包括 list
(Emails
、AddressInfo
)、一个可为 null 的列 (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}
})
可将所有这些表放入单个 SchemaTable
主架构表中:
SchemaTable = #table({"Entity", "SchemaTable"}, {
{"Airlines", Airlines},
{"Airports", Airports},
{"People", People}
})
下文介绍的 SchemaTransformTable
帮助程序函数将用于对数据强制应用架构。 它采用了以下参数:
参数 | 类型 | 描述 |
---|---|---|
表 | 表 | 要对其强制应用架构的数据表。 |
schema | 表 | 要从中读取列信息的架构表,且类型如下:type table [Name = text, Type = type] |
enforceSchema | 数字 | (可选)控制函数行为的枚举。 默认值 ( EnforceSchema.Strict = 1 ) 可确保输出表与通过添加所有缺失列和删除额外列而提供的架构表匹配。 EnforceSchema.IgnoreExtraColumns = 2 选项可用于保留结果中的额外列。 使用 EnforceSchema.IgnoreMissingColumns = 3 时,会忽略缺少的列和额外的列。 |
此函数的逻辑如下所示:
- 确定源表中是否存在缺失的列。
- 确定是否存在额外的列。
- 忽略结构化列(
list
、record
和table
类型),并将列设为any
类型。 - 使用
Table.TransformColumnTypes
设置每个列类型。 - 根据列在架构表中显示的顺序对其重新排序。
- 使用
Value.ReplaceType
设置针对表自身的类型。
备注
在查询编辑器中查看结果时,设置表类型的最后一步将消除 Power Query UI 推断类型信息的需求,而这有时可能会导致对 API 进行双重调用。
在完整扩展的更大上下文中,从 API 返回表时会进行架构处理。 通常,此功能出现在最低级别的分页函数(如果存在)中,其中包含从导航表传递的实体信息。
由于分页和导航表的大部分实现均特定于上下文,因此不会在此处显示实现硬编码架构处理机制的完整示例。 此 TripPin 示例演示了端到端解决方案的大致样子。
上文讨论的硬编码实现可以很好地确保架构对于简单的 JSON 响应保持一致,但仅限于解析第一级别的响应。 深度嵌套的数据集将从以下方法中受益,该方法利用了 M 类型。
有关语言规范内 M 语言中类型的快速刷新如下:
类型值是一个对其他值进行分类的值。 认为按类型分类的值符合该类型。 M 类型系统由以下类型组成:
- 基元类型可以对基元值(
binary
、date
、datetime
、datetimezone
、duration
、list
、logical
、null
、number
、record
、text
、time
、type
)进行分类,并且基元类型还包括很多抽象类型(function
、table
、any
和none
)。- 记录类型(根据字段名称和值类型对记录值进行分类)。
- 列表类型(使用单一项目基类型对列表进行分类)。
- 函数类型(根据其参数和返回值类型对函数值进行分类)。
- 表类型(根据列名、列类型和键对表值进行分类)。
- 可为 null 的类型,除了按基类型分类的所有值外,还会对 null 值进行分类。
- 类型类型(对属于类型的值进行分类)。
使用获取的原始 JSON 输出(以及/或通过查找服务 $metadata 中的定义),可定义以下记录类型来表示 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
];
请注意 LocationType
如何引用 CityType
和 LocType
来表示其结构化列。
对于要表示为表的顶级实体,可定义表类型:
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
];
然后,可以更新 SchemaTable
变量(可将其用于实体到类型映射的查找表),以使用这些新类型定义:
SchemaTable = #table({"Entity", "Type"}, {
{"Airlines", AirlinesType},
{"Airports", AirportsType},
{"People", PeopleType}
});
可依赖通用函数 (Table.ChangeType
) 对数据强制应用架构,就像在前面练习中使用的 SchemaTransformTable
一样。 与 SchemaTransformTable
不同,Table.ChangeType
会将实际的 M 表类型用作参数,并以递归方式为所有嵌套类型应用架构。 其签名为:
Table.ChangeType = (table, tableType as type) as nullable table => ...
备注
为提高灵活性,函数可用于表和记录列表(即,如何在 JSON 文档中表示表)。
然后,需要更新连接器代码以将 schema
参数从 table
更改为 type
,并添加对 Table.ChangeType
的调用。 同样,此操作的详细信息完全特定于具体实现,因此不值得在此详细介绍。 此扩展后的 TripPin 连接器示例演示了一个端到端的解决方案,该解决方案实现了此更复杂的架构处理方法。