Snova运维篇（十）：gp数据库中数据操作-2

本节继续前篇数据操作的内容进一步学习相关运维工作。

目录：

定义数据库对象
管理数据

基本概念：


视图	在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，我们也可以提交数据，就像这些来自于某个单一的表。

1.定义数据库对象：

（一）对大型表分区

Greenplum数据库支持：

范围分区：基于一个数字型范围划分数据，例如按照日期或价格划分。
列表分区：基于一个值列表划分数据，例如按照销售范围或产品线划分。
两种类型的组合。

创建分区表
定义日期范围分区表

代码语言：javascript复制

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') );

逐个声明定义：

代码语言：javascript复制

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 );

定义数字范围分区

代码语言：javascript复制

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 );

列表表分区

代码语言：javascript复制

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 );

代码语言：javascript复制

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)

定义多级分区

代码语言：javascript复制

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 );

验证分区表

代码语言：javascript复制

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)

查看分区设计

代码语言：javascript复制

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

维护分区表

（二）创建和使用一个序列

创建一个序列

代码语言：javascript复制

CREATE SEQUENCE myserial START 101;

使用序列

代码语言：javascript复制

INSERT INTO vendors VALUES (nextval('myserial'), 'acme');

重置序列

代码语言：javascript复制

SELECT setval('myserial', 201);

代码语言：javascript复制

komablog=# SELECT setval('myserial', 201);
 setval
--------
    201
(1 row)

代码语言：javascript复制


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)

修改一个序列

代码语言：javascript复制

                                              ^
komablog=# alter  sequence myserial restart with 105;
ALTER SEQUENCE

删除一个序列

代码语言：javascript复制

DROP SEQUENCE myserial;

（三）gp中索引的使用

中间表索引

代码语言：javascript复制

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索引

创建索引

代码语言：javascript复制

CREATE INDEX gender_idx ON employee (gender);

代码语言：javascript复制

CREATE INDEX title_bmp_id x ON films USING bitmap (title);

重建索引

代码语言：javascript复制

REINDEX my_table;

代码语言：javascript复制

DROP INDEX title_idx;

在载入数据时，删除所有索引、载入数据然后重建索引会更快。

（四）创建和管理视图

创建视图

代码语言：javascript复制

CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'comedy';

删除视图

代码语言：javascript复制

DROP VIEW topten;  DROP VIEW ... CASCADE命令也可以移除所有依赖的对象。

2.管理数据

插入行

代码语言：javascript复制

INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

代码语言：javascript复制

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

更新数据

代码语言：javascript复制

UPDATE products SET price = 10 WHERE price = 5;

删除行

代码语言：javascript复制

DELETE FROM products WHERE price = 10;

代码语言：javascript复制

DELETE FROM products;

清空表

代码语言：javascript复制

TRUNCATE mytable;

清理过期数据

代码语言：javascript复制

VACUUM mytable;

用下列服务器配置参数调整空闲空间映射的尺寸：
max_fsm_pages
max_fsm_relations

未完待续；

云数据仓库PostgreSQL

0 人点赞