07-hive---基础部分3

一、Hive的数据导出

数据导出的分类：

1. 从hive表中导出本地文件系统中(目录、文件)
2. 从hive表中导出hdfs文件系统中
3. hive表中导出到其它hive表中

1）导出数据到本地目录

insert overwrite local directory '/root/out/00' select * from t_user;

这个00不是文件名，而是文件夹的名字，没有可以自动创建

2）导出到hdfs的目录下

insert overwrite directory '/root/out/00' select * from t_user;

假如你导出的数据想要一个分隔符，比如 逗号 

insert overwrite directory '/root/out/00' 
row format delimited fields terminated by ','
select * from t_user;

3）直接将结果导出到本地文件中 

hive -e "sql语句"   不需要进入hive，直接执行语句
hive -e "select * from  yhdb.t_user" >> /root/out/a.txt

 

二、一个案例

数据整理：emp.txt

7369,SMITH,CLERK,7902,1980-12-17,800,null,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,null,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10
7788,SCOTT,ANALYST,7566,1987-04-19,3000,null,20
7839,KING,PRESIDENT,null,1981-11-17,5000,null,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23,1100,null,20
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-02,3000,null,20
7934,MILLER,CLERK,7782,1982-01-23,1300,null,10
3423,laoyan,MANAGER,8899,2022-01-01,50000,100000,40

根据数据的字段和格式，建表：

create table emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal int,
comm int,
deptno int
)
row format delimited 
fields terminated by ',';

加载数据：

load data local inpath "/home/hivedata/emp.txt" into table emp;

编写指标：

1、统计有领导的员工有哪些？
不完美
select * from emp where mgr is not null;
完美的写法：
select * from emp e1 where exists (select * from emp e2 where e2.empno = e1.mgr);

还有其他写法：
select * from emp where mgr in (select distinct empno from emp);

2、统计每个部门的员工的总工资和总薪水是多少？
select sum(sal),sum(comm+sal),deptno from emp group by deptno;

8750    NULL    10
10875   NULL    20
9400    7800    30
50000   150000  40
Time taken: 1.965 seconds, Fetched: 4 row(s)

null + 任何数字 结果为null
需要一个函数 IFNULL(mysql中的函数)，在hive中的对应的函数是nvl
select sum(sal),sum(nvl(comm,0)+sal),deptno from emp group by deptno;

 

 

三、表类型

1、表类型介绍

内部表：
表面来看，我们建的所有的表，默认都是内部表，内部表又叫做管理表，它的位置也很固定/user/hive/warehouse下面。
外部表：
创建的时候需要加关键字external 修饰，而且，外部表它的数据的存储位置可以不在/user/hive/warehouse，可以指定位置。

建表的语法格式：

create external table tableName(id int,name string) [location 'path'];

create external table t_user7(
id int,
name string
)
row format delimited
fields terminated by ','
location '/publicData'

这个location 是本地的意思还是hdfs的路径呢？答案是必须在hdfs上。

2、内部表和外部表转换

内部表转外部表

desc extended t_user; 查看表结构的详细信息

alter table tableName set tblproperties('EXTERNAL'='TRUE');
注意：内部表转外部表，true一定要大写;

外部表转内部表

alter table tableName set tblproperties('EXTERNAL'='false');
说明：false不区分大小

3、两种表的区别

用的最多的都是外部表，因为可以分析数据。

最大的区别就是删除表的时候，内部表会删除元数据和真正的hdfs上的数据。而外部表只删除元数据。

思考：为什么这么做？

Hive是一个数据分析的工具，存储数据不是它的本意。
假如一个人，将数据上传至hdfs，这个数据量很大，一般不挪动位置，你可以建一个外部表数据的位置指向hdfs的某个文件夹，然后就可以分析了。
/home   a.txt   2G
建一个外部表，指向/home/a.txt , 分析完毕之后，删除表就可以了，干嘛要删除数据，没必要。--用后即焚！

1) 内部表和外部表在创建时的差别

