Azure SQL 数据库的多模型功能Multi-model capabilities of Azure SQL Database

使用多模型数据库可以存储和处理以多种数据格式表示的数据,例如关系数据、图形、JSON/XML 文档、键/对,等等。Multi-model databases enable you to store and work with data represented in multiple data formats such as relational data, graphs, JSON/XML documents, key-value pairs, etc.

何时使用多模型功能When to use multi-model capabilities

Azure SQL 数据库设计为使用关系模型,在大多数情况下,该模型可为各种常规用途应用程序提供最佳性能。Azure SQL Database is designed to work with the relational model that provides the best performance in the most of the cases for a variety of general-purpose applications. 但是,Azure SQL 数据库并不局限于关系数据。However, Azure SQL Database is not limited to relational-data only. Azure SQL 数据库允许使用各种与关系模型紧密集成的非关系格式。Azure SQL Database enables you to use a variety of non-relational formats that are tightly integrated into the relational model. 对于以下情况,应考虑使用 Azure SQL 数据库的多模型功能:You should consider using multi-model capabilities of Azure SQL Database in the following cases:

  • 某些信息或结构更适合 NoSQL 模型,并且你不想要使用独立的 NoSQL 数据库。You have some information or structures that are better fit for NoSQL models and you don't want to use separate NoSQL database.
  • 大部分数据适合关系模型,并且你需要以 NoSQL 形式为某些数据部分建模。A majority of your data is suitable for relational model, and you need to model some parts of your data in NoSQL style.
  • 希望利用丰富的 Transact-SQL 语言来查询和分析关系数据与 NoSQL 数据,并将其集成到可以使用 SQL 语言的各种工具和应用程序。You want to leverage rich Transact-SQL language to query and analyze both relational and NoSQL data, and integrate it with a variety of tools and applications that can use SQL language.
  • 希望应用内存中技术等数据库功能来改善 NoSQL 数据结构的分析或处理性能,使用事务复制可读副本在其他位置创建数据的副本,并减轻主数据库中的某些分析工作负荷。You want to apply database features such as in-memory technologies to improve performance of your analytic or processing of your NoSQL data structures, use transactional replication or readable replicas to create copy of your data on the other place and offload some analytic workloads from the primary database.

概述Overview

Azure SQL 提供以下多模型功能:Azure SQL provides the following multi-model features:

  • 图形功能:以节点和边缘集的形式呈现数据,并使用通过图形 MATCH 运算符增强的标准 Transact-SQL 查询来查询图形数据。Graph features enable you to represent your data as set of nodes and edges, and use standard Transact-SQL queries enhanced with graph MATCH operator to query the graph data.

  • JSON 功能:在表中插入 JSON 文档,将关系数据转换为 JSON 文档,或反之。JSON features enable you to put JSON documents in tables, transform relational data to JSON documents and vice versa. 可以使用通过 JSON 函数增强的标准 Transact-SQL 语言来分析文档,并使用非聚集索引、列存储索引或内存优化表来优化查询。You can use the standard Transact-SQL language enhanced with JSON functions for parsing documents, and use non clustered indexes, columnstore indexes, or memory-optimized tables, to optimize your queries.

  • 空间功能:存储地理或几何图形数据、使用空间索引为其编制索引,并使用空间查询检索数据。Spatial features enables you to store geographical and geometrical data, index them using the spatial indexes, and retrieve the data using spatial queries.

  • XML 功能:在数据库中存储 XML 数据并为其编制索引,并使用本机 XQuery/XPath 运算来处理 XML 数据。XML features enable you to store and index XML data in your database and use native XQuery/XPath operations to work with XML data. Azure SQL 数据库提供专用的内置 XML 查询引擎用于处理 XML 数据。Azure SQL database has specialized built-in XML query engine that process XML data.

  • 键值对未明确作为特殊功能受到支持,因为键值对本身可作为包含两个列的表建模。Key-value pairs are not explicitly supported as special features since key-value pairs can be natively modeled as two-column tables.

    Note

    可以在同一个 Transact-SQL 查询中使用 JSON Path 表达式、XQuery/XPath 表达式、空间函数和图形查询表达式来访问数据库中存储的任何数据。You can use JSON Path expression, XQuery/XPath expressions, spatial functions, and graph-query expressions in the same Transact-SQL query to access any data that you stored in the database. 此外,可执行 Transact-SQL 查询的任何工具或编程语言也可以使用该查询接口来访问多模型数据。Also, any tool or programming language that can execute Transact-SQL queries, can also use that query interface to access multi-model data. 这是与 Azure Cosmos DB 等多模型数据库之间的主要差别,后者为不同的数据模型提供专用的 API。This is the key difference compared to the multi-model databases such as Azure Cosmos DB that provides specialized API for different data models.

