# 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:
```python
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
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`](reference.md#op) and [`rd`](reference.md#rd) extract a given column chunk, using the row group and column index:
```q
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:
```python
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
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:
```python
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
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](reference.md#virtual-table-api) combines multiple Parquet files into a single virtual table, using virtual partition columns.

Given the following Parquet files:
```python
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
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
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
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`](../../ref/meta.md) returns the usual column information for virtual tables:
```q
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.

## Next steps

- See the [Reference](reference.md) for the API, supported types, compression, and encodings.
- Read the [Overview](overview.md) for the concepts behind virtual tables and row groups.
