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

┌─────────────────────────────────────────────────────────┐
│                      Client                              │
│              (CLI / JDBC / Web UI)                       │
└─────────────────────────┬───────────────────────────────┘
                          ↓
┌─────────────────────────────────────────────────────────┐
│                      Driver                              │
│    (SQL Parser → Compiler → Optimizer → Executor)        │
└─────────────────────────┬───────────────────────────────┘
                          ↓
┌─────────────┬───────────────────────┬───────────────────┐
│  Metastore  │   Execution Engine    │      HDFS         │
│  (MySQL等)  │  (MR/Tez/Spark)       │   (数据存储)       │
└─────────────┴───────────────────────┴───────────────────┘

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

-- Array
ARRAY<data_type>
-- 示例：ARRAY<STRING>

-- Map
MAP<primitive_type, data_type>
-- 示例：MAP<STRING, INT>

-- 结构体
STRUCT<col_name:data_type [COMMENT col_comment], ...>
-- 示例：STRUCT<name:STRING, age:INT>

-- 联合体
UNIONTYPE<data_type, data_type, ...>

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

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mydb
COMMENT 'My database'
LOCATION '/user/hive/warehouse/mydb.db';

-- 使用数据库
USE mydb;

-- 显示数据库
SHOW DATABASES;

-- 删除数据库
DROP DATABASE IF EXISTS mydb CASCADE;

 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

-- 创建内部表
CREATE TABLE employee (
    id INT,
    name STRING,
    salary DOUBLE,
    department STRING
)
STORED AS ORC
TBLPROPERTIES ('orc.compress'='SNAPPY');