以下部分将会介绍 Azures SQL 数据库的最重要多模型功能。In the following sections, you can learn about the most important multi-model capabilities of Azures SQL Database.

图形功能Graph features

Azure SQL 数据库提供图形数据库功能,用于对数据库中的多对多关系建模。Azure SQL Database offers graph database capabilities to model many-to-many relationships in database. 图形是节点(或顶点)和边缘(或关系)的集合。A graph is a collection of nodes (or vertices) and edges (or relationships). 节点表示实体(例如,某个人或组织),边缘表示该实体连接的两个节点之间的关系(例如,爱好或朋友)。A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends). 下面是使图形数据库独一无二的某些功能:Here are some features that make a graph database unique:

  • 边缘或关系是图形数据库中的第一类实体,可以带有关联的特性或属性。Edges or relationships are first class entities in a Graph Database and can have attributes or properties associated with them.
  • 单个边缘可以灵活连接图形数据库中的多个节点。A single edge can flexibly connect multiple nodes in a Graph Database.
  • 可以轻松表达模式匹配和多跃点导航查询。You can express pattern matching and multi-hop navigation queries easily.
  • 可以轻松表达传递闭包和多态查询。You can express transitive closure and polymorphic queries easily.

图形关系和图形查询功能集成到 Transact-SQL 中,享有使用 SQL Server 作为基础数据库管理系统所带来的优势。The graph relationships and graph query capabilities are integrated into Transact-SQL and receive the benefits of using SQL Server as the foundational database management system. 图形处理是一项核心 SQL Server 数据库引擎功能,可在此处找到有关图形处理的更多信息。Graph processing is the core SQL Server Database Engine feature, so you can find more info about the Graph processing there.

何时使用图形功能When to use a graph capability

不能使用关系数据库实现的目的也不能使用图形数据库来实现。There is nothing a graph database can achieve, which cannot be achieved using a relational database. 但是,使用图形数据库可以轻松表达某些查询。However, a graph database can make it easier to express certain queries. 可根据以下因素来确定是要选择图形数据库还是关系数据库:Your decision to choose one over the other can be based on following factors:

  • 为分层数据建模,其中一个节点可以包含多个父级,因此不能使用 HierarchyIdModel hierarchical data where one node can have multiple parents, so HierarchyId cannot be used
  • 应用程序存在复杂的多对多关系;在应用程序演进过程中,会不断添加新的关系。Model has Your application has complex many-to-many relationships; as application evolves, new relationships are added.
  • 需要分析互联的数据和关系。You need to analyze interconnected data and relationships.

JSON 功能JSON features

使用 Azure SQL 数据库可以分析和查询以 JavaScript 对象表示法 (JSON) 格式表示的数据,并将关系数据导出为 JSON 文本。Azure SQL Database lets you parse and query data represented in JavaScript Object Notation (JSON) format, and export your relational data as JSON text.

JSON 是用于在新式 Web 与移动应用程序中交换数据的流行数据格式。JSON is a popular data format used for exchanging data in modern web and mobile applications. JSON 还用于将半结构化数据存储在日志文件或 NoSQL 数据库(例如 Azure Cosmos DB)中。JSON is also used for storing semi-structured data in log files or in NoSQL databases like Azure Cosmos DB. 许多 REST Web 服务返回采用 JSON 文本格式的结果,或接受采用 JSON 格式的数据。Many REST web services return results formatted as JSON text or accept data formatted as JSON. 大多数 Azure 服务(例如 Azure 认知搜索Azure 存储Azure Cosmos DB)都具有返回或使用 JSON 的 REST 终结点。Most Azure services such as Azure Cognitive Search, Azure Storage, and Azure Cosmos DB have REST endpoints that return or consume JSON.

使用 Azure SQL 数据库可以轻松处理 JSON 数据,将数据库与新式服务集成。Azure SQL Database lets you work with JSON data easily and integrate your database with modern services. Azure SQL 数据库提供以下函数用于处理 JSON 数据:Azure SQL Database provides the following functions for working with JSON data:

