ORE video : Demo Code Part 2
The following is the second set of demo code from my video on using R in the Oracle Database. Check out the video before using the following code. The blog post for the video will be updated to contain links to all blog posts that have the various demo code.
The following code gives a very quick demonstration of using the RORACLE R package to access the data in your Oracle schema. ROracle has a number of advantages over using RJDBC and most of the advantages are about the performance improvements. Typically when using ROracle you will see a many fold improvement with selecting data and moving it to your R client, processing data in the database and also writing data back to the Oracle Database. In some tests you can see a 7 times improvement in performance over RJDBC. Now that is a big difference.
But the problem with ROracle is that it is only available on certain platforms/OS. For example it is not officially available for the Mac. But if you google this issue carefully you will find unofficial ways over coming this problem.
ROracle is dependent on Oracle Client. So you will need to have Oracle Client installed on you machine and have it available on the search path.
When you have Oracle Client installed and the ROracle R package installed you are ready to start using it.
So here is the demo code from the video.
> library(ROracle) > drv # Create the connection string > host port sid connect.string "(CONNECT_DATA=(SID=", sid, ")))", sep = "") > con rs # fetch records from the resultSet into a data.frame > data # extract all rows > dim(data) [1] 6 1 > data VIEW_NAME 1 MINING_DATA_APPLY_V 2 MINING_DATA_BUILD_V 3 MINING_DATA_TEST_V 4 MINING_DATA_TEXT_APPLY_V 5 MINING_DATA_TEXT_BUILD_V 6 MINING_DATA_TEXT_TEST_V > dbCommit(con) > dbClearResult(rs) > dbDisconnect(con)
Oracle R Enterprise 1.5 (new release)
The Oracle Santa had a busy time just before Christmas with the release of several new version of products. One of these was Oracle R Enterprise version 1.5.
Oracle R Enterprise (1.5) is part of the Oracle Advanced Analytics option for the enterprise edition of the Oracle Database.
As with every new release of a product there are a range of bug fixes. But with ORE 1.5 there are also some important new features. These important new features include:
- New Random Forest specific for ORE.
- New ORE Data Store functions and privileges.
- Partitioning on multiple columns for ore.groupApply.
- Multiple improvements to ore.summary.
- Now performs parallel in-database execution for functions prcomp and svd.
- BLOB and CLOB data types are now supported in some of the ORE functions.
Check out the ORE 1.5 Release Notes for more details on the new features.
ORE 1.5 is only certified (for now) on R 3.2.x in both the open source version and the Oracle R Distribution version 3.2.
Check out the ORE 1.5 Documentation.
You can download ORE 1.5 Server side and Client side software here.
ORE Video : Demo Code part 1
In a previous blog post I posted a video on using R with the Oracle Database and using Oracle R Enterprise. This is a part 1 extension of that blog post that gives the first set of demo code.
This first set of demonstration code is for using RJDBC to connect to the Oracle Database. Using RJDBC relies on using the JDBC jar file for Oracle. It is easily found in various installations of Oracle products and will be called something like ojdbc.jar. I like to take a copy of this file and place it in the root/home directory.
> library(RJDBC)
> # Create connection driver and open
> connectionjdbcDriver jdbcConnection #list the tables in the schema
> #dbListTables(jdbcConnection)
> #get the DB connections details - it get LOTS of info - Do not run unless it is really needed
> dbGetInfo(jdbcConnection)
> # Query on the Oracle instance name.
> #instanceName #print(instanceName)tableNames print(tableNames)
> viewNames v names(v)
[1] "CUST_ID" "CUST_GENDER" "AGE"
[4] "CUST_MARITAL_STATUS" "COUNTRY_NAME" "CUST_INCOME_LEVEL"
[7] "EDUCATION" "OCCUPATION" "HOUSEHOLD_SIZE"
[10] "YRS_RESIDENCE" "AFFINITY_CARD" "BULK_PACK_DISKETTES"
[13] "FLAT_PANEL_MONITOR" "HOME_THEATER_PACKAGE" "BOOKKEEPING_APPLICATION”
[16] "PRINTER_SUPPLIES" "Y_BOX_GAMES" "OS_DOC_SET_KANJI"
> dim(v)
[1] 1500 18
> summary(v)
CUST_ID CUST_GENDER AGE CUST_MARITAL_STATUS COUNTRY_NAME
Min. :101501 Length:1500 Min. :17.00 Length:1500 Length:1500
1st Qu.:101876 Class :character 1st Qu.:28.00 Class :character Class :character
Median :102251 Mode :character Median :37.00 Mode :character Mode :character
Mean :102251 Mean :38.89
3rd Qu.:102625 3rd Qu.:47.00
Max. :103000 Max. :90.00
CUST_INCOME_LEVEL EDUCATION OCCUPATION HOUSEHOLD_SIZE YRS_RESIDENCE
Length:1500 Length:1500 Length:1500 Length:1500 Min. : 0.000
Class :character Class :character Class :character Class :character 1st Qu.: 3.000
Mode :character Mode :character Mode :character Mode :character Median : 4.000
Mean : 4.089
3rd Qu.: 5.000
Max. :14.000
> hist(v$RESIDENCE)
> hist(v$AGE)
> dbDisconnect(jdbcConnection)
Make sure to check out the other demonstration scripts that are shown in the video.
Running R in the Oracle Database video
Earlier this year I was asked by the Business Analysics & Big Data SIG (of the UKOUG) to give a presentation on Oracle R Enterprise. Unfortunately I had already committed to giving the same presentation at the OUG Norway conference on the same day.
But then they asked me if I could record a video of the presentation and they would show it at the SIG. The following video is what I recorded.
At the UKOUG annual (2015) conferences I was supposed to give a 2 hour presentation during their Super Sunday event. Unfortunately due to a storm passing over Ireland on the Saturday all flights going to the UK were cancelled. This meant that I would miss my 2 hour presentation.
Instead of trying to find an alternative speaker for my presentation slot at such sort notice, the committee suggested that they would show the video.
Based on the feedback and the people who thanked me in person during the rest of the conference, I’ve decided to make it available to everyone. Hopefully you will find it useful.
People have been asking me if the demo scripts I used in video are available. You will probably find some of these on various blog posts. So to make it easier for everyone I will post the demo scripts in one or more blog posts over the coming weeks. When these are available I will update this blog post with the links.
I have a few new presentations on Oracle R Enterprise in 2016 so watch out for these at an Oracle User Group conference.
KScope 2016 Acceptances
I’ve never been to KScope. Yes never.
I’ve always wanted to. Each year you hear of all of these stories about how much people really enjoy KScope and how much they learn.
So back in October I decided to submit 5 presentations to KScope. 4 of these presentations are solo presentations and 1 joint presentation.
This week I have received the happy news that 2 of my solo presentations have been accepted, plus my joint presentation with Kim Berg Hansen.
So at the end of June 2016 I will be making my way to Chicago for a week of Oracle geekie fun at KScope.
My presentations will be:
- Is Oracle SQL the best language for Statistic?
- Running R in your Oracle Database using Oracle R Enterprise
and my join presentations is called
Forecasting in Oracle using the Power of SQL (this will talk about ROracle, Forecasting in R, Using Oracle R Enterprise and SQL)
I was really hoping that one of my rejected presentations would have been accepted. I really enjoy this presentation and I get to share stories about some of my predictive analytics projects. Ah well, maybe in 2017.
The last time I was in Chicago was over 15 years ago when I sent 5 days in Cellular One (The brand was sold to Trilogy Partners by AT&T in 2008 shortly after AT&T had completed its acquisition of Dobson Communications). I was there to kick off a project to build them a data warehouse and to build their first customer churn predictive model. I stayed in a hotel across the road from their office which was famous because a certain person had stayed in it why one the run. Unfortunately I didn’t get time to visit downtown Chicago.
OUG Ireland 2015 : Call for Papers
We finally have confirmed dates for the OUG Ireland Annual Conference. It will be on the 3rd & 4th March, 2016.
In 2016 we are expanding the conference and marking it a 2 day conference. Over the past few years some of the feedback from delegates has been, can we have more sessions and add a second day. So we have listened and will now have a 2 day conference on 3rd & 4th March, 2016.
We also have a new venue too. The 2016 conference will be held in the Gresham Hotel, on O’Connell Street in central Dublin. It is so close to lots of the tourist attractions, lots of hotels and lots of entertainment.
The call for papers is now open and we are looking for presentations for the typical Business Analytics/Big Data, Hardcore DBA, Development, Cloud and Applications.
Do you have a story to share? Have you discovered something recently? Have you used a new product? Is there anything about some part of an Oracle Product you would like to share?
If so then click on the image below to go to the Submissions Website and get submitting. Closing date for submissions is 9am on 4th January.
Hopefully I’ll see you in Dublin in March.
OUG Ireland 2015 : Call for Papers
We finally have confirmed dates for the OUG Ireland Annual Conference. It will be on the 3rd & 4th March, 2016.
In 2016 we are expanding the conference and marking it a 2 day conference. Over the past few years some of the feedback from delegates has been, can we have more sessions and add a second day. So we have listened and will now have a 2 day conference on 3rd & 4th March, 2016.
We also have a new venue too. The 2016 conference will be held in the Gresham Hotel, on O’Connell Street in central Dublin. It is so close to lots of the tourist attractions, lots of hotels and lots of entertainment.
The call for papers is now open and we are looking for presentations for the typical Business Analytics/Big Data, Hardcore DBA, Development, Cloud and Applications.
Do you have a story to share? Have you discovered something recently? Have you used a new product? Is there anything about some part of an Oracle Product you would like to share?
If so then click on the image below to go to the Submissions Website and get submitting.
Hopefully I’ll see you in Dublin in March.
My presentations at UKOUG 2015
Video Posted on Updated on
The annual conferences of the UKOUG are coming up soon and I just wanted to give you a quick overview of the 3 presentations I will be giving at these conferences in Birmingham between Sunday 6th and Wednesday 9th December.
I’ll be making references to 50 Shades of Gray and Lord of the Rings in some of these presentations. Come along to find out why.
It all kicks off on the Sunday (12:30-14:20) with an almost 2 hour presentation titled ‘Predictive Analytics in Oracle: Mining the Gold & Creating Valuable Products’. We have being hearing about Predictive Analytics for a few years now. But most people are still unsure of what it really means and how it could be used in their company. This presentations aims to simplify what Predictive Analytics means and to illustrate how various companies from a wide range of industries have been using Predictive Analytics to gain deeper, valuable and actionable insights of their data. A number of case studies will be presented, based on some of my recent projects, illustrating how predictive analytics has been used to generate significant benefits for each company. These case studies will include examples from Banking, Telecommunications, Financial Fraud Detection, Retail and Merchandising. The second half of this seminar will look at what functionality is available from Oracle that allows you to build and deploy Predictive Analytics in your applications. We will at what you can do with SQL, PL/SQL, Oracle Data Miner, Oracle R Enterprise and Real-Time Decisions. We will then look at how you can build these features and tools into your front end applications. In the final section of the seminar we will look at what you need to do to get started with Predictive Analytics and how you can avoid some of the typical pitfalls.
On Monday 7th, I have a presentation between 9:00 and 9:50 titled ‘Is Oracle SQL the Best Language for Statistics?’. Did you know that Oracle comes with over 280+ statistical functions? and that these statistical functions are available in all versions of the Database? Most people do not seem to know this.When we hear about people performing statistical analytics we can hear them talking about Excel and R. But what if we could do statistical analysis in the database, without having to extract any data onto client machines. That would be really good and just think of the possible data security issues with using Excel, R and other tools.This presentation will explore the various statistical areas available in SQL and I will give a number of demonstrations on some of the more commonly used statistical techniques. With the introduction of Big Data SQL we can now use these same 280+ Oracle statistical functions on all our data including Hadoop and NoSQL. We can also greatly expand our statistical capabilities by using Oracle R Enterprise using the embedded capabilities in SQL.
My final presentation will on Wednesday (14:30-15:20) titled ‘Automating Predictive Analytics in Your Applications’. What does your Oracle DBA and Oracle Developer need to know to implement Data Mining? How can the Oracle DBA and Oracle Developer use Data Mining. This presentation will look at what the Oracle DBA and Oracle Developer needs to know to deploying, implementing and integrating the predictive models produced by the Data Miner. These predictive models can be used in multiple databases and across their many applications. Examples will be given of the typical tasks data mining task for the DBA and for the Oracle Developer. These examples will include SQL, PL/SQL and embedded Oracle R Enterprise. I will show how easy it is to build automated Predictive Analytics into, not only your Dashboards, but your everyday applications.
Here is a short video that gives an overview of my presentations for TECH15.
Error when trying to use GLM in ORE
If you have tried to use the ore.odmGLM function in ORE and some other functions in ORE and you are using the Oracle 12.1.0.2 Database you will be getting an error something like the following in R.
> GLMmodel <- ore.odmGLM(AFFINITY_CARD ~., ANALYTIC_RECORD) Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch, : ORA-29400: data cartridge error ORA-40024: solution to least squares problem could not be found ORA-06512: at "SYS.DBMS_DATA_MINING", line 2153 ORA-06512: at line 1
or you get something like the following when using ore.glm
Error in .External(C_pt, q, df, lower.tail, log.p) : NULL value passed as symbol address
Similarly if you are using these functions with embedded R execution when the R code is warpped in SQL, you will see similar errors.
What you need to do is to download and install the stand alond Patch 20173897.
Alternatively this patch is included in the latest Oracle 12.1.2 Database patch.
Slide from my OOW15 Presentations
At Oracle Open World (OOW15) I gave 2 presentations on the Sunday during the Oracle User Group Forum. The slides are now available for download from the Oracle Open World website.
Go get them now!
More Than Another 12 on Oracle Database 12c [UGF3190]
During this sessions I was one of 16 presenters talking about various features in the Oracle Database. All of the presenters where from the EOUC region.
Real Business Value from Big Data and Advanced Analytics [UGF4519]
I co-presented with Antony Heljula from Peak Indicators. During this presentation we talked about some of the Advanced Analytics projects we have worked on over the past 18-24 months. We also announced a new Analytics-as-a-Service offering.
The slides are also available for most of the other Oracle Open World Presentations and these can be accessed here. Just go search for the topic you are interested in.
Check out my previous blog post that summarises just a small part of what I got up to at OOW15.
Slide from my OOW15 Presentations
At Oracle Open World (OOW15) I gave 2 presentations on the Sunday during the Oracle User Group Forum. The slides are now available for download from the Oracle Open World website.
Go get them now!
More Than Another 12 on Oracle Database 12c [UGF3190]
During this sessions I was one of 16 presenters talking about various features in the Oracle Database. All of the presenters where from the EOUC region.
Real Business Value from Big Data and Advanced Analytics [UGF4519]
I co-presented with Antony Heljula from Peak Indicators. During this presentation we talked about some of the Advanced Analytics projects we have worked on over the past 18-24 months. We also announced a new Analytics-as-a-Service offering.
The slides are also available for most of the other Oracle Open World Presentations and these can be accessed here. Just go search for the topic you are interested in.
My OOW15 is not over
It seems to be traditional for people to write a blog post that summaries their Oracle Open World (OOW) experience. Well here is my attempt and it really only touches on a fraction of what I did at OOW, which was one of busiest OOWs I’ve experienced.
It all began back on Wednesday 21st October when I began my journey. Yes that is 9 days ago, a long long 9 days ago. I will be glad to get to home.
The first 2 days here in San Francisco was down at Oracle HQ were the traditional Oracle ACE Directors briefings are held. I’m one of the small (but growing) number of ACEDs and it is an honour he take part in the programme. The ACE Director briefings are 2 days of packed (did I say they are packed) full on talks by the leading VPs, SVPs, EVPs and especially Thomas Kurian. Yes we had Thomas Kurian come into talk to us for 60-90 minutes of pure gold. We get told all the things that Oracle is going to release or announce at OOW and for the next few months and beyond. Some of things that I was particularly interested in was the 12.2 database stuff.
Unfortunately we are under NDA for all of the stuff we were told, until Oracle announce it themselves.
On the Thursday night a few of us meet up with Bryn Llewellyn (the god father of PL/SQL) for a meal. Here is a photo to prove it. It was a lively dinner with some “interesting” discussions.
On the Friday we all transferred hotels into a hotel beside Union Square.
We had the Saturday free, and I’m struggling to remember what I actually did. But it did consist of going out and about around San Francisco. Later that evening there was a meet up arranged by “That” Jeff Smyth in a local bar.
Sunday began with me walking across (and back) the Golden Gate Bridge with a few other ACEDs and ACEs.
The rest of the Sunday was spent at the User Groups Forum. I was giving 2 presentations. The first presentation was part of the “Another 12 things about Oracle 12c”. For this session there was 15 presenters and we each had 7 minutes to talk about a topic. Mine was on Oracle R Enterprise. It was an almost full room, which was great.
Then I had my second presentation right afterwards and for that we had a full room. I was co-presenting with Tony Heljula and we were talking about some of the projects we have done using Oracle Advanced Analytics.
After that my conference duties were done and I got to enjoy the rest of the conference.
Monday and Tuesday were a bit mental for me. I was basically in sessions from 8am until 6pm without a break. There was lots of really good topics, but unfortunately there was a couple presentations that were total rubbish. Where the title and abstract had no relevance to what was covered in the presentation, and even the presentation was rubbish. There was only a couple of these.
Wednesday was the same as Monday and Tuesday but this time I had time for lunch.
As usual the evenings are taken up with lots of socials and although I had great plans to go to lots of them, I failed and only got to one or two each evening.
On Wednesday night we ended up out at Treasure Island to be entertained by Elton John and Becks, and then back into Union Square for another social.
Thursday was very quiet and as things started to wind down at OOW finishing up at 3pm. A few of us went down to Fishermans Wharf and Pier39 for a wander around and a meal. Here are the some photos from the restaurant.
Friday has finally arrived and it is time to go home. OTN are very generous and put on a limo for the ACEDs to bring us out to the airport.
Ora-lytics 
You must be logged in to post a comment.