HAWQ取代传统数仓实践（二）-搭建示例模型（MySQL、HAWQ）

HAWQ取代传统数仓实践（二）一一搭建示例模型（MySQL、

HAWQ）

一、业务场景

本系列实验将应用HAWQ数据库，为一个销售订单系统建立数据仓库。本篇

说明示例的业务场景、数据仓库架构、实验环境、源和目标库的建立过程、测试数

据和日期维度的生成。后面陆续进行初始数据装载、定期数据装载、调度ETL工

作流自动执行、维度表技术、事实表技术、OLAP和数据可视化等实验。目的是演

示以HAWQ代替传统数据仓库的具体实现全过程。

1.操作型数据源

示例的操作型系统是一个销售订单系统，初始时只有产品、客户、销售订单三

个表，实体关系图如图1所示。

图1

这个场景中的表及其属性都很简单。产品表和客户表属于基本信息表，分别存

储产品和客户的信息。产品只有产品编号、产品名称、产品分类三个属性，产品编

号是主键，唯一标识一个产品。客户有六个属性，除客户编号和客户名称外，还包

含省、市、街道、邮编四个客户所在地区属性。客户编号是主键，唯一标识一个客

户。在实际应用中，基本信息表通常由其它后台系统维护。销售订单表有六个属性,

订单号是主键，唯一标识一条销售订单记录。产品编号和客户编号是两个外键，分

别引用产品表和客户表的主键。另外三个属性是订单时间、登记时间和订单金额。

订单时间指的是客户下订单的时间，订单金额属性指的是该笔订单需要花费的金额,

这些属性的含义很清楚。订单登记时间表示订单录入的时间，大多数情况下它应该

等同于订单时间。如果由于某种情况需要重新录入订单，还要同时记录原始订单的

时间和重新录入的时间，或者出现某种问题，订单登记时间滞后于下订单的时间，

这两个属性值就会不同。

源系统采用关系模型设计，为了减少表的数量，这个系统只做到了2NF。地区

信息依赖于邮编，所以这个模型中存在传递依赖。

2.销售订单数据仓库模型

使用以下步骤设计数据仓库模型。

1.选择业务流程。在本示例中只涉及一个销售订单的业务流程。

2.声明粒度。ETL处理时间周期为每天一次，事实表中存储最细粒度的订单事

务记录。

3.确认维度。显然产品和客户是销售订单的维度。日期维度用于业务集成，并为

数据仓库提供重要的历史视角，每个数据仓库中都应该有一个日期维度。订单

维度是特意设计的，用于说明退化维度技术。

4.确认事实。销售订单是当前场景中唯一的事实。

示例数据仓库的实体关系图如图2所示。

图2

二、数据仓库架构

"架构"是什么？这个问题从来就没有一个准确的答案。在软件行业，一种被普

遍接受的架构定义是指系统的一个或多个结构。结构中包括软件的构建(构建是指

软件的设计与实现)，构建的外部可以看到属性以及它们之间的相互关系。参考此

定义，这里把数据仓库架构理解成构成数据仓库的组件及其之间的关系，那么就有

了下面的数据仓库架构图。

图中显示的整个数据仓库环境包括操作型系统和数据仓库系统两大部分。操作

型系统的数据经过抽取、转换和装载(ETL)过程进入数据仓库系统。这里把ETL

过程分成了抽取和转换装载两个部分。抽取过程负责从操作型系统获取数据，该过

程一般不做数据聚合和汇总，物理上是将操作型系统的数据全量或增量复制到数据

仓库系统的RDS中。转换装载过程将数据进行清洗、过滤、汇总、统一格式化等

一系列转换操作，使数据转为适合查询的格式，然后装载进数据仓库系统的TDS

中。传统数据仓库的基本模式是用一些过程将操作型系统的数据抽取到文件，然后

另一些过程将这些文件转化成MySQL这样的关系数据库的记录。最后，第三部分

过程负责把数据导入进数据仓库。本例中的业务数据使用MySQL数据库存储。

