JdbcIO should allow multiple statements
- Dominant language
- Java
- Stars
- 8.7k
- Forks
- 4.7k
- Avg merge
- 2d 2h
- Merged PRs (30d)
- 205
Description
Right now the JdbcIO pipeline adds a single Write statement to the batch per PCollection element. This makes it difficult to execute several statements that require a specific order (for instance due to constraints in the DB), since JDBC doesn't allow multiple statements in a single "addBatch".
For instance, I can't execute `INSERT INTO users (id) VALUES (test); INSERT INTO profiles (user_id, name) VALUES (test, 'John');`
There's several ways we could solve this, one would be not to return a PDone but just pass through the data so that you can at least chain several Writes.
Alternatively we could take the Statement string, split on the ";" character if there is one and apply each statement to the either an array of argument setters or the same setter with an additional `statement_#` parameter. And then we simply don't commit a batch between these statements.
Imported from Jira [BEAM-2376](https://issues.apache.org/jira/browse/BEAM-2376). Original Jira may contain additional context.
Reported by: rxminus.
Contributor guide
Research direction
Start by locating JdbcIO's Write transform and the code that adds one statement per PCollection element to a JDBC batch. Compare the proposed pass-through and multi-statement approaches, then define how ordered statements and argument setters should behave before checking that the resulting API supports the reported use case.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100