使用物化视图¶

物化视图的优点¶

Snowflake 对物化视图的实施提供许多独特特征：

• 物化视图可以提高重复使用相同子查询结果的查询的性能。

• 物化视图由 Snowflake 自动透明地维护。基表更改后，后台服务会更新物化视图。相较于在应用程序级别手动维护物化视图的等效项，这样更有效且更不容易出错。

• 无论在基表上执行了多少 DML，通过物化视图访问的数据始终最新。如果在物化视图更新之前运行查询，Snowflake 要么更新物化视图，要么使用物化视图的最新部分并从基表中检索所有必需的更新数据。

决定何时创建物化视图或常规视图¶

通常，在决定是创建物化视图还是常规视图时，应依据以下条件：

• 当满足以下 所有 条件时，创建物化视图：

  • 视图中的查询结果不经常更改。这几乎总是意味着视图的基础表/基表不经常更改，或者至少在物化视图中使用的基表行的子集不经常更改。

  • 视图的结果经常使用（通常比查询结果的更改频率高得多）。

  • 查询消耗大量资源。通常，这意味着查询会消耗大量处理时间或 credit，但也可能意味着查询会消耗大量存储空间来获得中间结果。

• 当满足以下 任一 条件时，创建常规视图：

  • 视图的结果经常更改。

  • 结果不经常使用（相对于结果更改的速率）。

  • 查询不会消耗大量资源，因此重新运行的成本并不高。

这些条件只是指导准则。即使不经常使用，物化视图也可能带来好处，尤其是结果更改频率低于视图使用频率时。

此外，在决定是使用常规视图还是使用物化视图时，还需要考虑其他因素。

例如，物化视图的存储成本是一个因素；如果结果的使用频率不高（即使使用频率高于更改频率），则可能不值得投入额外的存储成本来换取性能提升。

与表、常规视图和缓存结果进行比较¶

物化视图在某些方面与表类似，在其他方面与常规（即非物化）视图类似。此外，物化视图与缓存结果有一些相似之处，特别是因为两者都允许存储查询结果以备将来重复使用。

本部分描述了这些对象在特定领域的一些相似之处和不同之处，包括：

• 查询性能。

• 查询安全性。

• 降低的查询逻辑复杂性。

• 数据群集（与查询性能有关）。

• 存储和维护成本。

运行查询后，Snowflake 会在短时间内缓存查询结果。在某些情况下，如果重新运行相同的查询，并且查询访问的表没有任何更改，那么 Snowflake 无需重新运行查询即可简单地返回相同的结果。这种重复使用形式更快、更高效，但也更不灵活。有关更多详细信息，请参阅 使用持久化查询结果。

物化视图和缓存的查询结果都提供查询性能优势：

• 相较于缓存的结果，物化视图更灵活，但通常结果更慢。

• 物化视图因其“缓存”（即视图的查询结果）而比表更快；此外，如果数据发生更改，它们可以将“缓存”用于未更改的数据，并且为任何已更改的数据使用基表。

常规视图不缓存数据，因此无法通过缓存来提高性能。但是，在某些情况下，视图可以帮助 Snowflake 生成更高效的查询计划。此外，物化视图和常规视图都允许在行级或列级公开或隐藏数据，从而增强数据安全性。

下表显示表、常规视图、缓存查询结果和物化视图之间的主要相似之处和不同之处：

物化视图的用例示例¶

本部分介绍一些常规使用场景，这些场景还提供物化视图的概念性概述：

• 假设您每天运行包含子查询 S 的查询 Q。如果 S 需耗费大量资源，并且查询每周仅更改一次的数据，则可以运行 S 并将结果缓存到名为 CT 的表中，从而提高外部查询 Q 的性能：

  • 您只能每周更新一次表。

  • 对于其余时间，当您运行 Q 时，它将引用表中存储的 S 的子查询结果。

  只要子查询 S 的结果可预测地更改（例如，每周同一时间），此操作可以正常工作。

  但是，如果 S 的结果不可预测地更改，则将结果缓存到表中会有风险；有时，如果子查询 S 的结果过期（因此缓存表 CT 的结果过期），则主查询 Q 将返回过期的结果。

  理想情况下，您需要一种特殊类型的缓存，存储很少更改但更改时间不可预测的结果。换句话说，您需要在必要时强制重新运行子查询 S``（并更新缓存表 ``CT ）。

  物化视图几乎可以实现这两个方面。您为物化视图定义一个查询，并且缓存该查询的结果（就像它们存储在内部表中一样），但是当用于定义物化视图的表更新时，Snowflake 会更新缓存。因此，子查询结果随时可用，以实现快速性能。