RDS(RAWDATASTORES)是原始数据存储的意思。它的作用主要有三个：

作为数据缓冲区；提供细节数据；保留原始数据，便于跟踪和修正ETL的错误。

本例中的RDS使用HAWQ的HDFS外部表。

TDS(TRANSFORMEDDATASTORES)意为转换后的数据存储。这里存储真正

的数据仓库中的数据。本例中的TDS使用HAWQ内部表。

自动化调度组件的作用是自动定期重复执行ETL过程。作为通用的需求，所

有数据仓库系统都应该能够建立周期性自动执行的工作流作业。传统数据仓库一般

利用操作系统自带的调度功能(如Linux的cron或Windows的计划任务)实现

作业自动执行。本示例使用Falcon完成自动调度任务。

数据目录有时也被称为元数据存储•，它可以提供一份数据仓库中数据的清单。

一个好的数据目录是让用户体验到系统易用性的关键。HAWQ是数据库系统，自

带元数据表。

查询引擎组件负责实际执行用户查询。传统数据仓库中，它可能是存储转换后

数据的MySQL等关系数据库系统内置的查询引擎，还可能是以固定时间间隔向其

导入数据的OLAP立方体，如Essbasecube。HAWQ本身就是以一个强大的查询

引擎而存在，本示例使用HAWQ作为查询引擎正是物尽其用。

用户界面指的是最终用户所使用的接口程序。可能是一个GUI软件，如BI套

件的中的客户端软件，也可能就是一个浏览器。本示例的用户界面使用Zeppelin。

三、实验环境

1.硬件环境

四台VMware虚机组成的Hadoop集群，每台机器配置如下:

•15KRPMSAS100GB

•Intel(R)Xeon(R)E5-2620v2@2.10GHz,双核双CPU

•8G内存，8GSwap

・lOOOOMb/s虚拟网卡

2.软件环境

Linux：CentOSrelease6.4,核心2.6.32・358.el6.x86_64

Ambari：2.4.1

Hadoop：HDP2.5.0

HAWQ：2.1.1.0

HAWQPXF：3.1.1

MySQL5.6.14

HDP与HAWQ的安装部署参考"用HAWQ轻松取代传统数据仓库（二）

安装部署”。表1汇总了各主机的角色。

主机名IP地址角色

hdpl172.16.1.124HAWQSegment

hdp2172.16.1.125HAWQStandbyMaster、HAWQSegment

hdp3172.16.1.126HAWQPrimaryMaster、HAWQSegment

hdp4172.16.1.127HAWQSegment、MySQL

表1

四、HAWQ相关配置

1.创建客户端认证

编辑master上的/data/hawq/master/pg_hba.conf文件，添力口dwtest用户，

如图4所示。

