Skip to content

Create a database

This purpose of this walkthrough is to guide you through the steps to creating a database, called insights-demo using the kdb Insights Enterprise UI.

This page also explains how to setup the schema, associated with your database. Details on how to setup a pipeline to process event data, like crime, health, weather or transport data, are described in the pages that follow this.

This walkthrough guides you through the steps to:

Build a database

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

    Select a build a database.

  2. In the top left-hand corner of the workspace replace the default Name with insights-demo. This is the name of the database that is referred to throughout the following walkthroughs.

  3. On the Schema Settings tab click Code View to open the Schema Code View. You can use this to add large schema tables, in JSON format.

  4. Replace the existing code with the following JSON.

    insights-demo schema

    This JSON code is used in all the walkthrough examples that use the insights-demo database.

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

  5. Click Apply to setup the database schema.

  6. Click Save to save the new database and its schema. The saved database is displayed as shown below.

    A database with four schema tables.

  7. The next step is to deploy the database.

Deploy a database

Once you have created and saved your database you need to deploy the new database so that it can receive data.

  1. Go to Databases in the left hand menu

  2. You can deploy the the database by doing one of the following:

    • In the Database screen for insights-demo, shown in the previous section, click Deploy.

    • Open the list of databases on the right-hand menu and click the three dots beside insights-demo and select Deploy, as shown below.

    Select the insights-demo database, then deploy.

  3. Hover over the icon beside the database name in the left hand menu (or over the Getting Ready status for the database in the insights-demo screen) to see the progress of each of the processes which are preparing the database, as shown below. This can take several minutes depending on the resources that are available in your deployment.

    Deploying a database runs a series of processes.

    The processes are described in the following table:

    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 using the Query tab in the UI.

  4. Once all processes are ready the database shows a green circle with a green tick, as illustrated below. It is now ready to store data.

    Deployed database

Database warnings

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

Next Steps

Now that your database is created and deployed you can setup a pipeline to ingest data. Add weather data to the database.

Further Reading

To learn more about specific topics mentioned in this page please see the following links: