Page 1 of 1
How to implement “R” in Vertica" BLOG Post
Posted: Wed Oct 03, 2012 6:02 pm
by JimKnicely
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/
Re: How to implement “R” in Vertica" BLOG Post
Posted: Wed Jan 09, 2013 11:54 am
by whitish
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
Re: How to implement “R” in Vertica" BLOG Post
Posted: Wed Jan 09, 2013 4:00 pm
by JimKnicely
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)
Re: How to implement “R” in Vertica" BLOG Post
Posted: Wed Jan 09, 2013 6:40 pm
by id10t
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
)
}
Re: How to implement “R” in Vertica" BLOG Post
Posted: Fri Jan 11, 2013 12:52 am
by janice
sKwa, thanks for pointing out that there are examples of R files
I'll be checking them out later this week!
Re: How to implement “R” in Vertica" BLOG Post
Posted: Wed Apr 27, 2016 5:11 am
by KalpeshJ
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