PutDatabaseRecord into snowflake Variant field

Question

I am trying to read a json field from mySQL using executeSQLRecord and insert into snowflake VARIANT field type using PutDataBaseRecord in NiFi 2.6.0.

its giving me this error "

03:20:05 UTC

ERROR

1695731e-9b6f-3cb8-5a9a-98ab38589597

PutDatabaseRecord[id=1695731e-9b6f-3cb8-5a9a-98ab38589597] Failed to put Records to database for FlowFile[filename=c6087904-71a5-479c-9301-2c99883f5f59]. Routing to failure.: net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:

Expression type does not match column data type, expecting VARIANT but got VARCHAR(2) for column JSON_STORE

"

how do I fix this?

vafs · Answer

Hello @Samya,

Thanks for joining our community.

That issue is happening because JSON_STORE format is VARCHAR(2), not VARIANT as required.

Something you can try is to add a ConvertRecord processor before the PutDatabaseRecord.
There, you can use a JsonTreeReader → JsonRecordSetWriter to reformat the content. In that way, the JSON_STORE field becomes a proper JSON object.

Something like this👆

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.