Showing posts with label JSON. Show all posts
Showing posts with label JSON. Show all posts

Monday, July 13, 2015

V506 of Oracle OBIEE SampleApp Virtual Machine

A few days ago Oracle released the latest version of the Virtual Machine for OBIEE SampleApp. The current version has a number of new features and new product versions (see below).

To get this latest version go to the following link to download the VM files and to install. As always this is a beast of a VM and you should only consider the install and setup if you have the space and in particular you have 16G RAM.

Oracle Business Intelligence Enterprise Edition Samples on OTN.


v506 New Features

  • DB with the In-Memory option
  • Load and process JSON data
  • Integrates with Big Data SQL
  • Connects to Impala
  • Session tracking in UT
  • Exalytics Aggregation Functions
  • Lots of new Visualizations
  • Custom Style Features
  • Hierarchical Session Variables
  • etc.

Software on V506

  • Oracle Enterprise Linux 6.5 x64
  • OBIEE two distinct OBIEE instances, Essbase, updated BIMAD
  • Oracle MapViewer11.
  • Oracle BICS Data Sync v1
  • Oracle Database 12c IMDB, PDB Install, AWM, APEX 4.2.6 & ORDS 2.0.1, ODM, Oracle Spatial and Graph
  • ORE 1.4.1 & R 3.1.1
  • ENDECA 3.1, Server 7.6.1, Studio 3.1, Provisioning Services
  • Cloudera CDH 5.1.2, Oracle BigData SQL, Oracle BigData Connectors
  • Plug and Play Companions : EPM, BIApps Demos
  • Utils: Start scripts, MapBuilder, SQLDev 4.1

Thursday, May 14, 2015

Extracting Oracle data & Generating JSON data file using ROracle

In a previous blog post I showed you how to take a JSON data file and to load it into your Oracle Schema using R. To do this I used ROracle to connect to the database and jsonlite to do the JSON processing of the data.
Alternatives to using ROracle would be RODBC, RJDBC and DBI. So you could use one of these to connect to the database.
In this post I want to show you how to extract data from an Oracle table (or view) and to output it to a file in JSON format. Again I will be using the jsonlite R package to perform all the JSON formatting work for me.
1. Connect to the Database
This is the same connect setup that I used in the previous post.
# initialise the packages
> library(ROracle)
> library(jsonlite)
# Create the connection string
> drv <- dbdriver="" p="" racle="">
> host <- localhost="" p="">
> port <- 1521="" p="">
> service <- p="" pdb12c="">
> connect.string <- p="" paste="">
"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",
"(CONNECT_DATA=(SERVICE_NAME=", service, ")))", sep = "")
# establish the connection
> con <- dbconnect="" dbname="connect.string)" drv="" p="" password="dmuser" username="dmuser">

2. Read the data from the table/view
Read the data from the table into a R data frame.
> rs <- con="" dbsendquery="" from="" mining_data_build_v="" p="" select="">
> data <- fetch="" p="" rs="">
> dim(data)
[1] 1500 18
> head(data, 3)
1 101501 F 41 NeverM United States of America
2 101502 M 27 NeverM United States of America
3 101503 F 20 NeverM United States of America
1 J: 190,000 - 249,999 Masters Prof. 2 4 0
2 I: 170,000 - 189,999 Bach. Sales 2 3 0
3 H: 150,000 - 169,999 HS-grad Cleric. 2 2 0
1 1 1 1 1
2 1 1 0 1
3 1 0 0 1
1 1 0 0
2 1 1 0
3 1 1 0

We now have the data from the table in a data frame called data. We can now use this data frame to covert the data into JSON.
3. Convert into JSON format
To produced the JSON formatted output of the data in our table (or view) we can use the toJSON function that produces the outputted JSON data in an R String.
> jsonData <- data="" p="" tojson="">
> jsonData

4. Create the JSON file
We are now ready to output the formatted JSON data out to file. We can use the R function 'write' to write the JSON data out to a file.
> write(jsonData, file="c:/app/demo_json_data2.json")
Job Done!
5. Verify the JSON data was created correctly
To verify that JSON data file was created correctly, we can use the steps outlined in my previous post to read in the file. If all the correct then we should get no errors.
> jsonFile <- app="" c:="" demo_json_data2.json="" p="">
> jsonData <- fromjson="" jsonfile="" p="">
> str(jsonData)
> names(jsonData)
> nrow(jsonData)

