Integrate R script into SELECT query

Question

So I have the following integrated R code which I use to be able to match stuff via regex without too much complication (what I want to do eventually is more complex than this example so regex is pretty much required, this is just the first step):

DECLARE @in_adcn nvarchar(500)
DECLARE @out_ou nvarchar(500)
SET @in_adcn = N'CN=Surname\, Firstname (asdf),OU=999,OU=Department2,OU=Fixed,OU=Fixed,DC=Fixed,DC=Fixed,DC=Fixed'
SET @out_ou = N''

-- Extract department name from AD OU string
EXEC sp_execute_external_script @language=N'R',
    @script = N'
        pat = "^.+OU=([^,]+),OU=Fixed,OU=Fixed,DC=Fixed,DC=Fixed,DC=Fixed$";
        m <- gsub(pat, "\\1", regmatches(in_adcn1, gregexpr(pat, in_adcn1))[[1]]);
        out_ou1 <- m;',
    @params = N'@in_adcn1 varchar(500), @out_ou1 varchar(500) output',
    @in_adcn1 = @in_adcn,
    @out_ou1 = @out_ou OUTPUT
WITH RESULT SETS NONE;
SELECT @out_ou;
GO

This works fine, it returns exactly what I want from the AD distinguishedName string (in this case: "Department2").

Now I want that output string inside this SELECT query (the input string for the R script is acad.ADCn):

SELECT
    acad.ADCn,
    (
        --<here should be the code that returns the string from the R script above>
    ) AS Departmentname,
    acad.NBAccountName,
    acb.eMail
FROM
    MyDB.dbo.AccountTable AS acad
LEFT JOIN
    MyDB.dbo.AddressTable AS acb
    ON
        acad.[ObjectID]
        = acb.[ObjectID]

So far I haven't managed to get this right yet. Any help would be greatly appreciated.

What you would like is to have a scalar User Defined Function calling the `sp_execute_external_script` where you passed in the `acad.ADcn` to the function. However since you cannot call a UDF from inside a function you are out of luck. However, in this case - why do you want to use R for regex, couldn't you create a SQLCLR procedure/function that did this for you? — Niels Berglund, Aug 30 '17 at 15:54

score 1 · Accepted Answer · answered Aug 30 '17 at 16:13

Consider inputting the entire SELECT query as an @input_data_1 param (following the tutorial example). Then, run your regex operation to a new saved dataframe column, DepartmentName. Finally, output the entire four column resultset.

EXECUTE sp_execute_external_script
      @language = N'R'
    , @script = N' df <- InputDataSet;
                   pat = "^.+OU=([^,]+),OU=Fixed,OU=Fixed,DC=Fixed,DC=Fixed,DC=Fixed$";
                   df$DepartmentName <- gsub(pat, "\\1", regmatches(df$ADCn, gregexpr(pat, df$ADCn))[[1]]);
                   OutputDataSet <- df[c("ADCn", "DepartmentName", "NBAccountName", "eMail")];'
    , @input_data_1 = N' SELECT acad.ADCn, acad.NBAccountName, acb.eMail
                         FROM MyDB.dbo.AccountTable AS acad
                         LEFT JOIN MyDB.dbo.AddressTable AS acb
                            ON acad.[ObjectID] = acb.[ObjectID];'
    WITH RESULT SETS (( [ADCn] varchar(255), [DepartmentName] varchar(255), [NBAccountName] varchar(255), [eMail] varchar(255) ));

Of course without data, above is untested. Please adjust to fit needs especially with input of ADCn.

Almost perfect! Just 2 issues left: somehow this returns the same departmentname for every ADCn (there should be a lot of different ones). The first ADCn which contains "Department9" seems to be the one that is used again and again, because in the end they're all in "Department9" (which isn't true). And the second problem: this generates a "textual table" in the message output. Shouldn't this return a "real" table, because that's what I need. Thanks! — cheh, Aug 31 '17 at 07:27
Isn't that due to the `[1]` in this line of code: `df$DepartmentName <- gsub(pat, "\\1", regmatches(df$ADCn, gregexpr(pat, df$ADCn))[[1]]);`? What do you mean with "real table"? When I run similar code I get a resultset back with columns etc. — Niels Berglund, Aug 31 '17 at 09:06
Yes, thanks. And nevermind about the table issue, I just had a different display active. It works as expected now. Thanks all. — cheh, Aug 31 '17 at 09:54

Integrate R script into SELECT query

1 Answers1