Question

Oracle CDC to Snowflake destination throwing Snowflake_56


Hi, I am getting SNOWFLAKE_56 - Key fields not specified for table 'ORCL_EMP' error

When Table Auto create is enabled, CDC also enabled and given table key column as ID column 


14 replies

Userlevel 2
Badge

Hi @Omar,

 

In order to do CDC we need to set the primary keys in Snowflake, right now, even if the table is autocreated, we are not able to set those.

We are already working on making it possible in the future

Hi, I created the table in Snowflake and the primary key.

Have given that Table name in ‘Snowflake’ tab,

Unchecked ‘Table Auto Create’ and

Checked - Processing CDC Data (Use MERGE) and Get Primary Key Information From Snowflake also checked.

 

Ran it again got another error 

SNOWFLAKE_27 - CDC record is missing the 'ID' key field

 

Not sure why this error now.. please help

Userlevel 2
Badge

Hi @Omar,

The problem is now slightly different. In order to do merge, we need the records to contain the primary key field (ID), so looks like your records do not contain it.

Hi @Omar,

 

I have a similar issue. Do I need to activate the option “Get Primary Key Information From Snowflake” on the Snowflake target  if I create the table with PK manually?

 

Regards Sebastian

 

Userlevel 2
Badge

Hi @drozdse1

If the Snowflake tables have that information, yes, check that option and the stage will be able to retrieve it

Userlevel 4
Badge

I am getting the same error. I have tried both the option, specifying the table and column for keys and also adding PK to snowflake table but in both case I get SNOWFLAKE_27.

Userlevel 2
Badge

Hi @saleempothiwala 

 

Can you include the full error? I suspect that something else might be going wrong

Userlevel 4
Badge

@alex.sanchez , you are right, the logs has a different error.

 

I can see this in the logs:

 

2022-11-22 11:49:03,422 ERROR Trying to add the following exception in the stage exceptions queue OracleCDCSource *27ff777d-1f49-11ec-9afa-e9287be7c324@6e8c131e-1f49-11ec-9afa-6f42f802f595   Oracle CDC Data Generator - < 0 > - < 0f1d56da-9a5e-4763-8b66-a40530e42e15 >

com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.error.OracleCDCUnrecoverableException: Error while querying the current database time: {}. at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.nowAtDBTz(OracleCDCSource.java:2684) ~[streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.getEndTimeForStartTime(OracleCDCSource.java:2452) ~[streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.generateRecords(OracleCDCSource.java:1330) ~[streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.lambda$createGeneratorThread$5(OracleCDCSource.java:1024) ~[streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_332] at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_332] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_332] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_332] at java.lang.Thread.run(Thread.java:750) ~[?:1.8.0_332] Caused by: java.sql.SQLRecoverableException: IO Error: Socket read interrupted

 

Userlevel 2
Badge

That is interesting, looks like we are failing to retrieve some information from the origin and this is causing some problems inserting the data in the destination. It seems to be pointing to a Network error, but it probably requires digging deeper into the case.

If you are a customer, please open a Support ticket, they might be able to provide better guidance.

Userlevel 4
Badge

also got these:

 

2022-11-22 11:59:21,225 INFO Generation Task started successfully...: java.util.concurrent.FutureTask@684e771 OracleCDCSource *27ff777d-1f49-11ec-9afa-e9287be7c324@6e8c131e-1f49-11ec-9afa-6f42f802f595   ProductionPipelineRunnable-OracleCDC__335e3021-cfd0-414f-98ec-846ba86af88b__6e8c131e-1f49-11ec-9afa-6f42f802f595-Oracle CDC To Snowflake New - < 587c3ccf-e82a-48ca-93b0-762cdb56e98a>
2022-11-22 11:59:21,225 INFO Starting records generation work OracleCDCSource *27ff777d-1f49-11ec-9afa-e9287be7c324@6e8c131e-1f49-11ec-9afa-6f42f802f595   Oracle CDC Data Generator - < 0 > - < 587c3ccf-e82a-48ca-93b0-762cdb56e98a >
2022-11-22 11:59:39,319 WARN No Field produced for primary key column: schema - TEST01 / table - TABLE01 / column - COLUMN2
Userlevel 4
Badge

The insert works perfectly fine. UPDATE and DELETE shows the WARN above. 

Userlevel 2
Badge

Ok, that explains it, basically we are not being able to get the PK information from Oracle itself, I think you should either be able to specify it in the pipeline configuration or retrieving it from Snowflake (if the tables are created with PKs there)

Userlevel 4
Badge

Thanks for the pointers @alex.sanchez. I looked into my table creating script. 

I was able to fix the issue by adding:

 

ALTER TABLE test01.table01 ADD supplemental LOG data (PRIMARY KEY) columns;

 

Insert, Update and Delete works as intended now :-)

Userlevel 2
Badge

Awesome!! Glad it worked

Reply