X

Information, tips, tricks and sample code for Big Data Warehousing in an autonomous, cloud-driven world

Big Data SQL Quick Start. Big Data SQL over nested tables in Oracle NoSQL. - Part15.

Alexey Filanovskiy
Product Manager

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.

 

Big Data SQL over nested tables in Oracle NoSQL.

In the Oracle NoSQL database, customers can take advantage of the table model. The table model simplifies application data modeling by leveraging existing customer skills: SQL. The table model is built on top of the distributed key-value structure, inheriting all its advantage, and using AVRO schemas, which compress very well using less CPU and storage than JSON. Here we have an example of how to create a table in Oracle NoSQL. 

Oracle NoSQL Database tables can be organized in a parent/child hierarchy. We can create tables on inside other tables. Here we have another example:

 

We create the parent table: myInventory 

 

 

Then we can create the child or nested table: itemDetails 

 

 

 

When we create the child table, it inherits the parent table’s primary key. Therefore, the itemDetails table has two primary keys: itemCategory and itemSKU. Here you have a visual representation of the nested tables: 

 

Working with Big Data SQL.

To get a better understanding about how Oracle NoSQL works with Big Data SQL, you can start reading this blog post from Alexey here: https://blogs.oracle.com/datawarehousing/entry/big_data_sql_quick_start8

With Oracle Big Data SQL, you can take advantage of the predicate pushdown. You can send the query to the Oracle NoSQL database, and you will get the results very fast thanks to the key-value structure.

We are going to do a demo about how this works. First of all, let’s have a look into the data. Here we have a few documents for fleet:

{"vin":"023X43EKB0ON212J84F6","make":"FORD","model":"F150","year":2010,"fuelType":"G","vehicleType":"TRUCK"}

{"vin":"10W0251I02U4ILS32K25","make":"FORD","model":"F150","year":2010,"fuelType":"G","vehicleType":"TRUCK"}

{"vin":"5I4P0L132Q3518XOFVV3","make":"NISSAN","model":"PATHFINDER","year":2013,"fuelType":"G","vehicleType":"SUV"}

We have more data for each car, which is about mileage data. Here you can have a look:

{"vin":"023X43EKB0ON212J84F6","currentTime":10001,"driverID":"X6712184","longitude":58.0,"latitude":75.0,"odometer":7,"fuelUsed":0.37562913,"speed":70}

{"vin":"02O1J1O1O3545Z6682NB","currentTime":10001,"driverID":"F1605891","longitude":175.0,"latitude":30.0,"odometer":8,"fuelUsed":0.3891571,"speed":80}

{"vin":"04E0ZT2V10P25D71W703","currentTime":10001,"driverID":"P3433939","longitude":105.0,"latitude":140.0,"odometer":6,"fuelUsed":0.1807928”speed":60}

As you can see, they have in common the “vin” column. This will be used for the nested tables. Let’s create the first table for fleet. We will define the “vin” column as primary key. 

 

Next let’s create the nested table mileage. 

 

 

Finally, let’s insert the data shown before. 

 

 

Now let’s run some queries over the tables. In the first query we are going to query the fleet table over the primary key: 

 

 

We are getting just one result. Now let’s see what happens when we run the same query but over the mileage table: 

 

 

Here we can see the hierarchy 1 to N between the nested tables. Finally, if we query the mileage table with the two primary keys, this is the result: 

 

 

Once we have the Oracle NoSQL tables and the data inserted and tested, let’s create the Hive tables on top of the NoSQL tables. We will create the fleet hive table over the fleet table in NoSQL. 

 

 

Then we will create the mileage table over the child table. 

 

 

Once we have the hive tables, we are able to create the tables for the Oracle Database access. We will create Oracle Database external tables over the Hive tables. 

 

 

Once we had created the tables, we are able to query efficiently the data through Oracle Big Data SQL as it is taking advantage of the Oracle NoSQL nested tables. 

 

 

 

 

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.