就差两个关键字，EXTERNAL 和 LOCATION 举例：

- 内部表 -- CRAATE TABLE T_INNER(ID INT);
- 外部表 -- CREATE EXTERNAL TABLE T_OUTER(ID  INT) LOCATION 'HDFS:///AA/BB/XX'; 

) Hive表创建时要做的两件事：

1、在hdfs下创建表目录

2、在元数据库mysql创建相应表的描述数据（元数据）

3) drop时有不同的特性：

1、drop时，元数据都会被清除

2、drop时，内部表的表目录会被删除，但是外部表的表目录不会被删除。

4) 使用场景

内部表: 平时用来测试或者少量数据,并且自己可以随时修改删除数据.

外部表：使用后数据不想被删除的情况使用外部表（推荐使用）所以，整个数据仓库的最底层的表使用外部表。

4、练习

创建不存在的文件夹  
hdfs dfs -mkdir -p /user/hdfs/source/out_table
接着创建一个外部表
create external table out_table (
   id int,
   name string
)
row format delimited
fields terminated by ','
location '/user/hdfs/source/out_table';

加载数据：load data local inpath '/home/hivedata/user.txt' into table out_table;
查看数据存放的位置：

drop table out_table;

 发现数据依然存在：

 假如我再创建一个表，数据指向这个文件夹，表中是否会有数据？

create external table student (
   sid int,
   sname string
)
row format delimited
fields terminated by ','
location '/user/hdfs/source/out_table';

查看表数据，数据即可出现，根本不需要导入操作：

五、关于Hive使用的一些技巧

1、可以直接不进入hive的情况下执行sql语句

通过shell的参数 -e 可以执行一次就运行完的命令

hive -e "select * from yhdb.student"

hive -S -e "set" | grep cli.print
-S 是静默模式,会省略到多余的输出

 

假如我想在查询语句的结果上面显示字段名称，可以将
set hive.cli.print.header=true;

 

想永久设置：修改/opt/installs/hive/conf/.hiverc文件 

set hive.cli.print.header=true;

hive可以直接运行sql文件

hive -f  sql语句的路径

先创建一个sql语句test.sql：
use yhdb;
insert into student values(1,'laoyan');

hive -f test.sql  即可运行

3、可以在hive中执行linux命令

在Hive的shell中 加上前缀! 最后以分号;结尾,可以执行linux的命令

！ ls /home/hivedata ;

 

4、可以在hive中操作hdfs

用户可以在Hive的shell中执行HDFS的DFS命令,不用敲入前缀hdfs或者hadoop

dfs -ls /user/hive/warehouse

5、设置显示当前数据库 

hive-site.xml

<property>
    <name>hive.cli.print.current.db</name>
    <value>false</value>
    <description>Whether to include the current database in the Hive prompt.</description>
</property>

将value设置为true即可。
跟之前在.hiverc中设置 set  hive.cli.print.current.db=true; 效果是一样的。

思考：设置一个属性有几种方法？

第0 种：命令行方式

1、启动hive时，可以在命令行添加 --hiveconf param = value来设定参数
2、测试：通过命令行参数方式，配置hive不打印当前数据库名 
bin/hive --hiveconf hive.cli.print.current.db = false
注意：命令行参数方式仅仅对本次hive启动有效。

第一种：直接在hive的窗口上设置
第二种：在hive的conf下的.hiverc 设置
第三种：修改hive-site.xml 进行设置
第四种：默认设置

到底以哪种方式设置为准：
第一种 > 第二种 > 第三种 > 第四种  【就近原则】
假如窗口中设置以窗口为准，顶掉前面所有地方的设置，假如.hiverc设置，.hiverc 中的设置会顶替到它之前所有的设置，以此类推。

第四种默认设置：hive在安装的时候会有元数据，元数据中的设置为默认设置，假如你想更改设置，需要自己编写一个hive-site.xml ，在这个文件中想顶掉哪个默认设置就写哪个。

第一种和第二种其实是一种，生命周期都是客户端进入，hive进入后，会自动加载.hiverc文件，将里面的set执行一遍。断开连接后，配置消失。

