**需求:想在phoenix上维护两张宽表,一张作为即席查询使用,只有一天的数据、一张作为历史表。
宽表的特点是:由多个表组合而成,但是每张表的到数时间不一致,有的表先到,有的表可能隔天才到。
想要达到的效果:即席查询用的宽表是来一张表就加载一张表的数据,没来的等来了再加载,中间过程有查询的时候,查询结果是:已经更新的字段(已经到数的表字段)和未更新的字段(没有到数的表字段)
要求:即席查询的宽表中的数据,既要在impala、hive中可查,也要在phoenix、habse中可查。
**
目前方案:
假设:宽表由2张表组成:table1和table2
1)每张小表(组成宽表的每张表)先通过调度加载到hive里面
ods.table1(partition=‘202108010’)、ods.table2(partition=‘202108010’)
2)在hive里面创建每张小表与phoenix的映射表(表结构与第一步的表结构一致)
目的:为了能够在phoenix中操作hive中的表数据,因为宽表中插入数据的时候要用phoenix,而不用hive,因为hive如果只插入某几个字段的时候,其他的字段会为null,而需求是更新表字段,没有到数的字段,保持原貌。
hive>:
drop table if exists ods.table1_phoenix;
create table ods.table1_phoenix(
`id` string comment '用户id',
`name` string comment '年龄',
`etl_date` timestamp comment 'ETL执行时间'
) comment '用户表1'
STORED BY 'org.apache.phoenix.hive.PhoenixStorageHandler'
TBLPROPERTIES (
"phoenix.table.name" = "test.phoenix_table1",
"phoenix.zookeeper.quorum" = "hdp1,hdp2,hdp3",
"phoenix.zookeeper.znode.parent" = "/hbase",
"phoenix.zookeeper.client.port" = "2181",
"phoenix.rowkeys" = "id, name",
"phoenix.column.mapping" = "id:ID, name:NAME, etl_date:ETL_DATE"
);
--将ods.table1的数据导入 ods.table1_phoenix
--这时候,phoenix表 test.phoenix_table1中就会有数据
inser into table ods.table1_phoenix
select id,name,etl_date from ods.table1 where partiton='202108010';
注:table2也是执行相同的步骤
3)使用phoenix创建即系查询宽表
phoenix:>
create table test.user_hbase_wide1(
id varchar primary key,
"a".name varchar,
"a".sex varchar,
"a".age varchar,
"a".etl_date varchar
)COLUMN_ENCODE_BYTES=0;
--此时,phoenix中能查到这个宽表
--将test.phoenix_table1中数据插入到宽表中
upsert into test.user_hbase_wide1(id,name,etl_date)
select id,name,etl_date from test.phoenix_table1;
注: 1:"a"是指定hbase列族名称,一张表的列族一般是一个,最好是不要超过2个(官网建议),不宜太多。
2:COLUMN_ENCODE_BYTES=0是要加的,不加的话column的名字在hbase中会以16进制存储,查询的表现就是所谓的乱码。
3:字段类型都是varchar类型,因为hbase除了字符串类型,其他数据类型会自动转换成16进制的,到时候hive和impala查询的时候value就成了null。
4)在hive中,创建test.user_hbase_wide1宽表的映射
目的:可以让这个宽表能够支持hive和impala的查询
hive:>
drop table if exists ods.user_hbase_wide1;
create external table ods.user_hbase_wide1(
id string,
name string,
sex string,
age int,
etl_date string
)
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES (
"hbase.columns.mapping" = ":key,a:NAME,a:SEX,a:AGE,a:ETL_DATE")
TBLPROPERTIES("hbase.table.name" = "TEST.USER_HBASE_WIDE1");
--此时,你去hive和impala查询这个表的时候,里面的数据就和hbase和phoenix一致了
--注意:impala查询之前先刷新一下,否则找不到表
shell:>
impala-shell -i hdp1(主机别名) -q "
invalidate metadata ods.user_hbase_wide1;
REFRESH ods.user_hbase_wide1;
"
注: 1:这里的字段类型就可以根据实际情况设置了。 2:hbase.columns.mapping" =
":key,a:NAME,a:SEX,a:AGE,a:ETL_DATE"这里的列族名要和phoenix建宽表的时候一致。
3:“hbase.table.name” =
"TEST.USER_HBASE_WIDE1"对应hbase中的宽表名,一定要大写,因为phoenix并不区分大小写,但是hbase区分并默认是大写,如果想小写加“”就行,phoenix中创建的test.user_hbase_wide1,在hbase中实际是TEST.USER_HBASE_WIDE1
至此,hbase、phoenix、impala、hive映射完成,每次hive过来数据,就可以使用phoenix往宽表里面插数,并且不会让其他字段为null,在hive和impala也是动态更新宽表数据。