20、Azure SQL 的多模型功能：JSON、图结构与空间数据处理

Azure SQL 的多模型功能：JSON、图结构与空间数据处理

1. OPENJSON 函数

OPENJSON 函数用于处理 JSON 对象，它有两个重要的可选参数：
- 可选的 JSON 路径 ：用于在包含应返回值的 JSON 对象中引用属性。若未指定此路径，将使用列名来引用属性。例如，若列名为 severity 且未指定 JSON 路径， OPENJSON 将尝试在 $.severity 路径上查找值。
- 可选的 AS JSON 子句 ：默认情况下， OPENJSON 使用 JSON_VALUE 函数从当前转换的对象中获取值，因此它无法返回子对象或子数组。若该路径上有 JSON 对象或数组，除非指定 AS JSON 子句，否则不会返回。

OPENJSON 函数的结果是一个结果集，可插入到表中。

2. 图结构

2.1 图结构的应用场景

图是由对象（节点）通过关系（边）连接而成的复杂结构，常见应用场景如下：
- 社交网络 ：人们通过朋友、家人、合作伙伴或同事等关系相互连接。
- 交通地图 ：城镇和地点通过道路、河流和航线相互连接。
- 物料清单解决方案 ：零件与其他零件相互连接，形成复杂的层级结构。

2.2 图模型与关系模型的选择

当项目中关系占主导地位，且少量实体以多种不同的直接和间接方式相互连接时，选择图模型而非关系模型。图模型的关键区别在于关系的传递性，即若 a 与 b 相连， b 与 c 相连，则 a 与 c 相连。在这种情况下，我们不仅关注单跳关系，还关注从一个节点到另一个节点所需的所有跳数。为了高效处理这些问题，我们可以利用图特定的语义进行查询处理，例如查找传递闭包、寻找两个对象之间的最短路径或从指定对象开始递归遍历所有关系。

2.3 Azure SQL 中的图结构表示

在 Azure SQL 中，节点和边使用特殊表表示。使用关系表表示图元素有以下好处：
- 节点和边通常附带一些信息，表是存储这些信息的理想结构。
- 可以利用 Azure SQL 查询优化器提高整体查询性能。
- 可以使用列存储和索引进一步提高性能。

以下是一个简单的示例，用于建模机场和表示它们之间连接的航线：

CREATE TABLE Airport (
    AirportID int PRIMARY KEY,
    Name NVARCHAR(100),
    CityID int FOREIGN KEY REFERENCES Application.Cities(CityID)
) AS NODE
GO
CREATE TABLE Flightline (
    Name NVARCHAR(10)
) AS EDGE;

Airport 是图的节点，它的行为类似于常规表，我们可以添加任何列、索引或约束来描述该节点中的信息。节点表有一个隐藏列，用于表示节点的唯一标识符。 Flightline 是一个边表，用于连接两个 Airport 表。该表中的列表示描述两个节点之间关系的附加信息。为了明确指定 Flightline 连接的是 Airport ，我们需要引入以下边约束：

ALTER TABLE Flightline
ADD CONSTRAINT [Connecting airports]
                    CONNECTION (Airport TO Airport)
                    ON DELETE CASCADE;

这个约束规定不能使用 Flightline 边连接除 Airport 之外的节点，并且定义了节点删除时边的处理方式。

2.4 加载图数据

节点表是经典表，可以像其他经典表一样加载或读取。每个节点表都有一个隐藏列 $NODE_ID ，仅在某些特殊场景中使用。边表有隐藏的“外键关系”列，用于使用 $NODE_ID 值将边与关联节点连接起来。在导入边数据时，需要获取相关节点的 $NODE_ID 值以绑定它们。假设机场节点已经加载，我们使用 OPENROWSET 函数从 blob 存储中导入一组边数据。为了将数据加载到边表中，我们需要将加载的记录与节点进行连接，找到 $NODE_ID 值，并将它们与导入的航线名称一起插入到边表中：

