hive使用简介

hive -S :进入hive的静默模式&＃xff0c;只显示查询结果&＃xff0c;不显示执行过程&＃xff1b;

hive -e ‘show tables’ :直接在操作系统命令下执行hive语句&＃xff0c;不需要进入hive交互模式&＃xff1b;

source /root/my.sql; :在hive模式下使用source命令执行.sql文件&＃xff1b;

t1:创建普通表&＃xff1b;

t2:在hdfs中的指定目录创建表&＃xff1b;

t3:创建列分隔符为“,”的表&＃xff1b;

t4:使用查询语句创建有数据的表&＃xff1b;

t5:使用查询语句创建列以“,”分隔有数据的表&＃xff1b;

Hive复杂数据类型&＃xff1a;array、map、struct;

  Hive还可以把表或分区&＃xff0c;组织成桶。将表或分区组织成桶有以下几个目的&＃xff1a;

　　第一个目的是为看取样更高效&＃xff0c;因为在处理大规模的数据集时&＃xff0c;在开发、测试阶段将所有的数据全部处理一遍可能不太现实&＃xff0c;这时取样就必不可少。

第二个目的是为了获得更好的查询处理效率。

　　桶为了表提供了额外的结构&＃xff0c;Hive在处理某些查询时利用这个结构&＃xff0c;能给有效地提高查询效率。

　　桶是通过对指定列进行哈希计算来实现的&＃xff0c;通过哈希值将一个列名下的数据切分为一组桶&＃xff0c;并使每个桶对应于该列名下的一个存储文件。

在建立桶之前&＃xff0c;需要设置hive.enforce.bucketing属性为true&＃xff0c;使得hive能识别桶以性别为例创建分区表&＃xff1a;

分别导入性别为”M”和”F”的“sample_data”表中的数据&＃xff1a;

创建外部表&＃xff08;根据数据库中的对应字段创建外部表&＃xff0c;location指向hdfs的文件地址&＃xff09;&＃xff1a;

导入数据格式为JSON的分隔符&＃xff1a;ROW FORMAT SERDE &＃39;org.apache.hive.hcatalog.data.JsonSerDe&＃39;

创建桶表&＃xff1a;

创建视图view:

Hive数据的导入&＃xff1a;

使用LOAD进行数据导入

load语句:

LOAD DATA [LOCAL] INPATH &＃39;filepath&＃39; [OVERWRITE]

INTO TABLE tablename [PARTITION (partcoll&＃61;va11,partcoll&＃61;va12)]

//LOCAL表示从本地文件系统中导入数据&＃xff0c;省略则表示从HDFS中导入数据

//OVERWRITE表示是否需要覆盖表中已存在的数据

//PARTITION表示导入分区表&＃xff0c;后跟分区条件

注意&＃xff1a;在导入数据时要注意文件中数据之间的分隔符要与创建表时指定的分隔符相一致&＃xff0c;否则数据导入进去显示为null;

 要导入的文件内容为&＃xff1a;

t2表创建时没有指定分隔符&＃xff0c;默认分隔符为制表符&＃xff0c;而文件里数据是以”,”相分割的&＃xff0c;因此导入后数据为null&＃xff0c;如图&＃xff1a;

t3表创建时指定分隔符为“&＃xff0c;”与文件分隔符一致&＃xff1a;

有overwrite表示会覆盖原来表中的数据

数据文件导入分区表&＃xff1a;

要导入的文件&＃xff1a;

导入语句&＃xff1a;

使用Sqoop进行数据的导入&＃xff1a;

1、列出mysql数据库中的所有数据库&＃xff1a;

sqoop list-databases \

--connect jdbc:mysql://192.168.0.158:3306 \

--username root --password password

2、连接mysql并列出数据库中的表&＃xff1a;

sqoop list-tables \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password

3、将关系型数据的表结构复制到hive中
sqoop create-hive-table \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password \

--table holyrobot_url \

--hive-table holyrobot_url \

--fields-terminated-by "#" \

--lines-terminated-by "\n"

