Parquet Examples¶
This page provides worked examples of reading and querying Parquet files in KDB-X.
The examples use Parquet files created with pandas in Python, which requires pyarrow to be installed. Writing the files from Python makes the row group boundaries explicit.
Inspect row groups¶
Save a Parquet file with a forced row group size:
import pandas as pd
import pyarrow as pa
import pyarrow.parquet
f=pd.DataFrame(data={
'c_bool': [True,True,False,True,False,False,True,False,False],
'c_int8': pd.Series([1,2,3,4,5,6,7,8,9],dtype="int8"),
})
pa.parquet.write_table(pa.Table.from_pandas(f), 'rowgrp.parquet', row_group_size=3)
The row group index can be accessed using the hidden column RG__:
q).pq:use`kx.pq
q)t:.pq.pq`:rowgrp.parquet
q)select c_int8, RG__ from t
c_int8 RG__
-----------
1 0
2 0
3 0
4 1
5 1
6 1
7 2
8 2
9 2
Read column chunks¶
The low-level functions op and rd extract a given column chunk, using the row group and column index:
q)f:.pq.op`:rowgrp.parquet
q).pq.rd[f;0;0]
110b
q).pq.rd[f;1;0]
100b
q).pq.rd[f;2;0]
100b
q).pq.rd[f;0;1]
1 2 3h
q).pq.rd[f;1;1]
4 5 6h
q).pq.rd[f;2;1]
7 8 9h
Compare row group sizes¶
The performance effect of row group pruning can be demonstrated by writing the same data with different row group sizes:
import pandas as pd
import pyarrow as pa
import pyarrow.parquet
ROWS=50000000
f=pd.DataFrame(data={'time': pd.date_range("2012-01-01 00:00", periods=ROWS, freq=pd.Timedelta("00:00:01")),
'sym': (ROWS//4)*['aaa','bbb','ccc','ddd'],
'price': (ROWS//10)*[1,2,3,4,5,6,7,8,9,10]})
pa.parquet.write_table(pa.Table.from_pandas(f), 'rowgrp1.parquet', row_group_size=1000)
pa.parquet.write_table(pa.Table.from_pandas(f), 'rowgrp2.parquet', row_group_size=ROWS//10)
q).pq:use`kx.pq
q)\ts f1:.pq.pq`:rowgrp1.parquet
601 371102256
q)\ts f2:.pq.pq`:rowgrp2.parquet
2 88656
q)\ts select from f1 where time<2012.10.16
3273 1425051600
q)\ts select from f2 where time<2012.10.16
1962 1811943440
Filter on row groups¶
Given the following Parquet file:
import pandas as pd
import pyarrow as pa
import pyarrow.parquet
f2=pd.DataFrame(data={
'c_bool': [True,True,False,True,False,False,True,False,False],
'c_int8': pd.Series([1,2,3,4,5,6,7,8,9],dtype="int8"),
})
pa.parquet.write_table(pa.Table.from_pandas(f2), 'rowgrp.parquet', row_group_size=3)
The table is split into three row groups. If there is a condition on a particular column, querying considers the minimum and maximum value in each column chunk to determine whether it satisfies the condition. Any row groups that don't match the condition aren't scanned.
q).pq:use`kx.pq
q)select from .pq.pq`:rowgrp.parquet where c_int8<3
c_bool c_int8
-------------
1 1
1 2
0 3
The chunks of column c_int8 are checked against the condition <3. Using the minimum and maximum values, the module determines that only row group 0 may have matching rows. Therefore the chunks for row groups 1 and 2 aren't read for the column c_bool.
Partition across multiple files¶
The virtual table API combines multiple Parquet files into a single virtual table, using virtual partition columns.
Given the following Parquet files:
import pandas as pd
import pyarrow as pa
import pyarrow.parquet
fd1=pd.DataFrame(data={
'c_bool': [True,True,False,True],
'c_int8': pd.Series([1,2,3,4],dtype="int8"),
})
fd2=pd.DataFrame(data={
'c_bool': [False,False,True,False,False],
'c_int8': pd.Series([5,6,7,8,9],dtype="int8"),
})
pa.parquet.write_table(pa.Table.from_pandas(fd1), 'part1.parquet', row_group_size=3)
pa.parquet.write_table(pa.Table.from_pandas(fd2), 'part2.parquet', row_group_size=3)
Map the files as partitions in a virtual table, using one or more virtual columns:
q).pq:use`kx.pq
q).pq.t:use`kx.pq.t
q)t:.pq.t.mkP([]date:2001.01.01 2001.01.02;a:1 -1)!.pq.pq each`:part1.parquet`:part2.parquet
q)select from t
date a c_bool c_int8
---------------------------
2001.01.01 1 1 1
2001.01.01 1 1 2
2001.01.01 1 0 3
2001.01.01 1 1 4
2001.01.02 -1 0 5
2001.01.02 -1 0 6
2001.01.02 -1 1 7
2001.01.02 -1 0 8
2001.01.02 -1 0 9
As with partitioned kdb+ tables, filtering on the virtual columns avoids reading the data for partitions that don't match the filtering condition:
q)select from t where a=1
date a c_bool c_int8
--------------------------
2001.01.01 1 1 1
2001.01.01 1 1 2
2001.01.01 1 0 3
2001.01.01 1 1 4
Because the column a has the value -1 in the second partition, no data is loaded from the second file.
Virtual tables also support map-reduce-style operations. These are equivalent to, and subject to the same restrictions as, those for partitioned tables.
q)select avg c_int8 by date from t
date | c_int8
----------| ------
2001.01.01| 2.5
2001.01.02| 7
Check metadata¶
The built-in function meta returns the usual column information for virtual tables:
q)meta t
c | t f a
------| -----
date | d
a | j
c_bool| b
c_int8| h
Performance considerations¶
Large files with multiple row groups perform better than many small files.