基于hadoop生态圈的数据仓库实践 —— OLAP与数据可视化(三)资料

基于hadoop生态圈的数据仓库实践 —— OLAP与数据可视化(三)资料

基于hadoop生态圈的数据仓库实践——OLAP与数据可视化（三）

三、Impala OLAP实例

本节使用前面销售订单的例子说明如何使用Impala做OLAP类型的查询，以及实际遇到的问题及解决方案。为了处理SCD和行级更新，我们前面的ETL使用了Hive ORCFile 格式的表，可惜到目前为止，Impala还不支持ORCFile。用Impala查询ORCFile表时，错误信息如下图所示。

这是一个棘手的问题。如果我们再建一套和dw库中表结构一样的表，但使用Impala 能够识别的文件类型，如Parquet，又会引入两个新的问题：一是CDH 5.7.0的Hive版本是1.1.0，有些数据类型不支持，如date。另一个更大的问题是增量装载数据问题。dw库的维度表和事实表都有update操作，可Impala只支持数据装载，不支持update和delete等DML 操作。如果每天都做insert overwrite覆盖装载全部数据，对于大数据量来说很不现实。

尽管Impala不支持update语句，但通过使用HBase作为底层存储可是达到同样的效果。相同键值的数据被插入时，会自动覆盖原有的数据行。这样只要在每天定期ETL时，记录当天产生变化（包括修改和新增）的记录，只将这些记录插入到Impala表中，就可以实现增量数据装载。这个方案并不完美，毕竟冗余了一套数据，既浪费空间，又增加了ETL 的额外工作。其实前面ETL的Hive表也可以使用HBase做底层存储而不用ORCFile文件类型，利用HBase的特性，既可以用Hive做ETL，又可以用Impala做OLAP，真正做到一套数据，多个引擎。这个方案也需要一些额外的工作，如安装HBase，配置Hive、Impala 与HBase协同工作等，它最主要的问题是Impala在HBase上的查询性能并不适合OLAP场景。

如果没有累积快照事实表，可以对相对较小的维度表全量覆盖插入，而对大的事实表增量插入，这也是本实例中采用的方案。也就是说，为了保证查询性能和数据装载可行性，牺牲了对累积快照事实表的支持。希望Impala尽快支持ORCFile并能达到和Parquet同样的性能，这样就可以省却很多麻烦。

1. 建立olap库、表、视图

用下面的查询语句从MySQL的hive库生成建表文件：

[sql] view plain copy 在CODE上查看代码片派生到我的代码片

基于hadoop生态圈的数据仓库实践 —— OLAP与数据可视化(三)资料

use hive;

select concat('create table ', t1.tbl_name, ' (',group_concat(concat(t2.column_name,' ',t2.type_name) order by t2.integer_idx),') stored as parquet;') into outfile '/data/hive/create_table.sql'

from (select t1.tbl_id,

t1.tbl_name

from TBLS t1, DBS t2

where t1.db_id = t2. db_id

and http://www.77cn.com.cn = 'dw'

and tbl_type <> 'VIRTUAL_VIEW'

and (tbl_name like '%dim' or tbl_name like '%fact')) t1,

(select case when v.column_name = 'date' then 'date1' else v.column_name end column_name,

replace(v.type_name,'date','timestamp') type_name,

v.integer_idx,

t.tbl_id

from COLUMNS_V2 v,

CDS c,

SDS s,

TBLS t

where v.cd_id = c.cd_id

and c.cd_id = s.cd_id

and s.sd_id = t.sd_id) t2

where t1.tbl_id = t2.tbl_id

group by t1.tbl_name;

生成的create_table.sql文件包含所有维度表和事实表的建表语句，例如：

[sql] view plain copy 在CODE上查看代码片派生到我的代码片

create table product_dim (product_sk int,product_code int,product_name varchar(30),product_category varchar(30),version int,effective_date timestamp,expiry_date timestamp) stored as parquet;

用下面的查询语句从MySQL的hive库生成建视图文件：

[sql] view plain copy 在CODE上查看代码片派生到我的代码片

use hive;

select concat('create view ', t1.tbl_name, ' as ', replace(replace(t1.view_original_text,'\n',' '),' date,',' date1,'), ';')

into outfile '/data/hive/create_view.sql'

from TBLS t1, DBS t2

where t1.db_id = t2.db_id

and http://www.77cn.com.cn = 'dw'

and t1.tbl_type = 'VIRTUAL_VIEW';

生成的create_view.sql文件包含所有建立视图的语句，例如：

[sql] view plain copy 在CODE上查看代码片派生到我的代码片

create view allocate_date_dim as SELECT date_sk, date, month, month_name, quarter, year, promo_ind FROM date_dim;

从Hive命令行执行建立库、表、视图的脚本：

基于hadoop生态圈的数据仓库实践 —— OLAP与数据可视化(三)资料

[plain] view plain copy 在CODE上查看代码片派生到我的代码片

hive -e 'create database olap;use olap;source /data/hive/create_table.sql;source /data/hive/create_view.sql;'

2. 初始装载数据

用下面的查询语句从MySQL的hive库生成装载数据脚本文件：

[sql] view plain copy 在CODE上查看代码片派生到我的代码片

use hive;

select concat('insert overwrite table olap.', t1.tbl_name, ' select ', group_concat(t2.column_name order by t2.integer_idx),' from dw.', t1.tbl_name ,';') into outfile '/data/hive/insert_table.sql'

from (select t1.tbl_id,

t1.tbl_name

from TBLS t1, DBS t2

where t1.db_id = t2. db_id

and http://www.77cn.com.cn = 'dw'

and tbl_type <> 'VIRTUAL_VIEW'

and (tbl_name like '%dim' or tbl_name like '%fact')) t1,

(select v.column_name,

replace(v.type_name,'date','timestamp') type_name,

v.integer_idx,

t.tbl_id

from COLUMNS_V2 v,

CDS c,

SDS s,

TBLS t

where v.cd_id = c.cd_id

and c.cd_id = s.cd_id

and s.sd_id = t.sd_id) t2

where t1.tbl_id = t2.tbl_id

group by t1.tbl_name;

生成的insert_table.sql文件包含所有insert olap表的语句，例如：

[sql] view plain copy 在CODE上查看代码片派生到我的代码片

insert overwrite table olap.product_dim select product_sk,product_code,product_name,product_category,version,effective_date,expiry_date from dw.product_dim;

从Hive命令行执行初始装载数据的脚本：

[plain] view plain copy 在CODE上查看代码片派生到我的代码片

hive -e 'source /data/hive/insert_table.sql;'

3. 修改销售订单定期装载脚本

首先将dw和olap库中的事实表变更为动态分区表，这样在向olap库中装载数据时，或是在olap库上进行查询时，都可以有效地利用分区消除来提高性能。这里只修改了每日 …… 此处隐藏：16214字，全部文档内容请下载后查看。喜欢就下载吧 ……