Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL. – Part16.

Today I’m going to publish the blogpost, which has been written by Javier De La Torre Medina. Thanks to him for the great research! All below is his article.

Oracle Big Data SQL over complex data types in Oracle NoSQL

When working with Oracle NoSQL databases, we have the flexibility to choose complex data types like arrays, records and map. In this example we are going to show you how to use Oracle Big Data SQL over these complex data types.

Let’s use one Oracle NoSQL table included with the virtual machine Oracle Big Data Lite. This table it’s called: movie. This table includes simple data types like: string, integer, etc. but the last column is an array data type. On the Oracle NoSQL database, we can see the description of the table. We are going to focus only on the array:

kv-> show table -name movie

…….

{

“name” : “genres”,

“type” : “ARRAY”,

“collection” : {

“name” : “RECORD_gen”,

“type” : “RECORD”,

“fields” : [ {

“name” : “cid”,

“type” : “STRING”,

“nullable” : true,

“default” : null

}, {

“name” : “id”,

“type” : “INTEGER”,

“nullable” : true,

“default” : null

}, {

“name” : “name”,

“type” : “STRING”,

“nullable” : true,

“default” : null

} ]

},

Let’s have a look into the data.

kv-> get table -name movie 

17 Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL.   Part16.

We can see the array as the last column. Let’s create a Hive table on top of the Oracle NoSQL table. 

18 Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL.   Part16.

Now we can run a simple query to see if works: 

19 Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL.   Part16.

And also we can query the array directly: 

20 Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL.   Part16.

Now we can create the Oracle Big Data SQL table on top. On the following documentation link, we can see how is the mapping between different data types are done: http://docs.oracle.com/cd/NOSQL/html/examples/hadoop/hive/table/package-summary.html#ondb_hive_ora_data_model_mapping_table

We will have to create the table as varchar2 data type and we will define that GENRE column is an array. Here is the code to create the table: 

21 Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL.   Part16.

Now let’s run some queries. Let’s run a simple one, let’s query two columns to see how the data looks like: 

22 Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL.   Part16.

Let’s query over the GENRES column. In this case we will use the JSON_QUERY operator. This operator always returns a JSON, like an object or an array. Oracle Database 12c can work natively with JSON, so we will be able to query and select the field we want. As a final example, let’s query over the name field:

23 Big Data SQL Quick Start. Big Data SQL over complex data types in Oracle NoSQL.   Part16.

Let’s block ads! (Why?)

The Data Warehouse Insider