Hive基础（二）

大数据

Hive环境搭建

参考之前搭建Hadoop环境，依旧基于docker-compose来搭建Hadoop+Hive环境。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
version: "3"

networks:
  default:
    driver: bridge
    
services:
  namenode:
    image: bde2020/hadoop-namenode
    container_name: namenode
    ports:
      - 9870:9870
      - 9000:9000
    restart: "always"
    # volumes:
    #   - "hadoop_datanode:/hadoop/dfs/name"
    environment:
      CLUSTER_NAME: "docker-hadoop-cluster"
    env_file:
      - ./env.env

  datanode:
    image: bde2020/hadoop-datanode
    container_name: datanode
    depends_on:
      - namenode
    ports:
      - 9864:9864
    restart: "always"
    # volumes:
    #   - "hadoop_datanode:/hadoop/dfs/data"
    environment:
      SERVICE_PRECONDITION: "namenode:9870"
    links:
      - "namenode:namenode"
    env_file:
      - ./env.env
  
  resourcemanager:
    image: bde2020/hadoop-resourcemanager
    container_name: resourcemanager
    ports:
      - 8088:8088
    links:
      - "namenode:namenode"
      - "datanode:datanode"
    depends_on:
      - namenode
      - datanode
    restart: "always"
    environment:
      SERVICE_PRECONDITION: "namenode:9000 namenode:9870 datanode:9864"
    env_file:
      - ./env.env

  nodemanager:
    image: bde2020/hadoop-nodemanager
    container_name: nodemanager
    links:
      - "namenode:namenode"
      - "datanode:datanode"
      - "resourcemanager:resourcemanager"
    depends_on:
      - namenode
      - datanode
      - resourcemanager
    restart: "always"
    environment:
      SERVICE_PRECONDITION: "namenode:9000 namenode:9870 datanode:9864 resourcemanager:8088"
    env_file:
      - ./env.env
  
  historyserver:
    image: bde2020/hadoop-historyserver
    container_name: historyserver
    links:
      - "namenode:namenode"
      - "datanode:datanode"
      - "resourcemanager:resourcemanager"
    depends_on:
      - namenode
      - datanode
      - resourcemanager
    restart: "always"
    # volumes:
    #   - "hadoop_historyserver:/hadoop/yarn/timeline"
    environment:
      SERVICE_PRECONDITION: "namenode:9000 namenode:9870 datanode:9864 resourcemanager:8088"
    env_file:
      - ./env.env
      
  hive-metastore-postgresql:
    image: bde2020/hive-metastore-postgresql:2.3.0
    container_name: hive-metastore-postgresql
    restart: "always"
    ports:
      - "5432:5432"
      
  hive-metastore:
    image: bde2020/hive:2.3.2-postgresql-metastore
    container_name: hive-metastore
    env_file:
      - ./env.env
    command: /opt/hive/bin/hive --service metastore
    links:
      - "namenode:namenode"
      - "datanode:datanode"
      - "hive-metastore-postgresql:hive-metastore-postgresql"
    depends_on:
      - namenode
      - datanode
      - hive-metastore-postgresql
    restart: "always"
    environment:
      SERVICE_PRECONDITION: "namenode:9870 datanode:9864 hive-metastore-postgresql:5432"
    ports:
      - "9083:9083"
  
  hive-server:
    image: bde2020/hive:2.3.2-postgresql-metastore
    container_name: hive-server
    env_file:
      - ./env.env
    links:
      - "hive-metastore:hive-metastore"
      - "hive-metastore-postgresql:hive-metastore-postgresql"
    depends_on:
      - hive-metastore
      - hive-metastore-postgresql
    restart: "always"
    environment:
      SERVICE_PRECONDITION: "hive-metastore:9083 hive-metastore-postgresql:5432"
    ports:
      - "10000:10000"
      
  presto:
    image: shawnzhu/prestodb:0.181
    container_name: presto
    ports:
      - "8080:8080"

# volumes:
#   hadoop_namenode:
#   hadoop_datanode:
#   hadoop_historyserver:
进入Hive命令行操作。

1
2
docker exec -it hive-server bash

1
2
3
4
5
6
7
8
9
10
create table test(
  id      int
  ,name    string
  )
  row format delimited
  fields terminated by ',';

load data local inpath '/tmp/test.txt' into table test;

select * from test;

利用DataGrip等工具进行操作。

Hive常用DDL语句

数据库操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 查看数据列表
SHOW DATABASES;
-- 使用数据库
USE database_name;

