1
2
3
4

CREATE DATABASE [IF NOT EXISTS] db_name
[COMMENT '数据库描述']
[LOCATION 'hdfs_path'] -- 自定义 HDFS 存储路径
[WITH DBPROPERTIES ('key1'='value1', ...)]; -- 附加元数据

1
2
3
4

CREATE DATABASE IF NOT EXISTS tcec_dw
COMMENT 'TCEC 数据仓库'
LOCATION '/warehouse/tcec_dw.db'
WITH DBPROPERTIES ('owner'='liuyuhang', 'created'='2026-05-17');

1

USE db_name;

1
2
3
4
5

ALTER DATABASE db_name SET DBPROPERTIES ('edited-by'='liu', 'createtime'='20260515');

ALTER DATABASE db_name SET OWNER USER liuyuhang;

ALTER DATABASE db_name SET LOCATION 'hdfs://new_path'; -- Hive 2.2.1+ 支持

1

DROP DATABASE [IF EXISTS] db_name [RESTRICT | CASCADE];

1

DROP DATABASE IF EXISTS tcec_dw CASCADE;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
( col_name data_type [COMMENT '列注释'], ... )
[COMMENT '表注释']

[PARTITIONED BY (col_name data_type, ...)] -- 分区

[CLUSTERED BY (col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)]

INTO num_buckets BUCKETS] -- 分桶

