一、Hive的DDL语句
在 Hive 中，DDL（数据定义语言）语句用于数据库和表的创建、修改、删除等操作。以下是一些重要的 DDL 语句：
1. 创建数据库和表
-
创建数据库
CREATE DATABASE IF NOT EXISTS database_name; -
创建表
CREATE TABLE table_name (column1_name data_type,column2_name data_type,... ) COMMENT 'Table comment' STORED AS file_format;
2. 修改表
-
重命名表
ALTER TABLE old_table_name RENAME TO new_table_name; -
添加列
ALTER TABLE table_name ADD COLUMNS (new_column_name1 data_type , new_column_name2 data_type); -
重命名列
ALTER TABLE table_name CHANGE old_column_name new_column_name data_type;CHANGE不能进行从字符串到数字类型或从大数字类型到小数字类型的转换
-
删除列:
-
使用
REPLACE COLUMNS可以“删除”某些列，其实本质是通过重新定义表的列结构来实现。 -
实际上，删除的列的数据不会立即物理删除，而是从表结构中移除。
-
在
REPLACE COLUMNS中，需要列出所有想要保留的列及其数据类型。 -
未列出的列将被“删除”。
-
限制：只能从末尾开始删除列。如果从中间或开头删除，可能会导致表结构的错误，且无法恢复这些列的数据。
-
示例
假设有一个表 shop_fq，包含以下列：
CREATE TABLE shop_fq (id INT,name STRING,price DECIMAL(10, 2),category STRING
);
如果需要删除 price 和 category 列，只保留 id 和 name，可以这样操作：
ALTER TABLE shop_fq REPLACE COLUMNS (id INT,name STRING
);
注意
- 数据的物理存在: 虽然列被“删除”，但数据仍然存在于文件中，只是 Hive 不再管理这些数据。
3. 删除数据库和表
-
删除数据库
DROP DATABASE IF EXISTS database_name CASCADE; -
删除表
DROP TABLE IF EXISTS table_name;
4. 分区和分桶
-
创建分区表
CREATE TABLE table_name (column1_name data_type,column2_name data_type ) PARTITIONED BY (partition_column_name data_type) STORED AS file_format; -
创建分桶表
CREATE TABLE table_name (column1_name data_type,column2_name data_type ) CLUSTERED BY (bucket_column_name) INTO num_buckets BUCKETS STORED AS file_format;
5. 修改分区
-
添加分区
ALTER TABLE table_name ADD PARTITION (partition_column='value'); -
删除分区
ALTER TABLE table_name DROP PARTITION (partition_column='value');
二、Hive的子查询
查询出和10号部门的工作岗位相同的其他部门的员工信息
1. IN 子查询（由于HIVE莫名的不可抗力会经常报错，建议用后两种方法）
SELECT *
FROM emp
WHERE job IN (SELECT job FROM emp WHERE deptno=10)
AND deptno != 10;
- 从表
emp中选择所有员工。 - 条件是员工的
job在子查询返回的职位列表中。 - 子查询从
deptno为 10 的部门中选择job。 - 还要求
deptno不是 10，以排除自身。
2. LEFT SEMI JOIN
SELECT *
FROM (SELECT * FROM emp WHERE deptno != 10) a
LEFT SEMI JOIN (SELECT job FROM emp WHERE deptno=10) b
ON a.job = b.job;
- 从
emp表中选择deptno不等于 10 的员工。 - 使用
LEFT SEMI JOIN将这些员工和来自deptno为 10 的员工的职位匹配。 LEFT SEMI JOIN只返回在b中匹配的a的行，相当于使用IN的效果。
3. EXISTS
SELECT *
FROM emp e
WHERE EXISTS (SELECT 1 FROM emp b WHERE b.deptno=10 AND e.job = b.job)
AND e.deptno != 10;
- 从表
emp中选择所有员工。 - 使用
EXISTS子查询检查是否存在deptno为 10 且job相同的记录。 - 同时确保
deptno不等于 10，以排除自身。
