ODPS SQL <for 数据定义语言 DDL>
数据定义语言：（DDL）
建表语句：
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], ...)] //添加分区，目前分区标只能是string类型的； [LIFECYCLE days] //设置表的生命周期 [AS select_statement] //也可以用as方式建表，但是as和第一个的col方式是不能并存的
删表：
drop table [if exists] table_name;
修改表名：
alter table table_name rename to new_table_name;
分区操作：
添加分区：
alter table table_name add [if not exists] partition(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...);
删除分区：
alter table table_name drop [if exists] partition(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...);
修改属性：
添加列：
ALTER TABLE table_name ADD COLUMNS (col_name1 type1,col_name2 type2...);
修改列名：
ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;
修改表注释：
alter table table_name set comment 'tb1 comment'
修改列/分区注释：
ALTER TABLE table_name CHANGE COLUMN col_name COMMENT comment_string;
DDL数据定义语言案例演示：
创建/删除普通表：
odps@ sdrtest>create table t_people (id bigint , name string); //创建普通表 odps@ sdrtest>desc t_people; //查看普通表的表结构 +------------------------------------------------------------------------------------+ | Owner: ALIYUN$1399438812@qq.com | Project: sdrtest | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2019-04-13 09:02:41 | | LastDDLTime: 2019-04-13 09:02:41 | | LastModifiedTime: 2019-04-13 09:02:41 | +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | id | bigint | | | | name | string | | | +------------------------------------------------------------------------------------+ OK odps@ sdrtest>drop table t_people_p; //删除普通表
创建分区表：
odps@ sdrtest>create table t_people_p (id bigint,name string) partitioned by (gender string); //创建分区表 odps@ sdrtest>desc t_people_p; //查看表结构 +------------------------------------------------------------------------------------+ | Owner: ALIYUN$1399438812@qq.com | Project: sdrtest | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2019-04-13 09:08:53 | | LastDDLTime: 2019-04-13 09:08:53 | | LastModifiedTime: 2019-04-13 09:08:53 | +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | id | bigint | | | | name | string | | | +------------------------------------------------------------------------------------+ | Partition Columns: | +------------------------------------------------------------------------------------+ | gender | string | | +------------------------------------------------------------------------------------+ OK
增加/删除分区表中的分区：
odps@ sdrtest>alter table t_people_p add partition (gender = 'male'); //增加分区 odps@ sdrtest>alter table t_people_p drop if exists partition (gender = 'male'); //删除已有分区
修改表属性：
odps@ sdrtest>alter table t_people set lifecycle 7; //修改表的lifecycle属性为7天； 查看表结构验证： odps@ sdrtest>desc t_people; +------------------------------------------------------------------------------------+ | Owner: ALIYUN$1399438812@qq.com | Project: sdrtest | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2019-04-13 09:02:41 | | LastDDLTime: 2019-04-13 09:02:41 | | LastModifiedTime: 2019-04-13 09:02:41 | | Lifecycle: 7 | //验证为lifecycle里面的属性确定被修改为了7天； +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | id | bigint | | | | name | string | | | +------------------------------------------------------------------------------------+ OK
给表格新增一列： odps@ sdrtest>alter table t_people add columns (age bigint); odps@ sdrtest>desc t_people; +------------------------------------------------------------------------------------+ | Owner: ALIYUN$1399438812@qq.com | Project: sdrtest | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2019-04-13 09:02:41 | | LastDDLTime: 2019-04-13 09:20:09 | | LastModifiedTime: 2019-04-13 09:02:41 | | Lifecycle: 7 | +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | id | bigint | | | | name | string | | | | age | bigint | | | +------------------------------------------------------------------------------------+ OK
修改表名： odps@ sdrtest>alter table t_people rename to t_women; 查看修改结果： odps@ sdrtest>list tables; ALIYUN$1399438812@qq.com:t_women
更新列名： odps@ sdrtest>ALTER TABLE t_women CHANGE COLUMN age RENAME TO age1; //将age列名更新为age1 odps@ sdrtest>desc t_women; +------------------------------------------------------------------------------------+ | Owner: ALIYUN$1399438812@qq.com | Project: sdrtest | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2019-04-13 09:02:41 | | LastDDLTime: 2019-04-13 09:28:02 | | LastModifiedTime: 2019-04-13 09:02:41 | | Lifecycle: 7 | +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | id | bigint | | | | name | string | | | | age1 | bigint | | | //验证列名已经更新； +------------------------------------------------------------------------------------+ OK
others:...
