Ask Your Question
0

How do I insert a CLOB with JDBC producer? (error JDBC_05 - Unsupported data type in record)

asked 2019-11-17 15:06:47 -0600

streamset_tester gravatar image

Hi there

I'm currently evaluating streamsets and am writing my first pipelines of intermediate complexity. But my experience with StreamSets is still quite limited.

I am currently stuck at the end of my pipeline, when I want to write the result of the pipeline to an oracle db with the jdbc producer. The error I'm getting ist: JDBC_05 - Unsupported data type in record: {anrede=Field[MAP:{code=Field[STRING:CODE]}],... etc.

The destination table has 4 columns:

  • ID, type VARCHAR
  • JSON, type CLOB
  • JSON_CREATED_AT, type TIMESTAMP
  • CREATED_AT, type TIMESTAMP

Each record flowing into the JDBC producer contains exactly those fields as:

Record1 : {LIST_MAP}
0 CREATED_AT :  {DATETIME} Nov 17, 2019 9:50:30 PM
1 JSON_CREATED_AT : {DATETIME} Nov 17, 2019 9:50:30 PM
2 JSON : { MAP}
3 ID : {STRING} The_Id_of_that_record_which_I_can_not_post_here

The JSON field is causing the problems as I am not able to cast it to the appropriate format. It is a Map of various String fields and other nested maps.

When I write it to a local FS output in parallel and configure the output to write JSON as Data Format, the written file looks good from my point of view, i.e:

{"CREATED_AT":1574021928066,"JSON_CREATED_AT":1574021928066,"JSON":{"anrede":{"code":"SOME_CODE_HERE"},"vorname":"some_first_name_here", ... etc.

So how do I configure the JDBC producer correctly? I have already tried to cast the JSON field with various paramtertrized value settings, but none worked.

Many thanks in advance!

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted
1

answered 2019-11-18 09:14:20 -0600

streamset_tester gravatar image

Ok I found a way to make it work:

  • Use a JSON Generator for the /JSON Field which is taking the internal Map structure and writes it as a json string.
    • Place the JSON Generator right before the JDBC Producer

I had expected the JDBC Producer to do exactly the same, but apparently I was wrong ;-)

Or is there a better way to do it?

edit flag offensive delete link more

Comments

That sounds like the right approach. Glad you got it sorted out.

iamontheinet gravatar imageiamontheinet ( 2019-11-18 09:44:15 -0600 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-11-17 15:03:03 -0600

Seen: 32 times

Last updated: Nov 17