本文详细介绍了Hive的各种操作，包括表的创建、删除、修改和加载数据，以及视图、索引、分区、桶操作。讲解了Hive的JOIN用法、内置操作符和函数，还涉及到权限控制、JDBC连接示例以及性能调优策略。通过对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'; hive> show tables;
OK
student1
student2（3）删除表
首先创建一个表名为test1的表
hive> CREATE TABLE IF NOT EXISTS test1
> (id INT,name STRING); 然后查看一下是否有test1表
hive> SHOW TABLES;
OK
student1
student2
test1用命令删test1表
hive> DROP TABLE test1;
OK查看test1表是否删除
hive> SHOW TABLES;
OK
student1
student2（4）修改表的结构，比如为表增加字段
首先看一下student1表的结构
hive> DESC student1;
OK
sno int
sname string
age int
sex string 为表student1增加两个字段
hive> ALTER TABLE student1 ADD COLUMNS
> (address STRING,grade STRING);再查看一下表的结构，看是否增加
hive> DESC student1;
OK
sno int
sname string
age int
sex string
address string
grade string （5）修改表名student1为student3
hive> ALTER TABLE student1 RENAME TO student3;查看一下
hive> SHOW TABLES;
OK
student2
student3下面我们再改回来
hive> ALTER TABLE student3 RENAME TO student1;查看一下
hive> SHOW TABLES;
OK
student1
student2（6）创建和已知表相同结构的表
hive> CREATE TABLE copy_student1 LIKE student1;查看一下
hive> SHOW TABLES;
OK
copy_student1
student1
student22、加入导入数据的方法，（数据里可以包含重复记录），只有导入了数据，才能供后边的查询使用
（1）加载本地数据load
首先看一下表的结构
hive> DESC student1;
OK
sno int
sname string
age int
sex string
address string
grade string 创建/home/hadoop/data目录，并在该目录下创建student1.txt文件，添加如下内容
201901001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四
201901005 李红 23 女 北京 大三
加载数据到student1表中
hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/student1.txt' INTO TABLE student1;查看是否加载成功
hive> SELECT * FROM student1;
OK
201901001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四
201901005 李红 23 女 北京 大三（2）加载hdfs中的文件
首先将文件student1.txt上传到hdfs文件系统对应目录上
[hadoop@slave hadoop]$ hadoop fs -put /home/hadoop/data/student1.txt /user/hive
[hadoop@slave hadoop]$ hadoop fs -ls /user/hive加载hdfs中的文件数据到copy_student1表中
hive> LOAD DATA INPATH '/user/hive/student1.txt' INTO TABLE copy_student1;查看是否加载成功
hive> SELECT * FROM copy_student2;
OK
201501001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四
201901005 李红 23 女 北京 大三（3）表插入数据（单表插入、多表插入）
1）单表插入
首先创建一个表copy_student2,表结构和student1相同
hive> CREATE TABLE copy_student2 LIKE student1;查看一下是否创建成功
hive> SHOW TABLES;
OK
copy_student1
copy_student2
student1
student2看一下copy_student2表的表结构
hive> DESC copy_student2;
OK
sno int
sname string
age int
sex string
address string
grade string 把表student1中的数据插入到copy_student2表中
hive> INSERT OVERWRITE TABLE copy_student2 SELECT * FROM student1;查看数据是否插入
hive> SELECT * FROM copy_student2;
OK
201901001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四
201901005 李红 23 女 北京 大三2）多表插入
先创建两个表
hive> CREATE TABLE copy_student3 LIKE student1;
hive> CREATE TABLE copy_student4 LIKE student1;向多表插入数据
hive> FROM student1
> INSERT OVERWRITE TABLE copy_student3
> SELECT *
> INSERT OVERWRITE TABLE copy_student4
> SELECT *;查看结果
hive> SELECT * FROM copy_student3;
OK
201901001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四
201901005 李红 23 女 北京 大三hive> SELECT * FROM copy_student4;
OK
201901001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四
201901005 李红 23 女 北京 大三3、有关表的内容的查询
（1）查表的所有内容
hive> SELECT * FROM student1;
OK
201901001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四
201901005 李红 23 女 北京 大三（2）查表的某个字段的属性
hive> SELECT sname FROM student1;
OK
张三
李四
王娟
周王
李红（3）where条件查询
hive> SELECT * FROM student1 WHERE sno>201901004 AND address="北京";
OK
201901005 李红 23 女 北京 大三（4）all和distinct的区别（这就要求表中要有重复的记录，或者某个字段要有重复的数据）
hive> SELECT ALL age,grade FROM student1;
OK
22 大三
23 大二
22 大三
24 大四
23 大三hive> SELECT age,grade FROM student1;
OK
22 大三
23 大二
22 大三
24 大四
23 大三hive> SELECT DISTINCT age,grade FROM student1;
OK
22 大三
23 大三
23 大二
24 大四hive> SELECT DISTINCT age FROM student1;
OK
22
23
24（5）limit限制查询
hive> SELECT * FROM student1 LIMIT 4;
OK
201901001 张三 22 男 北京 大三
201901002 李四 23 男 上海 大二
201901003 王娟 22 女 广州 大三
201901004 周王 24 男 深圳 大四(6) GROUP BY 分组查询
group by 分组查询在数据统计时比较常用
1) 创建一个表 group_test，表的内容如下。
hive> create table group_test(uid STRING,gender STRING,ip STRING) row format delimited fields terminated by '\t' STORED AS TEXTFILE;向 group_test 表中导入数据。
hive> LOAD DATA LOCAL INPATH '/home/hadoop/data/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) 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
......
最低0.47元/天 解锁文章
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
