Hive数仓操作（十四）

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