参数说明&＃xff1a;
--fields-terminated-by "\0001"  是设置每列之间的分隔符&＃xff0c;"\0001"是ASCII码中的1&＃xff0c;它也是hive的默认行内分隔符&＃xff0c; 而sqoop的默认行内分隔符为"&＃xff0c;" 
--lines-terminated-by "\n"  设置的是每行之间的分隔符&＃xff0c;此处为换行符&＃xff0c;也是默认的分隔符&＃xff1b;
注意&＃xff1a;只是复制表的结构&＃xff0c;表中的内容没有复制

4、将数据从关系数据库导入文件到hive表中

sqoop import -m 1 \

--hive-import \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password \

--table holyrobot_url \

--columns &＃39;Url,ParentID,DataSource&＃39; \

--hive-table holyrobot_url \

--fields-terminated-by "#"
参数说明&＃xff1a;

-m 1 表示由一个map作业执行&＃xff1b;
--fields-terminated-by "\0001"  需同创建hive表时保持一致&＃xff1b;

--columns 不指定表示导入全部数据&＃xff1b;

--hive-table 不指定hive表名时&＃xff0c;会自动创建一个与数据库导入表同名的表&＃xff1b;

 5、将数据从关系数据库导入文件到hive表中&＃xff0c; --where 语句使用

sqoop import -m 1 --append \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password \

--table holyrobot_url \ 

--where ‘id > 3 and (age &＃61; 88 or age &＃61; 80)’ \

--target-dir /user/hive/warehouse/userinfos2 --fields-terminated-by ",";
注意&＃xff1a;--target-dir /user/hive/warehouse/userinfos2   可以用  --hive-import --hive-table userinfos2进行替换

 6、将hive中的表数据导入到mysql数据库表中

sqoop export \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password \

--table holyrobot_url \ 

--export-dir /user/hive/holyrobot_url/part-m-00000 \
--input-fields-terminated-by &＃39;\0001&＃39;
注意&＃xff1a;
1、在进行导入之前&＃xff0c;mysql中的表userst必须已经提起创建好了。
2、 jdbc:mysql://192.168.0.158:3306/testholyrobot中的IP地址改成localhost会报异常。

7、将数据从关系数据库导入文件到hive表中&＃xff0c;--query 语句使用
sqoop import -m 1 --append \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password \ 

--query "select id,age,name from userinfos where \$CONDITIONS" \

--target-dir /user/hive/warehouse/userinfos2 \

--fields-terminated-by ","

注意&＃xff1a;如果SQL语句中使用双引号&＃xff08;“”&＃xff09;&＃xff0c;则必须使用\$CONDITIONS代替$CONDITIONS&＃xff0c;使你的shell不将其识别为shell自身的变量

8、从mysql导入hdfs:

sqoop import -m 1 \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password \

--table holyrobot_url \

--target-dir /hdfs/testdata/url \

--fields-terminated-by &＃39;#&＃39;    //定义字段之间的分隔符

9、从mysql导入指定列的数据到hdfs(使用columns):

sqoop import -m 1 \

> --connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

> --username root --password password \

> --table holyrobot_url \

> --columns &＃39;Url,ParentID,DataSource&＃39; \

> --target-dir /hdfs/testdata/url2 \

> --fields-terminated-by &＃39;#&＃39;

10、从hdfs导入mysql:

sqoop export -m 1 \

--connect jdbc:mysql://192.168.0.158:3306/testholyrobot \

--username root --password password \

--table holyrobot_url \

--export-dir /hdfs/testdata/url/holyrobot_url.txt \

--input-fields-terminated-by &＃39;#&＃39;   //指定文件内容是以“,”分割的&＃xff0c;“\t”是以空格

Hive简单查询和fetch task&＃xff1a;

查询时可使用explain&＃43;HQL语句查看执行计划&＃xff1b;

现在我们打开了一个员工表:

查询员工所有信息:

select * from emp;

查询员工信息: 员工号 姓名 月薪

select empno, ename,sal from emp;

查询员工信息:员工号 姓名 月薪 年薪(需要算术表达式)

select empno,ename,sal,sal*12 from emp;

