1. Developers Guide
  2. Get Started
  3. Getting started with SQL for Oracle NoSQL Database

Getting started with SQL for Oracle NoSQL Database

Welcome to SQL for Oracle NoSQL Database. This language provides a SQL-like interface to Oracle NoSQL Database. The SQL for Oracle NoSQL Database data model supports flat relational data, hierarchical typed (schema-full) data, and schema-less JSON data. SQL for Oracle NoSQL Database is designed to handle all such data seamlessly without any impedance mismatch among the different sub-models. Impedance mismatch is the problem that occurs due to differences between the database model and the programming language model.

Pre-requisites: You already have an installation of the Oracle NoSQL Database. You could also use KVLite which is a simplified version of the Oracle NoSQL Database.

Schemas used in the examples

You have two different schemas ( with real-time scenarios) for learning various SQL concepts. These two schemas will include various data types that can be used in the Oracle NoSQL database.

Schema 1: BaggageInfo schema

Using this schema you can handle a use case wherein passengers traveling on a flight can track the progress of their checked-in bags or luggage along the route to the final destination. This functionality can be made available as part of the airline's mobile application. Once the passenger logs into the mobile application, the ticket number or reservation code of the current flight is displayed on the screen. Passengers can use this information to search for their baggage information. The mobile application is using NoSQL Database to store all the data related to the baggage. In the backend, the mobile application logic performs SQL queries to retrieve the required data.

Schema 2: Streaming Media Service - Persistent User Profile Store

Consider a TV streaming application. It streams various shows that are watched by customers across the globe. Every show has a number of seasons and every season has multiple episodes. You need a persistent meta-data store that keeps track of the current activity of the customers using the TV streaming application. Using this schema you can provide useful information to the customer such as episodes they watched, the watch time per episode, the total number of seasons of the show they watched, etc. The data is stored in the NoSQL Database and the application performs SQL queries to retrieve the required data and make it available to the user.

Starting the SQL shell

You can run SQL queries and run DDL statements directly from the SQL shell. Here is the general usage to start the shell: 
java -jar KVHOME/lib/sql.jar
       -helper-hosts <host:port[,host:port]*> 
       -store <storeName>
       [-username <user>] 
       [-security <security-file-path>]
       [-timeout <timeout ms>]
       [-consistency <NONE_REQUIRED(default) | ABSOLUTE | NONE_REQUIRED_NO_MASTER>]
       [-durability <COMMIT_SYNC(default) | COMMIT_NO_SYNC | COMMIT_WRITE_NO_SYNC>]
       [single command and arguments]

where:

-consistency Configures the read consistency used for this session.

-durability Configures the write durability used for this session.

-helper-hosts Specifies a comma-separated list of hosts and ports.

-store Specifies the name of the store.

-timeout Configures the request timeout used for this session.

-username Specifies the username to login as.

For example, you can start the shell like this:
java -jar KVHOME/lib/sql.jar -helper-hosts node01:5000 -store kvstore
sql->

This command assumes that a store kvstore is running at port 5000. After the SQL starts successfully, you run queries.

Tables used in the examples

The table is the basic structure to hold user data.

Schema 1: BaggageInfo schema

The table used in this schema is BaggageInfo. This schema has a combination of fixed data types like LONG, STRING. It also has a schema-less JSON (bagInfo) as one of its columns. The schema-less JSON does not have a fixed data type. The bag information of the passengers is a schema-less JSON. In contrast, the passenger's information like ticket number, full name, gender, contact details is all part of a fixed schema. You can add any number of fields to this non-fixed schemaless JSON field. .

The following code creates the table.
CREATE TABLE BaggageInfo (
ticketNo LONG,
fullName STRING,
gender STRING,
contactPhone STRING,
confNo STRING,
bagInfo JSON,
PRIMARY KEY (ticketNo)
)

Schema 2: Streaming Media Service - Persistent User Profile Store

