4.1.2.1.1-Greenplum-调研
Greenplum数据库软件是业内首创的大规模并行处理（massively parallel processing (MPP)）的数据库软件产品，它包含大规模并行计算技术和数据库技术最新的研发成果：包括无共享/MPP，行列存储数据库，数据库内压缩，MapReduce，永不停机扩容，多级容错等等。该软件产品被业界认可为扩展能力最大的分析型（OLAP）数据库软件。已有100多家世界级重大客户采用该软件，例如：NYSE,NASDAQ,AIG，德意志银行，美国联邦储备委员会，支付宝，淘宝，NTT-DoCoMo,T-Mobile,Skype，WalMart，中国电信等。Greenplum现有客户涵盖：金融，电信，互联网，零售，运输，医药，政府等行业。这些客户中大多数Greenplum数据仓库所管理的数据量都超过100TB，其中，全球最大的有6500TB，中国最大的有400TB。
1.1 无共享/MPP核心架构
Greenplum数据库软件将数据平均分布到系统的所有节点服务器上，所以节点存储每张表或表分区的部分行，所有数据加载和查询都是自动在各个节点服务器上并行运行，并且该架构支持扩展到上万个节点。
1.2 混合的存储和执行
Greenplum发明支持混合按列或按行存储数据，每张表或表分区可以由管理员根据应用需要，分别指定存储和压缩方式。 基于这个功能，用户可以对任何表或表分区选择按行或按列存储数据和处理方式。这些是在建表或表分区的DDL语句中配置的，只需在建表或表分区时指定： 这个功能基于Greenplum的多态维数据存储技术。
1.3 高可用性
Greenplum是高可用的系统，在已有案例中最多使用了96台机器的集群MPP环境。除了硬件级的Raid技术外，Greenplum还提供数据库层Mirror机制保护，即每个节点数据在另外的节点中同步镜像，单个节点的错误不影响整个系统的使用。对于主节点，Greenplum提供Master/Stand by机制进行主节点容错，当主节点发生错误时，可以切换到Stand by节点继续服务。
1.4 在线系统扩容
在系统中增加节点服务器即可增加存储容量，处理性能和加载性能。当系统扩展时，数据仓库保持在线，并且完全可用，扩展进程在后台运行。增加节点服务器，性能和容量线性增加。 Greenplum在扩展节点时操作简单，在很短时间内就能完成数据的重新分布。
1.5 负载管理（Workload Management） 和高并发
具有系统资源管控能力，并且可控制给各个查询分配各自系统资源。允许管理员指派资源队列，从而管理数据仓库的队列进入执行情况。在运行的查询的优先级可以随时调整。Greenplum提供资源管理功能(workload managemnt)来管理数据库资源，利用资源队列管理可实现按用户组的进行资源分配，如Session同时激活数、最大资源值等。通过资源管理功能，可以按用户级别进行资源分配和管理用户SQL查询优先级别，同时也能防止低质量SQL(如没有条件的多表join等)对系统资源的消耗。
1.7 海量数据存储
Greenplum支持50PB(1PB=1000TB)级海量数据的存储和处理，Greenplum将来自不同源系统的、不同部门、不同平台的数据集成到数据库中集中存放，并且存放详尽历史的数据轨迹，业务用户不用再面对一个又一个信息孤岛，也不再困惑于不同版本数据导致的偏差，同时对于IT人员也降低管理维护工作的复杂度。
1.8 PB级的装载能力
基于MPP Scatter/Gather 流技术的高性能并行加载功能。加载速度随着节点线性增加，实际超过4TB/小时。
1.9 灵活的外部数据访问
数据仓库软件可在任意外部数据源上并行运行常规SQL，不论外部数据源的位置，格式或存储介质。
1.10 数据库内压缩
利用业界领先的压缩技术，进一步提高性能，并极大地节省了数据存储空间。用户可获得3-10倍的空间节省，并且同时获得相应有效I/O性能提升。
1.11 多层次表分区能力
允许灵活地按照时间、范围、值域划分表分区。表分区由DDL设定，分区层级不限。数据仓库软件的查询优化器自动从查询执行计划中略去不涉及的表分区。
1.12 索引功能
Greenplum 支持各种数据库索引技术，包括B-Tree，Bitmap等等。按列存储、按行存储数据库表都支持索引。
1.13 完全遵从SQL最新标准
遵从SQL-92 ， SQL-99 ，至SQL 2003标准，并包括SQL 2003 OLAP扩展项。所有SQL查询都是在系统上并行执行。对SQL语言包括其OLAP扩展标准，都是在Greenplum数据仓库软件实现并行执行。全面支持SQL 2003 OLAP标准，包括Window 函数，Rollup，Cube等等。
1.14 原生MapReduce功能
MapReduce由Google发明，已被证实为一个高扩展性的文本非结构化数据分析的技术。Greenplum的并行数据库软件核心可原生运行MapReduce程序。
1.15 客户端访问及第三方工具支持
完全支持数据库技术接口标准，例如: SQL, ODBC, JDBC, OLEDB等。同时，广泛地支持各个BI和ETL软件工具。
1.16 Greenplum 性能监控器
一个Web GUI的Greenplum数据仓库系统运行状态监控工具，可以监控操作系统级信息以及数据库软件SQL运行状态细节。它的仪表盘可以查看，在SQL运行时，数据仓库系统资源利用情况。并且，还可以深入显示当前或历史的SQL运行细节，帮助更深入理解某一SQL性能状况。
2. 重要概念
2.1分布式的数据表
2.2表分布方式
1）Hash分布 CREATE TABLE … DISTRIBUTED BY (column [,…]) 同样内容的键值被分配到同一个Segment上 2）循环分布 CREATE TABLE … DISTRIBUTED RANDOMLY 具有同样数值的行内容并不一定在同一个Segment上 2.3并行查询的实现方式 1）计划和调度一个查询
2）并行查询计划
例、SELECT customer, amount FROM sales JOIN customer USING (cust_id) WHERE date=04302008 3）查询工作的进程
3. 架构
3.1 客户端程序
1）psql 2）pgAdmin III 3)其他:ODBC、JDBC、Perl DBI、Python、libpq 5.1.1 PSQL客户端连接 5.1.1.1通过Master连接数据库系统 1）连接信息 – 数据库名称(-d | PGDATABASE) – master主机名称 (-h | PGHOST) – master 端口(-p | PGPORT) – 用户名 (-U | PGUSER) 2)第一次连接 默认的template1数据库 默认的superuser (gpadmin)
3.1.1.2执行SQL语句的方式
1)交互方式 psql mydatabase mydatabase=# SELECT FROM table; 2）非交互方式 (单行命令) psql mydatabase –c “SELECT FROM table;” psql mydatabase –c “SELECT * FROM table;” 3）非交互方式 (多行命令) psql mydatabase –f /home/lab1/sql/createdb.sql 注：每个SQL语句的结束需要使用分号(;) 来表示。
5.1.1.3 SQL基本使用命令 |命令|功能| |------|------| |\?|psql查询命令帮助| |\h|SQL命令语法帮助 |\dt|显示表| |\dtS|显示系统表
|\dg or \du|显示角色| |\l|显示数据库| |\c db_name|连接到这个数据库| |\q|退出psql|
5.1.2图形界面的客户端 – gpAdmin III 1)基本功能 使用pgAdmin III查看图形化的查询计划 5.2 Master主机 1）访问系统的入口 2）数据库侦听进程 (postgres) 3）处理所有用户连接 4）建立查询计划
5）协调工作处理过程 6）管理工具 7）系统目录表和元数据（数据字典） 8）不存放任何用用户数据
5.3 Segments主机 1）每个Segment上存放一部分用户数据 2）一台Segment主机可以有多个实例 3）通过增加Segment主机实现线性扩展 4）用户不能直接存取访问 5）所有对段的访问都经过Master 6）数据库监听进程(postgres)来自Master的连接
5.4 Interconnect 1）Greenplum数据库之间的连接层 2）进程间协调和管理 3）基于千兆以太网架构 4）属于系统内部私网配置 5）支持两种协议：TCP or UDP
4. Sql 语句支持
6、对sql语句的支持 1）Greenplum数据库对SQL的支持 2）Greenplum支持SQL-2003标准的绝大部分功能 3）Greenplum的目标是完全支持PostgreSQL中所有SQL命令 4）DDL命令有部分功能不支持(triggers, foreign keys等) 5）支持所有的DML/DQL 命令 (有某些小的限制条件） 6）Greenplum在通用PostgreSQL基础上有针对DW/BI的增强SQL功能 (Parallelism, OLAP, External Tables, Workload Management)
6.1 DDL 6.1.1数据库 6.1.1.1数据库相关SQL命令 1）创建: CREATE DATABASE or created 2）删除: DROP DATABASE or dropdb 3）编辑: ALTER DATABASE
6.1.1.2 PSQL连接数据库的提示 1）PSQL 界面会显示当前连接的数据库 例子: template1=# (超级用户) names=> (非超级用户) 2）显示所有数据库列表:\l 3）连接到另一个数据库:\c db_name 4）使用PGDATABASE环境变量来设置默认的数据库
6.1.2 Schemas 6.1.2.1关于Schemas 1）Schemas是在一个数据库中将对象逻辑上组织的方式（表，函数等） 2）使用带有schema的“完整的”名称来读取数据库对象 例如: myschema.mytable 3）对于只有对象名称的内容，通过schema的搜索路径来决定默认的schema (search_path) 4）每个数据库有名称为public的schema 5）其它系统级别的schemas: pg_catalog, information_schema, pg_toast, pg_bitmap 6.1.2.2 Schema相关SQL命令 1）创建: CREATE SCHEMA 2）删除: DROP SCHEMA 3）编辑: ALTER SCHEMA 6.1.2.3 PSQL中Schema相关提示 1）查看当前的schema:SELECT current_schema(); 2）查看数据库中所有schema列表:\dn 3）查看schema的搜索路径:SHOW search_path; 4）设置某个数据库的搜索路径:ALTER DATABASE SET search_path TO myschema, public, pg_catalog
6.1.3表 6.1.3.1关于表 1）Greenplum数据库的所有表都会根据分布键分布到每个Segment实例上 2）3.3版本以上可以通过update table修改表的分布键 3）某些表功能目前在Greenplum数据库中不支持 a）外键约束（Foreign key) b）唯一约束的限制 (Unique constraints)
6.1.3.2关于表分布键 1）使用一列或多列内容来分隔数据到各segment上 2）为确保数据分布均衡，最好是唯一的。 3）在CREATE TABLE是使用DISTRIBUTED BY语句来定义分布键 例：CREATE TABLE sales (dt date, prc float, qty int, cust_id int, prod_id int, vend_id int) DISTRIBUTED BY (dt, cust_id, prod_id); 4）如果表中没有唯一值的列，可以定义DISTRIBUTED RANDOMLY。 5）如果没有明确定义，默认表的主键或表的第一列作为分布键 默认表的主键或表的第一列作为分布键 6）Greenplum分布键设置类型 a）不能使用几何数据类型（如box, circle, line, 等 等） b）不能使用用户自定义数据类
6.1.3.3关于数据类型 1）描述表列所包含数据的类型 2）Greenplum支持与PostgreSQL相同的数据类型 3）Greenplum同样支持SQL标准定义的所有数据类型 4）常用的数据类型 a)字符类型：char,varchar,text; b)整数类型：smallint,integer,bigint; c)浮点类型：real,double precision;
d)任意精度类型：numeric e)日期/时间类型：timestamp,date,time; f)布尔类型：Boolean; g)array类型：integer[]。 6.1.3.4表和列的约束条件 1）CHECK 表或列的约束 2）NOT NULL 列约束条件 3）UNIQUE 列约束条件 a）每个表只能有一个 b）Unique定义的列会作为表分布键 c）如果表设置了主键,则不允许有Unique约束 d）PRIMARY KEY 表约束条件，默认会作为表分布键。 e）FOREIGN KEY 约束目前不支持 6.1.3.5表相关的SQL命令 1）创建: CREATE TABLE 2）编辑: ALTER TABLE 3）删除: DROP TABLE 6.1.3.6表相关提示PSQL 提示 1）列出数据库的所有表:\dt 2） 查看表的结构:\d+ table_name 3）列出所有系统目录表:\dtS 4）查看表的分布键:gpskew -t table_name –c
6.1.4视图(Views) 6.1.4.1关于视图 1）将频繁使用的查询定义为视图 2）视图在Greenplum数据库中不是实体
6.1.4.2视图相关SQL命令 1）CREATE VIEW 2）DROP VIEW 例：CREATE VIEW topten AS SELECT name, rank, gender, year FROM names, rank WHERE rank < ’11’ AND names.id=rank.id; SELECT * FROM topten ORDER BY year, rank;
6.1.4.3 PSQL提示 1）显示所有的视图：\dv 2）查看一个视图的定义：\d+ view_name
6.1.5索引(Indexes) 6.1.5.1关于索引 1）索引在Greenplum数据库中保守使用 2）索引在查询运行中并不是都有帮助 3）需要测试索引是否能有效提高性能 4）不使用的情况下应删除索引 5）PRIMARY KEY索引会自动创建 6）Unique索引只允许在Greenplum分布键所在的列上 7）索引的类型：B-tree，Bitmap*，R-tree，Hash，GiST。
6.1.5.2 索引相关的SQL命令 1）CREATE INDEX 2）ALTER INDEX 3） DROP INDEX 4） *CLUSTER 5） REINDEX
6.1.5.3 PSQL提示 1）显示所有的索引：\di 2）查看一个索引的定义：\d+ index_name
6.1.6序列(Sequences) 6.1.6.1关于序列 1）Greenplum Master序列生成器进程 (seqserver)
6.1.6.2序列相关的SQL命令 1）CREATE SEQUENCE 2）ALTER SEQUENCE 3）DROP SEQUENCE
6.1.6.3序列有关函数的限制 1）不支持lastval和currval 2）查询中更新数据不能使用setval 3）如果Mirrors启用时，nextval在更新和删除命令中是不允许的 4）nextval在某些查询中会获取一批数值
6.1.6.4 PSQL提示 1）显示所有的序列：\ds 2）查看一个序列的定义：\d+ sequence_name
6.1.7触发器(Triggers) 数据库在某些特定操作执行时，会自动触发并执行一些特殊函数。
6.1.8表空间(Tablespaces) 在文件系统中可设置的存储单元，保存数据库、表 、索引等数据。 6.2 DML 6.2.1插入数据 INSERT 命令 实例：INSERT INTO names VALUES (nextval('names_seq'), 'test', 'U'); 如果有多行数据需要插入，考虑使用“INSERT INTO
6.2.2更新数据 UPDATE 命令 实例: UPDATE names SET name='Emily' WHERE name='Emmmily';
6.2.3删除数据 1）DELETE命令 实例: DELETE FROM rank; (删除所有行) DELETE FROM rank WHERE year='2001'; 2）TRUNCATE命令 实例：TRUNCATE b2001; (删除指定表的所有行)
6.3 DQL 6.3.1连接 1）交叉连接(T1 CROSS JOIN T2) 对每个来自T1和T2 的行进行组合（也就是，一个笛卡尔积），连接成的表将包含这样的行： 所有T1里面的字段后面跟着所有T2 里面的字段。如果两表分别有 N 和 M 行，连接成的表将有 N*M 行。 FROM T1 CROSS JOIN T2 等效于FROM T1 INNER JOIN T2 ON TRUE(见下文)。 它还等效FROM T1,T2。 2）条件连接 a)INNER JOIN(内连接):对于 T1 中的每一行 R1 ，如果能在 T2 中找到一个或多个满足连接条件的行， 那么这些满足条件的每一行都在)连接表中生成一行。
b)LEFT OUTER JOIN(左外连接):首先执行一次内连接。然后为每一个 T1 中无法在 T2 中找到匹配的行生成一行， 该行中对应 T2 的列用 NULL 补齐。因此，生成的连接表里总是包含来自 T1 里的每一行至少一个副本。
c)RIGHT OUTER JOIN(右外连接):首先执行一次内连接。然后为每一个 T2 中无法在 T1 中找到匹配的行生成一行， 该行中对应 T1 的列用 NULL 补齐。因此，生成的连接表里总是包含来自 T2 里的每一行至少一个副本。
d)FULL OUTER JOIN(全连接):首先执行一次内连接。然后为每一个 T1 与 T2 中找不到匹配的行生成一行， 该行中无法匹配的列用 NULL 补齐。因此，生成的连接表里无条件地包含 T1 和 T2 里的每一行至少一个副本。 e)ON子句是最常见的连接条件的类型：它接收一个和WHERE 子句相同的布尔表达式。如果两个分别来自T1和T2 的行在ON表达式上运算的结果为真，那么它们就算是匹配的行。
f)USING子句是一种速记法，允许你在join两边都使用相同的连接字段名时获取便利。 它接受一个逗号分隔的共享字段名的列表，并生成一个包括每个字段相等比较的连接条件。 例如，用USING (a, b)连接T1和T2， 产生连接条件ON T1.a = T2.a AND T1.b = T2.b。 g)JOIN USING的输出抑制多余的字段： 两个匹配的字段不需要都打印出来，因为它们肯定拥有相等的值。 JOIN ON产生所有来自T1的字段， 后面跟着所有来自T2的字段，而JOIN USING 产生列出的每个字段对（以列出的顺序），跟着T1 中剩余的字段，然后跟着T2中剩余的字段。 h)NATURAL是USING的缩写形式： 它自动形成一个由两个表中同名的字段组成的USING列表(同名字段只出现一次)。 如果没有相同的字段名，NATURAL的行为会像CROSS JOIN。 6.3.1 GROUP BY和HAVING字句 1)GROUP BY用于将一个表中所有列出的字段值都相等的行分成一组。 字段列出的顺序没什么关系。 效果是将每个拥有相同值的行集合并为一组，代表该组中的所有行。 这样就可以删除输出里的重复，和/或计算应用于这些组的聚合。 2)如果一个表已经用GROUP BY分了组， 然后你又只对其中的某些组感兴趣，那么就可以用HAVING子句筛选分组。 必须像WHERE子句，从结果中消除组，语法是：SELECT select_list FROM ... [WHERE ...] GROUP BY ... HAVING boolean_expression
6.3.2窗口处理函数 1）窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数， 使用的窗口函数不会导致行被分组到一个单一的输出行；行保留其独立的身份。 在后台，窗口函数能够访问的不止查询结果的当前行。 例：计算每个员工的工资和在他或她的部门的平均工资： SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
2）还可以使用窗口函数OVER内的ORDER BY来控制行的顺序。 例：SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC)FROM empsalary;
3）当查询涉及多个窗口函数时，可以写成每一个都带有单独的OVER子句， 但是，如果期待为多个窗口函数采用相同的窗口行为，这样做就会产生重复，并且容易出错。 作为代替，每个窗口行为可以在WINDOW子句中进行命名，然后再被OVER引用。 例如：SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
6.3.3 组合查询 并：query1 UNION [ALL] query2 交：query1 INTERSECT [ALL] query2 差：query1 EXCEPT [ALL] query2
6.3.4 LIMIT和OFFSET 1)如果给出了一个 LIMIT 计数，那么将返回不超过该数字的行(也可能更少些， 因为可能查询本身生成的总行数就比较少)。 OFFSET指明在开始返回行之前忽略多少行。
6.3.5 WITH查询 1） WITH中的SELECT: 为了将复杂的查询分解为更简单的部分。 例：WITH regional_sales AS (SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product; 它显示了每个产品仅在销售区域的销售总额。WITH子句定义了两个名为 regional_sales 和 top_regions的辅助语句， regional_sales的输出用于top_regions， 而top_regions的输出用于初级的SELECT查询。 这个例子也可以不用WITH来写，但是需要两级嵌套的子SELECT查询。 用这种方法更容易理解。 2）WITH中的修改语句:可以在WITH中使用数据修改语句(INSERT,UPDATE 或 DELETE)。这允许你在相同的查询中执行几个不同的操作。 例：WITH moved_rows AS (DELETE FROM products WHERE "date" >= '2010-10-01' AND "date" < '2010-11-01' RETURNING ) INSERT INTO products_log SELECT FROM moved_rows; 这个查询有效的移动products中的行到products_log。 WITH中的DELETE从products中删除指定的行， 并且通过RETURNING子句返回它们的内容； 然后初级查询读取那个输出并且插入到products_log中。