如何在AWS Redshift中向现有表添加排序键 [英] How to add a sort key to an existing table in AWS Redshift

问题描述

在AWS Redshift中，我想向已创建的表添加排序键.有没有可以添加列并将其用作排序键的命令?

解决方案

更新:

Amazon Redshift现在使用户能够添加和更改现有Redshift表的排序键，而不必重新创建表.这项新功能简化了用户在Redshift中维护最佳排序顺序以在其查询模式不断发展并在不中断对表访问权限的情况下实现高性能的过程中的体验.

来源: https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-redshift-supports-changing-table-sort-keys-dynamics/

目前，我认为这是不可能的(希望将来会改变).过去，当我遇到这种情况时，我创建了一个新表并将数据从旧表复制到该表中.

来自 http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html :

ADD [COLUMN] column_name 将具有指定名称的列添加到表中.您只能在每个ALTER TABLE语句中添加一列.

您不能添加作为表的分发键(DISTKEY)或排序键(SORTKEY)的列.

您不能使用ALTER TABLE ADD COLUMN命令来修改以下表和列属性:

唯一

主键

参考(外键)

身份

最大列名长度为127个字符；较长的名称将被截断为127个字符.一个表中最多可以定义1600列.

In AWS Redshift, I want to add a sort key to a table that is already created. Is there any command which can add a column and use it as sort key?

解决方案

UPDATE:

Amazon Redshift now enables users to add and change sort keys of existing Redshift tables without having to re-create the table. The new capability simplifies user experience in maintaining the optimal sort order in Redshift to achieve high performance as their query patterns evolve and do it without interrupting the access to the tables.

source: https://aws.amazon.com/about-aws/whats-new/2019/11/amazon-redshift-supports-changing-table-sort-keys-dynamically/

At the moment I think its not possible (hopefully that will change in the future). In the past when I ran into this kind of situation I created a new table and copied the data from the old one into it.

from http://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html:

ADD [ COLUMN ] column_name Adds a column with the specified name to the table. You can add only one column in each ALTER TABLE statement.

You cannot add a column that is the distribution key (DISTKEY) or a sort key (SORTKEY) of the table.

You cannot use an ALTER TABLE ADD COLUMN command to modify the following table and column attributes:

UNIQUE

PRIMARY KEY

REFERENCES (foreign key)

IDENTITY

The maximum column name length is 127 characters; longer names are truncated to 127 characters. The maximum number of columns you can define in a single table is 1,600.

这篇关于如何在AWS Redshift中向现有表添加排序键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！