• 举一个不太抽象的例子，假设您经营一家大型药房的小分店，在总共成千上万种 FDA 批准药物中，分店储存了数百种药物。

  此外，还假设您有一份完整清单，列出了每位客户服用的所有药物，并且几乎所有这些客户都只订购库存药物（即特殊订单很少见）。

  在这种情况下，您可以创建物化视图，仅列出库存药物之间的相互作用。当客户订购她以前从未用过的药物时，如果物化视图涵盖该药物和她服用的所有其他药物，您无需检查整个 FDA 数据库来了解药物相互作用；您只需查看物化视图即可，因此搜索速度会更快。

• 您可以单独使用物化视图，也可以在联接中使用。

  继续以药房为例，假设您有一张表，列出了每位客户服用的所有药物；您可以将该表与关于药物相互作用的物化视图相联接，以了解客户当前的哪些药物可能与新药物相互作用。

  您可以使用外联接，确保列出客户的 所有 药物，而不管这些药物是否在物化视图中；如果外联接显示有当前药物不在物化视图中，您可以在完整药物相互作用表上重新运行查询。

查询优化器如何使用物化视图¶

您无需在 SQL 语句中指定物化视图即可使用视图。查询优化器可以针对基表或常规视图自动重写查询，以改用物化视图。

例如，假设物化视图包含对基表进行查询所需的所有行和列。优化器可以决定重写查询以使用物化视图，而不是基表。这样可以显著加快查询速度，尤其是基表包含大量历史数据时。

再举一个例子，在多表联接中，优化器可能决定对联接中的某个表使用物化视图，而不是表。

物化视图还可以用作子查询的数据源。

当优化器选择隐式使用物化视图时，物化视图会在 EXPLAIN 计划或查询配置文件中列出，而不是在基表中列出。您可以使用此类信息，试验并了解哪些查询可以从现有物化视图中受益。

用于物化视图的 DDL 命令¶

物化视图是出色的数据库对象。Snowflake 提供以下 DDL 命令，用于创建和维护物化视图：

• CREATE MATERIALIZED VIEW

• ALTER MATERIALIZED VIEW

• DROP MATERIALIZED VIEW

• DESCRIBE MATERIALIZED VIEW

• SHOW MATERIALIZED VIEWS

对物化视图的 DML 操作¶

Snowflake 不允许对物化视图使用标准 DML（例如 INSERT、UPDATE、DELETE）。Snowflake 不允许用户截断物化视图。

有关详细信息，请参阅 物化视图的使用限制 （本主题内容）。

访问控制权限¶

与物化视图相关的权限有三种：

• 对包含物化视图的架构的权限。

• 对物化视图本身的直接权限。

• 对物化视图访问的数据库对象（例如表）的权限。

您可以使用标准命令，授予和撤消对物化视图的权限：

• GRANT <privileges>

• REVOKE <privileges>

GRANT CREATE MATERIALIZED VIEW ON SCHEMA <schema_name> TO ROLE <role_name>;

安全的物化视图¶

物化视图可以是安全视图。

有关安全视图的大多数信息适用于安全的物化视图。在少数情况下，安全物化视图与安全的非物化视图不同。区别包括：

• 用于确定视图是否安全的命令。

  • 对于非物化视图，请检查 SHOW VIEWS 命令输出中的 IS_SECURE 列。

  • 对于物化视图，请检查 SHOW MATERIALIZED VIEWS 命令输出中的 IS_SECURE 列。

有关安全视图的详细信息，请参阅 使用安全视图。

用于创建安全物化视图的语法在 CREATE MATERIALIZED VIEW 中进行了介绍。

计划创建物化视图¶

在决定创建物化视图时，可以考虑进行一些分析以确定视图需求：

1. 检查频繁或昂贵的查询的筛选器、预测和汇总。

2. 使用查询配置文件和 EXPLAIN 命令，看看自动查询重写功能是否已经在使用现有的物化视图。您可能会发现，如果现有视图非常适合查询，则无需创建任何新的物化视图。

3. 在添加任何物化视图之前，记录当前的查询成本和性能，以便在创建新的物化视图后可以评估差异。

4. 如果您发现采用高选择性筛选器的查询无法受益于表聚类，则包含相同筛选器的物化视图可以帮助查询避免扫描大量数据。

   同样，如果您发现使用汇总的查询或包含评估成本非常高的表达式的查询（例如，昂贵的函数调用或对半结构化数据的昂贵操作），则使用相同表达式或汇总的物化视图可以带来好处。

5. 针对原始查询运行 EXPLAIN 命令，或者运行查询并检查查询配置文件，以查看是否正在使用新的物化视图。

