[jira] [Commented] (ARROW-13578) Inconsistent handling of integer-valued partitions in dataset filters API

["Joris Van den Bossche (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/ARROW-13578?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17396598#comment-17396598 ] 

Joris Van den Bossche commented on ARROW-13578:
-----------------------------------------------

[~mnizol] thanks a lot for the clear and detailed report. 

What's causing the confusion here is that we have both a legacy Python implementation of ParquetDataset and a new generic Datasets API, and that we are still in the middle of moving to the new implementation: {{ParquetDataset}} still uses the legacy implementation by default (but you can use {{use_legacy_dataset=False}} to opt in to the new), while {{pq.read_table}} (which is what {{pd.read_parquet}} uses under the hood) is already defaulting to the new implementation (but you can fall back to the old with {{use_legacy_dataset=True}}).

In the legacy ParquetDataset implementation, all partition keys are indeed parsed as strings as you show with the output of {{ParquetDataset.partitions.levels}}. So when passing {{use_legacy_dataset=True}} to the read function, using a string actually works:

{code}
In [19]: pd.read_parquet('./test.parquet', filters=[('key1','=','1')], use_legacy_dataset=True)
Out[19]: 
  data key1 key2 key3 key4   key5                 key6
0  bar    1    1    b  2.2  False  2021-06-03 00:00:00
{code}

BTW, also using an integer works here ({{('key1', '=', 1)}}), because the legacy implementation will try to interpret the value with the type of the partition levels.

In the new Datasets API, the parsing of the directory paths currently supports int32 and strings (when inferring, you can use other types when explicitly passing the schema for the partition keys). 
So when creating a ParquetDataset with use_legacy_dataset=False, we see:

{code}
In [21]: ds = pq.ParquetDataset('test_partitions', use_legacy_dataset=False)

In [22]: ds._dataset.partitioning
Out[22]: <pyarrow._dataset.HivePartitioning at 0x7f2fe010c830>

In [23]: ds._dataset.partitioning.schema
Out[23]: 
key1: dictionary<values=int32, indices=int32, ordered=0>
key2: dictionary<values=int32, indices=int32, ordered=0>
key3: dictionary<values=string, indices=int32, ordered=0>
key4: dictionary<values=string, indices=int32, ordered=0>
key5: dictionary<values=string, indices=int32, ordered=0>
key6: dictionary<values=string, indices=int32, ordered=0>
-- schema metadata --
pandas: '{"index_columns": [], "column_indexes": [{"name": null, "field_n' + 340

In [24]: ds._dataset.partitioning.dictionaries
Out[24]: 
[<pyarrow.lib.Int32Array object at 0x7f2f63ad8760>
 [
   0,
   1,
   2
 ],
 <pyarrow.lib.Int32Array object at 0x7f2f62dae220>
 [
   0,
   1,
   2
 ],
 <pyarrow.lib.StringArray object at 0x7f2f62dae8e0>
 [
   "a",
   "b",
   "c"
 ],
 <pyarrow.lib.StringArray object at 0x7f2f62dae0a0>
 [
   "1.1",
   "2.2",
   "3.3"
 ],
 <pyarrow.lib.StringArray object at 0x7f2f62dae1c0>
 [
   "True",
   "False"
 ],
 <pyarrow.lib.StringArray object at 0x7f2f62dae340>
 [
   "2021-06-02 00:00:00",
   "2021-06-03 00:00:00",
   "2021-06-04 00:00:00"
 ]]
{code}

So the first two partition keys are inferred as int, the others as string. And that's also the reason that for this case, you actually need to specify the filter using an integer (we decided to not do such automatic casting here in the new implementation).

Sidenote: I am using {{ds._dataset.partitioning}} above, but this will become {{ds.partitioning}} after ARROW-13525. 

Using the new datasets API directly, this looks like:

{code}
In [25]: import pyarrow.dataset as ds

In [26]: dataset = ds.dataset("test_partitions/", format="parquet", partitioning="hive")

In [28]: dataset.to_table(filter=ds.field("key1") == 1).to_pandas()
Out[28]: 
  data  key1  key2 key3 key4   key5                 key6
0  bar     1     1    b  2.2  False  2021-06-03 00:00:00
{code}

> Inconsistent handling of integer-valued partitions in dataset filters API
> -------------------------------------------------------------------------
>
>                 Key: ARROW-13578
>                 URL: https://issues.apache.org/jira/browse/ARROW-13578
>             Project: Apache Arrow
>          Issue Type: Bug
>            Reporter: Matt Nizol
>            Priority: Minor
>
> When creating a partitioned data set via the pandas.to_parquet() method, partition columns are ostensibly cast to strings in the partition metadata.  When reading specific partitions via the filters parameter in pandas.read_parquet(), string values must be used for filter operands _except when_ the partition column has an integer value.  
> Consider the following example:
> {code:python}
> import datetime
> import pandas as pd
> df = pd.DataFrame({
>     "key1": ['0', '1', '2'], 
>     "key2": [0, 1, 2],
>     "key3": ['a', 'b', 'c'],
>     "key4": [1.1, 2.2, 3.3],
>     "key5": [True, False, True],
>     "key6": [datetime.date(2021, 6, 2), datetime.date(2021, 6, 3), datetime.date(2021, 6, 4)],
>     "data": ["foo", "bar", "baz"]
> })
> df['key6'] = pd.to_datetime(df['key6'])
> df.to_parquet('./test.parquet', partition_cols=['key1', 'key2', 'key3', 'key4', 'key5', 'key6'])
> {code}
> Reading into a ParquetDataset and inspecting the partition levels suggests that partition keys have been cast to string, regardless of the original type:
> {code:python}
> import pyarrow.parquet as pq
> ds = pq.ParquetDataset('./test.parquet')
> for level in ds.partitions.levels:
>     print(f"{level.name}: {level.keys}")
> {code}
> Output:
> {noformat}
> key1: ['0', '1', '2']
> key2: ['0', '1', '2']
> key3: ['a', 'b', 'c']
> key4: ['1.1', '2.2', '3.3']
> key5: ['True', 'False']
> key6: ['2021-06-02 00:00:00', '2021-06-03 00:00:00', '2021-06-04 00:00:00']{noformat}
> Filtering the dataset using any of the non-integer partition keys along with string-valued operands works as expected:
> {code:python}
> df2=pd.read_parquet('./test.parquet', filters=[('key4','=','1.1'), ('key5', '=', 'True')])
> df2.head()
> {code}
> Output:
> {noformat}
> 	data	key1	key2	key3	key4	key5	key6
> 0	foo	0	0	a	1.1	True	2021-06-02 00:00:00
> {noformat}
> However, filtering the dataset using either of the integer-valued partition keys with a string-valued operand raises an exception, *even when the original column's data type is string*:
> {code:python}
> df2=pd.read_parquet('./test.parquet', filters=[('key1','=','1')])
> df2.head()
> {code}
> {noformat}
> ArrowNotImplementedError: Function equal has no kernel matching input types (array[int32], scalar[string])
> {noformat}
> It would seem to be less surprising / more consistent if filter operands either (a) are always cast to string, or (b) always retain their original type.
> Note, this issue may be related to ARROW-12114.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)