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 

See Running the SQL Shell for the general usage and command options.

The baggage tracking application that we saw in Example 5-2 can also be realized using a parent child schema. For every flight ticket number, there is a passenger and their baggage associated with it. The root table is ticket, and it has 2 child tables passengerInfo and baggageInfo. The passengerInfo table contains the details of the passenger and the baggageInfo contains details of the bags checked in by the passenger. These bags are tracked through their transit through multiple intermediary stations. This tracking information is captured in a table called flightlegs which is the child of the baggageInfo table. Following are the tables created:

### CREATE table ticket if not present ###
CREATE TABLE IF NOT EXISTS ticket(
ticketNo LONG, 
confNo STRING, 
PRIMARY KEY(ticketNo))
 
### CREATE table ticket.baginfo if not present ###
CREATE TABLE IF NOT EXISTS ticket.bagInfo(
id LONG,
tagNum LONG,
routing STRING,
lastActionCode STRING,
lastActionDesc STRING,
lastSeenStation STRING,
lastSeenTimeGmt TIMESTAMP(4),
bagArrivalDate TIMESTAMP(4),
PRIMARY KEY(id))
 
### CREATE table ticket.bagInfo.flightLegs if not present ###
CREATE TABLE IF NOT EXISTS ticket.bagInfo.flightLegs(
flightNo STRING, 
flightDate TIMESTAMP(4),
fltRouteSrc STRING,
fltRouteDest STRING,
estimatedArrival TIMESTAMP(4), 
actions JSON, PRIMARY KEY(flightNo))
 
### CREATE table ticket.passengerInfo if not present ###
CREATE TABLE IF NOT EXISTS ticket.passengerInfo(
contactPhone STRING, 
fullName STRING,
gender STRING, 
PRIMARY KEY(contactPhone))

Let us see a sample row from each of the tables:

Ticket:
  {
    "ticketNo": 1762344493810,
    "id": 79039899165297,
    "tagNum": 17657806255240,
    "routing": "MIA/LAX/MEL",
    "lastActionCode": "OFFLOAD",
    "lastActionDesc": "OFFLOAD",
    "lastSeenStation": "MEL",
    "lastSeenTimeGmt": "2019-02-01T16:13:00Z",
    "bagArrivalDate": "2019-02-01T16:13:00Z",
    "actions": null
  }
Passenger Info:
  {
    "ticketNo": 1762344493810,
    "contactPhone": "893-324-1064",
    "fullName": "Adam Phillips",
    "gender": "M"
  },
Baggage Info:
  {
    "ticketNo": 1762344493810,
    "id": 79039899165297,
    "tagNum": 17657806255240,
    "routing": "MIA/LAX/MEL",
    "lastActionCode": "OFFLOAD",
    "lastActionDesc": "OFFLOAD",
    "lastSeenStation": "MEL",
    "lastSeenTimeGmt": "2019-02-01T16:13:00Z",
    "bagArrivalDate": "2019-02-01T16:13:00Z",
    "actions": null
  },
Flight Legs:
  {
    "ticketNo": 1762344493810,
    "id": 79039899165297,
    "flightNo": "BM604",
    "flightDate": "2019-02-01T06:00:00Z",
    "fltRouteSrc": "MIA",
    "fltRouteDest": "LAX",
    "estimatedArrival": "2019-02-01T11:00:00Z",
    "actions": [
      {
        "actionAt": "MIA",
        "actionCode": "ONLOAD to LAX",
        "actionTime": "2019-02-01T06:13:00Z"
      },
      {
        "actionAt": "MIA",
        "actionCode": "BagTag Scan at MIA",
        "actionTime": "2019-02-01T05:47:00Z"
      },
      {
        "actionAt": "MIA",
        "actionCode": "Checkin at MIA",
        "actionTime": "2019-02-01T04:38:00Z"
      }
    ]
  }

Download the script parentchildtbls_loaddata.sql

Use the load command to run the script. The tables are created and the data from the JSON files is loaded into the tables.

load -file parentchildtbls_loaddata.sql

The parentchildtbls_loaddata.sql contains the following:

### Begin Script ###
load -file parentchild.ddl
import -table ticket -file ticket.json
import -table ticket.bagInfo -file bagInfo.json
import -table ticket.passengerInfo -file passengerInfo.json
import -table ticket.bagInfo.flightLegs -file flightLegs.json
### End Script ###