You will notice that there is one difference between the code shown above and what I showed in my previous example/blog post. This time we don't have an extra wrapper class of Items.
Generating JSON data - Using SQL Developer
In my previous post I showed you one way of generating a JSON file based on the data in a table. You could do that using SQL Developer and SQLcl.
An alternative is to use the Table Export feature to export the data in JSON format.
To do this right click on the table (or view) and select Export from the drop down menu.
The Export Wizard will open. De-select the Export DDL tick box. In the export data section change the format drop-down to JSON. Then enter the location and file name for the JSON file. Then click the next buttons until you are finished.
Blog json

Friday, May 8, 2015

Loading JSON data into Oracle using ROracle and jsonlite

In this post I want to show you one way of taking a JSON file of data and loading it into your Oracle schema using ROracle. The JSON data will then be used to create a table in your schema. Yes you could use other methods to connect to the database and to create the table. But ROracle is by far the fastest method of connecting, selecting and processing data.

1. Necessary R Packages

You will need two R library. The first of these is the ROracle package. This gives us all the connection and data processing commands to work with the Oracle database. The second package is the jsonlite R package. This package allows us to open, read and process a file that has JSON data.

> install.package("ROracle")

> install.package("jsonlite")

After you have installed the packages you can now load them into your R environment so that you can use them in your current session.

> library(ROracle)

> library(jsonlite)

Depending on your version of R you may get some working messages about the libraries being built under a different version of R. Then again maybe you won't get these :-)

2. Open & Read the JSON file in R

Now you are ready to name and open the file that contains your JSON data. In my case the file is called 'demo_json_data.json'

> jsonFile <- "c:/app/demo_json_data.json"

> jsonData <- fromJSON(jsonFile)

We now have the JSON data loaded into R. We can now look at the attributes of each JSON record and the number of records that was in the JSON file.

> names(jsonData$items)

[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"

> nrow(jsonData$items)

[1] 1500

As you can see the records are grouped under a higher label of 'items'. You might want to extract these records into a new data frame.

> data <- jsonData$items


Now we have our data ready in a data frame and we can use this data frame to create a table and insert the data.

3. Create the connection to the Oracle Schema

I have a previous post on connecting to an Oracle Schema using ROracle. That was connecting to an 11g Oracle Database.

JSON is a new feature in Oracle 12c and the connection details are a little bit different because we are now having to deal with connection to a pluggable database. The following illustrates connecting to a 12c database and assumes you have Oracle Client already installed and configured with your tnsnames.ora entry.

# Create the connection string

> host <- "localhost"

> port <- 1521

> service <- "pdb12c"

> connect.string <- paste(


"(ADDRESS=(PROTOCOL=tcp)(HOST=", host, ")(PORT=", port, "))",

"(CONNECT_DATA=(SERVICE_NAME=", service, ")))", sep = "")

> con2 <- dbConnect(drv, username = "dmuser", password = "dmuser",dbname=connect.string)


4. Create the table in your Oracle Schema

At this point we have our connection to our Oracle Schema setup and connected, we have read in the JSON file and we have the JSON data in a data frame. We are now ready to push the JSON data to a table in our schema.

> dbWriteTable(con, "JSON_DATA", overwrite=TRUE, value=data)

Job done :-)

The table JSON_DATA has been created and the data is stored in the table in typical table attributes and rows format.

One thing to watch our for with the above command is with the overwrite=TRUE parameter setting. This replaces a table if it already exists. So your old data will be gone. Be careful.

5. View and Query the data using SQL

When you now log into your schema in the 12c Database, you can now query the data in the JSON_DATA table. (Yes I know it is not in JSON format in this table).


How did I get/generate my JSON data?

I generated the JSON file using a table that I already had in one of my schemas. This table is part of the sample data set that is built on top of the Oracle sample schemas.

The image below shows the steps involved in generating the data in JSON format. I used SQL Developer and set the SQLFORMAT to be JSON. I then ran the query to select the data. You will need to run this as a script. Then copy the JSON data and paste it into a file.


The SQL FORMAT command sets the output format for a query back to the default query output format that we are well use to.

A nice little JSON viewer can be found at

Copy and paste your JSON data into this and you can view the structure of the data. Check it out.