hive-site.xml 中其实只需要编写自己需要的配置即可，没必要复制全部！！！！

查看当前session中的设置：
 

set hive.cli.print.current.db;

 6、设置本地模式运行速度更快（小任务）

--开启本地模式
set hive.exec.mode.local.auto=true
-- 当文件大小小于这个的值才会进入本地模式
set hive.exec.mode.local.auto.inputbytes.max=134217728
-- 假如文件的数量小于这个值才会进入本地模式
set hive.exec.mode.local.auto.input.files.max=4


.hiverc中不要写注释，否则报错

假如运行报：文件打开过多的错误，请修改如下配置

vi /etc/security/limits.conf

在下方添加：
root soft nofile 65535
root hard nofile 65535

六、表的数据查询

select ..
from ..
	join [tableName] on ..
	where ..
	group by ..
	having ..
	order by ..
	sort by ..
	limit ..
union | union all ...

执行顺序：

第一步: FROM <left_table>
第二步: ON <join_condition>
第三步: <join_type> JOIN <right_table>
第四步: WHERE <where_condition>
第五步: GROUP BY <group_by_list>
第六步: HAVING <having_condition>
第七步: SELECT
第八步: DISTINCT <select_list>
第九步: ORDER BY <order_by_condition>
第十步: LIMIT <limit_number>

讲一下count:

count的执行:
1. 执行效果上：
	- count(*)包括了所有的列，相当于行数，在统计结果的时候不会忽略null值
	- count(1)包括了所有列，用1代表行，在统计结果的时候也不会忽略null值
	- count(列名)只包括列名那一列，在统计结果时，会忽略null值

 比如：
 name
 zhangsan
 null
 lisi

 select count(name)  from xxxx;   == 2

2.执行效率上：
	- 列名为主键，count(列名)会比count(1)快
	- 列名不为主键，count(1)会比count(列名)快
	- 如果表中有多个列并且没有主键，count（1）的效率高于count(*)
	- 如果有主键count(主键)效率是最高的
	- 如果表中只有一个字段count(*)效率最高

说一下limit:

limit 在mysql中 可以有两个参数 limit [m,] n
    select * from t_user limit 1,3; // 从第二行开始查找，查找3条
	 在hive中，只能有一个参数 limit n;  查询前n条。
	  一般情况下，在使用limit时，都会先order by排序。

union | union all：

select sname,sage from student where sex='男'
union
select tname,tage from teacher where tname like '张%';

union: 可以去重的
union all: 不去重。

join :

内连接:  [inner] join
2. 外连接 (outer join)：（引出一个驱动表的概念：驱动表里的数据全部显示)
  -   左外连接:left [outer] join, 左表是驱动表
  -   右外连接:right [outer] join, 右表是驱动表
  -   全外连接:full [outer] join, hive支持，mysql不支持.两张表里的数据全部显示出来.

假如  select * from a join b on xxxx=xxx;  --内连接

各种连接的示意图：

多个表join 会产生笛卡尔积。 【笛卡尔】

select * from emp join dept;
假如 emp 14条数据
     dept 4条数据：  14 * 4 = 56
笛卡尔积中数据大部分都没有意义，所以要添加过滤条件。

select * from emp join dept on emp.deptno = dept.detpno;

 

left semi join --左半开连接【hive独有】

在hive中，有一种专有的join操作,left semi join,我们称之为半开连接。它是left join的一种优化形式，只能查询左表的信息，主要用于解决hive中左表的数据是否存在的问题。相当于exists关键字的用法。

举例：查询哪些员工有领导？
select * from emp A where exists (select 1 from emp B where B.empno = A.mgr );

第二种写法可以使用left semi join ：
select * from emp A left semi join emp B on A.mgr = B.empno;

注意： hive中不支持right semi join。

所有的左外连接都可以写成右外连接。

七、Hive默认的日志

默认位置 在 /tmp/root 下。对于我们使用hive非常有帮助。

 

