import java.sql.{DriverManager, ResultSet}
import mongoDb_foundation_data20180913.url
import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.types._
import org.apache.spark.sql.SparkSession
object devicests_20180916 {// spark-submit --driver-class-path /usr/local/jdk/lib/mysql-connector-java-5.1.46.jar --class "devicests_20180916" /testdata/u3.jar
val url = "jdbc:mysql://192.168.1.10:3306/saas?user=saas&password=saas2018"//val url = "jdbc:mysql://134.175.180.116:3306/saas?user=saas&password=saas2018"
val conn=DriverManager.getConnection(url)
def main(args: Array[String]): Unit ={
val conn=DriverManager.getConnection(url)
val conf=new SparkConf().setAppName("appName").setMaster("local")
val sc=new SparkContext(conf)
val spark= SparkSession.builder().appName("Spark SQL basic example").config("spark.debug.maxToStringFields", "200").getOrCreate()
spark.sql("usesaas")
import spark.implicits._
import org.apache.spark.sql.types._
import org.apache.spark.sql.SparkSession
val statement=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE)// val logData=spark.read.textFile("file:mysqldata/aasdata/2018-08-17/devices_2018-08-17")// val log = spark.read.json(logData)
val prop=new java.util.Properties//
// log.createOrReplaceTempView("devicests_states")//
//
// //df.write.mode("append").jdbc(url, "saas.devicests_states", prop)//import org.apache.calcite.adapter
valschema =new StructType()
.add("__v", StringType)
.add("_id", new StructType()
.add("$oid", StringType))
.add("device_type", StringType)
.add("hardware_info", new StructType()
.add("cid", StringType)
.add("mac", StringType)
.add("sn", StringType)
.add("versions", new StructType()
.add("app_version", StringType)
.add("hardware_version", StringType)
.add("zigbee_version", StringType)))
.add("model_id", StringType)
.add("name", StringType)
.add("nickname", StringType)
.add("parent", StringType)
.add("services", ArrayType(StringType))
.add("states", new StructType()
.add("onoff_line", StringType)
.add("passwords",//spark 默认将 passwords 视为 struct,不便于使用 explode 和 map_values// 需要手动定义为 Map[String, Struct]MapType(StringType, new StructType()
.add("description", StringType)
.add("id", StringType)
.add("is_default", StringType)
.add("name", StringType)
.add("permission", new StructType()
.add("begin", StringType)
.add("end", StringType)
.add("status", StringType))
.add("status", IntegerType)
.add("time", StringType)))
.add("power", StringType))
.add("status", IntegerType)
.add("time", StringType)
.add("uuid", StringType)
spark.read.schema(schema)
.json(s"file:///mysqldata/aasdata/2018-09-12/devices_2018-09-12")
.createOrReplaceTempView("devices")
val res=spark.sql(
"""|SELECTuuid,| COUNT(passwords.permission) AS count,| AVG(passwords.permission.end - passwords.permission.begin) AS avg
|FROM
|(| SELECT uuid,explode(map_values(states.passwords)) ASpasswords| FROMdevices|)|WHERE
| passwords.permission.begin IS NOT NULL
| AND passwords.permission.end IS NOT NULL group by uuid""".stripMargin)//.collect.head
res.write.mode("overwrite").jdbc(url, "saas.res_count_avg", prop)
val count = Long(res(0)) val avg = Double(res(1))
}
}