question Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL in Archives of Support Questions (Read Only)

ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Mon, 19 Aug 2019 08:30:08 GMT

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" }

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

mburgess — Wed, 20 Jul 2016 22:25:05 GMT

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

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

mburgess — Wed, 20 Jul 2016 22:26:37 GMT

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

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Mon, 19 Aug 2019 08:29:54 GMT

i set unmatched columns to ignore

i tried true and false on field names

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Wed, 20 Jul 2016 22:44:53 GMT

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

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

dpinkston — Sat, 30 Jul 2016 07:28:29 GMT

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

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Sun, 31 Jul 2016 03:12:30 GMT

That did not work.

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Sun, 31 Jul 2016 03:12:34 GMT

@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.

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

dpinkston — Mon, 19 Aug 2019 08:29:46 GMT

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

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Mon, 01 Aug 2016 10:45:08 GMT

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

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

121285904 — Thu, 11 Aug 2016 19:22:37 GMT

I used this method, but it is very slow, how about yours?

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Thu, 11 Aug 2016 19:32:53 GMT

it wasn't slow. I will try in NiFI 1.0

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

121285904 — Thu, 11 Aug 2016 20:37:22 GMT

I spent one day to insert 7000 rows data into hive, but I have more than 800 million rows.

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Thu, 11 Aug 2016 20:42:22 GMT

if you have that many rows you need to go parallel and run on multiple nodes. You should probably trigger a Sqoop job or Spark SQL job from NiFi. have a few nodes running at once.

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

121285904 — Thu, 11 Aug 2016 21:11:13 GMT

thanks for your reply. do you have a example for details?

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Thu, 11 Aug 2016 21:59:41 GMT

Sqoop is just regular sqoop. You call it with executeprocess.

https://sqoop.apache.org/docs/1.4.2/SqoopUserGuide.html#_literal_sqoop_create_hive_table_literal

NiFi + Spark (can be site-to-site, command trigger, kafka)

https://community.hortonworks.com/articles/30213/us-presidential-election-tweet-analysis-using-hdfn.html

https://community.hortonworks.com/articles/12708/nifi-feeding-data-to-spark-streaming.html

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

TimothySpann — Tue, 28 Feb 2017 13:13:19 GMT

store to HDFS as ORC and then create HIVE table ontop of it.

I did 600,000 rows on a 4 GB machine and did that in a few minutes

Re: ConvertJSONtoSQL in Apache NiFi for Sending to PutHiveQL

mburgess — Wed, 14 Jun 2017 21:23:11 GMT

I confirmed this to be a bug in ConvertJSONToSQL, I have written up NIFI-4071, please see the Jira for details.