The Data Samurai

There is no redshift inbuilt function to strip a character from start-end of a string. For eg. if a numeric value is stored as a character with preceding zeros ' 0000123' and you want to store/operate/aggregate/join it as a number it is not possible with inbuilt redshift functions.  What you can do is to create the following strip udfs and make a use of these. lstrip : strips out the left instances of a character from a string. CREATE OR REPLACE FUNCTION public.fn_lstrip(str_in character varying, a character) RETURNS character varying AS ' try: return(str_in.lstrip(a)) except: return None' LANGUAGE plpythonu VOLATILE; eg. select public.fn_lstrip('00001234','0') would result in 12345 rstrip : strips out the right instances of a character from a string. CREATE OR REPLACE FUNCTION public.fn_rstrip(str_in character varying, a character) RETURNS character varying AS ' try: return(str_in.rstrip(a)) except: return None...

We all know the data hotspots have negative impact on the performance in any distributed data processing environment and engine.  This holds true for hadoop / MPP columnar and other databases.  One way to avoid hotspots is to use the UUID to generate unique Ids. As defined by wikipedia a UUID is "  A UUID is simply a 128-bit value. The meaning of each bit is defined by any of several variants. " By default there is no UUID function in AWS Redshift.  However with the python UDF 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;