项目-Hive操作

第一个文件mydate.sql,设置文件使用的变量值

#!/bin/bash
#注意:mycollection.sql放的是存放SQL代码的文件名
hive -hiveconf mydate="$(date +%Y%m%d)" -f mycollection.sql

第二个文件mycollection.sql,存放SQL代码

create table if not exists kpi
(kip string, kdate string, kurl string, kresult int, ksize int)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;

load data local inpath '/root/part-r-00000' into table kpi partition (dt='${hiveconf:mydate}');

create table if not exists pv
(purl string, pcount int)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;

--动态加载
    --动态加载之前需要修改运行参数(临时修改,如果需要永久修改可以进入$HIVE_HOME/conf/hive-site.xml修改对应参数值):
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
    --动态加载代码
    --动态加载会自动寻找select字段列表末尾的字段,如果是n个字段分区,会找末尾的n个字段,顺序从前往后
insert into table pv
partition (dt)
select kurl,count(*),dt from kpi where dt='${hiveconf:mydate}' group by kurl,dt;

create table if not exists uv
(uip string, ucount int)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;

insert into table uv
partition (dt)
select kip,count(kip),dt from kpi where dt='${hiveconf:mydate}' group by kip,dt;

create table if not exists vip
(vid int, vip string, vcount int)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;

insert into table vip
partition (dt)
select row_number() over (order by ucount desc) ,uip,ucount,dt from uv limit 100 where dt='${hiveconf:mydate}';

执行的代码

sh -x mydate.sql

MySQL>

drop table kpi;
create table kpi 
(kip varchar(50), kdate varchar(50), kurl varchar(1000), kanswer int, ksize varchar(50));

drop table pv;
create table pv
(purl varchar(1000), pcount int);

drop table uv;
create table uv
(uip varchar(50), ucount int);

drop table vip;
create table vip
(vid int, vip varchar(50), vcount int);

Linux

sqoop export --connect jdbc:mysql://localhost:3306/sqoop --username sqoop --password 1 --table kpi --export-dir /user/hive/warehouse/kpi/dt=0530/part-r-00000 --input-fields-terminated-by '\t'

sqoop export --connect jdbc:mysql://localhost:3306/sqoop --username sqoop --password 1 --table pv --export-dir /user/hive/warehouse/pv/dt=0530/000000_0 --input-fields-terminated-by '\t'

sqoop export --connect jdbc:mysql://localhost:3306/sqoop --username sqoop --password 1 --table uv --export-dir /user/hive/warehouse/uv/dt=0530/000000_0 --input-fields-terminated-by '\t'

sqoop export --connect jdbc:mysql://localhost:3306/sqoop --username sqoop --password 1 --table vip --export-dir /user/hive/warehouse/vip/dt=0530/000000_0 --input-fields-terminated-by '\t'