注意username会报错,用user
.option("user","root")
简约方法
conh.sql("select * from ods.student").show()
val consq=conh.read.format("jdbc")
.option("url","jdbc:mysql://bigdata1:3306/ds_db01?useSSL=false")
.option("driver","com.mysql.jdbc.Driver")
.option("user","root")
.option("password","123456")
.option("dbtable","brand_info")
.load().createOrReplaceTempView("v")
conh.sql("select * from v").show()
笨方法
package Four.test
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession
object tx {
def main(args: Array[String]): Unit = {
val MYSQLIP = "bigdata1"
val DATABASE = "ds_db01" //这个是数据库名称 MySQL需要操作的数据库
val MYSQLDBURL = "jdbc:mysql://" + MYSQLIP + ":3306/" + DATABASE + "?characterEncoding=UTF-8"
val MYSQLDRIVER = "com.mysql.jdbc.Driver"
val MYSQLUSER = "root"
val MYSQLPASSWORD = "123456"
// System.setProperty("HADOOP_USER_NAME", "root")
val sparkConf = new SparkConf().setMaster("local[*]").setAppName("sparkSQL").set("spark.testing.memory", "2147480000")
val spark = SparkSession.builder().enableHiveSupport().config(sparkConf).getOrCreate()
val ss = spark.read.format("jdbc")
.option("url", MYSQLDBURL)
.option("driver", MYSQLDRIVER)
.option("user", MYSQLUSER)
.option("password", MYSQLPASSWORD)
.option("dbtable", "brand_info").load().createTempView("v") //这句代码的意思是使用刚刚的demo数据库的stu表并装载然后重新命名为v
//验证数据读取是否成功 成功则显示前二十条
spark.sql("select * from v ".stripMargin).show() //查表格可以直接使用刚刚的别名
spark.sql("select count(*) from v".stripMargin).show()
}
}