HBase-Kettle-元数据维护说明
文章目录
一、概述
Kettle通过读取HBase库中的pentaho_mappings表数据信息来获取HBase中表元信息,从而进行ETL过程。
二、示例流程说明
关系型数据表示例
以foodmart库中customer表作为示例表(表字段比较多,截取部分作为示例)
建表语句如下:
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中存储如下:
pentaho_mappings
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
Username为新添加功能,用于指定连接用户,便于权限管理。
HBase Output
Username为新添加功能,用于指定连接用户,便于权限管理。

评论
登录后参与技术讨论,分享你的经验与见解。
登录 / 注册暂无评论