1

I have a MSSQL database whose structure is replicated over a Postgres database. I've enabled CDC in MSSQL and I've used the SQL Server CDC Client in StreamSets Data Collector to listen for changes in that db's tables.

But I can't find a way to write to the same tables in Postgres.

For example I have 3 tables in MSSQL: tableA, tableB, tableC. Same tables I have in Postgres. I insert data into tableA and tableC. I want those changes to be replicated over Postgres.

In StreamSets DC, in order to write to Postgres, I'm using JDBC Producer and in the Table Name field I've specified: ${record:attributes('jdbc.tables')}.

Doing this, the data will be read from tableA_CT, tableB_CT, tableC_CT. Tables created by MSSQL when you enable the CDC option. So I'll end up with those table names in the ${record:attribute('jdbc.tables')}.

Is there a way to write to Postgres in the same tables as in MSSQL ?

bsd
  • 1,207
  • 4
  • 15
  • 28

1 Answers1

0

You can cut the _CT suffix off the jdbc.tables attribute by using an Expression Evaluator with a Header Attribute Expression of:

${str:isNullOrEmpty(record:attribute('jdbc.tables')) ? '' : 
  str:substring(record:attribute('jdbc.tables'), 0, 
    str:length(record:attribute('jdbc.tables')) - 3)}

Note - the str:isNullOrEmpty test is a workaround for SDC-9269.

metadaddy
  • 4,234
  • 1
  • 22
  • 46
  • 1
    Thanks ! I did that already. Was wondering if there was a more direct solution. – bsd Jun 27 '18 at 09:42
  • Not as far as I know - the _CT suffix is in the data that arrives from SQL Server, so it's what we pass on. – metadaddy Jul 04 '18 at 00:44