Wrong categories data on reading with engine='fastparquet'

[ElnazVakhitov]

Describe the issue:
After saving with mode='append', i'm trying to read the file, but categorical data is incorrected
Minimal Complete Verifiable Example:

import pandas as pd
import numpy as np
import pyarrow
import fastparquet
import sys

print(np.__version__)
# 1.26.4
print(pd.__version__)
# 2.2.2
print(pyarrow.__version__)
# 15.0.2
print(fastparquet.__version__)
# 2024.5.0
print(sys.version)
# 3.9.19 (main, Jun 11 2024, 10:17:45) 
# [GCC 11.4.0]

data = pd.DataFrame(np.arange(9).reshape(3, 3), columns=["first", "second", "thirst"])

data["thirst"] = data["thirst"].astype("category")
data.to_parquet("data.parquet", engine="fastparquet", index=False)
print(data)
#    first  second thirst
# 0      0       1      2
# 1      3       4      5
# 2      6       7      8

data_new = pd.read_parquet("data.parquet", engine="fastparquet")
print(data_new)
#    first  second thirst
# 0      0       1      2
# 1      3       4      5
# 2      6       7      8


data2 = pd.DataFrame(
    np.arange(3, 12).reshape(3, 3), columns=["first", "second", "thirst"]
)
data2["thirst"] = data2["thirst"].astype("category")
data2.to_parquet("data.parquet", engine="fastparquet", append=True, index=False)
print(data2)
#    first  second thirst
# 0      3       4      5
# 1      6       7      8
# 2      9      10     11

data2_new = pd.read_parquet("data.parquet", engine="fastparquet")

print(data2_new)
#    first  second thirst
# 0      0       1      5
# 1      3       4      8
# 2      6       7     11
# 3      3       4      5
# 4      6       7      8
# 5      9      10     11

data2_new["thirst"] = data2_new["thirst"].astype("object")
print(data2_new)
#    first  second thirst
# 0      0       1      5
# 1      3       4      8
# 2      6       7     11
# 3      3       4      5
# 4      6       7      8
# 5      9      10     11

data2_pyarrow = pd.read_parquet("data.parquet", engine="pyarrow")
print(data2_pyarrow)
#    first  second  thirst
# 0      0       1       2
# 1      3       4       5
# 2      6       7       8
# 3      3       4       5
# 4      6       7       8
# 5      9      10      11

Environment:

• Dask version:
• Python version: 3.9.19
• Operating System: Ubuntu 22.04

[martindurant]

fastparquet requires that categorical columns are coded the same throughout the dataset. I don't generally recommend appending for categoricals, but if you do, then you must explicitly set the categories on your new data to be identical to the exiting data.

It is not an unsolvable problem to recode categoricals on load and make this problem go away, but I don't think anyone has spare effort to make it happen.

[ElnazVakhitov]

I work at RecSys. I have a table whose unique key is two columns, one of them is the "Query" column. Initially, it has a string type, and only this column already weighs 50 GB in 1 day. If I convert it to a categorical type, it will weigh 10GB. I want to collect data for a week, in the usual form they will weigh 350GB (which will not fit on the RAM), and in the categorical ~50GB. That's why I can't read from pyarrow and then convert the categorical type.

OK. i can do something like this

df_old = pd.read_parquet(path, engine="fastparquet", columns=["query"])
df_new["query"] = update_category(df_new["query"], df_old["query"])
df_new.to_parquet(path, engine="fastparquet", append=True)

Maybe you will add a warning that the append mod does not work correctly with categorical types.

[martindurant]

Maybe you will add a warning that the append mod does not work correctly with categorical types.

You are right that documentation would help. As ever, the problem is how to make this prominent among all the other options that users have. There is some text about categorical in the read API only.

It would also be possible to check the incoming categories against existing ones in the dataset, but that would a) be an additional option b) be potentially costly c) need someone to develop it. Given that fastparquet is no one's priority, I doubt this will happen.

[yohplala]

Hi, I am investigating the topic (with help of Claude 3.7 Sonnet).
I would like to share the understanding and proposed solution that emerge from this investigation.

• categorical values are not stored in common metadata files. Categorical codes and values of a given row group are stored in a dictionary page at the beginning of each column chunk (in case of multi scheme, in each row group file)

• only number of categorical values for a given column is stored in common metadata

• the solution you proposed Martin, operating at write time, to check the incoming categories against existing ones in the dataset has been investigated in this way:

  • read from last row group file the category codes and values, check against those from new data, and consolidate if necessary.
  • write consolidated values and codes in the new row group file
  • however, fastparquet allows to re-order row groups and it could happen that the last file written (with the consolidated categorical values + codes) is not the file of the last row group. This solution may thus not be the most robust.

• an alternative would be to consolidate at read time, and is the one that "we" would currently favor:

  • as each row group is read, collect all unique category values + coresponding codes
  • build a global mapping of the values, with possibly new codes if it turns out that between different row groups, a same value has different codes or on the opposite, 2 different values share same code
  • if inconsistencies appear,
    • offset troubling codes to new codes
    • set categorical values from global mapping

As far as "we" see it, this operation would be managed in read_col.

Martin, please, do not hesitate to share any comments.
I do have 2 "open points":

• an alternative solution could be to store the dict categories <-> codes in common metadata. But I understand this is not the practise between the different fastparquet libraries? It seems to me that if categories are weighting 10GB in @ElnazVakhitov 's use case, it is a loss of space to store these same values in all row group files. We could store them only once in common metadata. I don't know what should be the guideline, if we can accept to do thing differently than other parquet libraries?

• num_categories info in common metadata is not necessarily correct? I see there is this "consolidate_categories()" function in writer.py but I am not sure when it is triggered, and given the current case submitted, do you think it does its job of correctly consolidating the number of categories in common metadata? If categorical values are not available in common metadata, I am really not sure this function has the required information to actually consolidate correctly the number of categories. Has it?

Thanks in advance for any feedbacks!

[martindurant]

A couple of quick opinions:

• collecting all code->value mappings at read time and then possibly recoding sounds difficult.
• storing categories in the metadata is a no-go, since the metadata is read by all access to the file, even when the column is not required. It could be small, but it could also be very very large. Also, it will be encoded in raw thrift, probably cannot get the full set of types.
• I don't remember how or when category consolidation happens. The num_categories is useful for the user but perhaps not actually used for reading
• "reordering of row-groups" may be possible, but the row groups do have a natural ordering that should be respected.

[yohplala]

@ElnazVakhitov
Please, check out PR #953.
It appears not working for data page v2 yet, I don't know if you are using it?
Maybe @martindurant can provide a few advices to sort this out.

Meanwhile, I would be interested that you test on your own use case and that you share your feedbacks (if not using data page v2 yet)
Thanks!

[yohplala]

I am very sorry, I created a new PR for this, because of a mess in my commit history.
PR #954

datapage v2 CI workflow now runs.

@martindurant when you have time, do not hesitate to share your feedbacks.

[ElnazVakhitov]

I also noticed that pd.read_parquet() can read directories if the files in it have the same schema. But again, if you read from fastparquet, then there is incorrect data in the received dataframe on categorical columns.

[martindurant]

@ElnazVakhitov , to summarise the conversation above: there are caveats with reading categorical data with fastparquet, as documented. It assumes the same parquet dictionaries in every row group, which will only be the case when the data is written from a single input dataframe. However, this very efficient way to read, along with its restrictions, is optional - you can always dereference the dicts as you load.