hive使用教程（2）--数据导入导出、查询与排序

1．语法

（1）load data:表示加载数据

（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

2．实 *** 案例

（0）创建一张表

（1）加载本地文件到hive

（2）加载HDFS文件到hive中

上传文件到HDFS

加载HDFS上数据

（3）加载数据覆盖表中已有的数据

上传文件到HDFS

加载数据覆盖表中已有的数据

1．创建一张分区表

2．基本插入数据

3．基本模式插入（根据单张表查询结果）

4．多插入模式（根据多张表查询结果）（有问题，只高辩是查询单表不同分区的）

根据查询结果创建表（查询的结果会添加到新创建的表中）

1．创建表，并指定在hdfs上的位置

2．上传数据到hdfs上

3．查询数据

注意：先用export导出后，再将数据导入。

1．将查询的结果导出到本地

2．将查询的结果格式化导出到本地

3．将查询的结果导出到HDFS上(没有local)

基本语法：（hive -f/-e 执行语句或者脚本 >file）

后续..............................。

注意：Truncate只能删除管理表，不能删除外部表中数据

1．全表查询

2．选择特定列查询

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

1．重命名一个列

2．便于计算

3．紧跟列名，也可以在列名和别名之间加入关键字‘AS’

4．案例实 ***

查询名称和部门

（1）where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。

（2）where后面不能写分组函数，而having后面可以使用分组函数。

（3）having只用于group by分组统计语句。

空字段赋值

5.CASE WHEN

需求

求出不同部门男女各多少人。结果如下：

创建本地emp_sex.txt，导入数据

创建hive表并导入数据

按需求查询数据

Order By：全局排序，一个Reducer

1．使用 ORDER BY 子句排序

ASC（ascend）: 升序（默认）

DESC（descend）: 降序

2．ORDER BY 子句在SELECT语句的结尾

3．案例实 ***

（1）查询员工信息按工资升序排列

（2）查询员工信息按工资降序排列

按照员工薪水的2倍排序

按照部门和工携册资升序排序

Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。

1．设置reduce个数

2．查看设置戚隐缺reduce个数

3．根据部门编号降序查看员工信息

4．将查询结果导入到文件中（按照部门编号降序排序）

Distribute By：类似MR中partition，进行分区，结合sort by使用。

注意，Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实 *** ：

当distribute by和sorts by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

1）以下两种写法等价

注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。

零．Hive的几种常见的数据导入方式

常用的的有三种：

1.从本地文件系统中导入数据到Hive表；

2.从HDFS上导入数据到Hive表；

3.在创建表的时候通过从别的表中查询出相应的记录并插入到所创建的表中。

Hive配置：

HDFS中Hive数据文件存放目录（启动hive后腔槐HDFS自动创建）：

HDFS： /usr/hive/warehouse

hadoop fs -mkdir /usr/hive/warehouse 命令创立

本地数据存放目录：

本地：/home/santiago/data/hive

一．从本地文件系统中导入数据到Hive表

1.在hive中建表

hive>show databases

OKdefaultTime taken: 1.706 seconds, Fetched: 1 row(s)

hive>create table guo_test(Name string,String string)

>row format delimited

>fields terminated by ','

>stored as textfile；

hive>伍告友 show tables

OK

guo_test

Time taken: 0.024 seconds, Fetched: 1 row(s)123456789101112

2.在本地文件建立同类型数据表

santi@hdp:~/data/hive$ ls

hive_test.txt

santi@hdp:~/data/hive$ cat hive_test.txt

santi,you are a zhazha.1234

3.导入数据并测试

hive>load data local inpath '/home/santi/data/hive/hive_test.txt' into table guo_test

hive>select * from guo_test

hive>dfs -ls /usr/hive/warehouse/guo_test

#hadoop fs -ls /usr/hive/warehouse

Found 1 items

drwxrwxr-x - santiago supergroup 0 2017-01-14 21:13

/usr/hive/warehouse/guo_test12345678

发现hive-site,xml设置的HDFS文件存储位置中多了guo_test这个文件夹

#hadoop fs -ls /usr/hive/warehouse/guo_test

Found 1 items-rwxrwxr-x 1 santiago supergroup 24 2017-01-14 21:13

/usr/hive/warehouse/guo_test/hive_test.txt

hive>select * from guo_test

OK

santi you are a zhazha.12345678

在该文件夹中找到了所写入hive数据仓库的文件。

[注]本地数据写入成功，但是从本地将数据导入到Hive表的过程中，其实是友行先将数据临时复制到HDFS的一个目录下（典型的情况是复制到上传用户的HDFS home目录下,比如/home/santi/），然后再将数据从临时目录下移动到对应的Hive表的数据目录里面(临时目录不保留数据)。

二．从HDFS文件系统中导入数据到Hive表

1.在HDFS文件系统上建立数据文件

hdfs上没有vim命令，则需要将本地数据文件手动传入到HDFS上

/data/hive# vim data_HDtoHive/data/hive# cat data_HDtoHivedata from, HDFS to Hive #hadoop fs -put /home/santi/data/hive/data_HDtoHive /usr/data/input//数据传入# hadoop fs -ls /usr/data/input12345

2导入数据

hive>load data inpath '/usr/data/input/data_HDtoHive' into table guo_test

hive>select * from guo_test

OK

data fromHDFS to Hive

santi you are a zhazha.

Time taken: 0.172 seconds, Fetched: 2 row(s)123456

数据写入成功

数据存hive配置的数据存储位置中。

[注]

从本地导入数据语句为

hive>load data local inpath ‘/home/santi/data/hive/hive_test.txt’ into table guo_test

从HDFS中导入数据的语句为

hive>load data inpath ‘/usr/data/input/data_HDtoHive’ into table guo_test

差距在local这个命令这里。

而从HDFS系统上导入到Hive表的时候，数据转移。HDFS系统上查找不到相关文件。

三．从HIVE表选取数据插入新的HIVE表

命令为create table 表名 as selecr xxx from 表名。

hive>create table hivedata_test1

>as

>select name

>from guo_test

hive>select * from hivedata_test1

OK

data fromsanti

Time taken: 0.116 seconds, Fetched: 2 row(s)123456789

[注]hive是分区表有稍微区别

在Hive中，表的每一个分区对应表下的相应目录，所有分区的数据都是存储在对应的目录中。比表有a和b两个分区，则对应a=xxx,b=xx对应表的目录为/user/hive/warehouse/a=xxx

user/hive/warehouse/b=xx，所有属于这个分区的数据都存放在这个目录中。

hive>create table hivedata_test2(

>Name string)

>partitioned by

>(String string)

>ROW FORMAT DELIMITED

>FIELDS TERMINATED BY ','>STORED AS TEXTFILE

hive>insert into table hivedata_test2

>partition(String='best')

>select Name

>from guo_test

hive>select * from hivedata_test2

OK

data from best

santi best

Time taken: 1.549 seconds, Fetched: 2 row(s)# hadoop fs -ls /usr/hive/warehouse/hivedata_test2Found 1 items

drwxrwxr-x -santiago supergroup 0 2017-02-14 17:40

/usr/hive/warehouse/hivedata_test2/string=best

（1）、从本地文件系统中导入数据到 Hive 表；

（2）、从 HDFS 上导入数据到 Hive 表；

（3）、从别的表中查询出相肆敏应的数据并导入到 Hive 表中；

（4）、在创建表的段雹雀时候通过从别的表中查询出相应的记录并握早插入到所创建的表中。

---