MaxComputeSQL DDL语句-表操作
表是MaxCompute的数据存储单元。数据仓库的开发、分析及运维都需要对表数据进行处理。
创建表
创建非分区表、分区表、外部表或聚簇表。
-
命令格式
create [external] table [if not exists] <table_name>
[(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)]
[comment <table_comment>]
[partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
[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>]
[tblproperties("transactional"="true")]
[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>];
-
参数说明
-
external：可选。表示创建的表为外部表。 -
if not exists：可选。如果不指定if not exists选项而存在同名表，会报错。如果指定if not exists，无论是否存在同名表，即使原表结构与要创建的目标表结构不一致，均返回成功。已存在的同名表的元数据信息不会被改动。 -
table_name：必填。表名。表名大小写不敏感，不能有特殊字符，只能包含az、AZ、数字和下划线（_）。建议以字母开头，名称的长度不超过128字节，否则报错。 -
col_name：可选。表的列名。列名大小写不敏感，不能有特殊字符，只能包含az、AZ、数字和下划线（_）。建议以字母开头，名称的长度不超过128字节，否则报错。 -
col_comment：可选。列的注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错。 -
data_type：可选。列的数据类型，包含BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等多种数据类型。 -
not null：可选。禁止该列的值为NULL。 -
default_value：可选。指定列的默认值，当insert操作不指定该列时，该列写入默认值。 -
table_comment：可选。表注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错。 -
partitioned by (<col_name> <data_type> [comment <col_comment>], …：可选。指定分区表的分区字段。
- col_name：表的分区列名。列名大小写不敏感，不能有特殊字符，只能包含az、AZ、数字和下划线（_）。建议以字母开头，名称的长度不超过128字节，否则报错。
- data_type：分区列的数据类型，包含BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等多种数据类型。MaxCompute 1.0版本仅支持STRING类型。MaxCompute 2.0版本扩充了分区类型，包含TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING类型。当使用分区字段对表进行分区时，新增分区、更新分区内数据和读取分区数据均不需要做全表扫描，提高处理效率。
- col_comment：分区列的注释内容。注释内容为长度不超过1024字节的有效字符串，否则报错。
说明 分区值不能包含双字节字符（如中文），必须以字母开头，包含字母、数字和允许的字符，长度不超过128字节。允许的字符包括空格、冒号（:）、下划线（_）、美元符号（$）、井号（#）、英文句点（.）、感叹号（!）和at（@），其他字符的行为未定义，例如转义字符\t、\n和/。
-
clustered by | range clustered by (<col_name> [, <col_name>, …]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] …])] into <number_of_buckets> buckets：可选。用于创建聚簇表时设置表的Shuffle和Sort属性。 聚簇表分为Hash聚簇表和Range聚簇表两种：
-
Hash聚簇表
-
clustered by：指定Hash Key。MaxCompute将对指定列进行Hash运算，按照Hash值分散到各个Bucket中。为避免数据倾斜和热点，并取得较好的并行执行效果，clustered by列适宜选择取值范围大，重复键值少的列。此外，为了达到join优化的目的，也应该考虑选取常用的Join或Aggregation Key，即类似于传统数据库中的主键。 -
sorted by：指定Bucket内字段的排序方式。建议sorted by和clustered by保持一致，以取得较好的性能。此外，当指定sorted by子句后，MaxCompute将自动生成索引，并且在查询时利用索引来加快执行。 -
number_of_buckets：指定哈希桶的数量。该值必须填写，且由数据量大小决定。此外，MaxCompute默认支持最多1111个Reducer，所以此处最多只支持1111个哈希桶。您可以使用set odps.sql.reducer.instances=xxx;来提升这个限制，但最大不得超过4000，否则会影响性能。 选择哈希桶数目时，请您遵循以下两个原则：
- 哈希桶大小适中：建议每个Bucket的大小为500 MB左右。例如，分区大小估计为500 GB，初略估算Bucket数目应该设为1000，这样平均每个Bucket大小约为500 MB。对于特别大的表，500 MB的限制可以突破，每个Bucket在2 GB~3 GB左右比较合适。同时，可以结合
set odps.sql.reducer.instances=xxx;来突破1111个桶的限制。 - 对于
join优化场景，去除Shuffle和Sort步骤能显著提升性能。因此要求两个表的哈希桶数目成倍数关系，例如256和512。建议哈希桶的数量统一使用2n，例如512、1024、2048或4096，这样系统可以自动进行哈希桶的分裂和合并，也可以去除Shuffle和Sort的步骤，提升执行效率。
-
Range聚簇表
-
range clustered by：指定范围聚簇列。MaxCompute将对指定列进行分桶运算，按照分桶编号分散到各个Bucket中。 -
sorted by：指定Bucket内字段的排序方式，使用方法与Hash聚簇表相同。 -
number_of_buckets：指定哈希桶的数量。Range聚簇表的Bucket桶数没有Hash聚簇表的2n最佳实践，在数据量分布合理的情况下，任何桶数都可以。Range聚簇表的Bucket数不是必须的，可以省略，此时系统会根据数据量自动决定最佳的Bucket数目。 -
当Join或Aggregation的对象是Range聚簇表，且Join Key或Group Key是Range Clustering Key或其前缀时，可以通过控制Flag消除数据的重分布，即Shuffle Remove，提升执行效率。您可以通过set odps.optimizer.enable.range.partial.repartitioning=ture/flase;
说明
- 聚簇表的优点：
- 优化Bucket Pruning。
- 优化Aggregation。
- 优化存储。
- 聚簇表的限制：
- 不支持
insert into，只能通过insert overwrite来添加数据。 - 不支持Tunnel直接Upload到Range聚簇表，因为Tunnel上传数据是无序的。
-
stored by StorageHandler：可选。按照外部表数据格式指定StorageHandler。 -
with serdeproperties (options)：可选。外部表的授权、压缩、字符解析等相关参数。 -
osslocation：可选。外部表数据OSS存储位置。 -
tblproperties(“transactional”=“true”)：可选。设置表为Transactional表。后续可以对Transactional表执行update、delete操作实现行级更新或删除数据。 Transactional表的使用限制如下：
-
MaxCompute只允许在创建表时设置Transactional属性。已创建的表不允许通过alter table方式修改Transactional属性，执行如下语句会报错： alter table not_txn_tbl set tblproperties("transactional"="true");
FAILED: Catalog Service Failed, ErrorCode: 151, Error Message: Set transactional is not supported
-
在创建表时，不支持将聚簇表、外部表设置为Transactional表。 -
不支持MaxCompute内部表、外部表、聚簇表与Transactional表互转。 -
不支持其他系统的作业（例如MaxCompute Spark、PAI、Graph）访问Transactional表。 -
不支持clone table、merge partition操作。 -
不支持通过备份与恢复功能备份数据，因此在对Transactional表的重要数据执行update、delete或insert overwrite操作前需要手动通过select+insert操作将数据备份至其他表中。
-
lifecycle：可选。表的生命周期，仅支持正整数。单位：天。
- 非分区表：自最后一次修改表数据开始计算，经过days天后数据无改动，则您无需干预此表，MaxCompute会自动回收（类似drop table操作）。
- 分区表：系统根据各分区的LastDataModifiedTime判断是否需要回收分区。不同于非分区表，分区表的最后一个分区被回收后，该表不会被删除。生命周期只能设定到表级别，不支持在分区级别设置生命周期。
-
通过create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;语句可以再创建一个表，并在建表的同时将数据复制到新表中。但通过该语句创建的表不会复制分区属性，只会把源表的分区列作为目标表的一般列处理，也不会复制源表的生命周期属性。您还可以通过lifecycle参数回收表。 -
通过create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];语句可以再创建一个表，使目标表和源表具有相同的表结构。但通过该语句创建的表不复制数据，也不会复制源表的生命周期属性。您还可以通过lifecycle参数回收表。
-
使用示例
-
示例1：创建非分区表test1。 create table test1 (key STRING);
-
示例2：创建一张分区表sale_detail。 create table if not exists sale_detail(
shop_name STRING,
customer_id STRING,
total_price DOUBLE)
partitioned by (sale_date STRING, region STRING);
-
示例3：创建一个新表sale_detail_ctas1，将sale_detail的数据复制到sale_detail_ctas1中，并设置生命周期。 create table sale_detail_ctas1 lifecycle 10 as select * from sale_detail;
您可以通过desc extended sale_detail_ctas1;命令查看到表的结构及生命周期等详细信息。 此处sale_detail是一张分区表，而通过create table ... as select_statement ...语句创建的表sale_detail_ctas1不会复制分区属性，只会把源表的分区列作为目标表的一般列处理。即sale_detail_ctas1是一个含有5列的非分区表。 -
示例4：创建一个新表sale_detail_ctas2，在select子句中使用常量作为列的值。
create table sale_detail_ctas2
as
select shop_name, customer_id, total_price, '2013' as sale_date, 'China' as region
from sale_detail;
create table sale_detail_ctas3
as
select shop_name, customer_id, total_price, '2013', 'China'
from sale_detail;
说明 如果在select子句中使用常量作为列的值，建议您指定列的名字。创建的表sale_detail_ctas3的第四、五列类似于_c4、_c5。
-
示例5：创建一个新表sale_detail_like，与sale_detail具有相同的表结构，并设置生命周期。 create table sale_detail_like like sale_detail lifecycle 10;
您可以通过desc extended sale_detail_like;命令查看到表的结构及生命周期等详细信息。 sale_detail_like的表结构与sale_detail完全相同。除生命周期属性外，列名、列注释以及表注释等均相同。但sale_detail中的数据不会被复制到sale_detail_like表中。 -
示例6：创建使用新数据类型的表test_newtype。 set odps.sql.type.system.odps2=true;
CREATE TABLE test_newtype (
c1 TINYINT
,c2 SMALLINT
,c3 INT
,c4 BIGINT
,c5 FLOAT
,c6 DOUBLE
,c7 DECIMAL
,c8 BINARY
,c9 TIMESTAMP
,c10 ARRAY<MAP<BIGINT,BIGINT>>
,c11 MAP<STRING,ARRAY<BIGINT>>
,c12 STRUCT<s1:STRING,s2:BIGINT>
,c13 VARCHAR(20))
LIFECYCLE 1
;
-
示例7：创建Hash聚簇非分区表t1。 create table t1 (a STRING, b STRING, c BIGINT) clustered by (c) sorted by (c) into 1024 buckets;
-
示例8：创建Hash聚簇分区表t2。 create table t2 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) clustered by (c) sorted by (c) into 1024 buckets;
-
示例9：创建Range聚簇非分区表t3。 create table t3 (a STRING, b STRING, c BIGINT) range clustered by (c) sorted by (c) into 1024 buckets;
-
示例10：创建Range聚簇分区表t4。 create table t4 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) range clustered by (c) sorted by (c);
-
示例11：创建Transactional非分区表t5。 create table t5(id bigint) tblproperties("transactional"="true");
-
示例12：创建Transactional分区表t6。 create table if not exists t6(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");
修改表的所有人
修改表的所有人，即表Owner。
修改表的注释
修改表的注释内容。
-
命令格式 alter table <table_name> set comment '<new_comment>';
-
参数说明
- table_name：必填。待修改注释的表的名称。
- new_comment：必填。修改后的注释名称。
-
使用示例 alter table sale_detail set comment 'new coments for table sale_detail';
您可以通过MaxCompute的desc table_name命令查看表中comment的修改结果。
修改表的修改时间
MaxCompute SQL提供touch操作用来修改表的LastDataModifiedTime，可将表的LastDataModifiedTime修改为当前时间。此操作会改变表的LastDataModifiedTime的值，MaxCompute会认为表的数据有变动，生命周期的计算会重新开始。
-
命令格式 alter table <table_name> touch;
-
参数说明 table_name：必填。待修改表的修改时间的表名称。 -
使用示例 alter table sale_detail touch;
修改表的聚簇属性
对于分区表，MaxCompute支持通过alter table语句增加或者去除聚簇属性。
-
命令格式
-
增加表的Hash聚簇属性的语法格式如下： alter table <table_name> [clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets];
-
去除表的Hash聚簇属性的语法格式如下： alter table <table_name> not clustered;
-
增加表的Range聚簇属性，Bucket数不是必须的，可以省略，此时系统会根据数据量自动决定最佳的Bucket数目。语法格式如下： alter table <table_name> [range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets];
-
去除表或分区的Range聚簇属性的语法格式如下： alter table <table_name> not clustered;
alter table <table_name> <pt_spec> not clustered;
说明
- 通过
alter table改变聚簇属性，只对分区表有效，非分区表一旦建立聚簇属性就无法改变。alter table语句适用于存量表，在增加了新的聚簇属性后，新的分区将按设置的聚簇属性存储。 alter table只会影响分区表的新建分区（包括insert overwrite生成的），新分区将按新的聚簇属性存储，老数据分区的聚簇属性和存储保持不变。即在一张曾经做过聚簇属性设置的表上，关闭了聚簇属性，再增加聚簇设置，可以在新分区设置不同于之前的聚簇列、排序列及分桶数。- 由于
alter table只影响新分区，所以该语句不可以再指定分区。
重命名表
重命名表的名称。仅修改表的名字，不改动表中的数据。
清空非分区表里的数据
将指定的非分区表中的数据清空，该命令不支持分区表。对于分区表，可以用alter table table_name drop partition命令清除分区中的数据。
删除表
删除非分区表或分区表。
查看表或视图信息
查看MaxCompute内部表、视图、外部表、聚簇表或Transactional表的信息。
-
命令格式
desc <table_name|view_name> [partition (<pt_spec>)];
desc extended <table_name>;
-
参数说明
- table_name：必填。待查看表的名称。
- view_name：必填。待查看视图的名称。
- pt_spec：可选。待查看分区表的指定分区。格式为
(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)。 - extended：如果表为外部表、聚簇表或Transactional表，需要包含此参数。显示表的扩展信息。也可以查看内部表的扩展信息，例如列的非空属性。
-
使用示例
-
示例1：查看创建的test1表的信息。 desc test1;
-
示例2：查看创建的sale_detail表的信息。 desc sale_detail;
-
示例3：查看创建的sale_detail_ctas1表的详细信息。 desc extended sale_detail_ctas1;
sale_date和region两个字段仅会作为普通列存在，而不是表的分区。 -
示例4：查看创建的sale_detail_ctas2表的信息。 desc sale_detail_ctas2;
返回结果如下。
-
示例5：查看创建的sale_detail_like表的详细信息。 desc extended sale_detail_like;
除生命周期属性外，sale_detail_like的其它属性（字段类型、分区类型等）均与sale_detail完全一致。
说明 通过desc table_name查看到的Size包含了在回收站的数据Size。如果您需要清空回收站，可以先执行purge table table_name，然后再执行desc table_name查看除回收站以外的数据大小。您也可以执行show recyclebin查看本项目中回收站内的数据明细。
-
示例6：查看创建的test_newtype表的信息。 desc test_newtype;
-
示例7：查看创建的Hash聚簇非分区表t1的信息。聚簇属性将显示在Extended Info中。 desc extended t1;
-
示例8：查看创建的Hash聚簇分区表t2的信息。聚簇属性将显示在Extended Info中。 desc extended t2;
-
示例9：查看Range聚簇非分区表t3的信息。聚簇属性将显示在Extended Info中。 desc extended t3;
-
示例10：查看Range聚簇分区表t4的信息。聚簇属性将显示在Extended Info中。
desc extended t4;
-
示例11：查看非分区表t5是否为Transactional表。 desc extended t5;
-
示例12：查看分区表t6是否为Transactional表。 desc extended t6;
查看分区信息
查看某个分区表具体的分区的信息。
-
命令格式 desc <table_name> partition (<pt_spec>);
-
参数说明
- table_name：必填。待查看分区信息的分区表名称。
- pt_spec：必填。待查看的分区信息。格式为
partition_col1=col1_value1, partition_col2=col2_value1...。对于有多级分区的表，必须指明全部的分区值。
-
使用示例
desc sale_detail partition (sale_date='201310',region='beijing');
查看建表语句
生成创建表的SQL DDL语句，方便您通过SQL重建Schema。
列出项目空间下的表和视图
列出项目空间下所有的表和视图，或符合某规则的表和视图。
列出所有分区
列出一张表中的所有分区，表不存在或为非分区表时，返回报错。
|