关于hive的日志，有一个配置文件：在hive的conf文件夹下

mv hive-log4j2.properties.template hive-log4j2.properties

property.hive.log.dir = ${sys:java.io.tmpdir}/${sys:user.name}
property.hive.log.file = hive.log

 有什么用处？

在复杂的sql语句运行时，假如失败了，hive窗口不显示，具体错误需要查看日志才能解决。

八、复杂数据类型

Array      Map      Struct

1、Array的使用 

语法格式

create table tableName(
......
colName array<基本类型>
......
)

说明：下标从0开始，越界不报错，以null代替

zhangsan	78,89,92,96
lisi	67,75,83,94
王五	23,12

新建表：

create table arr1(
  name string,
  scores array<int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

加载数据：

load data local inpath '/home/hivedata/arr1.txt' into table arr1;

hive (yhdb)> select * from arr1;
OK
arr1.name       arr1.scores
zhangsan        [78,89,92,96]
lisi    [67,75,83,94]
王五    [23,12]
Time taken: 0.32 seconds, Fetched: 3 row(s)

需求：

1、查询每一个学生的第一个成绩
select name,scores[0] from arr1;
name    _c1
zhangsan        78
lisi    67
王五    23
2、查询拥有三科成绩的学生的第二科成绩
select name,scores[1] from arr1 where size(scores) >=3;

3、查询所有学生的总成绩
select name,scores[0]+scores[1]+nvl(scores[2],0)+nvl(scores[3],0) from arr1;

以上写法有局限性，因为你不知道有多少科成绩，假如知道了，这样写也太Low

2、展开函数的使用 explode

为什么学这个，因为我们想把数据，变为如下格式：

列转行

zhangsan        78
zhangsan        89
zhangsan        92
zhangsan        96
lisi	67
lisi	75
lisi	83
lisi	94
王五	23
王五	12

explode 专门用于炸集合。

select explode(scores) from arr1;

col
78
89
92
96
67
75
83
94
23
12

想当然的以为加上name 就OK ，错误！
hive (yhdb)> select name,explode(scores) from arr1;
FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

-- lateral view:虚拟表。
	会将UDTF函数生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到数据聚合的目的。

具体使用：

select name,cj from arr1 lateral view explode(scores) mytable as cj;

解释一下：
lateral view explode(scores) 形成一张虚拟的表，表名需要自己起
里面的列有几列，就起几个别名，其他的就跟正常的虚拟表一样了。

name    cj
zhangsan        78
zhangsan        89
zhangsan        92
zhangsan        96
lisi    67
lisi    75
lisi    83
lisi    94
王五    23
王五    12

select name,sum(cj) from arr1 lateral view explode(scores) mytable as cj group by name;

需求4：查询每个人的最后一科的成绩
select name,scores[size(scores)-1] from arr1;

 

 

3、Map的使用

语法格式：

create table tableName(
.......
colName map<T,T>
......
)

上案例：

zhangsan	chinese:90,math:87,english:63,nature:76
lisi	chinese:60,math:30,english:78,nature:0
wangwu	chinese:89,math:25

建表：

create table map1(
  name string,
  scores map<string,int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';

加载数据：
 

load data local inpath '/home/hivedata/map1.txt' into table map1;

需求：

需求一：
#查询数学大于35分的学生的英语和自然成绩
select name,scores['english'],scores['nature'] from map1
where scores['math'] > 35;

需求二：-- 查看每个人的前两科的成绩总和
select name,scores['chinese']+scores['math'] from map1;

OK
name    _c1
zhangsan        177
lisi    90
wangwu  114
Time taken: 0.272 seconds, Fetched: 3 row(s)

需求三：将数据展示为：
-- 展开效果
zhangsan	chinese		90
zhangsan	math	87
zhangsan	english 	63
zhangsan	nature		76

select name,subject,cj   from map1 lateral view explode(scores) mytable as subject,cj ;

name    subject cj
zhangsan        chinese 90
zhangsan        math    87
zhangsan        english 63
zhangsan        nature  76
lisi    chinese 60
lisi    math    30
lisi    english 78
lisi    nature  0
wangwu  chinese 89
wangwu  math    25

需求四：统计每个人的总成绩
select name,sum(cj)   from map1 lateral view explode(scores) mytable as subject,cj  group by name;

 行转列

需求5：
-- 将下面的数据格式
zhangsan        chinese 90
zhangsan        math    87
zhangsan        english 63
zhangsan        nature  76
lisi    chinese 60
lisi    math    30
lisi    english 78
lisi    nature  0
wangwu  chinese 89
wangwu  math    25
wangwu  english 81
wangwu  nature  9
-- 转成：
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9

造一些数据（新建表）：

create table map_temp as
select name,subject,cj   from map1 lateral view explode(scores) mytable as subject,cj ;

第一步，先将学科和成绩形成一个kv对，其实就是字符串拼接

学习一下 concat的用法：
hive (yhdb)> select concat('hello','world');
OK
_c0
helloworld
Time taken: 0.333 seconds, Fetched: 1 row(s)
hive (yhdb)> select concat('hello','->','world');
OK
_c0
hello->world
Time taken: 0.347 seconds, Fetched: 1 row(s)

实战一下：
select name,concat(subject,":",cj) from map_temp;

结果：
name    _c1
zhangsan        chinese:90
zhangsan        math:87
zhangsan        english:63
zhangsan        nature:76
lisi    chinese:60
lisi    math:30
lisi    english:78
lisi    nature:0
wangwu  chinese:89
wangwu  math:25

以上这个结果再合并：
select name,collect_set(concat(subject,":",cj)) from map_temp
group by name;

lisi    ["nature:0","english:78","math:30","chinese:60"]
wangwu  ["math:25","chinese:89"]
zhangsan        ["nature:76","english:63","math:87","chinese:90"]
将集合中的元素通过逗号进行拼接：
select name,concat_ws(",",collect_set(concat(subject,":",cj))) from map_temp group by name;

结果：
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:25,english:81,nature:9



学习到了三个函数：
concat 进行字符串拼接
collect_set() 将分组的数据变成一个set集合。里面的元素是不可重复的。
collect_list(): 里面是可以重复的。
concat_ws(分隔符,集合) : 将集合中的所有元素通过分隔符变为字符串。

想将数据变为：

lisi    {"chinese":"60","math":"30","english":"78","nature":"0"}
wangwu  {"chinese":"89","math":"25"}
zhangsan        {"chinese":"90","math":"87","english":"63","nature":"76"}

需求：将字符串变为map集合 使用一个函数 str_to_map

select name,str_to_map(concat_ws(",",collect_set(concat(subject,":",cj)))) from map_temp group by name;

4、Struct结构体 

create table tableName(
........
colName struct<subName1:Type,subName2:Type,........>
........
)

有点类似于java类
调用的时候直接.
colName.subName

数据准备：

zhangsan	90,87,63,76
lisi	60,30,78,0
wangwu	89,25,81,9

创建表：

create table if not exists struct1(
name string,
score struct<chinese:int,math:int,english:int,natrue:int>
)
row format delimited 
fields terminated by '\t'
collection items terminated by ',';

加载数据：

load data local inpath '/home/hivedata/struct1.txt' into table struct1;

查看数据，有点像map:

hive (yhdb)> select * from struct1;
OK
struct1.name    struct1.score
zhangsan        {"chinese":90,"math":87,"english":63,"natrue":76}
lisi    {"chinese":60,"math":30,"english":78,"natrue":0}
wangwu  {"chinese":89,"math":25,"english":81,"natrue":9}
Time taken: 0.272 seconds, Fetched: 3 row(s)

查询数学大于35分的学生的英语和语文成绩

select name, score.english,score.chinese from struct1 
 where score.math > 35;

 这个看着和map很像，所以我认为map里 也可以使用 xxx.xxx
 或者说我这里也可以使用[]
经过尝试：不可以。