In Redshift, how can I copy a table, add dist and sort keys, and preserve column encodings?

Question

I would like to copy a table in Redshift, such that I can preserve the existing column encodings while also adding DIST and SORT keys. Is there a simple repeatable way to do this?

Previously I have copied the table DDL (which doesn't have encodings), ANALYZED the table, manually typed in the column encodings, manually added DIST and SORT keys, created the new table, inserted the data, dropped the old, then altered the new. This is quite time consuming and prone to error.

Is there a better way?

Joe Harris Joe Harris · Accepted Answer · 2018-01-23T19:17:33

Redshift automatically applies compression to new empty tables. Note that it typically defaults to LZO in this situation.

You can specify the DISTKEY and SORTKEY in a CREATE TABLE … AS query. So the simplest way to do what you want is:

CREATE TABLE new_table 
DISTKEY(new_dist_col) 
SORTKEY(new_sortkey_1, new_sortkey_2)
AS 
SELECT * FROM old_table;

EDIT: To keep existing encodings, use the v_generate_tbl_ddl view from our Utils library to get DDL of existing table with encoding. Then modify it to create the new target and INSERT INTO from the old table.

SELECT ddl 
FROM admin.v_generate_tbl_ddl 
WHERE tablename = 'old_table'
;
                   ddl
-------------------------------------------------
 --DROP TABLE "my_schema"."old_table";
 CREATE TABLE IF NOT EXISTS "my_schema"."old_table"
 (
         "dist_col" BIGINT   ENCODE zstd
         ,"sortkey_2" TIMESTAMP
         ,"sortkey_1" NUMERIC(28,4)   ENCODE zstd
         ,"column_d" VARCHAR(256)   ENCODE zstd
 )
 DISTSTYLE KEY
 DISTKEY ("dist_col")
 SORTKEY (
         "sortkey_2"
         "sortkey_1"
         )
 ;

In Redshift, how can I copy a table, add dist and sort keys, and preserve column encodings?

3 Answers