Skip to content

Database

What is a database?

A database stores data in kdb Insights Enterprise. Available databases are listed under Databases of the Overview home page. If not available, create a database, insights-demo, to store your data.

Every kdb Insights Enterprise database has a schema to transform data to a format compatible with a kdb+ database, and a stream to process event data like real-time prices.

I want to learn more about databases.
I want to learn more about schemas.

Build a database

  1. From the Overview page, choose Build a Database:

    Select a build a database.
    Select a database to activate, then deploy.

  2. From the database menu, name the database; guided walkthrough uses the database name insights-demo.

  3. Open the Schema Settings tab; for small data sets, continue with the Column Input. However, a JSON Code View option is available for adding large schema tables.

    1. Create four tables for each of weather, subway, health, and crime datasets; re-naming the initial table1, and adding new tables with [+]. Column descriptions are optional and can be left blank here.

      column data type
      timestamp timestamp
      sensor symbol
      airtemp float
      name symbol
      borough symbol
      longitude float
      latitude float
      color symbol
      column data type
      trip_id symbol
      arrival_time timestamp
      stop_id symbol
      stop_sequence short
      stop_name symbol
      stop_lat float
      stop_lon float
      route_id short
      trip_headsign symbol
      direction_id symbol
      route_short_name symbol
      route_long_name symbol
      route_desc string
      route_type short
      route_url symbol
      route_color symbol
      column data type
      name string
      lat float
      long float
      neighborhood string
      airquality float
      trafficcongestion float
      bikeacccnt integer
      caracccnt integer
      timestamp timestamp
      column data type
      event_id symbol
      incident_time time
      nypd_precinct symbol
      borough symbol
      patrol_borough symbol
      call_x_geo long
      call_y_geo long
      radio_code symbol
      description string
      crime_in_progress string
      call_timestamp timestamp
      dispatch_timestamp timestamp
      arrival_timestamp timestamp
      closing_timestamp timestamp
      latitude float
      longitude float

    2. Review the schema partitions in the right-hand Table Properties panel; these are set automatically to the timestamp column, but in the case of multiple timestamp columns select one.

      setting value
      Type partitioned
      Partition Column timestamp
      setting value
      Type partitioned
      Partition Column arrival_time
      setting value
      Type partitioned
      Partition Column timestamp
      setting value
      Type partitioned
      Partition Column arrival_timestamp

    3. Remove Attributes for each table, set them to none; by default, these are set on the first column.

      setting value
      IDB Attribute (Ordinal Partitioning) None
      HDB Attribute (Temporal Partitioning) None
    1. Click code view button on the right-hand-side of the Schema Settings tab.

    2. Paste the JSON into the code editor:

      insights-demo schema

      JSON code for the four walkthrough examples.

      [
    {
        "name": "weather",
        "type": "partitioned",
        "primaryKeys": [],
        "prtnCol": "timestamp",
        "sortColsDisk": [],
        "sortColsMem": [],
        "sortColsOrd": [],
        "columns": [
            {
                "type": "timestamp",
                "name": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "sensor",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "airtemp",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "name",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "borough",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "longitude",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "latitude",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "color",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            }
        ]
    },
    {
        "columns": [
            {
                "type": "symbol",
                "name": "trip_id",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "arrival_time",
                "type": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "stop_id",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "stop_sequence",
                "type": "long",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "stop_name",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "stop_lat",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "stop_lon",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "route_id",
                "type": "long",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "trip_headsign",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "direction_id",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "route_short_name",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "route_long_name",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "route_desc",
                "type": "string",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "route_type",
                "type": "long",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "route_url",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "route_color",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            }
        ],
        "primaryKeys": [],
        "type": "partitioned",
        "prtnCol": "arrival_time",
        "name": "subway",
        "sortColsDisk": [],
        "sortColsMem": [],
        "sortColsOrd": []
    },
    {
        "columns": [
            {
                "type": "string",
                "name": "name",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "lat",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "long",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "neighborhood",
                "type": "string",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "airquality",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "trafficcongestion",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "bikeacccnt",
                "type": "int",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "caracccnt",
                "type": "int",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "timestamp",
                "type": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            }
        ],
        "primaryKeys": [],
        "type": "partitioned",
        "prtnCol": "timestamp",
        "name": "health",
        "sortColsDisk": [],
        "sortColsMem": [],
        "sortColsOrd": []
    },
    {
        "columns": [
            {
                "type": "symbol",
                "name": "event_id",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "incident_time",
                "type": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "nypd_precinct",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "borough",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "patrol_borough",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "call_x_geo",
                "type": "long",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "call_y_geo",
                "type": "long",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "radio_code",
                "type": "symbol",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "description",
                "type": "string",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "crime_in_progress",
                "type": "string",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "call_timestamp",
                "type": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "dispatch_timestamp",
                "type": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "arrival_timestamp",
                "type": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "closing_timestamp",
                "type": "timestamp",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "latitude",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            },
            {
                "name": "longitude",
                "type": "float",
                "attrMem": "",
                "attrOrd": "",
                "attrDisk": ""
            }
        ],
        "primaryKeys": [],
        "type": "partitioned",
        "prtnCol": "arrival_timestamp",
        "name": "crime",
        "sortColsDisk": [],
        "sortColsMem": [],
        "sortColsOrd": []
    }
]

    3. Apply the JSON

  4. Save the database

    A database with four schema tables.
    A database with four schema tables.

  5. Go to Databases in the left hand menu and deploy the database.

I want to learn more about databases.

Deploy a database

Data is stored in an active database. Select the database created previously. Deploy the database to activate it, so it can receive data.

Select a database to activate, then deploy.
Select a database to activate, then deploy.

Activating a database starts a series of processes to prepare the database.

Deploying a database runs a series of processes.
Deploying a database runs a series of processes.

item description
Mount When a database is mounted, it is available for use. Mount(s) can be an hdb, idb or rdb.
Storage Database is ready to store data.
Data Access Data stored on the database is ready to be queried.
Stream Database is ready to receive event data; e.g. real-time prices.
Pipeline Data pipelines (if any) associated with the database are now active.
Query Environment Data is ready to be queried.

Database warnings

Once the database is active you will see some warnings in the Issues pane of the Database Overview page, these are expected and can be ignored.

Active databases

Active databases will show a green circle with a white tick. Databases receive and store data when active.

An active database
An Active database.

Add data to database

Next, add data to the active database.