Regular expression match U-SQL

Question

I need a help in writing in U-SQL to output records to two different files based on a regular expression output. Let me explain my scenario in detail.

Let us assume my input file has two columns, "Name" and person identification number ("PIN"):

Name , PIN
John ,12345
Harry ,01234
Tom, 24659

My condition for PIN is it should start with either 1 or 2. In the above case records 1 & 3 are valid and record 2 is invalid.

I need to output record 1 & 3 to my output processed file and 2 to my error file

How can I do this and also can I use Regex.Match to validate the regular expression?

//posting my code

@person =
EXTRACT UserId          int,
         PNR     string,
         UID String,
FROM "/Samples/Data/person.csv"
USING Extractors.csv();

@rs1=select UserId,PNR,UID,Regex.match(PNR,'^(19|20)[0-9]{2}((0[1-9])$') as pnrval,Regex.match(UID,'^(19|20)[0-9]{2}$') as uidval
from @person

@rs2 = select UserId,PNR,UID from @rs1 where pnrval=true or uidval=true
@rs3 = select UserId,PNR,UID from @rs1 where uidval=false or uidval= false

OUTPUT @rs2   
    TO "/output/sl.csv"
      USING Outputters.Csv();

OUTPUT @rs3   
    TO "/output/error.csv"
      USING Outputters.Csv();

But I'm receiving this error:

Severity Code Description Project File Line Suppression State Error E_CSC_USER_INVALIDCOLUMNTYPE: 'System.Text.RegularExpressions.Match' cannot be used as column type.

Answer 1

@person =
EXTRACT UserId          int,
         PNR     string,
         UID String,
FROM "/Samples/Data/person.csv"
USING Extractors.csv();

@rs1=select UserId,PNR,UID,Regex.Ismatch(PNR,'^(19|20)[0-9]{2}((0[1-9])$') as pnrval,Regex.Ismatch(UID,'^(19|20)[0-9]{2}$') as uidval
from @person

@rs2 = select UserId,PNR,UID from @rs1 where pnrval=true or uidval=true


@rs3 = select UserId,PNR,UID from @rs1 where pnrval=false or uidval= false

OUTPUT @rs2   
    TO "/output/sl.csv"
      USING Outputters.Csv();

OUTPUT @rs3   
    TO "/output/error.csv"
      USING Outputters.Csv();

This worked for my requirement. Thanks for the support and suggestions

Answer 2

@someData =
SELECT * FROM
    ( VALUES
    ("John", "12345"),
    ("Harry", "01234"),
    ("Tom", "24659")
    ) AS T(Name, pin);

@result1 =
    SELECT Name,
        pin
    FROM @someData
    WHERE pin.StartsWith("1") OR pin.StartsWith("2");

@result2 =
    SELECT Name,
        pin
    FROM @someData
    WHERE !pin.StartsWith("1") AND !pin.StartsWith("2");

Answer 3

Considering your input, I would use

.*\s*,\s*[12]\d+

• .* matches any amount of characters and is needed to match everything before the comma
• \s*,\s* matches a comma optionally preceded and or followed by any amount of blanks (\s matches a blank)
• [12] matches a single digit, equal to 1 or 2; this satisfies your requirement about PINs
• \d+ matches one or more digits

Live demo here.

As far as using Regex.Match, I'll quote this answer on StackOverflow:

System.Text.RegularExpressions.Match is not part of the built-in U-SQL types.

So what I would do here is pre-parsing your CSV in C#; something like:

        Regex CurrentRegex = new Regex(@".*\s*,\s*[12]\d+", RegexOptions.IgnoreCase);

        foreach (var LineOfText in File.ReadAllLines(InputFilePath))
        {
            Match CurrentMatch = CurrentRegex.Match(LineOfText);
            if (CurrentMatch.Success)
            {
                // Append line to success file
            }
            else
            {
                // Append line to error file
            }
            CurrentMatch = CurrentMatch.NextMatch();
        }