The table used in this schema is stream_acct. This schema has a composite primary key column comprised of acct_id and user_id.The schema also includes a JSON column (acct_data), which is schema-less. The schema-less JSON does not have a fixed data type. You can add any number of fields to this non-fixed schemaless JSON field.

The following code creates the table.
CREATE TABLE stream_acct(
acct_id INTEGER,
acct_data JSON, 
PRIMARY KEY(acct_id)
)

Describe tables

You use DESCRIBE or DESC command to view the description of a table.
(DESCRIBE | DESC) [AS JSON] TABLE table_name [ "(" field_name")"]

AS JSON can be specified if you want the output to be in JSON format. You could get information about a specific field in any table by providing the field name.

Example 1: Describe a table
DESCRIBE TABLE stream_acct
Output:
=== Information ===
 +-------------+-----+-------+----------+--------+----------+---------+---------+-------------+
 |    name     | ttl | owner | sysTable | parent | children | regions | indexes | description |
 +-------------+-----+-------+----------+--------+----------+---------+---------+-------------+
 | stream_acct |     |       | N        |        |          |         |         |             |
 +-------------+-----+-------+----------+--------+----------+---------+---------+-------------+
 === Fields ===
 +----+-----------+---------+----------+---------+----------+------------+----------+
 | id |   name    |  type   | nullable | default | shardKey | primaryKey | identity |
 +----+-----------+---------+----------+---------+----------+------------+----------+
 |  1 | acct_id   | Integer | N        | NULL    | Y        | Y          |          |
 +----+-----------+---------+----------+---------+----------+------------+----------+
 |  2 | acct_data | Json    | Y        | NULL    |          |            |          |
 +----+-----------+---------+----------+---------+----------+------------+----------+
Example 2: Describe a table and display the output as JSON
DESC AS JSON TABLE BaggageInfo
Output: 
{
  "json_version" : 1,
  "type" : "table",
  "name" : "BaggageInfo",
  "fields" : [{
    "name" : "ticketNo",
    "type" : "LONG",
    "nullable" : false
  }, {
    "name" : "fullName",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "gender",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "contactPhone",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "confNo",
    "type" : "STRING",
    "nullable" : true
  }, {
    "name" : "bagInfo",
    "type" : "JSON",
    "nullable" : true
  }],
  "primaryKey" : ["ticketNo"],
  "shardKey" : ["ticketNo"]
}
Example 3: Describe one particular field of a table
DESCRIBE TABLE BaggageInfo (ticketNo)
Output:
 +----+----------+------+----------+---------+----------+------------+----------+
 | id |   name   | type | nullable | default | shardKey | primaryKey | identity |
 +----+----------+------+----------+---------+----------+------------+----------+
 |  1 | ticketNo | Long | N        | NULL    | Y        | Y          |          |
 +----+----------+------+----------+---------+----------+------------+----------+

Sample data to run queries

Schema 1: BaggageInfo schema

If you want to follow along with the examples, download the script baggageschema_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table. One sample row is shown below.
"ticketNo" : 1762344493810,
"fullName" : "Adam Phillips",
"gender" : "M",
"contactPhone" : "893-324-1064",
"confNo" : "LE6J4Z",
 [ {
    "id" : "79039899165297",
    "tagNum" : "17657806255240",
    "routing" : "MIA/LAX/MEL",
    "lastActionCode" : "OFFLOAD",
    "lastActionDesc" : "OFFLOAD",
    "lastSeenStation" : "MEL",
    "flightLegs" : [ {
      "flightNo" : "BM604",
      "flightDate" : "2019-02-01T01:00:00",
      "fltRouteSrc" : "MIA",
      "fltRouteDest" : "LAX",
      "estimatedArrival" : "2019-02-01T03:00:00",
      "actions" : [ {
        "actionAt" : "MIA",
        "actionCode" : "ONLOAD to LAX",
        "actionTime" : "2019-02-01T01:13:00"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "BagTag Scan at MIA",
        "actionTime" : "2019-02-01T00:47:00"
      }, {
        "actionAt" : "MIA",
        "actionCode" : "Checkin at MIA",
        "actionTime" : "2019-02-01T23:38:00"
      } ]
    }, {
      "flightNo" : "BM667",
      "flightDate" : "2019-01-31T22:13:00",
      "fltRouteSrc" : "LAX",
      "fltRouteDest" : "MEL",
      "estimatedArrival" : "2019-02-02T03:15:00",
      "actions" : [ {
        "actionAt" : "MEL",
        "actionCode" : "Offload to Carousel at MEL",
        "actionTime" : "2019-02-02T03:15:00"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "ONLOAD to MEL",
        "actionTime" : "2019-02-01T07:35:00"
      }, {
        "actionAt" : "LAX",
        "actionCode" : "OFFLOAD from LAX",
        "actionTime" : "2019-02-01T07:18:00"
      } ]
    } ],
    "lastSeenTimeGmt" : "2019-02-02T03:13:00",
    "bagArrivalDate" : "2019.02.02T03:13:00"
  } ]