[gpadmin@hdp3more/data/hawq/master/pg_hba.conf

localallgpadminident

hostallgpadmin127.0.0.1/28trust

hostallgpadmin::1/128trust

hostallgpadmin172.16.1.126/32trust

hostallgpadminfe80::250:56ff:fea5:526f/128trust

hostallgpadmirlittp：j强1.鸣式强加夕的可丫°623trust

hostallgpadmin172.16.1.127/32trust

hostallgpadmin172.16.1.0/24trust

hostallwxy172,16・1・0/24md5

hostalldwtest172.16.1.0/24md5

图4

2.设置并发连接参数

-设置参数

hawqconfig-cmax__connections-v100

hawqconfig-cseg_max__connections-v1000

hawqconfig-cmax_prepared__transactions-v200

--重启HAWQ

hawqrestartcluster

--查看配置

hawqconfig-smax_connections

hawqconfig-sseg_max_connections

hawqconfig-smax_prepared_transactions

查看参数值如图5所示。

[gpadmin@hdp3hawqconfig-smax_connections

GUC:|max_connections|

ValueI

[gpadmin@hdp3hawqconfig-sseg_max_connections

GUC:Iseg_max_connectionsI

Value：hl(D0O//blc)g.csdn.net/w|y0623

[gpadmin@hdp3hawqconfig-smax_prepared_transactions

GUC:Imax_prepared_transactionsI

Value:200

[qpadinin@hdp3

图5

(1)max_connections

限制master允许的最大客户端并发连接数，缺省值是1280。在HAWQ系统

中，用户客户端只能通过master实例连接到系统。此参数的值越大，HAWQ需要

的共享内存越多。shared_buffers参数设置一个HAWQsegment实例使用的共享内

存缓冲区的，缺省值是125MB,最小值是128KB与16KB*max_connections的较

大者。如果连接HAWQ时发生共享内存分配的错误，可以尝试增加SHMMAX或

SHMALL操作系统参数的值，或者降低shared_buffers或者max_connections参数

的值解决此类问题。

(2)seg_max_connections

限制segment允许master发起的最大并发连接数，缺省值是1280。该参数应

该设置为max_connections的5-10倍。增加此参数时必须同时增加

max_prepared_transactions参数的值。与master类似，此参数的值越大，HAWQ

需要的共享内存越多。

(3)max_prepared_transactions

设置面时处于准"状态的事务数，缺省值为250。HAWQ内部使用准备事务保

证跨segment的数据完整性。

3.创建数据库用户

(1)用gpadmin连接HAWQ,建立用户dwtest,授予建库权限。

--创建用户

createroledwtestwithpassword'123456'logincreatedb;

--查看用户

\dg

查看用户如图6所示。

gpadmin=#\dg

Listofroles

Rolename|Attributes|Memberof

----------------+----------；--------,-------------------------------------+----------

dwtest|Crea超出‘『1咱csdn-net/wzy0623।

gpadmin|Superuser^CreaterolezCreateDB|

wxy|Superuser|

图6

(2)测试登录

psql-Udwtest-dgpadmin-hhdp3

--查看数据库

\1

连接成功后，查看数据库如图7所示。

[gpadmin@hdp3~]$psql-Udwtest-dgpadmin-hhdp3

Passwordforuserdwtest:

psql(8.2.15)

Type"help"forhelp.

gpadmin=>

gpadmin=>

gpadmin=>\1

httjLisIblog.dadhabaQeszyO623

NameIOwnerIEncodingIAccessprivileges

_1__________十1____________11____________________

gpadminIgpadminIUTF8|

postgresIgpadmin|UTF8|

templateOIgpadminIUTF8|

templatelIgpadminIUTF8|

(4rows)

图7

4.创建资源队列

(l)将缺省的pg_default的资源限制由50%改为20%,同时将过度使用因子设

置为5。

alterresourcequeuepg_defaultwith

(memory_limit_cluster=20%,core_limit_cluster=20%,resource_overcommit_fa

ctor=5);

(2)建立一个dwtest用户使用的专用队列，资源限制由80%,同时将过度使用

因子设置为2。

createresourcequeuedwtest_queuewith

(parent='pg_root',memory_limit_cluster=80%Jcore_limit_cluster=80%Jres

ource_overcommit_factor=2);

(3)查看资源队列配置，结果如图8所示。

selectrsqname,

parentoid^

activestats,

memorylimit,

corelimit,

resovercommit,

allocpolicy^

vsegresourcequota^

nvsegupperlimitj

nvseglowerlimitj

nvsegupperlimitperseg,

nvseglowerlimitperseg

frompg_resqueue;

rsqnameIparentoidIactivestats|memorylimit|corelimitIresovercommitIallocpolicyIvseqresourcequotaInvs

egupperlimitInvseglowerlimitInvsegupperlinitpersegInvseglowerlimitperseg

pg_rootI0I-1|100%|100*|2IevenI

~0101.°.°

|dwtest_queueI9800I-20I80t-2Ieven「•，:~|

iunon0

pg_defaultI9800I20|20%|20%I5IevenImem:256nb

0I0I0I0

(3rows)

图8

(4)用gpadmin将dwtest用户的资源队列设置为新建的dwtest_queue,结果如

图9所示。

--修改用户资源队列

alterroledwtestresourcequeuedwtest_queue;

--查看用户资源队列一

selectrolname,rsqnamefrompg_rolesJpg_resqueue

wherepg_roles.rolresqueue=pg_resqueue.oid;

rolname|rsqname

--------+-----------

dwtest|dwtest_queue

pg

wxyaerauiL

pg

gpadmindefault

(3rows)

图9

假设其他用户都使用缺省的pg_default队列。采用以上定义，工作负载通过资

源队列划分如下：

・如果没有活跃用户使用pg_default队列时，dwtest用户可以使用100%的资

源。

・如果有同时使用pg_default队列的其它用户，则dwtest用户使用80%,其它

用户使用20%资源。

・如果没有dwtest用户的活跃任务，其它使用pg_default队列的用户可以使用

100%的资源。

5.配置资源

(1)查看集群所有节点都已启动，结果如图10所示。

select*fromgp_segment_configuration;

roleIstatusIportIhostnameIaddressIdescription

+---------------+-----------+------------------+-------------------------+---------------------

3

m5432|hdp2I172.16.1.125

p5432|hdp3Ihdp3

172.16.1.126

p1搬篇rjd.耀zy0623|

p172.16.1.125

40000|hdp4|172.16.1.127

P

40000|hdplI172.16.1.124

图10

所有节点的状态都应该是启动状态(status='u')0

hawq_rm_rejectrequest_nseg_limit参数保持缺省值0.25,就是说现有集群的全部4

个segment中如果有两个宕机，则HAWQ的资源管理器将直接拒绝查询的资源请

求。

(2)资源管理使用缺省的独立模式，如图11所示。

在该模式下，HAWQ使用集群节点资源时，不考虑其它共存的应用，HAWQ

假设它能使用所有segment的资源。对于专用HAWQ集群，独立模式是可选的方

案。

hawqconfig-shawq_global_rm_type

[gpadmin@hdp3$hawqconfig-5hawRglobalrm/type

GUC:hawq_global_rm_type

Valuehtt)n6ririog.csdn.net/wzyOf23

[gpadmin@hdp3

图11

(3)设置segment资源配额，如图12所示。

物理内存8G,双核双CPU,共4个虚拟CPU核。所以每个segment使用的内

存与CPU核数配额分别配置为8GB和4,最大限度使用资源。

hawqconfig-shawq_rm_memory_limit_perseg

hawqconfig-shawq_rm_nvcore_limit__perseg

[gpadmin@hdp3

GUC

Value

[gpadmin@hdp3

GUC

Value

[gpadmin@hdp3

图12

所有资源队列中虚拟段的资源限额均为缺省的256MB,每个segment可以分

配32个虚拟段。并且8GB是256MB的32倍，每核2GB内存，这种配置防止形

成资源碎片。

6.在HDFS上创建HAWQ外部表对应的目录

su-hdfs-c'hdfsdfs-mkdir-p/data/ext'

su-hdfs-c"hdfsdfs-chown-Rgpadmin:gpadmin/data/ext

su-hdfs-c'hdfsdfs-chmod-R777/data/ext'

su-hdfs-c,hdfsdfs-chmod-R777/user'

su-hdfs-c'hdfsdfs-Is/data'

结果如图13所示。

[root@hdp2〜]#su-hdfs-c1hdfsdfs-mkdir-p/data/ext,

[root@hdp2su-hdfs-c'hdfsdfs-chown-Rgpadmin:gpadmin/data/ext,

[root@hdp2su-hdfs-c'hdfsdfs-chmod-R777/data/ext1

[root@hdp2#su-hdfs-c'hdfsdfs-chmod-R777/user1

[root@hdp2su-hdfshctiJhdfd(Mgf6sdd.su/da-ta0623

Found1items

jdrwxrwxrwx-gpadmingpadmin02017-05-0510:48/data/ext

[root@hdp2

图13

五、创建测试数据库

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

(1)执行下面的SQL语句在MySQL中建立源数据库表。

--建立源数据库

dropdatabaseifexistssource;

createdatabasesource;

usesource;

--建立客户表

createtablecustomer(

customer_numberintnotnullauto_incrementprimarykeycomment'客

户编号，主键匚,

customer_namevarchar(50)comment'客户名称，，

customer_street_addressvarchar(50)comment'客户住址

customer_zip_codeintcomment'邮编

customer_cityvarchar(30)comment'所在城市二

customer_statevarchar(2)comment'所在省份，

)；一

--建立产品表

createtableproduct(

product_codeintnotnullauto_incrementprimarykeycomment'产品

编码，主键‘，一

product_namevarchar(30)comment，产品名称]

product_categoryvarchar(30)comment'产品类型’

);

--建立销售订单表

createtablesales_order(

order_numberintnotnullauto_incrementprimarykeycomment'订单

号，主键

customer_numberintcomment'客户编号

product_codeintcomment'产品编码

order_datedatetimecomment'订单日期

entry_datedatetimecomment'登记日期

order_amountdecimal(10,2)comment，销售金额

foreignkey(customer__number)

referencescustomer(customer_number)

ondeletecascadeonupdatecascade,

foreignkey(product_code)

referencesproduct(product_code)

ondeletecascadeonupdatecascade

);

(2)执行下面的SQL语句生成源库测试数据。

usesource;

--生成客户表测试数据

insertintocustomer

(customer_name,customer_st:reet:_address,customer_zip_c:ode)

customer_city,customer_state)

values

(1reallylargecustomers1,'7500louisedr.',17050,'mechanicsburg','pa

，),

(1smallstores','2500woodlandst.,,17055,'Pittsburgh','pa'),

('mediumretailers','1111ritterrd.*,17055,'Pittsburgh','pa'),

('goodcompanies''9500scottst.1^17050^'mechanicsburg''pa')^

('wonderfulshops1'3333rossmoynerd.,^17050^'mechanicsburg','pa*)^

('loyalclients','7070ritterrd.',17055JPittsburgh'Jpa'),

('distinguishedpartners1,'9999scottst.1,17050,'mechanicsburg''pa');

--生成产品表测试数据

insertintoproduct(product_name,product_category)

values

(,harddiskdrive','storage'),

('floppydrive'1storage')

('ledpanel''monitor');

--生成100条销售订单表测试数据

dropprocedureifexistsgenerate__sales_order_data;

delimiter//

createproceduregenerate_sales_order__data()

begin

droptableifexiststemp_sales__order_data;

createtabletemp_sales_order_dataasselect*fromsales_orderwhe

re1=0;

set@start__date:=unix_timestamp('2016-03-01');

set@end_date:=unix_timestamp('2016-07-011);

set@i:=1;

while@i<=100do

set@customer_number:=floor(l+rand()*6);

set@product_code:=floor(l+rand()*2);

set@order_date:=from_unixtime(@start_date+rand()*(@end__d

ate-@start_date));

set@amount:=floor(1000+rand()*9000);

insertintotemp_sales_order_datavalues(@i,@customer_number,@

product_code,@order_date,@order_date,@amount);

set@i:=@i+l;

endwhile;

truncatetablesales_order;

insertintosales__order

selectnull,customer_number,product_code,order_date,entry_date,opde

r__amountfromtemp_sales_order_dataorderbyorder_date;

commit;

end

//

delimiter;

callgenerate_sales_order_data();

说明：创建了一个MySQL存储过程生成100条销售订单测试数据。为了模拟

实际订单的情况，订单表中的客户编号、产品编号、订单时间和订单金额都取一个

范围内的随机值，订单时间与登记时间相同。因为订单表的主键是自增的，为了保

持主键值和订单时间字段的值顺序保持一致，引入了一个名为

temp_sales_order_data的表，存储中间临时数据。在后面章节中都是使用此方案

生成订单测试数据。

（3）创建读取源数据的用户

createuser'dwtestidentifiedby'123456,;

grantselectonsource.*to'dwtest;

2.创建目标库及其模式

(1)用dwtest用户连接HAWQ

psql-Udwtest-dgpadmin-hhdp3

(2)建立数据库dw

createdatabasedw;

(3)在dw库中建立模式rds和tds,结果如图14所示。

--连接dw数据库

\cdw

--创建ext模式

createschemaext;

--创建rds模式

createschemards;

--创建tds模式

createschematds;

--查看模式

\dn

gpadmin=>createdatabasedw;

CREATEDATABASE

gpadmin=>\cdw

Youarenowconnectedtodatabase"dw"asuser“dwtest”.

dw=>createschemaext;

CREATESCHEMA

dw=>createschemards;

CREATESCHEMA

dw=>createschematds;

CREATESCHEMA

dw=>\dn

Listofschemas

Name,I.Owner.,

htzcsdn.net/wzy0623

ext1dwtest|

hawq_toolkitTgpadmin

information_5chema1gpadmin

pg_aoseg1gpadmin

pg_bitmapindex1gpadmin

pg_catalog1gpadmin

pg_toast1gpadmin

public1gpadmin

rdsTdwtest

tdsidwtest

(10rows)

图14

（4）设置模式查找路径，结果如图15所示。

--修改数据库的模式查找路径

alterdatabasedwsetsearch_pathtoext,rds,tds;

--重新连接dw数据库

\cdw

--显示模式查找路径

showsearch_path;

dw=>alterdatabasedwsetsearch_pathtoext,rds,tds;

ALTERDATABASE

dw=>\cdw

Youarenowconnectedtodatabase"dw"asuser"dwtest".

dw=>showsearc

卜比甫"/blog.csdn.net/wzy0623

search_path

|ext,rds,tds

(1row)

图15

HAWQ的模式是数据库中对象和数据的逻辑组织。模式允许在一个数据库中有

多个同名的对象，如表。如果对象属于不同的模式，同名对象之间不会冲突。使用

schema有如下好处：

・方便管理多个用户共享一个数据库，但是又可以互相独立。

•方便管理众多对象，更有逻辑性。

•方便兼容某些第三方应用程序，如果创建对象时是带schema的。

每个HAWQ会话在任一时刻只能连接一个数据库，因此将RDS和TDS对象存

放单独的数据库显然是不合适的。这里在dw库中创建了ext、rds、tds三个模式。

在前面描述数据仓库架构时只提到了RDS和TDS,并指出本示例的RDS使用

HAWQ的HDFS外部表，为什么这里创建了三个模式呢？究其原因有如下：

•Sqoop可以将MySQL数据导入到HDFS或hive,但目前还没有命令行工具可

以将MySQL数据直接导入到HAWQ数据库中。所以不得不将缓冲数据存储

到HDFS,再利用HAWQ的外部表进行访问。

•如果只创建两个模式分别用作RDS和TDS,则会带来性能问题。在变化数据

捕获（CDC）时需要关联RDS和TDS的表，而HAWQ的外部表和内部表关联

查询的速度是很慢的，在数据量非常大的情况下，更是慢到不能忍受。

•通常维度数据量比事实数据量小得多。在这个前提下，用EXT模式存储直接

从MySQL导出的外部数据，包括全部维度数据和增量的事实数据，然后将这

些数据装载进RDS模式内部表中。这一步装载的数据量并不是很大，而且没

有关联逻辑，都是简单的单表查询与数据插入。在装载TDS内部表时，仍然

关联RDS与TDS的表，但这两个模式中的表都是内部表，查询速度是可接受

的。

这里使用三个schema来划直接外部数据、源数据存储和多维数据仓库的对象,

不但逻辑上非常清晰，而且兼顾了ETL的处理速度。

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

(1)用HAWQ管理员用户授予dwtest用户在dw库中创建外部表的权限

psql-ddw-hhdp3-c"grantallonprotocolpxftodwtest"

如果不授予相应权限，创建外部表时会报以下错误：

ERROR:permissiondeniedforexternalprotocolpxf

(2)创建HAWQ外部表

-设置模式查找路径

setsearch_pathtoext;

--建立客户外部表

createexternaltablecustomer

(

customer_numberint,

customer_namevarchar(30),

customer_street_addressvarchar(30),

customer__zip_codeint,

customer_cityvarchar(30)

customer_statevarchar(2)

)一

location('pxf://mycluster/data/ext/customer?profile=hdfstextsimple')

format'text'(delimiter=e'J);

commentontablecustomeris，客户外部表'；

commentoncolumncustomer.customer_numberis‘客户编号’；

commentoncolumncustomer.customer_nameis'客户姓名'；

commentoncolumncustomer.customer__street_addressis‘客户地址’；

commentoncolumncustomer.customer_zip_codeis'客户邮编'；

commentoncolumncustomer.customer_cityis'客户所在城市'；

commentoncolumncustomer.customer_stateis'客户所在省份

--建立产品外部表

createexternaltableproduct

(

product_codeint,

product_namevarchar(30)

product_categoryvarchar(30)

)

location('pxf://mycluster/data/ext/product?profile=hdfstextsimple')

format'text'(delimiter=e','

commentontableproductis'产品外部表’；

commentoncolumnproduct.product_codeis'产品编码'；

commentoncolumnproduct.product_nameis'产品名称‘；

commentoncolumnproduct.product__categoryis'产品类型’；

--建立销售订单外部表

createexternaltablesales_order

(

order_numberint,

customer_numberint,

product_codeint,

order_datetimestamp,

entry_datetimestamp,

order_amountdecimal(10,2)

)

location('pxf://mycluster/data/ext/sales_order?profile=hdfstextsimple')

format'text'(delimiter=e','null='null');

commentontablesales_orderis1销售订单外部表，;

commentoncolumnsales_order.order__numberis'订单号'；

commentoncolumnsales_order.customer_numberis‘客户编号’；

commentoncolumnsales_order.product_codeis'产品编码’；

commentoncolumnsales_order.order_dateis'订单日期’；

commentoncolumnsales_order.entry__dateis'登i己日期'；

commentoncolumnsales_order.order_amountis'销售金额'；

说明:

外部表结构与MySQL里的源表完全对应，其字段与源表相同。

•PXF外部数据位置指向前面（四.6）创建的HDFS目录。

•文件格式使用逗号分隔的简单文本格式，文件中的'null，字符创代表数据库中

的NULL值。下一篇说明的数据初始装载时会看到，为了让EXT的数据文件

尽可能的小，Sqoop使用了压缩选项，而hdfstextsimples属性的PXF外部表

能自动正确读取Sqoop缺省的gzip压缩文件。

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

-设置模式查找路径

setsearch_pathtords;

--建立客户原始数据表

createtablecustomer

(

customer_numberint,

customer__namevarchar(30)

customer_street_addressvarchar(30),

customer_zip_codeint,

customer_cityvarchar(30),»

customer_statevarchar(2)

);

commentontablecustomeris'客户原始数据表'；

commentoncolumncustomer.customer__numberis'客户编号'；

commentoncolumncustomer.customer_nameis'客户姓名’；

commentoncolumncustomer.customer_street_addressis'客户地址

commentoncolumncustomer.customer_zip_codeis'客户邮编'；

commentoncolumncustomer.customer_cityis'客户所在城市'；

commentoncolumncustomer.customer_stateis'客户所在省份'；

--建立产品原始数据表

createtableproduct

(

product_codeint,

product_namevarchar(30)>

product_categoryvarchar(30)

)；

commentontableproductis1产品原始数据表

commentoncolumnproduct.product_codeis'产品编码

commentoncolumnproduct.product_nameis'产品名称'；

commentoncolumnproduct.product_categoryis'产品类型’；

--建立销售订单原始数据表

createtablesales_order

(一

ordernumberint,

customer_numberint,

product_codeint,

order_datetimestamp,

entry_datetimestamp,

order_amountdecimal(10,2)

)一

partitionbyrange(entry_date)

(start(date'2016-01-01')inclusive

end(date,2018-01-01*)exclusive

every(interval'1month'));

commentontablesales_orderis，销售订单原始数据表’；

commentoncolumnsales_order.order_numberis'订单号'；

commentoncolumnsales_order.customer_numberis'客户编号’；

commentoncolumnsales_order.product_codeis'产品编码

commentoncolumnsales_order.order_dateis'订单日期'；

commentoncolumnsales_order.entry_dateis'登i己日期'；

commentoncolumnsales_order.order_amountis'销售金额

说明:

•RDS模式中的表是HAWQ内部表。

•RDS模式中表数据来自EXT表，并且是原样装载，不需要任何转换，因此其

表结构与EXT中的外部表一致。

•HAWQ支持row和parquet两种数据存储格式。如果单纯从性能方面考虑，

似乎parquet列格式更适合数据仓库应用。这里使用缺省的row格式，只因

为注言到文档中这样一句话："HAWQdoesnotsupportusingALTERTABLEto

ADDorDROPacolumninanexistingParquettable."。我还没遇到哪个数据库

表创建完后就再也不用增删字段的情况。关于行列存储的选择，参见“大数据

存取的选择:行存储还是列存储?”

•为了保持查询弹性使用资源和更好的数据本地化，使用缺省的随机数据分布策

略，而没有使用哈希分布。关于HAWQ表数据的存储与分布，参见“用HAWQ

轻松取代传统数据仓库（七）一一存储分布

•RDS是实际上是原始业务数据的副本，维度数据量小，可以覆盖装载全部数

据，而事实数据量大，需要追加装载每天的新增数据。因此事实表采取分区表,

每月数据一分区，以登记日期作为分区键，预创建2017年一年的分区。

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

-设置模式查找路径

setsearch__pathtotds;

--建立客户维度表

createtablecustomer_dim(

customer_skbigserial,

customer_numberint,

customer_namevarchar(50),

customer_street_addressvarchar(50),

customer_zip_codeint,

customer_cityvarchar(30)

customer_statevarchar(2)

isdeletebooleandefaultfalse,

versionint,

effective_datedate

);

commentontablecustomer_dimis1客户维度表'；

commentoncolumncustomer_dim.customer_skis，客户维度代理键'；

commentoncolumncustomer_dim.customer_numberis'客户编号'；

commentoncolumncustomer_dim.customer_nameis'客户姓名

commentoncolumncustomer_dim.customer_street_addressis'客户/也址

commentoncolumncustomer_dim.customer_zip_codeis'客户邮编'；

commentoncolumncustomer_dim.customer_cityis'客户所在城市

commentoncolumncustomer_dim.customer_stateis'客户所在省份'；

commentoncolumncustomer_dim.isdeleteis'是否删J除‘；

commentoncolumncustomer_dim.versionis'版本'；

commentoncolumncustomer_dim.effective_dateis'生效日期'；

--建立产品维度表

createtableproduct_dim(

product_skbigserial,

product_codeint,

product_namevarchar(30),

product_categoryvarchar(30),

isdeletebooleandefaultfalse,

versionint,

effective_datedate

)；一

commentontableproduct_dimis，产品维度表

commentoncolumnproduct_dim.product_skis1产品维度代理键

commentoncolumnproduct_dim.product_codeis'产品编码'；

commentoncolumnproduct__dim.product_nameis'产品名称‘；

commentoncolumnproduct__dim.product_categoryis'产品类型’；

commentoncolumnproduct_dim.isdeleteis1是否删除'；

commentoncolumnproduct_dim.versionis'版本

commentoncolumnproduct_dim.effective_dateis'生效日期'；

--建立订单维度表

createtableorder_dim(

order_skbigserial,

order_numberint,

isdeletebooleandefaultfalse,

versionint,

effectivedatedate

）；

commentontableorder_dimis，订单维度表’；

commentoncolumnorder__dim.order_skis'订单维度代理键’；

commentoncolumnorder__dim.order_numberis'订单号'；

commentoncolumnorderdim.isdeleteis'是否删除'；

commentoncolumnorderdim.versionis'版本'；

commentoncolumnorder__dim.effective_dateis'生效日期

--建立日期维度表

createtabledate_dim(

date__skint,

datedate,

monthsmallintj

month_namevarchar(9)

qu