6. 监控组合查询 和 物化视图成本，并且评估性能或成本优势是否足以证明物化视图的维护成本是合理的。

   还要检查基表的查询成本。如果优化器可以重写查询以使用物化视图，则查询编译会消耗更多的时间和资源。（优化器需要考虑更多的可能性。）

7. 请记住，如果物化视图可以简化查询，或者您知道物化视图将提高性能，则可以随时直接引用物化视图。但是，在大多数情况下，您只需查询基表，自动查询重写功能将为您完成此操作。

创建物化视图¶

使用 CREATE MATERIALIZED VIEW 命令创建物化视图。有关示例，请参阅 基本示例：创建物化视图 （本主题内容）。

创建物化视图时，请注意以下事项：

• 处理物化视图中不允许的列名称

• 引用基表

• 指定筛选器以优化查询

CREATE OR REPLACE MATERIALIZED VIEW mv AS
  SELECT SYSTEM$ALPHA AS col1, ...

关于创建物化视图的限制¶

以下限制适用于创建物化视图：

• 物化视图只能查询单个表。

• 不支持联接（包括自联接）。

• 物化视图无法查询以下内容：

  • 物化视图。

  • 非物化视图。

  • UDTF（用户定义的表函数）。

• 物化视图不能包括以下内容：

  • UDFs（此限制适用于所有类型的用户定义函数，包括外部函数）。

  • 窗口函数。

  • HAVING 子句。

  • ORDER BY 子句。

  • LIMIT 子句。

  • 不在 SELECT 列表内的 GROUP BY 键。物化视图中的所有 GROUP BY 键必须是 SELECT 列表的一部分。

  • GROUP BY GROUPING SETS。

  • GROUP BY ROLLUP。

  • GROUP BY CUBE。

  • 物化视图内嵌套子查询。

  • MINUS、 EXCEPT 或 INTERSECT 集合运算符。

• 许多聚合函数在物化视图定义中不允许使用。

  • 物化视图中 支持的 聚合函数如下：

    • APPROX_COUNT_DISTINCT (HLL)。

    • AVG （在 PIVOT 中使用时除外）。

    • BITAND_AGG。

    • BITOR_AGG。

    • BITXOR_AGG。

    • COUNT。

    • COUNT_IF。

    • MAX。

    • MIN。

    • STDDEV、STDDEV_SAMP。

    • STDDEV_POP。

    • SUM。

    • VARIANCE（VARIANCE_SAMP、 VAR_SAMP）。

    • VARIANCE_POP (VAR_POP)。

    物化视图中 不支持 其他聚合函数。

  备注

  物化视图中允许的聚合函数仍然有一些限制：

  • 聚合函数不能嵌套。

  • 复杂表达式中使用的聚合函数（例如 (sum(salary)/10)）只能在查询的最外层使用，不能用于子查询或内联视图。

    例如，允许使用以下函数：

    create materialized view mv1 as
        select
            sum(x) + 100
          from t;
    

    Copy

    不 允许使用以下函数：

    create materialized view mv2 as
        select
            y + 10
          from (
            select
              sum(x) as y
            from t
          );
    

    Copy

  • DISTINCT 不能与聚合函数结合使用。

  • 在物化视图中，聚合函数 AVG、COUNT、COUNT_IF、MIN、MAX 和 SUM 可用作聚合函数，但不能用作窗口函数。在物化视图中，这些函数不能与 OVER 子句一起使用：

    OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> ] )
    

    Copy

  • 如果子查询中有聚合函数，则物化视图无法基于该子查询的聚合列创建表达式。例如，考虑以下物化视图定义：

    create or replace materialized view mv1 as
        select c1 + 10 as c1new, c2
            from (select sum(c1) as c1, c2 from t group by c2);
    

    Copy

    表达式“c1 + 10”是子查询中基于聚合函数的表达式，因此会导致错误消息。

    请注意，即使等号运算符也算作表达式，这意味着，使用表示子查询中聚合函数的列的 CASE 表达式也是禁止的。

    要解决此限制，请创建不带该表达式的物化视图，然后创建包含该表达式的非物化视图，例如：

    create or replace materialized view mv1 as
        select c1, c2
            from (select sum(c1) as c1, c2 from t group by c2);
    
    create or replace view expr_v1 as
        select c1 + 10 as c1new, c2
            from (select c1, c2 from mv1);
    

    Copy

• 物化视图中使用的函数必须是确定性的。例如，不允许使用 CURRENT_TIME 或 CURRENT_TIMESTAMP。

