4

I have 3 columns in a table i.e. email_id, rid, final_id.

Rules for rid and final_id:

  1. If the email_id has a corresponding rid, use rid as the final_id.
  2. If the email_id does not have a corresponding rid(i.e.rid is null), generate a unique 12 digit number and insert into final_id field.

How to generate 12 digit unique number in redshift?

  • There is a way where you can join timestamp, random_number, row_number to generate a 12 digit number. However, this is not a built in method and more of a hack. Also use `coalesce(rid, <12 digit int generation>)` to check the rules. –  Oct 05 '17 at 07:34

2 Answers2

13

From Creating a UUID function in Redshift:

By default there is no UUID function in AWS Redshift. However with the Python User-Defined Function you can easily create a UUID function in Redshift.

If you want random UUID:

CREATE OR REPLACE FUNCTION public.fn_uuid()
RETURNS character varying AS
' import uuid
 return uuid.uuid4().__str__()
 '
LANGUAGE plpythonu VOLATILE;

If you want sequential UUID :

CREATE OR REPLACE FUNCTION public.fn_uuid()
RETURNS character varying AS
' import uuid
 return uuid.uuid1().__str__()
 '
LANGUAGE plpythonu VOLATILE;
Community
  • 1
  • 1
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
  • Is there any other way ? As I am getting error like ' Invalid operation: permission denied for schema public;'. I don;t have access to public schema. –  Oct 05 '17 at 06:54
  • You could create the function in your own schema too. Try leaving out the `public.` bit. – John Rotenstein Oct 05 '17 at 07:16
  • Important - as per the documentation - you need to: revoke usage on language PLPYTHONU from PUBLIC; grant usage on language PLPYTHONU to group udf_devs; or grant usage on language PLPYTHONU to user youruserid; – Jon Scott Oct 05 '17 at 14:15
  • Hm - Anyone else running into the roadblock that UDF (user defined functions as shown above) are not allowed to be used in the "default" clause in Redshift? This makes the above moot if something like this won't work: `UPDATE table marbles ADD COLUMN marble_id NOT NULL default fn_uuid();` If you've found a work around to this, please post as that should be the accepted answer. Sincerely, - Lost marbles – isaacdre Apr 14 '23 at 18:34
2

Just to add to the accepted response:

Using uuid.uuid1().__str__() or uuid.uuid4().__str__() will give you values like XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX. This is completely valid as a uuid, but a neat way to get rid of the hash is to use the hex attribute:

CREATE OR REPLACE FUNCTION public.fn_uuid()
RETURNS character varying AS
' import uuid
 return uuid.uuid4().hex
 '
LANGUAGE plpythonu VOLATILE;

or 

CREATE OR REPLACE FUNCTION public.fn_uuid()
RETURNS character varying AS
' import uuid
 return uuid.uuid1().hex
 '
LANGUAGE plpythonu VOLATILE;

This will give you values without the hash, so just 32 hexadecimal characters.

Ewen Gillies
  • 76
  • 2