apache / apache/beam

JdbcIO should allow multiple statements

Open
#18,355 0 comments 0 reactions 0 assignees View on GitHub
improvement io java jdbc P3
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.