In ocean927/R2QCon: Prototype of connector to KDB.

knitr::opts_chunk$set(echo = TRUE)

PURPOSE:

Package connects R session with KDB process. It can be used to

1. Execute Q commands remotely. "x: 1 2 3" # create a list "t:([] date:date$(); sy:symbol$(); " # define a table "t:("DS"; enlist ",") 0: `:file.csv" # load csv

Note: operation result is not returned back to R session.

1. Convert Q table result set into R data frame.

"select from t" "10#select from t"

FUNCTIONS:

// creates a java connection manager create() ex: manager = create()

// connects to Q session connect(manager (javaref),host(string),port (string/int)) ex: handle = connect(manager,"localhost", 5000L)

// execute remote statement exec( handle, "statement") ex: manager.exec(handle,"x: 1 2 3")

// retrieve as data frame select( handle(int), query(string)) ex: df=select(manager,h1,"3#select from t")

you can also access a low-level Q driver inside conmgr.

// close Q session connect(manager(javaref),handle(int))

REQUIREMENTS:

KDB process live and listening on a port. Ex: >q -p 5000

LIMITATIONS:

• Currently only table 98h is returned back into R session.
• The following data types are supported: boolean(b), int(i), long(j), float(f), char(c), symbol(s), date(d), datetime(z), time(t),
• Additionally Q list of char(c) known as string will be converted to R string. See exampe:
• These types are not yet implemented: guid(g), byte(x), short(h), real(e), timestamp(p), month(m), timespan(n), minute(u), second(v)

CONVERSION TO R TYPES

Due to performance, package will implement the following conversions: date(d) - returned to R as numeric time(t) - returned to R as numeric. datetime(z) - returned to R as string "2016.12.24 21:16:38.067 EST"

Implementation may change.

TODO

1. Retrieve dictionary 99h.
2. Map remaining KDB reference types.
3. Write data frame back to Q as a table/dictionary.
4. Improve performance of select.
5. Implement batched retrieval for large tables.

DESIGN:

Package uses Java library to connect to and translate KDB types.

PERFORMANCE:

Example: KDB and R are started locally. table: 1M rows x 10 columns, 62455442 bytes. 1. Java library retrieves 1M records in 0.9 seconds. 2. R package retrieves 1M rows and converts to dataframe in ~10 seconds.

EXAMPLE:

//create table t1:([] date:date$(); time:time$(); sy:symbol$(); vboolean:boolean$(); charvalue:char$(); str:(); px:float$(); volume:int$(); long:long$(); tm:`datetime$() )

//fill with dummy data t1 insert(2016.12.1; "T"$"07:00:00.000";FOO; 0b; "a"; "xyz"; 1.345f; 100; 1099511627776j; .z.Z ) `t1 insert(til 1000; .... )

R code

# open session to one or more Q instances
manager = initmgr()
h1  = connect(manager,"localhost", 5000L)
h2  = connect(manager,"host", "port") 

# execute remotely
exec(manager,h1,"x: 1 2 3")
exec(manager,h1,".Q.w[]")

# retrieve as R data frame
df1=select(manager,h1,"10#select from t1")
head(df1)

# retrieve as R data frame, Q memory stats
df2=select(manager,h1,"select from ([] k:key .Q.w[]; v:value .Q.w[])")
head(df2)

# close Q connection.
close(manager,h1)

ocean927/R2QCon documentation built on May 24, 2019, 11:53 a.m.