2020. 2. 29. 14:30ㆍ카테고리 없음
I'm hopeful to avoid that. An easy circumstance to consider is a time dimension that's granular to the hour. I was hopeful to only insert rows that might get used on a dimensional query, but if I have to select and then insert for every possible hour that the data spans when those hours are being used by millions of rows - it's crazy.
The fact table has a time-key, yyyymmddhh - and this key links to the dimension table which expands the data (year, month, day, day of week, week, business quarter, etc.) Sure hope there's an alt.–Mar 2 '13 at 4:57. 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 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 ASSELECTpkfield,field1,field2.FROM (SELECTROWNUMBER OVER (PARTITION BY pkfield order by pkfield) AS r,t.from table1 t) xwhere x.r = 1. Confirmed, they don't enforce it:Uniqueness, primary key, and foreign key constraints are informationalonly; they are not enforced by Amazon Redshift. Nonetheless, primarykeys and foreign keys are used as planning hints and they should bedeclared if your ETL process or some other process in your applicationenforces their integrity.For example, the query planner uses primary and foreign keys incertain statistical computations, to infer uniqueness and referentialrelationships that affect subquery decorrelation techniques, to orderlarge numbers of joins, and to eliminate redundant joins.The planner leverages these key relationships, but it assumes that allkeys in Amazon Redshift tables are valid as loaded.
Redshift 3 Software
If yourapplication allows invalid foreign keys or primary keys, some queriescould return incorrect results. For example, a SELECT DISTINCT querymight return duplicate rows if the primary key is not unique. Do notdefine key constraints for your tables if you doubt their validity.
Redshift 7 Premium Edition Key Banking
Onthe other hand, you should always declare primary and foreign keys anduniqueness constraints when you know that they are valid.Amazon Redshift does enforce NOT NULL column constraints.