Greenplum数据库基础培训new教程.ppt
文本预览下载声明
Greenplum 数据库基础培训;Greenplum数据库;S1002 Network Configuration;Greenplum 基本体系架构;客户端接口和程序;Master Host; 每段(Segment)存放一部分用户数据
一个系统可以有多段
用户不能直接存取访问
所有对段的访问都经过Master
数据库监听进程(postgres)监听来自Master的连接; Greenplum数据库之间的连接层
进程间协调和管理
基于千兆以太网架构
属于系统内部私网配置
支持两种协议:TCP or UDP;Greenplum 高可用性体系架构;Standby 节点用于当 Master 节点损坏时提供 Master 服务
Standby 实时与 Master 节点的 Catalog 和事务日志保持同步;每个Segment的数据冗余存放在另一个Segment上,数据实时同步
当Primary Segment失败时,Mirror Segment将自动提供服务
Primary Segment恢复正常后,使用gprecoverseg –F 同步数据。; Hash分布
CREATE TABLE … DISTRIBUTED BY (column [,…])
同样数值的内容被分配到同一个Segment上
循环分布
CREATE TABLE … DISTRIBUTED RANDOMLY
具有同样数值的行内容并不一定在同一个Segment上;查询命令的执行;查询命令的执行;SQL查询处理机制;SELECT customer, amount FROM sales JOIN customer USING (cust_id) WHERE date;表分区的概念;Segment 1A
;;表分区示意图;压缩存储;行列存储;外部表加载;基于外部表的高速数据加载;外部表加载的特征;Load good rows and catch poorly formatted rows, such as:
rows with missing or extra attributes
rows with attributes of the wrong data type
rows with invalid client encoding sequences
Does not apply to constraint errors:
PRIMARY KEY, NOT NULL, CHECK or UNIQUE constraints
Optional error handling clause for external tables:
[LOG ERRORS INTO error_table] SEGMENT REJECT LIMIT count [ROWS | PERCENT]
( PERCENT based on gp_reject_percent_threshold parameter )
Example
CREATE EXTERNAL TABLE ext_customer
(id int, name text, sponsor text)
LOCATION ( gpfdist://filehost:8081/*.txt )
FORMAT TEXT ( DELIMITER | NULL )
LOG ERRORS INTO err_customer SEGMENT REJECT LIMIT 5 ROWS; ; Data resides outside the database
No database statistics for external table data
Not meant for frequent or ad-hoc access
Can manually set rough statistics in pg_class:
UPDATE pg_class SET reltuples=400000, relpages=400 WHERE relname=myexttable;
; PostgreSQL command
Support loading and unloading
Optimized for loading a large number of rows
Loads all rows in one command (not parallel)
Loads data from a file or from standard input
Supports error handling as does external tables
EX
显示全部