Support DATE and TIMESTAMP pushdown for Hive connector

[sopel39]

Is anybody working on this? Otherwise I will take this one.

[sopel39]

@electrum Does this ticket mean pushing down date and timestamp predicates?

[electrum]

Yes. The Raptor connector supports this.

[sopel39]

I have browsed through the code and I've noticed that predicates are described by ToupleDomain object. The ToupleDomain 'effectivePredicate' instance is pushed down to HivePageSourceFactory level. However, only OrcPageSourceFactory seems to make use of this object. Should filtering mechanism be implemented for other types of PageSource? Is there some other pushdown/filtering mechanism that I haven't noticed?

[electrum]

No other file formats support push down, so it is only used for partition pruning.

[sopel39]

From the code I see that date is supported for ORC: https://github.com/facebook/presto/blob/master/presto-orc/src/main/java/com/facebook/presto/orc/TupleDomainOrcPredicate.java#L107

Parquet also stores min/max statistics for each data page, so maybe it's also worth adding pruning for parquet and possibly other file formats too? Did you consider some kind of lazily computed page statistics (e.g. gathering min/max when reading data during query)?

[sopel39]

It looks that there is support for TIMESTAMP predicates in ORC too, am I right? TIMESTAMP column has stats represented by IntegerColumnStatistics, which store min/max long values for the column.

[dain]

@zhenxiao is working on predicate push down for parquet #3027 and that should be able to take advantage of this also when it lands.

For ORC statistics, the important thing is to test the stats ORC produces. ORC had/has a bunch of bugs with collecting and rolling up statistics, so any code using them needs to adapt around these bugs. For example the date statistics are rolled up wrong (https://github.com/facebook/presto/blob/master/presto-orc/src/main/java/com/facebook/presto/orc/metadata/OrcMetadataReader.java#L309).

[sopel39]

What about TIMESTAMP statistics? I believe timestamp domain is created at https://github.com/facebook/presto/blob/master/presto-orc/src/main/java/com/facebook/presto/orc/TupleDomainOrcPredicate.java#L110, but https://github.com/facebook/presto/blob/master/presto-orc/src/main/java/com/facebook/presto/orc/metadata/OrcMetadataReader.java#L188 does not check if isRowGroup is false. Shouldn't code path be the same as for DATE?

[kbajda]

Well, maybe we can contribute to ORC and fix the bugs as well :) One challenge might be with already existing ORC files. If they contain unreliable stats and we want to use to filter things out, that might be an issue.

[dain]

They already fixed the ones I found. For old files, they added the version of the writer to the file footer, so if you know which writers are broken, you can work around old data. We have not updated our code to deal with this yet.

[electrum]

There are two parts to this: supporting for partition pruning, and supporting pushdown for ORC. These are independent changes. I would start with partitions, since that should be easier. Though for both changes, you need to deal with Hive's craziness around timezones for timestamps.

[zhenxiao]

Thanks @dain Yes, I am doing predicate pushdown for parquet, using parquet's min/max stats. It is in https://github.com/facebook/presto/pull/3027. It would be nice if this is done, parquet side could make use of it.

[sopel39]

@electrum Isn't partition prunning for DATE and TIMESTAMPS already implemented in https://github.com/facebook/presto/blob/master/presto-hive/src/main/java/com/facebook/presto/hive/HiveSplitManager.java#L205 and https://github.com/facebook/presto/blob/master/presto-hive/src/main/java/com/facebook/presto/hive/HiveUtil.java#L374?

Also, by looking at ORC code I thought that DATA and TIMESTAMP predicates are already supported (as ints and longs).