Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
“How do I move my R
scripts stored in one database instance to another? I have my development/test
system and want to migrate to production.”
Users of Oracle R Enterprise Embedded R Execution will often
store their R scripts in the R Script Repository in Oracle Database, especially
when using the ORE SQL API. From previous blog posts, you may recall that Embedded
R Execution enables running R scripts managed by Oracle Database using both R
and SQL interfaces. In ORE 1.3.1., the SQL API requires scripts to be stored in
the database and referenced by name in SQL queries. The SQL API enables
seamless integration with database-based applications and ease of production
deployment.
Loading R scripts in
the repository
Before talking about migration, we’ll first introduce how
users store R scripts in Oracle Database. Users can add R scripts to the
repository in R using the function ore.scriptCreate,
or SQL using the function sys.rqScriptCreate.
For the sample R script
id <- 1:10
plot(1:100,rnorm(100),pch=21,bg="red",cex =2)
data.frame(id=id, val=id / 100)
users wrap this in a function and store it in the R Script
Repository with a name. In R, this looks like
ore.scriptCreate("RandomRedDots", function () { line-height: 115%; font-family: "Courier New";"> id <- 1:10 plot(1:100,rnorm(100),pch=21,bg="red",cex
=2) data.frame(id=id, val=id / 100)) })
In SQL, this looks like
begin sys.rqScriptCreate('RandomRedDots', 'function(){ id <-
1:10
plot(1:100,rnorm(100),pch=21,bg="red",cex =2)
data.frame(id=id, val=id / 100) }'); end; /
The R function ore.scriptDrop
and SQL function sys.rqScriptDrop
can be used to drop these scripts as well. Note that the system will give an
error if the script name already exists.
Accessing R scripts
once they’ve been loaded
If you’re not using a source code control system, it is
possible that your R scripts can be misplaced or files modified, making what is
stored in Oracle Database to only or best copy of your R code. If you’ve loaded
your R scripts to the database, it is straightforward to access these scripts
from the database table SYS.RQ_SCRIPTS. For example,
select * from sys.rq_scripts where name='myScriptName';
From R, scripts in the repository can be loaded into the R
client engine using a function similar to the following:
ore.scriptLoad
<- function(name) { query <- paste("select script
from sys.rq_scripts where name='",name,"'",sep="") str.f <-
OREbase:::.ore.dbGetQuery(query) assign(name,eval(parse(text =
str.f)),pos=1)
}
ore.scriptLoad("myFunctionName")
This
function is also useful if you want to load an existing R script from the
repository into another R script in the repository – think modular coding style.
Just include this function in the body of the other function and load the named
script.
Migrating R scripts
from one database instance to another
To move a set of functions from one system to another, the
following script loads the functions from one R script repository into the
client R engine, then connects to the target database and creates the scripts
there with the same names.
scriptNames <-
OREbase:::.ore.dbGetQuery("select name from sys.rq_scripts where name not
like 'RQG$%' and name not like 'RQ$%'")$NAME
for(s in scriptNames) { cat(s,"\n") ore.scriptLoad(s)
}
ore.disconnect()
ore.connect("rquser","orcl","localhost","rquser")
for(s in scriptNames) { cat(s,"\n") ore.scriptDrop(s) ore.scriptCreate(s,get(s))
}
Best Practice When naming R scripts, keep in mind that the name can be up to 128
characters. As such, consider organizing scripts in a directory structure
manner. For example, if an organization has multiple groups or applications sharing
the same database and there are multiple components, use “/” to facilitate the function
organization:
line-height: 115%;">ore.scriptCreate("/org1/app1/component1/myFuntion1",
myFunction1)
ore.scriptCreate("/org1/app1/component1/myFuntion2", myFunction2)
ore.scriptCreate("/org1/app2/component2/myFuntion2", myFunction2)
ore.scriptCreate("/org2/app2/component1/myFuntion3", myFunction3)
ore.scriptCreate("/org3/app2/component1/myFuntion4", myFunction4)
Users can then query for all functions using the path prefix
when looking up functions.
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-fareast-font-family:"Times New Roman";
mso-fareast-theme-font:minor-fareast;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:"Times New Roman";
mso-bidi-theme-font:minor-bidi;}
