一、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,以排除自身。