• 不应使用为不同的参数设置（例如会话级参数 TIMESTAMP_TYPE_MAPPING）生成不同结果的函数来定义物化视图。

  例如，假设视图定义如下：

  create materialized view bad_example (ts1) as
      select to_timestamp(n) from t1;
  

  Copy

  来自 TO_TIMESTAMP(n) 的返回值的数据类型取决于参数 TIMESTAMP_TYPE_MAPPING，因此物化视图的内容取决于视图创建时 TIMESTAMP_TYPE_MAPPING 的值。

  在创建物化视图时，系统会评估和存储用于定义各列的表达式。如果列定义依赖于特定的会话变量，并且该会话变量发生变化，则不会重新计算表达式，也不会更新物化视图。如果物化视图依赖于会话变量的特定值，并且该会话变量的值已更改，则物化视图查询将失败。

  要避免此问题，请强制将表达式转换为不依赖于任何会话变量的值。以下示例将输出强制转换为与 TIMESTAMP_TYPE_MAPPING 参数无关的特定数据类型：

  create materialized view good_example (ts1) as
      select to_timestamp(n)::TIMESTAMP_NTZ from t1;
  

  Copy

  此问题特定于 物化 视图。非物化视图根据当前参数设置动态生成输出，因此结果不会过时。

• 在物化视图的定义中，不支持从 FLATTEN 函数的输出中选择 SEQ 列。

  从物化视图中选择时， SEQ 列中的值不一定按照任意方式排序。如果在物化视图定义中选择此列，则输出可能不确定。

• 无法使用 Time Travel 功能 创建物化视图。

基本示例：创建物化视图¶

本部分包含关于创建和使用物化视图的基本示例：

CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT My_ResourceIntensive_Function(binary_col) FROM table1;

SELECT * FROM mv1;

Copy

` 示例 `_ （本主题内容）中提供更详细的示例。

了解如何维护物化视图¶

创建物化视图后，后台进程会自动维护物化视图中的数据。请注意以下事项：

• 物化视图的维护由后台进程执行，时间最好基于基表和物化视图上的工作负载。

  • 此进程根据基表的 DML 操作（插入、更新和删除）所做的更改来更新物化视图。

    此外，基表群集还可能导致物化视图的刷新。请参阅 用于群集物化视图及其基表的最佳实践。

  • 在基表中插入行时，该进程会执行“刷新”操作，将新行插入物化视图。

  • 在基表中删除行时，该进程对物化视图执行“压缩”操作，将这些行从物化视图中删除。

• 要查看上次刷新物化视图的时间，请执行 SHOW MATERIALIZED VIEWS 命令。

  检查输出中的 REFRESHED_ON 和 BEHIND_BY 列：

  • REFRESHED_ON 和 COMPACTED_ON 列分别显示刷新和压缩操作所处理的基表上上次 DML 操作的时间戳。

  • BEHIND_BY 列表示基表更新后物化视图更新的时间长度。

• 如果维护滞后，查询的运行速度可能会比视图更新时慢，但结果始终最新。

  如果物化视图的某些微分区已过时，Snowflake 会跳过这些分区并从基表中查找数据。

• 如果后台进程遇到某些用户错误（例如，视图查询导致“零除”错误），则进程将使物化视图失效。

  查询无效物化视图会导致错误。错误消息包括物化视图失效的原因。例如：

  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero
  

  如果出现这种情况，请解决错误消息中描述的问题（例如，删除导致“零除”错误的行），然后使用以下命令恢复物化视图： ALTER MATERIALIZED VIEW ...RESUME 命令来执行完全刷新。

暂停和恢复物化视图维护¶

如果您需要暂停物化视图的维护和使用，请执行 ALTER MATERIALIZED VIEW 命令与 SUSPEND 参数：

ALTER MATERIALIZED VIEW <name> SUSPEND

如果您暂停视图的维护，则在恢复维护之前无法查询该视图。

要恢复物化视图的维护和使用，请执行 ALTER MATERIALIZED VIEW 命令与 RESUME 参数：

ALTER MATERIALIZED VIEW <name> RESUME

有关示例，请参阅 暂停物化视图更新。

显示有关物化视图的信息¶

以下命令和视图提供有关物化视图的信息：

• SHOW VIEWS 命令返回有关物化视图和常规视图的信息。

• INFORMATION_SCHEMA.TABLES 视图 显示物化视图。TABLE_TYPE 列显示“MATERIALIZED VIEW”。IS_INSERTABLE 列始终为“NO”，因为您无法直接插入物化视图中。

  备注

  INFORMATION_SCHEMA.VIEWS 视图 不显示物化视图。物化视图通过 INFORMATION_SCHEMA.TABLES 显示。

物化视图的使用限制¶

在使用物化视图时，适用以下限制：

• 为确保物化视图与用于定义物化视图的基表保持一致，您不能对物化视图本身执行大多数 DML 操作。例如，您不能将行直接插入物化视图（当然可以将行插入基表）。禁止的 DML 操作包括：

  • COPY

  • DELETE

  • INSERT

  • MERGE

  • UPDATE

  不支持截断物化视图。

