Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Loading a Json file content using PutDatabaseRecord (nifi) causing error

Highlighted

Loading a Json file content using PutDatabaseRecord (nifi) causing error

New Contributor

Hi,

I am trying to load a file content into database (oracle12c, ojdbc7.jar) but it is throwing below error.

A simple file like below is working fine but when I have nested structure in one of the key's value it is throwing invalid column type.

working file content -->

{
"UCPECLLI": "LNDNNJNW00W:PMC-BPOM-Di-910311910",
"RAWSDWANCONFIG": "it is test dhcp",
"CONFIGTYPE": "DHCP"
}

 

problematic file content --> 

{
"UCPECLLI": "LNDNNJNW00W:PMC-BPOM-Di-910311910",
"RAWSDWANCONFIG": {
"dhcp": {
"statistics": {},
"dhcp4-server-and-relay": {},
"dhcp4-options-profiles": {
"dhcp4-options-profile": [{
"dns-server": ["10.10.10.23"],
"domain-name": "Comcast.net",
"echo-client-id": true,
"name": "DHCP_Options_Profile_1",
"default-router": ["10.10.10.22"]
}]
},
"dhcp6-server-and-relay": {}
}
},
"CONFIGTYPE": "DHCP"
}

 

Screen.JPG

I tried my table with following options:

CREATE TABLE DATASTORE (CONFIGTYPE VARCHAR2(200), UCPECLLI VARCHAR2(200), RAWSDWANCONFIG CLOB)

And

CREATE TABLE DATASTORE (CONFIGTYPE VARCHAR2(200), UCPECLLI VARCHAR2(200), RAWSDWANCONFIG varchar2(2000)).

 

GetFile processor

screen1.JPG

PutDatabaseRecord processor

screen2.JPG

Any help is much appreciated.. thanks in advance.

Don't have an account?
Coming from Hortonworks? Activate your account here