none
Writing data from data frame into sql server table RRS feed

  • Question

  • Hi guys

    I'm fairly new to R and interacting with it via sql server. I am trying to get the results of a data frame in to a table on my database. I have created the script below and am able to get the rules displayed as output, however I'm unable to work out how to send these results to a table.

    exec sp_execute_external_script 
    @language = N'R',   
    @script= N'
          library(arules);
          order_items <- read.transactions("C:\\Code\\baskets.csv", sep = ",");
          apriori(order_items);
          order_rules <- apriori(order_items, parameter = list(support = 0.0009, confidence = 0.25, minlen = 2));
          order_rules_df <- as(order_rules, "data.frame");',
    @input_data_1 = N'',
    @output_data_1_name = N'order_rules_df'
    with result sets ((
      "rules" varchar(50),
      "support" varchar(50),
      "confidence" float,
      "lift" float
      ));

    Also the output looks like the sample below... is there any way I can separate the results of the "rules" column...

    Output...
    {72735} => {160908} 0.000902925 0.732142857142857 325.932773109244
    {160908} => {72735} 0.000902925 0.401960784313726 325.932773109244
    {172626} => {172628} 0.000902925 0.854166666666667 538.694444444444
    {172628} => {172626} 0.000902925 0.569444444444444 538.694444444444

    Preferred output...
    72735, 160908, 0.000902925 0.732142857142857 325.932773109244
    160908, 72735, 0.000902925 0.401960784313726 325.932773109244
    172626, 172628, 0.000902925 0.854166666666667 538.694444444444
    172628, 172626, 0.000902925 0.569444444444444 538.694444444444

    *** meaning I would like to split the output in to 5 separate columns in my database table. Otherwise I will have to import the data and then split it programmatically.

    Thanks

    Joe


    • Edited by BTuser987 Tuesday, August 9, 2016 8:48 PM
    Tuesday, August 9, 2016 8:21 PM