• 您不能使用 CREATE MATERIALIZED VIEW ... CLONE... 命令直接克隆物化视图。但是，如果您克隆包含物化视图的架构或数据库，则该物化视图将被克隆并包含在新架构或数据库中。

• Snowflake 不支持使用 Time Travel 功能 来查询 过去某个时刻 的物化视图（例如，在查询物化视图时使用 AT 子句 ）。

  但是，您可以使用 Time Travel 克隆包含过去某个时刻的物化视图的数据库或架构。有关详细信息，请参阅 物化视图和 Time Travel。

• Snowflake 不监控物化视图，请参阅 使用资源监视器。

基表更改对物化视图的影响¶

以下各部分说明物化视图如何受基表更改的影响。

• 向基表添加列

• 更改或删除基表中的列

• 重命名或替换基表

• 删除基表

克隆架构和数据库中的物化视图¶

如果您克隆包含物化视图的架构或数据库，也会克隆该物化视图。

如果您同时克隆物化视图和相应的基表（作为相同 CREATE SCHEMA ... CLONE 或 CREATE DATABASE ... CLONE 操作的一部分），则克隆的物化视图引用克隆的基表。

如果您克隆物化视图而不克隆基表（例如，如果表位于 Database1.Schema1，视图位于 Database1.Schema2，并且您只克隆 Schema2 而不是所有 Database1），则克隆视图将引用原始基表。

估算和控制成本¶

没有用于估算物化视图维护成本的工具。一般来说，成本与以下因素成正比：

• 每个基表上创建的物化视图的数量，以及基表更改时每个物化视图中发生变更的数据量。基表中微分区的任何更改都需要最终的物化视图维护，无论这些更改是出于重聚类，还是出于基表上运行的 DML 语句。

• 聚类的物化视图的数量。维护群集（表或物化视图）会增加成本。

  如果物化视图的聚类方式与基表不同，则物化视图中更改的微分区数量可能大大超过基表中更改的微分区数量。

  例如，假设基表主要通过插入（追加）数据来更改，并且不聚类，因此基表很大程度上按照行插入表中的顺序排列。设想物化视图按独立列（例如邮政编码）聚类。如果向基表添加 100 个新行，这些行可能会进入一个或两个新的微分区，而基表中的其他微分区保持不变。但是，这 100 行可能需要在聚类物化视图中重写 100 个微分区。

  再举一个例子，考虑下删除操作。删除非聚类基表中较早的行可能只删除较早的微分区，但可能需要在未按年龄聚类的物化视图中更改更多微分区。

  （有关聚类物化视图的更多详细信息，请参阅 物化视图和群集。）

您可以仔细选择视图创建数量、用于创建视图的表以及每个视图的定义（包括视图中的行数和列数），从而控制物化视图的维护成本。

您还可以通过暂停或恢复物化视图来控制成本；但是，暂停维护通常只会推迟成本，而不会降低成本。维护推迟的时间越长，要做的维护就越多。

另请参阅 用于维护物化视图的最佳实践。

查看成本¶

您可以使用 Snowsight、 Classic Console 或 SQL 查看物化视图的维护账单成本：

Snowsight:

作为具有适当权限的用户，请选择 Admin » Usage。

Classic Console:

作为账户管理员，请选择 Account » Billing & Usage。

名为 MATERIALIZED_VIEW_MAINTENANCE 的 Snowflake 虚拟仓库会跟踪 credit 成本。

SQL:

查询以下任一内容：

• MATERIALIZED_VIEW_REFRESH_HISTORY 表函数（在 Snowflake Information Schema 中）。

  例如：

  SELECT * FROM TABLE(INFORMATION_SCHEMA.MATERIALIZED_VIEW_REFRESH_HISTORY());
  

  Copy

• MATERIALIZED_VIEW_REFRESH_HISTORY 视图 视图（在 Account Usage 中）。

  可对 MATERIALIZED_VIEW_REFRESH_HISTORY 视图执行以下查询：

  查询：物化视图成本历史记录（按天、按对象）

  此查询提供物化视图的完整列表，以及过去 30 天通过服务使用的 Credit（按天细分）。Credit 使用中的任何违规行为或持续高使用量都是需要进行进一步调查的标志。

  SELECT TO_DATE(start_time) AS date,
    database_name,
    schema_name,
    table_name,
    SUM(credits_used) AS credits_used
  FROM snowflake.account_usage.materialized_view_refresh_history
  WHERE start_time >= DATEADD(month,-1,CURRENT_TIMESTAMP())
  GROUP BY 1,2,3,4
  ORDER BY 5 DESC;
  

  Copy

  查询：物化视图历史记录和 m 天平均值

  此查询显示去年物化视图使用的 Credit 每日平均值（按周分组）。它可以帮助识别一年中每日平均值的异常情况，以便您可以调查使用量的峰值或意外变化。

  WITH credits_by_day AS (
    SELECT TO_DATE(start_time) AS date,
      SUM(credits_used) AS credits_used
    FROM snowflake.account_usage.materialized_view_refresh_history
    WHERE start_time >= DATEADD(year,-1,CURRENT_TIMESTAMP())
    GROUP BY 1
    ORDER BY 2 DESC
  )
  
  SELECT DATE_TRUNC('week',date),
    AVG(credits_used) AS avg_daily_credits
  FROM credits_by_day
  GROUP BY 1
  ORDER BY 1;
  

  Copy

用于创建物化视图的最佳实践¶

• 大多数物化视图应执行以下一项或两项操作：

  • 筛选数据。为此，您可以执行以下操作：

    • 筛选行（例如，定义物化视图，以便仅包含非常新的数据）。在某些应用程序中，要存储的最佳数据是异常数据。例如，如果您正在监控天然气管道中的压力，以估计管道何时可能出现故障，则可以在基表中存储所有压力数据，而在物化视图中仅存储异常高的压力测量值。同样，如果您正在监控网络流量，则基表可能会存储所有监控信息，而物化视图可能仅存储异常和可疑信息（例如，来自已知用于发起 DOS [拒绝服务] 攻击的 IP 地址）。

    • 筛选列（例如，选择特定列，而不是“SELECT * ...”）。使用 SELECT * ... 来定义物化视图通常会很昂贵。它还可能在将来导致错误；如果稍后将列添加到基表中（例如 ALTER TABLE ... ADD COLUMN ...），则物化视图不会自动合并新列。

  • 执行资源密集型操作并存储结果，这样就不需要经常执行资源密集型操作。

• 您可以为相同基表创建多个物化视图。例如，您可以创建一个仅包含更新数据的物化视图，以及另一个用于存储异常数据的物化视图。然后，您可以创建一个非物化视图，该视图将两个表联接，显示与异常历史数据相匹配的新数据，这样您就可以快速检测异常情况，例如正在加剧的 DOS（拒绝服务）攻击。

  Snowflake 建议仅在以下情况下对异常数据使用物化视图：

  • 基表未聚类，或者包含异常数据的列尚不属于基表的群集密钥。

  • 这些数据非常不寻常，易于区分，但并不罕见，很少使用。（如果数据很少使用，则物化视图的维护成本可能会超过在使用物化视图时快速访问所带来的性能优势和成本节约。）

用于维护物化视图的最佳实践¶

• Snowflake 建议对基表进行批处理 DML 操作：

  • DELETE：如果表存储最近一段时间（例如最近一天、一周或一个月）的数据，那么当您通过删除旧数据来修剪基表时，基表更改会传播到物化视图。根据数据在微分区中的分布方式，这可能会导致您为物化视图的后台更新支付更多费用。在某些情况下，您可以通过降低删除频率（例如，每天而不是每小时，或每小时而不是每 10 分钟）来降低成本。

    如果您不需要保留特定数量的旧数据，则应尝试找到成本与功能之间的出色平衡。

  • INSERT、 UPDATE 和 MERGE：在基表上批处理这些类型的 DML 语句可以降低物化视图的维护成本。

用于群集物化视图及其基表的最佳实践¶

• 如果在基表上创建物化视图，并且经常访问物化视图而不经常访问基表，则避免对基表进行聚类通常会更有效。

  如果在群集表上创建物化视图，请考虑移除基表上的所有群集，因为基表群集的任何更改最终都需要刷新物化视图，这会增加物化视图的维护成本。

• 群集物化视图，尤其是频繁更改的基表上的物化视图，会增加成本。群集物化视图不要超过所需数量。

• 关于群集表的几乎所有信息也适用于群集物化视图。有关群集表的更多信息，请参阅 选择群集密钥的策略。

简单物化视图¶

第一个示例说明了简单物化视图和简单物化查询。

创建表并加载数据，然后创建视图：

CREATE TABLE inventory (product_ID INTEGER, wholesale_price FLOAT,
  description VARCHAR);
    
CREATE OR REPLACE MATERIALIZED VIEW mv1 AS
  SELECT product_ID, wholesale_price FROM inventory;

INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (1, 1.00, 'cog');

Copy

从视图中选择数据：

SELECT product_ID, wholesale_price FROM mv1;
+------------+-----------------+
| PRODUCT_ID | WHOLESALE_PRICE |
|------------+-----------------|
|          1 |               1 |
+------------+-----------------+

