Delete by Embulk

[sureshkmr761]

Hello, we can insert or update the records in target by using respective modes. If we want to delete selected records from the target, is there any way I can do by tweaking the config. file to achieve that e.g. if I expose set of records I want to delete in the target rather than insert/update?

[hiroyuki-sato]

Hello, @sureshkmr761

What plugin are you using? embulk-output-jdbc?
If so, What do you think about deleting records using the after_load setting like the one below?

delete target where update_at < last_update_time

[sureshkmr761]

Thanks @hiroyuki-sato, Yes its embulk-output-jdbc. I need to try after_load. But can this option do this operation in comparison to input table something like:
delete from target_table where Id not in (select Id from input_table)

[hiroyuki-sato]

Due to you don't write your environment, I don't know what is the problem. In general, the input and the target table need id field of course. You can use the delete statement in the after_load part.

[sureshkmr761]

thanks @hiroyuki-sato for the response, my doubt is as below, will these types for query will work for after load or before load? Can we call another table from the in section?
in:
type: postgresql
host: localhost
user: myuser
password: ""
database: pg_database
table: pg_table
query: Select Id, name from pg_table
out:
type: redshift
host: myinstance.us-west-2.redshift.amazonaws.com
user: pg
password: ""
database: rd_database
table: rd_table
s3_bucket: my-redshift-transfer-bucket
s3_key_prefix: temp/redshift
mode: insert
aws_auth_method: env
after_load: delete from rd_table where Id not in (select Id from pg_table)
after_load: delete from rd_table where Id not in (select Id from pg_database.pg_table1)

[hiroyuki-sato]

embulk-output-redshift Configuration

before_load: if set, this SQL will be executed before loading all records. In truncate_insert mode, the SQL will be executed after truncating. replace mode doesn't support this option.
after_load: if set, this SQL will be executed after loading all records.

You can't use multiple after_load keys. Maybe, It works concat SQL statements with the ; sign.

after_load: |
  delete from rd_table where Id not in (select Id from pg_table);
  delete from rd_table where Id not in (select Id from pg_database.pg_table1)"

[dmikurube]

We see no response for a year. Let me close this discussion.