SQL Database (Health)
No kdb+ knowledge required
This example assumes no prior experience with q/kdb+ and you can replace the details provided with any other PostgreSQL connection details to gain similar results.
The KX Insights Platform supports connecting with many SQL databases like PostgreSQL and Microsoft SQL Server. These connectors help bring in data from a source of your choice to the KX Insights Platform and then stream it to the destination of your choice.
KX has provided a free to use PostgreSQL Database containing data from a series of health and covid related datasets.
If you have PostgreSQL Client installed on your local machine, you can query the dataset from PostgreSQL using the following method:
$ psql "sslmode=disable dbname=postgres user=postgres hostaddr=22.214.171.124" Password for user postgres: dxrHvgtjHHvsOH7B psql (14.1, server 13.4) Type "help" for help. postgres=> \connect health psql (14.1, server 13.4) You are now connected to database "health" as user "postgres". health=> \dt List of relations schema | name | type | owner --------+---------------------+-------+---------- public | health | table | postgres (5 rows)
Select Import from the Overview panel to start the process of loading in new data.
Select the PostgreSQL node and enter the details below to the datasource.
|Query||select * from health|
The next screen that comes up is Apply Schema. This is a useful tool that transforms the upstream data to the correct datatype of the destination database.
In the "Apply A Schema" screen select Data Format = Table from the dropdown.
Next, click on the blue "+" icon next to "Parse Strings" and you should get a popup window called "Load Schema". Select
insights-demo database and
health table from the dropdown.
Note, for any fields of type time, timestamp and string check the box that says "Parse Strings".
This indicates whether parsing of input string data into other datatypes is required. Generally for all time, timestamp, string fields you should have ‘Parse Strings’ ticked unless your input is IPC or RT.
Finally, select to write this table to the
insights-demo database and table called
health and select Open Pipeline.
Next you can Save your pipeline giving it a name that is indicative of what you are trying to do. Then you can select Deploy.
Once deployed you can check on the progress of your pipeline back in the Overview pane where you started. When it reaches
Status=Running then it is done and your data is loaded.
Select Explore from the Overview panel to start the process of exploring your loaded data.
insights-demo from the assembly dropdown on the top left hand side.
In the Explore tab there are a few different options available to you, Query, SQL and q. Let's look at SQL. You can run the following
SQL to retrieve from the
SELECT * FROM health
Note that you will need to define an
Output Variable and then select
Get Data to execute the query. You should see the output below.
If the database or tables are not appearing in the Explore tab as expected - it is a good idea to refresh your browser and retry.
If the database or tables are still not outputting after refreshing your browser, try our Troubleshooting page.
Let's ingest some more data! Try next with Protocol Buffer on Crime Data.