Copy

联接物化视图¶

您可以将物化视图与表或其他视图联接。本示例基于上一个示例构建，首先创建一个附加表，然后创建非物化视图，通过将物化视图联接到表来显示利润：

CREATE TABLE sales (product_ID INTEGER, quantity INTEGER, price FLOAT);

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (1,  1, 1.99);

CREATE or replace VIEW profits AS
  SELECT m.product_ID, SUM(IFNULL(s.quantity, 0)) AS quantity,
      SUM(IFNULL(quantity * (s.price - m.wholesale_price), 0)) AS profit
    FROM mv1 AS m LEFT OUTER JOIN sales AS s ON s.product_ID = m.product_ID
    GROUP BY m.product_ID;

Copy

从视图中选择数据：

SELECT * FROM profits;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
+------------+----------+--------+

Copy

暂停物化视图更新¶

以下示例暂停 mv1 物化视图的使用（和维护），并显示在暂停物化视图时，视图查询会生成错误消息：

ALTER MATERIALIZED VIEW mv1 SUSPEND;
    
INSERT INTO inventory (product_ID, wholesale_price, description) VALUES 
    (2, 2.00, 'sprocket');

INSERT INTO sales (product_ID, quantity, price) VALUES 
   (2, 10, 2.99),
   (2,  1, 2.99);

Copy

从物化视图中选择数据：

SELECT * FROM profits ORDER BY product_ID;

Copy

输出：

002037 (42601): SQL compilation error:
Failure during expansion of view 'PROFITS': SQL compilation error:
Failure during expansion of view 'MV1': SQL compilation error: Materialized View MV1 is invalid.

Copy

恢复：

ALTER MATERIALIZED VIEW mv1 RESUME;

Copy

从物化视图中选择数据：

SELECT * FROM profits ORDER BY product_ID;
+------------+----------+--------+
| PRODUCT_ID | QUANTITY | PROFIT |
|------------+----------+--------|
|          1 |        1 |   0.99 |
|          2 |       11 |  10.89 |
+------------+----------+--------+

Copy

对物化视图进行聚类¶

本示例创建一个物化视图，然后将其聚类：

这些语句创建两个表，用于跟踪有关管道（例如天然气管道）各分段的信息。

在不久的将来很有可能出现故障的分段通常是非常旧的分段、由易于腐蚀的材料制成的分段，或者经历过异常高压的分段，因此本示例跟踪每条管道的使用年限、压力和材料（铁、铜、 PVC 塑料等）。

CREATE TABLE pipeline_segments (
    segment_ID BIGINT,
    material VARCHAR, -- e.g. copper, cast iron, PVC.
    installation_year DATE,  -- older pipes are more likely to be corroded.
    rated_pressure FLOAT  -- maximum recommended pressure at installation time.
    );
    
INSERT INTO pipeline_segments 
    (segment_ID, material, installation_year, rated_pressure)
  VALUES
    (1, 'PVC', '1994-01-01'::DATE, 60),
    (2, 'cast iron', '1950-01-01'::DATE, 120)
    ;

CREATE TABLE pipeline_pressures (
    segment_ID BIGINT,
    pressure_psi FLOAT,  -- pressure in Pounds per Square Inch
    measurement_timestamp TIMESTAMP
    );
INSERT INTO pipeline_pressures 
   (segment_ID, pressure_psi, measurement_timestamp) 
  VALUES
    (2, 10, '2018-09-01 00:01:00'),
    (2, 95, '2018-09-01 00:02:00')
    ;

Copy

管道分段的更改频率不高，较旧的管道分段更有可能出现故障，因此请为较旧的分段创建物化视图。

CREATE MATERIALIZED VIEW vulnerable_pipes 
  (segment_ID, installation_year, rated_pressure) 
  AS
    SELECT segment_ID, installation_year, rated_pressure
        FROM pipeline_segments 
        WHERE material = 'cast iron' AND installation_year < '1980'::DATE;

Copy

您可以添加群集或更改群集密钥。例如，要基于 installation_year 进行聚类，请执行以下语句：

ALTER MATERIALIZED VIEW vulnerable_pipes CLUSTER BY (installation_year);

Copy

新的压力测量值经常出现（可能每 10 秒一次），因此基于压力测量值维护物化视图将非常昂贵。因此，尽管近期压力数据的高性能（快速检索）很重要，但 pipeline_pressures 表启动时没有物化视图。

如果性能太慢，您可以创建仅包含近期压力数据的物化视图，或者仅包含有关异常高压事件的数据的物化视图。

创建（非物化）视图，将来自物化视图和 pipeline_pressures 表的信息组合在一起：

