本文为您介绍如何通过MaxCompute客户端(odpscmd)创建、查看、删除、重命名和修改表信息。
创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [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] -- 用于创建聚簇表时设置表的Shuffle和Sort属性。
[STORED BY StorageHandler] -- 仅限外部表。
[WITH SERDEPROPERTIES (Options)] -- 仅限外部表。
[LOCATION OSSLocation] -- 仅限外部表。
[LIFECYCLE days];
CREATE TABLE [IF NOT EXISTS] table_name
[AS select_statement | LIKE existing_table_name];
- IF NOT EXISTS:创建表时,如果不指定
IF NOT EXISTS
选项而存在同名表,则返回报错。如果指定此选项,则无论是否存在同名表,即使原表结构与要创建的目标表结构不一致,均返回成功。已存在的同名表的元数据信息不会被改变。 - table_name、col_name:
- 表名与列名均对大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字和下划线(_),且建议以字母开头,名称的长度不超过128字节。
- 单表的列个数最多为1200。
- data_type:数据类型,可以为BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等,MaxCompute 2.0版本扩展了很多数据类型,详情请参见数据类型。
说明 MaxCompute SQL中使用到新数据类型(TINYINT、SMALLINT、INT、FLOAT、VARCHAR、TIMESTAMP、BINARY)时,您需要执行如下命令打开新数据类型开关:
- Session级别:需要在SQL语句前增加
set odps.sql.type.system.odps2=true;
语句,并与SQL语句一起提交执行。 - Project级别:支持在Project级别打开新数据类型。项目所有者可根据需要对Project进行设置,命令为:
setproject odps.sql.type.system.odps2=true;
setproject
的详细说明请参见设置项目空间属性。
- Session级别:需要在SQL语句前增加
- DEFAULT value:指定列的默认值,当INSERT操作不指定该列时,该列写入默认值。
- COMMENT table_comment:长度不超过1024字节的有效字符串。
- PARTITIONED BY (col_name data_type [COMMENT col_comment], …):指定表的分区字段,支持TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING类型。
分区值不允许有双字节字符(例如中文),必须是以字母开头,包含字母、数字或字符,长度不超过128字节。允许的字符包括:空格、英文冒号(:)、下划线(_)、美元符($)、井号(#)、点(.),感叹号(!)和and(@),其他字符行为未定义,例如\t)、(\n)、(/)等。当利用分区字段对表进行分区时,新增分区、更新分区内数据和读取分区数据均不需要做全表扫描,可以提高处理效率。说明 一张表最多允许60000个分区,单表的分区层次不能超过6级。
- 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将自动生成索引,并且在查询时利用索引来加快执行。
- INTO number_of_buckets 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的步骤,提升执行效率。
- 哈希桶大小适中:经验值是每个Bucket的大小在500 MB左右比较合理。例如,分区大小估计为500 GB,初略估算Bucket数目应该设为1000,这样平均每个Bucket大小约为500
MB。对于特别大的表,500 MB的限制可以突破,每个Bucket在2 GB~3 GB左右比较合适。同时,可以结合
- CLUSTERED BY:指定Hash Key。MaxCompute将对指定列进行Hash运算,按照Hash值分散到各个Bucket中。为避免数据倾斜和热点,取得较好的并行执行效果,
- Range聚簇表
- RANGE CLUSTERED BY:指定范围聚簇列。MaxCompute将对指定列进行分桶运算,按照分桶编号分散到各个Bucket中。
- SORTED BY:指定Bucket内字段的排序方式,使用方法与Hash聚簇表相同。
- INTO number_of_buckets BUCKETS:指定哈希桶的数量。Range聚簇表的Bucket桶数不像Hash聚簇表那样有2n的最佳实践,在数据量分布合理的情况下,任何桶数都可以。但是Range聚簇表的Bucket数不是必须的,可以省略,此时系统会根据数据量自动决定最佳的Bucket数目。
- 当Join和Aggregate的对象是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上传数据是无序的。
- 不支持
- 聚簇表的优点:
- Hash聚簇表
- 外部表相关的子句:
- STORED BY StorageHandler:按照外表数据格式指定StorageHandler。
- WITH SERDEPROPERTIES (Options):外表的授权、压缩、字符解析等相关参数。
- LOCATION OSSLocation:外表数据OSS存储位置,详情请参见内置Extractor访问OSS和处理OSS的开源格式数据。
- LIFECYCLE:表的生命周期,单位为天。
CREATE TABLE LIKE
语句不会复制源表的生命周期属性。 - CREATE TABLE [IF NOT EXISTS] table_name [AS select_statement | LIKE existing_table_name]:
- 通过
CREATE TABLE…AS select_statement…
语句再创建一个表,并在建表的同时将数据复制到新表中,但通过CREATE TABLE…AS select_statement…
语句创建的表不会复制分区属性,只会把源表的分区列作为目标表的一般列处理。 - 通过CREATE TABLE…LIKE existing_table_name语句再创建一个表,可以使目标表和源表具有相同的表结构,但不复制数据,也不会复制源表的生命周期属性。
- 通过
创建分区表示例
sale_detail
来保存销售记录,该表使用销售时间sale_date
和销售区域region
作为分区列,建表语句如下所示。CREATE TABLE IF NOT EXISTS sale_detail
(
shop_name STRING,
customer_id STRING,
total_price DOUBLE
)
PARTITIONED BY (sale_date STRING,region STRING);
--创建一张分区表sale_detail。
CREATE TABLE...AS...
示例
CREATE TABLE...AS select_statement...
语句再创建一个表,并在建表的同时将数据复制到新表中,命令示例如下。CREATE TABLE sale_detail_ctas1 AS
SELECT * FROM sale_detail;
sale_detail
中存在数据,上述语句会将表sale_detail
的数据全部复制到表sale_detail_ctas1
。
sale_detail
是一张分区表,而通过CREATE TABLE...AS select_statement...
语句创建的表sale_detail_ctas1
不会复制分区属性,只会把源表的分区列作为目标表的一般列处理。即sale_detail_ctas1
是一个含有5列的非分区表。
CREATE TABLE...AS select_statement...
语句中,如果在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;
创建的表sale_detail_ctas3
的第四、五列类似于_c4
、_c5
。
CREATE TABLE...LIKE...
示例
CREATE TABLE...LIKE...
操作,命令示例如下。CREATE TABLE sale_detail_like LIKE sale_detail;
此时,sale_detail_like
的表结构与sale_detail
完全相同。除生命周期属性外,列名、列注释以及表注释等均相同。但sale_detail
中的数据不会被复制到sale_detail_like
表中。
创建聚簇表示例
创建Hash聚簇表的命令示例如下。
CREATE TABLE T1 (a STRING, b STRING, c BIGINT) CLUSTERED BY (c) SORTED by (c) INTO 1024 BUCKETS; --创建Hash聚簇非分区表。
CREATE TABLE T1 (a STRING, b STRING, c BIGINT) PARTITIONED BY (dt STRING) CLUSTERED BY (c) SORTED by (c) INTO 1024 BUCKETS; --创建Hash聚簇分区表。
CREATE TABLE T2 (a STRING, b STRING, c BIGINT) RANGE CLUSTERED BY (c) SORTED by (c) INTO 1024 BUCKETS; --创建Range聚簇非分区表。
CREATE TABLE T2 (a STRING, b STRING, c BIGINT) PARTITIONED BY (dt STRING) CLUSTERED BY (c) SORTED by (c) ; --创建Range聚簇分区表,可以不指定BUCKETS数。
查看表信息
DESC <table_name>;
DESC EXTENDED <table_name>; --查看外部表信息。
- 如果您需要查看上述示例中表
sale_detail
的信息,可输入如下命令。DESC sale_detail;
结果如下。odps@ $odps_project>DESC sale_detail; +--------------------------------------------------------------------+ | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $odps_project | | TableComment: | +--------------------------------------------------------------------+ | CreateTime: 2017-06-28 15:05:17 | | LastDDLTime: 2017-06-28 15:05:17 | | LastModifiedTime: 2017-06-28 15:05:17 | +--------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +--------------------------------------------------------------------+ | Native Columns: | +--------------------------------------------------------------------+ | Field | Type | Label | Comment | +--------------------------------------------------------------------+ | shop_name | string | | | | customer_id | string | | | | total_price | double | | | +--------------------------------------------------------------------+ | Partition Columns: | +--------------------------------------------------------------------+ | sale_date | string | | | region | string | | +--------------------------------------------------------------------+ OK
- 如果您需要查看上述示例表
sale_detail_ctas2
中的信息,可输入如下命令。DESC sale_detail_ctas2;
结果如下。odps@ $odps_project>desc sale_detail_ctas2; +--------------------------------------------------------------------+ | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $odps_project | | TableComment: | +--------------------------------------------------------------------+ | CreateTime: 2017-06-28 15:42:17 | | LastDDLTime: 2017-06-28 15:42:17 | | LastModifiedTime: 2017-06-28 15:42:17 | +--------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +--------------------------------------------------------------------+ | Native Columns: | +--------------------------------------------------------------------+ | Field | Type | Label | Comment | +--------------------------------------------------------------------+ | shop_name | string | | | | customer_id | string | | | | total_price | double | | | | sale_date | string | | | | region | string | | | +--------------------------------------------------------------------+ OK
- 如果您需要查看上述示例表
sale_detail_like
中的信息,可输入如下命令。DESC sale_detail_like;
结果如下。odps@ $odps_project>DESC sale_detail_like; +--------------------------------------------------------------------+ | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $odps_project | | TableComment: | +--------------------------------------------------------------------+ | CreateTime: 2017-06-28 15:42:17 | | LastDDLTime: 2017-06-28 15:42:17 | | LastModifiedTime: 2017-06-28 15:42:17 | +--------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +--------------------------------------------------------------------+ | Native Columns: | +--------------------------------------------------------------------+ | Field | Type | Label | Comment | +--------------------------------------------------------------------+ | shop_name | string | | | | customer_id | string | | | | total_price | double | | | +--------------------------------------------------------------------+ | Partition Columns: | +--------------------------------------------------------------------+ | sale_date | string | | | region | string | | +--------------------------------------------------------------------+ OK
sale_detail_like
的其它属性(字段类型、分区类型等)均与sale_detail
完全一致。查看表信息的更多详情请参见表操作。
DESC table_name
查看到的Size包含了在回收站的数据Size。如果您需要清空回收站,可以先执行PURGE TABLE table_name
,然后再执行DESC table_name
查看除回收站以外的数据大小。您也可以执行SHOW recyclebin
查看本项目中回收站内的数据明细。
如果您查看表sale_detail_ctas1
的信息,会发现sale_date
、region
两个字段仅会作为普通列存在,而不是表的分区。
DESC
命令后可以返回的类型也在增多。要使用MaxCompute新类型,普通的SQL语句需要设置新类型打开开关,但DESC
命令无需打开便可返回新类型。
DESC table_name
的输出结果有解析依赖,请及时更新适配解析MaxCompute新数据类型。
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
;
DESC test_newtype;
命令的返回结果(省略部分结果)如下。| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| 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) | | |
+------------------------------------------------------------------------------------+
DESC EXTENDED table_name;
命令查看聚簇表的聚簇属性,例如查看Hash聚簇表T1的返回结果如下,聚簇属性将显示在Extended Info中。odps@ $odps_project>DESC extended t1;
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxxxx@aliyun.com | Project: xxxxx |
| TableComment: |
+------------------------------------------------------------------------------------+
| CreateTime: 2017-12-25 11:18:26 |
| LastDDLTime: 2017-12-25 11:18:26 |
| LastModifiedTime: 2017-12-25 11:18:26 |
| Lifecycle: 2 |
+------------------------------------------------------------------------------------+
| InternalTable: YES | Size: 0 |
+------------------------------------------------------------------------------------+
| Native Columns: |
+------------------------------------------------------------------------------------+
| Field | Type | Label | Comment |
+------------------------------------------------------------------------------------+
| a | string | | |
| b | string | | |
| c | bigint | | |
+------------------------------------------------------------------------------------+
| Partition Columns: |
+------------------------------------------------------------------------------------+
| dt | string | |
+------------------------------------------------------------------------------------+
| Extended Info: |
+------------------------------------------------------------------------------------+
| TableID: 91a3395d3ef64b4d9ee1d2852755 |
| IsArchived: false |
| PhysicalSize: 0 |
| FileNum: 0 |
| ClusterType: hash |
| BucketNum: 1024 |
| ClusterColumns: [c] |
| SortColumns: [c ASC] |
+------------------------------------------------------------------------------------+
对于聚集属性分区表,除了可以使用上述命令查看表属性,还可以通过以下命令查看分区的属性。DESC EXTENDED table_name PARTITION(pt_spec);
使用SELECT
命令查看表中的详细信息,详情请参见SELECT语法介绍。
查看建表语句
SHOW CREATE TABLE <table_name>;
删除表
DROP TABLE [IF EXISTS] table_name;
- 如果不指定
IF EXISTS
选项而表不存在,则返回异常。若指定此选项,无论表是否存在,皆返回成功。 - 删除外部表时,OSS上的数据不会被删除。
CREATE TABLE sale_detail_drop LIKE sale_detail;
DROP TABLE sale_detail_drop;
--若表存在,返回成功;若不存在,返回异常。
DROP TABLE IF EXISTS sale_detail_drop2;
--无论是否存在sale_detail_drop2表,均返回成功。
重命名表
ALTER TABLE table_name RENAME TO new_table_name;
RENAME
操作仅修改表的名字,不改动表中的数据。- 如果已存在与
new_table_name
同名表,则报错。 - 如果
table_name
不存在,则报错。
CREATE TABLE sale_detail_rename1 LIKE sale_detail;
ALTER TABLE sale_detail_rename1 RENAME TO sale_detail_rename2;
修改表Owner
CHANGEOWNER
命令来修改表的拥有人(表Owner),相应的语法格式如下。ALTER TABLE table_name CHANGEOWNER TO 'ALIYUN$xxx@aliyun.com';
修改表的注释
ALTER TABLE table_name SET COMMENT 'tbl comment';
table_name
必须是已存在的表。COMMENT
最长为1024字节。
ALTER TABLE sale_detail SET COMMENT 'new coments for table sale_detail';
通过MaxCompute的DESC
命令,可以查看表中comment
的修改结果。
修改表的修改时间
MaxCompute SQL提供TOUCH
操作用来修改表的LastDataModifiedTime
,可将表的LastDataModifiedTime
修改为当前时间。
ALTER TABLE table_name TOUCH;
table_name
不存在,则报错返回。- 此操作会改变表的
LastDataModifiedTime
的值,此时,MaxCompute会认为表的数据有变动,生命周期的计算会重新开始。
修改表的聚簇属性
对于分区表,MaxCompute支持通过ALTER TABLE
语句增加或者去除聚簇属性。
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]
ALTER TABLE table_name NOT CLUSTERED;
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]
ALTER TABLE table_name NOT CLUSTERED;
ALTER TABLE table_name partition_spec NOT CLUSTERED;
- 通过
ALTER TABLE
改变聚集属性,只对于分区表有效,非分区表一旦建立聚集属性就无法改变。ALTER TABLE
语句适用于存量表,在增加了新的聚集属性之后,新的分区将按设置的聚簇属性存储。 ALTER TABLE
只会影响分区表的新建分区(包括INSERT OVERWRITE
生成的),新分区将按新的聚集属性存储,老的数据分区聚簇属性和存储保持不变。即在一张曾经做过聚簇属性设置的表上,关闭了聚簇属性,再增加聚簇设置,可以在新分区设置不同于之前的聚簇列、排序列及分桶数。- 由于
ALTER TABLE
只影响新分区,所以该语句不可以再指定分区。
清空非分区表里的数据
将指定的非分区表中的数据清空,该命令不支持分区表。对于分区表,可以用ALTER TABLE table_name DROP PARTITION
的方式将分区里的数据清除。
TRUNCATE TABLE table_name;