on 09-15-2014 3:03 PM
Dear All
We installed R and RServe on a server and connected our SAP HANA to it. The next thing we want to do is wrap our R-scripts in a SAP HANA procedure. Such a procedure accept table types and physical tables as input and output parameters. Unfortunately I can't get it to work. I know the SAP HANA R Integration Guide contains an example. But it uses a package called kernlab and I don't want to do that.
What I want to know is, how and when to use table types or physical tables. Preferably with an example showing how to store the results of the following two R-commands: library() and R.Version().
Any help is greatly appreciated.
Kind regards, Robbert
Message was edited by: Tom Flanagan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Henrique
Thank you for your answer. Those are two good links. Unfortunately I already knew them. Alvaro's scripts are to complex for a Hello world script and they don't explain the pros and cons of table types versus physical tables. When I'm sufficient desperate, I will use his scripts and then start tinkering. But this will take more time, and time is realy scarce. So that's why I'm looking for a basic Hello world script.
Regards, Robbert
I think that question is rather generic to any procedure in HANA, not necessarily RLang procedures.
Check for example the section 6.2.1 in the HANA Developer Guide:
6.2.1 Define Local Table Types in Procedures
You can use table types to define parameters for a procedure, which represent tabular results. These
parameters have a type and are either based on a global table (with a reference to a catalog table), global table
types, or a local table type.
Source: http://help.sap.com/hana/SAP_HANA_Developer_Guide_en.pdf
Hello Henrique
Indeed, this is a generic question. But it is about a specific subject. Working with table types differs hugely from working with physical tables. Look at table types as boxes for input and output parameters. The RLang procedure has to write all the answers (tables, scalars, ...) to the output box. When the RLang procedure is finished I have to extract the answers from this output box. When you use physical tables, you might expect to write the answer directly to a physical table. Wrong. So yes, this is a generic question about a specific subject.
Kind regards, Robbert
Robert - my feedback would be this is a very generic question which seeks very specific advice. That's quite a tall ask. Perhaps if you could explain the context to your question and your requirements, you would get some more replies?
In general my approach is to wrap R with local table variables and to pass these through. Then we use the local table variable with a global table type as the output parameter for the outer SQLScript procedure.
More often than not we then copy and paste the SQLScript procedure into a Scripted Calculation View and use the native table type parameters in there to create a view that can be queried by external tools.
Hope this helps.
There is no real definitive answer. If you have a table with the metadata you need use, if not, you can create just a table type if you know you only need the table type definition. It's all about options. John also gave some more tips on what direction you might wanna go, given the fact that you will want to wrap your proc as a calc view (or not).
John and Henrique, thank you both (again) for your answers.
The background is simple, we want to offer RStats to our analysts. But before we do, we want to make sure it works. And I want to make sure I can answer the questions our analysts are going to ask me. They know everything about RStats, they just don't know how to use it in an SAP HANA environment. So that's why I'm looking for a 'Hello World' script and a list of dos and don'ts. The answers might come from people already experienced with both SAP HANA and RStats, or it might be a referral to some documentation or a blog. As stated before, any help is appreciated.
Kind regards, Robbert
There is (unfortunately) nothing like that available out-of-the box.
But it is pretty straight forward to create such a test, even "simple enough for a a basis person".
(doesn't sound as if you think highly of them... )
create type RSERVEINFO AS TABLE (
"R.version.platform" NVARCHAR (256),
"R.version.arch" NVARCHAR (256),
"R.version.os" NVARCHAR (256),
"R.version.system" NVARCHAR (256),
"R.version.status" NVARCHAR (256),
"R.version.major" NVARCHAR (256),
"R.version.minor" NVARCHAR (256),
"R.version.year" NVARCHAR (256),
"R.version.month" NVARCHAR (256),
"R.version.day" NVARCHAR (256),
"R.version.svn.rev" NVARCHAR (256),
"R.version.language" NVARCHAR (256),
"R.version.version.string" NVARCHAR (256),
"R.version.nickname" NVARCHAR (256));
drop procedure testR;
create procedure testR (OUT rinfo RSERVEINFO)
LANGUAGE R
READS SQL DATA AS
BEGIN
si <- sessionInfo()
si_df <- as.data.frame (si[1])
rinfo <- si_df
END;
call testR(?);
R.version.platform | R.version.arch | R.version.os | R.version.system | R.version.status | R.version.major | R.version.minor | R.version.year | R.version.month | R.version.day | R.version.svn.rev | R.version.language | R.version.version.string | R.version.nickname |
x86_64-unknown-linux-gnu | x86_64 | linux-gnu | x86_64, linux-gnu | 2 | 15.3 | 2013 | 03 | 01 | 62090 | R | R version 2.15.3 (2013-03-01) | Security Blanket |
I'd say that's simple enough for any person required to perform DBA work on a SAP HANA instance.
- Lars
Thank you Lars.
First to clarify "simple enough for a basis person" .. "doesn't sound as if you think highly of them". Quite the opposite. I have a basis/technical background myself.
I meant it from a perspective I would not want a basis person to have to worry about complicated code, they have enough to deal with already. The code examples out there are all too programmer orientated.
Pity there isn't build it procedures for this already. However, the example you provided is perfect for these purposes. I'll save it somewhere.
Once created I guess we can leave it there for future use (help troubleshooting).
- Jacques
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.