FinSpace with Managed kdb Insights Tips and Tricks

Catalog of tips and tricks for using FinSpace with Managed kdb Insights.

Parallel Save a Splayed Table

For more information about splayed tables, see the KX documentation: Splayed Tables. This function can replace the common .Q.dpft function and takes advantage of available secondary threads with peach to save columns of a splayed table in parallel.

pdpft:{[d;p;f;t] 
    i:iasc t f; 
    tab:.Q.en[d;`. t]; 
    .[{[d;t;i;c;a]@[d;c;:;a t[c]i]}[d:.Q.par[d;p;t];tab;i;;]]peach flip(c;)(::;`p#)f=c:cols t; 
    @[d;`.d;:;f,c where not f=c]; t 
    };

Example Use

saveTables:{[db;path;d]
    .aws.get_latest_sym_file[db;path];
    t:tables`.;
    t@:where `g=attr each t@\:`Ticker;
    {pdpft[hsym`$x;y;`Ticker;z]}[path;d] each tables`.;
/    {.Q.dpft[hsym`$x;y;`Ticker;z]}[path;d] each tables`.;

    dt:string d;
    dict:flip`input_path`database_path`change_type!(
        (`$path,dt;`$path,"sym");
        (`$"/",dt,"/";`$"/");`PUT`PUT);
    cid:.aws.create_changeset[db;dict];
    
    nuke hsym`$path,string[d];
    hdel hsym`$path,"sym";

    @[;`Ticker;`g#] each t;
    .Q.gc[];

    cid
    };

Reference: process_algoseek.ipynb

System Command Equivalents

Linux	kdb	Reference
mv	\r <src. <dest>	https://code.kx.com/q/basics/syscmds/#r-rename
ls	key `:<path>	https://code.kx.com/q/ref/key/
pwd	\cd	https://code.kx.com/q/basics/syscmds/#cd-change-directory
cd	\cd <path>	https://code.kx.com/q/basics/syscmds/#cd-change-directory
rm	hdel `:<path>	https://code.kx.com/q/ref/hdel/
file size	hcount `:<path>	https://code.kx.com/q/ref/hcount/
sleep	{t:.z.p;while[.z.p<t+x]}	https://stackoverflow.com/questions/70805093/how-can-i-make-kdb-sleep-for-5-seconds

.z Namespace Overrides

Today, the FinSpace kdb cluster doesn't support direct assignment for .z namespace callbacksdue to security concerns. Managed kdb Insights does provide the .awscust namespace as a replacement, other .z namespace replacements are below:

.z	FinSpace	Description
.z.ts	.awscust.z.ts	timer
.z.pc	.awscust.z.pc	close
.z.zd	Command line argument	Send as command line arguments when starting cluster:

commandLineArguments=[{"key": "AWS_ZIP_DEFAULT", "value": "17,2,6"}], | |.z.pd | |Peach handles | |.z.ph | |HTTP get |

FinSpace Documentation: .z namespace override

How can I sleep without using system "sleep"?

Few system commands are allowed for managed clusters, for sleep you can use this simple q lambda as a replacement

q) sleep:{t:.z.p;while[.z.p<t+x]}
q) sleep[00:00:05]

Alternative:

q) sleep:{t:.z.p;while[.z.p<t+(x*1000000000)]}
q) sleep 5

hopen error reconnecting to a cluster

Error Message:Error: 'hop. OS reports: Protocol not available

PROBABLE CAUSE: Not having configured correctly for SSL. kdb reference: SSL/TLS

Don't forget: If you do NOT wish to provide an SSL certificate, then ensure the environment variable is set to 0 (Example: export SSL_VERIFY_SERVER=0 in your .bashrc file)

Selective use of .awscust in code

Issue

Customer codebase could be run either from finspace OR not, how does one set values in .awscust.z when in finspace, and set in .z otherwise?

Solution