查询员工信息:员工号 姓名 月薪 年薪 奖金 年收入

(需要注意的是&＃xff0c;若算式中有空值null则计算结果则会是空值&＃xff0c;所以我们需要对空值进行处理)

这里我们介绍一个函数:nvl(某个可能为空的值, 转变的值)

select empno,ename,sal,sal*12,comm,sal*12&＃43;nvl(comm, 0) from emp;

查询奖金为null的员工

select * from emp where comm&＃61;null;       //成功执行&＃xff0c;但没有显示查询结果

(在HQI和SQL语句中,判断一个值是否为null&＃xff0c;不能使用 !&＃61; 或者 &＃61;, 因为它永远是不等)

所以我们可以使用is来查询:

select * from emp where comm is null;

使用distinct去掉重复记录

select distinct deptno from emp;

select distinct deptno,job from emp;     //distinct作用于后面所有的列

//如果deptno和job的组合不同&＃xff0c;则就被distinct认为是不同的数据

Fetch Task功能:开启此功能后我们执行一条简单的查询语句(没有排序&＃xff0c;没有函数)

就不会使用mapreduce作业,而直接使用Fetch Task从HDFS中查询输出语句&＃xff0c;这样对于简单的查询&＃xff0c;效率会更高

fetchtask设置方法:

配置好开启后&＃xff0c;执行简单查询(无排序&＃xff0c;无函数)时&＃xff0c;不会生成MapReduce作业&＃xff0c;使用FetchTask来直接从HDFS处理

1.从hive0.10版本开始支持

2.配置方式

- set hive.fetch.task.conversion &＃61; more &＃xff08;仅当前session下有用&＃xff09;

- hive --hiveconf hive.fetch.task.conversion &＃61; more  &＃xff08;仅当前session下有用&＃xff09;

- 修改hive-site.xml文件

在查询中是引用过滤

*查询10号部门的员工

select * from emp where deptno &＃61; 10;

*查询名叫KING的员工&＃xff08;HIve数据库中严格区分大小写&＃xff09;

select * from emp where name &＃61; &＃39;KING&＃39;;

*查询部门号是10&＃xff0c;薪水小于2000的员工

select * from emp where deptno &＃61; 10 and sal <2000;

*查看查询操作的执行计划

explain select * from emp where deptno &＃61; 10 and sal <2000;

*模糊查询&＃xff1a;查询名字以S打头的员工

select empno, ename, sal from emp where ename like &＃39;S%&＃39;;

*查询名字中有下滑线的员工,使用转移字符串\\

select empno, ename, sal from emp where ename like &＃39;%\\_%&＃39;;

模糊查询&＃xff1a;

%&＃xff1a;指任意个字符串&＃xff0c;     _&＃xff1a;指任意一个字符

Hive在查询中使用排序&＃xff1a;

HIVE查询中使用排序(会被转变成mapreduce作业,执行)

HIVE中的排序默认使用升序排列(从小到大)&＃xff0c;也可以使用desc进行降序排列

-查询员工信息:员工号 姓名 月薪 按照月薪降序排序

>select empno, ename, sal from emp order by sal desc;

//ps: order by 后面可以跟: 列名&＃xff0c; 列的表达式&＃xff0c; 列的别名&＃xff0c; 序号

设置排序编号&＃xff1a;set hive.groupby.orderby.position.alias&＃61;true  //默认为false

>select empno, ename, sal, sal*12 from emp order by 4;

如果在排序的时候含有空值null:

升序排列时null在最前面

降序排列时null在最后面

HIVE数学函数&＃xff1a;

四舍五入:

>select round(45.926, 2), round(45.926, 1), round(45.926, 0)

        round(45.926, -1), round(45.926, -2);

结果&＃xff1a;45.93  45.9  46.0  50.0  0.0

//round(某数字, [..., 2, 1, 0, -1, -2, ...])

//2表示保留小数点后2位&＃xff0c;1表示保留小数点后1位&＃xff0c;0表示保留小数点后0位

//-1表示保留个位&＃xff0c; -2表示保留十位&＃xff0c;以此类推

