Previous Next JavaScript must be enabled to correctly display this content

## Example: Hierarchical Table

This section creates the hierarchical tables and loads the data for a sample baggage tracking application.

Airline Baggage Tracking Application Using Hierarchical Tables

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 ###