Hive执行过程

--1、cd /usr/local/cdh/hive/bin/


--2、启动hive命令行
   hive


--3、查看数据库
   show databases;


--4、切换数据库
   use t_test;


--5、查看表
   show tables;


--6、 hive sql 对hbase中的原始数据进行统计
SELECT t.username,collect_set(t.channel)[0] AS channel,collect_set(idCard)[0] AS idCard,collect_set(registtime)[0] AS registTime,collect_set(realname)[0],SUM(rechargeMoney) AS rechargeMoney,collect_set(firstBorrowApr)[0] AS firstBorrowApr,collect_set(firstTenderMoney)[0] AS firstTenderMoney,COUNT(rechargeMoney)AS rechargeTime,COUNT(t.tenderTime) AS tenderTime ,collect_set(phone)[0] AS phone,collect_set(bankCard)[0]AS bankCard,IF(collect_set(phone)[0]!='' ,TRUE, FALSE)AS isPhone,IF(collect_set(bankCard)[0]!='' ,TRUE,FALSE)AS isBindCard,IF( collect_set(idCard)[0]!='', TRUE, FALSE)AS isReal FROM hbase_t_channel t  GROUP BY t.username;


-- 7、将jar包添加到classpath中
add jar /usr/local/cdh/hive/lib/hive-contrib-1.1.0-cdh5.8.0.jar;
add jar /usr/local/cdh/hive/lib/mysql-connector-java-5.1.27.jar;
add jar /usr/local/cdh/hive/lib/udf.jar;
-- 8、创建临时方法
CREATE TEMPORARY FUNCTION dboutput AS 'demo.hive.GenericUDFDBOutput';
-- 8、执行hive—sql脚本
SELECT dboutput('jdbc:mysql://192.168.18.82/test?useUnicode=true&characterEncoding=utf-8','root','root','INSERT INTO t_channel(username,channel,idCard,registTime,realname,rechargeMoney,firstBorrowApr,firstTenderMoney,rechargeTime,tenderTime,phone,bankCard,isPhone,isBindCard,isReal)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',t.username,collect_set(t.channel)[0],collect_set(idCard)[0],collect_set(registtime)[0],collect_set(realname)[0],SUM(rechargeMoney),collect_set(firstBorrowApr)[0],collect_set(firstTenderMoney)[0],COUNT(rechargeMoney),COUNT(t.tenderTime),collect_set(phone)[0],collect_set(bankCard)[0],IF(collect_set(phone)[0]!='' ,TRUE, FALSE),IF(collect_set(bankCard)[0]!='' ,TRUE,FALSE),IF( collect_set(idCard)[0]!='', TRUE, FALSE))FROM hbase_t_channel t GROUP BY t.username;


--9、通过shell脚本执行hive-sql
 --备注：创建sh文件的时候要赋执行权限
   chmod u+x channel_data_zyj.sh
   cd /home/zhangyj/shell
   ./channel_data_zyj.sh


--10、channel_data_zyj.sh源码
#!/bin/bash
#===========
#
#channel_data_count.sh
#
#===========


DT=`date -d '-1 day' "+%y-%m-%d"`
if [ $1 ];then
DT=$1
fi


SQL="SELECT 
dboutput('jdbc:mysql://192.168.18.82/test?useUnicode=true&characterEncoding=utf-8','root','root','INSERT INTO t_channel(username,channel,idCard,registTime,realname,rechargeMoney,firstBorrowApr,firstTenderMoney,rechargeTime,tenderTime,phone,bankCard,isPhone,isBin
dCard,isReal)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',t.username,collect_set(t.channel)[0],collect_set(idCard)[0],collect_set(registtime)[0],collect_set(realname)[0],SUM(rechargeMoney),collect_set(firstBorrowApr)[0],collect_set(firstTenderMoney)[0],COUNT(rechargeMoney),C
OUNT(t.tenderTime),collect_set(phone)[0],collect_set(bankCard)[0],IF(collect_set(phone)[0]!='' ,TRUE, FALSE),IF(collect_set(bankCard)[0]!='' ,TRUE,FALSE),IF( collect_set(idCard)[0]!='', TRUE, FALSE))FROM hbase_t_channel t GROUP BY t.username;"


echo "${SQL}"


hive -e "use t_test; 


add jar /usr/local/cdh/hive/lib/hive-contrib-1.1.0-cdh5.8.0.jar;
add jar /usr/local/cdh/hive/lib/mysql-connector-java-5.1.27.jar;
add jar /usr/local/cdh/hive/lib/udf.jar;
cREATE TEMPORARY FUNCTION dboutput AS 'demo.hive.GenericUDFDBOutput';
$SQL"