CODEWITHSUNDEEP
postgresqlamazon-web-servicesbulkinsertamazon-redshift
Marsellus Wallace
Marsellus Wallace

Reputation: 18601

AWS Redshift Bulk Insert + Encoding definition

Is it possible to do a bulk insert into REdshift using the create table as syntax while defining data type and encoding at the same time? What's the correct syntax?

EG The following gives a syntax error near 'as': 

create table my_table (
  a int not null encode runlength,
  b int not null encode runlength
) distkey(a) sortkey (a, b) as (
    select * from other_table
);

I can only get it to work by defining column name only (a or b) and that's a huge limitation...

Upvotes: 1

Views: 961

Answers (2)

Rakesh Singh
Rakesh Singh

Reputation: 170

More details on Redshift CTAS is given here: http://docs.aws.amazon.com/redshift/latest/dg/r_CTAS_usage_notes.html . In a nutshell, no where its mentioned that you can define the encoding in the CTAS statement. But you can define Sort Keys and Hash Keys. The default encoding chosesn by this statement is none.

However if you want to do a bulk insert, why don't you do in two steps.

  1. Create table new_table with your encoding and sort/hash keys
  2. Insert into new_table as select * from old_table

Upvotes: 0

Joe Harris
Joe Harris

Reputation: 14035

You can specify the DIST and SORT keys in a CREATE TABLE … AS query like this:

CREATE TABLE new_table 
DISTSTYLE KEY
DISTKEY ( my_dist )
SORTKEY ( my_sort )
AS
SELECT *
FROM   old_table
;

As per the docs: http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_AS.html I don't believe you can alter the compression encoding from the source table using CREATE TABLE AS.

Upvotes: 1

Related Questions