Foreign keys

The role of foreign keys within Refinery

In Refinery, foreign keys link tables together (primary → foreign), just as they do in SQL. This allows columns that weren't originally in a table to be queried and used as needed. By creating standard kdb+ foreign keys, this same functionality can be implemented within Refinery.

This method of setting up a foreign key is aligned to the standard foreign key creation within kdb+. A basic example will be shown with using this through Refinery but for more details on setting up foreign keys in general see the following whitepaper: https://code.kx.com/q/wp/foreign-keys/

In this example, a date partitioned table named DemoTable was created through a Refinery pipeline. The YAML file for this table is below.

table:
  name: DemoTable
  id-col: sym
  time-col: time
  intra-persist-type: splay
  end-persist-type: date-partition

  taxonomy:
    -
      region: global
      data-source: foreignKeyDoc
      data-class: foreignKeyDoc
      sub-class: foreignKeyDoc

  columns:
    -
      name: sym
      data-type: symbol
      attribute: parted
    -
      name: time
      data-type: timestamp
      data-type: sorted
    -
      name: category
      data-type: symbol
    -
      name: price
      data-type: float
    -
      name: volume
      data-type: long

As we can see, there is nothing specific in the YAML file when it comes to the foreign key creation. We will create a foreign key between the column sym in DemoTable and the table Contract, which has the following schema below.

|sym |chain |expiry    |
|----|------|----------|
|a   |aa    |2023.10.10|
|b   |bb    |2023.11.11|
|c   |cc    |2023.12.12|

The foreign key links will have to be set up on the RDB and HDB processes.

Note

Refinery does not support foreign key links within an IDB setup.

In order to create and maintain the foreign key linking on the RDB, we will need to edit the upd analytic .tpClient.upsertFunc. This analytic as standard is a basic upsert. We will update this to perform the upsert and also create the foreign key link between DemoTable and Contract. An example of what we would change this analytic to on the RDB is: 

.tpClient.upsertFunc:{[t;d]
    //Upsert data (d) to table (t)
    t upsert d;
    //If table is DemoTable, update the sym column to be foreign keyed to the Contract table
    if[t=`DemoTable;
      update `Contract$sym from `DemoTable;
    //Update the .schema.meta table so correct meta data is saved in the cache table
      .schema.meta[`DemoTable]:meta DemoTable
    ];

    }

Note

This method assumes that the Contract table has already been created on the RDB.

Following this, every time that DemoTable data is sent to the RDB the foriegn key link with Contract will be set up.

On the HDB, the Contract table and the foreign key between the two tables will be created on disk. This will need to be refreshed at a per partition level at every occurance of a writedown to disk. In this example, we create a listener analytic that will run after EOD is complete. This will create the foreign key between the tables once an EOD is ran.

.event.createForeignKeyOnDisk:{[]
        {[dt]
        //Check if foreign key link already exists for date, if it does, do not recreate
            if[not `Contract=key symForDate:get `$":",(string dt),"/DemoTable/sym";
                //If the forieng key is not present for a given date, create it and re-save down the sym column and Contract table    
                Contract::0!select from Contract;
                symForDate: `p#`Contract!Contract.sym?symForDate;
                (`$":",(string dt),"/DemoTable/sym") set symForDate;
                `:./Contract set `sym xkey Contract
            ]
        } each date;
        //Reload the HDB
        system "l .";
        //Update the .schema.meta table so correct meta data is saved in the cache table
        .schema.meta[`DemoTable]:meta DemoTable
    };

.event.addListener[`hdb.reload.complete;`.event.createForeignKeyOnDisk]

Note

This code assumes that the Contract table has been saved to the HDB as a flat keyed table on the column sym.

Once this is complete, we will have a foreign key link in the RDB and HDB between the sym column in DemoTable and the Contract table. We can then run API calls on these foreign key columns.

When a table has been correctly created with a foreign key column in the database, we can utilize this in Refinery API calls. Below are some examples with the tables that we have created. We can see the use of dot notation when we are calling a foreign key column.

getTicks - columns using foreign key

getTicks `assetClass`dataType`startDate`endDate`symList`columns!(`foreignKeyDoc; `DemoTable; .z.d-1; .z.d; `;`category`price`sym`sym.chain)

|sym |time                          |category |price |sym.chain|
|----|------------------------------|---------|------|---------|
|a   |2023.10.06D09:55:56.962070717 |top      |100   |aa       |
|b   |2023.10.06D09:55:56.962070718 |mid      |200   |bb       |
|c   |2023.10.06D09:55:56.962070719 |bot      |300   |cc       |

getTicks - applyFilter using foreign key

getTicks `assetClass`dataType`startDate`endDate`symList`applyFilter!(`foreignKeyDoc; `DemoTable; .z.d-1; .z.d; `;(=;`sym.chain;`aa))

|sym |time                          |category |price |volume|
|----|------------------------------|---------|------|------|
|a   |2023.10.06D09:55:56.962070717 |top      |100   |1     |

getTicks - sortCols using foreign key

getTicks `assetClass`dataType`startDate`endDate`symList`columns`sortCols!(`foreignKeyDoc; `DemoTable; .z.d-1; .z.d; `;`sym`time`category`price`volume`sym.chain;`desc`sym.chain)

|sym |time                          |category |price |volume |sym.chain|
|----|------------------------------|---------|------|-------|---------|
|c   |2023.10.06D09:55:56.962070719 |bot      |300   |3      |cc       |
|b   |2023.10.06D09:55:56.962070718 |mid      |200   |2      |bb       |
|a   |2023.10.06D09:55:56.962070717 |top      |100   |1      |aa       |