Hive【基础知识 05】常用DDL操作（数据库操作+创建表+修改表+清空删除表+其他命令）

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'
LOCATION '/tmp/hive/test'
WITH DBPROPERTIES ('create'='yuanzhengme');

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

-- 1.5 删除数据库
-- 默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用CASCADE 级联删除。
-- 语法：
DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [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 加载数据到表
-- 加载数据到 emp 表中
load data local inpath "/usr/file/emp.txt" into table emp;

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 上的数据；
-- 删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。

5.其他命令

-- 5.1 Describe
-- 查看数据库：
DESCRIBE|Desc DATABASE [EXTENDED] db_name;  --EXTENDED 是否显示额外属性
-- 查看表：
DESCRIBE|Desc [EXTENDED|FORMATTED] table_name --FORMATTED 以友好的展现方式查看表详情

-- 5.2 Show
-- 1. 查看数据库列表
-- 语法
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
-- 示例：
SHOW DATABASES like 'hive*';
-- LIKE子句允许使用正则表达式进行过滤，但是SHOW语句当中的LIKE子句只支持 *（通配符）和 |（条件或）两个符号。例如employees,emp * , emp * | * ees ，所有这些都将匹配名为 employees的数据库。

-- 2. 查看表的列表
-- 语法
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
-- 示例
SHOW TABLES IN default;

-- 3. 查看视图列表
SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];  --仅支持 Hive 2.2.0 +

-- 4. 查看表的分区列表
SHOW PARTITIONS table_name;

-- 5. 查看表/视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name);