向上取整:

select ceil(45.9);

向下取整:

select floor(45.9);

HIVE的数据查询之字符函数:

字符串转换成小写:lower

字符串转换成大写:upper

>select lower(&＃39;Hello World&＃39;), upper(&＃39;Hello Hive&＃39;);

字符串长度(字符数):length

>select length(&＃39;Hello World&＃39;), length(&＃39;你好&＃39;);    //&＃39;你好&＃39;的字符数为2&＃xff0c;字节数为4

拼接字符串:concat

>select concat(&＃39;Hello&＃39;, &＃39; World&＃39;);

求字符串的子串:substr        

//substr(a, b)从a中第b位开始取&＃xff0c;取右边所有字符

>select substr(&＃39;Hello World&＃39;, 3);

//substr(a, b, c)   从a中,第b位开始取&＃xff0c;取c个字符

>select substr(&＃39;Hello World&＃39;, 3, 4);

去掉字符串前后的空格:trim

左填充:lpad

右填充:rpad

>select lpad(&＃39;abcd&＃39;, 10, &＃39;*&＃39;);    //表示用*左填充&＃39;abcd&＃39;到10位

>select rpad(&＃39;abcd&＃39;, 10, &＃39;*&＃39;);    //表示用*右填充&＃39;abcd&＃39;到10位

收集函数:size     

//返回Map集合中形式的数据的数量大小个数

>select size(map(1, &＃39;Tom&＃39;, 2, &＃39;Marry&＃39;));    //2

转换函数:cast    

//把某个数据类型转换成别的数据类型

>select cast(1 as bigint);          //1

>select cast(1 as float);           //1.0

>select cast(&＃39;2017-11-07&＃39; as date);   //2017-11-07

HIVE中的日期函数&＃xff1a;

取出一个字符串当中日期的部分:to_date

>select to_date(&＃39;2017-11-07 20:32:00&＃39;);

取出一个日期中年:year

取出一个日期中月:month

取出一个日期中日:day

>select year(&＃39;2017-11-07 20:32:00&＃39;),month(&＃39;2017-11-07 20:32:00&＃39;),day(&＃39;2017-11-07 20:32:00&＃39;);

返回一个日期在一年中是第几个星期:weekofyear

>select weekofyear(&＃39;2017-11-07 20:32:00&＃39;);

两个日期相减返回他们相差的天数:datediff

>select datediff(&＃39;2017-11-07 20:32:00&＃39;, &＃39;2017-11-11 20:32:00&＃39;);

在一个日期值上加上多少天:date_add

>select date_add(&＃39;2017-11-07 20:32:00&＃39;, 2);

在一个日期上减去多少天:date_sub

>select date_sub(&＃39;2017-11-07 20:32:00&＃39;, 2);

HIVE条件函数&＃xff1a;

从左到右返回第一个不为null的值:coalesce

>select comm, sal,coalesce(comm, sal) from emp;

条件表达式:case...when...

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

//如果a的值等于b时返回c&＃xff0c;如果等于d返回e&＃xff0c;等等等等&＃xff0c;最后如果都不是返回f

 涨工资--案例

(给员工涨工资&＃xff0c;总裁1000, 经理800&＃xff0c; 其他400)

>select ename,job,sal, case job when &＃39;PRESIDENT&＃39; then sal&＃43;1000 when &＃39;MANAGER&＃39; then sal&＃43;800 else sal&＃43;400 end from emp;

HIVE之聚合函数以及表生成函数&＃xff1a;

聚合函数:

计数:count

求和:sum

最小值:min

最大值:max

平均值:avg

>select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;

//这里*代表所有列,count(*)表示对记录数进行计数操作

表生成函数:

explode  //把map集合&＃xff0c;或者array中的元素单独生成一个行

>select explode(map(1, &＃39;Tom&＃39;, 2, &＃39;Mary&＃39;, 3, &＃39;Mike&＃39;));

HIVE表连接(转换成mapreduce作业,提交到hadoop上)&＃xff1a;

等值连接(连接条件中为等号):

select e.empno, e.ename, e.sal, d.dname     //选择要查询的字段

