You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@beam.apache.org by Juan Romero <js...@gmail.com> on 2024/02/19 22:57:21 UTC

Problem in jdbc connector with autoincrement value

Hi guys. I have a table in apache beam that has an auto increment id with a
sequence.






*CREATE SEQUENCE sap_tm_customer_id_seq;CREATE TABLE IF NOT EXISTS test (
 id bigint DEFAULT nextval('sap_tm_customer_id_seq'::regclass),   name
VARCHAR(10),   load_date_time TIMESTAMP);*

And i have the following pipeline to make the insert into the table:
with beam.Pipeline() as p:
  _ = (
      p
      | beam.Create(
        [

            ExampleRow(id=1,name='adsf', load_date_time='2023-04-05
12:34:55'),
            ExampleRow(id=3,name='adsf', load_date_time='2023-04-05
12:34:56')
        ]).with_output_types(ExampleRow)
      | 'Write to jdbc' >> WriteToJdbc(
          driver_class_name='org.postgresql.Driver',
          jdbc_url='jdbc:postgresql://localhost:5432/postgres',
          username='postgres',
          password='postgres',
          table_name= 'test',
          connection_properties="stringtype=unspecified",
          statement='INSERT INTO test \
                        VALUES(?,?,?) '
      ))

The problem is that I haven't been able to insert the register omitting the
id field into the pcollection in a way the database automatically assigns
the auto increment id.

Can you help me? I have spent a lot of time but i have not realize the
solution.

Thanks!!

Re: Problem in jdbc connector with autoincrement value

Posted by Juan Romero <js...@gmail.com>.

Thanks!! It was useful!!!

On Sat, 24 Feb 2024 at 6:13 PM XQ Hu via user <us...@beam.apache.org> wrote:

> Here is what I did:
>
> CREATE TABLE IF NOT EXISTS test2 (id bigint DEFAULT
> nextval('sap_tm_customer_id_seq'::regclass) NOT NULL, name VARCHAR(10),
> load_date_time TIMESTAMP)
>
> make sure id cannot be NULL (you might not need this).
>
> I tried this for my data without using the id field:
>
> class ExampleRow(NamedTuple):
>     name: str
>     load_date_time: str
>
> For the statement, I used this:
>
> "INSERT INTO test2 VALUES(DEFAULT, ?,?::timestamp)"
>
> DEFAULT fills in the id using sap_tm_customer_id_seq.
>
> I hope this is what you are looking for.
>
>
> On Mon, Feb 19, 2024 at 5:57 PM Juan Romero <js...@gmail.com> wrote:
>
>> Hi guys. I have a table in apache beam that has an auto increment id with
>> a sequence.
>>
>>
>>
>>
>>
>>
>> *CREATE SEQUENCE sap_tm_customer_id_seq;CREATE TABLE IF NOT EXISTS test
>> (   id bigint DEFAULT nextval('sap_tm_customer_id_seq'::regclass),   name
>> VARCHAR(10),   load_date_time TIMESTAMP);*
>>
>> And i have the following pipeline to make the insert into the table:
>> with beam.Pipeline() as p:
>>   _ = (
>>       p
>>       | beam.Create(
>>         [
>>
>>             ExampleRow(id=1,name='adsf', load_date_time='2023-04-05
>> 12:34:55'),
>>             ExampleRow(id=3,name='adsf', load_date_time='2023-04-05
>> 12:34:56')
>>         ]).with_output_types(ExampleRow)
>>       | 'Write to jdbc' >> WriteToJdbc(
>>           driver_class_name='org.postgresql.Driver',
>>           jdbc_url='jdbc:postgresql://localhost:5432/postgres',
>>           username='postgres',
>>           password='postgres',
>>           table_name= 'test',
>>           connection_properties="stringtype=unspecified",
>>           statement='INSERT INTO test \
>>                         VALUES(?,?,?) '
>>       ))
>>
>> The problem is that I haven't been able to insert the register
>> omitting the id field into the pcollection in a way the database
>> automatically assigns the auto increment id.
>>
>> Can you help me? I have spent a lot of time but i have not realize the
>> solution.
>>
>> Thanks!!
>>
>>
>>
>>

Re: Problem in jdbc connector with autoincrement value

Posted by XQ Hu via user <us...@beam.apache.org>.

Here is what I did:

CREATE TABLE IF NOT EXISTS test2 (id bigint DEFAULT
nextval('sap_tm_customer_id_seq'::regclass) NOT NULL, name VARCHAR(10),
load_date_time TIMESTAMP)

make sure id cannot be NULL (you might not need this).

I tried this for my data without using the id field:

class ExampleRow(NamedTuple):
    name: str
    load_date_time: str

For the statement, I used this:

"INSERT INTO test2 VALUES(DEFAULT, ?,?::timestamp)"

DEFAULT fills in the id using sap_tm_customer_id_seq.

I hope this is what you are looking for.


On Mon, Feb 19, 2024 at 5:57 PM Juan Romero <js...@gmail.com> wrote:

> Hi guys. I have a table in apache beam that has an auto increment id with
> a sequence.
>
>
>
>
>
>
> *CREATE SEQUENCE sap_tm_customer_id_seq;CREATE TABLE IF NOT EXISTS test (
>  id bigint DEFAULT nextval('sap_tm_customer_id_seq'::regclass),   name
> VARCHAR(10),   load_date_time TIMESTAMP);*
>
> And i have the following pipeline to make the insert into the table:
> with beam.Pipeline() as p:
>   _ = (
>       p
>       | beam.Create(
>         [
>
>             ExampleRow(id=1,name='adsf', load_date_time='2023-04-05
> 12:34:55'),
>             ExampleRow(id=3,name='adsf', load_date_time='2023-04-05
> 12:34:56')
>         ]).with_output_types(ExampleRow)
>       | 'Write to jdbc' >> WriteToJdbc(
>           driver_class_name='org.postgresql.Driver',
>           jdbc_url='jdbc:postgresql://localhost:5432/postgres',
>           username='postgres',
>           password='postgres',
>           table_name= 'test',
>           connection_properties="stringtype=unspecified",
>           statement='INSERT INTO test \
>                         VALUES(?,?,?) '
>       ))
>
> The problem is that I haven't been able to insert the register
> omitting the id field into the pcollection in a way the database
> automatically assigns the auto increment id.
>
> Can you help me? I have spent a lot of time but i have not realize the
> solution.
>
> Thanks!!
>
>
>
>