在之前的博文《vertcia介绍》中有讲到,在vertica中,表只是一个逻辑上的概念,其真实数据其实是存储在一个个的projection中。projections以优化查询执行的格式存储数据。与物化视图类似,projections将结果集存储在磁盘上,而不是在每次查询时计算它们。您可以使用新数据或更新数据刷新projections。
projections提供以下好处:
1.压缩和编码数据以减少存储空间。
Vertica尽可能对编码数据进行操作,以避免解码成本。压缩和编码的这种组合可优化磁盘空间,同时最大限度地提高查询性能。
2.促进跨数据库群集的分发。
根据其大小,可以跨群集节点对projections进行分段或复制。
例如,可以对大型表的projections进行分段并在所有节点上分布。可以跨所有节点复制小表的未分段projections。
3.对最终用户透明。
Vertica的查询优化器会自动选择执行给定查询的最佳projections。
4.提供高可用性和恢复。
Vertica复制群集中至少K + 1个节点上的表列。如果一台计算机在K-Safe环境中出现故障,则数据库将继续使用其余节点上的复制数据进行操作。当节点恢复正常操作时,它会自动查询其他节点以恢复数据和丢失的对象。
projections与物化视图
再回顾一下projection的定义:
A projection consists of a set of columns with the same sort order, defined by a column to sort by or a sequence of columns by which to sort. Like an index or materialized view in a traditional database, a projection accelerates query processing.
物化视图:物化视图是包括一个查询结果的数据库对象,它是远程数据的的本地副本,或者用来生成基于数据表求和的汇总表。物化视图和视图类似,反映的是某个查询的结果,但是和视图仅保存SQL定义不同,物化视图本身会存储数据,因此是物化了的视图。
对比项 | Projections | 物化视图 |
---|---|---|
定义 | 包含列数据 | 包含查询结果 |
数据存储 | 数据存储在projections中 | 数据是从表中拷贝到物化视图里 |
创建方式 | 创建于:1. 在进行数据导入时自动创建。 2. Database designer的方式创建。3. 手动创建。 | 通过定义查询或声明的方式创建 |
更新 | 数据加载时会自动更新 | 有特定的更新流程 |
数据一致性 | 通常会包含最新的数据 | 可能会包含out-of-data的数据 |
索引 | 不必要也不存在 | 包含索引 |
创建projection
可通过手动创建及自动创建的方式去创建一个或多个projection。
projection的创建语句如下图所示:
可通过vertica 自带的Database Designer去自动创建并部署一些projection,以达到优化数据库性能的目的。
projection的分类
有3类projection,分别是超级projections,查询特定的projections,聚合projections。
超级projections
定义:包含表的所有列的projections。
对于数据库中的每个表,Vertica 至少需要一个projections,即超级projections。如果CREATE PROJECTION 指定包括所有表列,则可以创建超级projections。一个表可以有多个超级projections。
在某些条件下,Vertica 会在创建表时立即自动创建表的超级projections。
对于普通表,当建表语句中包含以下内容中的某一个时,会自动创建super projection:
AS SELECT
ENCODED BY
ORDER BY
SEGMENTED BY / UNSEGMENTED
KSAFE
当建表语句不包含上面语句中的任何一个时,只有在通过insert或copy向表内导入数据时,才会自动创建super projection。
查询特定的projections
查询特定的projections是仅包含表列子集以处理给定查询的projections。查询特定的projections可显著提高已优化查询的性能。
若想优化一些特定的查询sql,可通过手动创建projections的方式进行优化,也可以通过提供具体查询,利用Database Desinger去创建增量设计。
聚合projections
包括表达式或聚合函数(例如 SUM 和 COUNT)的查询在使用已包含聚合数据的projections时, 其执行效率更高。
补充:在vertcia的8.0版本之前,还有一种projection,叫pre-join projections,从命名上可以看出,这种projection主要用于优化表之间的关联。在8.0版本之后,pre-join projections 已被flatten table所替代。关于flatten table后面会进行介绍。
projections的分段
projections可以分为未分段的projections和分段的projections。
分段是指跨群集节点组织和分发数据,以便实现快速数据清除和提升查询性能。分段的目的是使数据在多个数据库节点上均匀分布,以便所有节点均参与查询执行。可通过 CREATE PROJECTION 语句的哈希分段子句来指定分段。
未分段的projections
在许多情况下,维度表相对较小,因此您无需对其进行分段。因此,您应该设计一个K-safe数据库,以便在所有集群节点上复制其维度表的projections而不进行分段。您可以使用CREATE PROJECTION包含UNSEGMENTED ALL NODES的语句创建未分段的projections 。此子句指定在所有群集节点上创建相同的projections实例。
=> CREATE PROJECTION store.store_dimension_proj (storekey, name, city, state)
AS SELECT store_key, store_name, store_city, store_state
FROM store.store_dimension
UNSEGMENTED ALL NODES;
CREATE PROJECTION
分段的projections
通常会为大型事实表创建分段projections。Vertica将分段projections拆分为相似大小的块(段),并在群集中均匀分布这些段。系统K-safety确定在不同节点上创建和维护每个段的重复(伙伴)projections的数量。
您可以使用CREATE PROJECTION包含SEGMENTED BY子句的语句创建分段projections。
=> CREATE PROJECTION public.employee_dimension_super
AS SELECT * FROM public.employee_dimension
ORDER BY employee_key
SEGMENTED BY hash(employee_key) ALL NODES;
vertica会根据系统的ksafe值去创建projection的副本,假设某个vertica集群的ksafe为1,则在任意一个节点上,都会有两个projection,其中一个projection为原始的分段projection,另一个为其他节点分段projection的副本。
=> SELECT projection_name FROM projections WHERE projection_basename='employee_dimension_super';
projection_name
-----------------------------
employee_dimension_super_b0
employee_dimension_super_b1
(2 rows)
K-safe数据库projections
可通过CREATE PROJECTION选项KSAFE设置各个projections的K-safety。projections K-safety必须大于等于数据库 K-safety(可通过select get_design_ksafe()
或者select * from system
命令来查看k-safety)。如果您忽略设置 KSAFE,projections将从数据库获取Ksafety。
分段的projections
如果将数据库 K-safety 设置为 1,则数据库的每个projections分段需要两个实例(或伙伴实例)。以下 CREATE PROJECTION 为事实数据表 store.store_orders_fact 定义分段projections:
VMart=> CREATE PROJECTION store.store_orders_fact (prodkey, ordernum, storekey, total)
AS SELECT product_key, order_number, store_key, quantity_ordered*unit_price FROM store.store_orders_fact
SEGMENTED BY HASH(product_key, order_number) ALL NODES KSAFE 1;
CREATE PROJECTION
CREATE PROJECTION 语句中的三个关键字适用于设置projections K-safety:
K-SAFE 1
将 K-safety 设置为 1。Vertica 使用以下命名约定自动创建两个projections实例:
projection-name_bn
其中, n 是介于 0 和 k 之间的值。由于 K-safety 设置为 1, 因此
Vertica 将创建两个projections:
store.store_orders_fact_b0
store.store_orders_fact_b1
ALL NODES
指定将projections跨所有群集节点分段。
HASH
有助于确保分段projections数据在群集上均匀分布。
未分段的projections
在 K-safe 数据库中, 使用包括关键字 UNSEGMENTED ALL NODES 的 CREATE PROJECTION 语句创建未分段projections。这些关键字指定在所有群集节点上创建整个projections的相同实例。如果在单个节点上创建未分段投影,则Vertica认为它不安全且不使用它。
以下示例展示了如何为表 store.store_dimension 创建未分段projections:
VMart=> CREATE PROJECTION store.store_dimension_proj (storekey, name, city, state) AS SELECT store_key, store_name, store_city, store_state
FROM store.store_dimension UNSEGMENTED ALL NODES;
CREATE PROJECTION
查看projections
1.简单查看有哪些projections:
\dj
可在 \dj后接具体的projection名。
2.可以使用系统视图projection来查看projections的详细信息。
=> SELECT projection_name FROM projections WHERE projection_basename='employee_dimension_super';
projection_name
-----------------------------
employee_dimension_super_b0
employee_dimension_super_b1
(2 rows)
3.使用PROJECTION_COLUMNS视图,可以查看到关于projections列,如编码类型,排序,统计的类型,并在其列统计信息的最后更新的时间信息。
刷新及删除projections
刷新projections
CREATE PROJECTION 并不将数据加载到物理存储中。如果锚表已经包含数据,请运行 START_REFRESH 来更新projections。更新projections可能比较耗时,具体视表中的数据量而定。projections处于最新状态时, 仍会在 COPY、DELETE、INSERT, MERGE 或UPDATE 语句执行期间自动更新。
示例:
=> SELECT START_REFRESH();
START_REFRESH
----------------------------------------
Starting refresh background process.
(1 row)
或者使用refresh命令,有针对性的对某些表的projections进行刷新。
示例:
select refresh('dbd_test.web_returns, dbd_test.web_sales, dbd_test.catalog_sales, dbd_test.store_sales');
可以查询PROJECTION_REFRESHES
和PROJECTIONS
查看刷新操作的进度。您还可以调用Vertica函数GET_PROJECTIONS
来查看给定表的投影刷新的最终状态:
=> SELECT GET_PROJECTIONS('customer_dimension');
GET_PROJECTIONS
----------------------------------------------------------------------------------------------------------
Current system K is 1.
# of Nodes: 3.
Table public.customer_dimension has 2 projections.
Projection Name: [Segmented] [Seg Cols] [# of Buddies] [Buddy Projections] [Safe] [UptoDate] [Stats]
----------------------------------------------------------------------------------------------------
public.customer_dimension_b1 [Segmented: Yes] [Seg Cols: "public.customer_dimension.customer_key"] [K: 1]
[public.customer_dimension_b0] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
public.customer_dimension_b0 [Segmented: Yes] [Seg Cols: "public.customer_dimension.customer_key"] [K: 1]
[public.customer_dimension_b1] [Safe: Yes] [UptoDate: Yes] [Stats: RowCounts]
(1 row)
删除projections
可通过 DROP PROJECTION 语句明确删除projections。当您删除projections的锚表时,projections也会被隐式删除。
示例:
DROP PROJECTION
dbd_test.dbgen_version_DBD_1_seg_99sql_1_b0, dbd_test.dbgen_version_DBD_1_seg_99sql_1_b1 CASCADE;
参考:
vertica官方文档9.1.x—>Administrator’s Guide—>Working with Projections
链接如下:
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Projections/WorkingWithProjections.htm%3FTocPath%3DAdministrator’s%20Guide|Working%20with%C2%A0Projections|_____0