from emp e, dept d                          //选择表&＃xff0c;并取别名

where e.deptno&＃61;d.deptno;                    //条件是表e的deptno等于表d的deptno

不等值连接(条件中是不等号:>, <, !&＃61;):

between and            //含有边界&＃xff0c;并且小值在前&＃xff0c;大值在后

select e.empno, e.ename, e.sal, s.grade   //选择要查询的字段

from emp e, salgrade s                    //选择表&＃xff0c;并取别名

where e.sal between 2000 and 8000;        //条件是表e的sal大于等于2000小于等于8000

外连接:

(在select语句中&＃xff0c;所有没有包含在聚合函数中的列,都必须要出现在grouop by 子句的后面)

#一般的等值连接

按部门统计员工人数&＃xff1a;部门号&＃xff0c;部门名称&＃xff0c;人数

select d.deptno, d.dname, count(e.empno)

from emp e, dept d

where e.deptno&＃61;d.deptno

group by d.deptno, d.dname;

#外连接(左外连接&＃xff0c;右外连接)

左外连接(当我们的连接条件不成立时&＃xff0c;连接条件的左边的表依然可以包含在结果中)

右外连接(当我们的连接条件不成立时&＃xff0c;连接条件的右边的表依然可以包含在结果中)

#右外连接

select d.deptno, d.dname, count(e.empno)

from emp e right outer jion dept d

on (e.deptno&＃61;d.deptno)

group by d.deptno, d.dname;

自连接(自己和自己连接)

(自连接的核心:通过表的别名将一张表视为多张表)

查询员工的姓名和员工的老板姓名&＃xff1a;

select e.ename, b.ename

from emp e, emp b

where e.mgr&＃61;b.empno;

HIVE子查询&＃xff1a;

hive只支持from和where子查询

select e.ename from emp e where e.deptno in (select e.deptno from dept d where d.dname&＃61;&＃39;SALES&＃39; or d.dname&＃61;&＃39;ACCOUNTING&＃39;);

注意的问题:

-语法中的括号

-合理的书写风格

-hive中只支持WHERE和FEOM子句中的子查询

-主查询和子查询可以不是同一张表

-子查询的空值问题

select * from emp e where e.empno not in (select e1.mgr from emp e1);

//如果子集合中不含有空值null&＃xff0c;则可以使用not in

//如果子集合中含有空值&＃xff0c;则不可以使用not in,可以使用in

#改进

select * from emp e where e.empno not in (select e1.mgr from emp e1 where e1.mgr is not null);

Hive的Thrift操作&＃xff1a;

首先打开hive服务&＃xff1a;hive --service hiveserver;

查看emp表的列信息&＃xff1a;

HIVE的自定义函数(UDF)z;

可以直接应用于select语句&＃xff0c;对查询结果做格式化处理后&＃xff0c;再输出结果

HIVE自定义函数实现细节:

首先&＃xff0c;我们要创建一个继承org.apache.hadoop.hive.ql.UDF的JAVA类

然后&＃xff0c;需要实现evaluate函数&＃xff0c;evaluate函数支持重载

创建好自定义函数后&＃xff0c;我们需要把程序打成jar包&＃xff0c;放到linux的hive服务器上

并且&＃xff0c;进入hive客户端&＃xff0c;添加jar包:

add jar /root/training/udfjar/udf_test.jar

创建临时函数(用来指向JAR包):

create temporary function <函数名>

as &＃39;JAVA类名&＃39;;

最后,可以在一条select语句中,直接调用自定义函数(会执行相应的mapreduce程序,调用我们的java类,完成特定的功能);

select <函数名> from table;

销毁临时函数:

drop temporary function <函数名>;

 Hive的自定义函数&＃xff1a;

1.数据类型必须使用Hadoop的MapReduce可以识别的Text类型&＃xff0c;而不是String

2.MyEclipse中打jar包&＃xff0c;export-->jar file --->

上传jar包至linux:

在hive中添加这个jar文件&＃xff1a;

创建自定义函数myconcat&＃xff1a;

运行myconcat: