HBase-Kettle-元数据维护说明

一、概述

Kettle通过读取HBase库中的pentaho_mappings表数据信息来获取HBase中表元信息,从而进行ETL过程。

二、示例流程说明

关系型数据表示例

以foodmart库中customer表作为示例表(表字段比较多,截取部分作为示例)

clipboard.png

建表语句如下:

CREATE TABLE `customer` (
    `customer_id` INT(11) NOT NULL DEFAULT '0',
    `account_num` DOUBLE NULL DEFAULT '0',
    `lname` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `fname` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `mi` VARCHAR(20) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `address1` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `address2` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `address3` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `address4` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `city` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `state_province` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `postal_code` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `country` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `customer_region_id` INT(11) NULL DEFAULT '0',
    `phone1` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `phone2` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `birthdate` DATE NULL DEFAULT NULL,
    `marital_status` VARCHAR(1) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `yearly_income` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `gender` VARCHAR(1) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `total_children` SMALLINT(6) NULL DEFAULT '0',
    `num_children_at_home` SMALLINT(6) NULL DEFAULT '0',
    `education` VARCHAR(30) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `date_accnt_opened` DATE NULL DEFAULT NULL,
    `member_card` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `occupation` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `houseowner` VARCHAR(1) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `num_cars_owned` SMALLINT(6) NULL DEFAULT '0',
    `fullname` VARCHAR(150) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`customer_id`),
    INDEX `account_num` (`account_num`),
    INDEX `fname` (`fname`),
    INDEX `lname` (`lname`),
    INDEX `num_cars_owned` (`num_cars_owned`),
    INDEX `num_children_at_home` (`num_children_at_home`),
    INDEX `postal_code` (`postal_code`),
    INDEX `region_id` (`customer_region_id`)
)

从上可知customer_id为主键值。

HBase创建表

在HBase中创建customer表以存储foodmart库中customer表数据,建表语句如下:

create 'customer ','data '

表名为customer,列簇为data,针对foodmart库中customer表的存储,指定customer_id为key,其他字段存储在列簇data中,即customer_id为1的记录在HBase中存储如下:

clipboard.png

pentaho_mappings

clipboard.png

pentaho_mappings的描述信息为:

COLUMN FAMILIES DESCRIPTION                                                                                                                                            
{NAME => 'columns', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}                                                            
{NAME => 'key', BLOOMFILTER => 'ROW', VERSIONS => '1', IN_MEMORY => 'false', KEEP_DELETED_CELLS => 'FALSE', DATA_BLOCK_ENCODING => 'NONE', TTL => 'FOREVER', COMPRESSION => 'NONE', MIN_VERSIONS => '0', BLOCKCACHE => 'true', BLOCKSIZE => '65536', REPLICATION_SCOPE => '0'}

其中key用来指定主键字段,columns用来指定非主键字段。
针对上述示例,需要在pentaho_mapping中存储对应的映射数据,操作语句如下:

put 'pentaho_mappings','customer,customer_mappings','columns:data,account_num,account_num_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,address1,address1_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,address2,address2_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,address3,address3_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,address4,address4_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,birthdate,birthdate_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,city,city_a','String'  
put 'pentaho_mappings','customer,customer_mappings','columns:data,country,country_a','String'  
put 'pentaho_mappings','customer,customer_mappings','columns:data,customer_region_id,customer_region_id_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,dateccnt_opened,dateccnt_opened_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,education,education_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,fname,fname_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,fullname,fullname_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,gender,gender_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,houseowner,houseowner_a','String'  
put 'pentaho_mappings','customer,customer_mappings','columns:data,lname,lname_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,marital_status,marital_status_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,member_card,member_card_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,mi,mi_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,num_cars_owned,num_cars_owned_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,num_childrent_home,num_childrent_home_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,occupation,occupation_a','String'  
put 'pentaho_mappings','customer,customer_mappings','columns:data,phone1,phone1_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,phone2,phone2_a','String'
put 'pentaho_mappings','customer,customer_mappings','columns:data,postal_code,postal_code_a','String'  
put 'pentaho_mappings','customer,customer_mappings','columns:data,state_province,state_province_a','String' 
put 'pentaho_mappings','customer,customer_mappings','columns:data,total_children,total_children_a','String'    
put 'pentaho_mappings','customer,customer_mappings','columns:data,yearly_income,yearly_income_a','String'   
put 'pentaho_mappings','customer,customer_mappings','key:customer_id','String'

此处将所有类型设定为String类型,具体类型根据实际情况进行修改调整,其中非主键字段设定如下:

put 'pentaho_mappings','customer,customer_mappings','columns:data,yearly_income,yearly_income_a','String'
参数 详细解释
pentaho_mappings 为操作表名
customer,customer_mappings customer为业务表名
customer_mappings为映射表名(名称可自定义)
columns:data,yearly_income,yearly_income_a columns为pentaho_mappings的列簇
data为业务表的列簇,此处为customer的列簇
yearly_income为业务表的字段
yearly_income_a为Kettle中用于输出的字段
String 为字段类型,还可为:String、Integer、Long、Float、Double、Boolean、Date、BigNumber、Serializable、Binary

主键字段设定如下:

put 'pentaho_mappings','customer,customer_mappings','key:customer_id','String'
参数 详细解释
pentaho_mappings 为操作表名
customer,customer_mappings customer为业务表名
customer_mappings为映射表名(名称可自定义)
key:customer_id key为pentaho_mappings的列簇
customer_id为业务表的主键字段,HBase中不显示,用于Kettle中输出
String 为字段类型,还可为:String、Integer、Long、Float、Double、Boolean、Date、BigNumber、Serializable、Binary

Kettle界面元素

HBase Input

clipboard.png

Username为新添加功能,用于指定连接用户,便于权限管理。
HBase Output

clipboard.png

Username为新添加功能,用于指定连接用户,便于权限管理。

评论

暂无评论