(一)表操作
(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
student2
2、加入导入数据的方法,(数据里可以包含重复记录),只有导入了数据,才能供后边的查询使用
(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
......