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.
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 |
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.
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.
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.
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.
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.