ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

[#1]

Is there anything special to get this to work?

Hive Table

create table
twitter(
  id int,
  handle string,
  hashtags string,
  msg string,
  time string,
  user_name string,
  tweet_id string,
  unixtime string,
  uuid string
) stored as orc
tblproperties ("orc.compress"="ZLIB");

Data is paired down tweet:

{ "user_name" : "Tweet Person", "time" : "Wed Jul 20 15:09:42 +0000 2016", "unixtime" : "1469027382664", "handle" : "SomeTweeter", "tweet_id" : "755781737674932224", "hashtags" : "", "msg" : "RT some stuff" }

[#2]

Is Translate Field Names set to true? That should enable the matching of the column (which appears capitalized) against the field (which is lowercase)

[#3]

Also if you don't care about that column you can set the Unmatched Column Behavior to warn/ignore

[#4]

i set unmatched columns to ignore

i tried true and false on field names

[#5]

I had catalog and schema name and then left them off. I tried a few options. twitter is a table in default hive database

SelectHiveQL is working fine

[#6]

That did not work.

[#7]

I don't have a column called IS_AUTOINCREMENT. that's the something should be standard in JDBC. wonder if HIVE driver missing something

[#8]

@Timothy Spann did you find a solution to this? I'm hitting the same thing with a sample 3 column hive database

[#9 · Accepted solution]

@dpinkston

Not optimal, but this is a nice workaround:

Use ReplaceText processor

insert into twitter values (${tweet_id}, '${handle:urlEncode()}','${hashtag:urlEncode()}', '${msg:urlEncode()}','${time}', '${user_name:urlEncode()}','${tweet_id}', '${unixtime}','${uuid}')

So that's attributes in there.

I do url encode because of quotes and such. Would like a prepared statement or custom processor or call a groovy script. But this works.

[#10]

I ended up with the same workaround to get it flowing, agreed not optimal but its working!