Creating Kx kdb+ Input Data Source

The Kx kdb+ input data source allows connection to the Kx kdb+ databases on a polled basis.

Steps:

1.     In the New Data Source page, select Input > Kdb+ in the Connector drop-down list.

 

 

2.     Enter the following properties:

Property	Description
Host	Kx kdb+ host address.
Port	Kx kdb+ host port. Default is 5001.
TLS Enabled	Ensure to check if you have started q with TLS only.
User Id	The user Id that will be used to connect to Kx kdb+.
Password	The password that will be used to connect to Kx kdb+.
Timeout	The length of time to wait for the server response in seconds. Default is 30.
Retry Count	For long running queries, a query timeout can be specified to prevent the server from locking up. Default is 0.

 

 

3.     When Table is selected, the section below is enabled:

The Namespace drop-down is an editable combo box.

You can either:

·         click  and select a namespace from the list of all root level namespaces. By default, the selected namespace will be root (backtick `).

·         For nested namespaces, enter them in the Namespace box (e.g., panopticon.test) to get the tables that were created under these namespaces.

4.     On the Table field, click  to populate the drop-down list with tables and views. Select a table or view.

5.     Click . The columns of the selected table or view populates the Output Column section.

6.     Individual columns can be added by checking the corresponding Column box in the Output Column listing.

7.     If the data returned is to be aggregated, then the Aggregate checkbox should be selected. For each selected column, the possible aggregation methods are listed including:

·         Text Columns: Group By

·         Date Columns: Count, Min, Max, Group By

·         Numeric Columns: Sum, Count, Min, Max, Group By

In addition, the qSQL query is generated and displayed on the Query text box.

8.     Check the Parameterize checkbox and match the parameter to the appropriate column. By default, they will be matched by name.

The appropriate qSQL query is updated on the Query text box. This shows the default parameter value for the preview, and at run time the qSQL will be updated to whatever the parameter value is.

9.     If the data is to be filtered or aggregated on Date/Times, then a valid Date Time field needs to be selected from either a single Date/Time field, or a compound column created from a selected Date and a selected Time column.

10.   Check the Constrain by Date Time box and enter From and To Date/Time constraints.

11.   In Kx kdb+, you can modify the query to regroup the aggregated data per time units (i.e., Seconds, Minutes, Hours, Date, Week, Month). Check the Period box, enter the time duration and click  then select the time unit.

12.   Click the Query radio button to enable the text box and modify the qSQL query language.

13.    Select the Flatten List Limit.

This allows retrieval of the first ‘n’ items in the list and produce new columns in the output schema with a dot notation.

For example, if there are two nested fields (BidPrices and OfferPrices) and the flatten list limit selected is five, then the output schema will be:

BidPrices.1, BidPrices.2, BidPrices.3, BidPrices.4, BidPrices.5, OfferPrices.1, OfferPrices.2, OfferPrices.3, OfferPrices.4, OfferPrices.5

If there are less than five items in the list, then the values will be null.

 

NOTE	Currently, this feature works for the Service subscription type. Also, it only flattens numeric columns.

 

14.   Check Pass to function box to activate a connection to a server using a proxy. Enter the value.

15.   You may also define a Deferred Sync Query.

16.   Click . The new data source is added in the Data Sources list.