[ROW FORMAT DELIMITED -- 指定分隔符
FIELDS TERMINATED BY ',' 

COLLECTION ITEMS TERMINATED BY '|'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'] -- 行格式

[STORED AS TEXTFILE | ORC | PARQUET | SEQUENCEFILE | AVRO] -- 存储格式

[LOCATION 'hdfs_path'] -- 数据位置

[TBLPROPERTIES ('key'='value', ...)]; -- 表属性

1
2
3
4
5
6
7

CREATE TABLE dwd_order (
order_id BIGINT,
user_id BIGINT,
amount DECIMAL(18,2)
)
PARTITIONED BY (dt STRING, region STRING) -- 二级分区
STORED AS PARQUET;

1
2
3
4
5
6

/user/hive/warehouse/dwd_order/
├── dt=2024-05-17/
│   ├── region=Chengdu/
│   │   └── 000000_0  (Parquet文件)
│   ├── region=Beijing/
│   └── region=Shanghai/

1
2
3
4
5
6

-- 错误示例：CTAS缺少分区字段
CREATE TABLE employee_partitioned
PARTITIONED BY (dept STRING, year INT)
AS
SELECT id, name, salary  -- 只有3个字段，但表需要5个字段（3个普通+2个分区）
FROM source_table;

1
2
3

LOAD DATA LOCAL INPATH '/path/to/data_china_0517.csv'
INTO TABLE dwd_order s
PARTITION (dt='2024-05-17', region='2024-05-17China');

1
2
3

-- 高效查询：只扫描‘China’分区下的‘2024-05-17’子目录
SELECT * FROM dwd_order 
WHERE region='China' AND dt='2024-05-17';

1

show partitions dwd_order;

1
2
3
4

ALTER TABLE dwd_order ADD
PARTITION(dt='2024-05-17', region='Chengdu')
PARTITION(dt='2024-05-17', region='Beijing')
PARTITION(dt='2024-05-17', region='Shanghai');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 这将删除 dt='2024-05-17' 下的所有region分区
ALTER TABLE dwd_order DROP PARTITION(dt='2024-05-17');

-- 或者删除多个指定分区
ALTER TABLE dwd_order DROP PARTITION(dt='2024-05-17', region='Chengdu'), PARTITION(dt='2024-05-17', region='Beijing');


-- 删除所有以特定模式匹配的分区
ALTER TABLE dwd_order DROP PARTITION(dt='2024-05-*');
ALTER TABLE dwd_order DROP PARTITION(region LIKE 'Cheng%');

1
2
3

-- TRUNCATE PARTITION: 只删除数据，保留分区结构
TRUNCATE TABLE dwd_order PARTITION(dt='2024-05-17');
-- 执行后，分区目录还存在，但里面的数据文件被清空

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 静态分区：需要明确指定分区值
INSERT INTO sales PARTITION (dt='2024-05-17', region='Chengdu')
SELECT order_id, amount FROM source_table WHERE dt='2024-05-17' AND region='Chengdu';

-- 动态分区：分区值从数据中自动提取
INSERT INTO sales PARTITION (dt, region)
SELECT order_id, amount, dt, region FROM source_table;

-- 常用模式：一级静态分区 + 二级动态分区
INSERT OVERWRITE TABLE user_behavior PARTITION (dt='2024-05-17', province)
SELECT 
    user_id,
    action,
    province  -- 动态分区字段
FROM source_table
WHERE dt = '2024-05-17';
-- 结果：在 dt=2024-05-17 下，按province自动创建子分区

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 1. 启用动态分区（必须）
SET hive.exec.dynamic.partition=true;

-- 2. 设置分区模式（重要！）
SET hive.exec.dynamic.partition.mode=nonstrict;
-- strict: 必须至少有一个静态分区（安全但繁琐）
-- nonstrict: 允许所有分区都是动态的（生产常用）

-- 3. 控制最大分区数（防止创建过多分区）
SET hive.exec.max.dynamic.partitions=10000;  -- 总共允许的最大动态分区数
SET hive.exec.max.dynamic.partitions.pernode=1000;  -- 每个Mapper/Reducer允许的最大分区数

-- 4. 控制文件数量
SET hive.exec.max.created.files=100000;  -- 整个作业允许创建的最大文件数

-- 5. 错误处理
SET hive.error.on.empty.partition=false;  -- 是否在动态分区插入产生空分区时报错

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 1. 开启分桶约束（老版本需要，新版本Hive默认开启）
SET hive.enforce.bucketing = true;
-- 2. 设置Reduce任务数等于桶数，确保能生成对应数量的文件
SET mapreduce.job.reduces = 32; -- 注意：这里应该设置为32，与INTO 32 BUCKETS对应

CREATE TABLE dwd_user (
    user_id BIGINT,
    name STRING
)
-- 3. 核心定义：此表根据user_id的哈希值，将数据分散到32个桶文件中
CLUSTERED BY (user_id)
-- 4. 在每个桶文件内部，数据按照user_id升序排列
SORTED BY (user_id)
-- 5. 总共创建32个桶
INTO 32 BUCKETS
-- 6. 数据以ORC列式存储格式保存，以获得最佳的压缩和查询性能
STORED AS ORC;

1
2

# 这只是简单的文件复制，Hive完全无法控制
hdfs dfs -put /local/user.txt /user/hive/warehouse/dwd_user/

1
2

-- 这只会移动和转换数据格式，但不会计算分桶
LOAD DATA INPATH '/input/user.txt' INTO TABLE dwd_user;

1
2
3

-- 这才是正确的分桶表数据加载方式
INSERT OVERWRITE TABLE dwd_user
SELECT user_id, name FROM source_table;

1
2
3
4
5
6
7
8

-- 1. 启动MapReduce/Spark作业
-- 2. Map阶段：读取源数据
-- 3. 为每行计算哈希：hash(user_id) % 32 → 得到桶编号(0-31)
-- 4. Reduce阶段：32个Reducer各自处理分配到的数据
--    - 每个Reducer处理一个桶的数据
--    - 在Reducer内部对user_id排序
--    - 以ORC格式写入到对应桶文件
-- 5. 结果：生成32个有序的ORC文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 重命名
ALTER TABLE old_name RENAME TO new_name;

-- 修改表属性
ALTER TABLE t SET TBLPROPERTIES ('comment'='新描述');

-- 修改 SerDe
ALTER TABLE t SET SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde';

-- 内外部表互转
ALTER TABLE t SET TBLPROPERTIES ('EXTERNAL'='TRUE'); -- 改外部表
ALTER TABLE t SET TBLPROPERTIES ('EXTERNAL'='FALSE'); -- 改内部表

1
2
3
4
5
6
7
8

-- 增加列
ALTER TABLE t ADD COLUMNS (email STRING COMMENT '邮箱');

-- 修改列（改名、改类型、调整位置）
ALTER TABLE t CHANGE COLUMN old_col new_col STRING [AFTER other_col | FIRST];

-- 替换全部列（保留表，覆盖列定义，⚠️慎用）
ALTER TABLE t REPLACE COLUMNS (id BIGINT, name STRING);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 添加分区（手动）
ALTER TABLE t ADD PARTITION (dt='2026-05-17', region='cn')
LOCATION '/data/t/dt=2026-05-17/region=cn';

-- 删除分区
ALTER TABLE t DROP IF EXISTS PARTITION (dt='2026-05-17');
-- 修改分区路径
ALTER TABLE t PARTITION (dt='2026-05-17') SET LOCATION 'hdfs://new_path';

-- 修复分区（HDFS 上有目录但元数据没注册时常用）
MSCK REPAIR TABLE t;

1
2
3
4
5

-- 删除表（内部表会删 HDFS 数据，外部表只删元数据）
DROP TABLE [IF EXISTS] table_name;

-- 清空表（保留结构，删数据；仅对内部表生效）
TRUNCATE TABLE table_name [PARTITION (dt='2026-05-17')];

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-- 从本地文件加载（复制）
LOAD DATA LOCAL INPATH '/home/liu/data.csv' INTO TABLE t;

-- 从 HDFS 加载（移动，原路径文件消失）
LOAD DATA INPATH '/tmp/data.csv' [OVERWRITE] INTO TABLE t PARTITION (dt='2026-05-17');

-- INSERT 查询写入
INSERT INTO TABLE t PARTITION (dt='2026-05-17') SELECT ... FROM src;
INSERT OVERWRITE TABLE t PARTITION (dt='2026-05-17') SELECT ... FROM src;

-- 创建表时加载查询结果数据
CREATE TABLE t AS SELECT ... FROM src;

-- 创建表时加载本地文件数据
CREATE EXTERNAL TABLE t (t_id, t_name)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION './teacher';

-- 根据已有表结构建表,并导入表数据
CREATE TABLE teacher2 like teacher;
EXPORT TABLE teacher TO '/export/teacher';
IMPORT TABLE teacher2 FROM '/export/teacher';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

-- 基本语法：导出整个表或查询结果（格式化导出）
INSERT OVERWRITE LOCAL DIRECTORY '/export/teacher'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','  -- 指定字段分隔符，例如CSV格式
STORED AS TEXTFILE
SELECT * FROM teacher;

-- 导出指定查询结果到本地
INSERT OVERWRITE LOCAL DIRECTORY '/export/dept_sales'
SELECT dept_id, sum(salary) as total_salary
FROM employee
GROUP BY dept_id;

-- 导出指定查询结果到HDFS（没有LOCAL关键字）
INSERT OVERWRITE DIRECTORY '/export/dept_sales'
SELECT dept_id, sum(salary) as total_salary
FROM employee
GROUP BY dept_id;

1
2
3
4

-- export.hql
-- 可以包含多条SQL语句
USE myhive;
SELECT * FROM score;

1

hive -f export_data.hql > /export/servers/exporthive/score.txt;

1
2
3
4

#!/bin/bash
# export.sh
# 这是Shell脚本，内部调用Hive
hive -e "SELECT * FROM myhive.score;"

1

hive -f export_data.sh > /export/servers/exporthive/score.txt;

1

EXPORT TABLE score TO '/export/exporthive/score';

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
| [ORDER BY col_list]
]
[LIMIT [offset,] rows]