Start your KVSTORE or KVLite and open the SQL.shell.
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
Using the load command, run the script.
load -file baggageschema_loaddata.sql

Schema 2: Streaming Media Service - Persistent User Profile Store

Download the script acctstream_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table. One sample row is shown below.
1,
{
   "firstName" : "John",
   "lastName" : "Sanders",
   "country" : "USA",
   "contentStreamed":  [
      {
        "showName" : "Casa de papel",
        "showId" : 26,
        "showtype" : "tvseries",
        "genres" : ["action", "crime", "spanish"], 
        "numSeasons" : 4,
        "seriesInfo": [
          {
            "seasonNum" : 1,
            "numEpisodes" : 2,
            "episodes": [
              {
                "episodeID": 20,
                "lengthMin": 85,
                "minWatched": 85,
                "date" : "2022-04-18"
              },
              {
                "episodeID": 30,
                "lengthMin": 60,
                "minWatched": 60,
                "date" : "2022-04-18"
              }
            ]
          },
          {
            "seasonNum": 2,
            "numEpisodes" : 4,
            "episodes": [
              {
                "episodeID": 40,
                "lengthMin": 50,
                "minWatched": 50,
                "date" : "2022-04-25"
              },
              {
                "episodeID": 50,
                "lengthMin": 45,
                "minWatched": 30,
                "date" : "2022-04-27"
              }
            ]
          }
        ]
      },
      {
        "showName": "Call My Agent",
        "showId": 15,
        "showtype": "tvseries",
        "genres" : ["comedy", "french"], 
        "numSeasons" : 2,
        "seriesInfo": [
          {
            "seasonNum" : 1,
            "numEpisodes" : 2,
            "episodes": [
              {
                "episodeID": 20,
                "lengthMin": 45,
                "minWatched": 45,
                "date" : "2022-03-07"
              },
              {
                "episodeID": 30,
                "lengthMin": 42,
                "minWatched": 42,
                "date" : "2022-03-08"
              }
            ]
          }
        ]
      }
    ]
  }
Start your KVSTORE or KVLite and open the SQL.shell.
java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore
Using the load command, run the script.
load -file acctstream_loaddata.sql

Table Hierarchies

The Oracle NoSQL Database enables tables to exist in a parent-child relationship. This is known as table hierarchies.

The create table statement allows for a table to be created as a child of another table, which then becomes the parent of the new table. This is done by using a composite name (name_path) for the child table. A composite name consists of a number N (N > 1) of identifiers separated by dots. The last identifier is the local name of the child table and the first N-1 identifiers point to the name of the parent.

Characteristics of parent-child tables:
  • A child table inherits the primary key columns of its parent table.
  • All tables in the hierarchy have the same shard key columns, which are specified in the create table statement of the root table.
  • A parent table cannot be dropped before its children are dropped.
  • A referential integrity constraint is not enforced in a parent-child table.

You should consider using child tables when some form of data normalization is required. Child tables can also be a good choice when modeling 1 to N relationships and also provide ACID transaction semantics when writing multiple records in a parent-child hierarchy.