hive与hbase的整合
在hive中如果关联hbase中已经存在的表,要使用外部表,反之使用内部关联的话,会在hbase中创建对应的新表:
实例:
CREATE EXTERNAL TABLE hhh1 (
id int,
name string,
age int
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f:name,f:age")
TBLPROPERTIES ("hbase.table.name" = "xyz");
将hbase当中的表与hive相关联
CREATE EXTERNAL TABLE event_log_hourly (
rowkey string,
u_ud string,
pl string,
s_time bigint,
bt string,
bv string,
province string
) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = ":key,f:u_ud, f:pl,f:s_time,f:bt, f:bv, f:province")
TBLPROPERTIES ("hbase.table.name" = "nginx_js");
需求:
每天 每个小时(24小时) 每个平台 每个浏览器 每个浏览器版本 的活跃用户数
create temporary table event_log_hourly_tmp (
date_ string comment 'yyyy-MM-dd',
hour string comment '00|01...23',
pl string,
bt string,
bv string,
u_ud string
);
常见hive函数:
1、current_database()—>查看当前的数据库
select current_database() from event_log_hourly limit 1;
OK
event_log
在mysql中查看当前数据库使用select database();
2、current_date--->查看当前日期
select current_date() from event_log_hourly limit 1;
OK
2018-08-08
3、current_timestamp--->查看当前时间
hive (event_log)> select current_timestamp() from event_log_hourly limit 1;
OK
2018-08-08 19:01:30.75
获取日期
select from_unixtime(cast((s_time/1000) as int), 'yyyy-MM-dd') from event_log_hourly limit 1;
获取小时
select from_unixtime(cast((s_time/1000) as int), 'yyyy-MM-dd HH:mm:ss') from event_log_hourly limit 1;
临时表:
create table event_log_hourly_tmp (
date_ string comment "yyyy-MM-dd",
hour string comment "00|01...23",
pl string,
bt string,
bv string,
u_ud string
);
insert into event_log_hourly_tmp select
from_unixtime(cast((s_time/1000) as int), 'yyyy-MM-dd'),
from_unixtime(cast((s_time/1000) as int), 'yyyy-MM-dd HH:mm:ss'),
pl,
bt,
bv,
u_ud
from event_log_hourly;
说明:为了获取用户的操作时间戳和u_ud,完全可以从rowkey中进行拆分获取,这样减少数据的冗余。
select split(rowkey, "_")[0], split(rowkey, "_")[1] from event_log_hourly limit 1;
每天 每个小时(24小时) 每个平台 每个浏览器 每个浏览器版本 的活跃用户数
select date_, hour, pl, bt, bv, count(distinct u_ud) as u_count from event_log_hourly_tmp group by date_, hour, pl, bt, bv;
echarts呈现(因为要从mysql中读取对应的数据,所以需要从hive将结果迁移至mysql)
mysql表
create table event_log_hourly(
date_ varchar(20) comment "yyyy-MM-dd",
hour_ varchar(20) comment "00|01...23",
pl varchar(100),
bt varchar(100),
bv varchar(100),
u_count int
);
create table event_log_hourly_tmp1 (
date_ string comment "yyyy-MM-dd",
hour_ string comment "00|01...23",
pl string ,
bt string ,
bv string ,
u_count int
) as select date_, hour, pl, bt, bv, count(distinct u_ud) as u_count from event_log_hourly_tmp group by date_, hour, pl, bt, bv;
insert <into | overwrite table> tblName
insert overwrite table event_log_hourly_tmp1 select date_, hour, pl, bt, bv, count(distinct u_ud) as u_count from event_log_hourly_tmp t group by date_, hour, pl, bt, bv;
自动化脚本
#!/bin/sh
###############
##
## collect mysql data import into hdfs
## 约定:变量都用大写,多个单词之间使用下划线分割
## mysql: test/t_user
## hdfs : /input/data-clean/t_user
## 在""中会执行``命令,但是在''中,无法解析${}
###############
##source配置文件
function run() {
source /home/bigdata/shells/db-conf.conf
HIVE_BIN=/home/bigdata/app/hive/bin/hive
SQOOP_BIN=/home/bigdata/app/sqoop/bin/sqoop
##创建hive中外部表关联hbase
##查询对应的数据,生成临时表
##统计数据,生成临时表1
sql="
set hive.exec.mode.local.auto=true; ## 本地模式
USE event_log;
CREATE EXTERNAL TABLE event_log_hourly ( rowkey string, u_ud string, pl string, s_time bigint, bt string, bv string, province string ) STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' WITH SERDEPROPERTIES (\"hbase.columns.mapping\" = \":key,f:u_ud, f:pl,f:s_time,f:bt, f:bv, f:province\") TBLPROPERTIES (\"hbase.table.name\" = \"nginx_js\");
CREATE TABLE event_log_hourly_tmp(date_ string, hour string, pl string, bt string, bv string, u_ud string);
insert overwrite table event_log_hourly_tmp select from_unixtime(cast((s_time/1000) as int), 'yyyy-MM-dd'), from_unixtime(cast((s_time/1000) as int), 'yyyy-MM-dd HH:mm:ss'), pl, bt, bv, u_ud from event_log_hourly;
CREATE TABLE event_log_hourly_tmp1 (date_ string, hour_ string, pl string, bt string , bv string, u_count int);
insert overwrite table event_log_hourly_tmp1 select date_, hour, pl, bt, bv, count(distinct u_ud) as u_count from event_log_hourly_tmp t group by date_, hour, pl, bt, bv;
"
${HIVE_BIN} -e "${sql}"
##将结果通过sqoop迁移到mysql
${SQOOP_BIN} export \
--connect jdbc:mysql://${DB_PRODUCT_HOST}:${DB_PRODUCT_PORT}/test \
--username ${DB_PRODUCT_USER} \
--password ${DB_PRODUCT_PASSWORD} \
--export-dir hdfs://ns1/user/hive/warehouse/event_log.db/event_log_hourly_tmp1 \
--table event_log_hourly \
--fields-terminated-by '\001';
}
run $*