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

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. 

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

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 

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

Then we can create the child or nested table: itemDetails 

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

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: 

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

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.

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

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. 

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

Next let’s create the nested table mileage. 

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

Finally, let’s insert the data shown before. 

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

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: 

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

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

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

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: 

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

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. 

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

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

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

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. 

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

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

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. 

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

Let’s block ads! (Why?)

The Data Warehouse Insider