专栏首页Snova最佳实践系列Snova运维篇(十):gp数据库中数据操作-2
原创

Snova运维篇(十):gp数据库中数据操作-2

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

目录:

  1. 定义数据库对象
  2. 管理数据


基本概念:

视图

在 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

未完待续;

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

相关文章

  • Snova基础篇(一):生产环境搭建及运行测试数据

    地址: https://buy.cloud.tencent.com/snova#/?region=ap-guangzhou

    snova-最佳实践
  • Snova运维篇(八):GP数据库访问管理及认证

    Master实例的pg_hba.conf文件控制对Greenplum数据库系统的客户端访问及认证。

    snova-最佳实践
  • Snova基础篇(三):Greenplum集群初始化问题及调试

    gpinitsystem 的日志文件。默认路径为 ~/gpAdmin/gpinitsystem_***

    snova-最佳实践
  • 如何从0到1运营一个公众号

    相信越来越多的朋友会发现自己身边的人开通了个人公众号,有的甚至在学生时代就通过公众号实现了经济独立。然而运营好一个公众号,尤其是一个有变现能力的公众号不是那么...

    腾讯NEXT学位
  • LeetCode 112&113&437 Path Sum I&II&III

    Given a binary tree and a sum, determine if the tree has a root-to-leaf path suc...

    大学里的混子
  • TiDB 源码阅读系列文章(五)TiDB SQL Parser 的实现

    PingCAP 发布了 TiDB 的源码阅读系列文章,让我们可以比较系统的去学习了解TiDB的内部实现。最近的一篇《SQL 的一生》,从整体上讲解了一条 SQL...

    PingCAP
  • 问答 | 如何理解 NVIDIA 新 GPU 架构 Turing 的 Tensor Core?

    问:如何理解 NVIDIA 新 GPU 架构 Turing 的 Tensor Core?

    AI研习社
  • Find Mode in Binary Search Tree

    Tyan
  • 2.14 文件和目录权限chmod

    文件属性 [root@localhost ~]# ls -l 总用量 12 -rw-r--r--. 1 root root 0 10月 25 16:06 ...

    运维小白
  • Path Sum

    问:二叉树是否存在路径和等于sum的路径,若存在输出true,否则输出false 分析:递归调用二叉树,每次将上一层的val值传递给子结点并加上子节点的val,...

    用户1624346

扫码关注云+社区

领取腾讯云代金券