flattened table

最新推荐文章于 2022-10-09 09:52:59 发布

hmxz1024

最新推荐文章于 2022-10-09 09:52:59 发布

阅读量556

点赞数

分类专栏： Vertica 文章标签： vertica扁平表 flattened table

本文链接：https://blog.csdn.net/hmxz2nn/article/details/90316875

版权

Vertica 专栏收录该内容

27 篇文章 9 订阅

订阅专栏

flattened table(扁平表)

高度规范化的数据库设计通常使用星型或雪花型架构模型，包括多个大型事实表和许多较小的维度表。查询通常涉及大事实表和多个维表之间的连接。根据表的数量和加入的数据量，这些查询会产生很大的开销。

为避免此问题，某些用户创建了宽表，这些表组合了查询所需的所有事实和维度表列，这些表可以显着加快查询执行速度。但是，维护冗余的规范化和非规范化数据集有其自己的管理成本。

非规范化或扁平化的表可以最小化这些问题。扁平表可以包含通过查询其他表来获取其值的列。源表和扁平表的操作是分离的，一个中的更改不会自动传播到另一个。这最小化了非规范化表的典型开销。
扁平表使用以下列约束子句之一定义派生列：
1.DEFAULT query-expression 在以下两个事件发生时设置列值：

使用CREATE TABLE 或创建列时ALTER TABLE...ADD COLUMN。
任何表加载操作如INSERT。

2.SET USING query-expression 仅在REFRESH_COLUMNS调用函数时设置列值。
在这两种情况下，query-expression必须只返回一个行和列值，或者不返回任何值。如果查询未返回任何行，则将列值设置为NULL。

创建扁平表

扁平表通常是一个事实表，其中一个或多个列查询其他表的值，通过DEFAULT或SET USING约束。与其他列一样，您可以在创建扁平表时设置这些约束，也可以在以后通过修改表模式设置这些约束：

CREATE TABLE ...(...,列名 数据类型 {DEFAULT | SET USING} 表达式,...)
ALTER TABLE ... ADD COLUMN 列名 {DEFAULT | SET USING} 表达式
ALTER TABLE ... ALTER COLUMN column-name {SET DEFAULT | SET USING} 表达式

DEFAULT和SET USING约束可用于所有数据类型的列。
您为这些约束设置的表达式存储在系统表中COLUMNS，列COLUMN_DEFAULT和COLUMN_SET_USING中。两个约束都支持相同的表达式。

DEFAULT 并且SET USING通常支持相同的表达式。这些包括：

查询
同一个表中的其他列
文字（常数）
所有Vertica支持的操作

以下类别的功能：

空处理
用户定义的标量
系统信息
字符串
数学
格式

注意：如果SET USING或DEFAULT查询表达式连接具有相同名称的两列，则列名必须包括其表名。否则Vertica假定两列都引用维度表，并且谓词始终求值为true。

扁平表示例

在以下示例中，列orderFact.cust_name和orderFact.cust_gender分别定义为SET USING和DEFAULT列。两列都通过查询表custDim获取它们的值：

=> CREATE TABLE public.custDim(
     cid int PRIMARY KEY NOT NULL,
     name varchar(20),
     age int,
     gender varchar(1)
);
=> CREATE TABLE public.orderFact(
     order_id int PRIMARY KEY NOT NULL,
     cid int REFERENCES public.custDim(cid),
     cust_name varchar(20) SET USING (SELECT name FROM public.custDim WHERE (custDim.cid = orderFact.cid)),
     cust_gender varchar(1) DEFAULT (SELECT gender FROM public.custDim WHERE (custDim.cid = orderFact.cid)),
     amount numeric(12,2)
);

向两个表中插入一些数据：

=> INSERT INTO custDim VALUES(1, 'Alice', 25, 'F');
=> INSERT INTO custDim VALUES(2, 'Boz', 30, 'M');
=> INSERT INTO custDim VALUES(3, 'Eva', 32, 'F');
=>
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(100, 1, 15);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(200, 1, 1000);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(300, 2, -50);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(400, 3, 100);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(500, 2, 200);
=> COMMIT;

查询两张表，得到以下信息：

=> SELECT * FROM custDim;
 cid | name  | age | gender
-----+-------+-----+--------
   1 | Alice |  25 | F
   2 | Boz   |  30 | M
   3 | Eva   |  32 | F
(3 rows)

=> SELECT * FROM orderFact ORDER BY cid;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      100 |   1 |           | F           |   15.00
      200 |   1 |           | F           | 1000.00
      300 |   2 |           | M           |  -50.00
      500 |   2 |           | M           |  200.00
      400 |   3 |           | F           |  100.00
(5 rows)

Vertica会自动填充DEFAULT列orderFact.cust_gender，但SET USING列orderFact.cust_name仍然存在NULL。要填充此列，必须在扁平表orderFact上调用函数REFRESH_COLUMNS。此函数调用orderFact.cust_name列的SET USING查询并从结果集中填充列：

=> SELECT REFRESH_COLUMNS('orderFact', 'cust_name', 'REBUILD');
 
      REFRESH_COLUMNS
