Skip to main content

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 

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

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

 

Hi @drozdse1

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

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.

Hi @saleempothiwala 

 

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

@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) ~4streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.getEndTimeForStartTime(OracleCDCSource.java:2452) ~2streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.generateRecords(OracleCDCSource.java:1330) ~0streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at com.streamsets.pipeline.stage.origin.jdbc.cdc.oracle.OracleCDCSource.lambda$createGeneratorThread$5(OracleCDCSource.java:1024) ~4streamsets-datacollector-jdbc-protolib-5.1.0.jar:?] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~1?:1.8.0_332] at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~6?:1.8.0_332] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~9?:1.8.0_332] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~4?:1.8.0_332] at java.lang.Thread.run(Thread.java:750) ~0?:1.8.0_332] Caused by: java.sql.SQLRecoverableException: IO Error: Socket read interrupted

 

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.

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

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

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)

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 :-)

Awesome!! Glad it worked

Reply


Terms & Conditions