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]

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

[#3]

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

[#4]

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

[#5]

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.

[#6]

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

[#7]

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

[#8]

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

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

[#9]

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