Create Redshift Tables using Query Engine

Users can now create redshift tables using Query Engine using the CREATE TABLE AS SELECT(CTAS) queries. The created table and it's data will be synced back to Amorphic datasets if the query execution is successful.

CREATE TABLE AS SELECT Queries

These queries can be used to create new redshift tables containing results of a SELECT query using existing redshift tables on which the user has SELECT privilege.

If the query execution is successful, Redshift dataset will be created with all the details and users can view the files under the Files tab. If the query execution fails, the created dataset and related redshift table would be deleted. Users need to wait until the delete operation is complete before they can run the query again or the query would fail with the error saying dataset already exists.

Query Syntax

CREATE [ LOCAL ]
TABLE table_name
[ ( column_name [, ... ] ) ]
[ BACKUP { YES | NO } ]
[ table_attributes ]
AS query

where table_attributes are:
[ DISTSTYLE { AUTO | EVEN | ALL | KEY } ]
[ DISTKEY( distkey_identifier ) ]
[ [ COMPOUND | INTERLEAVED ] SORTKEY( column_name [, ...] ) ]

Note: In Amorphic, creation of temporary tables is not allowed using CTAS queries.

Please refer to the AWS documentation for more information regarding the redshift CTAS queries.

Use Cases

1. Reducing a large dataset into a smaller, more efficient dataset.
2. Selecting a subset of the columns and rows to only deliver what the consumer of the data really needs.
3. Cost Optimization, By aggregating data with CTAS queries, you can potentially reduce the amount of data scanned during query execution. This can lead to cost savings, as Redshift charges based on the amount of data processed.