JSON 函数

如果具有 JSON 文本,可使用内置函数 JSON_VALUEJSON_QUERYISJSON,从 JSON 中提取数据,或者验证 JSON 的格式是否正确。If you have JSON text, you can extract data from JSON or verify that JSON is properly formatted by using the built-in functions JSON_VALUE, JSON_QUERY, and ISJSON. 使用 JSON_MODIFY 函数可以更新 JSON 文本中的值。The JSON_MODIFY function lets you update value inside JSON text. 对于更高级的查询和分析,使用 OPENJSON 函数可将 JSON 对象数组转换成行集。For more advanced querying and analysis, OPENJSON function can transform an array of JSON objects into a set of rows. 可对返回的结果集执行任何 SQL 查询。Any SQL query can be executed on the returned result set. 最后,使用 FOR JSON 可将存储在关系表中的数据格式化为 JSON 文本。Finally, there is a FOR JSON clause that lets you format data stored in your relational tables as JSON text.

有关详细信息,请参阅如何处理 Azure SQL 数据库中的 JSON 数据For more information, see How to work with JSON data in azure SQL Database. JSON 是一项核心 SQL Server 数据库引擎功能,可在此处找到有关 JSON 功能的更多信息。JSON is core SQL Server Database Engine feature, so you can find more info about the JSON feature there.

何时使用 JSON 功能When to use a JSON capability

在某些特定的情况下,可以使用文档模型而不使用关系模型:Document models can be used instead of the relational models in some specific scenarios:

  • 架构高度规范化不能带来明显的好处,因为你要一次性访问对象的所有字段,或者永远不更新对象的规范化部分。High-normalization of schema doesn't bring significant benefits because you access the all fields of objects at once, or you never update normalized parts of the objects. 但是,规范化模型会增大查询的复杂性,因为需要联接大量的表才能获取数据。However, the normalized model increases the complexity of your queries due to the large number of tables that you need to join to get the data.
  • 原生使用 JSON 文档的应用程序采用通信或数据模型,而你不想要引入附加的层来将关系数据转换为 JSON,或反之。You are working with the applications that natively use JSON documents are communication or data models, and you don't want to introduce additional layers that transforms relational data to JSON and vice versa.
  • 需要通过取消规范化子表或“实体-对象-值”模式来简化数据模型。You need to simplify your data model by de-normalizing child tables or Entity-Object-Value patterns.
  • 需要加载或导出以 JSON 格式存储的数据,且不使用其他某种用于分析数据的工具。You need to load or export data stored in JSON format without some additional tool that parses the data.

空间功能Spatial features

空间数据表示有关物理位置和几何对象形状的信息。Spatial data represents information about the physical location and shape of geometric objects. 这些对象可能是点位置或更复杂的对象,例如国家/地区/区域、道路或湖泊。These objects can be point locations or more complex objects such as countries/regions, roads, or lakes.

Azure SQL 数据库支持两种空间数据类型 - 几何数据类型和地理数据类型。Azure SQL Database supports two spatial data types - the geometry data type and the geography data type.

  • 几何类型在欧几里得(平面)坐标系中表示数据。The geometry type represents data in a Euclidean (flat) coordinate system.
  • 地理类型在环球坐标系中表示数据。The geography type represents data in a round-earth coordinate system.

在 Azure SQL 数据库中可以使用许多空间对象,例如线串多边形,等等。There is a number of Spatial objects that can be used in Azure SQL database such as Point, LineString, Polygon, etc.

Azure SQL 数据库还提供专用的空间索引,可用于改进空间查询的性能。Azure SQL Database also provides specialized Spatial indexes that can be used to improve performance of your spatial queries.

空间支持是一项核心 SQL Server 数据库引擎功能,可在此处找到有关空间功能的更多信息。Spatial support is core SQL Server Database Engine feature, so you can find more info about the spatial feature there.

XML 功能XML features

