Changing and masking field values using MODIFY

This section covers the use of MODIFY on streams of user-defined types. For streams of type WAEvent, see Modifying and masking values in the WAEvent data array using MODIFY.

Using MODIFY in a CQ's SELECT statement allows you to change or mask the contents of specified fields. When a stream has many fields, this can be much simpler and more efficient approach than writing a SELECT statement that handles each of the fields.

The syntax is:

SELECT <field name> FROM <stream name> MODIFY (<field name> = <expression>)

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 Amount field using an exchange rate of 1.09:

CREATE CQ ConvertAmount 
INSERT INTO ConvertedStream
SELECT * FROM UnconvertedStream
MODIFY(Amount = TO_FLOAT(Amount) * 1.09);

The next example illustrates the use of 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 the PhoneNum field and the country codes are in the  Country field.

CREATE CQ maskData 
INSERT INTO maskedDataStream
SELECT * FROM unmaskedDataStream
MODIFY(
data[4] = CASE
    WHEN Country == "US" THEN maskPhoneNumber(PhoneNum, "###-xxx-xxx")
    ELSE maskPhoneNumber(PhoneNum), "#####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.

See also Modifying output using ColumnMap. In some cases that may be a more straightforward and efficient solution than using MODIFY.