---------------------------
refresh_columns completed
(1 row)

=> COMMIT;
COMMIT
=> SELECT * FROM orderFact ORDER BY cid;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      100 |   1 | Alice     | F           |   15.00
      200 |   1 | Alice     | F           | 1000.00
      300 |   2 | Boz       | M           |  -50.00
      500 |   2 | Boz       | M           |  200.00
      400 |   3 | Eva       | F           |  100.00
(5 rows)

关于REFRESH_COLUMNS函数

刷新使用SET USING约束定义的表列。与REFRESH_COLUMNS操作关联的所有刷新操作都属于同一事务。REFRESH_COLUMNS 必须刷新指定的所有表和列，否则，整个操作将回滚。

语法：

REFRESH_COLUMNS ( 'tables', '[columns]' [, '[refresh‑mode]' ] )

对于参数tables，可以指定一个或多个表，当指定多个表时，刷新模式必须指定为REBUILD。
对于参数columns，可以指定一个或多个列，或者某个表的所有列。

SELECT REFRESH_COLUMNS ('t1, t2', 't1.a, t2.b', 'REBUILD');
SELECT REFRESH_COLUMNS ('t1, t2', 't1.*, t2.b', 'REBUILD');

若为’ '(空字符串)，则表示刷新指定表中的所有SET USING/ DEFAULT USING列。
对于参数refresh‑mode：

UPDATE：将原始行标记为已删除，并将其替换为新行。要保存这些更新，您必须发出COMMIT声明。
REBUILD：替换指定列中的所有数据。重建操作是自动提交的。

如果设置为空字符串或省略，REFRESH_COLUMNS则以UPDATE模式执行。如果指定多个表，则必须显式指定REBUILD模式。

UPDATE模式与REBUILD模式的比较

通常，当SET USING列数据的更改行数相对较少时，使用UPDATE是一个更好的选择。
当SET USING 列数据有大量是是陈旧的，必须进行更新，使用REBUILD通常是更好的做法。建议在设置任何新的SET USING列时，调用REFRESH_COLUMNS函数并使用REBUILD模式。例如，在通过ALTER TABLE…ADD COLUMN添加列后进行刷新。

REBUILD模式的一些限制

如果你调用REFRESH_COLUMNS 并指定刷新模式REBUILD去刷新一个SET USING列，如果该列满足任何下列条件，则返回一个错误：

指定为表分区键。
包含在实时聚合projections或带有表达式的projections中。
包含在projections的排序顺序或分段中。
包含在projections中，并且projections省略了列SET USING表达式中引用的锚表列。
包含在projections的GROUPED条款中。

SET USING 与 DEFAULT 的对比

扁平表中的具有SET USING和DEFAULT约束的列可以查询其他表。在这两种情况下，查询表中的更改都不会自动传播到扁平表。这两个约束条件有以下不同：

DEFAULT

只有在通过加载操作（如INSERT和COPY）将新行添加到扁平表时，Vertica才会对新行执行DEFAULT查询。此后，原始数据源的更改（如delete和update）对扁平表没有影响（此时，对于default列，要手动调用update…set…default…，可见下文中的例子）。

SET USING

只有在调用函数REFRESH_COLUMNS时，Vertica才会执行SET USING查询。
加载操作将SET USING中的列设置为NULL，加载后，必须调用REFRESH_COLUMNS以从查询的表中填充这些列。这在两个方面很有用：您可以将更新扁平表的开销推迟到方便的任何时间，以及您可以重复查询源表以获取新数据。

一个例外：当您使用ALTER TABLE … ALTER COLUMN将SET USING应用于现有列，或修改现有的SET USING表达式时。在这种情况下，DDL操作使用UPDATE模式自动调用列上的REFRESH_COLUMNS。刷新操作完成后，DDL操作会自动提交更新并返回。如果刷新操作失败，Vertica将回滚整个DDL操作。如果刷新操作涉及大数据集，则执行时间可能很长。

提示：删除列并添加新列可能更有效。当您向表中添加SET USING列时，ALTER TABLE …ADD COLUMN不会调用REFRESH_COLUMNS。

SET USING对于引用来自多个维度表的数据的大型扁平表特别有用。通常，只有一小部分SET USING列可能会发生变化，而对扁平表的查询并不总是需要最新的数据。在这种情况下，您可以定期刷新表格内容，或仅在非高峰时段刷新表格内容。这些策略中的一个或两个可以最小化维护成本，并在查询大型数据集时提升性能。

实例比较default与set using

用前面例子中的表，现在两张表的内容为：

  => SELECT * FROM custDim;
     cid | name  | age | gender
    -----+-------+-----+--------
       1 | Alice |  25 | F
       2 | Boz   |  30 | M
       3 | Eva   |  32 | F
    (3 rows)
=> SELECT * FROM orderFact ORDER BY cid;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      100 |   1 | Alice     | F           |   15.00
      200 |   1 | Alice     | F           | 1000.00
      300 |   2 | Boz       | M           |  -50.00
      500 |   2 | Boz       | M           |  200.00
      400 |   3 | Eva       | F           |  100.00