-- 创建数据库
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name   --DATABASE|SCHEMA 是等价的
  [COMMENT database_comment] --数据库注释
  [LOCATION hdfs_path] --存储在 HDFS 上的位置
  [WITH DBPROPERTIES (property_name=property_value, ...)]; --指定额外属性
-- 示例
CREATE DATABASE IF NOT EXISTS hive_test
  COMMENT 'hive database for test'
  LOCATION '/hive_test'
  WITH DBPROPERTIES ('creater'='H3rmesk1t');

-- 修改数据库, 可以使用ALTER DATABASE命令来修改数据库的一些属性, 但是数据库的元数据信息是不可更改的, 包括数据库的名称以及数据库所在的位置
ALTER DATABASE test_hive set dbproperties('createtime'='20260617');

-- 查看数据库信息
DESC DATABASE [EXTENDED] db_name; --EXTENDED 表示是否显示额外属性
-- 示例
DESC DATABASE EXTENDED hive_test;

-- 删除数据库
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
-- 默认行为是RESTRICT, 如果数据库中存在表则删除失败
-- 要想删除库及其中的表, 可以使用CASCADE级联删除
-- 示例
drop database demohive cascade;

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 建表语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name     --表名
  [(col_name data_type [COMMENT col_comment],
    ... [constraint_specification])]  --列名 列数据类型
  [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
  ]  --分桶表分桶规则
  [SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
   [STORED AS DIRECTORIES]
  ]  --指定倾斜列和值
  [
   [ROW FORMAT row_format]
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]  -- 指定行分隔符、存储文件格式或采用自定义存储格式
  [LOCATION hdfs_path]  -- 指定表的存储位置
  [TBLPROPERTIES (property_name=property_value, ...)]  --指定表的属性
  [AS select_statement];   --从查询结果创建表

内部表

1
2
3
4
5
6
7
8
9
10
11
-- 内部表建表示例
CREATE TABLE emp(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2),
  deptno INT)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

外部表

1
2
3
4
5
6
7
8
9
10
11
12
-- 外部表建表示例
CREATE EXTERNAL TABLE emp_external(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2),
  deptno INT)
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_external';
1
2
3
4
5
6
7
-- 从本地文件系统向表中加载数据
-- 追加操作
load data local inpath '/export/servers/hivedatas/emp.csv' into table emp_external;
-- 覆盖操作
load data local inpath '/export/servers/hivedatas/emp.csv' overwrite into table emp_external;

-- 使用load data local表示从本地文件系统加载, 文件会拷贝到hdfs上
1
2
3
4
5
6
-- 从hdfs文件系统向表中加载数据
load data inpath '/hivedatas/emp.csv' into table emp_external;
-- 加载数据到指定分区
load data inpath '/hivedatas/emp.csv' into table emp_external partition(cur_date=20250617);

-- 使用load data表示从hdfs文件系统加载, 文件会直接移动到hive相关目录下, 注意不是拷贝过去, 因为hive认为hdfs文件已经有3副本了, 没必要再次拷贝

分区表

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 分区表建表示例
CREATE EXTERNAL TABLE emp_partition(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2)
  )
  PARTITIONED BY (deptno INT)   -- 按照部门编号进行分区
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_partition';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 加载数据到一个分区的表中
load data local inpath '/export/servers/hivedatas/emp.csv' into table emp_partition partition (month='201806');
-- 加载数据到一个多分区的表中去
load data local inpath '/export/servers/hivedatas/emp.csv' into table emp_partition partition(year='2018',month='06',day='01');
-- 查看分区
show partitions emp;
-- 添加一个分区
alter table score add partition(month='201805');
-- 同时添加多个分区
alter table score add partition(month='201804') partition(month='201803');
-- 删除分区
alter table score drop partition(month='201806');

-- 添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹
-- hive表创建的时候可以用location指定一个文件或者文件夹, 当指定文件夹时, hive会加载文件夹下的所有文件; 当表中无分区时, 这个文件夹下不能再有文件夹, 否则报错
-- 当表是分区表时, 比如partitioned by (day string), 则这个文件夹下的每一个文件夹就是一个分区

分桶表

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 分桶表建表示例
CREATE EXTERNAL TABLE emp_bucket(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2),
  deptno INT)
  CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS  --按照员工编号散列到四个 bucket 中
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_bucket';
1
2
3
4
5
-- 桶表中加载数据
insert overwrite table emp_bucket select * from emp_common cluster by(c_id);  -- 最后指定桶字段

