................. - 116 - 分区大表.......................................................................................................................... - 116 - 理解 GP 的表分区 .......................... 版权所有：Esena(陈淼 +86 18616691889) 编写：陈淼 - 5 - 决定表是否分区的原则 .......................................................................................... - 118 - 创建分区表 ........................................... ........................... - 119 - 插入数据到分区表 .................................................................................................. - 125 - 验证分区策略 .......................................

从hdfs加载数据 从mysql中导⼊数据 从oracle中导⼊数据 从ufile加载数据 开发指南 开发指南 1、连接数据库 2、数据库管理 3、模式管理 4、表格设计 5、加载数据 6、分区表 7、序列 8、索引 9、 ANALYZE/VACUUM 10、常⽤SQL⼤全 12、常⽤SQL命令 13、⽤⼾⾃定义函数 ⽬录 Greenplum数据仓库 UDW Copyright postgresql，由于 udw 采⽤ mpp 数据，创建表格的时候可以选择不同的数据分布策略，不同的存储⽅式等等。创建表格的时候可以定义下⾯信息： 数据类型 表约束 数据分布策略 表存储模型 分区策略 外部表：udwfile、udwhdfs 下⾯分别根据上⾯的可选信息对表格设计进⾏分析。 4.1 数据类型 数据类型 开发指南 Greenplum数据仓库 UDW Copyright © null、foreign 等约束，主键约束必须使⽤ hash 策略来分布表数据存储，不能在同⼀个表同时使⽤主键和唯 ⼀约束，并且指定了primary 和 unique 的列必须全部或者部分包含在分布键中。 创建表检查约束 CREATE TABLE products( product_no integer, name text, price numeric CHECK (price > 0)

个节点仅保存总数据量的 1/100，100 个节点同时并行处理，性能会是单个配置更强节点的几十倍。 Greenplum 不仅仅实现了基本的分布式数据存储，还提供了更高级更灵活的特性，譬如多种分布 策略、多级分区以及多态存储。 分布式数据存储 Confidential │ ©2021 VMware, Inc. 9 Greenplum 6 提供了以下 3 种数据分布策略: l 哈希分布 (Hash Distribution) (Replicated Distribution) 数据分布策略 Confidential │ ©2021 VMware, Inc. 10 哈希分布是分布式数据库最为常用的数据分布方式。根据用户自定义的分布键计算哈希值，然后将 哈希结果映射到某个 Segment 上。在 Greenplum 6 中，默认采用一致性哈希(Jump Consistent Hash)分布策略。 哈希分布 当增加一个新的节点时 中，从而实现最小数据迁移。 Confidential │ ©2021 VMware, Inc. 11 随机分布则采用随机的方式将数据存储到不同的节点。当不确定一张表的哈希分布键，或者是不存 在合理的避免数据倾斜的分布键时，即可采用随机分布的方式。 随机分布与复制分布 复制分布则表示整张表在每个节点上都有一份完整的拷贝，假设我们有 100 个节点，复制表则会将 数据保存 100 份。复制表可

Greenplum 还研发了非常多的高级数据分析管理功能和企业级管理模 块，如下这些功能都是 Postgresql 没有提供的： ·外部表并行数据加载 ·可更新数据压缩表 ·行、列混合存储 ·数据表多级分区 ·Bitmap 索引 ·Hadoop 外部表 ·Gptext 全文检索 ·并行查询计划优化器和 Orca 优化器 ·Primary/Mirror 镜像保护机制 ·资源队列管理 ·WEB/Brower 数据库并行计算而言， Mapreduce 的数据在计算前未经整理和组织（只是做了简单数据 分块，数据无模式），而 MPP 预先会把数据有效的组织（有模式）， 例如：行列表关系、Hash 分布、索引、分区、列存储等、统计信 息收集等，这就决定了在计算过程中效率大为不同： ·MAP 效率对比 Hadoop 的 MAP 阶段需要对数据再解析，而 MPP 数据库则会直接 取行列表，效率高。 Hadoop 拆分文件，而且数据不能保证在所有节点都均匀 分布，因此，MAP 过程的并行化程度低；MPP 数据库按照数据记 录拆分和 Hash 分布，粒度更细，数据分布在所有节点中非常均匀， 并行化程度很高。 HadoopHDFS 没有灵活的索引、分区、列存储等技术支持，而 MPP 通常利用这些技术大幅提高数据的检索效率。 ·MAShuffle 效率对比 Hadoop Shuffle 对比 MPP 计算中的重分布 -- 由于 Hadoop 数据

数据库对象数上限的最佳实践  GPDB内部的对象：所有的表（包括分区表）、索引、视图等都称为对象  GPDB最佳实践所推荐的对象管理要求是：一个数据库内对象不要超过10 0000个  最佳实践是出于对系统性能和稳定性因素建议对pg_class 所维护的对象数进行约束  减少对象数的方法： – 提高分区粒度 – 避免大范围使用列存储  pg_class对象数如果不进行约束，可能会产生以下问题： 混合负载的性能 分布键使用： • 尽量采用一个常用关联字段作为分布键，例如账号、客户号，这个可以提高关联条件的命中率，减少关联时数据重分布 （主要对大表） • 选用分布键同时考虑数据平均分布（一个例子，日志号不是最好的分布键，大量的空值导致资料倾斜） 物理模型经验分享（续） 分区表使用： • 不建议使用二级分区，二级分区不便于管理，而且Parser效率较低； 较低； • 二级分区可以用一级分区+Bitmap方式替代，例如按照“发生日期”做分区，然后在机构字段上将bitmap索引 • 对于1亿条记录以下的表不分区(对于小系统，该阀值适当调低) 索引使用： • 以数据批处理为主要功能的系统一般不需建索引 • 以并发查询为主要功能，特别OLTP查询（根据KEY，Attribute等作为筛选条件）的系统按照常用字段建索引。 • 建索引的方法

数据从Disk上的I/O吞吐性能 Ø master管理节点 Ø segment数据节点 • greenplum的核心功能 Ø 无共享MPP Ø 多态存储 Ø 高效数据加载 （gpfdist+外部表，每小时4TB+） Ø 分布分区 Ø 数据压缩 Ø 外部访问 15 Greenplum现状说明 三 Greenplum体系架构 二 数据仓库体系架构 一 Greenplum开发规范 五 Greenplum运维体系 四 表定义过大 Ø 表类型单一 Ø 表的散列键不恰当 Ø 分区表的分区键性能不佳 • 加载易出现问题 Ø 文件加载出现特殊字符 Ø 数据校验标准问题 35 Greenplum开发规范 业务库表设计规范 • GP中表的范围 Ø 最大时间为年表 Ø 数据量小，可用单表 • 多种表类型 Ø 堆表 （选好常用列作为三列键） Ø 分区表 （按照 yyyymmdd 分区，建议都添加 datenum int8）

relational database are well-showcased 12 Pivotal Confidential–Internal Use Only 卓越的OLAP特性 列式存储 分区、压缩 高级特性 递归查询、窗口函数 集成分析 多格式、多语言 Madlib: 机器学习 数据库内并行模型训练和预测、分类 ORCA 复杂查询优化器 成熟稳定 完备生态、支撑核心生产系统 Segment 2C Segment 3C Segment 3D 分区 SELECT COUNT(*) FROM orders WHERE order_date >= ‘Oct 1 2007’ AND order_date <= ‘Oct 31 2007’ 仅仅扫描 orders 表2017年十月份数据所在的分区C Segment 1A Segment 1B Segment 1C 02 Efficiently Processing Complex Correlated Queries 01 Dynamic Partition Elimination 03 动态分区裁剪 公共表达式的下推 高效处理相关子查询 超过8年的投资，多位博士的长期贡献 基于Cascades / Volcano框架, Goetz Graefe 优化分布式大数据系统中特别复杂的查询

er的 mirror可以在建库时建，也可以在建完greenplum后再 添加。 理解greenplum分布式数据库 理解GP分布式数据库 理解GP的数据分布策略 Hash分布：按分布键对数据时行hash分布，这个hash 分布算法没有公布，只有greenplum内部知道数据是如 何hash分布的。 随机分布：数据随机分布在数据库，每次查询都会查询 所有的 segment。 (a int, b text) WITH (appendonly=true, compresstype=zlib, compresslevel=5); GP使用人性化的partion语法支持分区表 CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 day') ); GP使用人性化的partion语法支持分区表 CREATE TABLE sales (id int, date date, amt decimal(10,2)) DISTRIBUTED BY (id) PARTITION BY RANGE

科学、分析、报告和数据洞察方法方面，这些功能对大多数客户都很有帮助。Greenplum 解决方案的架构设计目的是管理 非常复杂的查询，以及为符合 ANSI 标准的 SQL 提供强有力的分析改进。通过自动对数据进行分区和并行运行查询，它让 服务器群集能够以单一数据超级计算机的方式运行，且性能比传统数据库或其他同类平台高出数十甚至数百倍。其多种分 析扩展功能支持 ANSI SQL，并通过封装扩展提供多种内置语言和附加功能。Greenplum 计划的速度比传统规划器优化器平 均要快 7 倍。6 要整理大型表中的数据，一种常见方法就是使用分区。Greenplum 5 采用 GPORCA 作为默认查询优化器，可确定消除与结 果无关的分区的经济方式，从而改进动态分区消除。这是通过引入以下三个新的查询操作符实现的，这三个操作符在生成 者 / 使用者模式下配合使用以便对分区表执行扫描：PartitionSelector、DynamicScan 和 Sequence。通过在查询计划中放置 Sequence。通过在查询计划中放置 这些 PartitionSelector，GPORCA 可以支持更复杂的模式，例如基于相等和范围谓词的分区选择，以及动态分区消除。7 Greenplum 5 中的 ANALYZE 命令使用更快速的 PostgreSQL 实施来收集表统计数据，从而针对堆积优化表和附加优化表提 高其性能。系统会在单个查询中收集行示例，并在内存中执行每列统计数据的计算。而在过去，则会针对每列运行单独的

Scatter/Gather 流处理 在线系统扩展 任务管理 服务 加载 & 数据联邦 高速数据加载 近实时数据加载 任意系统数据访问 存储 & 数据访问 混合存储引擎（行存&列存） 多种压缩，多级分区表 索引（B树，位图，GiST） 安全性 语言支持 标准SQL支持，SQL 2003 OLAP扩展 支持 MapReduce 扩展编程语言 (Python,R, Java, Perl, Segment 3B Segment 3C Segment 3D 分布和分区 SELECT COUNT(*) FROM orders WHERE order_date >= ‘Oct 20 2007’ AND order_date < ‘Oct 27 2007’ & orders 表数据均匀分布于各个节点 仅仅扫描 orders 表相关的分区 Segment 1A Segment 1B Segment Segment 3C Segment 3D 10 Pivotal Confidential–Inter nal Use Only 多级分区存储 • 哈希Distribution：数据均 匀的分布到各个数据节点 • 范围分区： 数据节点内部， 根据多种规则分区，降低扫 描量 数据集 Segment 1A Segment 1C Segment 1D Segment 2A Segment 2B