[jira] [Commented] (BEAM-1323) Add parallelism/splitting in JdbcIO

["Eugene Kirpichov (JIRA)" <ji...@apache.org>]

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

Eugene Kirpichov commented on BEAM-1323:
----------------------------------------

I don't think there's a generic way to automatically split an arbitrary SQL query into several sub-queries while satisfying the following reasonable requirements:

- The union of subquery results is equivalent to the result of the original query, e.g. there are no duplicates or missed records: e.g. paging definitely violates this if the query does not have an ORDER BY clause on a unique key (since order of query results is by default nondeterministic), and also violates this if new records can be inserted into the database.
- Executing all the sub-queries in parallel should be linearly faster than executing the original query. Paging violates this too: databases usually don't have an efficient way to retrieve the second page of results without scanning (and skipping) the first page, so splitting a query into N pages will, from the POV of the database, be equivalent to executing the original query N times in parallel.

A more promising direction is to allow the user to specify a query parameterized by primary key range - "select blah where primary_key between (from, to)" where from,to are parameters along which splitting happens. Then there is a chance that the database will be able to efficiently execute the sub-queries in parallel.

Please note that with the current JdbcIO, *processing* of the query results already happens in parallel, because we reshuffle them. It is only *executing the query* that doesn't.

> Add parallelism/splitting in JdbcIO
> -----------------------------------
>
>                 Key: BEAM-1323
>                 URL: https://issues.apache.org/jira/browse/BEAM-1323
>             Project: Beam
>          Issue Type: Improvement
>          Components: sdk-java-extensions
>            Reporter: Jean-Baptiste Onofré
>            Assignee: Jean-Baptiste Onofré
>
> Now, the JDBC IO is basically a {{DoFn}} executed with a {{ParDo}}. So, it means that parallelism is "limited" and executed on one executor.
> We can imagine to create several JDBC {{BoundedSource}}s splitting the SQL query in  subset (for instance using row id paging or any "splitting/limit" we can figure based on the original SQL query) (something similar to what Sqoop is doing).



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)