INSERT INTO Flightline ($from_id, $to_id, Name)
SELECT f.$NODE_ID, t.$NODE_ID, a.Name
FROM OPENROWSET(
         BULK 'data/flightlines.csv',
         DATA_SOURCE = 'MyAzureBlobStorage',
         FORMATFILE='data/flightlines.fmt',
         FORMATFILE_DATA_SOURCE = 'MyAzureBlobStorage') as a
     JOIN Airport f ON f.Name = a.FromAirport
     JOIN Airport t ON t.Name = a.ToAirport;

Flightlines CSV 文件包含源机场、目的机场和它们之间航线名称的信息。我们需要通过 Name 列将这些数据与机场表进行连接，并获取应导入的 $NODE_ID 值。

2.5 查询图数据

加载数据后，我们可以使用 Cypher 表达式（ www.opencypher.org/ ）查询图数据。以下查询将遍历从源机场节点通过航线边到另一个机场节点的所有路径：

SELECT
      src.Name, line.Name, dest.Name
FROM
      Airport src, Flightline line, Airport dest
WHERE
      MATCH(src-(line)->dest)
AND
      src.Name='Belgrade';

这个查询将返回从贝尔格莱德到所有其他城镇的所有目的地。 MATCH 子句定义了从源机场（ src ）到目的机场（ dest ）的路径应通过航线表（ line ）建立。

Azure SQL 图处理支持的主要优势是能够跨节点的边进行查询。例如， SHORTEST_PATH 谓词可以帮助我们找到两个节点之间的最短路径。以下代码展示了如何使用该功能找到两个城镇之间的最短路线：

WITH routes AS (
   SELECT
      src.Name,
      STRING_AGG(dest.name, '->')
            WITHIN GROUP (GRAPH PATH) AS path,
      COUNT(dest.name)
            WITHIN GROUP (GRAPH PATH) AS stops,
      LAST_VALUE(line.name)
            WITHIN GROUP (GRAPH PATH) AS lastFlight,
      LAST_VALUE(dest.name)
            WITHIN GROUP (GRAPH PATH) AS destination
FROM
      Airport src,
            Flightline FOR PATH line,
            Airport FOR PATH dest
WHERE
      MATCH(SHORTEST_PATH(src(-(line)->dest)+))
AND
      src.Name='BEG'
)
SELECT TOP (10) path, stops, lastFlight, destination
FROM routes
WHERE destination IN ('JFK', 'SEA');

MATCH 子句中的 SHORTEST_PATH 子句将找到起始位置和结束位置之间的最短路径，这两个位置可能没有直接连接。聚合函数 STRING_AGG 将连接路径上的所有机场名称，并使用箭头 -> 分隔显示。 COUNT 和 LAST_VALUE 将显示最短路线上的经停次数、最后一班航班和终点城镇。最短路径探索完成后，我们需要在最终查询中选择目的地城镇。

Azure SQL 的图处理功能使我们能够降低模型和查询的复杂性，这些模型和查询用于分析表之间的不同路径和关系。

2.6 图结构操作流程

graph LR
    A[定义图结构] --> B[创建节点表和边表]
    B --> C[添加边约束]
    C --> D[加载图数据]
    D --> E[查询图数据]

3. 空间数据

3.1 空间数据的表示问题

表示空间对象（如地点、道路、国家边界）并不适合传统的结构化关系模型。虽然可以将道路或边界表示为一组小的直线段，每条线段存储在单独的行中，其端点与继续该道路的线段相连，但这种表示方式并不高效。针对空间对象的查询通常具有“这个地点是否在形状内”或“这个地点离道路有多远”等条件，这些不是使用标准 SQL 语言可以轻松描述的典型查询。

3.2 Azure SQL 的空间数据功能

Azure SQL 具有符合开放地理空间联盟（OGC）标准的专门功能，可用于实现处理空间数据的应用程序：
- 专门类型 ：可用于表示复杂的几何和地理对象及形状（如点、线、多边形）。所有形状都可以表示为几何或地理模型。
- 空间查询功能 ：如查找两点之间的距离（ ST_DISTANCE ）、确定一个区域是否包含指定点（ ST_CONTAINS ）等。
- 专门索引 ：针对空间类型的查询进行了优化。

