[SPARK-38852] Better Data Source V2 operator pushdown framework

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.3.0
• Component/s: SQL
• Epic Link: More comprehensive DSV2 push down capabilities
• Assignee: Unassigned
• Reporter: jiaan.geng
• Created: 11/Apr/22 06:43
• Updated: 08/Jun/22 10:32

Description

Currently, Spark supports push down Filters and Aggregates to data source.
However, the Data Source V2 operator pushdown framework has the following shortcomings:

1. Only simple filter and aggregate are supported, which makes it impossible to apply in most scenarios
2. The incompatibility of SQL syntax makes it impossible to apply in most scenarios
3. Aggregate push down does not support multiple partitions of data sources
4. Spark's additional aggregate will cause some overhead
5. Limit push down is not supported
6. Top n push down is not supported
7. Aggregate push down does not support group by expressions
8. Aggregate push down does not support not use aggregate functions
9. Offset push down is not supported
10. Paging push down is not supported

Sub-Tasks

• pushDownPredicate=false should prevent push down filters to JDBC data source: Resolved
• Improve the implement of aggregate pushdown.: Resolved
• Move compileAggregates from JDBCRDD to JdbcDialect: Resolved
• Support push down top N to JDBC data source V2: Resolved
• Support datasource v2 complete aggregate pushdown: Resolved
• Improve the implement of JDBCV2Suite: Resolved
• Translate more standard aggregate functions for pushdown: Resolved
• Upgrade h2 from 1.4.195 to 2.0.202: Resolved
• DS V2 supports partial aggregate push-down AVG: Resolved
• Compile aggregate functions of build-in JDBC dialect: Resolved
• A new framework to represent catalyst expressions in DS v2 APIs: Resolved
• Reactor framework so as JDBC dialect could compile expression by self way: Resolved
• Add factory method getConnection into JDBCDialect.: Resolved
• If `Sum`, `Count`, `Any` accompany distinct, cannot do partial agg push down.: Resolved
• Refactor framework so as JDBC dialect could compile filter by self way: Resolved
• DS V2 aggregate push-down supports project with alias: Resolved
• DS V2 topN push-down supports project with alias: Resolved
• Datasource v2 supports partial topN push-down: Resolved
• Support push down Cast to JDBC data source V2: Resolved
• If limit could pushed down and Data source only have one partition, DS V2 should not do limit again: Resolved
• DS V2 supports push down misc non-aggregate functions: Resolved
• DS V2 supports push down math functions: Resolved
• Update document of JDBC options for pushDownAggregate and pushDownLimit: Resolved
• DS V2 supports push down string functions: Resolved
• DS V2 supports push down collection functions: Open
• DS V2 supports push down misc functions: Resolved
• DS V2 supports push down OFFSET operator: Resolved
• DS V2 aggregate push-down supports group by expressions: Resolved
• DS V2 supports push down datetime functions: Resolved
• DS V2 Top N push-down supports order by expressions: Resolved
• DS V2 Limit push-down should avoid out of memory: Resolved
• DS V2 aggregate partial push-down should supports group by without aggregate functions: Resolved
• DS V2 supports push down DS V2 UDF: Resolved
• DS V2 aggregate push down can work with OFFSET or LIMIT: Resolved
• H2Dialect should override getJDBCType so as make the data type is correct: Resolved
• Jdbc dialect should decide which function could be pushed down.: Resolved
• JDBC dialect supports registering dialect specific functions: Resolved
• Compile build-in linear regression aggregate functions for JDBC dialect: Resolved
• Translate linear regression aggregate functions for pushdown: Resolved
• Capitalize sql keywords in JDBCV2Suite: Resolved
• DS V2 supports push down misc non-aggregate functions(non ANSI): Resolved
• DS V2 supports push down math functions(non ANSI): Resolved
• DS V2 pushdown should unify the compile API: Resolved
• Update document of JDBC options for pushDownOffset: Resolved
• DS V2 aggregate push down can work with Top N or Paging (Sort with group expressions): Resolved
• Simplify V2ExpressionBuilder by extract common method.: Resolved
• Translate common used aggregate functions for pushdown (non ANSI): Open
• Organize the check of push down information for JDBCV2Suite: Resolved
• DS V2 supports push down string functions(non ANSI): Resolved
• DS V2 push-down translate Cast if the cast is safe: Resolved
• DS V2 pushdown should unify the translate path: Resolved
• DS V2 expressions should have the default toString: Resolved
• Extract the function that construct the select statement for JDBC dialect.: Resolved
• DS V2 pushdown supports supports JDBC dialects compile `SortOrder` by themselves: Resolved
• DS V2 pushdown could let JDBC dialect decide to push down offset and limit: Resolved
• The built-in dialects support OFFSET and paging query.: Resolved
• Fix the bug that pushdown offset or paging is invalid for some built-in dialect: In Progress
• Change the default value of JDBC options about push down to true: Resolved
• Abstract the excluded method for better test for JDBC docker tests.: Resolved