Hive常用DDL操作

人工智能
2025-08-15 03:39:01

本专栏案例数据集链接： download.csdn.net/download/shangjg03/88478038 1. Database 1.1 查看数据库列表 show databases; 1.2 使用数据库 USE database_name; 1.3 新建数据库

语法：

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' WITH DBPROPERTIES ('create'='shangjack'); 1.4 查看数据库信息

语法：

DESC DATABASE [EXTENDED] db_name; --EXTENDED 表示是否显示额外属性

示例：

DESC DATABASE EXTENDED hive_test; 1.5 删除数据库

语法：

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 CASCADE 级联删除。

示例：

DROP DATABASE IF EXISTS hive_test CASCADE; 2.创建表 2.1 建表语法 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]; --从查询结果创建表 2.2 内部表 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"; 2.3 外部表 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';

使用 `desc format emp_external` 命令可以查看表的详细信息如下：

2.4 分区表 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'; 2.5 分桶表 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'; 2.6 倾斜表

通过指定一个或者多个列经常出现的值（严重偏斜），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'; 2.7 临时表

临时表仅对当前 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"; 2.8 CTAS创建表

支持从查询语句的结果创建表：

CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20'; 2.9 复制表结构

语法：

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 2.10 加载数据到表

加载数据到表中属于 DML 操作，这里为了方便大家测试，先简单介绍一下加载本地数据到表中：

-- 加载数据到 emp 表中 load data local inpath "/usr/file/emp.txt" into table emp;

其中 emp.txt 的内容如下

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800.00 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975.00 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850.00 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450.00 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 1500.00 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000.00 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100.00 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950.00 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000.00 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300.00 10

加载后可查询表中数据：

3.修改表 3.1 重命名表

语法：

ALTER TABLE table_name RENAME TO new_table_name;

示例：

ALTER TABLE emp_temp RENAME TO new_emp; --把 emp_temp 表重命名为 new_emp 3.2 修改列

语法：

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'; 3.3 新增列

示例：

ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address'); 4.清空表/删除表 4.1 清空表

语法：

-- 清空整个表或表指定分区中的数据 TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];

目前只有内部表才能执行 TRUNCATE 操作，外部表执行时会抛出异常 `Cannot truncate non-managed table XXXX`。

示例：

TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20); 4.2 删除表

语法：

DROP TABLE [IF EXISTS] table_name [PURGE];

内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；

外部表：只会删除表的元数据，不会删除 HDFS 上的数据；

删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。