CREATE VIEW high_risk AS
    SELECT seg.segment_ID, installation_year, measurement_timestamp::DATE AS measurement_date, 
         DATEDIFF('YEAR', installation_year::DATE, measurement_timestamp::DATE) AS age, 
         rated_pressure - age AS safe_pressure, pressure_psi AS actual_pressure
       FROM vulnerable_pipes AS seg INNER JOIN pipeline_pressures AS psi 
           ON psi.segment_ID = seg.segment_ID
       WHERE pressure_psi > safe_pressure
       ;

Copy

现在，列出高风险管道分段：

SELECT * FROM high_risk;
+------------+-------------------+------------------+-----+---------------+-----------------+
| SEGMENT_ID | INSTALLATION_YEAR | MEASUREMENT_DATE | AGE | SAFE_PRESSURE | ACTUAL_PRESSURE |
|------------+-------------------+------------------+-----+---------------+-----------------|
|          2 | 1950-01-01        | 2018-09-01       |  68 |            52 |              95 |
+------------+-------------------+------------------+-----+---------------+-----------------+

Copy

这表明由腐蚀材料制成的 segment_id = 2 管道分段较旧。该分段在安装时从未经历超过额定压力上限的压力，但由于可能发生腐蚀，其“安全极限”随着时间的推移而下降，其经历的最高压力高于压力测量时为与该管道新旧程度同等的管道所建议的压力。

基于共享数据创建物化视图¶

您可以基于共享数据创建物化视图。

账户 1：

create or replace table db1.schema1.table1(c1 int);
create or replace share sh1;
grant usage on database db1 to share sh1;
alter share sh1 add accounts = account2;
grant usage on schema db1.schema1 to share sh1;
grant select on table db1.schema1.table1 to share sh1;

账户 2：

create or replace database dbshared from share account1.sh1;
create or replace materialized view mv1 as select * from dbshared.schema1.table1 where c1 >= 50;

共享物化视图¶

您可以使用 Snowflake 的数据共享功能来共享物化视图。

有关数据共享的更多信息，请参阅 Snowflake 中的数据共享和 Collaboration 概述。

Compilation Error: Failure during expansion of view '<name>': SQL compilation error: Materialized View <name> is invalid.¶

可能原因:

• 物化视图已暂停。有关暂停和恢复视图的更多信息，请参阅 ALTER MATERIALIZED VIEW。

• 针对物化视图基表的更改使物化视图失效。例如，以下情况会返回此错误：

  • 基表已删除。

  • 基表列中的一列已删除。

• 后台进程遇到特定类型的错误（例如，“零除”错误），并且未能刷新物化视图。

可能的解决方案:

• 如果视图已暂停，请执行以下操作：

  • 考虑通过执行以下命令来恢复视图： ALTER MATERIALIZED VIEW ...RESUME。

  • 考虑对基表运行查询。但是，相较于对物化视图运行查询，这可能会消耗更多 credit并花费更长时间。

• 如果基表已修改或删除，请执行以下操作：

  • 如果基表已删除，则删除物化视图。

  • 如果基表已修改（例如，删除了视图引用的列），并且物化视图在新版本的表中仍然有用，则可以考虑删除并重新创建物化视图，使用基表中保留的列。

  • 如果没有明显的其他原因导致错误消息，请考虑删除并重新创建物化视图。

  • 考虑对基表运行查询。但是，相较于对物化视图运行查询，这可能会消耗更多 credit并花费更长时间。

• 如果后台进程因错误而无法刷新物化视图，则错误消息应包含有关物化视图失效原因的详细信息。例如：

  Failure during expansion of view 'MY_MV':
    SQL compilation error: Materialized View MY_MV is invalid.
    Invalidation reason: Division by zero
  

如果出现这种情况，请解决错误消息中描述的问题，然后使用以下命令恢复物化视图： ALTER MATERIALIZED VIEW ...RESUME 命令来执行完全刷新。

SHOW MATERIALIZED VIEWS 命令显示未更新的物化视图¶

可能的原因:

一个可能的原因是因视图定义中的 SELECT 语句失败而导致刷新失败。

由于刷新是由后台进程执行，因此在尝试刷新时您不会看到错误消息。相反，当您查询物化视图或执行 SHOW MATERIALIZED VIEWS 时，您会看到错误消息。

可能的解决方案:

如果 invalid 列为 true，请检查 invalid_reason 列以了解视图失效的原因。

在某些情况下，您可以手动运行物化视图定义中的 SELECT 语句，或者对物化视图定义中引用的表运行更简单（更便宜）的 SELECT 语句，以便调试问题。

如果您不知道物化视图的确切定义，则可以在 SHOW MATERIALIZED VIEWS 输出中找到，或者使用 GET_DDL 函数找到。