处理架构

根据数据源,有关数据类型和列名的信息可能或可能不会显式提供。 OData REST API 通常使用 $metadata 定义来处理此问题,而 Power Query OData.Feed 方法会自动解析此信息并将其应用于从 OData 源返回的数据。

很多 REST API 无法以编程方式确定其架构。 在这些情况下,需在连接器中包含架构定义。

简易硬编码方法

最简单的方法是将架构定义硬编码到连接器中。 这对于大多数用例来说已足够。

总的来说,对连接器返回的数据强制应用架构具有多种优势,例如:

  • 设置正确的数据类型。
  • 删除无需向最终用户显示的列(例如内部 ID 或状态信息)。
  • 通过添加响应中可能缺少的列(REST API 通常通过省略字段来表明这些字段应为 null),从而确保每个数据页面具有相同的形状。

使用 Table.Schema 查看现有架构

请考虑使用以下代码,该代码可从 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 来演示手动硬编码架构的技术。

此表的结果如下:

TripPin Airline 数据表。

可使用方便的 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)

应用于 TripPin Airline 数据的 Table.Schema 的结果。

AirlineCode 和 Name 均为 any 类型。 Table.Schema 将返回有关表中列的大量元数据,其中包括名称、位置、类型信息和许多高级属性,例如 Precision、Scale 和 MaxLength。 现在,只应关注归属类型 (TypeName)、基元类型 (Kind) 以及列值是否为 null (IsNullable)。

定义简易架构表

架构表由两列组成:

详细信息
名称 列的名称。 它必须与服务返回结果中的名称匹配。
类型 要设置的 M 数据类型。 它可以是基元类型(文本、数字、日期/时间等),也可以是归属类型(Int64.Type、Currency.Type 等)。

Airlines 表的硬编码架构表会将其 AirlineCodeName列设为 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 表有七个字段,包括 listEmailsAddressInfo)、一个可为 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 帮助程序函数

下文介绍的 SchemaTransformTable 帮助程序函数将用于对数据强制应用架构。 它采用了以下参数:

参数 类型 描述
要对其强制应用架构的数据表。
schema 要从中读取列信息的架构表,且类型如下:type table [Name = text, Type = type]
enforceSchema 数字 可选)控制函数行为的枚举。
默认值 (EnforceSchema.Strict = 1) 可确保输出表与通过添加所有缺失列和删除额外列而提供的架构表匹配。
EnforceSchema.IgnoreExtraColumns = 2 选项可用于保留结果中的额外列。
使用 EnforceSchema.IgnoreMissingColumns = 3 时,会忽略缺少的列和额外的列。

此函数的逻辑如下所示:

  1. 确定源表中是否存在缺失的列。
  2. 确定是否存在额外的列。
  3. 忽略结构化列(listrecordtable 类型),并将列设为 any 类型。
  4. 使用 Table.TransformColumnTypes 设置每个列类型。
  5. 根据列在架构表中显示的顺序对其重新排序。
  6. 使用 Value.ReplaceType 设置针对表自身的类型。

备注

在查询编辑器中查看结果时,设置表类型的最后一步将消除 Power Query UI 推断类型信息的需求,而这有时可能会导致对 API 进行双重调用。

汇总

在完整扩展的更大上下文中,从 API 返回表时会进行架构处理。 通常,此功能出现在最低级别的分页函数(如果存在)中,其中包含从导航表传递的实体信息。

由于分页和导航表的大部分实现均特定于上下文,因此不会在此处显示实现硬编码架构处理机制的完整示例。 此 TripPin 示例演示了端到端解决方案的大致样子。

复杂方法

上文讨论的硬编码实现可以很好地确保架构对于简单的 JSON 响应保持一致,但仅限于解析第一级别的响应。 深度嵌套的数据集将从以下方法中受益,该方法利用了 M 类型。

有关语言规范内 M 语言中类型的快速刷新如下:

类型值是一个对其他值进行分类的值。 认为按类型分类的值符合该类型。 M 类型系统由以下类型组成:

  • 基元类型可以对基元值(binarydatedatetimedatetimezonedurationlistlogicalnullnumberrecordtexttimetype)进行分类,并且基元类型还包括很多抽象类型(functiontableanynone)。
  • 记录类型(根据字段名称和值类型对记录值进行分类)。
  • 列表类型(使用单一项目基类型对列表进行分类)。
  • 函数类型(根据其参数和返回值类型对函数值进行分类)。
  • 表类型(根据列名、列类型和键对表值进行分类)。
  • 可为 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 如何引用 CityTypeLocType 来表示其结构化列。

对于要表示为表的顶级实体,可定义表类型

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 连接器示例演示了一个端到端的解决方案,该解决方案实现了此更复杂的架构处理方法。