[C++] Substrait SinkNode Modification for usability

[asfimport]

Currently, the Substrait implementation uses a consuming_sink node as the end-point to capture the results of an executed query. To further enhance the ability to use the wide variety of SinkNodes in the streaming execution engine, it is better to evaluate other options to provide usability for various use cases. 

Some of the use cases include consuming a Substrait query plan in R and Python APIs. And this extends beyond the Arrow APIs where third-party tools willing to use Substrait can be benefitted from these. At the moment there is active work on providing R/Python APIs for Substrait consumption (https://issues.apache.org/jira/browse/ARROW-15779). 
A potential modification to provide usability is to take in a SinkNodeFactory-like interface instead of just using the ConsumingSink (current implementation). Such a modification would provide flexibility in developing a wide array of applications. 
This issue is open for discussion. 

Reporter: Vibhatha Lakmal Abeykoon / @vibhatha

_{Note: This issue was originally created as ARROW-16036. Please see the migration documentation for further details.}

[asfimport]

Vibhatha Lakmal Abeykoon / @vibhatha:
@westonpace @lidavidm  I added a brief description. Please feel free to modify and update the content of the ticket and continue the discussion here. 

[asfimport]

Todd Farmer / @toddfarmer:
This issue was last updated over 90 days ago, which may be an indication it is no longer being actively worked. To better reflect the current state, the issue is being unassigned. Please feel free to re-take assignment of the issue if it is being actively worked, or if you plan to start that work soon.

[github-actions]

This issue has been marked as stale because it has had no activity in the past 365 days. Please remove the stale label or comment below, or this issue will be closed in 14 days. If this improvement is still desired but has no current owner, please add the 'Status: needs champion' label.