本文为您介绍表创建和删除的相关操作命令。
类型 | 功能 | 角色 | 操作入口 |
创建非分区表、分区表、外部表或聚簇表。 | 具备项目创建表权限(CreateTable)的用户。 | 本文中的命令您可以在如下工具平台执行: | |
删除分区表或非分区表。 | 具备删除表权限(Drop)的用户。 |
创建表
创建非分区表、分区表、外部表或聚簇表。
使用限制
分区表的分区层级不能超过6级。例如某张表以日期为分区列,分区层级为
年/月/周/日/时/分
。一张表允许的分区个数支持按照具体的项目配置,默认为6万个。
更多表的限制条件,请参见SQL使用限制项。
命令格式
创建内部表。
CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table_name> ( <col_name> <data_type>, ... ) [comment <table_comment>] [PATITIONED BY (<col_name> <data_type> [comment <col_comment>], ...)];
创建聚簇表。
CREATE TABLE [IF NOT EXISTS] <table_name> ( <col_name> <data_type>, ... ) [CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...]) [SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])] INTO <number_of_buckets> buckets];
创建外部表。
以OSS外部表为例,通过内置文本数据解析器创建外部表。详情请参见创建OSS外部表。
CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( <col_name> <data_type>, ... ) STORED AS '<file_format>' [WITH SERDEPROPERTIES (options)] LOCATION '<oss_location>';
创建表并指定表类型。
指定表为Transactional表。后续可以对该表执行更新或删除表数据操作,但是Transactional表有部分使用限制,请根据需求创建。
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> ( <col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [comment <col_comment>], ... [PRIMARY KEY (<pk_col_name>[, <pk_col_name2>, ...] )]) [comment <table_comment>] [TBLPROPERTIES ("transactional"="true")];
指定表为Delta Table表。结合PRIMARY KEY,后续可以做upsert、增量查询和Time Travel等操作。
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> ( <col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [comment <col_comment>], ... [PRIMARY KEY (<pk_col_name>[, <pk_col_name2>, ...] )]) [comment <table_comment>] [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> [LIFECYCLE <days>] LIKE <existing_table_name>;
参数说明
通用参数
参数 | 是否必填 | 说明 | 备注 |
OR REPLACE | 否 | 若<table_name>已经存在,则对现有表使用DROP TABLE,然后创建具有相同名称的新表进行替换。 | 等效于执行如下两条命令:
说明 使用限制:CREATE OR REPLACE TABLE暂不支持和以下语法共用。
|
EXTERNAL | 否 | 创建的表为外部表。 | 无 |
IF NOT EXISTS | 否 | 确认库中是否已存在创建表名。 | 如果不指定IF NOT EXISTS选项而存在同名表,会报错。如果指定IF NOT EXISTS,只要存在同名表,即使原表结构与要创建的目标表结构不一致,均返回成功。已存在的同名表的元数据信息不会被改动。 |
table_name | 是 | 表名。 | 表名大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字和下划线(_)。建议以字母开头,名称的长度不超过128字节,否则报错。 |
PRIMARY KEY(pk) | 否 | 表的主键。 | 可以定义一个或多个列作为主键,表示这些列的组合在表中必须唯一,语法遵循标准SQL primary key语法,pk列必须设置NOT NULL,不允许修改。 |
col_name | 是 | 表的列名。 | 列名大小写不敏感,不能有特殊字符,只能包含a~z、A~Z、数字、下划线(_)或中文。建议以字母开头,名称的长度不超过128字节,否则报错。 |
col_comment | 否 | 列名的注释。 | 注释内容为长度不超过1024字节的有效字符串,否则报错。 |
data_type | 是 | 列的数据类型。 | 包含BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等多种数据类型,详情请参见数据类型版本说明。 |
NOT NULL | 否 | 禁止该列的值为NULL。 | 更多修改非空属性信息,请参见分区操作。 |
default_value | 否 | 列的默认值。 | 当 说明 当前默认值不支持函数,例如 |
table_comment | 否 | 表注释。 | 注释内容为长度不超过1024字节的有效字符串,否则报错。 |
LIFECYCLE | 否 | 表的生命周期。 | 仅支持正整数。单位为天。
|
分区表参数
参数 | 是否必填 | 说明 | 备注 |
PARTITIONED BY | 是 | 指定分区表的分区字段。 | 无 |
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字节的有效字符串,否则报错。 |
分区值不能包含双字节字符(如中文),必须以字母开头,包含字母、数字和允许的字符,长度不超过255字节。允许的字符包括空格、冒号(:)、下划线(_)、美元符号($)、井号(#)、英文句点(.)、感叹号(!)和at(@),其他字符的行为未定义,例如转义字符\t
、\n
和/
。
聚簇表参数
聚簇表分为Hash聚簇表和Range聚簇表两种。
HASH聚簇表
参数 | 是否必填 | 说明 | 备注 |
CLUSTERED BY | 是 | 指定Hash Key。 | MaxCompute将对指定列进行Hash运算,按照Hash值分散到各个Bucket中。为避免数据倾斜和热点,并取得较好的并行执行效果, |
SORTED BY | 是 | 指定Bucket内字段的排序方式。 | 建议SORTED BY和CLUSTERED BY保持一致,以取得较好的性能。此外,当指定SORTED BY子句后,MaxCompute将自动生成索引,并且在查询时利用索引来加快执行。 |
number_of_buckets | 是 | 指定哈希桶的数量。 | 该值必须填写,且由数据量大小决定。此外,MaxCompute默认支持最多1111个Reducer,所以此处最多只支持1111个哈希桶。您可以使用 |
选择哈希桶数目时,请您遵循以下两个原则:
哈希桶大小适中:建议每个Bucket的大小为500 MB左右。例如,分区大小估计为500 GB,粗略估算Bucket数目应该设为1000,这样平均每个Bucket大小约为500 MB。对于特别大的表,500 MB的限制可以突破,每个Bucket在2 GB~3 GB左右比较合适。同时,可以结合
set odps.stage.reducer.num=<并发数>;
来突破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=true/false;
进行设置,默认关闭。
聚簇表的优点:
优化Bucket Pruning。
优化Aggregation。
优化存储。
聚簇表的限制:
不支
INSERT INTO
,只能通过INSERT OVERWRITE
来添加数据。不支持Tunnel直接Upload到Range聚簇表,因为Tunnel上传数据是无序的。
不支持备份恢复功能。
外部表参数
本示例以OSS外部表创建参数为例。更多外部表创建,详情请参见外部数据源。
参数 | 是否必填 | 说明 |
| 是 | 按照外部表数据格式指定file_format。 |
| 否 | 外部表的授权、压缩、字符解析等相关参数。 |
oss_location | 是 | 外部表数据OSS存储位置,详情请参见创建OSS外部表。 |
Transaction Table与Delta Table表参数
Transaction Table表参数
参数 | 是否必填 | 说明 |
TBLPROPERTIES(transactional"="true") | 是 | 设置表为Transactional表。后续可以对Transactional表执行 |
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表互转。
不支持自动合并Transactional表文件,需要手动执行合并操作,详情请参见合并Transactional表文件。
不支持
merge partition
操作。其他系统的作业访问Transactional表有一些限制,例如Graph不支持读写;Spark、PAI只支持读,不支持写。
Delta Table表参数
Delta Table是一种可支持近实时读写,增量存储和读写,实时更新等能力的表格式,目前只支持主键表。
参数 | 是否必填 | 说明 | 备注 |
PRIMARY KEY(PK) | 是 | 创建Delta Table主键表时必填,可包含多列。 | 语法遵循标准SQL primary key语法,PK列必须设置not null,不允许修改。 设置后,后续表数据会根据PK列进行去重,Unique约束在单个partition范围内有效,或非分区表内有效。 |
transactional | 是 | 创建Delta Table类型表时必填,必须设置为true。 | 表示符合MaxCompute的ACID表的事务特性,采用MVCC事务管理模式,保证快照隔离级别。 |
write.bucket.num | 否 | 默认取值为16,取值范围为 | 表示每个partition或者非分区表的分桶数量,也表示数据写入的并发节点数量。分区表支持修改,新分区默认生效,非分区表不支持修改。该参数用法可参考如下建议:
|
acid.data.retain.hours | 否 | 默认取值为24,取值范围为 | 表示TimeTravel可查询数据历史状态的时间范围(单位为小时)。若需要超过168小时(7天)的长时间TimeTravel历史回溯,可联系MaxCompute技术支持。
|
acid.incremental.query.out.of.time.range.enabled | 否 | 默认 | True表示增量查询指定的endTimestamp可大于表最大的数据Commit Time,在endTimestamp大于当前时间场景下,用户多次查询可能得到不同的结果,因为可能有新的数据插入。支持修改表的此参数取值。 |
acid.write.precombine.field | 否 | 可以指定一个列的名称,且只能指定一个。 | 如果指定了列名,在同一提交的文件处理中,系统会结合主键(PK)列对数据进行去重处理,确保数据的唯一性和一致性。 说明 当一次性提交的数据量超过128 M时,会导致生成多个文件,该参数对多个文件不适用。 |
Delta Table其他通用参数的设置要求。
LIFECYCLE: 表生命周期必须大于等于Time Travel可查询的生命周期,即
lifecycle >= acid.data.retain.hours / 24
。创建表时会做检查,不符合会报错。其他不支持的特性:不支持设置CLUSTER BY、EXTERNAL外表和CREATE TABLE AS。
其他使用限制:
当前不支持除MaxCompute SQL外的其他引擎直接操作Delta Table。
存量普通表不支持修改为Delta Table。
不支持对Delta Table主键表的PK列做表结构变更。
基于已有数据表建表参数
通过
CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] AS <select_statement>;
语句可以再创建一个表,并在建表的同时将数据复制到新表中。但通过该语句创建的表不会复制分区属性,只会把源表的分区列作为目标表的一般列处理,也不会复制源表的生命周期属性。
您还可以通过lifecycle参数回收表。同时也支持创建内部表复制外部表的数据。
通过
CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] LIKE <existing_table_name>;
语句可以再创建一个表,使目标表和源表具有相同的表结构。但通过该语句创建的表不复制数据,也不会复制源表的生命周期属性。
您还可以通过lifecycle参数回收表。同时也支持创建内部表复制外部表的结构。
使用示例
创建非分区表。
CREATE TABLE test1 (key STRING);
创建分区表。
CREATE TABLE IF NOT EXISTS sale_detail( shop_name STRING, customer_id STRING, total_price DOUBLE) PARTITIONED BY (sale_date STRING, region STRING);
创建新表,替换原有表。
创建原有表
mytable
,并写入数据。CREATE OR REPLACE TABLE my_table(a bigint); INSERT INTO my_table(a) VALUES (1),(2),(3);
使用OR REPLACE创建新的同名表,并修改字段。
CREATE OR REPLACE TABLE my_table(b string);
查询
my_table
表,返回结果如下。+------------+ | b | +------------+ +------------+
以下为非法的SQL语句:
CREATE OR REPLACE TABLE IF NOT EXISTS my_table(b STRING); CREATE OR REPLACE TABLE my_table AS SELECT; CREATE OR REPLACE TABLE my_table LIKE newtable;
创建新表,将已有表数据复制到新表,并设置生命周期。
-- 创建一个新表sale_detail_ctas1,将sale_detail的数据复制到sale_detail_ctas1中,并设置生命周期。 SET odps.sql.allow.fullscan=true; 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列的非分区表。创建新表,在SELECT语句中使用常量作为列的值。
说明如果在
SELECT
子句中使用常量作为列的值,建议您指定列的名字。创建的表sale_detail_ctas3的第四、五列类似于_c4
、_c5
。指定列的名字。
SET odps.sql.allow.fullscan=true; CREATE TABLE sale_detail_ctas2 AS SELECT shop_name, customer_id, total_price, '2013' AS sale_date, 'China' AS region FROM sale_detail;
不指定列的名字。
SET odps.sql.allow.fullscan=true; CREATE TABLE sale_detail_ctas3 AS SELECT shop_name, customer_id, total_price, '2013', 'China' FROM 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表中。
创建新表,与外部表具有相同的表结构。
-- 创建一个新表mc_oss_extable_orc_like,与外部表mc_oss_extable_orc具有相同的表结构。 CREATE TABLE mc_oss_extable_orc_like LIKE mc_oss_extable_orc;
您可以通过
DESC mc_oss_extable_orc_like;
命令查看表结构等详细信息。+------------------------------------------------------------------------------------+ | Owner: ALIYUN$****@***.aliyunid.com | Project: max_compute_7u************yoq | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2022-08-11 11:10:47 | | LastDDLTime: 2022-08-11 11:10:47 | | LastModifiedTime: 2022-08-11 11:10:47 | +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | id | string | | | | name | string | | | +------------------------------------------------------------------------------------+
创建使用新数据类型的表。
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;
创建Hash聚簇非分区表。
CREATE TABLE t1 (a STRING, b STRING, c BIGINT) CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;
创建Hash聚簇分区表。
CREATE TABLE t2 (a STRING, b STRING, c BIGINT) PARTITIONED BY (dt STRING) CLUSTERED BY (c) SORTED BY (c) into 1024 buckets;
创建Range聚簇非分区表。
CREATE TABLE t3 (a STRING, b STRING, c BIGINT) RANGE CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;
创建Range聚簇分区表。
CREATE TABLE t4 (a STRING, b STRING, c BIGINT) PARTITIONED BY (dt STRING) RANGE CLUSTERED BY (c) SORTED BY (c);
创建Transactional非分区表。
CREATE TABLE t5(id bigint) tblproperties("transactional"="true");
创建Transactional分区表。
CREATE TABLE IF NOT EXISTS t6(id bigint) PARTITIONED BY (ds string) tblproperties ("transactional"="true");
创建非分区表,并为字段指定默认值。
CREATE TABLE test_default( tinyint_name tinyint NOT NULL default 1Y, smallint_name SMALLINT NOT NULL DEFAULT 1S, int_name INT NOT NULL DEFAULT 1, bigint_name BIGINT NOT NULL DEFAULT 1, binary_name BINARY , float_name FLOAT , double_name DOUBLE NOT NULL DEFAULT 0.1, decimal_name DECIMAL(2, 1) NOT NULL DEFAULT 0.0BD, varchar_name VARCHAR(10) , char_name CHAR(2) , string_name STRING NOT NULL DEFAULT 'N', boolean_name BOOLEAN NOT NULL DEFAULT TRUE );
创建内部表复制外部分区表数据,内部表不包含分区属性。
创建OSS外部表和MaxCompute内部表。
-- 创建oss外部表并插入数据。 CREATE EXTERNAL table max_oss_test(a int, b int, c int) stored AS TEXTFILE location "oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket_name>"; INSERT INTO max_oss_test VALUES (101, 1, 20241108), (102, 2, 20241109), (103, 3, 20241110); SELECT * FROM max_oss_test; -- 结果 a b c 101 1 20241108 102 2 20241109 103 3 20241110 -- 使用create table as创建内部表 CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test; -- 查询新建内部表 SELECT * FROM from_exetbl_oss; -- 返回结果,全表数据都有 a b c 101 1 20241108 102 2 20241109 103 3 20241110
执行
DESC from_exetbl_as_par;
查询内部表结构。返回结果如下。+------------------------------------------------------------------------------------+ | Owner: ALIYUN$*********** | | Project: ***_*****_*** | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2023-01-10 15:16:33 | | LastDDLTime: 2023-01-10 15:16:33 | | LastModifiedTime: 2023-01-10 15:16:33 | +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 919 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | a | string | | | | b | string | | | | c | string | | | +------------------------------------------------------------------------------------+
创建内部表复制外部分区表数据,内部表包含分区属性。
创建内部表
from_exetbl_like
。-- MaxCompute侧查询湖仓一体外部表 SELECT * FROM max_oss_test; -- 返回结果 a b c 101 1 20241108 102 2 20241109 103 3 20241110 -- 使用create table like创建内部表 CREATE TABLE from_exetbl_like LIKE max_oss_test; -- 查询新建内部表 SELECT * FROM from_exetbl_like; -- 返回结果,只有表结构出现 a b c
执行
DESC from_exetbl_like;
查询内部表结构。返回结果如下。+------------------------------------------------------------------------------------+ | Owner: ALIYUN$************ | | Project: ***_*****_*** | | TableComment: | +------------------------------------------------------------------------------------+ | CreateTime: 2023-01-10 15:09:47 | | LastDDLTime: 2023-01-10 15:09:47 | | LastModifiedTime: 2023-01-10 15:09:47 | +------------------------------------------------------------------------------------+ | InternalTable: YES | Size: 0 | +------------------------------------------------------------------------------------+ | Native Columns: | +------------------------------------------------------------------------------------+ | Field | Type | Label | Comment | +------------------------------------------------------------------------------------+ | a | string | | | | b | string | | | +------------------------------------------------------------------------------------+ | Partition Columns: | +------------------------------------------------------------------------------------+ | c | string | | +------------------------------------------------------------------------------------+
创建Delta Table表。
CREATE TABLE mf_tt (pk bigint NOT NULL PRIMARY KEY, val bigint) tblproperties ("transactional"="true");
创建Delta Table表,设置主要表属性。
CREATE TABLE mf_tt2 ( pk bigint NOT NULL, pk2 bigint NOT NULL, val bigint, val2 bigint, PRIMARY KEY (pk, pk2) ) tblproperties ( "transactional"="true", "write.bucket.num" = "64", "acid.data.retain.hours"="120" ) lifecycle 7;
删除表
删除非分区表或分区表。
注意事项
请谨慎操作,确认表可以删除后,再执行删除操作。如果误删了表,当项目开启了备份恢复功能,且删除操作未超过项目设置的备份数据保留天数时,则可以恢复表。更多备份恢复信息,请参见备份与恢复。
删除表之后,MaxCompute项目的存储量会降低。
命令格式
DROP TABLE [IF EXISTS] <table_name>;
参数说明
参数 | 是否必填 | 说明 |
IF EXISTS | 否 | 如果不指定IF EXISTS且表不存在,则返回异常。如果指定IF EXISTS,无论表是否存在,均返回成功。 |
table_name | 是 | 待删除的表名。 |
使用示例
--删除表sale_detail。无论sale_detail表是否存在,均返回成功。
DROP TABLE IF EXISTS sale_detail;
相关文档
复制表数据至另一张表中,详情请参见CLONE TABLE。
修改和查看表信息,详情请参见修改表。
对已有表分区执行操作,详情请参见分区操作。
对已有表的列执行操作,详情请参见列操作。