ODPS日常工具箱

创建ODPS分区表的SQL语法如下：

1. 基本语法：

CREATE TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)
[LIFECYCLE days];

2. 示例：创建一个按日期和地区分区的销售表

CREATE TABLE sales (
    id STRING COMMENT '订单ID',
    amount DOUBLE COMMENT '订单金额',
    product STRING COMMENT '产品名称'
)
COMMENT '销售记录表'
PARTITIONED BY (dt STRING COMMENT '日期分区', region STRING COMMENT '地区分区')
LIFECYCLE 365;

3. 关键说明：

• PARTITIONED BY 指定分区列，这些列不会出现在表数据中
• 分区列需要单独指定数据类型
• 分区表查询时通常需要指定分区条件以提高效率
• LIFECYCLE 设置表的生命周期(天数)，过期自动删除

4. 添加分区语法：

ALTER TABLE sales ADD PARTITION (dt='20230501', region='hangzhou');

5. 查询分区表时建议指定分区：

SELECT * FROM sales WHERE dt='20230501' AND region='hangzhou';

ODPS分区表sales的完整CRUD操作示例：

1. 插入数据到特定分区：

-- 插入单条数据到hangzhou地区20230501分区
INSERT INTO sales PARTITION (dt='20230501', region='hangzhou') 
VALUES ('order001', 199.9, '手机');

-- 从其他表导入数据到特定分区
INSERT INTO sales PARTITION (dt='20230501', region='shanghai')
SELECT order_id, amount, product FROM temp_orders 
WHERE city='shanghai' AND order_date='2023-05-01';

2. 查询分区数据：

-- 查询特定分区数据
SELECT * FROM sales 
WHERE dt='20230501' AND region='hangzhou';

-- 查询多分区数据(使用IN)
SELECT * FROM sales 
WHERE dt IN ('20230501', '20230502') AND region='hangzhou';

-- 查询分区元数据
SHOW PARTITIONS sales;

3. 更新分区数据：

-- 更新特定分区数据(实际是重写分区)
INSERT OVERWRITE TABLE sales PARTITION (dt='20230501', region='hangzhou')
SELECT id, amount, '新商品名称' 
FROM sales 
WHERE dt='20230501' AND region='hangzhou' AND id='order001';

4. 删除分区数据：

-- 清空特定分区数据
ALTER TABLE sales DROP PARTITION (dt='20230501', region='hangzhou');

-- 删除后需要重新添加分区才能使用
ALTER TABLE sales ADD PARTITION (dt='20230501', region='hangzhou');

5. 其他常用操作：

-- 查看表结构
DESC sales;

-- 查看分区结构
DESC sales PARTITION (dt='20230501', region='hangzhou');

-- 重命名表
ALTER TABLE sales RENAME TO sales_new;

-- 删除表
DROP TABLE sales;

注意事项：

1. 分区表操作必须指定分区条件，否则会扫描全表
2. 更新操作实际上是重写整个分区
3. 删除分区会同时删除该分区所有数据
4. 添加分区后才能向该分区插入数据

INSERT OVERWRITE操作在ODPS分区表中的详细解释：

1. 基本语法：

INSERT OVERWRITE TABLE 表名 PARTITION (分区列=值,...)
SELECT 查询语句;

2. 工作原理：

• 该操作会先完全清空指定分区的所有数据
• 然后将SELECT查询结果写入该分区
• 不是真正的"更新"操作，而是分区数据替换
• 原子性操作：要么全部成功，要么全部失败

3. 用户示例解析：

INSERT OVERWRITE TABLE sales PARTITION (dt='20230501', region='hangzhou')
SELECT id, amount, '新商品名称' 
FROM sales 
WHERE dt='20230501' AND region='hangzhou' AND id='order001';

解析：

• 操作目标：sales表的(dt='20230501', region='hangzhou')分区
• 操作步骤： a. 先删除该分区所有现有数据 b. 从原分区查询出id='order001'的记录 c. 修改product字段值为'新商品名称' d. 将修改后的数据写回分区
• 最终效果：只有order001记录被保留，其他原分区数据被删除

4. 完整更新示例：

-- 1. 先备份需要保留的数据
CREATE TABLE sales_tmp AS
SELECT * FROM sales 
WHERE dt='20230501' AND region='hangzhou';

-- 2. 修改需要更新的数据
INSERT OVERWRITE TABLE sales PARTITION (dt='20230501', region='hangzhou')
SELECT 
    id,
    CASE WHEN id='order001' THEN 250.0 ELSE amount END,
    CASE WHEN id='order001' THEN '新商品名称' ELSE product END
FROM sales_tmp;

5. 注意事项：

• 会删除分区内所有现有数据，只保留SELECT结果
• 必须确保SELECT查询返回完整数据
• 建议先备份数据再进行OVERWRITE操作
• 不适合频繁小数据量更新，更适合批量重写