1

SELECT * FROM employees WHERE deptno < 20;

1
2
3
4
5
6

SELECT 
    province,
    city,
    COUNT(DISTINCT user_id) AS 活跃用户数
FROM user_behavior
GROUP BY province, city;

1

reducer_id = hash(分发字段) % reducer数量

1
2
3

Map1 ┐          ┌──→ Reducer1: 部门10的所有员工
Map2 ┼──hash──→ ├──→ Reducer2: 部门20的所有员工
Map3 ┘          └──→ Reducer3: 部门30的所有员工

1
2
3

Reducer1: 部门10的所有员工，按 sal 降序
Reducer2: 部门20的所有员工，按 sal 降序
Reducer3: 部门30的所有员工，按 sal 降序

1
2
3

SELECT * FROM employees
DISTRIBUTE BY deptno
SORT BY sal DESC;

1
2
3

-- 以下两句等价
SELECT * FROM emp CLUSTER BY deptno;
SELECT * FROM emp DISTRIBUTE BY deptno SORT BY deptno;

1
2
3

Map1 ┐
Map2 ┼──→  [单个 Reducer，所有数据汇总]  →  输出 1 个全局有序文件
Map3 ┘

1

ON e.dept_id = d.dept_id

1
2
3
4
5
6
7

+-----+-------+---------+
| id  | name  | dept_id |
+-----+-------+---------+
| 1   | Alice | 101     |
| 2   | Bob   | 102     |
| 3   | Carol | NULL    |
+-----+-------+---------+

1
2
3
4
5
6

+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 101     | Sales     |
| 103     | IT        |
+---------+-----------+

1
2
3

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

1
2
3
4
5

+-------+-----------+
| name  | dept_name |
+-------+-----------+
| Alice | Sales     |
+-------+-----------+

1
2
3

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

1
2
3
4
5
6
7

+-------+-----------+
| name  | dept_name |
+-------+-----------+
| Alice | Sales     |
| Bob   | NULL      |
| Carol | NULL      |
+-------+-----------+

1
2
3

SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

1
2
3
4
5
6

+-------+-----------+
| name  | dept_name |
+-------+-----------+
| Alice | Sales     |
| NULL  | IT        |
+-------+-----------+

1
2
3

SELECT e.name, d.dept_name
FROM employees e
FULL JOIN departments d ON e.dept_id = d.dept_id;

1
2
3
4
5
6
7
8

+-------+-----------+
| name  | dept_name |
+-------+-----------+
| Alice | Sales     |
| Bob   | NULL      |
| Carol | NULL      |
| NULL  | IT        |
+-------+-----------+

1
2
3

SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+-------+-----------+
| name  | dept_name |
+-------+-----------+
| Alice | Sales     |
| Alice | IT        |
| Bob   | Sales     |
| Bob   | IT        |
| Carol | Sales     |
| Carol | IT        |
+-------+-----------+

1
2
3

SELECT e.name
FROM employees e
LEFT SEMI JOIN departments d ON e.dept_id = d.dept_id;

1
2
3
4
5

+-------+
| name  |
+-------+
| Alice |
+-------+

1
2
3
4

-- 查询有合法部门的员工
SELECT e.*
FROM employees e
LEFT SEMI JOIN departments d ON e.dept_id = d.dept_id;

1
2
3

SELECT * FROM emp LIMIT 5; -- 前 5 行

SELECT * FROM emp LIMIT 10, 5; -- 跳过 10 行，取 5 行（部分版本支持）

1
2
3
4
5
6

-- UNION ALL：不去重，效率高
SELECT * FROM emp WHERE deptno = 10
UNION ALL
SELECT * FROM emp WHERE deptno = 20;

-- UNION：去重，会触发额外 MR