一、业务场景

二、数据仓库架构

三、实验环境

1. 硬件环境

• 15K RPM SAS 100GB
• Intel(R) Xeon(R) E5-2620 v2 @ 2.10GHz，双核双CPU
• 8G内存，8GSwap
• 10000Mb/s虚拟网卡

2. 软件环境

• Linux：CentOS release 6.4，核心2.6.32-358.el6.x86_64
• Ambari：2.4.1
• Hadoop：HDP 2.5.0
• HAWQ：2.1.1.0
• HAWQ PXF：3.1.1
• MySQL 5.6.14

四、HAWQ相关配置

1. 创建客户端认证

图3

2. 设置并发连接参数

-- 设置参数
hawq config -c max_connections -v 100 
hawq config -c seg_max_connections -v 1000  
hawq config -c max_prepared_transactions -v 200 

-- 重启HAWQ
hawq restart cluster

-- 查看配置
hawq config -s max_connections
hawq config -s seg_max_connections
hawq config -s max_prepared_transactions

图4

3. 创建数据库用户

-- 创建用户
create role dwtest with password ‘123456‘ login createdb;
-- 查看用户
\dg

图5

psql -U dwtest -d gpadmin -h hdp3
-- 查看数据库
\l

图6

4. 创建资源队列

alter resource queue pg_default with
(memory_limit_cluster=20%,core_limit_cluster=20%,resource_overcommit_factor=5);

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;

-- 修改用户资源队列
alter role dwtest resource queue dwtest_queue;
-- 查看用户资源队列
select rolname, rsqname from pg_roles, pg_resqueue  
 where pg_roles.rolresqueue=pg_resqueue.oid;

• 如果没有活跃用户使用pg_default队列时，dwtest用户可以使用100%的资源。
• 如果有同时使用pg_default队列的其它用户，则dwtest用户使用80%，其它用户使用20%资源。
• 如果没有dwtest用户的活跃任务，其它使用pg_default队列的用户可以使用100%的资源。

5. 配置资源

select * from gp_segment_configuration;

hawq config -s hawq_global_rm_type

hawq config -s hawq_rm_memory_limit_perseg
hawq config -s hawq_rm_nvcore_limit_perseg

su - hdfs -c ‘hdfs dfs -mkdir -p /data/ext‘
su - hdfs -c ‘hdfs dfs -chown -R gpadmin:gpadmin /data/ext‘
su - hdfs -c ‘hdfs dfs -chmod -R 777 /data/ext‘
su - hdfs -c ‘hdfs dfs -chmod -R 777 /user‘
su - hdfs -c ‘hdfs dfs -ls /data‘

        结果如图12所示。

图12

五、创建测试数据库

1. 创建源库对象并生成测试数据

-- 建立源数据库  
drop database if exists source;  
create database source;  

use source;  

-- 建立客户表    
create table customer (    
    customer_number int not null auto_increment primary key comment ‘客户编号，主键‘,    
    customer_name varchar(50) comment ‘客户名称‘,    
    customer_street_address varchar(50) comment ‘客户住址‘,    
    customer_zip_code int comment ‘邮编‘,    
    customer_city varchar(30) comment ‘所在城市‘,    
    customer_state varchar(2) comment ‘所在省份‘    
);  

-- 建立产品表    
create table product (       
    product_code int not null auto_increment primary key comment ‘产品编码，主键‘,    
    product_name varchar(30) comment ‘产品名称‘,    
    product_category varchar(30) comment ‘产品类型‘       
);  

-- 建立销售订单表    
create table sales_order (    
    order_number int not null auto_increment primary key comment ‘订单号，主键‘,    
    customer_number int comment ‘客户编号‘,    
    product_code int comment ‘产品编码‘,    
    order_date datetime comment ‘订单日期‘,    
    entry_date datetime comment ‘登记日期‘,    
    order_amount decimal(10 , 2 ) comment ‘销售金额‘,    
    foreign key (customer_number)    
        references customer (customer_number)    
        on delete cascade on update cascade,    
    foreign key (product_code)    
        references product (product_code)    
        on delete cascade on update cascade    
);

use source;  

