MaxCompute表操作相关命令

一、创建表

1.1 建表语法格式

--创建新表。
 create [external] table [if not exists] <table_name>
 [primary key (<pk_col_name>, <pk_col_name2>),(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)]
 [comment <table_comment>]
 [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
 
--用于创建聚簇表时设置表的Shuffle和Sort属性。
 [clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets] 

--仅限外部表。
 [stored by StorageHandler] 
 --仅限外部表。
 [with serdeproperties (options)] 
 --仅限外部表。
 [location <osslocation>] 

--指定表为Transactional1.0表，后续可以对该表执行更新或删除表数据操作，但是Transactional表有部分使用限制，请根据需求创建。
 [tblproperties("transactional"="true")]

--指定表为Transactional2.0表，后续可以做upsert，增量查询，time-travel等操作
 [tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [lifecycle <days>]
;

--基于已存在的表创建新表并复制数据，但不复制分区属性。支持外部表和湖仓一体外部项目中的表。
create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;

--基于已存在的表创建具备相同结构的新表但不复制数据，支持外部表和湖仓一体外部项目中的表。
create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];

1.2 建表函数

external：可选。表示创建的表为外部表。
if not exists：可选。如果不指定if not exists选项而存在同名表，会报错。如果指定if not exists，只要存在同名表，即使原表结构与要创建的目标表结构不一致，均返回成功。已存在的同名表的元数据信息不会被改动。
table_name：必填。表名。表名大小写不敏感，不能有特殊字符，只能包含a~z、A~Z、数字和下划线（_）。建议以字母开头，名称的长度不超过128字节，否则报错。
col_name：可选。表的列名。列名大小写不敏感，不能有特殊字符，只能包含a~z、A~Z、数字、下划线（_）或中文。建议以字母开头，名称的长度不超过128字节，否则报错。
col_comment：可选。列的注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错。
data_type：可选。列的数据类型，包含BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等多种数据类型，详情请参见数据类型版本说明。
not null：可选。禁止该列的值为NULL。更多修改非空属性信息，请参见修改表的列非空属性。
default_value：可选。指定列的默认值，当insert操作不指定该列时，该列写入默认值。
table_comment：可选。表注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错。
lifecycle：可选。表的生命周期，仅支持正整数。单位：天。
非分区表：自最后一次修改表数据开始计算，经过days天后数据无改动，则您无需干预此表，MaxCompute会自动回收（类似drop table操作）。
分区表：系统根据各分区的LastModifiedTime判断是否需要回收分区。不同于非分区表，分区表的最后一个分区被回收后，该表不会被删除。生命周期只能设定到表级别，不支持在分区级别设置生命周期。

1.3 分区表参数

partitioned by (<col_name> <data_type> [comment <col_comment>], ...：可选。指定分区表的分区字段。
col_name：表的分区列名。列名大小写不敏感，不能有特殊字符，只能包含a~z、A~Z、数字、下划线（_）或中文。建议以字母开头，名称的长度不超过128字节，否则报错。
data_type：分区列的数据类型。MaxCompute 1.0版本仅支持STRING类型。MaxCompute 2.0版本扩充了分区类型，包含TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING类型。详情请参见数据类型版本说明。当使用分区字段对表进行分区时，新增分区、更新分区内数据和读取分区数据均不需要做全表扫描，提高处理效率。
col_comment：分区列的注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错

1.4 常用的分区操作命令

1.4.1 添加分区----为已经存在的分区表新增分区

alter table <table_name> add [if not exists] partition <pt_spec> [partition <pt_spec> partition <pt_spec>...];
-- table_name 必填 待新增分区的分区表名称
-- if not exists：可选。如果未指定if not exists而同名的分区已存在，会执行失败并返回报错。
-- pt_spec：必填。新增的分区，格式为(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。partition_col是分区字段，partition_col_value是分区值。分区字段不区分大小写，分区值区分大小写。
-- 示例，给test_table 表添加20230101的分区
alter table test_table add if not exists partition (ds='20230101');

1.4.2 删除分区----为已存在的分区表删除分区。

--一次删除一个分区。
alter table <table_name> drop [if exists] partition <pt_spec>;
--一次删除多个分区。
alter table <table_name> drop [if exists] partition <pt_spec>,partition <pt_spec>[,partition <pt_spec>....];
--批量删除分区。
alter table test_table drop if exists partition(ds < '20230101');
alter table test_table drop if exists partition(ds >= '20230101');
alter table test_table drop if exists partition(ds LIKE '2023%');
alter table test_table drop if exists partition(ds IN ('20230101','20230102','20230103'));
alter table test_table drop if exists partition(ds BETWEEN '20230101' AND '20230201');
alter table test_table drop if exists partition(substr(ds, 1, 4) = '2023');
alter table test_table drop if exists partition(ds < '20230101' OR ds >= '20230201');
alter table test_table drop if exists partition(ds > '20230101' AND ds <= '20230201');
alter table test_table drop if exists partition(NOT ds > '20230101');
--支持多个分区过滤表达式，表达式之间是OR的关系。
alter table test_table drop if exists partition(ds < '20230101'), partition(ds >= '20230201');

1.4.3 清空分区表

-- 未指定筛选条件
truncate table <table_name> partition <pt_spec>[, partition <pt_spec>....];
-- 指定筛选条件
truncate table <table_name> partition <partition_filtercondition>;

1.4.4 清空非分区表

--清空非分区表，表类型必须为非分区表, 否则报错
truncate table mf_tt2; 

二、创建视图

2.1 语法格式

create [or replace] view [if not exists] <view_name>
    [(<col_name> [comment <col_comment>], ...)]
    [comment <view_comment>]
    as <select_statement>;
   --参数说明 
   --or replace：可选。更新视图需要携带该字段。
   --if not exists：可选。如果没有指定if not exists，在视图已经存在时用create view会导致异常。这种情况可以用create or replace view重建视图，重建后视图本身的权限保持不变。
   --view_name：必填。待创建或更新的视图的名称。
   --col_name：必填。待创建视图包含的列名称。
   --col_comment：可选，待创建视图的列的注释。
   --view_comment：可选。待创建视图的注释。
   --select_statement：必填。select查询语句，是视图的数据来源，您必须有视图所引用表的读权限。视图只能包含一个有效的select语句。
   -- 删除视图
   drop view [if exists] <view_name>;