(5 rows)

操作1，修改表custDim

=> UPDATE custDim SET name='Roz', gender='F' WHERE cid=2;
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT

操作2，查看两张表中的内容，发现表orderFact，无论是default约束的列还是set using约束的列，都没有刷新。

=> SELECT * FROM custDim ORDER BY cid;
 cid | name  | age | gender
-----+-------+-----+--------
   1 | Alice |  25 | F
   2 | Roz   |  30 | F
   3 | Eva   |  32 | F
(3 rows)

=> SELECT * FROM orderFact ORDER BY cid;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      100 |   1 | Alice     | F           |   15.00
      200 |   1 | Alice     | F           | 1000.00
      300 |   2 | Boz       | M           |  -50.00
      500 |   2 | Boz       | M           |  200.00
      400 |   3 | Eva       | F           |  100.00
(5 rows)

操作3，向表orderFact中插入一条数据，发现orderFact中的default列自动更新。

=> INSERT INTO orderFact(order_id, cid, amount)  VALUES(500, 3, 750);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=> SELECT * FROM orderFact ORDER BY cid;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      100 |   1 | Alice     | F           |   15.00
      200 |   1 | Alice     | F           | 1000.00
      300 |   2 | Boz       | M           |  -50.00
      500 |   2 | Boz       | M           |  200.00
      400 |   3 | Eva       | F           |  100.00
      500 |   3 |           | F           |  750.00
(6 rows)

操作4，使用REFRESH_COLUMNS对表orderFact进行刷新。

=> SELECT REFRESH_COLUMNS ('orderFact','');
      REFRESH_COLUMNS 
---------------------------
 refresh_columns completed
(1 row)

=> COMMIT;
COMMIT
=> SELECT * from orderFact ORDER BY cid;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      100 |   1 | Alice     | F           |  150.00
      200 |   1 | Alice     | F           | 1000.00
      300 |   2 | Roz       | M           |  -50.00
      500 |   2 | Roz       | M           |  200.00
      400 |   3 | Eva       | F           |  100.00
      500 |   3 | Eva       | F           |  750.00
(6 rows)

发现REFRESH_COLUMNS仅影响列cust_name中的值。列cust_gender中的值未更改，因此cid = 2（Roz）行的设置保持设置为M。要使用custDim.gender的默认值重新填充orderFact.cust_gender，需在orderFact上调用UPDATE：

=> UPDATE orderFact SET cust_gender=DEFAULT WHERE cust_name='Roz';
 OUTPUT
--------
      2
(1 row)

=> COMMIT;
COMMIT
=> SELECT * FROM orderFact ORDER BY cid;
 order_id | cid | cust_name | cust_gender | amount
----------+-----+-----------+-------------+---------
      100 |   1 | Alice     | F           |   15.00
      200 |   1 | Alice     | F           | 1000.00
      300 |   2 | Roz       | F           |  -50.00
      500 |   2 | Roz       | F           |  200.00
      400 |   3 | Eva       | F           |  100.00
      500 |   3 | Eva       | F           |  750.00
(6 rows)

修改SET USING和DEFAULT列

删除SET USING和DEFAULT约束

ALTER TABLE table-name ALTER COLUMN column-name DROP {SET USING | DEFAULT};

修改SET USING和DEFAULT表达式

您可以通过调用更改现有SET USING或DEFAULT列的查询表达式ALTER TABLE...ALTER COLUMN。修改SET USING列表达式时，Vertica会自动调用REFRESH_COLUMNS使用UPDATE模式，并重新填充该列。如果必须刷新大量行，则以下方法可能更可取：

1.删除列ALTER TABLE…DROP COLUMN。
2.调用ALTER TABLE…ADD COLUMN添加具有所需SET USING表达式的新表达式。

这种方法有两个好处：

避免立即调用REFRESH_COLUMNS，并推迟原本发生的开销。
允许您在调用时REFRESH_COLUMNS指定模式REBUILD ，这在许多情况下会更有效地重新填充列。

删除由SET USING或DEFAULT查询的列

Vertica强制执行扁平表与其查询的表之间的依赖关系。除非删除操作（如DROP TABLE）时使用CASCADE关键字，否则尝试删除查询的列或其表将返回错误。 Vertica通过从扁平表中删除SET USING或DEFAULT约束来实现CASCADE。表列及其数据不受影响。

参考：
vertica官方文档9.1.x—>Analyzing Data—>Flattened Tables
链接如下：
https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm%3FTocPath%3DAnalyzing%2520Data%7CFlattened%2520Tables%7C_____0

hmxz1024

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
flattened table

flattened table(扁平表)高度规范化的数据库设计通常使用星型或雪花型架构模型，包括多个大型事实表和许多较小的维度表。查询通常涉及大事实表和多个维表之间的连接。根据表的数量和加入的数据量，这些查询会产生很大的开销。为避免此问题，某些用户创建了宽表，这些表组合了查询所需的所有事实和维度表列，这些表可以显着加快查询执行速度。但是，维护冗余的规范化和非规范化数据集有其自己的管理成本。非...
复制链接

扫一扫