Create a database
This purpose of this walkthrough is to guide you through the steps to create and deploy a database, called insights-demo using the kdb Insights Enterprise UI.
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 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 to see the progress of each of the processes which are preparing the database. This can take several minutes depending on the resources that are available in your deployment. For more details refer to deploying a database.
-
Once all processes are ready the database shows a green circle with a green tick. 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: