Snova运维篇（十）：gp数据库中数据操作-2
- 2019 年 12 月 31 日
- 筆記
本节继续前篇数据操作的内容进一步学习相关运维工作。
目录：
- 定义数据库对象
- 管理数据
基本概念：
|
|
|
|---|---|
|
视图 |
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。 |
1.定义数据库对象：
（一）对大型表分区
- Greenplum数据库支持：
范围分区：基于一个数字型范围划分数据，例如按照日期或价格划分。
列表分区：基于一个值列表划分数据，例如按照销售范围或产品线划分。
两种类型的组合。
- 创建分区表
- 定义日期范围分区表
komablog=# CREATE TABLE sales (id int, date date, amt decimal(10,2))DISTRIBUTED BY (id)PARTITION BY RANGE (date)( START (date '2016-01-01') INCLUSIVE END (date '2017-01-01') EXCLUSIVE EVERY (INTERVAL '1 day') );
逐个声明定义：
CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE (date) ( PARTITION Jan16 START (date '2016-01-01') INCLUSIVE , PARTITION Feb16 START (date '2016-02-01') INCLUSIVE , PARTITION Mar16 START (date '2016-03-01') INCLUSIVE , PARTITION Apr16 START (date '2016-04-01') INCLUSIVE , PARTITION May16 START (date '2016-05-01') INCLUSIVE , PARTITION Jun16 START (date '2016-06-01') INCLUSIVE , PARTITION Jul16 START (date '2016-07-01') INCLUSIVE , PARTITION Aug16 START (date '2016-08-01') INCLUSIVE , PARTITION Sep16 START (date '2016-09-01') INCLUSIVE , PARTITION Oct16 START (date '2016-10-01') INCLUSIVE , PARTITION Nov16 START (date '2016-11-01') INCLUSIVE , PARTITION Dec16 START (date '2016-12-01') INCLUSIVE END (date '2017-01-01') EXCLUSIVE );
- 定义数字范围分区
CREATE TABLE rank (id int, rank int, year int, gender char(1), count int) DISTRIBUTED BY (id) PARTITION BY RANGE (year) ( START (2006) END (2016) EVERY (1), DEFAULT PARTITION extra );
- 列表表分区
komablog=# CREATE TABLE rank (id int, rank int, year int, genderchar(1), count int )DISTRIBUTED BY (id)PARTITION BY LIST (gender)( PARTITION girls VALUES ('F'), PARTITION boys VALUES ('M'), DEFAULT PARTITION other );
komablog=# d+ rank Table "public.rank" Column | Type | Modifiers | Storage | Description --------+--------------+-----------+----------+------------- id | integer | | plain | rank | integer | | plain | year | integer | | plain | gender | character(1) | | extended | count | integer | | plain | Child tables: rank_1_prt_boys, rank_1_prt_girls, rank_1_prt_other Has OIDs: no Distributed by: (id) Partition by: (gender)
- 定义多级分区
CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text) DISTRIBUTED BY (trans_id) PARTITION BY RANGE (date) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'), DEFAULT SUBPARTITION other_regions) (START (date '2011-01-01') INCLUSIVE END (date '2012-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION outlying_dates );
- 验证分区表
komablog=# EXPLAIN SELECT * FROM sales WHERE date='01-07-12' AND komablog-# region='usa'; QUERY PLAN ----------------------------------------------------------------------------------------------------- Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..431.00 rows=1 width=24) -> Sequence (cost=0.00..431.00 rows=1 width=24) -> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=50 width=4) Partitions selected: 1 (out of 52) -> Dynamic Table Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24) Filter: date = '2012-01-07'::date AND region = 'usa'::text Optimizer status: PQO version 3.59.0 (7 rows)
- 查看分区设计
komablog=# SELECT partitionboundary, partitiontablename, partitionname, komablog-# partitionlevel, partitionrank komablog-# FROM pg_partitions komablog-# WHERE tablename='sales'; partitionboundary | partitiontablename | partitionname | partitionlevel | partitionrank -------------------------------------------------------------------------------+------------------------------------------------+ ----------------+----------------+--------------- DEFAULT PARTITION outlying_dates | sales_1_prt_outlying_dates | outlying_dates | 0 | START ('2011-01-01'::date) END ('2011-02-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_2 | | 0 | 1 START ('2011-02-01'::date) END ('2011-03-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_3 | | 0 | 2 START ('2011-03-01'::date) END ('2011-04-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_4 | | 0 | 3 START ('2011-04-01'::date) END ('2011-05-01'::date) EVERY ('1 mon'::interval) | sales_1_prt_5
- 维护分区表
（二）创建和使用一个序列
- 创建一个序列
CREATE SEQUENCE myserial START 101;
- 使用序列
INSERT INTO vendors VALUES (nextval('myserial'), 'acme');
- 重置序列
SELECT setval('myserial', 201);
komablog=# SELECT setval('myserial', 201); setval -------- 201 (1 row)
komablog=# SELECT * FROM myserial; sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called ---------------+------------+--------------+---------------------+-----------+-------------+---------+-----------+----------- myserial | 201 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t (1 row)
- 修改一个序列
^ komablog=# alter sequence myserial restart with 105; ALTER SEQUENCE
- 删除一个序列
DROP SEQUENCE myserial;
（三）gp中索引的使用
- 中间表索引
CREATE TABLE new_table (LIKE old_table) AS SELECT * FROM old_table ORDER BY myixcolumn; DROP old_table; ALTER TABLE new_table RENAME TO old_table; CREATE INDEX myixcolumn_ix ON old_table; VACUUM ANALYZE old_table;
- 索引类型
Greenplum数据库数据库支持Postgres索引类型B-树和GiST，不支持Hash和GIN索引
- 创建索引
CREATE INDEX gender_idx ON employee (gender);
CREATE INDEX title_bmp_id x ON films USING bitmap (title);
- 重建索引
REINDEX my_table;
DROP INDEX title_idx;
在载入数据时，删除所有索引、载入数据然后重建索引会更快。
（四）创建和管理视图
- 创建视图
CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'comedy';
- 删除视图
DROP VIEW topten; DROP VIEW ... CASCADE命令也可以移除所有依赖的对象。
2.管理数据
- 插入行
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99), (2, 'Bread', 1.99), (3, 'Milk', 2.99);
- 更新数据
UPDATE products SET price = 10 WHERE price = 5;
- 删除行
DELETE FROM products WHERE price = 10;
DELETE FROM products;
- 清空表
TRUNCATE mytable;
- 清理过期数据
VACUUM mytable;
用下列服务器配置参数调整空闲空间映射的尺寸：
max_fsm_pages
max_fsm_relations
未完待续；
