1

Is it possible to delete records from Hive external table with AWS S3 bucket as location using IICS.

For example : DELETE FROM MY_HIVE_TABLE WHERE COLUMN1='TEST1';

Anoob K A
  • 59
  • 8

1 Answers1

0

You can not delete from hive(unless they are kudu).

As per your comment, i think you can add a filter transformation right before target hive S3 table with condition COLUMN1<>'TEST1'

And then overwrite the hive target table in S3 using IICS.

This will overwrite same table with everything but COLUMN1=TEST1 i.e. deleting the data.

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
  • Thanks Koushik for your response. However I need to perform the delete operation with IICS target as hive AWS S3 and MS SQL server table as source. Could you please advice how I can perform in that context. – Anoob K A Feb 11 '22 at 04:36
  • In iics can you put a filter right before target. I mentioned this in details in my answer. – Koushik Roy Feb 15 '22 at 07:44
  • Here the source is Microsoft SQL Server table. And we are pulling the new data (delta) using a control table in PostgreSQL. Then before writing this delta to Hive we need to remove existing data matching to that month in the delta from Hive. – Anoob K A Feb 15 '22 at 11:06