3.3 扩展图模型以处理空间数据

为了解决一些复杂的空间查询问题，我们需要将地理数据扩展到图模型中。以下是扩展后的机场和航线模型：

CREATE TABLE Airport (
    AirportID int PRIMARY KEY,
    Name NVARCHAR(100),
    Location GEOGRAPHY,
    CityID int FOREIGN KEY REFERENCES Application.City(CityID)
) AS NODE
GO
CREATE TABLE FlightLine (
    Name NVARCHAR(10),
    Route GEOGRAPHY
) AS EDGE;

3.4 空间数据类型

Azure SQL 有两种主要的基本类型用于表示几何和地理图形：
- GEOMETRY 类型 ：表示欧几里得（平面）坐标系中的数据。适用于表示相对较小的对象，如建筑物或室内。
- GEOGRAPHY 类型 ：表示地球坐标系中的数据。更适合表示较大的形状，如河流、城市或国家边界，以及任何需要在接近地球表面的情况下工作以避免误差的对象。

在这两种类型中，根据开放地理空间联盟的规定，还可以创建更具体的类型：
| 类型 | 描述 |
| ---- | ---- |
| Point | 用于表示二维地点，如城镇 |
| LineString 和 CircularString | 可表示开放或封闭的线，如道路或边界 |
| Polygon 和 CurvePolygon | 用于表示区域，如国家 |
| MultiPoint、MultiLine 和 MultiPolygon | 表示一组逻辑上相关的不相连地理对象（如群岛可以用 MultiPolygon 表示） |

在 Azure SQL 中，在表中创建 Geometry 或 Geography 列后，可以使用这些类型构建所需的形状，甚至可以同时使用多种类型，使用集合或“Multi”类型。

3.5 查询空间数据

Azure SQL 的空间数据类型内置了一些方法，可用于轻松查询空间数据。假设航班线路和机场的所有信息都已填充，我们可以轻松找到穿越内布拉斯加州的航线：

DECLARE @nebraska GEOGRAPHY = (
      SELECT TOP (1) Border FROM Application.StateProvinces
      WHERE StateProvinceName = 'Nebraska'
);
SELECT *
FROM FlightLine
WHERE Route.STIntersects(@nebraska) = 1;

STIntersects 方法用于确定两个形状是否在某个位置相交。如果一个地理实例与另一个地理实例相交，该方法返回 1，否则返回 0。

STDistance 方法用于测量两个对象之间的距离，可帮助我们找到离某个特定坐标较近的对象。以下查询返回离某个对象当前位置最近的五个机场：

DECLARE @currentLocation GEOGRAPHY = 'POINT(-121.626 47.8315)';
SELECT TOP(5) *
FROM Airports
ORDER BY Location.STDistance(@currentLocation) ASC;

空间查询使我们能够轻松执行特定分析，解决原本需要花费大量时间进行特定数学变换的问题，无需自己编写这些计算或使用其他更专业的解决方案，从而避免了数据移动，使解决方案更加高效。

3.6 空间查询操作流程

graph LR
    A[定义空间对象] --> B[选择查询方法]
    B --> C{方法类型}
    C -->|STIntersects| D[检查形状相交]
    C -->|STDistance| E[测量对象距离]
    D --> F[返回相交结果]
    E --> G[返回距离结果]

3.7 空间索引

理论上， STIntersects 方法可以实现为一个自包含的函数，使用复杂的数学计算来确定图形之间的关系。然而，由于计算复杂度高，对许多对象运行此类函数会消耗大量时间和 CPU 资源。为了高效处理，Azure SQL 使用一种特殊类型的空间索引。

