Skip to main content

Modifying and masking values in the WAEvent data array using MODIFY

When a CDC reader's output is the input of a writer, you may insert a CQ between the two to modify the values in the WAEvent's data array. This provides more flexibility when replicating data.

In this context, the syntax for the SELECT statement is:

SELECT <alias> FROM <stream name> <alias> MODIFY (<alias>.data[<field number>] = <expression>,...)

  • Precede the CREATE CQ statement with a CREATE STREAM <name> OF TYPE Global.WAEvent statement that creates the output stream for the CQ.

  • Start the SELECT statement with SELECT <alias> FROM <stream name> <alias>.

  • <alias>.data[<field number>] specifies the field of the array to be modified. Fields are numbered starting with 0.

  • The expression can use the same operators and functions as SELECT.

  • The MODIFY clause may include CASE statements.

The following simple example would convert a monetary amount in the data[4] field using an exchange rate of 1.09:

CREATE STREAM ConvertedStream OF TYPE Global.WAEvent;
CREATE CQ ConvertAmount 
INSERT INTO ConvertedStream
SELECT r FROM RawStream r
MODIFY(r.data[4] = TO_FLOAT(r.data[4]) * 1.09);

The next example illustrates the use of masking functions and CASE statements. It uses the maskPhoneNumber function (see Masking functions) to mask individually identifiable information from US and India telephone numbers (as dialed from the US) while preserving the country and area codes. The US numbers have the format ###-###-####, where the first three digits are the area code. India numbers have the format 91-###-###-####, where 91 is the country code and the third through fifth digits are the subscriber trunk dialing (STD) code. The telephone numbers are in data[4] and the country codes are in data[5].

CREATE STREAM MaskedStream OF Global.WAEvent;
CREATE CQ maskData 
INSERT INTO maskedDataStream
SELECT r FROM RawStream r
MODIFY(
r.data[4] = CASE
    WHEN TO_STRING(r.data[5]) == "US" THEN maskPhoneNumber(TO_STRING(r.data[4]), "###-xxx-xxx")
    ELSE maskPhoneNumber(TO_STRING(r.data[4]), "#####x#xxx#xxxx")
  END
);

This could be extended with additional WHEN statements to mask numbers from additional countries, or with additional masking functions to mask individually identifiable information such as credit card, Social Security, and national identification numbers.

See Masking functions for additional examples.