Thank you both very much for the feedback. Let me digest this and do some examining of the codebase and will get back with any questions but your responses are very much appreciated.
- Jeremy Dyer On Tue, Dec 21, 2021 at 5:28 PM Vladimir Ozerov <[email protected]> wrote: > Hi Jeremy, > > There are several solutions here. As Jacques mentioned, the popular one is > to push the filter/project into the TableScan operator. The critical > observations are: > > 1. SqlNode cannot be linked to RelNode and vice versa in the general > case since an optimized relational plan may differ dramatically from the > original one. > 2. Any rel tree has either TableScan or Values as leaf nodes, and > TableScan contains table and column names. Therefore, it is possible to > construct a valid query for the target backend traversing the RelTree > bottom-up. You may need to generate some intermediate aliases, but the > original column names would be there. > > With this in mind, there are two common approaches to do the operator > pushdown. If the underlying system has limited pushdown capabilities, you > may flatten multiple operators into a single one. An example is a custom > TableScan operator that (1) prunes unused columns extracting referenced > fields from the upper Project operator, and (2) restricts the returned rows > via a Filter pushdown. To achieve this, you can implement a custom > TableScan operator that contains an explicit set of returned columns and a > filter. Then you may implement two rules - to push the attributes from the > parent Project into the scan and push the Filter into the scan. Finally, > you may use either VolcanoPlanner or HepPlanner to execute these rules. For > example, this is a ubiquitous approach, e.g., please see > the PushFilterIntoTableSourceScanRule [1] in Apache Flink. Sample custom > TableScan: > > class CustomTableScan extends TableScan { > List<Integer> projects; // Indexes of returned fields. > RexNode filter; // Optional predicate. > } > > Consider the following query: > SELECT d+e > FROM t > WHERE c > 10 > > The initial logical plan would be: > Project[$3+$4] > Filter[$2>10] > TableScan[t, columns=[a,b,c,d,e]] > > After the Filter pushdown, the plan would be: > Project[$3+$4] > CustomTableScan[t, columns=[a,b,c,d,e], filter=[$2>10]] > > After the project pushdown, the plan would be: > Project[$0+$1] > CustomTableScan[t, columns=[a,b,c,d,e], filter=[$2>10], projects={$3,$4}] > > Now, by looking at the contents of the CustomTableScan, you may deduce that > only columns $2, $3, and $4 are used, referring to b, c, and d, > respectively. By replacing the indexes with real column names, you may > construct a valid query/request for the target system. > > This approach works well if the underlying system has limited optimization > opportunities. For example, the typical optimizations for columnar backend > are column pruning achieved through a project pushdown and > block/page/partition pruning achieved through a filter pushdown (possibly > with the help of SARGs). > > However, some systems may have rich pushdown opportunities. For example, if > the target backend is a JDBC data source, sometimes you may push the whole > operator tree. In this case, the uber operator from the example above might > not work well, as there could be multiple alternative plans. For example, > if the underlying system supports Join and Aggregate pushdown, should we > push "A join B" or "B join A"? Or should we push "Aggregate(Join)" or > "Join(Aggregate)"? In this case, it might be better to convert individual > supported operators one by one with the VolcanoPlanner and the proper cost > model. Examples are Calcite's JDBC infrastructure [2] and Dremio's > integration with other backends via StoragePlugin [3]. This approach is > more powerful but is also much more involved. > > From your explanation, it seems that the Project/Filter pushdown into a > custom TableScan operator might be a good starting point. > > Regards, > Vladimir. > > [1] > > https://github.com/apache/flink/blob/release-1.14.2/flink-table/flink-table-planner/src/main/java/org/apache/flink/table/planner/plan/rules/logical/PushFilterIntoTableSourceScanRule.java > [2] > > https://github.com/apache/calcite/blob/calcite-1.28.0/core/src/main/java/org/apache/calcite/adapter/jdbc/JdbcRel.java > [3] > > https://github.com/dremio/dremio-oss/blob/5d615463e5d468c589c0b91bd20cbf70db3bc581/sabot/kernel/src/main/java/com/dremio/exec/store/StoragePlugin.java#L123 > > вт, 21 дек. 2021 г. в 21:25, Jeremy Dyer <[email protected]>: > > > Hi Vladimir, > > > > I'm certain my design has room for improvement and would love any > > suggestions. Here is the use case. > > > > I'm working on Dask-SQL [1]. We wrap Calcite with a Python layer and use > > Calcite to parse, validate, and generate relational algebra. From the > > relational algebra generated we in turn convert those to Dask Python (and > > therefore Dataframe) API calls. Leaving out a lot of detail in a nutshell > > this is the order of what happens. > > > > 1.) Parse SQL Python str to SqlNode > > 2.) Generate RelNode from SqlNode > > 3.) Convert each RexNode into a Python Pandas/cuDF Dataframe - this is > the > > step where I want to get the original SQL identifier at > > > > For step 3 there are some large performance gains that can be achieved by > > using "predicate pushdown" in the IO readers and for example only reading > > certain columns from a Parquet or ORC file. The format needed to achieve > > this is DNF and requires the original column names so those predicates > can > > be passed down into the implementation libraries. The problem is those > > libraries already exist as CUDA C/C++ implementations and cannot be > > modified. > > > > Does that make sense? If there is a more intelligent way to conditional > > predicates from the SQL query, even if it isn't at the Rex level I would > > love to hear suggestions > > > > [1] - https://github.com/dask-contrib/dask-sql > > > > On Tue, Dec 21, 2021 at 1:05 PM Vladimir Ozerov <[email protected]> > > wrote: > > > > > Hi Jeremy, > > > > > > Could you please share the use case behind this requirement? In the > > general > > > case, it is not possible to link RelNode's attributes to specific > > > identifiers. For this reason, an attempt to extract such identifier > from > > > any "rel" except for the RelRoot might indicate a design issue. > > > > > > Regards, > > > Vladimir. > > > > > > вт, 21 дек. 2021 г. в 20:34, Jeremy Dyer <[email protected]>: > > > > > > > Hello, > > > > > > > > Is it possible to get the original SQL identifier from an instance of > > > > RexInputRef? For example given a simple query like > > > > > > > > SELECT id FROM employees WHERE fname = 'adam' > > > > > > > > Instead of the ordinal name generated by RexInputRef ($11, for > > example). > > > I > > > > would like to find the original SQL identifier (fname, for example) > > > > > > > > Thanks, > > > > Jeremy Dyer > > > > > > > > > >
