How to implement “R” in Vertica" BLOG Post

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

How to implement “R” in Vertica" BLOG Post

Post by JimKnicely » Wed Oct 03, 2012 6:02 pm

Cool BLOG post on Vertica's site - "How to implement “R” in Vertica" by Mark Warner and Pratibha Rana

http://www.vertica.com/2012/10/02/how-t ... n-vertica/
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

whitish
Newbie
Newbie
Posts: 2
Joined: Wed Jan 09, 2013 11:52 am

Re: How to implement “R” in Vertica" BLOG Post

Post by whitish » Wed Jan 09, 2013 11:54 am

I tried to reproduce step-by-step on this post:
http://www.vertica.com/2012/10/02/how-t ... n-vertica/

and I got error:
dbadmin=> create libRARY kmeansGeoLib as '/home/dbadmin/R/kmeans_cluster.R' language 'R';
CREATE LIBRARY
dbadmin=> create transform function kmeansGeo as name 'kmeansGeoData' library kmeansGeoLib;
ROLLBACK 3399: Failure in UDx RPC call InvokeGetUdxType(): Error calling getUdxType() in User Defined Object [] at [/scratch_a/release/vbuild/vertica/UDxFence/vertica-udx-R.cpp:151], error code: 0, message: Error happened in getUdxType : Exception in processing required attribute udxtype in the factory function : Error evaluating: kmeansGeoData

Do you have any thoughts what may be wrong about udxtype?

Thank you,
-Alex

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: How to implement “R” in Vertica" BLOG Post

Post by JimKnicely » Wed Jan 09, 2013 4:00 pm

Hi,

Welcome to the forums!

There may be a bug in the code they provide for the library (in the content of the kmeans.R file) because I get the error too ;)

Here is a simple example that works!

Code: Select all

bash-3.2$ cat mul.R
##########
# Example: Multiplication
# Filename: mul.R
##########

###
# @brief multiplies col1 and col2 of the input data frame.
###
mul<- function(x)
{
        pr <- x[,1] * x[,2]
        pr
}

mulFactory <- function()
{
        list(name=mul,udxtype=c("scalar"),intype=c("float","float"), outtype=c("float"), outtypecallback=mulReturnType)
}


mulReturnType <- function(x)
{
         ret = data.frame(datatype = rep(NA,1), length = rep(NA,1), scale = rep(NA,1), name = rep(NA,1))
         ret[1,1] = "float"
         ret[1,4] = "Multiplied"
         ret
}
Sample table data to be used:

Code: Select all

dbadmin=> select * from twocols order by 1;
 x  | y
----+----
  1 |  1
  2 |  2
  3 |  3
  4 |  4
  5 |  5
  6 |  6
  7 |  7
  8 |  8
  9 |  9
 10 | 10
(10 rows)
Create the library and UDx:

Code: Select all

dbadmin=> CREATE LIBRARY mulLib AS '/usr/home/dbadmin/mul.R' LANGUAGE 'R';
CREATE LIBRARY
dbadmin=> CREATE FUNCTION mul AS NAME 'mulFactory' LIBRARY mulLib;
CREATE FUNCTION
Now you can use the function:

Code: Select all

dbadmin=> select mul(x,y) from twocols;
 mul
-----
   1
  16
  25
  64
 100
  49
  81
   4
   9
  36
(10 rows)
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: How to implement “R” in Vertica" BLOG Post

Post by id10t » Wed Jan 09, 2013 6:40 pm

Hi Alex!

Check for /opt/vertica/sdk/examples/RFunctions/Rfunctions.R file, it includes `k-mean` example.

/opt/vertica/sdk/examples/RFunctions.sql - script that installs functions + examples.


BTW Jim right, there are bug in factory function:

In blog:

Code: Select all

kmeansFactory <- function()
{
list(name=kmeans_cluster,    #function that does the processing
udxtype=c(“transform”), #type of the function
intype=c(“int”, “float”,”float”),   #input types
outtype=c(“int”,”int”),  #output types
)
}
should be:

Code: Select all

kmeansFactory <- function()
{
    list(
          name=kmeans_cluster,              # function that does work
          udxtype=c("transform"),           # type of the function
          intype=c("int", "float","float"), # input types
          outtype=c("int","int"),           # output types
          outnames=c("x","y")               # names of columns
        )
}

User avatar
janice
Intermediate
Intermediate
Posts: 51
Joined: Wed May 30, 2012 1:14 pm

Re: How to implement “R” in Vertica" BLOG Post

Post by janice » Fri Jan 11, 2013 12:52 am

sKwa, thanks for pointing out that there are examples of R files :o I'll be checking them out later this week!
Everyday is an adventure!

KalpeshJ
Newbie
Newbie
Posts: 3
Joined: Tue Apr 26, 2016 3:00 pm

Re: How to implement “R” in Vertica" BLOG Post

Post by KalpeshJ » Wed Apr 27, 2016 5:11 am

Hello ,

I have a readily available R Prediction script with me that predicts with the help of Machine Learning algorithm written by me.
Now, I want to test its applicability in Vertica.
I have installed R-Vertica Lang package in my linux machine and also made everything ready to run.

My Question:
Is it possible to run the R-script in vertica for in-database predictive analytics.
Or
We should use those that are readily available only for use.

Note: I have tried many approaches but i am yet to get through it.
Also, I have successfully pulled data from Vertica into R and did prediction.
Now, I want to go reverse and run the R-script in vertica database.

Any help on this would be greatly appreciated.

Thank You.
Kalpesh

Post Reply

Return to “R Language Integration”