Hi, I'm Philip from the HANA Academy. In
this series of videos were looking at
R integration for SAP HANA. In this
video we're going to look at how we can
use the R integration for HANA when
we're working with the XS Advanced and
Web IDE for HANA in terms of our
project development. So the process is
very similar to what we saw in other
videos in the R series. In the R
Integration of SAP HANA series on the
HANA Academy. We’re also going to make
use of knowledge that you typically be
able to get from the XS Advanced model
playlist as well in terms of getting
started with Web IDE for HANA etc, etc.
And the architecture how it works with
HDI containers. So let's go to the GitHub
site. You'll see that in GitHub we've got
the R Integration repository and in
there that's where we found the code
snippets that we saw in previous videos.
Now one of the code snippets is very
important. The Getting Started with HANA
2.0 SPS 02 because as well as creating a
user, we also created a remote source
which allows us to access HANA. We also
granted create R script to the HDI
default Container role. Which means that
any HDI Container that we now create
will be able to have this create R
script option and we'll be able to grant
it to technical users that this
container owner might create or might
authorize. That's very important when
we're working in XS Advanced with
Web IDE for HANA.
We'll also see if we just go back that
we've got a another repository here
called R app and this is actually a
fully finished application just to save
you some typing. If you want to follow
along. We’ve got an app that we can
actually go and access. So I'm going to
click on that and if we notice here it's
got all the code that we need but we're
not going to take each file individually.
We've got different bits of data
actually and we've got a whole fully
packaged application. What we're going to
do is clone it. So for that we choose the
clone or download option and then choose
to copy this link that ends in .git. So
we're going to copy this
URL. Which means we can then easily clone
it when we're using Web IDE for HANA. So
let's go across to Web IDE for HANA.
Again we’re actually in the Database
Explorer
from previous videos. So let's just go up
and we're now we got ourselves into
Web IDE for HANA. Now just to make it
simpler I'll just close out these SQL
windows that we were using.
Notice here we specified our remote
source, R serve and we pointed it to the
IP address and port for what we've got R
and we have run the code to authorize
this HDI container owner. Now that's something
you only need to do once in a system
installation. Now we should be good to go.
So we're basically now in Web IDE for
HANA. What I'm going to do is a create
a project and the easy way to do that is
to right mouse button on workspace
choose Git and then choose clone
repository and we've already copied the
URL of that repository the R app one. So
I'm going to paste that in and hit the
clone button and basically it will clone
that into your workspace. So you've got
the whole thing up and done and if you
want you can create a local branch for
your own changes etc, etc. So now we've
got the project we can see that we've
actually got a database component. This
will give us an HDI container, a schema
and technical users and in the database
component we've got a number of
different artifacts as they're called
here. Otherwise it's pretty much a
standard XS Advanced project. What
I'm going to do first though is we need
to specify which space the application
is going to work with and as we've
actually cloned it from GitHub we've not
said it during the typically you would
set that up when you're doing in the
template for creating a new multi target
application. So I'm going to right mouse
button on the project and choose project
settings. I'm going to go down to project
space and in space I'm going to select
development. Which in HANA Express which
is what I'm using here, is the default
space. Now you need to make sure that
you've enabled that space to work in
your tenant database.
You've got other videos that cover that
process. So let's save that and close. Now
if we actually want to go ahead and look
at some of this. The first thing we
should look at is maybe the tables hdbcds.
This is where we're going to set up
our data we're going to work with in
this example and you can see we've
actually got some tables and we've got
some table types. Now maybe it's a little
easier if we want to just open it with a
code editor. So we can see in more detail
what we've got. So we're creating some
table types which we're going to use as
input to a stored procedure. So TTData,
TTParams for parameters and TTResults
for our results. In fact it's the very
same clustering example that we ran in
the previous video, R 09 to do
clustering. It’s just this time we've
adapted it for XS Advanced. Then we've
got the actual data we're working with.
So in here we've created an entity
called customers with the columns that
I've got in there. Entity called
parameters. Entity called results. So this
allows us to basically create tables or
views whatever we're going to do. So
that's the the first thing we're going
to work with. Now just to save you effort
and time actually used, a quite nice
feature here to get data and embed data
into application without having to load
it and running SQL in Database
Explorer. What we can do is go and use
something called HDB table data and so
for the customers table and also for the
parameters table but a little bit of
JSON syntax here. Which allows us to
basically import from a CSV file. So when
we build this it will actually do the
import of data from a CSV file. You can
see I've got my customers data in CSV
format.
Now I've done exactly the same thing for
the parameters. Again to make it faster
and easier for you to get up and running
in the real world you might do this a
different way. So I'm going to say I'm
going to have a name value pair. Clusters
as the name and I want to create three
clusters when I do my clustering. So
again, of course we're working
always with tables when we're talking
about the R Integration for HANA.
Tables for all input parameters, input
data. Tables for all results that you get
back from R
integration as well. Okay so that's all
now pretty much set up. What we can do is
actually just go ahead and build that. So
I'm going to go to the database module
of my multi target application and
choose build. This will take a couple of
seconds. So it's now built successfully.
We're almost ready to get started. Now
there's one other thing we need to do.
Now we've actually built this you'll see
that a HDI container called R App
_ HDI _ container
_ one has been created. Of
course you can customize the name. It’s
an option when you create a database
module. Well that's what we're using in
this case. Now we need to make sure that
this container can access the remote
source and the way it works with the
syntax of remote sources is you need to
actually do a little bit of syntax. So
we've got an authorized remote source
SQL entry in our project. Now it's not
actually gonna be running this project.
Just a way to give you the source code
more easily. So you can copy and paste it.
What we need to do is run this code
against the tenant database as a system
user to the tenant. In our case in HANA
Express its HXE. We're going to run this
code so that the remote source we
created called RSERVE is then
assigned as being accessible by the HDI
object, HDI Container object owner and
then that means that when we actually
want to do some testing within this
XS Advanced Web IDE for HANA
landscape we'll be able to access that
remote R server. So I'm just going to
copy that code. I'm going to go back to
Database Explorer. We’re in previous
videos we actually connected to the
system. Now you need to do this as a
system user because only the system user
has the right to grant this at this
point. So I'm gonna do a SQL window
as a system user. Paste in this line.
Now we know this is the name of the
container that's created because we just
check that in the log but of course in
your case if you created an application
with a different name or different
options in the database module then your
container
name would be different. So you need to
check that and we've got pound or hash
OO because it's the object owner
technical user that gets created that
works alongside that HDI Container. So
let's go ahead and run that and it's now
done. It’s complete. So we're good to go.
We can go back to Web IDE for HANA.
Actually just before we do that why
don't we open up and look at the HDI
Container. So let's click plus sign and
we can actually go and we get a list of
HDI Containers by default and this is
our one our R app HDI Container. So I’ll
connect to it and if I look there we
should find that we've got some tables.
So customers, parameters and results
tables and let's just check that we've
actually got our data loaded correctly.
Yes we have. We've got our customer data
and we've got our parameters. Again, yep
with one row. Clusters value three and
our results table exists but it should
be empty. Okay so now I think we're ready
to go back to Web IDE for HANA to look
at our project. Now there's one more
thing we need to do and that's to look
at this entry here which is called
cluster.hdb procedure. So one of the
options you can do when you're creating
modules or artifacts, new there are lots
of different things you can do and one
of them is to create stored procedures
or procedure. So this is the equivalent
of actually having done that but it's
there to save you copying and pasting
all the code. Now if we go and have a
look at it you'll see it's got some
interesting syntax. It’s actually
basically the same syntax that we used
in regular SQL script in the previous
video to do clustering. The difference is
that we've taken the create statement
out of the procedure because you take
the create out when you're working with
HDB procedures and we've changed the
naming to be basically working with XS
Advanced. Naming HDI Container naming. So
we have the project name, the module name
: : and then actually the name
of our entry which in this case is
cluster. So we've called it cluster. Again
for the tables, the input and output
tables we’ve got the project name, module
name : : and then the
context name that was defined in
the CDS entry and then we've got the
table types that we've referenced. We’re
going to specify languages RLang not
SQL script being the default. So RLang
because it's going to be R script
put in there and then what's in here
is exactly the same as we had previously
when we were running with a standard
SQL script. So once you've done it in
standard SQL script it's very easy to
integrate into an XS Advanced
application. Now we actually did the
build of the project. So this is already
built. So we should be able to go out and
actually test it. If we just go back to
Database Explorer and go to procedures
within our HDI Container. We can see that
that cluster procedure exists. So I'm
going to right mouse button on it and
choose generate call statement because
we're going to need some parameters on
it. We need at least two. So I've got the
call statement. You can see it says call
and then the HDI Container name which is
actually the schema name here. So RAPP
_ HDI container
_ 1. Then the name of our stored
procedure and we need to specify the
input data and the parameters. So for
that I'm just going to copy and paste to
save a little effort. So we've got the
names. In fact there's another way we can
do it maybe a little easy. We built it to
drag and drop. So for the data and the
parameter tables. Let's go back to the
list of tables. I've got the customers
table as my input. So I can drag that up
to here and the parameters table. So
let's put the cursor there and drag and
drop and we should be good to go. This
time we're running in line. So the
results we can get in line. So let's go
ahead and run it and we can see it runs
and we've got our clustering results
available. We can see we've got our
results shown in line. Now of course if
you want to actually populate that
results table which we'll see it's still
empty. If you want to populate that
results table then what we can do is
also drag and drop that and put that in
directly into our call. Let’s take the
results table and we also need to add
just before the end
of this procedure we need to say with
overview. Which means we're going to run
it and actually actually have tables. All
tables for inputs and outputs. So let's
run that again. We can see it runs.
We've got output here in line basically
saying the results table is this table
and it's just now been populated and
if we go back to our results that we did
open data on and we refresh it we should
found we've now got our clustering data
in there. So that's really all there is
to it. It’s very, very straightforward.
When you want to actually work in a Web
IDE for HANA and XS Advanced you
just need to create an HDB procedure
artifact and then paste in your R
script and work with the tables using
the syntax. The slightly tricky bit is that
you must remember in actually to be able
to run this that you've got to authorize
that remote source. So as we saw for the
container name which will be different
for your own applications make sure that
you actually assign the remote source
that's being used. The actually the
name of the remote source that you set
up in the getting started for accessing
the remote R server. Make sure that
you've specified that the HDI Container
owner is actually has got access to the
remote source and also we remember we
saw in the getting started, but we need
to just go back. Let's just double check
it here in R Integration. Code snippets
and getting started with a HANA 2 SPS02.
Remember that we did do this line 18
grant create R script to the SYS DI
DEFAULTS with admin option. That's very
important as well. Otherwise you're going
to get into authorizations challenges. So
that's it. What we've seen in this video
is how we can work with R integration
for HANA
but do that from a XS Advanced
application working with Web IDE for
HANA as our tooling. You can find more
HANA video tutorials on our YouTube
channel. If you'd like to be informed as
soon as new video tutorials are
published please subscribe to the
channel. You can also connect with us on
LinkedIn, follow us on Twitter and we
have pages on Facebook and
Google+. Code snippets and demo data
for all of our tutorials are published
to GitHub. Thanks for watching.