Set a global variable (example `isfinspace) and then check that variable to assign values to .z or .awscust.z

$[`.@`isfinspace; `.awscust.z.ts;`.z.ts] set {[]

...

}

How do I know which endpoint belongs to which cluster?

All clusters created by FinSpace have a tag with the cluster's name, look for 'FinSpaceKdbCluster' tag on the endpoint:

Explore q Functions

q) key ` / lists namespaces
`q`Q`h`j`o

q) \f .Q / lists functions in a namespace
`Cf`IN`L`Ll`Lp`Ls`Lu`Lx`MAP`S`V`Xf`addmonths`addr`ajf0`bc`bd`bp`bs`bt`btoa`btx`bu`bv`chk`cn`d0`dbg`dd`def`dpft`dpfts`dpt`dpts`dr`dsftg`dt`dw`en`ens`enx`enxs`err`f`fc`ff`fk`fl`fmt`foo`fp`fpn`fps`fqk`fql`fs`fsn`ft`fu`gc`gz`hap`hdpf`hg`hmb`host`hp`id`ind`j10`j12`jl8`l`lu`nct`nv`opt`ord`p`p1`p2`par`p..


q) \a .o / lists tables
,`TI

q) \b / lists views

Tail FinSpace Logs from a Terminal

You can tail the logs of a FinSpace cluster from a terminal using the AWS CLI for cloudwatch, for example:

% aws logs tail /aws/vendedlogs/finspace/jlcenjvtkgzrdek2qqv7ic/algoseek_taq_cluster --follow

AWS CLI Reference

See Contents of File on Cluster

read0 `:/opt/kx/app/db/DEMO_DB/par.txt

q Reference for read0

Getting Error: Q.pn

Are you querying an HDB with partitioned tables? If so, include a date (the partition) in your query. You can aggregate by date or use in a where clause, like:

select from df1 where date = min date, i<3

Alternative Method

.Q.ind[CEUX_quotes; til 3]

TCP Keepalive Settings

Prevent server dropping connections to clients, modify the TCP keep alive settings.

Example of setting values for Mac or Linux

sudo sysctl -w net.ipv4.tcp_keepalive_time=300
sudo sysctl -w net.ipv4.tcp_keepalive_intvl=60
sudo sysctl -w net.ipv4.tcp_keepalive_probes=25

Definitions

tcp_keepalive_time

the interval between the last data packet sent (simple ACKs are not considered data) and the first keepalive probe; after the connection is marked to need keepalive, this counter is not used any further

tcp_keepalive_intvl

the interval between subsequential keepalive probes, regardless of what the connection has exchanged in the meantime

tcp_keepalive_probes

the number of unacknowledged probes to send before considering the connection dead and notifying the application layer

Development Advice

Some advice on how to approach developing kdb applications in managed kdb.

1. Use scaling groups
2. When creating clusters in scaling groups, avoid optional memory arguments such as memoryLimit, use minimal memoryReservation (6).
3. Avoid multi-node clusters in early development

.aws Namespace

All FinSpace Managed kdb Insights clusters will have an aws namespace that contains useful variables.

\d .aws
akdb / DB name
akdbs / DB Name as string
akcp / code path
akcsp / code sratch path
akscp / scratch path
akdbp / DB path

Output

DEMO_DB
"DEMO_DB"
/opt/kx/app/code
/opt/kx/app/code_scratch
/opt/kx/app/scratch
/opt/kx/app/db

Load the database as named by the system

When a cluster is created with CreateKxCluster you can relate databases to it with the database argument. Usually that list is one item, and if so you can load that database using variables set by the service on the cluster.

.Q.l `$.aws.akdbp,"/",.aws.akdb

How to connect to FinSpace Cluster Using qcon

qcon is a q console application that connects to remote clusters and then behaves like a regular q REPL. Reference: https://github.com/KxSystems/kdb/tree/master/l64

You can get the cluster connection string and use it to connect to the remote cluster using qcon like this.

export CONN_STRING=$(aws finspace get-kx-connection-string --environment-id $ENV_ID --user-arn $USER_ARN --cluster-name $HDB_CLUSTER_NAME | jq -r '.signedConnectionString')
./qcon ${CONN_STRING:8}