SQL Server 提供一个强大平台用于开发丰富的半结构化数据管理应用程序。SQL Server provides a powerful platform for developing rich applications for semi-structured data management. XML 支持已集成到 SQL Server 中的所有组件,其中包括:Support for XML is integrated into all the components in SQL Server and includes the following:

  • XML 数据类型。The xml data type. XML 值原生可存储在 XML 数据类型列中,可以根据 XML 架构集合将该列类型化,或者将其保持非类型化。XML values can be stored natively in an xml data type column that can be typed according to a collection of XML schemas, or left untyped. 可为 XML 列编制索引。You can index the XML column.
  • 可以针对列中存储的 XML 数据和 XML 类型的变量指定 XQuery 查询。The ability to specify an XQuery query against XML data stored in columns and variables of the xml type. 可在任何 Transact-SQL 查询中使用 XQuery 功能来访问数据库中使用的任何数据模型。XQuery functionalities can be used in any Transact-SQL query that access any data model that you use in your database.
  • 使用主要 XML 索引为 XML 文档中的所有元素自动编制索引,或者使用辅助 XML 索引来指定要编制索引的确切路径。Automatically index all elements in XML documents using primary XML index or specify the exact paths that should be indexed using secondary XML index.
  • OPENROWSET 允许批量加载 XML 数据。OPENROWSET that allows bulk loading of XML data.
  • 将关系数据转换为 XML 格式。Transform relational data to XML format.

XML 是一项核心 SQL Server 数据库引擎功能,可在此处找到有关 XML 功能的更多信息。XML is core SQL Server Database Engine feature, so you can find more info about the XML feature there.

何时使用 XML 功能When to use an XML capability

在某些特定的情况下,可以使用文档模型而不使用关系模型:Document models can be used instead of the relational models in some specific scenarios:

  • 架构高度规范化不能带来明显的好处,因为你要一次性访问对象的所有字段,或者永远不更新对象的规范化部分。High-normalization of schema doesn't bring significant benefits because you access the all fields of objects at once, or you never update normalized parts of the objects. 但是,规范化模型会增大查询的复杂性,因为需要联接大量的表才能获取数据。However, the normalized model increases the complexity of your queries due to the large number of tables that you need to join to get the data.
  • 原生使用 XML 文档的应用程序采用通信或数据模型,而你不想要引入附加的层来将关系数据转换为 XML,或反之。You are working with the applications that natively use XML documents are communication or data models, and you don't want to introduce additional layers that transforms relational data to XML and vice versa.
  • 需要通过取消规范化子表或“实体-对象-值”模式来简化数据模型。You need to simplify your data model by de-normalizing child tables or Entity-Object-Value patterns.
  • 需要加载或导出以 XML 格式存储的数据,且不使用其他某种用于分析数据的工具。You need to load or export data stored in XML format without some additional tool that parses the data.

键值对Key-value pairs

Azure SQL 数据库不提供专用的类型或结构用于支持键值对,因为键值结构原生表示为标准的关系表:Azure SQL Database don't have specialized types or structures that support key-value pairs since key-value structures can be natively represented as standard relational tables:

CREATE TABLE Collection (
  Id int identity primary key,
  Data nvarchar(max)
)

可根据需求,不受约束地自定义此键值结构。You can customize this key-value structure to fit your needs without any constraints. 例如,值可以是 XML 文档而不是 nvarchar(max) 类型,如果值为 JSON 文档,则你可以施加 CHECK 约束用于验证 JSON 内容的有效性。As an example, the value can be XML document instead of nvarchar(max) type, if the value is JSON document, you can put CHECK constraint that verifies the validity of JSON content. 可在附加的列中放置与某个键相关的任意数目的值、添加计算列与索引来简化和优化数据访问、将表定义为内存表/优化的仅限架构表以提高性能,等等。You can put any number of values related to one key in the additional columns, add computed columns and indexes to simplify and optimize data access, define the table as memory/optimized schema-only table to get better performance, etc.

请参阅 BWin 如何使用内存中 OLTP 实现前所未有的性能和处理规模,其中提到 BWin 的 ASP.NET 缓存解决方案每秒可以实现 1.200.000 次批处理,并举例说明如何在实践中有效地将关系模型用作键值对解决方案。See how BWin is using In-Memory OLTP to achieve unprecedented performance and scale for their ASP.NET caching solution that achieved 1.200.000 batches per seconds, as an example how relational model can be effectively used as key-value pair solution in practice.

后续步骤Next steps

Azure SQL 数据库中的多模型功能也是 Azure SQL 数据库和 SQL Server 之间共享的核心 SQL Server 数据库引擎功能。Multi-model capabilities in Azure SQL Databases are also the core SQL Server Database Engine features that are shared between Azure SQL Database and SQL Server. 有关这些功能的更多详细信息,请访问 SQL 关系数据库文档页:To learn more details about these features, visit the SQL Relational database documentation pages: