Skip to content

Get Data - Kafka (Subway)

Apache Kafka is an event streaming platform supported by kdb Insights Enterprise. The subway Kafka feed generates live alerts for NYC Subway trains - tracking arrival time, station location coordinates, direction and route details.

No kdb+ knowledge required

This example assumes no prior experience with q/kdb+ and you can replace the endpoints provided with other Kafka brokers to gain similar results.

1. Create and deploy a database

A database stores data on kdb Insights Enterprise. Available databases are listed under Databases of the Overview home page. If not available, create a database, insights-demo, to store your data.

To use a pipeline, your database must be deployed and active.

2. Import Data

Open the import wizard by selecting 2. Import from the Overview page. The import process creates a pipeline; a pipeline is a connection of nodes to read data from source, transform to a kdb+ compatible format, then write to a kdb Insights Enterprise database.

I want to learn more about the import wizard.

3. Select a Reader

A reader stores details of data to import, including any required authentication. Select the Kafka reader and complete connection details; required properties are marked with an *.

Choose the Kafka reader.
Choose the Kafka reader.

setting value
Broker* kafka.trykdb.kx.com:443
Topic* subway
Offset* End
Use TLS No
Use Schema Registry No
Use Advanced Configuration Yes

Add the following key value-pairs into the Use Advanced Configuration section:

key value
sasl.username demo
sasl.password demo
sasl.mechanism SCRAM-SHA-512
security.protocol SASL_SSL

Click Next when done.

I want to learn more about reader nodes.

4. Select a Decoder

Kafka event data is in JSON and has to be decoded to a kdb+ friendly format (a kdb+ dictionary).

Select a JSON decoder.
Select a JSON decoder.

Keep default JSON decoder settings:

Keep default JSON decoder settings; Decode Each is left unchecked.
Keep default JSON decoder settings; Decode Each is left unchecked.

Click Next when done.

I want to learn more about decoder nodes.

5. Define the Schema

The schema converts data to a type compatible with a kdb Insights Database. Every imported data table requires a schema; and every data table must have a timestamp key to be compatible with kdb's time series columnar database. insights-demo has a predefined schema for subway data.

setting value
Apply a Schema Checked
Data Format Any
  1. Click Schema icon
  2. Select insights-demo schema from the dropdown

    Select insghts-demo schema.
    Select insghts-demo schema.

  3. Select the subway table

    Select the subway table from the insights-demo database.
    Select the subway table from the insights-demo database.

    Review the subway schema. **Parse Strings** is set to auto for all fields.
    Review the subway schema. Parse Strings is set to auto for all fields.

Parse Strings

Determines the requirement to parse input string data to other datatypes. Generally, parse strings is enabled for all time, timestamp, string fields unless your input is IPC or RT; retain the Auto default if unsure.

Manual entry subway schema

If not adding a schema with a table from a database, add the following columns with the add row button instead. Column descriptions are optional and not required here:

column type
trip_id symbol
arrival_time timestamp
stop_id symbol
stop_sequence short
stop_name symbol
stop_lat float
stop_lon float
route_id short
trip_headsign symbol
direction_id symbol
route_short_name symbol
route_long_name symbol
route_desc string
route_type short
route_url symbol
route_color symbol

I want to learn more about schema kdb+ types

Click Next when done.

I want to learn more about transform nodes.

6. Configure Writer

Write transformed data to the kdb Insights Enterprise database.

setting value
Database insights-demo
Table subway
Write Direct to HDB No
Deduplicate Stream Yes
Set Timeout Value No

Click writer to review the pipeline in the pipeline viewer

A pipeline created by the import wizard, reads data from its source, transforms to a kdb+ compatible format, and writes it to a kdb Insights Enterprise database.

Writer Node

The Writer - kdb Insights Database node is essential for exploring data in a pipeline. The node defines the database to write too that must be active to receive data. Stream data uses its own writer node, Writer - kdb Insights Stream.

7. Review Pipeline

The pipeline is a linked set of nodes derived from each step of the import process.

A Kafka pipeline built using the import wizard.
A Kafka pipeline built using the import wizard.

8. Modify Pipeline

The Kafka pipeline requires an additional piece of functionality to convert the decoded data to a kdb+ table prior to deployment. This is done with an enlist of the data.

  • In the pipeline template view, click-and-drag into the workspace a Map node from the list of Functions.
  • Connect the Map node between the Decoder and Transform node. Remove the existing connection between Decoder and Transform nodes by right-clicking the link and selecting Remove Edge.

Adding a **Function Map** node to a Kafka data pipeline. Connect edges with click-and-drag, right-click a conncetion to remove.
Adding a Function Map node to a Kafka data pipeline. Connect edges with click-and-drag, right-click a conncetion to remove.

  • Edit the Map node properties.

Select the Function Map node to edit its properties.
Select the Function Map node to edit its properties.

{[data]
    enlist data
 }

9. Save the Pipeline

  1. Save the pipeline as subway-1.

    Save the pipeline as subway-1.
    Save the pipeline as subway-1.

The subway-1 pipeline is available in the left-hand pipeline menu.

10. Deploy the Pipeline

Deploy a pipeline to access it's data.

Deploy the pipeline.
Deploy the pipeline.

Check the progress of pipeline under Running Pipelines of the Overview panel. The data is ready to query when Status=Running.

A running subway pipeline available for querying.
A running subway pipeline available for querying.

Database Deployment: If not already active, ensure insights-demo, or the database created with the subway schema table, is deployed from Databases in the left-hand menu for it to receive data from the pipeline.

11. Query the Data

Deploy the insights-demo database and subway-1 pipeline if not active or running.

Query data on a successful deploy to the database.

12. Visualize the Data

Build a visualization from the data.