Kx kdb+ Discovery

The Kx kdb+ Discovery connector is very similar to the Kx kdb+ connector, except that the former is using a Host Lookup URL for the host information (i.e., Host and Port).

Using Kx kdb+ Discovery

1. When creating a new data table, select KDB+ Discovery from the Connect to Data dialog. The KDB+ Discovery Connection dialog is displayed:

   

2. Enter the Host Lookup URL.

3. Enter the Username and Password to pass to the Host Lookup URL as basic authentication.

4. If Tables & Views is selected, the section below is enabled:

The Namespaces drop-down is an editable combo box.

You can either:

1. • click Load Namespaces 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 Namespaces box (e.g., panopticon.test) to get the tables that were created under these namespaces.

5. Click Load Tables. This loads the list of tables and views, which can be filtered by entering text the Search Tables search box.

6. When a table or view is selected, the Search Columns listing is populated.  By default, all columns are returned through SELECT * FROM TABLE.

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

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

1. • Text Columns: Group By

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

   • Numeric Columns: Sum, Count, Min, Max, Mean, Group By

In addition, the SQL query is generated. Click the Query expander button to expand the Query section.

If a parameter has been defined in the data table, the SQL can automatically be modified to refer to it.

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

The appropriate SQL Query is updated, and shown at the bottom of the dialog. This shows the default parameter value for the preview, and at run time the SQL will be updated to whatever the parameter value is.

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

11. The Timezone selection by default is empty. If changed to a valid time zone, check the Constrain by Date Time box, and enter From and To Date/Time constraints that are assumed to be in this time zone, and are converted to UTC for incorporation into the query.

Similarly results returned from the query are converted from UTC to the selected time zone.

 

If the query is to filter/constrain the results on Date/Time, the constrain sections are completed.

12. 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 select the time unit.

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: This feature is not supported in on-demand queries. 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 OK. The source table is returned in the Edit Data Table view, with the fields displayed in the Data Source Preview.

All fields are mapped to one of the following data types:

1. • Text

   • Numeric (double)

   • Date/Time

   • For long running queries, a query timeout can be specified to prevent Panopticon Designer (Desktop) from locking up.

This results to:

1. • The data connector either returns data, or a timeout occurs after the specified time.

   • The kdb+ instance will still try to process the query and will not stop.

When a q query is executed that returns a single value, a resulting table is automatically created with one column entitled Value.

If the result is a list of primitive data types, a resulting table is automatically created with one column entitled Values.

Finally if the result is a dictionary of primitive data types, a resulting table is automatically created with two columns entitled:

• Key

• Value