Create a database
This purpose of this section is to guide you through the steps to create and deploy a database, called insights-demo using the kdb Insights Enterprise UI.
This walkthrough guides you through the steps to:
Details on how to setup a pipeline to process event data, like crime, health, weather or subway data, are described in the associated walkthroughs.
Build a database
This section explains how to create a new database and setup the schema for the new database.
-
From the Overview page, choose Build a Database:
-
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.
- 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.
-
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-demodatabase.[ { "name": "weather", "type": "partitioned", "primaryKeys": [], "prtnCol": "timestamp", "sortColsDisk": ["airtemp"], "sortColsMem": ["timestamp"], "sortColsOrd": ["sensor"], "columns": [ { "type": "timestamp", "name": "timestamp", "attrMem": "", "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"] } ] -
Click Apply to setup the database schema.
-
Click Save to save the new database and its schema. The saved database is displayed as shown below.
-
The next step is to deploy the database.
Deploy a database
Once you have created and saved your database you need to deploy it so that it can receive data.
-
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.
-
-
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.
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. -
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.
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
Use the following links to learn more about specific topics mentioned in this page: