Hive常用命令参考

一、数据库操作

1. 创建数据库

sql

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

示例：创建名为test_db的数据库

sql

CREATE DATABASE IF NOT EXISTS test_db 
COMMENT 'This is a test database'
LOCATION '/user/hive/databases/test_db'
WITH DBPROPERTIES ('creator'='admin', 'date'='2023-01-01');

2. 查看数据库

sql

SHOW DATABASES;
SHOW DATABASES LIKE 'hive_*'; -- 模糊查询

3. 切换数据库

sql

USE database_name;

4. 查看数据库详情

sql

DESCRIBE DATABASE [EXTENDED] database_name;

5. 删除数据库

sql

DROP DATABASE [IF EXISTS] database_name [CASCADE]; -- CASCADE表示级联删除表

二、表操作

1. 创建内部表

sql

CREATE TABLE [IF NOT EXISTS] table_name (
  column_name data_type [COMMENT column_comment],
  ...
)
[COMMENT table_comment]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
 LINES TERMINATED BY '\n']
[STORED AS TEXTFILE]
[LOCATION hdfs_path];

示例：创建员工表

sql

CREATE TABLE IF NOT EXISTS employees (
  id INT COMMENT 'Employee ID',
  name STRING COMMENT 'Employee Name',
  salary FLOAT COMMENT 'Employee Salary'
)
COMMENT 'Employee Information Table'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

2. 创建外部表

sql

CREATE EXTERNAL TABLE [IF NOT EXISTS] table_name (
  column_name data_type [COMMENT column_comment],
  ...
)
[COMMENT table_comment]
[ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
 LINES TERMINATED BY '\n']
[STORED AS TEXTFILE]
LOCATION hdfs_path;

3. 创建分区表

sql

CREATE TABLE [IF NOT EXISTS] table_name (
  column_name data_type [COMMENT column_comment],
  ...
)
PARTITIONED BY (partition_column data_type)
[ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
 LINES TERMINATED BY '\n'];

示例：按日期分区的日志表

sql

CREATE TABLE IF NOT EXISTS logs (
  id INT,
  content STRING
)
PARTITIONED BY (log_date STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

4. 查看表列表

sql

SHOW TABLES;
SHOW TABLES LIKE 'emp_*'; -- 模糊查询

5. 查看表结构

sql

DESCRIBE [EXTENDED] table_name; -- EXTENDED显示详细信息

6. 修改表名

sql

ALTER TABLE old_table_name RENAME TO new_table_name;

7. 删除表

sql

DROP TABLE [IF EXISTS] table_name; -- 内部表数据会被删除，外部表仅删除元数据

三、数据加载与导出

1. 从本地文件系统加载数据

sql

LOAD DATA LOCAL INPATH 'local_file_path' 
[OVERWRITE] INTO TABLE table_name 
[PARTITION (partition_column=value)];

示例：加载本地数据到员工表

sql

LOAD DATA LOCAL INPATH '/home/user/employees.csv' 
OVERWRITE INTO TABLE employees;

2. 从 HDFS 加载数据

sql

LOAD DATA INPATH 'hdfs_file_path' 
[OVERWRITE] INTO TABLE table_name 
[PARTITION (partition_column=value)];

3. 插入数据到表

sql

INSERT INTO TABLE table_name [PARTITION (partition_column=value)]
SELECT column1, column2, ... FROM source_table;

4. 导出数据到本地

sql

INSERT OVERWRITE LOCAL DIRECTORY 'local_output_path'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT column1, column2, ... FROM table_name;

四、查询操作

1. 基本查询

sql

SELECT column1, column2, ... 
FROM table_name 
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]]
[LIMIT number];

2. 关联查询

sql

SELECT a.column, b.column 
FROM table_a a
JOIN table_b b ON a.key = b.key; -- INNER JOIN
-- LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN

3. 子查询

sql

SELECT * FROM table_name 
WHERE column IN (SELECT column FROM another_table WHERE condition);

五、分区与分桶

1. 添加分区

sql

ALTER TABLE table_name ADD [IF NOT EXISTS]
PARTITION (partition_column=value) [LOCATION hdfs_path];

2. 删除分区

sql

ALTER TABLE table_name DROP [IF EXISTS] PARTITION (partition_column=value);

3. 创建分桶表

sql

CREATE TABLE [IF NOT EXISTS] table_name (
  column_name data_type,
  ...
)
CLUSTERED BY (column) INTO num_buckets BUCKETS;

六、常用函数

1. 字符串函数

sql

SELECT CONCAT('Hello', ' ', 'World'); -- 拼接字符串
SELECT SUBSTR('Hive', 1, 2); -- 截取子串，返回'HI'
SELECT LENGTH('Hive'); -- 返回长度4

2. 日期函数

sql

SELECT CURRENT_DATE(); -- 获取当前日期
SELECT DATE_FORMAT('2023-01-01', 'yyyy-MM-dd'); -- 格式化日期

3. 聚合函数

sql

SELECT COUNT(*), AVG(salary), MAX(salary) FROM employees;

七、其他常用命令

1. 查看 Hive 版本

sql

SELECT VERSION();

2. 执行 HQL 脚本

bash

hive -f /path/to/script.hql

3. 查看作业历史

sql

SHOW HISTORY;

4. 设置 Hive 参数

sql

SET hive.exec.dynamic.partition.mode=nonstrict; -- 动态分区模式