-- 生成客户表测试数据    
insert into customer 
(customer_name,customer_street_address,customer_zip_code,
customer_city,customer_state) 
values      
(‘really large customers‘, ‘7500 louise dr.‘,17050, ‘mechanicsburg‘,‘pa‘),    
(‘small stores‘, ‘2500 woodland st.‘,17055, ‘pittsburgh‘,‘pa‘),    
(‘medium retailers‘,‘1111 ritter rd.‘,17055,‘pittsburgh‘,‘pa‘),    
(‘good companies‘,‘9500 scott st.‘,17050,‘mechanicsburg‘,‘pa‘),    
(‘wonderful shops‘,‘3333 rossmoyne rd.‘,17050,‘mechanicsburg‘,‘pa‘),    
(‘loyal clients‘,‘7070 ritter rd.‘,17055,‘pittsburgh‘,‘pa‘),    
(‘distinguished partners‘,‘9999 scott st.‘,17050,‘mechanicsburg‘,‘pa‘);  

-- 生成产品表测试数据    
insert into product (product_name,product_category)    
values     
(‘hard disk drive‘, ‘storage‘),    
(‘floppy drive‘, ‘storage‘),    
(‘lcd panel‘, ‘monitor‘);  
  
-- 生成100条销售订单表测试数据  
drop procedure if exists generate_sales_order_data;  
delimiter //    
create procedure generate_sales_order_data()  
begin   
    drop table if exists temp_sales_order_data;  
    create table temp_sales_order_data as select * from sales_order where 1=0;  
  
    set @start_date := unix_timestamp(‘2016-03-01‘);  
    set @end_date := unix_timestamp(‘2016-07-01‘);  
    set @i := 1;  
  
    while @i<=100 do  
        set @customer_number := floor(1 + rand() * 6);  
        set @product_code := floor(1 + rand() * 2);  
        set @order_date := from_unixtime(@start_date + rand() * (@end_date - @start_date));  
        set @amount := floor(1000 + rand() * 9000);  
  
        insert into temp_sales_order_data values (@i,@customer_number,@product_code,@order_date,@order_date,@amount);  
        set @i:=@i+1;  
    end while;  
  
    truncate table sales_order;  
    insert into sales_order   
    select null,customer_number,product_code,order_date,entry_date,order_amount from temp_sales_order_data order by order_date;  
    commit;  
  
end   
//    
delimiter ;   
  
call generate_sales_order_data();

create user ‘dwtest‘@‘%‘ identified by ‘123456‘;
grant select on source.* to ‘dwtest‘@‘%‘;

2. 创建目标库及其模式

psql -U dwtest -d gpadmin -h hdp3

create database dw;

-- 连接dw数据库
\c dw
-- 创建ext模式
create schema ext;
-- 创建rds模式
create schema rds;
-- 创建tds模式
create schema tds;
-- 查看模式
\dn

-- 修改数据库的模式查找路径
alter database dw set search_path to ext, rds, tds; 
-- 重新连接dw数据库
\c dw
-- 显示模式查找路径
show search_path;

• 方便管理多个用户共享一个数据库，但是又可以互相独立。
• 方便管理众多对象，更有逻辑性。
• 方便兼容某些第三方应用程序，如果创建对象时是带schema的。

• Sqoop可以将MySQL数据导入到HDFS或hive，但目前还没有命令行工具可以将MySQL数据直接导入到HAWQ数据库中。所以不得不将缓冲数据存储到HDFS，再利用HAWQ的外部表进行访问。
• 如果只创建两个模式分别用作RDS和TDS，则会带来性能问题。在变化数据捕获（CDC）时需要关联RDS和TDS的表，而HAWQ的外部表和内部表关联查询的速度是很慢的，在数据量非常大的情况下，更是慢到不能忍受。
• 通常维度数据量比事实数据量小得多。在这个前提下，用EXT模式存储直接从MySQL导出的外部数据，包括全部维度数据和增量的事实数据，然后将这些数据装载进RDS模式内部表中。这一步装载的数据量并不是很大，而且没有关联逻辑，都是简单的单表查询与数据插入。在装载TDS内部表时，仍然关联RDS与TDS的表，但这两个模式中的表都是内部表，查询速度是可接受的。

3. 创建EXT模式中的数据库对象

psql -d dw -h hdp3 -c "grant all on protocol pxf to dwtest"

ERROR:  permission denied for external protocol pxf

-- 设置模式查找路径
set search_path to ext;

