Hive执行过程

74 阅读 0 评论 49 点赞

我是靠谱客的博主积极白羊，最近开发中收集的这篇文章主要介绍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"