search

kestra-io / plugin-jdbc

https://kestra.io/plugins/
Apache License 2.0
10 stars 7 forks source link

Improve the MySQL Batch insert task to not require an explicit columns specification #343

Closed paulgrainger85 closed 3 weeks ago

paulgrainger85 commented 1 month ago

Feature description

A challenge with the MySQL insert statements is that you must provide the values as a list of '?' characters, e.g. (from our docs)

   sql: |
      insert into xref values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

For users with a large list of columns doing bulk inserts can be inconvieint and difficult to maintain with long lists of these characters.

A possible solution instead would be something of the form

tasks:
  - id: query
    type: io.kestra.plugin.jdbc.mysql.Query
    url: jdbc:mysql://127.0.0.1:3306/
    username: mysql_user
    password: mysql_passwd
    sql: |
      SELECT *
      FROM xref
      LIMIT 1500;
    store: true
  - id: update
    type: io.kestra.plugin.jdbc.mysql.Batch
    from: "{{ outputs.query.uri }}"
    url: jdbc:mysql://127.0.0.1:3306/
    username: mysql_user
    password: mysql_passwd
    table: target_table  <- new parameter
loicmathieu commented 3 weeks ago

Fixed by https://github.com/kestra-io/plugin-jdbc/pull/358