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.

Reading Parquet files

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 a Parquet dataset can be queried without first converting it to a KDB-X database.

Reading parquet differs from the formats above in that the file is not loaded into the heap at all. .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 138MB file below costs under 200KB 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.

External interfaces

Interfaces extend the set of file formats q can reach beyond the ones it reads natively.

Python, Numpy and Pandas

KDB-X Python moves data between q and Python in both directions, which opens up every format Python can read. Because the conversion is to Python's own analytical types rather than to a generic intermediate type, the file handling usually costs nothing extra.

A q object converts to and from plain Python types, NumPy arrays, Pandas DataFrames, and PyArrow tables, so any reader in that ecosystem becomes a way into KDB-X. Read the file with whichever Python library already handles it — Excel, JSON, HDF5, Avro, ORC, a vendor SDK, a REST client — and convert the resulting DataFrame or Arrow table into a q table.

The same path runs in reverse, sending a q table into Python for a library q has no equivalent for, such as scikit-learn or PyTorch. This is often the shortest route to a format with no native q reader and no dedicated interface, and it avoids writing an intermediate CSV.

Arrow and Parquet

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

ODBC

Many 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 version

Use the name of the driver actually installed on your machine, which may 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 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