Hive 常用操作，
Hive 操作
（一）表操作
Hive 和 Mysql 的表操作语句类似，如果熟悉 Mysql，那Hive 的表操作就非常容易，下面对 Hive 的表操作进行深入讲解。
(1)先来创建一个表名为student1的内部表
hive> CREATE TABLE IF NOT EXISTS student1
(sno INT,sname STRING,age INT,sex STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;
建表规则如下：
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format]
[STORED AS file_format] [LOCATION hdfs_path]
•CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常
•EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径
（LOCATION）
•LIKE 允许用户复制现有的表结构，但是不复制数据
•COMMENT可以为表与字段增加描述
•ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
•STORED AS
SEQUENCEFILE
| TEXTFILE
| RCFILE
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用
STORED AS SEQUENCE 。
（2）创建外部表
hive> CREATE EXTERNAL TABLE IF NOT EXISTS student2
> (sno INT,sname STRING,age INT,sex STRING)
> ROW FORMAT DELIMITED
> FIELDS TERMINATED BY '\t'
> STORED AS TEXTFILE
> LOCATION '/user/external';
OK
Time taken: 0.331 seconds
hive> show tables;
OK student1 student2
Time taken: 0.06 seconds, Fetched: 12 row(s)
（3）删除表
首先创建一个表名为test1的表
hive> CREATE TABLE IF NOT EXISTS test1 > (id INT,name STRING);
OK
Time taken: 0.07 seconds
然后查看一下是否有test1表
hive> SHOW TABLES;
OK student1 student2 test1
Time taken: 0.042 seconds, Fetched: 12 row(s)
用命令删test1表
hive> DROP TABLE test1;
OK
Time taken: 0.191 seconds
查看test1表是否删除
hive> SHOW TABLES;
OK student1 student2
Time taken: 0.027 seconds, Fetched: 11 row(s)
（4）修改表的结构，比如为表增加字段
首先看一下student1表的结构
hive> DESC student1;
OK
sno int
sname string
age int
sex string
Time taken: 0.886 seconds, Fetched: 4 row(s)
为表student1增加两个字段
hive> ALTER TABLE student1 ADD COLUMNS > (address STRING,grade STRING);
OK
Time taken: 0.241 seconds
再查看一下表的结构，看是否增加
hive> DESC student1;
OK
sno int
sname string
age int
sex string
address string
grade string
Time taken: 0.154 seconds, Fetched: 6 row(s)
（5）修改表名student1为student3
hive> ALTER TABLE student1 RENAME TO student3;
OK
Time taken: 0.172 seconds
查看一下
hive> SHOW TABLES;
OK student2 student3
Time taken: 0.088 seconds, Fetched: 11 row(s)
下面我们再改回来
hive> ALTER TABLE student3 RENAME TO student1;
OK
Time taken: 0.153 seconds
查看一下
hive> SHOW TABLES;
OK student1 student2
Time taken: 0.064 seconds, Fetched: 11 row(s)
（6）创建和已知表相同结构的表
hive> CREATE TABLE copy_student1 LIKE student1;
OK
Time taken: 1.109 seconds
查看一下
hive> SHOW TABLES;
OK copy_student1 student1 student2
Time taken: 0.083 seconds, Fetched: 12 row(s)
2、加入导入数据的方法
（数据里可以包含重复记录），只有导入了数据，才能供后边的查询使用
（1）加载本地数据load
首先看一下表的结构
hive> DESC student1;
OK
sno int
sname string
age int
sex string
address string
grade string
Time taken: 1.018 seconds, Fetched: 6 row(s)
创建/home/hadoop/data目录，并在该目录下创建student1.txt文件，添加如下内容
|
201501001 |
张三 |
22 |
男 |
北京 |
大三 |
|
201501003 |
李四 |
23 |
男 |
上海 |
大二 |
|
201501004 |
王娟 |
22 |
女 |
广州 |
大三 |
|
201501010 |
周王 |
24 |
男 |
深圳 |
大四 |
|
201501011 |
李红 |
23 |
女 |
北京 |
大三 |
加载数据到student1表中
hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/student1.txt' INTO TABLE student1; Loading data to table default.student1
Table default.student1 stats: [numFiles=1, numRows=0, totalSize=191, rawDataSize=0]
OK
Time taken: 0.766 seconds
查看是否加载成功
|
hive> SELECT * FROM student1; |
|
|
|
||
|
OK |
|
|
|
|
|
|
201501001 |
张三 |
22 |
男 |
北京 |
大三 |
|
201501003 |
李四 |
23 |
男 |
上海 |
大二 |
|
201501004 |
王娟 |
22 |
女 |
广州 |
大三 |
|
201501010 |
周王 |
24 |
男 |
深圳 |
大四 |
|
201501011 |
李红 |
23 |
女 |
北京 |
大三 |
Time taken: 0.512 seconds, Fetched: 5 row(s)
（2）加载hdfs中的文件
首先将文件student1.txt上传到hdfs文件系统对应目录上
|
[hadoop@hadoop01 hadoop]$ hadoop fs |
‐put /home/hadoop/data/student1.txt /user/hive |
|||||
|
16/05/16 17:15:43 WARN util.NativeCodeLoader: Unable to load |
native‐hadoop library for your platform.. |
|||||
|
[hadoop@hadoop01 hadoop]$ hadoop fs |
‐ls /user/hive |
|
|
|
||
|
16/05/16 17:16:15 WARN util.NativeCodeLoader: Unable to load |
native‐hadoop library for your platform.. |
|||||
|
Found 2 items |
|
|
|
|
|
|
|
‐rw‐r‐‐r‐‐ |
3 hadoop supergroup |
191 2016‐05‐19 |
03:27 |
/user/hive/student1.txt |
||
|
drwxr‐xr‐x |
‐ hadoop supergroup |
0 2016‐05‐19 |
02:46 |
/user/hive/warehouse |
||
|
|
|
|
|
|
|
|
加载hdfs中的文件数据到copy_student1表中
hive> LOAD DATA INPATH '/user/hive/student1.txt' INTO TABLE copy_student1; Loading data to table default.copy_student1
Table default.copy_student1 stats: [numFiles=1, totalSize=191]
OK
Time taken: 1.354 seconds
查看是否加载成功
|
hive> SELECT * FROM copy_student1; |
|
|
|||
|
OK |
|
|
|
|
|
|
201501001 |
张三 |
22 |
男 |
北京 |
大三 |
|
201501003 |
李四 |
23 |
男 |
上海 |
大二 |
|
201501004 |
王娟 |
22 |
女 |
广州 |
大三 |
|
201501010 |
周王 |
24 |
男 |
深圳 |
大四 |
|
201501011 |
李红 |
23 |
女 |
北京 |
大三 |
Time taken: 0.44 seconds, Fetched: 5 row(s)
（3）表插入数据（单表插入、多表插入）
1）单表插入
首先创建一个表copy_student2,表结构和student1相同
hive> CREATE TABLE copy_student2 LIKE student1;
OK
Time taken: 0.586 seconds
查看一下是否创建成功
hive> SHOW TABLES;
OK copy_student1 copy_student2 student1 student2
Time taken: 0.073 seconds, Fetched: 13 row(s)
看一下copy_student2表的表结构
hive> DESC copy_student2;
OK
sno int
sname string
age int
sex string
address string
grade string
Time taken: 0.121 seconds, Fetched: 6 row(s)
把表student1中的数据插入到copy_student2表中
hive> INSERT OVERWRITE TABLE copy_student2 SELECT * FROM student1;
查看数据是否插入
|
hive> SELECT * FROM copy_student2; |
|
|
|||
|
OK |
|
|
|
|
|
|
201501001 |
张三 |
22 |
男 |
北京 |
大三 |
|
201501003 |
李四 |
23 |
男 |
上海 |
大二 |
|
201501004 |
王娟 |
22 |
女 |
广州 |
大三 |
|
201501010 |
周王 |
24 |
男 |
深圳 |
大四 |
|
201501011 |
李红 |
23 |
女 |
北京 |
大三 |
Time taken: 0.107 seconds, Fetched: 5 row(s)
2）多表插入先创建两个表
hive> CREATE TABLE copy_student3 LIKE student1;
OK
Time taken: 0.622 seconds
hive> CREATE TABLE copy_student4 LIKE student1;
OK
Time taken: 0.162 seconds
向多表插入数据
hive> FROM student1
INSERT OVERWRITE TABLE copy_student3
SELECT *
INSERT OVERWRITE TABLE copy_student4
SELECT *;
查看结果
|
|
|
|
|
|
|
|
hive> SELECT * FROM copy_student3; |
|
|
|||
|
OK |
|
|
|
|
|
|
201501001 |
张三 |
22 |
男 |
北京 |
大三 |
|
201501003 |
李四 |
23 |
男 |
上海 |
大二 |
|
201501004 |
王娟 |
22 |
女 |
广州 |
大三 |
|
201501010 |
周王 |
24 |
男 |
深圳 |
大四 |
|
201501011 |
李红 |
23 |
女 |
北京 |
大三 |
|
Time taken: 0.103 seconds, Fetched: 5 row(s) |
|
||||
|
hive> SELECT * FROM copy_student4; |
|
|
|||
|
OK |
|
|
|
|
|
|
201501001 |
张三 |
22 |
男 |
北京 |
大三 |
|
201501003 |
李四 |
23 |
男 |
上海 |
大二 |
|
201501004 |
王娟 |
22 |
女 |
广州 |
大三 |
|
201501010 |
周王 |
24 |
男 |
深圳 |
大四 |
|
201501011 |
李红 |
23 |
女 |
北京 |
大三 |
Time taken: 0.071 seconds, Fetched: 5 row(s)
3、有关表的内容的查询
（1）查表的所有内容
|
hive> SELECT * FROM student1; |
|
|
|
||
|
OK |
|
|
|
|
|
|
201501001 |
张三 |
22 |
男 |
北京 |
大三 |
|
201501003 |
李四 |
23 |
男 |
上海 |
大二 |
|
201501004 |
王娟 |
22 |
女 |
广州 |
大三 |
|
201501010 |
周王 |
24 |
男 |
深圳 |
大四 |
|
201501011 |
李红 |
23 |
女 |
北京 |
大三 |
Time taken: 1.201 seconds, Fetched: 5 row(s)
（2）查表的某个字段的属性
hive> SELECT sname FROM student1;
OK
张三
李四
王娟
周王
李红
Time taken: 1.22 seconds, Fetched: 5 row(s)
（3）where条件查询
hive> SELECT * FROM student1 WHERE sno>201501004 AND address="北京";
OK
201501011 李红 23 女 北京 大三 Time taken: 0.873 seconds, Fetched: 1 row(s)
（4）all和distinct的区别（这就要求表中要有重复的记录，或者某个字段要有重复的数据）
hive> SELECT ALL age,grade FROM student1;
OK
22 大三
23 大二
|
22 |
大三 |
|
24 |
大四 |
|
23 |
大三 |
Time taken: 0.448 seconds, Fetched: 5 row(s)
hive> SELECT age,grade FROM student1;
OK
22 大三
23 大二
|
22 |
大三 |
|
24 |
大四 |
|
23 |
大三 |
Time taken: 0.072 seconds, Fetched: 5 row(s)
hive> SELECT DISTINCT age,grade FROM student1;
OK
22 大三
23 大三
23 大二
24 大四
Time taken: 127.397 seconds, Fetched: 4 row(s)
hive> SELECT DISTINCT age FROM student1;
OK 22 23 24
Time taken: 106.21 seconds, Fetched: 3 row(s)
（5）limit限制查询
hive> SELECT * FROM student1 LIMIT 4;
OK
201501001 张三 22 男北京大三
201501003 李四 23 男上海大二
201501004 王娟 22 女广州大三
201501010 周王 24 男深圳大四
Time taken: 0.253 seconds, Fetched: 4 row(s)
(6) GROUP BY 分组查询
group by 分组查询在数据统计时比较常用，接下来讲解 group by 的使用。
1) 创建一个表 group_test，表的内容如下。
hive> create table group_test(uid STRING,gender STRING,ip STRING) row format delimited fields terminat
向 group_test 表中导入数据。
hive> LOAD DATA LOCAL INPATH '/home/hadoop/hadoop/user.txt' INTO TABLE group_test;
2) 计算表的行数命令如下。
hive> select count(*) from group_test;
3) 根据性别计算去重用户数。
首先创建一个表 group_gender_sum
hive> create table group_gender_sum(gender STRING,sum INT);
将表 group_test 去重后的数据导入表 group_gender_sum。
hive> insert overwrite table group_gender_sum select group_test.gender,count(distinct group_test.uid)
同时可以做多个聚合操作，但是不能有两个聚合操作有不同的 distinct 列。下面正确合法的聚合操作语句。
首先创建一个表 group_gender_agg
hive> create table group_gender_agg(gender STRING,sum1 INT,sum2 INT,sum3 INT);
将表 group_test 聚合后的数据插入表 group_gender_agg。
hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),
但是，不允许在同一个查询内有多个 distinct 表达式。下面的查询是不允许的。
hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),
这条查询语句是不合法的，因为 distinct group_test.uid 和 distinct group_test.ip 操作了
uid 和 ip 两个不同的列。
(7) ORDER BY 排序查询
ORDER BY 会对输入做全局排序，因此只有一个 Reduce（多个 Reduce 无法保证全局有序）会导致当输入规模较大时，需要较长的计算时间。使用 ORDER BY 查询的时候，为了优
化查询的速度，使用 hive.mapred.mode 属性。
hive.mapred.mode = nonstrict;(default value/默认值) hive.mapred.mode=strict;
与数据库中 ORDER BY 的区别在于，在 hive.mapred.mode=strict 模式下必须指定limit ，
否则执行会报错。
hive> set hive.mapred.mode=strict;
hive> select * from group_test order by uid limit 5; Total jobs = 1
.............
Total MapReduce CPU Time Spent: 4 seconds 340 msec
|
OK |
|
|
|
01 |
male |
192.168.1.2 |
|
01 |
male |
192.168.1.32 |
|
01 |
male |
192.168.1.26 |
|
01 |
male |
192.168.1.22 |
|
02 |
female |
192.168.1.3 |
Time taken: 58.04 seconds, Fetched: 5 row(s)
(8) SORT BY 查询
sort by 不受 hive.mapred.mode 的值是否为 strict 和 nostrict 的影响。sort by 的数据只
能保证在同一个 Reduce 中的数据可以按指定字段排序。
使用 sort by 可以指定执行的 Reduce 个数（set mapred.reduce.tasks=< number>）这
样可以输出更多的数据。对输出的数据再执行归并排序，即可以得到全部结果。
hive> set hive.mapred.mode=strict;
hive> select * from group_test sort by uid ;
Total MapReduce CPU Time Spent: 4 seconds 450 msec
|
OK |
|
|
|
01 |
male |
192.168.1.2 |
|
01 |
male |
192.168.1.32 |
|
01 |
male |
192.168.1.26 |
|
01 |
male |
192.168.1.22 |
|
02 |
female |
192.168.1.3 |
|
03 |
male |
192.168.1.23 |
|
03 |
male |
192.168.1.5 |
|
04 |
male |
192.168.1.9 |
|
05 |
male |
192.168.1.8 |
|
05 |
male |
192.168.1.29 |
|
06 |
female |
192.168.1.201 |
|
06 |
female |
192.168.1.52 |
|
06 |
female |
192.168.1.7 |
|
07 |
female |
192.168.1.11 |
|
08 |
female |
192.168.1.21 |
|
08 |
female |
192.168.1.62 |
|
08 |
female |
192.168.1.88 |
|
08 |
female |
192.168.1.42 |
Time taken: 77.875 seconds, Fetched: 18 row(s)
(9) DISTRIBUTE BY 排序查询
按照指定的字段对数据划分到不同的输出 Reduce 文件中，操作如下。
hive> insert overwrite local directory '/home/hadoop/hadoop/test' select * from group_test distribute by
此方法根据 gender 的长度划分到不同的 Reduce 中，最终输出到不同的文件中。length 是内建函数，也可以指定其它的函数或者使用自定义函数。
hive> insert overwrite local directory '/home/hadoop/hadoop/test' select * from group_test order by gende
order by gender 与 distribute by length(gender) 不能共用。
(10) CLUSTER BY 查询
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。