CREATE TABLE `category` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`editdate` char(20) NOT NULL DEFAULT "20170622",
`number` CHAR(100) NOT NULL DEFAULT "spell",
`category` CHAR(100) NOT NULL DEFAULT "综合",
`words` CHAR(100) NOT NULL DEFAULT "name",
PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
ALTER TABLE `category` ADD INDEX(`words`)；
/**
* Created by lkl on 2017/6/27.
*/
import java.sql.{DriverManager, ResultSet}
import org.apache.spark.SparkContext
import org.apache.spark.SparkConf
import java.math.BigDecimal
object layer {
def main(args: Array[String]) {
val conf = new SparkConf().setMaster("local").setAppName("test")
val sc = new SparkContext(conf)
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
val role = "jdbc:mysql://192.168.0.37:3306/emotional?user=root&password=123456&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false"
// val log = sc.textFile("hdfs://192.168.0.211:9000/user/hadoop/emotion/汉语情感词极值表.txt")
// val g = log.map(line => (line.split(" ").head, line.split(" ").last.trim))
// import sqlContext.implicits._
// val df = g.toDF("words", "value").registerTempTable("layer")
// val value = sqlContext.sql("select words,value from layer")
// val pp=value.map(p => {
// val words= p.getString(0)
// val value = p.getString(1)
// (words,value)
// })
//
// pp.foreach(p => {
// val v0 = p._1
// val v1 = p._2.toFloat
// insert(v0, v1)
//
// })
val logCsenticNet = sc.textFile("hdfs://192.168.0.211:9000/user/hadoop/emotion/editCsenticNet.txt")
val gCsenticNet = logCsenticNet.map(line => (line.split(",")(0), line.split(",")(1), line.split(",")(2), line.split(",")(3), line.split(",")(4)))
import sqlContext.implicits._
val dfCsenticNet = gCsenticNet.toDF("words", "attitude", "expression", "extreme", "VALUE").registerTempTable("CsenticNetlayer2")
val valueCsenticNet = sqlContext.sql("select words,attitude,expression,extreme,VALUE from CsenticNetlayer2")
val ppCsenticNet = valueCsenticNet.map(p => {
val v0 = p.getString(0)
val v1 = p.getString(1)
val v2 = p.getString(2)
val v3 = p.getString(3)
val v4 = p.getString(4)
(v0, v1, v2, v3, v4)
})
ppCsenticNet.foreach(p => {
val v0 = p._1
val v1 = p._2
val v2 = p._3
val v3 = p._4
val v4 = p._5
insert2(v0, v1, v2, v3, v4)
})
def insert(value0: String, value1: Float): Unit = {
println(value0, value1)
val rl = "jdbc:mysql://192.168.0.37:3306/emotional?user=root&password=123456&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false"
classOf[com.mysql.jdbc.Driver]
val conn = DriverManager.getConnection(rl)
val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
try {
val prep = conn.prepareStatement("INSERT INTO layer(words,VALUE) VALUES (?,?) ")
prep.setString(1, value0)
prep.setFloat(2, value1)
prep.executeUpdate
} catch {
case e: Exception => e.printStackTrace
}
finally {
// conn.close
}
}
def insert2(value0: String, value1: String, value2: String, value3: String, value4: String): Unit = {
val rl = "jdbc:mysql://192.168.0.37:3306/emotional?user=root&password=123456&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false"
classOf[com.mysql.jdbc.Driver]
val conn = DriverManager.getConnection(rl)
val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)
// CREATE TABLE words2(innersessionId VARCHAR(100),words VARCHAR(100), VARCHAR(100),posit VARCHAR(100),va VARCHAR(100))
try {
val prep = conn.prepareStatement("INSERT INTO CsenticNetlayer2(words,attitude,expression,extreme,VALUE) VALUES (?,?,?,?,?) ")
prep.setString(1, value0)
prep.setString(2, value1)
prep.setString(3, value2)
prep.setString(4, value3)
prep.setString(5, value4)
prep.executeUpdate
} catch {
case e: Exception => e.printStackTrace
}
finally {
// conn.close
}
}
}}