-- 创建外部表
CREATE EXTERNAL TABLE external_employee (
    name STRING,
    salary DOUBLE,
    department STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/data/employee/';

-- 创建分区表
CREATE TABLE partitioned_employee (
    name STRING,
    salary DOUBLE
)
PARTITIONED BY (department STRING, year INT)
STORED AS ORC;

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

-- 修改列
ALTER TABLE employee CHANGE salary salary DECIMAL(10,2);

-- 添加列
ALTER TABLE employee ADD COLUMNS (bonus DOUBLE);

-- 添加分区
ALTER TABLE partitioned_employee ADD PARTITION (department='IT', year=2023);

-- 删除分区
ALTER TABLE partitioned_employee DROP PARTITION (department='IT', year=2023);

-- 重命名表
ALTER TABLE employee RENAME TO emp;

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

-- 插入数据
INSERT INTO employee VALUES (1, 'John', 5000.0, 'IT');

-- 从查询插入
INSERT INTO employee
SELECT id, name, salary, department
FROM temp_employee;

-- 覆盖插入
INSERT OVERWRITE TABLE employee
SELECT * FROM temp_employee;

-- 分区插入
INSERT INTO partitioned_employee PARTITION(department='IT', year=2023)
SELECT id, name, salary FROM temp_employee;

-- 动态分区插入
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO partitioned_employee PARTITION(department, year)
SELECT id, name, salary, department, year FROM temp_employee;

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

-- 聚合查询
SELECT department, AVG(salary) as avg_salary
FROM employee
GROUP BY department
HAVING AVG(salary) > 6000;

-- 连接查询
SELECT e.name, d.dept_name
FROM employee e
JOIN department d ON e.department = d.dept_id;

-- 窗口函数
SELECT name, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employee;

-- 子查询
SELECT * FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

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

-- 静态分区
INSERT INTO partitioned_employee PARTITION(department='IT', year=2023)
SELECT id, name, salary FROM source_table;

-- 动态分区
INSERT INTO partitioned_employee PARTITION(department, year)
SELECT id, name, salary, department, year FROM source_table;

-- 查询特定分区
SELECT * FROM partitioned_employee
WHERE department='IT' AND year=2023;

-- 显示分区
SHOW PARTITIONS partitioned_employee;

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

-- 创建分桶表
CREATE TABLE bucketed_employee (
    id INT,
    name STRING,
    salary DOUBLE,
    department STRING
)
CLUSTERED BY (id) INTO 4 BUCKETS
STORED AS ORC;

-- 启用分桶
SET hive.enforce.bucketing=true;

-- 插入数据（自动分桶）
INSERT INTO bucketed_employee
SELECT * FROM employee;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 字符串操作
SELECT
    CONCAT(first_name, ' ', last_name) as full_name,
    UPPER(name) as upper_name,
    LOWER(name) as lower_name,
    LENGTH(name) as name_length,
    SUBSTR(name, 1, 3) as name_prefix,
    TRIM(name) as trimmed_name,
    REGEXP_REPLACE(name, '[0-9]', '') as no_digits
FROM employee;

1
2
3
4
5
6
7
8

-- 日期操作
SELECT
    FROM_UNIXTIME(UNIX_TIMESTAMP()) as current_time,
    YEAR(hire_date) as hire_year,
    MONTH(hire_date) as hire_month,
    DATEDIFF(CURRENT_DATE, hire_date) as days_since_hire,
    DATE_ADD(hire_date, 30) as after_30_days
FROM employee;

1
2
3
4
5
6
7
8
9

-- 聚合统计
SELECT
    COUNT(*) as total_count,
    SUM(salary) as total_salary,
    AVG(salary) as avg_salary,
    MAX(salary) as max_salary,
    MIN(salary) as min_salary,
    STDDEV(salary) as salary_stddev
FROM employee;

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

// MyUpperUDF.java
package com.example.udf;

import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;

public class MyUpperUDF extends UDF {
    public Text evaluate(Text input) {
        if (input == null) return null;
        return new Text(input.toString().toUpperCase());
    }
}

1
2
3
4
5
6

-- 注册 UDF
ADD JAR /path/to/my-udf.jar;
CREATE TEMPORARY FUNCTION my_upper AS 'com.example.udf.MyUpperUDF';

-- 使用 UDF
SELECT my_upper(name) FROM employee;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 选择合适的存储格式
CREATE TABLE optimized_table (
    col1 STRING,
    col2 INT
)
STORED AS ORC
TBLPROPERTIES (
    'orc.compress'='SNAPPY',
    'orc.create.index'='true'
);

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

-- 推荐：使用分区裁剪
SELECT * FROM partitioned_table
WHERE partition_col = 'value';

-- 避免：全表扫描
SELECT * FROM partitioned_table
WHERE non_partition_col = 'value';

-- 推荐：只查询需要的列
SELECT name, salary FROM employee;

-- 避免：SELECT *
SELECT * FROM employee;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- MapJoin（小表广播）
SELECT /*+ MAPJOIN(small_table) */ *
FROM large_table a
JOIN small_table b ON a.key = b.key;

-- 优化连接顺序（大表在前）
SELECT *
FROM large_table a
JOIN medium_table b ON a.key = b.key
JOIN small_table c ON b.key = c.key;

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

-- 启用代价优化器
SET hive.cbo.enable=true;

-- 设置合理的 MapReduce 参数
SET mapreduce.job.reduces=10;
SET hive.exec.reducers.bytes.per.reducer=1000000000;

-- 启用并行执行
SET hive.exec.parallel=true;
SET hive.exec.parallel.thread.number=8;

-- 启用向量化执行
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;

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

-- 1. 增加 Reduce 任务数
SET mapreduce.job.reduces=100;

-- 2. 启用负载均衡
SET hive.groupby.skewindata=true;

-- 3. 使用随机前缀打散热点 Key
SELECT *
FROM (
    SELECT CONCAT(CAST(RAND() * 100 AS INT), '_', key) as new_key, value
    FROM skewed_table
) a
JOIN small_table b ON SPLIT(a.new_key, '_')[1] = b.key;

-- 4. 使用 MapJoin 避免 Shuffle
SELECT /*+ MAPJOIN(b) */ *
FROM large_table a
JOIN small_table b ON a.key = b.key;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 1. 合并小文件
SET hive.merge.mapfiles=true;
SET hive.merge.mapredfiles=true;
SET hive.merge.size.per.task=256000000;

-- 2. 使用 Concatenate 合并 ORC 文件
ALTER TABLE table_name CONCATENATE;

-- 3. 重新组织数据
INSERT OVERWRITE TABLE new_table
SELECT * FROM old_table;