空间索引内部创建一个网格（如图 8 - 2 所示），其中的单元格可能与要索引的图形部分重叠，也可能不重叠。Azure SQL 创建网格后，会记录每个要索引的空间对象是否与网格中的单元格完全重叠、部分重叠或完全不重叠，这个过程称为镶嵌。使用这种技术， STOverlaps 方法在确定两个对象是否重叠时，不会立即应用复杂的数学计算。如果有索引可用，它会首先使用索引检查网格中是否至少有一个单元格同时属于两个空间对象，或者一个对象所属的单元格是否也与另一个对象部分重叠。如果是，则它们重叠，这是确定是否有交集的更快方法。如果没有单元格与两个对象至少部分重叠，则这些对象不重叠。如果有一些单元格与两个对象部分重叠，则这些对象可能重叠也可能不重叠。只有在这种情况下，Azure SQL 才会应用复杂的空间计算，但不是对对象的整个区域进行计算，而是对它们可能潜在重叠的较小单元格进行计算。尽管这可能是一个消耗 CPU 的操作，但它是在小单元格和可能在该单元格内的对象小部分上执行的。因此，这种操作比直接比较对象所有部分的简单方法快几个数量级。

空间索引使用特殊的 CREATE SPATIAL INDEX 语法创建：

CREATE SPATIAL INDEX SI_Flightline_Routes
   ON Flightline(Route)
   USING GEOGRAPHY_GRID
   WITH (
   GRIDS = ( MEDIUM, LOW, MEDIUM, HIGH ),
    CELLS_PER_OBJECT = 64 );

除了要索引的空间列外，还可以指定用于索引空间值的网格的特征，如要覆盖的区域或用于索引的镶嵌网格的密度。更精细的索引会更大，需要更多时间扫描所有网格单元格并确定路线部分是否与每个单元格重叠。然而，更大的网格密度使得对象部分重叠的最坏情况阶段更快，因为使用复杂数学规则处理的对象部分更小。索引的合适大小和参数取决于数据，可能需要进行实验并使用不同参数重建索引，以找到最适合数据的设置。如果一开始不确定，可以避免指定边界框，Azure SQL 会尝试为你猜测最佳的边界框和镶嵌。当然，自动定义的值在特定场景中可能不是最优的，因此需要时可以手动指定。

3.8 空间索引创建流程

graph LR
    A[确定索引列] --> B[选择索引类型]
    B --> C[设置网格参数]
    C --> D[执行创建语句]
    D --> E[完成索引创建]

3.9 Geometry 与 Geography 的区别

如前所述，Azure SQL 有两类用于不同场景的空间数据类型：
- Geometry 数据类型 ：用于在经典二维坐标系中表示平面数学形状。
- Geography 数据类型 ：用于表示投影到二维平面的球形对象和形状。

理解 Geometry 和 Geography 类型之间的差异是开发空间应用程序的关键。 Geometry 类型可想象为可以在一张纸上绘制的对象，对象的距离和大小测量方式与在纸上或板上绘制的对象相同。例如，要找到塞尔维亚贝尔格莱德和美国华盛顿州西雅图之间的最短飞行轨迹，从几何角度看，可能会使用一条经过法国和美国东海岸的直线。然而，由于地球是圆形的，实际的最短轨迹（称为测地线）会经过冰岛、格陵兰岛和加拿大。 Geography 数据模型考虑了地球的实际形状，能够找到现实世界中的最短距离和路径。

将地球表面映射到二维平面是最困难的空间问题。著名数学家卡尔·高斯在他的《卓越定理》（拉丁语为 “Remarkable Theorem”）中证明，球面不能无失真地映射到二维平面。在一些地图上，靠近两极的地区（如格陵兰岛、南极洲、加拿大北部和俄罗斯）可能看起来被拉伸或比实际更大，这是因为靠近两极的密集坐标必须“拉伸”才能投影到二维坐标中。更复杂的是，地球表面既不是球形也不是椭球形。地球的不规则形状和靠近两极的情况迫使人们使用不同的映射策略将其投影到二维平面。

综上所述，Azure SQL 的多模型功能，包括 JSON 处理、图结构和空间数据处理，为处理复杂的数据和查询提供了强大的支持。通过合理使用这些功能和相应的索引技术，可以显著提高数据处理的效率和查询性能，降低模型和查询的复杂性。无论是处理社交网络、交通地图、物料清单等图结构数据，还是处理地点、道路、国家边界等空间数据，Azure SQL 都能提供有效的解决方案。