Hive基本语法操练

弓背霞明剑照霜，秋风走马出咸阳。这篇文章主要讲述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
snoint
snamestring
ageint
sexstring
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
snoint
snamestring
ageint
sexstring
addressstring
gradestring
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
snoint
snamestring
ageint
sexstring
addressstring
gradestring
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@djt01 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... using builtin-java classes where applicable
[hadoop@djt01 hadoop]$ hadoop fs -ls /user/hive
16/05/16 17:16:15 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 2 items
-rw-r--r--3 hadoop supergroup191 2016-05-19 03:27 /user/hive/student1.txt
drwxr-xr-x- hadoop supergroup0 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
snoint
snamestring
ageint
sexstring
addressstring
gradestring
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限制查询
pre class="html"> 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 terminated by \\tSTORED AS TEXTFILE;

向 group_test 表中导入数据。

hive> LOAD DATA LOCAL INPATH /home/hadoop/djt/user.txtINTO 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) from group_test group by group_test.gender;

同时可以做多个聚合操作，但是不能有两个聚合操作有不同的 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),count(*),sum(distinct group_test.uid) from group_test group by group_test.gender;

但是，不允许在同一个查询内有多个 distinct 表达式。下面的查询是不允许的。

hive> insert overwrite table group_gender_agg select group_test.gender,count(distinct group_test.uid),count(distinct group_test.ip) from group_test group by group_test.gender;

这条查询语句是不合法的，因为 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
01male192.168.1.2
01male192.168.1.32
01male192.168.1.26
01male192.168.1.22
02female192.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
01male192.168.1.2
01male192.168.1.32
01male192.168.1.26
01male192.168.1.22
02female192.168.1.3
03male192.168.1.23
03male192.168.1.5
04male192.168.1.9
05male192.168.1.8
05male192.168.1.29
06female192.168.1.201
06female192.168.1.52
06female192.168.1.7
07female192.168.1.11
08female192.168.1.21
08female192.168.1.62
08female192.168.1.88
08female192.168.1.42
Time taken: 77.875 seconds, Fetched: 18 row(s)

(9) DISTRIBUTE BY 排序查询
按照指定的字段对数据划分到不同的输出 Reduce 文件中，操作如下。

hive> insert overwrite local directory /home/hadoop/djt/test select * from group_test distribute by length(gender);

此方法根据 gender 的长度划分到不同的 Reduce 中，最终输出到不同的文件中。length 是内建函数，也可以指定其它的函数或者使用自定义函数。

hive> insert overwrite local directory /home/hadoop/djt/test select * from group_test order by genderdistribute by length(gender);

order by gender 与 distribute by length(gender) 不能共用。

(10) CLUSTER BY 查询
cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。
(二)视图操作
1) 创建一个测试表。

hive> create table test(id int,name string);
OK
Time taken: 0.385 seconds
hive> desc test;
OK
idint
namestring
Time taken: 0.261 seconds, Fetched: 2 row(s)

2) 基于表 test 创建一个 test_view 视图。

hive> create view test_view(id,name_length) as select id,length(name) from test;

3) 查看 test_view 视图属性。

hive> desc test_view;

4) 查看视图结果。

hive> select * from test_view;

（三）索引操作
1) Hive 创建索引。

hive> create index user_index on table user(id) as org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler with deferred rebuild IN TABLE user_index_table;

2) 更新数据。

hive> alter index user_index on user rebuild;

3) 删除索引

hive> drop index user_index on user;

4) 查看索引

hive> show index on user;

5) 创建表和索引案例

hive> create table index_test(id INT,name STRING) PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FILEDS TERMINATED BY ,;

创建一个索引测试表 index_test，dt作为分区属性，“ROW FORMAT DELIMITED FILEDS TERMINATED BY ,” 表示用逗号分割字符串，默认为‘\\001’。
6) 创建一个临时索引表 index_tmp。

hive> create table index_tmp(id INT,name STRING,dt STRING) ROW FORMAT DELIMITED FILEDS TERMINATED BY ,;

7) 加载本地数据到 index_tmp 表中。

hive> load data local inpath /home/hadoop/djt/test.txt into table index_tmp;

设置 Hive 的索引属性来优化索引查询，命令如下。

hive> set hive.exec.dynamic.partition.mode=nonstrict; ----设置所有列为 dynamic partition
hive> set hive.exec.dynamic.partition=true; ----使用动态分区

8) 查询index_tmp 表中的数据，插入 table_test 表中。

hive> insert overwrite table index_test partition(dt) select id,name,dt from index_tmp;

9) 使用 index_test 表，在属性 id 上创建一个索引 index1_index_test 。

hive> create index index1_index_test on table index_test(id) as org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler WITH DEFERERD REBUILD;

10) 填充索引数据。

hive> alter index index1_index_test on index_test rebuild;

11) 查看创建的索引。

hive> show index on index_test

12) 查看分区信息。

hive> show partitions index_test;

13) 查看索引数据。

$ hadoop fs -ls /usr/hive/warehouse/default_index_test_index1_index_test_

14) 删除索引。

hive> drop index index1_index_test on index_test;
show index on index_test;

【Hive基本语法操练】15) 索引数据也被删除。

$ hadoop fs -ls /usr/hive/warehouse/default_index_test_index1_index_test_
no such file or directory

& 16) 修改配置文件信息。

< property>
< name> hive.optimize.index.filter< /name>
< value> true< /value>
< /property>
< property>
< name> hive.optimize.index.groupby< /name>
< value> true< /value>
< /