-- 由于桶表无法通过hdfs dfs -put 文件或者通过load data加载数据, 只能通过insert overwrite进行加载
-- 所以把文件加载到桶表中, 需要先创建普通表并通过insert overwrite的方式将普通表的数据通过查询的方式加载到桶表当中去

倾斜表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 倾斜表建表示例
-- 通过指定一个或者多个列经常出现的值(严重偏斜), Hive 会自动将涉及到这些值的数据拆分为单独的文件; 在查询时如果涉及到倾斜值, 它就直接从独立文件中获取数据, 而不是扫描所有文件, 这使得性能得到提升
CREATE EXTERNAL TABLE emp_skewed(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2)
  )
  SKEWED BY (empno) ON (66,88,100)  --指定 empno 的倾斜值 66,88,100
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_skewed';

临时表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 临时表建表示例
-- 临时表仅对当前Session可见, 临时表的数据将存储在用户的暂存目录中, 并在会话结束后删除
-- 如果临时表与永久表表名相同, 则对该表名的任何引用都将解析为临时表, 而不是永久表
-- 临时表还具有以下两个限制: 不支持分区列, 不支持创建索引
CREATE TEMPORARY TABLE emp_temp(
  empno INT,
  ename STRING,
  job STRING,
  mgr INT,
  hiredate TIMESTAMP,
  sal DECIMAL(7,2),
  comm DECIMAL(7,2)
  )
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";

复制表结构

1
2
3
4
5
6
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name  --创建表表名
   LIKE existing_table_or_view_name  --被复制表的表名
   [LOCATION hdfs_path]; --存储位置

-- 示例
CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co LIKE emp

CTAS建表

1
2
-- Hive支持从查询语句的结果创建表
CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';

修改删除表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 修改表名称
ALTER TABLE table_name RENAME TO new_table_name;

-- 修改列信息
ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type
  [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
-- 示例
-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
-- 修改字段sal的名称, 并将其放置到empno字段后
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2) AFTER ename;
-- 为字段增加注释
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';

-- 增加列信息
ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');


-- 删除表
drop table score;
-- 清空表, 只能清空管理表, 也就是内部表, 清空外部表会产生错误
truncate table score;
-- 如果hdfs开启了回收站, drop删除的表数据是可以从回收站恢复的, 表结构恢复不了, 需要自己重新创建; truncate清空的表是不进回收站的, 所以无法恢复truncate清空的表

加载数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 直接向分区表中插入数据
insert into table score partition(month='201807') values ('001','002','100');

-- 通过load方式加载数据
load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');

-- 通过查询方式加载数据
insert overwrite table score2 partition(month='201806') select s_id,c_id,s_score from score1;

-- 查询语句中创建表并加载数据
create table score2 as select * from score1;

-- 在创建表是通过location指定加载数据的路径
create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';

-- export导出与import导入hive表数据(内部表操作)
create table techer2 like techer; --依据已有表结构创建表
export table techer to '/export/techer';
import table techer2 from '/export/techer';

导出数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 将查询的结果导出到本地
insert overwrite local directory '/export/servers/exporthive' select * from score;

-- 将查询的结果格式化导出到本地
insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;

-- 将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;

-- Hadoop命令导出到本地
dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;

-- hive shell命令导出, 基本语法: hive -f/-e 执行语句或者脚本 > file
hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt
hive -f export.sh > /export/servers/exporthive/score.txt

-- export导出到HDFS上
export table score to '/export/exporthive/score';

Hive常用DQL语句

单表查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- 查询表中全部数据
SELECT * FROM emp;

-- 查询20号部门中员工编号大于7566的员工信息
SELECT * FROM emp WHERE empno > 7566 AND deptno = 20;

-- 查询所有工作类型, Hive支持使用DISTINCT关键字去重
SELECT DISTINCT job FROM emp;

-- 查询分区表中部门编号在[20,40]之间的员工
SELECT emp_ptn.* FROM emp_ptn
WHERE emp_ptn.deptno >= 20 AND emp_ptn.deptno <= 40;

-- 查询薪资最高的3名员工
SELECT * FROM emp ORDER BY sal DESC LIMIT 3;

-- 分组聚合查询, 查询各个部门薪酬综合
-- hive.map.aggr控制程序如何进行聚合, 默认值为false, 如果设置为true, Hive会在map阶段就执行一次聚合, 可以提高聚合效率但需要消耗更多内存
set hive.map.aggr=true;
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;

