【MaxCompute】学习笔记操作表sql

创建表

命令格式：

# 建表
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], ...)]
 [AS select_statement] 
 
 # 复制表
 CREATE TABLE [IF NOT EXISTS] table_name
 LIKE existing_table_name

实例：创建表 shop 用于存储所有的店铺信息：

create table shop(
shop_name string comment '店铺名',
shop_location string comment ' 商店地址',
revenue double comment '多少钱'
)comment '表备注';

备注：

1. 表名与列名均对大小写不敏感。
2. 在创建表时，如果不指定 IF NOT EXISTS 选项而表名已存在，则返回出错。
3. PARTITIONED BY 指定表的分区字段，如前所述，当利用 partition 字段对表进行分区时，新增分区、更新分区内数据和读取分区数据均不需要做全表扫描，可以提高处理效率。
4. 表名，列名中不能有特殊字符，只能用英文的 a-z,A-Z 及数字和下划线_，且以字母开头，名称的长度不超过 128 字节。
5. 分区名称不可以有双字节字符（如中文），必须是以英文字母 a-z,A-Z 开始后可跟字母数字，名称的长度不超过 128 字节。 如果名称中出现了\t, \n 等特殊字符会导致分区中的数据无法读出，允许的字符包括：空格” “，冒号”:”, 下划线”_”, 美元符”$”, 井号”#”, 点”.”, 感叹号”!”和@
6. 注释内容是长度不超过 1024 字节的有效字符串。

在下面的例子中，创建表 sale_detail 保存销售记录，该表使用销售时间(sale_date)和销售区域(region)作为分区列：

create table sale_detail(
shop_name string comment '店铺名',,
customer_id string comment '顾客ID',,
total_price double comment '总价',)
partitioned by (sale_date string comment '销售时间',region string comment '销售区域');

7. 目前分区键只能用 STRING 类型。

也可以通过 CREATE TABLE … AS SELECT …语句创建表，并在建表的同时将数据复制到新表中，
如：

create table shop_backup as
select * from shop;

如果只想复制已有表结构，而不复制任何数据，可以用 CREATE LIKE，用这种方式创建的新表会复制表的结构，列注释，表注释以及定义在表上的统计项。

create table shop_tmp like shop;

注意：

正常情况下，规定建的分区层次不超过 5 级

创建视图

命令格式：

CREATE [OR REPLACE] VIEW [IF NOT EXISTS] view_name
 [(col_name [COMMENT col_comment], ...)]
 [COMMENT view_comment]
 [AS select_statement]

注：

1. 创建视图时，必须有对基础表的读权限。
2. 视图只能包含一个有效的 SELECT 语句。
3. 视图可以引用其它视图，但不能引用自己，也不能循环引用。
4. 视图不可以写。
5. 当视图建好以后，如果视图的基础表发生了变更，有可能导致视图无法访问，如基础表被删除。
6. 在 VIEW 已经存在时用 CREATE VIEW 会导致异常，这种情况可以用 CREATE OR REPLACE VIEW 来重建 VIEW，重建后 VIEW 本身的权限保持不变。

删除表

命令格式：

DROP TABLE [IF EXISTS] table_name;

实例：

DROP TABLE shop;

注：

if exists：可选。如果不指定if exists且表不存在，则返回异常。如果指定if exists，无论表是否存在，均返回成功。

删除视图

命令格式：

DROP VIEW [IF EXISTS] view_name;

重命名表

命令格式：

ALTER TABLE table_name RENAME TO new_table_name;

Rename 操作仅修改表的名字，不改动表中的数据。：

ALTER TABLE shop RENAME to store;

重命名视图

命令格式：

ALTER VIEW view_name RENAME TO new_view_name;

添加分区

命令格式：

ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec 

partition_spec:
 : (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

注意：

如果未指定 IF NOT EXISTS 而同名的分区已存在，则出错返回。

如为 sale_detail 表添加一个新的分区，用来存储 2011 年 10 月 10 日杭州地区的销售记录：

ALTER TABLE sale_detail ADD IF NOT EXISTS PARTITION
(sale_date= '20111010', region=' hangzhou');

删除分区

命令格式：

ALTER TABLE table_name DROP [IF EXISTS] partition_spec’;

如从表 sale_detail 中删除一个分区，该分区存储的是 2011 年 10 月 10 日杭州地区的销售记录。这个操作不必扫描全表判断销售日期和销售区域。

ALTER TABLE sale_detail DROP PARTITION(sale_date=‘20111010’,region=‘hangzhou’);

修改列名和列注释

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment]

备注：

1. ODPS SQL 仅仅允许修改列名和列注释，不允许修改列数据类型、列位置，也不允许删除已有列。

实例：

ALTER TABLE shop CHANGE shop_location shop_city STRING COMMENT ' in which city the shop';

修改表的注释

ALTER TABLE table_name SET COMMENT ‘tbl comment’

添加列

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 CHANGE COLUMN col_name COMMENT ‘comment’;

查看表的信息

查看当前数据库中的所有表，默认显示当前 project 中的表。如果指定了 IN project 选项，则显示的是在该 project 中的表

SHOW TABLES [IN project_name];

查看一张表的所有 partition

SHOW PARTITIONS table_name;

查看表结构信息

DESCRIBE|DESC table_name [partition_name];

该命令可以查看表或是表的 partition 的信息，并且可以返回表的大小、表的创建者、表的创建时间、表的上次 meta 修改时间、上次数据修改时间，输出的格式如：

desc xdj_pt partition (pt=2011);
+-------------------------------------------------------+ 
|  PartitionSize:           184                         |
+-------------------------------------------------------+
| CreateTime:               2012-08-04 13:14:13_________|
| LastDDLTime:              2012-08-04 13:27:27_________|
| LastModifiedTime:         2012-08-04 13:27:27_________|

查看 SQL 语句的执行计划

可以用 explain 功能查看 SQL 语句的执行计划，语法是:

explain sql_statement;

如:

explain create table dual_a as select * from dual;

job0 is root job

In Job job0:
root Tasks: M1_Stg1

In Task M1_Stg1:
    Data source: odps_de_1.dual
    TS:
        SEL: dual.id
            FS: output: odps_de_1.dual_a

注：

DDL 建表语句不支持 explain 功能。