Skip to content

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.

SQL Database

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.

PostgreSQL Dataset

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=34.132.151.134"
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)

Ingesting Data

Importing

Select Import from the Overview panel to start the process of loading in new data. Expression

Select the PostgreSQL node and enter the details below to the datasource.

Server Details

config value
Server 34.132.151.134
Port 5432
Database health
Username postgres
Password dxrHvgtjHHvsOH7B
Query select * from health

Expression

Transforming

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.

Expression Note, for any fields of type time, timestamp and string check the box that says "Parse Strings".

Parse

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.

Writing

Finally, select to write this table to the insights-demo database and table called health and select Open Pipeline.

Expression

Deploying

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.

Exploring

Select Explore from the Overview panel to start the process of exploring your loaded data.

Expression

First select 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 health table.

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.

Expression

Refresh Browser

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.

Troubleshooting

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.