Batch

yaml

type: "io.kestra.plugin.jdbc.postgresql.Batch"

Execute a batch query to a PostgreSQL server.

Examples

Fetch rows from a table, and bulk insert them to another one.

yaml

id: postgres_bulk_insert
namespace: company.team

tasks:
  - id: query
    type: io.kestra.plugin.jdbc.postgresql.Query
    url: jdbc:postgresql://dev:56982/
    username: pg_user
    password: pg_password
    sql: |
      SELECT *
      FROM xref
      LIMIT 1500;
    fetchType: STORE

  - id: update
    type: io.kestra.plugin.jdbc.postgresql.Batch
    from: "{{ outputs.query.uri }}"
    url: jdbc:postgresql://prod:56982/
    username: pg_user
    password: pg_password
    sql: |
      insert into xref values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

Fetch rows from a table, and bulk insert them to another one, without using sql query.

yaml

id: postgres_bulk_insert
namespace: company.team

tasks:
  - id: query
    type: io.kestra.plugin.jdbc.postgresql.Query
    url: jdbc:postgresql://dev:56982/
    username: pg_user
    password: pg_password
    sql: |
      SELECT *
      FROM xref
      LIMIT 1500;
    fetchType: STORE

  - id: update
    type: io.kestra.plugin.jdbc.postgresql.Batch
    from: "{{ outputs.query.uri }}"
    url: jdbc:postgresql://prod:56982/
    username: pg_user
    password: pg_password
    table: xre

Properties

`chunk`

Type: integer
Dynamic: ✔️
Required: ✔️
Default: 1000

The size of chunk for every bulk request.

`from`

Type: string
Dynamic: ✔️
Required: ✔️

Source file URI

`sql`

Type: string
Dynamic: ✔️
Required: ✔️

Insert query to be executed.

The query must have as many question marks as the number of columns in the table. Example: 'insert into <table_name> values( ? , ? , ? )' for 3 columns. In case you do not want all columns, you need to specify it in the query in the columns property Example: 'insert into <table_name> (id, name) values( ? , ? )' for inserting data into 2 columns: 'id' and 'name'.

`url`

Type: string
Dynamic: ✔️
Required: ✔️

The JDBC URL to connect to the database.

`columns`

Type: array
SubType: string
Dynamic: ✔️
Required: ❌

The columns to be inserted.

If not provided, ? count need to match the from number of columns.

`password`

Type: string
Dynamic: ✔️
Required: ❌

The database user's password.

`ssl`

Type: boolean
Dynamic: ❌
Required: ❌
Default: false

Is the connection SSL?

`sslCert`

Type: string
Dynamic: ✔️
Required: ❌

The SSL cert.

Must be a PEM encoded certificate

`sslKey`

Type: string
Dynamic: ✔️
Required: ❌

The SSL key.

Must be a PEM encoded key

`sslKeyPassword`

Type: string
Dynamic: ✔️
Required: ❌

The SSL key password.

`sslMode`

Type: string
Dynamic: ❌
Required: ❌
Possible Values:
- DISABLE
- ALLOW
- PREFER
- REQUIRE
- VERIFY_CA
- VERIFY_FULL

The SSL mode.

`sslRootCert`

Type: string
Dynamic: ✔️
Required: ❌

The SSL root cert.

Must be a PEM encoded certificate

`table`

Type: string
Dynamic: ✔️
Required: ❌

The table from which column names will be retrieved.

This property specifies the table name which will be used to retrieve the columns for the inserted values. You can use it instead of specifying manually the columns in the columns property. In this case, the sql property can also be omitted, an INSERT statement would be generated automatically.