-- 建立客户外部表
create external table customer 
( 
  customer_number int,  
  customer_name varchar(30),  
  customer_street_address varchar(30),  
  customer_zip_code int,  
  customer_city varchar(30),  
  customer_state varchar(2)
)  
location (‘pxf://mycluster/data/ext/customer?profile=hdfstextsimple‘)  
  format ‘text‘ (delimiter=e‘,‘); 

comment on table customer is ‘客户外部表‘;
comment on column customer.customer_number is ‘客户编号‘;
comment on column customer.customer_name is ‘客户姓名‘;
comment on column customer.customer_street_address is ‘客户地址‘;
comment on column customer.customer_zip_code is ‘客户邮编‘;
comment on column customer.customer_city is ‘客户所在城市‘;
comment on column customer.customer_state is ‘客户所在省份‘;

-- 建立产品外部表
create external table product 
( 
  product_code int, 
  product_name varchar(30),  
  product_category varchar(30)
)  
location (‘pxf://mycluster/data/ext/product?profile=hdfstextsimple‘)  
  format ‘text‘ (delimiter=e‘,‘); 

comment on table product is ‘产品外部表‘;
comment on column product.product_code is ‘产品编码‘;
comment on column product.product_name is ‘产品名称‘;
comment on column product.product_category is ‘产品类型‘;

-- 建立销售订单外部表
create external table sales_order
( 
  order_number int,    
  customer_number int,    
  product_code int,    
  order_date timestamp,    
  entry_date timestamp,    
  order_amount decimal(10 , 2 ) 
)  
location (‘pxf://mycluster/data/ext/sales_order?profile=hdfstextsimple‘)  
  format ‘text‘ (delimiter=e‘,‘, null=‘null‘); 

comment on table sales_order is ‘销售订单外部表‘;
comment on column sales_order.order_number is ‘订单号‘;
comment on column sales_order.customer_number is ‘客户编号‘;
comment on column sales_order.product_code is ‘产品编码‘;
comment on column sales_order.order_date is ‘订单日期‘;
comment on column sales_order.entry_date is ‘登记日期‘;
comment on column sales_order.order_amount is ‘销售金额‘;

• 外部表结构与MySQL里的源表完全对应，其字段与源表相同。
• PXF外部数据位置指向前面（四.6）创建的HDFS目录。
• 文件格式使用逗号分隔的简单文本格式，文件中的‘null‘字符创代表数据库中的NULL值。下一篇说明的数据初始装载时会看到，为了让EXT的数据文件尽可能的小，Sqoop使用了压缩选项，而hdfstextsimples属性的PXF外部表能自动正确读取Sqoop缺省的gzip压缩文件。

4. 创建RDS模式中的数据库对象

-- 设置模式查找路径
set search_path to rds;

-- 建立客户原始数据表
create table customer 
( 
  customer_number int,  
  customer_name varchar(30),  
  customer_street_address varchar(30),  
  customer_zip_code int,  
  customer_city varchar(30),  
  customer_state varchar(2)
); 

comment on table customer is ‘客户原始数据表‘;
comment on column customer.customer_number is ‘客户编号‘;
comment on column customer.customer_name is ‘客户姓名‘;
comment on column customer.customer_street_address is ‘客户地址‘;
comment on column customer.customer_zip_code is ‘客户邮编‘;
comment on column customer.customer_city is ‘客户所在城市‘;
comment on column customer.customer_state is ‘客户所在省份‘;

-- 建立产品原始数据表
create table product 
( 
  product_code int, 
  product_name varchar(30),  
  product_category varchar(30)
); 

comment on table product is ‘产品原始数据表‘;
comment on column product.product_code is ‘产品编码‘;
comment on column product.product_name is ‘产品名称‘;
comment on column product.product_category is ‘产品类型‘;

-- 建立销售订单原始数据表
create table sales_order
( 
  order_number int,    
  customer_number int,    
  product_code int,    
  order_date timestamp,    
  entry_date timestamp,    
  order_amount decimal(10 , 2 ) 
)
partition by range (entry_date)  
( start (date ‘2016-01-01‘) inclusive  
   end (date ‘2018-01-01‘) exclusive  
   every (interval ‘1 month‘) ); 
; 

comment on table sales_order is ‘销售订单原始数据表‘;
comment on column sales_order.order_number is ‘订单号‘;
comment on column sales_order.customer_number is ‘客户编号‘;
comment on column sales_order.product_code is ‘产品编码‘;
comment on column sales_order.order_date is ‘订单日期‘;
comment on column sales_order.entry_date is ‘登记日期‘;
comment on column sales_order.order_amount is ‘销售金额‘;

• RDS模式中的表是HAWQ内部表。
• RDS模式中表数据来自EXT表，并且是原样装载，不需要任何转换，因此其表结构与EXT中的外部表一致。
• HAWQ支持row和parquet两种数据存储格式。如果单纯从性能方面考虑，似乎parquet列格式更适合数据仓库应用。这里使用缺省的row格式，只因为注意到文档中这样一句话：“HAWQ does not support using ALTER TABLE to ADD or DROP a column in an existing Parquet table.”。我还没遇到哪个数据库表创建完后就再也不用增删字段的情况。关于行列存储的选择，参见“大数据存取的选择：行存储还是列存储？”
• 为了保持查询弹性使用资源和更好的数据本地化，使用缺省的随机数据分布策略，而没有使用哈希分布。关于HAWQ表数据的存储与分布，参见“用HAWQ轻松取代传统数据仓库（七） —— 存储分布”。
• RDS是实际上是原始业务数据的副本，维度数据量小，可以覆盖装载全部数据，而事实数据量大，需要追加装载每天的新增数据。因此事实表采取分区表，每月数据一分区，以登记日期作为分区键，预创建2017年一年的分区。

5. 创建TDS模式中的数据库对象

-- 设置模式查找路径
set search_path to tds;

-- 建立客户维度表    
create table customer_dim (    
    customer_sk bigserial, 
    customer_number int,    
    customer_name varchar(50),    
    customer_street_address varchar(50),    
    customer_zip_code int,    
    customer_city varchar(30),    
    customer_state varchar(2),  
    isdelete boolean default false,	
	version int,  
    effective_date date  
);
 
comment on table customer_dim is ‘客户维度表‘;
comment on column customer_dim.customer_sk is ‘客户维度代理键‘;
comment on column customer_dim.customer_number is ‘客户编号‘;
comment on column customer_dim.customer_name is ‘客户姓名‘;
comment on column customer_dim.customer_street_address is ‘客户地址‘;
comment on column customer_dim.customer_zip_code is ‘客户邮编‘;
comment on column customer_dim.customer_city is ‘客户所在城市‘;
comment on column customer_dim.customer_state is ‘客户所在省份‘;
comment on column customer_dim.isdelete is ‘是否删除‘;
comment on column customer_dim.version is ‘版本‘;
comment on column customer_dim.effective_date is ‘生效日期‘;

-- 建立产品维度表    
create table product_dim (    
    product_sk bigserial, 
    product_code int,    
    product_name varchar(30),    
    product_category varchar(30),
    isdelete boolean default false,	
    version int,  
    effective_date date 
);  

comment on table product_dim is ‘产品维度表‘;
comment on column product_dim.product_sk is ‘产品维度代理键‘;
comment on column product_dim.product_code is ‘产品编码‘;
comment on column product_dim.product_name is ‘产品名称‘;
comment on column product_dim.product_category is ‘产品类型‘;
comment on column product_dim.isdelete is ‘是否删除‘;
comment on column product_dim.version is ‘版本‘;
comment on column product_dim.effective_date is ‘生效日期‘;

-- 建立订单维度表    
create table order_dim (    
    order_sk bigserial,    
    order_number int, 
    isdelete boolean default false,	
    version int,  
    effective_date date
);

comment on table order_dim is ‘订单维度表‘;
comment on column order_dim.order_sk is ‘订单维度代理键‘;
comment on column order_dim.order_number is ‘订单号‘;
comment on column order_dim.isdelete is ‘是否删除‘;
comment on column order_dim.version is ‘版本‘;
comment on column order_dim.effective_date is ‘生效日期‘;

-- 建立日期维度表
create table date_dim (    
    date_sk bigserial,  
    date date,  
    month smallint,  
    month_name varchar(9),  
    quarter smallint,  
    year smallint 
); 

comment on table date_dim is ‘日期维度表‘;
comment on column date_dim.date_sk is ‘日期维度代理键‘;
comment on column date_dim.date is ‘日期‘;
comment on column date_dim.month is ‘月份‘;
comment on column date_dim.month_name is ‘月份名称‘;
comment on column date_dim.quarter is ‘季度‘;
comment on column date_dim.year is ‘年份‘;

-- 建立销售订单事实表    
create table sales_order_fact (    
    order_sk bigint,    
    customer_sk bigint,    
    product_sk bigint,    
    order_date_sk bigint, 
    year_month int,	
    order_amount decimal(10 , 2 )
)
partition by range (year_month)  
( partition p201601 start (201601) inclusive ,  
  partition p201602 start (201602) inclusive ,  
  partition p201603 start (201603) inclusive ,  
  partition p201604 start (201604) inclusive ,  
  partition p201605 start (201605) inclusive ,  
  partition p201606 start (201606) inclusive ,  
  partition p201607 start (201607) inclusive ,  
  partition p201608 start (201608) inclusive ,  
  partition p201609 start (201609) inclusive ,  
  partition p201610 start (201610) inclusive ,  
  partition p201611 start (201611) inclusive ,  
  partition p201612 start (201612) inclusive ,
  partition p201701 start (201701) inclusive ,  
  partition p201702 start (201702) inclusive ,  
  partition p201703 start (201703) inclusive ,  
  partition p201704 start (201704) inclusive ,  
  partition p201705 start (201705) inclusive ,  
  partition p201706 start (201706) inclusive ,  
  partition p201707 start (201707) inclusive ,  
  partition p201708 start (201708) inclusive ,  
  partition p201709 start (201709) inclusive ,  
  partition p201710 start (201710) inclusive ,  
  partition p201711 start (201711) inclusive ,  
  partition p201712 start (201712) inclusive  
                    end (201801) exclusive );  

comment on table sales_order_fact is ‘销售订单事实表‘;
comment on column sales_order_fact.order_sk is ‘订单维度代理键‘;
comment on column sales_order_fact.customer_sk is ‘客户维度代理键‘;
comment on column sales_order_fact.product_sk is ‘产品维度代理键‘;
comment on column sales_order_fact.order_date_sk is ‘日期维度代理键‘;
comment on column sales_order_fact.year_month is ‘年月分区键‘;
comment on column sales_order_fact.order_amount is ‘销售金额‘;

• TDS模式中的表是HAWQ内部表。
• 比源库多了一个日期维度表。数据仓库可以追踪历史数据，因此每个数据仓库都有日期时间相关的维度表。
• 为了捕获和表示数据变化，除日期维度表外，其它维度表比源表多了代理键、是否删除标志、版本号和版本生效日期四个字段。日期维度一次性生成数据后就不会改变，因此除了日期本身相关属性，只增加了一列代理键。
• 事实表由维度表的代理键和度量属性构成。目前只有一个销售订单金额的度量值。
• 由于事实表数据量大，事实表采取分区表。事实表中冗余了一列年月，作为分区键。之所以用年月做范围分区，是考虑到数据分析时经常使用年月分组进行查询和统计，这样可以有效利用分区消除提高查询性能。与rds.sales_order不同，这里显式定义了2017一年的分区。
• 出于同样的考虑，与RDS一样，TDS表也使用row存储格式和随机数据分布策略。

6. 装载日期维度数据

-- 生成日期维度表数据的函数
create or replace function fn_populate_date (start_dt date, end_dt date)  
returns void as  
$$  
declare  
    v_date date:= start_dt;	
    v_datediff int:= end_dt - start_dt;
begin  
    for i in 0 .. v_datediff
    loop    
        insert into date_dim(date, month, month_name, quarter, year)  
        values(v_date, extract(month from v_date), to_char(v_date,‘mon‘), extract(quarter from v_date), extract(year from v_date)); 
        v_date := v_date + 1;
    end loop;
    analyze date_dim;
end;  
$$  
language plpgsql; 

-- 执行函数生成日期维度数据
select fn_populate_date(date ‘2000-01-01‘, date ‘2020-12-31‘);

-- 查询生成的日期
select min(date_sk) min_sk, min(date) min_date, max(date_sk) max_sk, max(date) max_date, count(*) c from date_dim;

HAWQ实践（二）——搭建示例模型（MySQL、HAWQ）