You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@beam.apache.org by Deepak Nagaraj <de...@primer.ai> on 2022/03/31 04:27:48 UTC
WriteToJDBC ignoring "statement" parameter
Hi,
I'm trying to write to Postgres database from Python using portable runner.
The API [1] has a "statement" parameter to let us specify a SQL statement:
driver_class_name – name of the jdbc driver class
jdbc_url – full jdbc url to the database.
username – database username
password – database password
statement – sql statement to be executed
^^
The write works as expected if I don't specify a "statement" parameter.
If I do specify a statement, it's ignored by Beam:
I have specified the following statement, equivalent to an UPSERT in Postgres.
INSERT INTO jdbc_external_test_write_v2a (id, name, id_name)
VALUES (?, ?)
ON CONFLICT ON CONSTRAINT jdbc_external_test_write_v2a_pkey
DO UPDATE SET id = EXCLUDED.id, name = EXCLUDED.name, id_name = EXCLUDED.id_name
However, my Postgres logs indicate that Beam is sending the default
statement, not the statement I have specified:
2022-03-30 21:20:09.055 PDT [14504] LOG: execute S_2: INSERT INTO
jdbc_external_test_write_v2a VALUES($1, $2, $3)
2022-03-30 21:20:09.055 PDT [14504] DETAIL: parameters: $1 = '1', $2
= 'hello', $3 = '1_hello'
2022-03-30 21:20:09.071 PDT [14503] LOG: execute S_2: ROLLBACK
2022-03-30 21:20:09.072 PDT [14504] ERROR: duplicate key value
violates unique constraint "jdbc_external_test_write_v2a_pkey"
How can I get Beam to recognize my (override) SQL statement?
Thanks,
Deepak
[1] https://beam.apache.org/releases/pydoc/2.35.0/apache_beam.io.jdbc.html?highlight=jdbc#module-apache_beam.io.jdbc
Re: WriteToJDBC ignoring "statement" parameter
Posted by Deepak Nagaraj <de...@primer.ai>.
Thanks, I created BEAM-14230 to track this issue:
https://issues.apache.org/jira/browse/BEAM-14230
Regards,
Deepak
On Thu, Mar 31, 2022 at 5:28 PM Chamikara Jayalath <ch...@google.com> wrote:
>
> Probably related: https://github.com/apache/beam/pull/17017
> Though I think that might be the incorrect fix and the order we specify fields in the Python side should not matter.
>
> Thanks,
> Cham
>
> On Thu, Mar 31, 2022 at 7:42 AM Deepak Nagaraj <n....@gmail.com> wrote:
>>
>> Many thanks, I’m happy to take the fix for now, and do an upgrade when it makes it into the release.
>>
>> Thanks again,
>> Deepak
>>
>> On Wed, Mar 30, 2022 at 11:59 PM Pablo Estrada <pa...@google.com> wrote:
>>>
>>> This looks like a bug that we need to fix. I'll take a look tomorrow morning and let you know.
>>>
>>> Once the bug is fixed, you may need to wait for a release, or copy the connector with the fix into your repository.
>>> -P
>>>
>>> On Wed, Mar 30, 2022, 9:28 PM Deepak Nagaraj <de...@primer.ai> wrote:
>>>>
>>>> Hi,
>>>>
>>>> I'm trying to write to Postgres database from Python using portable runner.
>>>>
>>>> The API [1] has a "statement" parameter to let us specify a SQL statement:
>>>>
>>>> driver_class_name – name of the jdbc driver class
>>>> jdbc_url – full jdbc url to the database.
>>>> username – database username
>>>> password – database password
>>>> statement – sql statement to be executed
>>>> ^^
>>>>
>>>> The write works as expected if I don't specify a "statement" parameter.
>>>>
>>>> If I do specify a statement, it's ignored by Beam:
>>>>
>>>> I have specified the following statement, equivalent to an UPSERT in Postgres.
>>>>
>>>> INSERT INTO jdbc_external_test_write_v2a (id, name, id_name)
>>>> VALUES (?, ?)
>>>> ON CONFLICT ON CONSTRAINT jdbc_external_test_write_v2a_pkey
>>>> DO UPDATE SET id = EXCLUDED.id, name = EXCLUDED.name, id_name = EXCLUDED.id_name
>>>>
>>>> However, my Postgres logs indicate that Beam is sending the default
>>>> statement, not the statement I have specified:
>>>>
>>>> 2022-03-30 21:20:09.055 PDT [14504] LOG: execute S_2: INSERT INTO
>>>> jdbc_external_test_write_v2a VALUES($1, $2, $3)
>>>> 2022-03-30 21:20:09.055 PDT [14504] DETAIL: parameters: $1 = '1', $2
>>>> = 'hello', $3 = '1_hello'
>>>> 2022-03-30 21:20:09.071 PDT [14503] LOG: execute S_2: ROLLBACK
>>>> 2022-03-30 21:20:09.072 PDT [14504] ERROR: duplicate key value
>>>> violates unique constraint "jdbc_external_test_write_v2a_pkey"
>>>>
>>>> How can I get Beam to recognize my (override) SQL statement?
>>>>
>>>> Thanks,
>>>> Deepak
>>>>
>>>> [1] https://beam.apache.org/releases/pydoc/2.35.0/apache_beam.io.jdbc.html?highlight=jdbc#module-apache_beam.io.jdbc
Re: WriteToJDBC ignoring "statement" parameter
Posted by Chamikara Jayalath <ch...@google.com>.
Probably related: https://github.com/apache/beam/pull/17017
Though I think that might be the incorrect fix and the order we specify
fields in the Python side should not matter.
Thanks,
Cham
On Thu, Mar 31, 2022 at 7:42 AM Deepak Nagaraj <n....@gmail.com> wrote:
> Many thanks, I’m happy to take the fix for now, and do an upgrade when it
> makes it into the release.
>
> Thanks again,
> Deepak
>
> On Wed, Mar 30, 2022 at 11:59 PM Pablo Estrada <pa...@google.com> wrote:
>
>> This looks like a bug that we need to fix. I'll take a look tomorrow
>> morning and let you know.
>>
>> Once the bug is fixed, you may need to wait for a release, or copy the
>> connector with the fix into your repository.
>> -P
>>
>> On Wed, Mar 30, 2022, 9:28 PM Deepak Nagaraj <de...@primer.ai>
>> wrote:
>>
>>> Hi,
>>>
>>> I'm trying to write to Postgres database from Python using portable
>>> runner.
>>>
>>> The API [1] has a "statement" parameter to let us specify a SQL
>>> statement:
>>>
>>> driver_class_name – name of the jdbc driver class
>>> jdbc_url – full jdbc url to the database.
>>> username – database username
>>> password – database password
>>> statement – sql statement to be executed
>>> ^^
>>>
>>> The write works as expected if I don't specify a "statement" parameter.
>>>
>>> If I do specify a statement, it's ignored by Beam:
>>>
>>> I have specified the following statement, equivalent to an UPSERT in
>>> Postgres.
>>>
>>> INSERT INTO jdbc_external_test_write_v2a (id, name, id_name)
>>> VALUES (?, ?)
>>> ON CONFLICT ON CONSTRAINT jdbc_external_test_write_v2a_pkey
>>> DO UPDATE SET id = EXCLUDED.id, name = EXCLUDED.name, id_name =
>>> EXCLUDED.id_name
>>>
>>> However, my Postgres logs indicate that Beam is sending the default
>>> statement, not the statement I have specified:
>>>
>>> 2022-03-30 21:20:09.055 PDT [14504] LOG: execute S_2: INSERT INTO
>>> jdbc_external_test_write_v2a VALUES($1, $2, $3)
>>> 2022-03-30 21:20:09.055 PDT [14504] DETAIL: parameters: $1 = '1', $2
>>> = 'hello', $3 = '1_hello'
>>> 2022-03-30 21:20:09.071 PDT [14503] LOG: execute S_2: ROLLBACK
>>> 2022-03-30 21:20:09.072 PDT [14504] ERROR: duplicate key value
>>> violates unique constraint "jdbc_external_test_write_v2a_pkey"
>>>
>>> How can I get Beam to recognize my (override) SQL statement?
>>>
>>> Thanks,
>>> Deepak
>>>
>>> [1]
>>> https://beam.apache.org/releases/pydoc/2.35.0/apache_beam.io.jdbc.html?highlight=jdbc#module-apache_beam.io.jdbc
>>>
>>
Re: WriteToJDBC ignoring "statement" parameter
Posted by Deepak Nagaraj <n....@gmail.com>.
Many thanks, I’m happy to take the fix for now, and do an upgrade when it
makes it into the release.
Thanks again,
Deepak
On Wed, Mar 30, 2022 at 11:59 PM Pablo Estrada <pa...@google.com> wrote:
> This looks like a bug that we need to fix. I'll take a look tomorrow
> morning and let you know.
>
> Once the bug is fixed, you may need to wait for a release, or copy the
> connector with the fix into your repository.
> -P
>
> On Wed, Mar 30, 2022, 9:28 PM Deepak Nagaraj <de...@primer.ai>
> wrote:
>
>> Hi,
>>
>> I'm trying to write to Postgres database from Python using portable
>> runner.
>>
>> The API [1] has a "statement" parameter to let us specify a SQL statement:
>>
>> driver_class_name – name of the jdbc driver class
>> jdbc_url – full jdbc url to the database.
>> username – database username
>> password – database password
>> statement – sql statement to be executed
>> ^^
>>
>> The write works as expected if I don't specify a "statement" parameter.
>>
>> If I do specify a statement, it's ignored by Beam:
>>
>> I have specified the following statement, equivalent to an UPSERT in
>> Postgres.
>>
>> INSERT INTO jdbc_external_test_write_v2a (id, name, id_name)
>> VALUES (?, ?)
>> ON CONFLICT ON CONSTRAINT jdbc_external_test_write_v2a_pkey
>> DO UPDATE SET id = EXCLUDED.id, name = EXCLUDED.name, id_name =
>> EXCLUDED.id_name
>>
>> However, my Postgres logs indicate that Beam is sending the default
>> statement, not the statement I have specified:
>>
>> 2022-03-30 21:20:09.055 PDT [14504] LOG: execute S_2: INSERT INTO
>> jdbc_external_test_write_v2a VALUES($1, $2, $3)
>> 2022-03-30 21:20:09.055 PDT [14504] DETAIL: parameters: $1 = '1', $2
>> = 'hello', $3 = '1_hello'
>> 2022-03-30 21:20:09.071 PDT [14503] LOG: execute S_2: ROLLBACK
>> 2022-03-30 21:20:09.072 PDT [14504] ERROR: duplicate key value
>> violates unique constraint "jdbc_external_test_write_v2a_pkey"
>>
>> How can I get Beam to recognize my (override) SQL statement?
>>
>> Thanks,
>> Deepak
>>
>> [1]
>> https://beam.apache.org/releases/pydoc/2.35.0/apache_beam.io.jdbc.html?highlight=jdbc#module-apache_beam.io.jdbc
>>
>
Re: WriteToJDBC ignoring "statement" parameter
Posted by Pablo Estrada <pa...@google.com>.
This looks like a bug that we need to fix. I'll take a look tomorrow
morning and let you know.
Once the bug is fixed, you may need to wait for a release, or copy the
connector with the fix into your repository.
-P
On Wed, Mar 30, 2022, 9:28 PM Deepak Nagaraj <de...@primer.ai>
wrote:
> Hi,
>
> I'm trying to write to Postgres database from Python using portable runner.
>
> The API [1] has a "statement" parameter to let us specify a SQL statement:
>
> driver_class_name – name of the jdbc driver class
> jdbc_url – full jdbc url to the database.
> username – database username
> password – database password
> statement – sql statement to be executed
> ^^
>
> The write works as expected if I don't specify a "statement" parameter.
>
> If I do specify a statement, it's ignored by Beam:
>
> I have specified the following statement, equivalent to an UPSERT in
> Postgres.
>
> INSERT INTO jdbc_external_test_write_v2a (id, name, id_name)
> VALUES (?, ?)
> ON CONFLICT ON CONSTRAINT jdbc_external_test_write_v2a_pkey
> DO UPDATE SET id = EXCLUDED.id, name = EXCLUDED.name, id_name =
> EXCLUDED.id_name
>
> However, my Postgres logs indicate that Beam is sending the default
> statement, not the statement I have specified:
>
> 2022-03-30 21:20:09.055 PDT [14504] LOG: execute S_2: INSERT INTO
> jdbc_external_test_write_v2a VALUES($1, $2, $3)
> 2022-03-30 21:20:09.055 PDT [14504] DETAIL: parameters: $1 = '1', $2
> = 'hello', $3 = '1_hello'
> 2022-03-30 21:20:09.071 PDT [14503] LOG: execute S_2: ROLLBACK
> 2022-03-30 21:20:09.072 PDT [14504] ERROR: duplicate key value
> violates unique constraint "jdbc_external_test_write_v2a_pkey"
>
> How can I get Beam to recognize my (override) SQL statement?
>
> Thanks,
> Deepak
>
> [1]
> https://beam.apache.org/releases/pydoc/2.35.0/apache_beam.io.jdbc.html?highlight=jdbc#module-apache_beam.io.jdbc
>