网站服务器建设费用,百度地图导航网页版,网页浏览设置在哪里打开,wordpress视频多集播放–odps sql –– –author:宋文理 –create time: –– 创建表 创建非分区表、分区表、外部表或聚簇表。
限制条件 分区表的分区层级不能超过6级。例如某张表以日期为分区列#xff0c;分区层级为年/月/周/日/时/分。 一张表允许的分区个数支持按照具体的项目配置#xff0c…–odps sql –– –author:宋文理 –create time: –– 创建表 创建非分区表、分区表、外部表或聚簇表。
限制条件 分区表的分区层级不能超过6级。例如某张表以日期为分区列分区层级为年/月/周/日/时/分。 一张表允许的分区个数支持按照具体的项目配置默认为6万个。 更多表的限制条件请参见SQL使用限制项。
命令格式如下 –创建新表。 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], …)] –用于创建聚簇表时设置表的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 ] –指定表为Transactional表后续可以对该表执行更新或删除表数据操作但是Transactional表有部分使用限制请根据需求创建。 [tblproperties(“transactional”“true”)] [lifecycle ];
–基于已存在的表创建新表并复制数据但不复制分区属性。支持外部表和湖仓一体外部项目中的表。 create table [if not exists] table_name [lifecycle ] as select_statement;
–基于已存在的表创建具备相同结构的新表但不复制数据支持外部表和湖仓一体外部项目中的表。 create table [if not exists] table_name like existing_table_name [lifecycle ]; 参数说明 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分区列的数据类型。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属性。
– 示例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; – 此处sale_detail是一张分区表而通过create table … as select_statement …语句创建的表sale_detail_ctas1不会复制分区属性 – 只会把源表的分区列作为目标表的一般列处理。即sale_detail_ctas1是一个含有5列的非分区表。
– 想要从原表查询后建立分区表,应该使用clone table方法
示例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创建一个新表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 | | | ±----------------------------------------------------------------------------------- – 示例7创建使用新数据类型的表test_newtype。 set odps.sql.type.system.odps2true; CREATE TABLE test_newtype ( c1 TINYINT ,c2 SMALLINT ,c3 INT ,c4 BIGINT ,c5 FLOAT ,c6 DOUBLE ,c7 DECIMAL ,c8 BINARY ,c9 TIMESTAMP ,c10 ARRAYMAPBIGINT,BIGINT ,c11 MAPSTRING,ARRAY ,c12 STRUCTs1:STRING,s2:BIGINT ,c13 VARCHAR(20)) LIFECYCLE 1 ; – 示例8创建Hash聚簇非分区表t1。 create table t1 (a STRING, b STRING, c BIGINT) clustered by © sorted by © into 1024 buckets; – 示例9创建Hash聚簇分区表t2。 create table t2 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) clustered by © sorted by © into 1024 buckets; – 示例10创建Range聚簇非分区表t3。 create table t3 (a STRING, b STRING, c BIGINT) range clustered by © sorted by © into 1024 buckets; – 示例11创建Range聚簇分区表t4。 create table t4 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) range clustered by © sorted by ©; – 示例12创建Transactional非分区表t5。 create table t5(id bigint) tblproperties(“transactional”“true”); – 示例13创建Transactional分区表t6。 create table if not exists t6(id bigint) partitioned by(ds string) tblproperties (“transactional”“true”); – 示例14创建非分区表test_default并为字段指定默认值。 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 ); – 示例15使用create table [if not exists] table_name [lifecycle ] as select_statement;语句创建内部表复制外部分区表数据内部表不包含分区属性。 – MaxCompute侧查询湖仓一体外部表 select * from hive_external2_1.myhive_0110; – 结果 a b c 101 1 20230110 102 2 20230110 103 3 20230110
– 使用create table as创建内部表 create table from_exetbl_as_par as select * from hive_external2_1.myhive_0110_par;
– 查询新建内部表 select * from from_exetbl_as_par; – 返回结果全表数据都有 a b c 101 1 20230110 102 2 20230110 103 3 20230110
– 查询新建内部表结构 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 | | | – ±----------------------------------------------------------------------------------- – 示例16使用create table [if not exists] table_name like existing_table_name [lifecycle ];语句创建内部表复制外部分区表结构内部表包含分区属性。 – MaxCompute侧查询湖仓一体外部表 select * from hive_external2_1.myhive_0110_par; – 返回结果 a b c 101 1 20230110 102 2 20230110 103 3 20230110
– 使用create table like创建内部表 create table from_exetbl_like like hive_external2_1.myhive_0110_par;
– 查询新建内部表 select * from from_exetbl_like_par; – 返回结果只有表结构出现 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 | | ±----------------------------------------------------------------------------------- 修改表的所有人 修改表的所有人即表Owner。
命令格式 alter table table_name changeowner to new_owner; 参数说明 table_name必填。待修改Owner的表名。 new_owner必填。修改后的Owner账号。 使用示例 –将表test1的所有人修改为ALIYUNxxxaliyun.com。altertabletest1changeownerto′ALIYUNxxxaliyun.com。 alter table test1 changeowner to ALIYUNxxxaliyun.com。altertabletest1changeownerto′ALIYUNxxxaliyun.com’; 修改表的注释 修改表的注释内容。
命令格式 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操作用来修改表的LastModifiedTime可将表的LastModifiedTime修改为当前时间。此操作会改变表的LastModifiedTime的值 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 rename to new_table_name; 参数说明 table_name必填。待修改名称的表。 new_table_name必填。修改后的表名称。如果已存在与new_table_name同名的表会返回报错。 使用示例 alter table sale_detail rename to sale_detail_rename; 清空非分区表里的数据 将指定的非分区表中的数据清空。如果您需要清空分区表中单个或多个分区的数据请参见清空分区数据。
命令格式 truncate table table_name; 参数说明 table_name必填。待清空数据的非分区表的名称。
删除表 删除非分区表或分区表。
注意事项 请谨慎操作确认表可以删除后再执行删除操作。如果误删了表当项目开启了备份恢复功能且删除操作未超过项目设置的备份数据保留天数时则可以恢复表。更多备份恢复信息请参见备份与恢复。 删除表之后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; 查看表或视图信息 查看MaxCompute内部表、视图、外部表、聚簇表或Transactional表的信息。如果您需要查看表的详细数据信息请参见SELECT语法。
命令格式 –查看表或视图信息。 desc table_name|view_name [partition (pt_spec)]; –查看外部表、聚簇表或Transactional表信息。也可以查看内部表的扩展信息。 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; 返回结果如下。 ±----------------------------------------------------------------------------------- | Owner: ALIYUN$maoXXXalibaba-inc.com | Project: projectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2020−11−1617:47:48∣∣LastDDLTime:2020−11−1617:47:48∣∣LastModifiedTime:2020−11−1617:47:48∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣key∣string∣∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例2查看创建的saledetail表的信息。descsaledetail;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNproject_name | | TableComment: | ------------------------------------------------------------------------------------ | CreateTime: 2020-11-16 17:47:48 | | LastDDLTime: 2020-11-16 17:47:48 | | LastModifiedTime: 2020-11-16 17:47:48 | ------------------------------------------------------------------------------------ | InternalTable: YES | Size: 0 | ------------------------------------------------------------------------------------ | Native Columns: | ------------------------------------------------------------------------------------ | Field | Type | Label | Comment | ------------------------------------------------------------------------------------ | key | string | | | ------------------------------------------------------------------------------------ 示例2查看创建的sale_detail表的信息。 desc sale_detail; 返回结果如下。 -------------------------------------------------------------------- | Owner: ALIYUNprojectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2020−11−1617:47:48∣∣LastDDLTime:2020−11−1617:47:48∣∣LastModifiedTime:2020−11−1617:47:48∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣key∣string∣∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例2查看创建的saledetail表的信息。descsaledetail;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNmaoXXXalibaba-inc.com | Project: projectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2017−06−2815:05:17∣∣LastDDLTime:2017−06−2815:05:17∣∣LastModifiedTime:2017−06−2815:05:17∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣shopname∣string∣∣∣∣customerid∣string∣∣∣∣totalprice∣double∣∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣PartitionColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣saledate∣string∣∣∣region∣string∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例3查看创建的saledetailctas1表的详细信息。descextendedsaledetailctas1;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNproject_name | | 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 | | -------------------------------------------------------------------- 示例3查看创建的sale_detail_ctas1表的详细信息。 desc extended sale_detail_ctas1; 返回结果如下。 ------------------------------------------------------------------------------------ | Owner: ALIYUNprojectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2017−06−2815:05:17∣∣LastDDLTime:2017−06−2815:05:17∣∣LastModifiedTime:2017−06−2815:05:17∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣shopname∣string∣∣∣∣customerid∣string∣∣∣∣totalprice∣double∣∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣PartitionColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣saledate∣string∣∣∣region∣string∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例3查看创建的saledetailctas1表的详细信息。descextendedsaledetailctas1;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNmaoXXXalibaba-inc.com | Project: projectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2021−07−0715:29:53∣∣LastDDLTime:2021−07−0715:29:53∣∣LastModifiedTime:2021−07−0715:29:53∣∣Lifecycle:10∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣ExtendedLabel∣Nullable∣DefaultValue∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣shopname∣string∣∣∣true∣NULL∣∣∣customerid∣string∣∣∣true∣NULL∣∣∣totalprice∣double∣∣∣true∣NULL∣∣∣saledate∣string∣∣∣true∣NULL∣∣∣region∣string∣∣∣true∣NULL∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣ExtendedInfo:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣TableID:98cb8a38733c49eabed4735173818147∣∣IsArchived:false∣∣PhysicalSize:0∣∣FileNum:0∣∣StoredAs:AliOrc∣∣CompressionStrategy:normal∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−saledate和region两个字段仅会作为普通列存在而不是表的分区。示例4查看创建的saledetailctas2表的信息。descsaledetailctas2;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNproject_name | | TableComment: | ------------------------------------------------------------------------------------ | CreateTime: 2021-07-07 15:29:53 | | LastDDLTime: 2021-07-07 15:29:53 | | LastModifiedTime: 2021-07-07 15:29:53 | | Lifecycle: 10 | ------------------------------------------------------------------------------------ | InternalTable: YES | Size: 0 | ------------------------------------------------------------------------------------ | Native Columns: | ------------------------------------------------------------------------------------ | Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment | ------------------------------------------------------------------------------------ | shop_name | string | | | true | NULL | | | customer_id | string | | | true | NULL | | | total_price | double | | | true | NULL | | | sale_date | string | | | true | NULL | | | region | string | | | true | NULL | | ------------------------------------------------------------------------------------ | Extended Info: | ------------------------------------------------------------------------------------ | TableID: 98cb8a38733c49eabed4735173818147 | | IsArchived: false | | PhysicalSize: 0 | | FileNum: 0 | | StoredAs: AliOrc | | CompressionStrategy: normal | ------------------------------------------------------------------------------------ sale_date和region两个字段仅会作为普通列存在而不是表的分区。 示例4查看创建的sale_detail_ctas2表的信息。 desc sale_detail_ctas2; 返回结果如下。 -------------------------------------------------------------------- | Owner: ALIYUNprojectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2021−07−0715:29:53∣∣LastDDLTime:2021−07−0715:29:53∣∣LastModifiedTime:2021−07−0715:29:53∣∣Lifecycle:10∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣ExtendedLabel∣Nullable∣DefaultValue∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣shopname∣string∣∣∣true∣NULL∣∣∣customerid∣string∣∣∣true∣NULL∣∣∣totalprice∣double∣∣∣true∣NULL∣∣∣saledate∣string∣∣∣true∣NULL∣∣∣region∣string∣∣∣true∣NULL∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣ExtendedInfo:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣TableID:98cb8a38733c49eabed4735173818147∣∣IsArchived:false∣∣PhysicalSize:0∣∣FileNum:0∣∣StoredAs:AliOrc∣∣CompressionStrategy:normal∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−saledate和region两个字段仅会作为普通列存在而不是表的分区。示例4查看创建的saledetailctas2表的信息。descsaledetailctas2;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNxxxxxalibaba-inc.com | Project: projectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2017−06−2815:42:17∣∣LastDDLTime:2017−06−2815:42:17∣∣LastModifiedTime:2017−06−2815:42:17∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣shopname∣string∣∣∣∣customerid∣string∣∣∣∣totalprice∣double∣∣∣∣saledate∣string∣∣∣∣region∣string∣∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例5查看创建的saledetaillike表的详细信息。descextendedsaledetaillike;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNproject_name | | 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 | | | -------------------------------------------------------------------- 示例5查看创建的sale_detail_like表的详细信息。 desc extended sale_detail_like; 返回结果如下。 ------------------------------------------------------------------------------------ | Owner: ALIYUNprojectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2017−06−2815:42:17∣∣LastDDLTime:2017−06−2815:42:17∣∣LastModifiedTime:2017−06−2815:42:17∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣shopname∣string∣∣∣∣customerid∣string∣∣∣∣totalprice∣double∣∣∣∣saledate∣string∣∣∣∣region∣string∣∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例5查看创建的saledetaillike表的详细信息。descextendedsaledetaillike;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNxxxxxalibaba-inc.com | Project: $project_name | | TableComment: | ±----------------------------------------------------------------------------------- | CreateTime: 2021-07-07 15:40:38 | | LastDDLTime: 2021-07-07 15:40:38 | | LastModifiedTime: 2021-07-07 15:40:38 | | Lifecycle: 10 | ±----------------------------------------------------------------------------------- | InternalTable: YES | Size: 0 | ±----------------------------------------------------------------------------------- | Native Columns: | ±----------------------------------------------------------------------------------- | Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment | ±----------------------------------------------------------------------------------- | shop_name | string | | | true | NULL | | | customer_id | string | | | true | NULL | | | total_price | double | | | true | NULL | | ±----------------------------------------------------------------------------------- | Partition Columns: | ±----------------------------------------------------------------------------------- | sale_date | string | | | region | string | | ±----------------------------------------------------------------------------------- | Extended Info: | ±----------------------------------------------------------------------------------- | TableID: 61782ff7713f426e9d6f91d5deeac99a | | IsArchived: false | | PhysicalSize: 0 | | FileNum: 0 | | StoredAs: AliOrc | | CompressionStrategy: normal | ±----------------------------------------------------------------------------------- 除生命周期属性外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; 返回结果如下。 | 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 | arraymapbigint,bigint | | | | c11 | mapstring,array | | | | c12 | structs1:string,s2:bigint | | | | c13 | varchar(20) | | | ±-----------------------------------------------------------------------------------
OK 示例7查看创建的Hash聚簇非分区表t1的信息。聚簇属性将显示在Extended Info中。 desc extended t1; 返回结果如下。 ±----------------------------------------------------------------------------------- | Owner: ALIYUN$xxxxxalibaba-inc.com | Project: $project_name | | TableComment: | ±----------------------------------------------------------------------------------- | CreateTime: 2020-11-16 18:00:56 | | LastDDLTime: 2020-11-16 18:00:56 | | LastModifiedTime: 2020-11-16 18:00:56 | ±----------------------------------------------------------------------------------- | InternalTable: YES | Size: 0 | ±----------------------------------------------------------------------------------- | Native Columns: | ±----------------------------------------------------------------------------------- | Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment | ±----------------------------------------------------------------------------------- | a | string | | | true | NULL | | | b | string | | | true | NULL | | | c | bigint | | | true | NULL | | ±----------------------------------------------------------------------------------- | Extended Info: | ±----------------------------------------------------------------------------------- | TableID: e6b06f705dc34a36a5b72e5af486cab7 | | IsArchived: false | | PhysicalSize: 0 | | FileNum: 0 | | StoredAs: AliOrc | | CompressionStrategy: normal | | ClusterType: hash | | BucketNum: 1024 | | ClusterColumns: [c] | | SortColumns: [c ASC] | ±-----------------------------------------------------------------------------------
OK 示例8查看创建的Hash聚簇分区表t2的信息。聚簇属性将显示在Extended Info中。 desc extended t2; 返回结果如下。 ±----------------------------------------------------------------------------------- | Owner: ALIYUN$xxxxxalibaba-inc.com | Project: $project_name | | 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] | ±-----------------------------------------------------------------------------------
OK 示例9查看Range聚簇非分区表t3的信息。聚簇属性将显示在Extended Info中。 desc extended t3; 返回结果如下。 ±----------------------------------------------------------------------------------- | Owner: ALIYUN$xxxxxalibaba-inc.com | Project: $project_name | | TableComment: | ±----------------------------------------------------------------------------------- | CreateTime: 2020-11-16 18:01:05 | | LastDDLTime: 2020-11-16 18:01:05 | | LastModifiedTime: 2020-11-16 18:01:05 | ±----------------------------------------------------------------------------------- | InternalTable: YES | Size: 0 | ±----------------------------------------------------------------------------------- | Native Columns: | ±----------------------------------------------------------------------------------- | Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment | ±----------------------------------------------------------------------------------- | a | string | | | true | NULL | | | b | string | | | true | NULL | | | c | bigint | | | true | NULL | | ±----------------------------------------------------------------------------------- | Extended Info: | ±----------------------------------------------------------------------------------- | TableID: 38d170aca2684f4baadbbe1931a6ae1f | | IsArchived: false | | PhysicalSize: 0 | | FileNum: 0 | | StoredAs: AliOrc | | CompressionStrategy: normal | | ClusterType: range | | BucketNum: 1024 | | ClusterColumns: [c] | | SortColumns: [c ASC] | ±-----------------------------------------------------------------------------------
OK 示例10查看Range聚簇分区表t4的信息。聚簇属性将显示在Extended Info中。 desc extended t4; 返回结果如下。 ±----------------------------------------------------------------------------------- | Owner: ALIYUN$xxxxxalibaba-inc.com | Project: $project_name | | TableComment: | ±----------------------------------------------------------------------------------- | CreateTime: 2020-11-16 19:17:48 | | LastDDLTime: 2020-11-16 19:17:48 | | LastModifiedTime: 2020-11-16 19:17:48 | ±----------------------------------------------------------------------------------- | InternalTable: YES | Size: 0 | ±----------------------------------------------------------------------------------- | Native Columns: | ±----------------------------------------------------------------------------------- | Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment | ±----------------------------------------------------------------------------------- | a | string | | | true | NULL | | | b | string | | | true | NULL | | | c | bigint | | | true | NULL | | ±----------------------------------------------------------------------------------- | Partition Columns: | ±----------------------------------------------------------------------------------- | dt | string | | ±----------------------------------------------------------------------------------- | Extended Info: | ±----------------------------------------------------------------------------------- | TableID: 6ebc3432e283449188c861427bcd6ee4 | | IsArchived: false | | PhysicalSize: 0 | | FileNum: 0 | | StoredAs: AliOrc | | CompressionStrategy: normal | | ClusterType: range | | BucketNum: 0 | | ClusterColumns: [c] | | SortColumns: [c ASC] | ±-----------------------------------------------------------------------------------
OK 示例11查看非分区表t5是否为Transactional表。 说明 推荐您使用 MaxCompute客户端查看表是否为Transactional表需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题 导致查询结果不显示Transactional信息。 desc extended t5; 返回结果如下。 ±----------------------------------------------------------------------------------- | Owner: ALIYUN$xxxxxaliyun.com | Project: projectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2021−02−1810:56:27∣∣LastDDLTime:2021−02−1810:56:27∣∣LastModifiedTime:2021−02−1810:56:27∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣ExtendedLabel∣Nullable∣DefaultValue∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣id∣bigint∣∣∣true∣NULL∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣ExtendedInfo:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−...∣Transactional:true∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例12查看分区表t6是否为Transactional表。说明推荐您使用MaxCompute客户端查看表是否为Transactional表需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题导致查询结果不显示Transactional信息。descextendedt6;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNproject_name | | TableComment: | ------------------------------------------------------------------------------------ | CreateTime: 2021-02-18 10:56:27 | | LastDDLTime: 2021-02-18 10:56:27 | | LastModifiedTime: 2021-02-18 10:56:27 | ------------------------------------------------------------------------------------ | InternalTable: YES | Size: 0 | ------------------------------------------------------------------------------------ | Native Columns: | ------------------------------------------------------------------------------------ | Field | Type | Label | ExtendedLabel | Nullable | DefaultValue | Comment | ------------------------------------------------------------------------------------ | id | bigint | | | true | NULL | | ------------------------------------------------------------------------------------ | Extended Info: | ------------------------------------------------------------------------------------ ... | Transactional: true | ------------------------------------------------------------------------------------ 示例12查看分区表t6是否为Transactional表。 说明 推荐您使用 MaxCompute客户端查看表是否为Transactional表需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题 导致查询结果不显示Transactional信息。 desc extended t6; 返回结果如下。 ------------------------------------------------------------------------------------ | Owner: ALIYUNprojectname∣∣TableComment:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣CreateTime:2021−02−1810:56:27∣∣LastDDLTime:2021−02−1810:56:27∣∣LastModifiedTime:2021−02−1810:56:27∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣InternalTable:YES∣Size:0∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣NativeColumns:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Field∣Type∣Label∣ExtendedLabel∣Nullable∣DefaultValue∣Comment∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣id∣bigint∣∣∣true∣NULL∣∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣ExtendedInfo:∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−...∣Transactional:true∣−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−示例12查看分区表t6是否为Transactional表。说明推荐您使用MaxCompute客户端查看表是否为Transactional表需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题导致查询结果不显示Transactional信息。descextendedt6;返回结果如下。−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−∣Owner:ALIYUNxxxxxtest.aliyunid.com | Project: $project_name | | TableComment: | ±----------------------------------------------------------------------------------- | CreateTime: 2021-02-18 15:34:54 | | LastDDLTime: 2021-02-18 15:34:54 | | LastModifiedTime: 2021-02-18 15:34:54 | ±----------------------------------------------------------------------------------- | InternalTable: YES | Size: 0 | ±----------------------------------------------------------------------------------- | Native Columns: | ±----------------------------------------------------------------------------------- | Field | Type | Label | Comment | ±----------------------------------------------------------------------------------- | id | bigint | | | ±----------------------------------------------------------------------------------- | Partition Columns: | ±----------------------------------------------------------------------------------- | ds | string | | ±----------------------------------------------------------------------------------- | Extended Info: | ±----------------------------------------------------------------------------------- … | Transactional: true | ±----------------------------------------------------------------------------------- 查看分区信息 查看某个分区表具体的分区的信息。
命令格式 desc table_name partition (pt_spec); 参数说明 table_name必填。待查看分区信息的分区表名称。 pt_spec必填。待查看的分区信息。格式为partition_col1col1_value1, partition_col2col2_value1…。对于有多级分区的表必须指明全部的分区值。 使用示例 –查询分区表sale_detail的分区信息。 desc sale_detail partition (sale_date‘201310’,region‘beijing’); 返回结果如下。 ±----------------------------------------------------------------------------------- | PartitionSize: 2109112 | ±----------------------------------------------------------------------------------- | CreateTime: 2015-10-10 08:48:48 | | LastDDLTime: 2015-10-10 08:48:48 | | LastModifiedTime: 2015-10-11 01:33:35 | ±----------------------------------------------------------------------------------- OK 查看建表语句 生成创建表的SQL DDL语句方便您通过SQL重建Schema。
命令格式 show create table table_name; 参数说明 table_name必填。待查看建表语句的表的名称。
使用示例 –查看表sale_detail的建表语句。 show create table sale_detail; 返回结果如下。 CREATE TABLE IF NOT EXISTS doc_test_dev.sale_detail(shop_name STRING,customer_id STRING,total_price DOUBLE) PARTITIONED BY (sale_date STRING,region STRING) STORED AS ALIORC; 列出项目下的表和视图 列出项目下所有的表和视图或符合某规则的表和视图。
命令格式 –列出项目下所有的表和视图。 show tables; –列出项目下表名或视图名与chart匹配的表。 show tables like ‘’; 使用示例 –列出项目下表名与sale*匹配的表。表示任意字段。 show tables like sale’; 返回结果如下。 ALIYUN$account_name:sale_detail … –ALIYUN是系统提示符表示您是阿里云主账号用户。如果您是阿里云RAM用户系统提示符为RAM。 列出所有分区 列出一张表中的所有分区表不存在或为非分区表时返回报错。
命令格式 show partitions table_name; 参数说明 table_name必填。待查看分区信息的分区表名称。
使用示例 –列出sale_detail中的所有分区。 show partitions sale_detail; 返回结果如下。 sale_date201310/regionbeijing sale_date201312/regionshenzhen sale_date201312/regionxian sale_date2014/regionshenzhen
OK
