Redshift tables DDL generation

Note: ENCODE encoding

The compression encoding for a column. ENCODE AUTO is the default for tables. Amazon Redshift automatically manages compression encoding for all columns in the table. If you specify compression encoding for any column in the table, the table is no longer set to ENCODE AUTO. Amazon Redshift no longer automatically manages compression encoding for all columns in the table.

CREATE TABLE dev.tbl_encode (clmn varchar(30));

This is dbForge output:

CREATE TABLE dev.tbl_encode (

  clmn CHARACTER VARYING(30) ENCODE LZO

);

Now create the second table:

CREATE TABLE dev.tbl_encode2 (clmn CHARACTER VARYING(30) ENCODE LZO);

Generate DDL script for the second table:

CREATE TABLE dev.tbl_encode2 (

  clmn CHARACTER VARYING(30) ENCODE LZO

);

The DDL script looks the same, but we should check ENCODE AUTO property.

Now insert some data into tables. Unfortunately, the table is visible in 'svv_table_info' only after an initial block is allocated to it by inserting some data into the table.  

insert into dev.tbl_encode (clmn) values ('xyz');

insert into dev.tbl_encode2 (clmn) values ('xyz');

Now we can check which table uses ENCODE AUTO.

SELECT "schema","table", encoded FROM svv_table_info WHERE "table" IN ('tbl_encode','tbl_encode2') AND schema = 'dev' ORDER BY "table";

We can see that tbl_encode table was created without ENCODE information. In this case, dbForge should not include encode statements in DDL.

Algorithm:

1.       Check if the table is listed in svv_table_info. If not, generate DDL with encode.

2.       If the table is listed in svv_table_info, check “encoded” column. If this column equals ‘Y, AUTO(ENCODE)’ – generate DDL without encoding.