oushuDB应用
一.简介
OushuDB是由Apache HAWQ创始团队基于HAWQ打造的新一代数据仓库(New Data Warehouse)。该产品采用了存储与计算分离技术架构，具有MPP的所有优点，还具有弹性，支持混合工作负载和高扩展性等优点。作为HAWQ的增强版，OushuDB遵循ANSI-SQL标准，兼容Oracle、Greenplum Database和PostgreSQL，提供PB级数据交互式查询能力，提供对主要BI工具的描述性分析和AI支持。相对于开源的Apache HAWQ，OushuDB的增强主要体现在以下方面：
-
采用全新执行引擎，充分利用硬件的所有特性，比Apache HAWQ性能高出5-10倍。
-
替换JAVA PXF，性能高数倍，无需安装部署PXF额外组件，极大简化了用户安装部署和运维。
-
原生支持CSV/TEXT外部存储。
-
可以实现可插拔文件系统：比如S3, Ceph等。
-
可以实现可插拔文件格式：比如ORC，Parquet等。
-
支持ORC/TEXT/CSV作为内部表格式，支持ORC作为外部表格式（通过C++可插拔外部存储）。
-
CSV和TEXT文件格式中对非ASCII字符串或长度大于1的字符串作为分隔符的支持。
-
关键Bug fixes。
二.架构
1.主要架构
图中显示的整个数据仓库环境包括操作型系统和数据仓库系统两大部分。操作型系统的数据经过抽取、转换和装载（ETL）过程进入数据仓库系统。这里把ETL过程分成了抽取和转换装载两个部分。抽取过程负责从操作型系统获取数据，该过程一般不做数据聚合和汇总，物理上是将操作型系统的数据全量或增量复制到数据仓库系统的RDS中。转换装载过程将数据进行清洗、过滤、汇总、统一格式化等一系列转换操作，使数据转为适合查询的格式，然后装载进数据仓库系统的TDS中。传统数据仓库的基本模式是用一些过程将操作型系统的数据抽取到文件，然后另一些过程将这些文件转化成MySQL这样的关系数据库的记录。最后，第三部分过程负责把数据导入进数据仓库。
-
RDS（RAW DATA STORES）是原始数据存储的意思。它的作用主要有三个：作为数据缓冲区；提供细节数据；保留原始数据，便于跟踪和修正ETL的错误。本例中的RDS使用OushuDB的ORC外部表。
-
TDS（TRANSFORMED DATA STORES）意为转换后的数据存储。这里存储真正的数据仓库中的数据。本例中的TDS使用OushuDB的ORC内部表。
-
自动化调度组件的作用是自动定期重复执行ETL过程。作为通用的需求，所有数据仓库系统都应该能够建立周期性自动执行的工作流作业。传统数据仓库一般利用操作系统自带的调度功能（如Linux的cron或Windows的计划任务）实现作业自动执行。本示例使用Falcon完成自动调度任务。
-
数据目录有时也被称为元数据存储，它可以提供一份数据仓库中数据的清单。一个好的数据目录是让用户体验到系统易用性的关键。OushuDB是数据库系统，自带元数据表。
-
查询引擎组件负责实际执行用户查询。传统数据仓库中，它可能是存储转换后数据的MySQL等关系数据库系统内置的查询引擎，还可能是以固定时间间隔向其导入数据的OLAP立方体，如Essbase cube。OushuDB本身就是以一个强大的查询引擎而存在，而且具有优良的性能。本示例使用OushuDB作为查询引擎正是物尽其用。
-
用户界面指的是最终用户所使用的接口程序。可能是一个GUI软件，如BI套件的中的客户端软件，也可能就是一个浏览器。本示例的用户界面使用Zeppelin。
2.主要功能
-
利用现有技能和工具轻松与其他工具集成
-
发现新知识,数据科学,分析外部数据源,查询所有数据类型
-
和hadoop生态系统原生集成
-
管理多种工作负载,PB级数据分析,交互式查询功能
三.多租户操作
oushu通过角色管理数据库的访问权限,角色的概念在oushu包括用户及组,一个角色可以是数据库的一个用户,或者一个组,或者是两者都是,角色拥有数据库对象的权限,也可以将其在某些对象所拥有的权限给其他角色,某个角色可以是其他角色的成员,因此成员角色可以继承它的父角色所拥有的对象的权限.
1.创建共同用户
-
条件
-
必须连接到具有CREATE USER特权的共同用户。
-
在当前的数据库必须是根容器数据库。
-
常见的用户的用户名必须与"C##"或"c##"作为前缀，并包含唯一的 ASCII 或 EBCDIC 字符。
-
在所有容器该共同用户名必须是唯一的。DEFAULT TABLESPACE、TEMPORARY TABLESPACE、QUOTA和PROFILE必须存在于所有容器中的所有引用对象。您可以指定CONTAINER=ALL子句，或者忽略它，因为这是默认设置时，当前的容器是根。
-
公共用户可以在不同的CDB、PDB数据库中有不同的权限。
-
-
操作
-
在 cdb 中创建公共用户的时候， pdbs 中也会创建相同用户。若CDB 下 GRANT 命令赋权，如果赋权时未指定container=all，则赋权只在CDB中生效，并不会在PDB中生效,这个用户要能够访问PDB，需要切换到 pdb 再赋权。若赋权时指定 container=all，则赋权在CDB中生效，也在PDB中生效。
-
2.创建本地用户
-
条件
-
必须连接到具有CREATE USER特权的用户。
-
本地用户的用户名不必须与"C##"或"c##"作为前缀。
-
用户名必须是在 PDB 内是唯一的。
-
当前的容器是 PDB 的时候，您可以指定CONTAINER=CURRENT子句，或者忽略它，因为这是默认设置。
-
3.创建共同角色
类似于前面所述的用户，角色可以是共同的或本地的 。 Oracle 提供的所有角色在根容器中和所有的 Pdb可见。创建共同角色需要满足以下条件。
-
条件
-
必须连接到共同用户CREATE ROLE与通常授予的SET CONTAINER权限。
-
当前的容器必须是根容器。
-
常见角色的角色名称必须与"C##"或"c##"为前缀，并且包含唯一 ASCII 或 EBCDIC 字符。
-
在所有容器的角色名称必须是唯一的。
-
角色创建与CONTAINER=ALL子句
-
公共角色可以赋予公共用户或者本地用户
-
-
操作 注意：在 CDB 赋予角色权限的时候，需要指定 container=all ，否则 PDB 中不可见。
4.创建本地角色
-
条件
-
必须连接到具有CREATE ROLE权限的用户。
-
如果您连接到公共用户，容器必须设置为本地 PDB。
-
角色名称为本地角色不必须与"C##"或"c##"作为前缀。
-
角色名称必须是唯一在 PDB 内。
-
本地角色可以赋权给公共用户（作用范围局限于pdb内操作，不影响CDB权限）或者本地用户。
-
四.HAWQ++资源管理模型
1.三级资源管理
-
集群资源管理,基于GRM containers
-
自动推算集群共用内存CPU比例,校正segment可用资源上限
-
通过GRM container的分配实现segment可用资源管理
-
segment端针对可用资源量实时检查资源使用情况
-
-
面向Role的基于Resource Queue的SQL计算资源管理
-
用户创建Resource Queue
-
用户将Role指定列到Resource Queue
-
Resource Queue定义VSEG资源使用细节
-
-
查询计划操作符级别的资源管理
-
细粒度,低延迟,高性能的SQL执行资源分配
-
大粒度,秒级延迟,低频率外部资源分配
-
VSEG是逻辑上的资源分配单位,一个VSEG的资源供一个计划的一组slice对应的一组进程共同消费
-
GRM container全局字眼管理容器的累加代表当前segment可用资源的总量.VSEG的分配代表了资源的消费
-
HAWQ segment管理一个启动的segment实例所有可用资源.一个系统节点只安装一个segment实例.HAWQ对SQL执行的计算并法度完全由VSEG的数量决定
-
2.资源队列
-
将缺省的pg_default的资源限制由50%改为20%，同时将过度使用因子设置为5。 alter resource queue pg_default with (memory_limit_cluster=20%,core_limit_cluster=20%,resource_overcommit_factor=5);
-
建立一个dwtest用户使用的专用队列，资源限制由80%，同时将过度使用因子设置为2。 create resource queue dwtest_queue with (parent='pg_root', memory_limit_cluster=80%, core_limit_cluster=80%,resource_overcommit_factor=2);
-
查看资源队列配置。
-
select rsqname,
parentoid,
activestats,
memorylimit,
corelimit,
resovercommit,
allocpolicy,
vsegresourcequota,
nvsegupperlimit,
nvseglowerlimit,
nvsegupperlimitperseg,
nvseglowerlimitperseg
from pg_resqueue;
-
-
用gpadmin将dwtest用户的资源队列设置为新建的dwtest_queue。
-
-- 修改用户资源队列
alter role dwtest resource queue dwtest_queue;
-- 查看用户资源队列
select rolname, rsqname from pg_roles, pg_resqueue
where pg_roles.rolresqueue=pg_resqueue.oid;
-
-
假设其他用户都使用缺省的pg_default队列。采用以上定义，工作负载通过资源队列划分如下：
-
如果没有活跃用户使用pg_default队列时，dwtest用户可以使用100%的资源。
-
如果有同时使用pg_default队列的其它用户，则dwtest用户使用80%，其它用户使用20%资源。
-
如果没有dwtest用户的活跃任务，其它使用pg_default队列的用户可以使用100%的资源。
-
-
所有资源队列中虚拟段的资源限额均为缺省的256MB，每个segment可以分配32个虚拟段。并且hawq_rm_memory_limit_perseg的值设置为8GB，是256MB的32倍，每核2GB内存，这种配置防止形成资源碎片。
五.oushuDB连接管理
-
配置pg_hba.conf文件
-
reload配置文件,使其生效
-
配置连接参数
-
hawq config -c max_connections -V 1000
-
hawq config -c seg_max_connections -V 3000
-
六.对象管理
-
定义字段和数据类型
-
选择使用最小空间的字段类型
-
使用相同的字段类型作为join字段
-
-
表属性
-
通过with关键字指定存储选项,也可以用在分区和子分区上
-
默认是行存储
-
可以采用parquet模式存储
-
可以进行表级别指定压缩也可以进行字段级别指定压缩
-
-
使用行表的情况
-
大量的通过insert...values增量加载
-
查询多采用宽表的情况下
-
-
使用parquet表的情况
-
一个写多少读
-
没有使用分区的情况下
-
查询多采用窄表方式
-
七,数据管理
1.数据分布
-
Random分布的表比较灵活,在系统扩容添加节点后五需要重新分布数据
-
Hash分布的表在某些查询上会有性能上的好处,因为有时可以避免重新分布某些表.但hash分布的表在系统扩容后,为了利用新增加节点的计算能力,需要重新分布数据.另外,针对hash分布的表资源管理器在分配资源的时候才去固定virtual segment数的方式,不如random分布灵活.
-
针对hash分布的表,bucketnum决定了一个查询的并行度.
-
影响数据分布的部分参数
-
default_hash_table_buket_number 6*slave(只针对hash分布的表)
-
hawq_rm_nvseg_perquery_limit
-
这是资源管理器针对一个查询可以分配的最大virtual segment数,默认512,可以调整该值来放松或者加紧对并行度的限制
-
-
hawq_rm_nvseg_perquery_perseg_limit
-
每segment每次查询的vseg限制(只针对随机)
-
-
2.执行SQL VSEG分配方案的决定因素
-
用户是否在客户端指定statement级别的设置
-
用户关于当前SQL的guc值对VSEG的约束
-
查询目标表的hash的还是自由分布的
-
当前查询目标表的规模和数据分布信息
-
当前集群动态规模和可调度总资源量
-
用户对应Resource Queue的容量
-
Resource Queue关于各项指标的设置
-
当前正在运行的SQL对资源的占用状况
-
当前节点的健康状况
-
当前节点的总负载情况
-
VSEG分配结果检测标准设置
3.基于外部表的高速数据加载/导出
-
利用并行数据流引擎,Greenplum可以直接用SQL操作外部表,数据加载完全并行
-
外部表加载
-
外部表的特征
-
read-only/write-only两种方式
-
数据存放在数据库外
-
可执行select.join,sort等命令,类似正规表的操作
-
-
外部表的优点
-
并行方式加载
-
ETL的灵活性
-
格式错误行的容错处理
-
支持多种数据源
-
-
两种方式
-
external table:基于文件
-
webtables:基于URL或指令
-
-
可插拔存储和格式支持
-
原外部格式支持是两段式的,即协议部分和格式部分
-
引入更快捷的数据输入输出方式将协议和格式处理一次性完成
-
引入了外部表数据分布获取接口,使数据访问和优化上与内部表无区别
-
writable external table默认支持读写操作
-
external table支持只读操作
-
新外部表支持弹性引擎的动态执行,支持基于HDFS块的任务分配优化
-
-
4.大表分区
-
表分区
-
针对大型的数据仓库事实表,往往我们可以通过对表进行分区的方式把一个很大的表拆分成多个字表,优点有:
-
查询优化器可以针对分区表进行优化,查询时只需要扫描部分分区,从而加速查询
-
若按照日期进行分区的话,我们可以进行简单的假如分区和删除分区
-
-
oushuDB支持range和list两种分区方式
-
range分区:依据数值范围进行分区,例如日期,价格等等
-
list分区:依据一个值的列表进行分区,例如地区
-
-
-
多级分区
-
使用分布策略将数据分布到所有的segment
-
使用范围分区将数据分成若干区域
-
-
指定分区
-
通过写SQL语句,在创建表时进行制定
-
-
分区注意事项
-
支持多级分区但不建议使用(会导致过多的小文件)
-
为了获得最佳性能,给定查询的扫描分区的最大数目不应超过2000
-
八.设计规范
-
表创建规范
-
保存表名都是小写,表名中不允许出现中文和特殊字符
-
不允许在建表语句中使用双引号
-
单个数据库的表数量建议不要超过10万张
-
进制使用二级分区
-
-
表结构设计
-
字段命名
-
字段名在建表时不允许使用双引号
-
不允许出现特殊字符和中文
-
-
数据类型
-
在满足业务的情况下,尽可能选择空间占用小的数据类型,以节省数据存储空间
-
多数情况下选择varchar不选择char
-
定长字符串类型使用varchar,不用char
-
进制使用不带长度定义的varchar类型
-
尽量选择更小的数据类型来适应数据
-
进制使用不带长度的numeric类型
-
做join的column,应考虑选择相同的数据类型,其工作效率会更高
-
一般情况下,应尽量使用上述规范数据类型
-
-
表的存储格式
-
AO是行存储格式,如果一个表有很多频繁的小的追加的话,建议使用AO,其他情况下建议使用snappy压缩的parquet格式
-
parquet格式是列存储
-
所有单表查询,如where条件查询,group by,sort by采用orc格式表,这样可以用到新执行器,性能是老执行器的5~10倍
-
一般情况下,维表,小表,经常追加数据的表使用AO行存储
-
事实表,大表,对表部分列做聚合查询的情况下,建议列存储
-
压缩默认使用snappy,在特殊情况下,压缩比要求很高时,考虑用其他压缩
-
-
数据分布
-
分布键字段原则上为1个,尽量不超过3个
-
分区的父子表的分布键应完全一致
-
中间过程表,临时表,派生表的DK应尽可能保持和源表一致
-
具有关联关系的数据表,尽可能使用关联字段作为分布键
-
-
分区
-
在性能可以满足的情况下,尽量不使用数据分区
-
禁止使用二级分区
-
数据量在亿级别一下,建议不要使用分区
-
表的数据在单个实例的数据量在100万级别一下,不需要分区
-
-
-
其他数据库对象设计
-
schema
-
不允许在系统的schema下创建用户表
-
数据库内所有开发的实体均应按照其所在的模型层次使用对应的模式名
-
-
视图
-
视图名不允许使用双引号和中文
-
不建议在视图中使用order by子句
-
对频繁访问,具有多个大表关联,并含有复杂计算或排序的视图,建议修改为物理表
-
-
临时表和中间表
-
临时表和中间表啊评估表数据量时建议大表统一采用压缩表
-
-
九.SQL开发规范
-
where条件
-
where过滤中,应尽量将函数处理放在等式的右边,以提高查询性能
-
对于日期类型的字段判断,条件值可直接使用字符串
-
在条件过滤中使用函数,不需要写select关键字,否则会影响执行计划的准确性
-
采用日期分区的表中,等式左边不要使用数值运算
-
在where条件中错误的添加1<>1的判断,会导致执行计划混乱
-
-
分区字段使用
-
多个分区表关联时,每个分区表都需要制定分区字段的条件
-
-
表关联
-
表连接中的每个表指定缩写的别名,尽可能清洗可辨
-
一个SQL语句中多表关联的关联表不要超过10张表
-
几个大小差不多的表做关联时,过滤性较强的优先做join
-
先join小表再join大表
-
表通过分布键关联时,不要使用表达式字段的方式进行关联,否则会导致数据重分布
-
-
排序语句
-
建议不要在视图定义中使用order by排序语句
-
尽量避免使用
-
不要在大的数据结果集上执行排序操作
-
partition by,union内部实现需要对数据排序,在数据量在千万级别下,差别不大,如果数据量在亿级别上,建议尽量使用group by实现
-
-
嵌套子查询
-
子查询嵌套的层次不要超过3层
-
-
union/union all
-
union操作,如果不需要去重,请用union all替代
-
不允许过多的使用union all
-
-
高效的SQL写法
-
在关联字段中,尽量包含分布键作为关联条件,避免数据重分布
-
对于大表的union操作,如果不需要去重,请用union all替代
-
对于大表的union操作,如果需要去重,请用union all加上 group by替代
-
清空数据表,应使用truncate操作,不要使用无条件的delete操作,避免vacuum处理
-
应尽量将嵌套子查询,转换为等价的外连接方式实现,减少嵌套层次
-
-
