Synapse SQL 池中的视图Views in Synapse SQL pool

可以通过多种不同的方式使用视图来提升解决方案的质量。Views can be used in a number of different ways to improve the quality of your solution.

SQL 池支持标准视图和具体化视图。SQL pool supports both standard and materialized views. 两者都是使用 SELECT 表达式创建的虚拟表,作为逻辑表提供给查询。Both are virtual tables created with SELECT expressions and presented to queries as logical tables.

视图封装了常见数据计算的复杂性,并为计算更改添加了抽象层,因此无需重写查询。Views encapsulate the complexity of common data computation and add an abstraction layer to computation changes so there's no need to rewrite queries.

标准视图Standard view

标准视图在每次使用视图时都会计算其数据。A standard view computes its data each time when the view is used. 磁盘上没有存储数据。There's no data stored on disk. 用户通常使用标准视图作为帮助组织数据库中的逻辑对象和查询的工具。People typically use standard views as a tool that helps organize the logical objects and queries in a database.

若要使用标准视图,查询需要直接引用它。To use a standard view, a query needs to make direct reference to it. 有关详细信息,请参阅 CREATE VIEW 文档。For more information, see the CREATE VIEW documentation.

SQL 池中的视图仅存储为元数据。Views in SQL pool are stored as metadata only. 因此,无法使用以下选项:Consequently, the following options aren't available:

  • 不提供任何架构绑定选项There is no schema binding option
  • 无法通过视图更新基表Base tables can't be updated through the view
  • 无法基于临时表创建视图Views can't be created over temporary tables
  • 不支持 EXPAND/NOEXPAND 提示There is no support for the EXPAND / NOEXPAND hints
  • SQL 池中没有索引视图There are no indexed views in SQL pool

可以利用标准视图在表之间实施性能优化联接。Standard views can be utilized to enforce performance optimized joins between tables. 例如,视图可以合并冗余分布键作为联接条件的一部分,以便最大程度地减少数据移动。For example, a view can incorporate a redundant distribution key as part of the joining criteria to minimize data movement.

视图的另一个好处是强制执行特定查询或联接提示。Another benefit of a view could be to force a specific query or joining hint. 以这种方式使用视图可确保始终以最佳方式执行联接,用户不需要记住其联接的正确构造。Using views in this manner guarantees that joins are always performed in an optimal fashion avoiding the need for users to remember the correct construct for their joins.

具体化视图Materialized view

像表一样,具体化视图在 SQL 池中预先计算、存储和维护其数据。A materialized view pre-computes, stores, and maintains its data in SQL pool just like a table. 每次使用具体化视图时都不需要重新计算。There's no recomputation needed each time when a materialized view is used.

当数据加载到基表中时,SQL 池会同步刷新具体化视图。As the data gets loaded into base tables, SQL pool synchronously refreshes the materialized views. 即使查询中未引用视图,查询优化器也会自动使用已部署的具体化视图来提高查询性能。The query optimizer automatically uses deployed materialized views to improve query performance even if the views aren't referenced in the query.

从具体化视图中获益最大的查询是对产生小结果集的大型表的复杂查询(通常是使用联接和聚合的查询)。Queries benefiting most from materialized views are complex queries (typically queries with joins and aggregations) on large tables that produce small result set.

有关具体化视图语法和其他要求的详细信息,请参阅 CREATE MATERIALIZED VIEW AS SELECTFor details on the materialized view syntax and other requirements, refer to CREATE MATERIALIZED VIEW AS SELECT.

有关查询优化指南,请查看通过具体化视图进行性能优化For query tuning guidance, check Performance tuning with materialized views.

示例Example

一种常见的应用模式是在加载数据时使用 CREATE TABLE AS SELECT (CTAS) 并后接对象重命名模式,通过这种方式来重建表。A common application pattern is to re-create tables using CREATE TABLE AS SELECT (CTAS) followed by an object renaming pattern while loading data.

以下示例向日期维度添加新的日期记录。The following example adds new date records to a date dimension. 请注意,这里先创建了一个新表 DimDate_New,然后将它重命名以替换表的原始版本。Note how a new table, DimDate_New, is first created and then renamed to replace the original version of the table.

CREATE TABLE dbo.DimDate_New
WITH (DISTRIBUTION = ROUND_ROBIN
, CLUSTERED INDEX (DateKey ASC)
)
AS
SELECT *
FROM   dbo.DimDate  AS prod
UNION ALL
SELECT *
FROM   dbo.DimDate_stg AS stg;

RENAME OBJECT DimDate TO DimDate_Old;
RENAME OBJECT DimDate_New TO DimDate;

但是,此方法可能会导致表在用户的视图中出现并消失,并发出“表不存在”错误消息。However, this approach can result in tables appearing and disappearing from a user's view along with issuing a "table does not exist" error messages.

使用视图可为用户提供一致的呈现层,同时对基础对象重命名。Views can be used to provide users with a consistent presentation layer while the underlying objects are renamed. 用户可以借助视图来访问数据,这样就不需要看到基础表。By providing access to data through views, users don't need visibility to the underlying tables.

此层可提供一致的用户体验,同时确保数据仓库设计人员可以改进数据模型。This layer provides a consistent user experience while ensuring that the data warehouse designers can evolve the data model. 能够改进基础表意味着设计人员可以使用 CTAS 来使数据加载过程中的性能最大化。Being able to evolve the underlying tables means designers can use CTAS to maximize performance during the data loading process.

后续步骤Next steps

如需更多开发技巧,请参阅 SQL 池开发概述For more development tips, see SQL pool development overview.