ALTER TABLE
此命令更改 Amazon Redshift 表或 Amazon Redshift Spectrum 外部表的定义。此命令更新 CREATE TABLE 或 CREATE EXTERNAL TABLE 设置的值和属性。
您不能在以下事务块内的外部表上运行 ALTER TABLE (BEGIN ... END)。有关事务的更多信息,请参阅 可序列化的隔离。
除非文档中明确规定可以在表更改时查询表或执行其他操作,否则 ALTER TABLE 会锁定表的读写操作,直到包含 ALTER TABLE 操作的事务完成。
所需的权限
修改表的用户需要适当的权限才能成功执行命令。根据具体的 ALTER TABLE 命令,需要以下权限之一。
Superuser
具有 ALTER TABLE 权限的用户
对模式拥有 USAGE 权限的表拥有者
语法
ALTER TABLE table_name { ADD table_constraint | DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ] | OWNER TO new_owner | RENAME TO new_name | RENAME COLUMN column_name TO new_name | ALTER COLUMN column_name TYPE updated_varchar_data_type_size | ALTER COLUMN column_name ENCODE new_encode_type | ALTER COLUMN column_name ENCODE encode_type, | ALTER COLUMN column_name ENCODE encode_type, .....; | ALTER DISTKEY column_name | ALTER DISTSTYLE ALL | ALTER DISTSTYLE EVEN | ALTER DISTSTYLE KEY DISTKEY column_name | ALTER DISTSTYLE AUTO | ALTER [COMPOUND] SORTKEY ( column_name [,...] ) | ALTER SORTKEY AUTO | ALTER SORTKEY NONE | ALTER ENCODE AUTO | ADD [ COLUMN ] column_name column_type [ DEFAULT default_expr ] [ ENCODE encoding ] [ NOT NULL | NULL ] [ COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ] | | DROP [ COLUMN ] column_name [ RESTRICT | CASCADE ] | ROW LEVEL SECURITY { ON | OFF } [ CONJUNCTION TYPE { AND | OR } ] [ FOR DATASHARES ]} where table_constraint is: [ CONSTRAINT constraint_name ] { UNIQUE ( column_name [, ... ] ) | PRIMARY KEY ( column_name [, ... ] ) | FOREIGN KEY (column_name [, ... ] ) REFERENCES reftable [ ( refcolumn ) ]} The following options apply only to external tables: SET LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' } | SET FILE FORMAT format | | SET TABLE PROPERTIES ('property_name'='property_value') | PARTITION ( partition_column=partition_value [, ...] ) SET LOCATION { 's3://bucket/folder' |'s3://bucket/manifest_file' } | ADD [IF NOT EXISTS] PARTITION ( partition_column=partition_value [, ...] ) LOCATION { 's3://bucket/folder' |'s3://bucket/manifest_file' } [, ... ] | DROP PARTITION ( partition_column=partition_value [, ...] )
要减少运行 ALTER TABLE 命令的时间,可以结合使用 ALTER TABLE 命令的一些子句。
Amazon Redshift 支持以下 ALTER TABLE 子句组合:
ALTER TABLE tablename ALTER SORTKEY (column_list), ALTER DISTKEY column_Id; ALTER TABLE tablename ALTER DISTKEY column_Id, ALTER SORTKEY (column_list); ALTER TABLE tablename ALTER SORTKEY (column_list), ALTER DISTSTYLE ALL; ALTER TABLE tablename ALTER DISTSTYLE ALL, ALTER SORTKEY (column_list);
参数
- table_name
-
要修改的表的名称。只指定表的名称,或者通过格式 schema_name.table_name 使用特定 schema。外部表必须通过一个外部 schema 名称进行限定。如果您使用 ALTER TABLE 语句重命名视图或更改其拥有者,您还可以指定视图名称。表名称的最大长度为 127 个字节;更长的名称将被截断为 127 个字节。您可以使用 UTF-8 多字节字符,每个字符最多为四个字节。有关有效名称的更多信息,请参阅名称和标识符。
- ADD table_constraint
-
用于将指定约束添加到表的子句。有关有效 table_constraint 值的描述,请参阅 CREATE TABLE。
注意
您不能将主键约束添加到可为空的列。如果列最初是使用 NOT NULL 约束创建的,您可以添加主键约束。
- DROP CONSTRAINT constraint_name
-
用于从表中删除指定约束的子句。要删除约束,请指定约束名称而不是约束类型。要查看表约束名称,请运行以下查询。
select constraint_name, constraint_type from information_schema.table_constraints;
- RESTRICT
-
用于仅删除指定约束的子句。RESTRICT 是 DROP CONSTRAINT 的一个选项。RESTRICT 不能与 CASCADE 一起使用。
- CASCADE
-
用于删除指定约束和依赖于该约束的任何内容的子句。CASCADE 是 DROP CONSTRAINT 的选项。CASCADE 不能与 RESTRICT 一起使用。
- OWNER TO new_owner
-
用于将表(或视图)的所有者更改为 new_owner 值的子句。
- RENAME TO new_name
-
用于将表(或视图)重命名为 new_name 中指定的值的子句。表名称的最大长度为 127 个字节;更长的名称将被截断为 127 个字节。
您不能将永久表重命名为以“#”开头的表。名称以“#”开头的表是临时表。
您无法重命名外部表。
- ALTER COLUMN column_name TYPE updated_varchar_data_type_size
-
这是一个更改定义为 VARCHAR 数据类型的列大小的子句。此子句仅支持修改 VARCHAR 数据类型的大小。请考虑以下限制:
-
您无法修改具有 BYTEDICT、RUNLENGTH、TEXT255 或 TEXT32K 压缩编码的列。
-
您无法将大小减少到小于现有数据的最大大小。
-
您无法更改具有默认值的列。
-
您无法更改具有 UNIQUE、PRIMARY KEY 或 FOREIGN KEY 的列。
-
您不能更改以下事务块中的列:(BEGIN ... END)。有关事务的更多信息,请参阅 可序列化的隔离。
-
- ALTER COLUMN column_name ENCODE new_encode_type
-
更改列的压缩编码的子句。如果为列指定压缩编码,则表不再设置为 ENCODE AUTO。有关压缩编码的信息,请参阅使用列压缩。
在更改列的压缩编码时,表仍可供查询。
请考虑以下限制:
-
您不能将列更改为与当前为该列定义的相同的编码。
-
不能使用交错排序键更改表中列的编码。
-
- ALTER COLUMN column_name ENCODE encode_type, ALTER COLUMN column_name ENCODE encode_type, .....;
-
更改单个命令中多个列的压缩编码的子句。有关压缩编码的信息,请参阅使用列压缩。
在更改列的压缩编码时,表仍可供查询。
请考虑以下限制:
您不能在单个命令中多次将列更改为相同或不同的编码类型。
您不能将列更改为与当前为该列定义的相同的编码。
-
不能使用交错排序键更改表中列的编码。
- ALTER DISTSTYLE ALL
-
用于将表的现有分配样式更改为
ALL
的子句。请考虑以下事项:-
ALTER DISTSTYLE、ALTER SORTKEY 和 VACUUM 不能同时对同一个表运行。
-
如果 VACUUM 当前正在运行,则运行 ALTER DISTSTYLE ALL 将返回错误。
-
如果 ALTER DISTSTYLE ALL 正在运行,则不在表上启动后台 vacuum。
-
-
对于具有交错排序键和临时表的表,不支持 ALTER DISTSTYLE ALL 命令。
如果分配方式以前被定义为 AUTO,则表不再是自动表优化的候选项。
有关 DISTSTYLE ALL 的更多信息,请参阅 CREATE TABLE。
-
- ALTER DISTSTYLE EVEN
-
用于将表的现有分配样式更改为
EVEN
的子句。请考虑以下事项:-
ALTER DISTSYTLE、ALTER SORTKEY 和 VACUUM 不能同时对同一个表运行。
-
如果 VACUUM 当前正在运行,则运行 ALTER DISTSTYLE EVEN 将返回错误。
-
如果 ALTER DISTSTYLE EVEN 正在运行,则不在表上启动后台 Vacuum。
-
对于具有交错排序键和临时表的表,不支持 ALTER DISTSTYLE EVEN 命令。
如果分配方式以前被定义为 AUTO,则表不再是自动表优化的候选项。
有关 DISTSTYLE EVEN 的更多信息,请参阅 CREATE TABLE。
-
- ALTER DISTKEY column_name 或 ALTER DISTSTYLE KEY DISTKEY column_name
-
一个子句,可更改用作表的分配键的列。请考虑以下事项:
-
不能在同一个表上并发运行 VACUUM 和 ALTER DISTKEY。
-
如果 VACUUM 已经运行,则 ALTER DISTKEY 返回错误。
-
如果 ALTER DISTKEY 正在运行,则不在表上启动后台 Vacuum。
-
如果 ALTER DISTKEY 正在运行,则前台 vacuum 会返回错误。
-
-
您一次只能在一个表上运行一个 ALTER DISTKEY 命令。
-
具有交错排序键的表不支持 ALTER DISTKEY 命令。
如果分配方式以前被定义为 AUTO,则表不再是自动表优化的候选项。
指定 DISTSTYLE KEY 时,按 DISTKEY 列中的值分配数据。有关 DISTSTYLE 的更多信息,请参阅 CREATE TABLE。
-
- ALTER DISTSTYLE AUTO
-
用于将表的现有分配方式更改为 AUTO 的子句。
将分配方式更改为 AUTO 时,表的分配模式设置为以下内容:
带有 DISTSTYLE ALL 的小型表被转换为 AUTO(ALL)。
带有 DISTSTYLE EVEN 的小型表被转换为 AUTO(ALL)。
带有 DISTSTYLE KEY 的小型表被转换为 AUTO(ALL)。
带有 DISTSTYLE ALL 的大型表被转换为 AUTO(EVEN)。
带有 DISTSTYLE EVEN 的大型表被转换为 AUTO(EVEN)。
带有 DISTSTYLE KEY 的大型表被转换为 AUTO(KEY),且 DISTKEY 被保留。在这种情况下,Amazon Redshift 不对表进行任何更改。
如果 Amazon Redshift 确定新的分配方式或密钥将提高查询的性能,那么 Amazon Redshift 将来可能会更改您的表格的分配方式或键。例如,Amazon Redshift 可能会将 DISTSTYLE 为 AUTO(KEY) 的表转换为 AUTO(EVEN),反之亦然。有关分发密钥被更改时行为的更多信息(包括数据重新分发和锁定),请参阅 Amazon Redshift Advisor 建议。
有关 DISTSTYLE AUTO 的更多信息,请参阅CREATE TABLE。
要查看表的分配方式,请查询 SVV_TABLE_INFO 系统目录视图。有关更多信息,请参阅 SVV_TABLE_INFO。要查看 Amazon Redshift Advisor 对表的建议,请查询 SVV_ALTER_TABLE_RECOMMENDATIONS 系统目录视图。有关更多信息,请参阅 SVV_ALTER_TABLE_RECOMMENDATIONS。要查看 Amazon Redshift 所采取的操作,请查询 SVL_AUTO_WORKER_ACTION 系统目录视图。有关更多信息,请参阅 SVL_AUTO_WORKER_ACTION。
- ALTER [COMPOUND] SORTKEY ( column_name [,...] )
-
一个旨在更改或添加用于表的排序键的子句。临时表不支持 ALTER SORTKEY。
当您更改排序键时,新排序键或原始排序键中列的压缩编码可能会更改。如果没有为表显式定义编码,则 Amazon Redshift 按如下方式自动分配压缩编码:
-
为定义为排序键的列分配 RAW 压缩。
-
为定义为 BOOLEAN、REAL 或 DOUBLE PRECISION 数据类型的列分配 RAW 压缩。
-
定义为 SMALLINT、INTEGER、BIGINT、DECIMAL、DATE、TIME、TIMETZT、IMESTAMP 或 TIMESTAMPTZ 的列分配了 AZ64 压缩。
-
定义为 CHAR 或 VARCHAR 的列分配了 LZO 压缩。
请考虑以下事项:
-
最多可以为每个表的排序键定义 400 列。
-
您可以将交错排序键更改为复合排序键或没有排序键。但是,您不能将复合排序键更改为交错排序键。
如果排序键以前被定义为 AUTO,则表不再是自动表优化的候选项。
-
Amazon Redshift 建议对定义为排序键的列使用 RAW 编码(不压缩)。当您更改列以将其选择为排序键时,列的压缩将更改为 RAW 压缩(无压缩)。这将增加表所需的存储空间。表大小的增加程度取决于特定的表定义和表格内容。有关压缩的更多信息,请参阅压缩编码
将数据加载到表中时,将按照排序键的顺序加载数据。更改排序键时,Amazon Redshift 对数据重新排序。有关 SORTKEY 的更多信息,请参阅 CREATE TABLE。
-
- ALTER SORTKEY AUTO
-
一个可更改目标表的排序键或将其添加到 AUTO 的子句。临时表不支持 ALTER SORTKEY AUTO。
当您将排序键更改为 AUTO 时,Amazon Redshift 会保留表的现有排序键。
如果 Amazon Redshift 确定新的排序键将提高查询的性能,那么 Amazon Redshift 将来可能会更改您的表的排序键。
有关 SORTKEY AUTO 的更多信息,请参阅CREATE TABLE。
要查看表的排序键,请查询 SVV_TABLE_INFO 系统目录视图。有关更多信息,请参阅 SVV_TABLE_INFO。要查看 Amazon Redshift Advisor 对表的建议,请查询 SVV_ALTER_TABLE_RECOMMENDATIONS 系统目录视图。有关更多信息,请参阅 SVV_ALTER_TABLE_RECOMMENDATIONS。要查看 Amazon Redshift 所采取的操作,请查询 SVL_AUTO_WORKER_ACTION 系统目录视图。有关更多信息,请参阅 SVL_AUTO_WORKER_ACTION。
- ALTER SORTKEY NONE
-
删除目标表的排序键的子句。
如果排序键以前被定义为 AUTO,则表不再是自动表优化的候选项。
- ALTER ENCODE AUTO
-
将目标表列的编码类型更改为 AUTO 的子句。当您将编码更改为 AUTO 时,Amazon Redshift 会保留表中列的现有编码类型。然后,如果 Amazon Redshift 确定新的编码类型可以提高查询性能,则 Amazon Redshift 可以更改表列的编码类型。
如果您更改一个或多个列以指定编码,Amazon Redshift 将不再自动调整表中所有列的编码。这些列保留当前的编码设置。
以下操作不会影响表的 ENCODE AUTO 设置:
重命名表。
更改表的 DISTSTYLE 或 SORTKEY 设置。
使用 ENCODE 设置添加或删除列。
使用 COPY 命令的 COMPUPDATE 选项。有关更多信息,请参阅 数据加载操作。
要查看表的编码,请查询 SVV_TABLE_INFO 系统目录视图。有关更多信息,请参阅 SVV_TABLE_INFO。
- RENAME COLUMN column_name TO new_name
-
用于将列重命名为 new_name 中指定的值的子句。列名称的最大长度为 127 个字节;更长的名称将被截断为 127 个字节。有关有效名称的更多信息,请参阅名称和标识符。
- ADD [ COLUMN ] column_name
-
用于将具有指定名称的列添加到表的子句。您在每个 ALTER TABLE 语句中只能修改一列。
您无法添加用作表的分配键 (DISTKEY) 或排序键 (SORTKEY) 的列。
您不能使用 ALTER TABLE ADD COLUMN 命令修改以下表和列属性:
-
UNIQUE
-
PRIMARY KEY
-
REFERENCES(外键)
-
IDENTITY 或 GENERATED BY DEFAULT AS IDENTITY
列名称的最大长度为 127 个字节;更长的名称将被截断为 127 个字节。可在单个表中定义的列的最大数目为 1,600。
在将列添加到外部表时,会应用以下限制:
-
您无法向列约束为 DEFAULT、ENCODE、NOT NULL 或 NULL 的外部表中添加列。
-
您无法向使用 AVRO 文件格式定义的外部表中添加列。
-
如果启用 pseudocolumns,则可在单个表中定义的最大列数为 1,598。如果未启用 pseudocolumns,则可在单个表中定义的最大列数为 1600。
有关更多信息,请参阅 CREATE EXTERNAL TABLE。
-
- column_type
-
要添加的列的数据类型。对于 CHAR 和 VARCHAR 列,您可以使用 MAX 关键字而不是声明最大长度。MAX 将最大长度设置为 4096 字节(对于 CHAR)或 65535 字节(对于 VARCHAR)。GEOMETRY 对象的最大大小为 1048447 字节。
有关 Amazon Redshift 支持的数据类型的信息,请参阅数据类型。
- DEFAULT default_expr
-
用于为列分配默认数据值的子句。default_expr 的数据类型必须匹配列的数据类型。DEFAULT 值必须是无变量的表达式。不允许子查询、对当前表中其他列的交叉引用和用户定义的函数。
default_expr 在未为列指定值的任何 INSERT 操作中使用。如果未指定默认值,则列的默认值为 null。
如果 COPY 操作在具有 DEFAULT 值和 NOT NULL 约束的列上遇到空字段,则 COPY 命令将插入 default_expr 值。
外部表不支持 DEFAULT。
- ENCODE encoding
-
列的压缩编码。预设情况下,如果您没有为表中的任何列指定压缩编码,或者您为表指定了 ENCODE AUTO 选项,Amazon Redshift 会自动管理表中所有列的压缩编码。
如果您为表中的任何列指定压缩编码,或者您没有为表指定 ENCODE AUTO 选项,Amazon Redshift 会自动将压缩编码分配给您未指定压缩编码的列,如下所示:
-
默认情况下,会为临时表中的所有列分配 RAW 压缩。
-
为定义为排序键的列分配 RAW 压缩。
-
定义为 BOOLEAN、REAL、DOUBLE PRECISION、GEOMETRY 或 GEOGRAPHY 数据类型的列分配了 RAW 压缩。
-
定义为 SMALLINT、INTEGER、BIGINT、DECIMAL、DATE、TIME、TIMETZ、TIMESTAMP 或 TIMESTAMPTZ 的列分配了 AZ64 压缩。
-
定义为 CHAR、VARCHAR 或 VARBYTE 的列分配了 LZO 压缩。
注意
如果您不希望压缩某个列,请显式指定 RAW 编码。
-
AZ64
-
BYTEDICT
-
DELTA
-
DELTA32K
-
LZO
-
MOSTLY8
-
MOSTLY16
-
MOSTLY32
-
RAW(无压缩)
-
RUNLENGTH
-
TEXT255
-
TEXT32K
-
ZSTD
外部表不支持 ENCODE。
-
- NOT NULL | NULL
-
NOT NULL 指定列中不允许包含 null 值。NULL(默认值)指定列接受 null 值。
外部表不支持 NOT NULL 和 NULL。
- COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE }
-
指定列中的字符串搜索或比较是 CASE_SENSITIVE 还是 CASE_INSENSITIVE 的子句。默认值与数据库的当前区分大小写的配置相同。
要查找数据库排序规则信息,请使用以下命令:
SELECT db_collation();
db_collation ---------------- case_sensitive (1 row)
- DROP [ COLUMN ] column_name
-
要从表中删除的列的名称。
您无法删除表中的最后一列。表必须有至少一列。
您无法删除用作表的分配键 (DISTKEY) 或排序键 (SORTKEY) 的列。如果列具有任何从属对象,例如视图、主键、外键或 UNIQUE 限制,则 DROP COLUMN 的默认行为是 RESTRICT。
对外部表中的列执行 DROP 时,会应用以下限制:
-
如果列用作分区,则您无法哦那个外部表中删除列。
-
您无法从使用 AVRO 文件格式定义的外部表中删除列。
-
对于外部表,将会忽略 RESTRICT 和 CASCADE。
除非删除或分离策略,否则您无法删除策略定义中引用的策略表中的列。在指定 CASCADE 选项时,这也将适用。您可以删除策略表中的其他列。
有关更多信息,请参阅 CREATE EXTERNAL TABLE。
-
- RESTRICT
-
在下面这些情况下,与 DROP COLUMN 一起使用时,RESTRICT 表示不删除要删除的列:
-
定义的视图引用了要删除的列
-
外键引用了该列
-
该列参与了多部分键
RESTRICT 不能与 CASCADE 一起使用。
对于外部表,将会忽略 RESTRICT 和 CASCADE。
-
- CASCADE
-
在与 DROP COLUMN 配合使用时,删除指定的列以及依赖该列的任何内容。CASCADE 不能与 RESTRICT 一起使用。
对于外部表,将会忽略 RESTRICT 和 CASCADE。
以下选项仅适用于外部表。
- SET LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
-
包含数据文件的 Amazon S3 文件夹的路径或包含 Amazon S3 对象路径列表的清单文件。桶必须与 Amazon Redshift 集群位于同一 AWS 区域。有关受支持的 AWS 区域的列表,请参阅Amazon Redshift Spectrum 限制。有关使用清单文件的更多信息,请参阅 CREATE EXTERNAL TABLE 参数 参考中的 LOCATION。
- SET FILE FORMAT format
-
外部数据文件的文件格式。
有效格式如下所示:
-
AVRO
-
PARQUET
-
RCFILE
-
SEQUENCEFILE
-
TEXTFILE
-
- SET TABLE PROPERTIES ( 'property_name'='property_value')
-
一个子句,用于设置外部表的表属性的表定义。
注意
表属性区分大小写。
- 'numRows'='row_count'
-
用于为表定义设置 numRows 值的属性。若要显式更新外部表的统计数据,请设置 numRows 属性来指示表的大小。Amazon Redshift 不分析外部表来生成表统计数据,查询优化程序会使用这些统计数据来生成查询计划。如果没有为外部表设置表统计数据,则 Amazon Redshift 会生成查询执行计划。此计划是基于“外部表较大,本地表较小”的假设生成的。
- 'skip.header.line.count'='line_count'
-
用于设置在每个源文件开头要跳过的行数的属性。
- PARTITION ( partition_column=partition_value [, ...] SET LOCATION { 's3://bucket/folder' | 's3://bucket/manifest_file' }
-
用于为一个或多个分区列设置新位置的子句。
- ADD [ IF NOT EXISTS ] PARTITION ( partition_column=partition_value [, ...] ) LOCATION { 's3://bucket/folder' | 's3://bucket/manifest_file' } [, ... ]
-
用于添加一个或多个分区的子句。您可以使用单个 ALTER TABLE … ADD 语句指定多个 PARTITION 子句。
注意
如果使用 AWS Glue 目录,则可以使用单个 ALTER TABLE 语句最多添加 100 个分区。
IF NOT EXISTS 子句指示,如果指定的分区已存在,命令应不进行任何更改。它还指示该命令应返回分区存在的消息,而不是以错误终止。此子句在编写脚本时很有用,可使脚本在 ALTER TABLE 尝试添加已存在的分区时不会失败。
- DROP PARTITION (partition_column=partition_value [, ...] )
-
用于删除指定分区的子句。删除分区只会更改外部表元数据。Amazon S3 上的数据不受影响。
- ROW LEVEL SECURITY { ON | OFF } [ CONJUNCTION TYPE { AND | OR } ] [ FOR DATASHARES ]
一个对关系开启或关闭行级安全性的子句。
在为关系开启行级安全性后,您只能读取行级安全策略允许您访问的行。如果没有策略向您授予对关系的访问权限,您将看不到关系中的任何行。只有超级用户和拥有
sys:secadmin
角色的用户或角色才能设置 ROW LEVEL SECURITY 子句。有关更多信息,请参阅 行级别安全性。[ CONJUNCTION TYPE { AND | OR } ]
一个允许您为关系选择行级安全策略的联接类型的子句。将多个行级安全策略附加到关系时,可以将这些策略与 AND 或 OR 子句合并。默认情况下,Amazon Redshift 将 RLS 策略与 AND 子句合并。具有
sys:secadmin
角色的超级用户、用户或角色可以使用此子句为关系定义行级安全策略的联接类型。有关更多信息,请参阅 为每个用户组合多个策略。FOR DATASHARES
一个子句,用于确定是否可以通过数据共享访问受 RLS 保护的关系。默认情况下,无法通过数据共享访问受 RLS 保护的关系。使用此子句运行的 ALTER TABLE ROW LEVEL SECURITY 命令只会影响关系的数据共享可访问性属性。ROW LEVEL SECURITY 属性未更改。
如果您允许通过数据共享访问受 RLS 保护的关系,则该关系在使用者端数据共享数据库中没有行级安全性。该关系在生产者端保留其 RLS 属性。
示例
有关说明 ALTER TABLE 命令用法的示例,请参阅以下内容。