-- 使用ORDER BY时会有一个Reducer对全部查询结果进行排序, 可以保证数据的全局有序性
-- 使用SORT BY时只会在每个Reducer中进行排序, 这可以保证每个Reducer的输出数据是有序的, 但不能保证全局有序
-- 由于ORDER BY的时间可能很长, 如果设置了严格模式(hive.mapred.mode = strict), 则其后面必须再跟一个limit子句
-- 查询员工工资, 结果按照部门升序, 按照工资降序排列
SELECT empno, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;

-- 使用HAVING对分组数据进行过滤, 查询工资总和大于9000的所有部门
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000;

-- 默认情况下, MapReduce程序会对Map输出结果的Key值进行散列并均匀分发到所有Reducer上
-- 如果想要把具有相同Key值的数据分发到同一个Reducer进行处理, 这就需要使用DISTRIBUTE BY
-- 需要注意的是, DISTRIBUTE BY虽然能保证具有相同Key值的数据分发到同一个Reducer, 但是不能保证数据在Reducer上是有序的, 可以结合SORT BY使用来让Reducer上的数据有序
-- 将数据按照部门分发到对应的Reducer上处理
SELECT empno, deptno, sal FROM emp DISTRIBUTE BY deptno SORT BY deptno ASC;

-- 如果SORT BY和DISTRIBUTE BY指定的是相同字段, 且SORT BY排序规则是ASC, 此时可以使用CLUSTER BY进行替换, 同时CLUSTER BY可以保证数据在全局是有序的
SELECT empno, deptno, sal FROM emp CLUSTER BY deptno;

多表查询

与传统数据库中的概念一致，Hive也支持内连接，外连接，左外连接，右外连接，笛卡尔连接。

需要特别强调，JOIN语句的关联条件必须用ON指定，不能用WHERE指定，否则就会先做笛卡尔积再过滤，会导致得不到预期的结果。

INNER JOIN

1
2
3
4
5
6
7
8
-- 查询员工编号为7934的员工的详细信息
SELECT e.*,d.* 
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE empno=7934;

-- 如果是三表或者更多表连接, 语法如下
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

LEFT OUTER JOIN

1
2
3
4
-- 左连接
SELECT e.*,d.* 
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;

RIGHT OUTER JOIN

1
2
3
4
-- 右连接
SELECT e.*,d.* 
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

FULL OUTER JOIN

1
2
3
SELECT e.*,d.* 
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;

LEFT SEMI JOIN

1
2
3
4
5
6
7
8
9
10
11
12
13
-- LEFT SEMI JOIN（左半连接）是IN/EXISTS子查询的一种更高效的实现
-- JOIN子句中右边的表只能在ON子句中设置过滤条件
-- 查询结果只包含左边表的数据, 所以只能SELECT左表中的列

-- 查询在纽约办公的所有员工信息
SELECT emp.*
FROM emp LEFT SEMI JOIN dept
ON emp.deptno = dept.deptno AND dept.loc="NEW YORK";

-- 上面的语句就等价于
SELECT emp.* 
FROM emp
WHERE emp.deptno IN (SELECT deptno FROM dept WHERE loc="NEW YORK");

JOIN

1
2
-- 笛卡尔积连接, 性能消耗比较大, 如果在严格模式下(hive.mapred.mode = strict), Hive会阻止用户执行此操作
SELECT * FROM emp JOIN dept;

本地模式

在上文Demo语句中，大多数操作都会触发MapReduce，仅少部分不会触发，例如select * from emp limit 5就不会触发MapReduce，此时Hive只是简单的读取数据文件中的内容，然后格式化后进行输出。

在需要执行MapReduce的查询中，执行时间可能会很长，这时候可以选择开启本地模式。启用后，Hive将分析查询中每个map-reduce作业的大小，如果满足以下条件，则可以在本地运行它：

  1. 作业的总输入大小低于hive.exec.mode.local.auto.inputbytes.max（默认为128MB）
  2. map-tasks的总数小于hive.exec.mode.local.auto.tasks.max（默认为4）
  3. 所需的reduce任务总数为1或0
1
2
-- 本地模式默认关闭, 需要手动开启此功能
SET hive.exec.mode.local.auto=true;

在下图中可以看到，当满足本地查询条件后，由于Demo数据集很小，再次执行涉及MapReduce操作的查询时（以左半连接查询在纽约办公的所有员工信息为例），可以看到速度有显著的提升。

参考

LanguageManual DDL

LanguageManual Select

LanguageManual Joins

LanguageManual GroupBy

LanguageManual SortBy

五万字 | Hive知识体系保姆级教程