Just testing out AWS Redshift, and having discovered some dupe data on an insert that I'd hoped would just fail on duplication in the key column, reading the docs reveal that primary key constraints aren't "enforced".
Anyone figured out how to prevent duplication on primary key (per "traditional" expectation).
Thanks to any Redshift pioneers!
I assign UUIDs when the records are created. If the record is inherently unique, I use type 4 UUIDs (random), and when they aren't I use type 5 (SHA-1 hash) using the natural keys as input.
Then you can follow this instruction by AWS very easily to perform UPSERTs. If your input has duplicates, you should be able to clean up by issuing a SQL that looks something like this in your staging table:
CREATE TABLE cleaned AS
SELECT
pk_field,
field_1,
field_2,
...
FROM (
SELECT
ROW_NUMBER() OVER (PARTITION BY pk_field order by pk_field) AS r,
t.*
from table1 t
) x
where x.r = 1
If it is too late to add an identity column to use as rowid (ALTER won't allow you to add an IDENTITY column in Redshift) you can do this:
DISTINCT to get rid of dupes)Here's a sample: (let's assume id is your key to check dupes against, and data_table is your table)
CREATE TEMP TABLE delete_dupe_row_list AS
SELECT t.id FROM data_table t WHERE t.id IS NOT NULL GROUP BY t.id HAVING COUNT(t.id)>1;
CREATE TEMP TABLE delete_dupe_rows AS
SELECT DISTINCT d.* FROM data_table d JOIN delete_dupe_row_list l ON l.id=d.id;
START TRANSACTION;
DELETE FROM data_table USING delete_dupe_row_list l WHERE l.id=data_table.id;
INSERT INTO data_table SELECT * FROM delete_dupe_rows;
COMMIT;
DROP TABLE delete_dupe_rows;
DROP TABLE delete_dupe_row_list;
Confirmed, they don't enforce it:
Uniqueness, primary key, and foreign key constraints are informational only; they are not enforced by Amazon Redshift. Nonetheless, primary keys and foreign keys are used as planning hints and they should be declared if your ETL process or some other process in your application enforces their integrity.
For example, the query planner uses primary and foreign keys in certain statistical computations, to infer uniqueness and referential relationships that affect subquery decorrelation techniques, to order large numbers of joins, and to eliminate redundant joins.
The planner leverages these key relationships, but it assumes that all keys in Amazon Redshift tables are valid as loaded. If your application allows invalid foreign keys or primary keys, some queries could return incorrect results. For example, a SELECT DISTINCT query might return duplicate rows if the primary key is not unique. Do not define key constraints for your tables if you doubt their validity. On the other hand, you should always declare primary and foreign keys and uniqueness constraints when you know that they are valid.
Amazon Redshift does enforce NOT NULL column constraints.
http://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html
Yeah You can not do that. For the time being, I think you should just insert duplicate data(basically duplicate keys) with an extra column of timestamp. So it will have all versions of that particular row, since update is also an insert and while you query Redshift, make sure you pick the latest one.
A quick and dirty way is to use group by
select max(<column_a>), max(<column_a>), <pk_column1>, <pk_column2>
from <table_name>
group by <pk_column1>, <pk_column2>
I'm using IDENTITY to auto increment my primary key.
Here is a question I asked on the AWS forums:
https://forums.aws.amazon.com/message.jspa?messageID=450157#450157
