I want to find average of the 'rate' column using scala code in Spark.For that I have created Dataframe and view then use Spark SQL for queries. When I run select query using view it gives proper output But when I perform avg and group by using view then it gives no records.
data.txt is a tab separated file.
data.txt :
abandon -2
abandoned -2
abandons -2
I want to perform twitter sentiment analysis so I stored tweets in dstream and from dstream generate dataframe then join dataframe with AFINN.txt file dataframe But when I perform below code it fetch null records while performing grouping and avg on DF of AFINN.
val consumerKey="xxxxxxxxx"
val consumerSecret="xxxxxxxxxx"
val accessToken="xxxxx-xxxxxxx"
val accessTokenSecret="xxxxxxxx"
val args = Array(consumerKey, consumerSecret, accessToken, accessTokenSecret)
System.setProperty("twitter4j.oauth.consumerKey", consumerKey)
System.setProperty("twitter4j.oauth.consumerSecret", consumerSecret)
System.setProperty("twitter4j.oauth.accessToken", accessToken)
System.setProperty("twitter4j.oauth.accessTokenSecret", accessTokenSecret)
val sparkConf = new SparkConf().setAppName("twitterSentiment").setMaster("local[4]").set("spark.driver.allowMultipleContexts","true")
val ssc = new StreamingContext(sparkConf, Seconds(10))
val tweets = TwitterUtils.createStream(ssc, None, Array("#India","#Sports"),StorageLevel.MEMORY_AND_DISK)
val englishTweets = tweets.filter(_.getLang() == "en")
val textMsg = englishTweets.map( status => (status.getId(), status.getText(),status.getText().split(" ")))
val AFINN = sc.textFile("hdfs://sandbox-hdp.hortonworks.com:8020/Input/AFINN1.txt").map(x=> x.split("\t")).map(x=>(x(0).toString,x(1).toInt))
val AFINNDF = AFINN.toDF("word","rate")
AFINNDF.createOrReplaceTempView("temp")
val DF = spark.sql("select * from temp")
DF.show()
Output:
+----------+----+
| word|rate|
+----------+----+
| abandon| -2|
| abandoned| -2|
| abandons| -2|
+----------+----+
val DF = spark.sql("select word,avg(rate) as rating from temp group by word")
//DF: org.apache.spark.sql.DataFrame = [word: string, rating: double]
Output:
+----+------+
|word|rating|
+----+------+
+----+------+
How to find avg using Spark SQL queries in scala?
Thanks,
