Menu
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?
324
Amazon Redshift now enables users to add and change sort keys of existing Redshift tables without having to re-create the table.
Redshift allows designating multiple columns as SORTKEY columns, but most of the best-practices documentation is written as if there were only a single SORTKEY.
If recent data is queried most frequently, specify the timestamp column as the leading column for the sort key. Queries are more efficient because they can skip entire blocks that fall outside the time range. If you do frequent range filtering or equality filtering on one column, specify that column as the sort key.
Amazon Redshift supports two different types of Sort Keys, Compound Sort Keys, and Interleaved Sort Keys. Selecting the right kind requires knowledge of the queries that you plan to execute.
As Yaniv Kessler mentioned, it's not possible to add or change distkey and sort key after creating a table, and you have to recreate a table and copy all data to the new table. You can use the following SQL format to recreate a table with a new design.
ALTER TABLE test_table RENAME TO old_test_table; CREATE TABLE new_test_table([new table columns]); INSERT INTO new_test_table (SELECT * FROM old_test_table); ALTER TABLE new_test_table RENAME TO test_table; DROP TABLE old_test_table; In my experience, this SQL is used for not only changing distkey and sortkey, but also setting the encoding(compression) type.
69
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
