阿里云dataworks表操作_dataworks删除表

作者：从前慢现在也慢 | 2024-08-22 01:04:48

踩

dataworks删除表

–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：必填。表名。表名大小写不敏感，不能有特殊字符，只能包含a_z、AZ、数字和下划线（）。
建议以字母开头，名称的长度不超过128字节，否则报错。
col_name：可选。表的列名。列名大小写不敏感，不能有特殊字符，只能包含a_z、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：表的分区列名。列名大小写不敏感，不能有特殊字符，只能包含a_z、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.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>
,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

命令格式
alter table <table_name> changeowner to <new_owner>;
参数说明
table_name：必填。待修改Owner的表名。
new_owner：必填。修改后的Owner账号。
使用示例
–将表test1的所有人修改为ALIYUN $xxx @ a l i y u n . co m 。 a lt er t ab l e t es t 1 c han g eo w n er t o^{'} A L I Y U N$ xxx@aliyun.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语法。

OK
示例11：查看非分区表t5是否为Transactional表。
说明推荐您使用 MaxCompute客户端查看表是否为Transactional表，需要将MaxCompute客户端升级到0.35.4版本。其他工具可能会存在版本升级未就绪的问题，
导致查询结果不显示Transactional信息。
desc extended t5;
返回结果如下。
±-----------------------------------------------------------------------------------+
| Owner: ALIYUN$xxxxx@aliyun.com | Project: $project_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: ALIYUN$ xxxxx@test.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_col1=col1_value1, partition_col2=col2_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_date=201310/region=beijing
sale_date=201312/region=shenzhen
sale_date=201312/region=xian
sale_date=2014/region=shenzhen

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/1014069