Replacement Rules


The Replacement Rule feature allows you to replace certain characters during your data transfer.

For example, you may have a column that contains data you want to remove or hide before transferring to Amazon Redshift. You can define a replacement rule for that column, and replace all values before loading them into Amazon Redshift.


The Replacement Rule is defined in JSON, following the example below.

[    { "table" : "users",      "column" : "user_id",      "replace_rules" : [        {"^-$" : null},        {"#$" : ""}      ]    },    { "table" : "orders",      "column" : "name",      "replace_rules" : [        {"^(.*)$":"\\1!"}}      ]    },  ]  

The JSON array should contain a collection of JSON objects, with table,column, and replace_rules as keys.

For replace_rules, the value will be an array of JSON key-value pairs. The key will be the regular expression to use to match the original text you want to replace. The value will be what you want to replace the original text to. We support any regular expression supported in Ruby.

For example, using the Replacement Rule defined above, any # characters at the very end of any user_id will be removed before being loaded into Amazon Redshift (based on the second rule set in the example).

You can also use capture groups as in the third replace_rules example. The value that matches (.*) will be stored in the numbered back-reference \1. So by stating "\\1!" in the replacement rule, all name values in the orderstable will have a ! appended to it, before being loaded to Amazon Redshift. (Since a single backslash in a double quote is interpreted as an octal escape, please make sure you use double backslashes; e.g., \\1.)