Skip to content

External file formats

This page covers the formats q reaches beyond its own: Parquet through the built-in module, and the interfaces that open up everything Python, Arrow, ODBC, and HDF5 can read.

Parquet

Parquet is a compressed columnar format widely used for interchange with tools such as Spark, pandas, and Arrow. KDB-X reads it through the pq module, so you can query a Parquet dataset without converting it first.

Reading Parquet differs from text and binary files in that q never loads the file into the heap. .pq.pq returns a virtual table that decodes only the column chunks a query asks for, reading them through the operating system page cache. A file much larger than memory therefore stays queryable, and opening one is nearly free. The 138 MB file below costs under 200 KB of heap to open, and never shows up in the mmap figure from .Q.w.

To try it, take a day of Bitcoin transactions from the AWS Public Blockchain dataset:

curl -o btc.parquet "https://aws-public-blockchain.s3.us-east-2.amazonaws.com/v1.0/btc/transactions/date=2014-06-30/part-00000-2fdb59cc-58c4-44f1-9e8c-5692087aa321-c000.snappy.parquet"

Import the module, open the file, and query it like a table:

q).pq:use`kx.pq
q)t:.pq.pq`:btc.parquet
q)select n:count i, totalFee:sum fee, avgValue:avg output_value by is_coinbase from t
is_coinbase| n     totalFee avgValue
-----------| -----------------------
0          | 70099 11.8892  9.742962
1          | 132   0        25.09007

For the full Parquet-to-q type mapping, the supported compression codecs and encodings, the column-map callback, and support for hive-partitioned datasets, see Parquet in KDB-X.

The Arrow interface reads and writes Arrow and Parquet formats. To read Parquet, use the built-in pq module described earlier; arrowkdb is the option when you need to write Parquet or use Arrow itself.

ODBC

Many Open Database Connectivity (ODBC) drivers expose files as well as database servers — there are drivers for Microsoft Access databases, Parquet, Microsoft Excel, and more. Install the ODBC client driver for KDB-X, then open a source with .odbc.open. To read an Access database:

q)h:.odbc.open "driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=C:\\mydb.mdb"

Driver names vary by installation

Use the name of the driver actually installed on your machine, which might differ from the example above.

.odbc.tables lists what the connection offers:

q).odbc.tables h
`aa`bb`cc`dd`ii`nn

.odbc.eval runs SQL over the connection:

q).odbc.eval[h;"select * from aa"]

Alternatively .odbc.load pulls the whole database into KDB-X, after which you query it in q rather than SQL.

HDF5

The HDF5 adapter reads and writes Hierarchical Data Format version 5 (HDF5) files.

Custom interfaces

Where no interface exists, extend KDB-X with C code to integrate a third-party format directly. To check what already exists, see the full list of external interfaces.

Next steps